共计 4278 个字符,预计需要花费 11 分钟才能阅读完成。
conn / as sysdbaConnected.SQL> SQL> oradebug setmypid Statement processed.SQL> SQL> oradebug event…”>
自动写代码机器人,免费开通
使用 10046 事件查看真实的执行计划
操作如下:
SQL conn / as sysdba
Connected.
SQL
SQL oradebug setmypid
Statement processed.
SQL
SQL oradebug event 10046 trace name context forever,level 12;
Statement processed.
SQL select count(object_id) from roidba.tt;
COUNT(OBJECT_ID)
—————————–
5524288
SQL oradebug event 10046 trace name context off;
Statement processed.
SQL oradebug tracefile_name;
/u01/app/oracle/diag/rdbms/orcl/orcl2/trace/orcl2_ora_29019.trc
SQL !ls -l /u01/app/oracle/diag/rdbms/orcl/orcl2/trace/orcl2_ora_29019.trc
-rw-r—– 1 oracle asmadmin 3478 Apr 17 10:32 /u01/app/oracle/diag/rdbms/orcl/orcl2/trace/orcl2_ora_29019.trc
SQL
roidb2:orcl2:/home/oracle $tkprof /u01/app/oracle/diag/rdbms/orcl/orcl2/trace/orcl2_ora_29019.trc trace.out
TKPROF: Release 11.2.0.4.0 – Development on Tue Apr 17 10:37:19 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
roidb2:orcl2:/home/oracle $more trace.out
TKPROF: Release 11.2.0.4.0 – Development on Tue Apr 17 10:37:19 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Trace file: /u01/app/oracle/diag/rdbms/orcl/orcl2/trace/orcl2_ora_29019.trc
Sort options: default
********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************
SQL ID: fp42r7m3kgabz Plan Hash: 1131838604
select count(object_id)
from
roidba.tt
call count cpu elapsed disk query current rows
——- —— ——– ———- ———- ———- ———- ———-
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.59 0.63 0 12279 0 1
——- —— ——– ———- ———- ———- ———- ———-
total 4 0.59 0.63 0 12279 0 1
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
———- ———- ———- —————————————————
1 1 1 SORT AGGREGATE (cr=12279 pr=0 pw=0 time=631302 us)
5524288 5524288 5524288 INDEX FAST FULL SCAN IDX_OBJECT_ID (cr=12279 pr=0 pw=0 time=2086333 us cost=3335 size=
13810720 card=2762144)(object id 87785)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
—————————————- Waited ———- ————
SQL*Net message to client 2 0.00 0.00
SQL*Net message from client 2 7.62 7.62
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
——- —— ——– ———- ———- ———- ———- ———-
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.59 0.63 0 12279 0 1
——- —— ——– ———- ———- ———- ———- ———-
total 4 0.59 0.63 0 12279 0 1
Misses in library cache during parse: 0
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
—————————————- Waited ———- ————
SQL*Net message to client 3 0.00 0.00
SQL*Net message from client 3 7.62 13.91
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
——- —— ——– ———- ———- ———- ———- ———-
Parse 0 0.00 0.00 0 0 0 0
Execute 0 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
——- —— ——– ———- ———- ———- ———- ———-
total 0 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
1 user SQL statements in session.
0 internal SQL statements in session.
1 SQL statements in session.
********************************************************************************
Trace file: /u01/app/oracle/diag/rdbms/orcl/orcl2/trace/orcl2_ora_29019.trc
Trace file compatibility: 11.1.0.7
Sort options: default
1 session in tracefile.
1 user SQL statements in trace file.
0 internal SQL statements in trace file.
1 SQL statements in trace file.
1 unique SQL statements in trace file.
69 lines in trace file.
0 elapsed seconds in trace file.
向 AI 问一下细节
丸趣 TV 网 – 提供最优质的资源集合!