-
set autotrace on
-
explain plan
explain plan for select 0 from dual;
select * from table(dbms_xplan.display) -
10046事件
alter session setevents ‘10046 trace name context forever,level 12’; (开启跟踪)
执行sql
alter session setevents ‘10046 trace name context off’; (关闭跟踪)
步骤4:找到跟踪后产生的文件
tkprof trc文件 目标文件 sys=no sort=prsela,exeela,fchela (格式化命令)
例子:SQL> set autot off SQL> alter session set statistics_level=typical; Session altered. SQL> alter session set events '10046 trace name context forever,level 12'; Session altered. SQL> select count(*) from test; COUNT(*) ---------- 7 SQL> alter session set events '10046 trace name context off'; Session altered. SQL> select d.value 2 || '/' 3 || LOWER (RTRIM(i.INSTANCE, CHR(0))) 4 || '_ora_' 5 || p.spid 6 || '.trc' trace_file_name 7 from (select p.spid 8 from v$mystat m,v$session s, v$process p 9 where m.statistic#=1 and s.sid=m.sid and p.addr=s.paddr) p, 10 (select t.INSTANCE 11 FROM v$thread t,v$parameter v 12 WHERE v.name='thread' 13 AND(v.VALUE=0 OR t.thread#=to_number(v.value))) i, 14 (select value 15 from v$parameter 16 where name='user_dump_dest') d; TRACE_FILE_NAME -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- /u01/app/oracle/diag/rdbms/ora12c/ora12c/trace/ora12c_ora_12195.trc SQL> host [oracle@ora12c ~]$ tkprof /u01/app/oracle/diag/rdbms/ora12c/ora12c/trace/ora12c_ora_12195.trc /home/oracle/10046.txt sys=no sort=prsela,exeela,fchela TKPROF: Release 12.1.0.1.0 - Development on Fri Jan 20 08:22:25 2017 Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved. SQL ID: 7b2twsn8vgfsc Plan Hash: 784602781 select count(*) from test call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 3 3 2 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 0.00 0.00 4 66 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.00 0.00 7 69 2 1 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 103 Number of plan statistics captured: 1 Rows (1st) Rows (avg) Rows (max) Row Source Operation ---------- ---------- ---------- --------------------------------------------------- 1 1 1 SORT AGGREGATE (cr=66 pr=4 pw=0 time=298 us) 7 7 7 PARTITION RANGE ALL PARTITION: 1 3 (cr=66 pr=4 pw=0 time=397 us cost=39 size=0 card=11) 7 7 7 TABLE ACCESS FULL TEST PARTITION: 1 3 (cr=66 pr=4 pw=0 time=290 us cost=39 size=0 card=11) Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ db file sequential read 4 0.00 0.00 SQL*Net message to client 2 0.00 0.00 db file scattered read 1 0.00 0.00 SQL*Net message from client 2 7.03 7.03
-
statistics_level=all
alter session setstatistics_level=all;
执行sql
select * fromtable(dbms_xplan.display_cursor(null,null,‘allstats last’)); -
dbms_xplan.display_cursor输入sql_id参数直接获取
select * fromtable(dbms_xplan.display_cursor(’&sq_id’)); #从共享池获取 -
awrsqlrpt.sql
@?/rdbms/admin/awrsqrpt.sql
选择你要的断点(begin snap 和end snap)
输入sql_id
Oracle 查看执行计划的几种方式
最新推荐文章于 2023-11-08 16:30:51 发布