查看sql执行计划

OS:Oracle Linux Server release 6.4 64
数据库：oracle10.2.0.1.0 64bit
1、首先查询scott用户下的对象信息
SQL> select object_name from target_test where owner='SCOTT';
2、查看执行SQL的sql_id
SQL> select sql_id,sql_text from v$sql where sql_text like '%select object_name from target_test%';
SQL_ID
-------------
SQL_TEXT
--------------------------------------------------------------------------------
8tdzkb039ktpu
select sql_id,sql_text from v$sql where sql_text like '%select object_name from
target_test%'

02yvzm3xykx25
select object_name from target_test where owner='SCOTT'
3、查看sql_sid
SQL>  SELECT SQL_ID,CHILD_NUMBER  FROM V$SQL  WHERE SQL_TEXT LIKE '%select object_name from target_test%';


SQL_ID        CHILD_NUMBER
------------- ------------
bkf4khtnj9dun            0
8tdzkb039ktpu            0
02yvzm3xykx25            0

4、查看该语句执行计划
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('02yvzm3xykx25',0));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  02yvzm3xykx25, child number 0
-------------------------------------
select object_name from target_test where owner='SCOTT'

Plan hash value: 2764412825

--------------------------------------------------------------------------------
---------------------------------


| Id  | Operation            | Name        | Rows  | Bytes | Cost (%CPU)| Time
   |    TQ  |IN-OUT| PQ Distrib |


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------


--------------------------------------------------------------------------------
---------------------------------


|   0 | SELECT STATEMENT     |             |       |       | 38722 (100)|
   |        |      |            |


|   1 |  PX COORDINATOR      |             |       |       |            |
   |        |      |            |


|   2 |   PX SEND QC (RANDOM)| :TQ10000    | 13993 |  1134K| 38722   (1)| 00:07:


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
45 |  Q1,00 | P->S | QC (RAND)  |


|   3 |    PX BLOCK ITERATOR |             | 13993 |  1134K| 38722   (1)| 00:07:
45 |  Q1,00 | PCWC |            |


|*  4 |     TABLE ACCESS FULL| TARGET_TEST | 13993 |  1134K| 38722   (1)| 00:07:
45 |  Q1,00 | PCWP |            |

-------------------------------------------------------------------------------
---------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access(:Z>=:Z AND :Z<=:Z)
       filter("OWNER"='SCOTT')

Note
-----
   - dynamic sampling used for this statement

26 rows selected.
SQL> 

5、如果提示：
NOTE: cannot fetch plan for SQL_ID: 9babjv8yq8ru3, CHILD_NUMBER: 0
      Please verify value of SQL_ID and CHILD_NUMBER;
      It could also be that the plan is no longer in cursor cache (check v$sql_p
lan)

执行 set serveroutput off，然后再执行就可以了

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31414383/viewspace-2132838/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31414383/viewspace-2132838/