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博客

查看sql执行计划

最新文章