dbms_xplan.display_cursor oracle 10g查看執行計畫__oracle

查看已執行過SQL的執行計畫

先抓一個最近一小時最消耗IO的SQL

SELECT sql_id, COUNT(*)FROM gv$active_session_history ash, gv$event_name evtWHERE ash.sample_time > SYSDATE - 1/24AND ash.session_state = 'WAITING'AND ash.event_id = evt.event_idAND evt.wait_class = 'User I/O'GROUP BY sql_idORDER 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)