Oracle 11g 查看执行计划10046事件

48次阅读
没有评论

共计 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 网 – 提供最优质的资源集合!

正文完
 
丸趣
版权声明:本站原创文章,由 丸趣 2023-12-18发表,共计4278字。
转载说明:除特殊说明外本站除技术相关以外文章皆由网络搜集发布,转载请注明出处。
评论(没有评论)