--环境构造 --研究Nested Loops Join访问次数前准备工作 DROP TABLE t1 CASCADE CONSTRAINTS PURGE; DROP TABLE t2 CASCADE CONSTRAINTS PURGE; CREATE TABLE t1 ( id NUMBER NOT NULL, n NUMBER, contents VARCHAR2(4000) ) ; CREATE TABLE t2 ( id NUMBER NOT NULL, t1_id NUMBER NOT NULL, n NUMBER, contents VARCHAR2(4000) ) ; execute dbms_random.seed(0); INSERT INTO t1 SELECT rownum, rownum, dbms_random.string('a', 50) FROM dual CONNECT BY level <= 1000 ORDER BY dbms_random.random; INSERT INTO t2 SELECT rownum, rownum, rownum, dbms_random.string('b', 50) FROM dual CONNECT BY level <= 100000 ORDER BY dbms_random.random; COMMIT; CREATE INDEX t1_n ON t1 (n); CREATE INDEX t2_t1_id ON t2(t1_id); /* 下面我们将会用多种方法来查看如下语句的执行计划 SELECT * FROM t1, t2 WHERE t1.id = t2.t1_id AND t1.n in(18,19);
*/
----方法1(explain plan for 的方式。类似PLSQL DEVELOPE里的F5)
/* 步骤1:explain plan for "你的SQL" 步骤2:select * from table(dbms_xplan.display()); */
set linesize 1000 set pagesize 2000 explain plan for SELECT * FROM t1, t2 WHERE t1.id = t2.t1_id AND t1.n in(18,19); select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------- Plan hash value: 3532430033 ------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2 | 8138 | 6 (0)| 00:00:01 | | 1 | NESTED LOOPS | | | | | | | 2 | NESTED LOOPS | | 2 | 8138 | 6 (0)| 00:00:01 | | 3 | INLIST ITERATOR | | | | | | | 4 | TABLE ACCESS BY INDEX ROWID| T1 | 2 | 4056 | 2 (0)| 00:00:01 | |* 5 | INDEX RANGE SCAN | T1_N | 1 | | 1 (0)| 00:00:01 | |* 6 | INDEX RANGE SCAN | T2_T1_ID | 1 | | 1 (0)| 00:00:01 | | 7 | TABLE ACCESS BY INDEX ROWID | T2 | 1 | 2041 | 2 (0)| 00:00:01 | ------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 5 - access("T1"."N"=18 OR "T1"."N"=19) 6 - access("T1"."ID"="T2"."T1_ID") Note ----- - dynamic sampling used for this statement (level=2)
----方法2(set autotrace on 方式) /* 步骤1:set autotrace on 步骤2:在此处执行你的SQL即可,后续自然会有结果输出
另,有如下几种方式: set autotrace on (得到执行计划,输出运行结果) set autotrace traceonly (得到执行计划,不输出运行结果) set autotrace traceonly explain (得到执行计划,不输出运行结果和统计信息部分,仅展现执行计划部分) set autotrace traceonl statistics(不输出运行结果和执行计划部分,仅展现统计信息部分) */ set autotrace on SELECT * FROM t1, t2 WHERE t1.id = t2.t1_id AND t1.n in(18,19);
执行计划 ---------------------------------------------------------- Plan hash value: 3532430033 ------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2 | 8138 | 6 (0)| 00:00:01 | | 1 | NESTED LOOPS | | | | | | | 2 | NESTED LOOPS | | 2 | 8138 | 6 (0)| 00:00:01 | | 3 | INLIST ITERATOR | | | | | | | 4 | TABLE ACCESS BY INDEX ROWID| T1 | 2 | 4056 | 2 (0)| 00:00:01 | |* 5 | INDEX RANGE SCAN | T1_N | 1 | | 1 (0)| 00:00:01 | |* 6 | INDEX RANGE SCAN | T2_T1_ID | 1 | | 1 (0)| 00:00:01 | | 7 | TABLE ACCESS BY INDEX ROWID | T2 | 1 | 2041 | 2 (0)| 00:00:01 | ------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 5 - access("T1"."N"=18 OR "T1"."N"=19) 6 - access("T1"."ID"="T2"."T1_ID") Note ----- - dynamic sampling used for this statement (level=2) 统计信息 ---------------------------------------------------------- 0 recursive calls 0 db block gets 12 consistent gets 0 physical reads 0 redo size 1032 bytes sent via SQL*Net to client 416 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 2 rows processed
*/ set autotrace off alter session set statistics_level=all ; SELECT * FROM t1, t2 WHERE t1.id = t2.t1_id AND t1.n in(18,19); select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------- SQL_ID 1a914ws3ggfsn, child number 0 ------------------------------------- SELECT * FROM t1, t2 WHERE t1.id = t2.t1_id AND t1.n in(18,19)
步骤1: select * from table(dbms_xplan.display_cursor('&sq_id')); (该方法是从共享池里得到)
注: 1. 还有一个方法,select * from table(dbms_xplan.display_awr('&sq_id'));(这是awr性能视图里获取到的) 2. 如果有多执行计划,可以用类似方法查出 select * from table(dbms_xplan.display_cursor('cyzznbykb509s',0)); select * from table(dbms_xplan.display_cursor('cyzznbykb509s',1));
*/
select * from table(dbms_xplan.display_cursor('1a914ws3ggfsn')); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------- SQL_ID 1a914ws3ggfsn, child number 0 ------------------------------------- SELECT * FROM t1, t2 WHERE t1.id = t2.t1_id AND t1.n in(18,19)
步骤1:alter session set events '10046 trace name context forever,level 12'; (开启跟踪) 步骤2:执行你的语句 步骤3:alter session set events '10046 trace name context off'; (关闭跟踪) 步骤4:找到跟踪后产生的文件 步骤5:tkprof trc文件 目标文件 sys=no sort=prsela,exeela,fchela (格式化命令)
*/ set autotace off alter session set statistics_level=typical; alter session set events '10046 trace name context forever,level 12';
SELECT * FROM t1, t2 WHERE t1.id = t2.t1_id AND t1.n in(18,19);
alter session set events '10046 trace name context off'; select d.value || '/' || LOWER (RTRIM(i.INSTANCE, CHR(0))) || '_ora_' || p.spid || '.trc' trace_file_name from (select p.spid from v$mystat m,v$session s, v$process p where m.statistic#=1 and s.sid=m.sid and p.addr=s.paddr) p, (select t.INSTANCE FROM v$thread t,v$parameter v WHERE v.name='thread' AND(v.VALUE=0 OR t.thread#=to_number(v.value))) i, (select value from v$parameter where name='user_dump_dest') d;
Misses in library cache during parse: 0 Optimizer mode: ALL_ROWS Parsing user id: 94
Rows Row Source Operation ------- --------------------------------------------------- 2 NESTED LOOPS (cr=12 pr=0 pw=0 time=0 us) 2 NESTED LOOPS (cr=10 pr=0 pw=0 time=48 us cost=6 size=8138 card=2) 2 INLIST ITERATOR (cr=5 pr=0 pw=0 time=16 us) 2 TABLE ACCESS BY INDEX ROWID T1 (cr=5 pr=0 pw=0 time=0 us cost=2 size=4056 card=2) 2 INDEX RANGE SCAN T1_N (cr=3 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 108621) 2 INDEX RANGE SCAN T2_T1_ID (cr=5 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 108622) 2 TABLE ACCESS BY INDEX ROWID T2 (cr=2 pr=0 pw=0 time=0 us cost=2 size=2041 card=1)
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 1.31 1.31