dbms_xplan.display_cursor oracle 10g查看执行计划

查看已执行过SQL的执行计划

先抓一个最近一小时最消耗IO的SQL

SELECT sql_id, COUNT(*)
FROM gv$active_session_history ash, gv$event_name evt
WHERE ash.sample_time > SYSDATE - 1/24
AND ash.session_state = 'WAITING'
AND ash.event_id = evt.event_id
AND evt.wait_class = 'User I/O'
GROUP BY sql_id
ORDER BY COUNT(*) DESC;

查看SQL执行计划

SELECT * FROM TABLE(dbms_xplan.display_cursor('51f3uqkpv1fja'));

SQL_ID  51f3uqkpv1fja, child number 0
-------------------------------------
SELECT T1.NAME GRADE_0,        T1.NAME GRADE_1,        :"SYS_B_0" GRADE_2,        :"SYS_B_1" 
GRADE_3,        :"SYS_B_2" GRADE_4,        :"SYS_B_3" GRADE_5,        :"SYS_B_4" GRADE_6,    
    :"SYS_B_5" GRADE_7,        :"SYS_B_6" GRADE_8,        T1.ABBREV ABBRNAME,        
T1.ADDITIONALINFO,        T1.COVERAGE,        T1.GISSITEID,        T1.NONGISSITEID,        
T1.ALIAS,        T1.SERVICEGRADE,        T1.COMMENTS,        T1.ID,        T1.REGIONCODE,    
    T1.Longitude,        T1.Latitude,        T1.Mapitemid,        T1.Importtime,        
:"SYS_B_7" IMPFLAG,        T1.STATUS,        T1.ISAUTOCONFIGURE,        T1.ADSLBANDWIDTH,    
    T1.LANBANDWIDTH,        T1.FTTHBANDWIDTH   FROM BIZC_STANDARDADDRESS t1   start with 
t1.id = :"SYS_B_8"  connect by   T1.ID= T1.PARENTADDRESSID
 
Plan hash value: 3601985599
 
-----------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                      |       |       |  4565 (100)|          |
|   1 |  CONNECT BY WITHOUT FILTERING|                      |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| BIZC_STANDARDADDRESS |       |       |            |          |
|*  3 |    INDEX UNIQUE SCAN         | SYS_C00139211        |     1 |     8 |     2   (0)| 00:00:01 |
|   4 |   TABLE ACCESS FULL          | BIZC_STANDARDADDRESS |   778K|    92M|  4565   (1)| 00:00:55 |
-----------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("T1"."ID"=:SYS_B_8)