前提是执行计划还在共享池中,没有被移除。
1.查看SQL的 HASH_VALUE
SQL> select HASH_VALUE,SQL_ID from v$sqlarea where SQL_ID='9y3dt1pbmwdb1';
HASH_VALUE SQL_ID
---------- -------------
1463694689 9y3dt1pbmwdb1
2.收集共享池中的执行计划:HASH_VALUE+子游标号。
display_cursor_9i.sql 脚本的使用:SQL_HASH_VALUE,子游标号0输入一次。
SQL> start display_cursor_9i 1463694689 0
old 6: from v$sql where hash_value=&1
new 6: from v$sql where hash_value=1463694689
HASH_VALUE CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS BUFFER_GETS GETS_PER_EXEC ROWS_PROCESSED ROWS_PER_EXEC DISK_READS READS_PER_EXEC CPU_TIME
---------- ------------ --------------- ---------- ----------- ------------- -------------- ------------- ---------- -------------- ----------
1463694689 0 3157290088 1 16 16 2 2 0 0 .006974
CPU_PER_EXEC ELAPSED_TIME ELA_PER_EXEC
------------ ------------ ------------
.006974 .006966 .006966
old 271: s_hash_value := &1;
new 271: s_hash_value := 1463694689;
old 272: s_child_num := &2;
new 272: s_child_num := 0;
HASH_VALUE: 1463694689 CHILD_NUMBER: 0
---------------------------------------------------------------------------------------------------------------------------------------------
select t1.id,t1.name from test.test01 t1,test.test03 t2 where t1.id=t2.id
Plan hash value: 3157290088
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 2 | 00:00:00.00 | 16 |
| 1 | NESTED LOOPS | | 1 | 2 | 2 | 00:00:00.00 | 16 |
| 2 | NESTED LOOPS | | 1 | 2 | 2 | 00:00:00.00 | 14 |
| 3 | TABLE ACCESS FULL | TEST03 | 1 | 2 | 2 | 00:00:00.00 | 7 |
| * 4 | INDEX RANGE SCAN | IDX_ID | 2 | 1 | 2 | 00:00:00.00 | 7 |
| 5 | TABLE ACCESS BY INDEX ROWID | TEST01 | 2 | 1 | 2 | 00:00:00.00 | 2 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T1"."ID"="T2"."ID")
PL/SQL procedure successfully completed.
可以看到SQL的执行计划:
3->4>2->5->1->0;
(1)3:TEST03全表扫描,作为驱动表。
(2) 4:TEST01表的索引范围扫描
(3)2:TEST03表的结果集和TEST01表的索引先做嵌套循环连接。得到TEST01中需要取值的ID和TEST03表的和TEST01 ID 相同的子集。
(4)5:根据上一步得到的ID,找到TEST01里面需要访问的数据。
(5)1:前面得到的TEST01的子集 和 TEST01根据ID取到的结果集做嵌套循环连接。得到TEST01里面和TEST03 ID 相同的记录
(6)0:从上面的结果集中取ID,NAME 列的值。
3.声明
上述SQL脚本由崔华老师提供。这里仅做演示。