【指令碼】通過hash_value直接獲得sql語句的執行計劃（9i-->10g過度）

secooler發表於2009-03-06
原文網址 : http://blog.itpub.net/519536/viewspace-563050/
指令碼SQL
1.先看一下我在9i環境下通過hash值獲取sql語句執行計劃的常用sql指令碼
ora10g@linux5 /home/oracle/sql$ cat plan.sql
set pagesize 0
set serveroutput on size 10000
col plan_table_output format a125
undefine hash_value
set verify off feedback off
var hash_value varchar2(20)
begin
  :hash_value := '&hash_value';
end;
/
insert into plan_table
      (statement_id,timestamp,operation,options,object_node,object_owner,object_name,
       optimizer,search_columns,id,parent_id,position,cost,cardinality,bytes,other_tag,
       partition_start,partition_stop,partition_id,other,distribution,
       cpu_cost,io_cost,temp_space,access_predicates,filter_predicates
      )
select distinct hash_value,sysdate,operation,options,object_node,object_owner,object_name,
       optimizer,search_columns,id,parent_id,position,cost,cardinality,bytes,other_tag,
       partition_start,partition_stop,partition_id,other,distribution,
       cpu_cost,io_cost,temp_space,access_predicates,filter_predicates
  from v$sql_plan
 where hash_value = :hash_value
/
col piece noprint
select distinct piece,sql_text from v$sqltext where hash_value = :hash_value order by piece
/
@?/rdbms/admin/utlxplp.sql
set verify on feedback on pagesize 1000

2.因為10g資料庫的執行計劃表與9i的表結構存在差異，因此需要調整10g plan_table表結構到9i模式
sys@ora10g> create table PLAN_TABLE_FOR_9I (
    statement_id    varchar2(30),
    timestamp       date,
    remarks         varchar2(80),
    operation       varchar2(30),
    options         varchar2(255),
    object_node     varchar2(128),
    object_owner    varchar2(30),
    object_name     varchar2(30),
    object_instance numeric,
    object_type     varchar2(30),
    optimizer       varchar2(255),
    search_columns  number,
    id              numeric,
    parent_id       numeric,
    position        numeric,
    cost            numeric,
    cardinality     numeric,
    bytes           numeric,
    other_tag       varchar2(255),
    partition_start varchar2(255),
    partition_stop  varchar2(255),
    partition_id    numeric,
    other           long,
    distribution    varchar2(30),
    cpu_cost        numeric,
    io_cost         numeric,
    temp_space      numeric,
    access_predicates varchar2(4000),
    filter_predicates varchar2(4000));

Table created.

sys@ora10g> create view plan_table as select * from plan_table_for_9i;

View created.

sys@ora10g> drop PUBLIC SYNONYM plan_table;

Synonym dropped.

sys@ora10g> create public synonym plan_table for sys.plan_table;

Synonym created.

sys@ora10g> GRANT ALL ON sys.plan_table TO public;

Grant succeeded.

sys@ora10g> select object_name,object_type,owner from dba_objects where object_name='PLAN_TABLE';

OBJECT_NAME                    OBJECT_TYPE         OWNER
------------------------------ ------------------- ------------------------------
PLAN_TABLE                     SYNONYM             PUBLIC

3.使用方法
sec@ora10g> select HASH_VALUE,sql_text from v$sql where sql_text like 'select * from emp%';

HASH_VALUE SQL_TEXT
---------- ----------------------------------------------------------------------
1893626834 select * from emp where empno=2000
1745700775 select * from emp

2 rows selected.

sec@ora10g> @plan
Enter value for hash_value: 1893626834
select * from emp where empno=2000

---------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)|
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |       |       |     1 (100)|
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    37 |     1   (0)|
|*  2 |   INDEX UNIQUE SCAN         | PK_EMP |     1 |       |     0   (0)|
---------------------------------------------------------------------------

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

   2 - access("EMPNO"=2000)

Note
-----
   - 'PLAN_TABLE' is old version
sec@ora10g> @plan
Enter value for hash_value: 1745700775
select * from emp

---------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)|
---------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     3 (100)|
|   1 |  TABLE ACCESS FULL| EMP  |    14 |   518 |     3   (0)|
---------------------------------------------------------------

Note
-----
   - 'PLAN_TABLE' is old version

-- The End --

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/519536/viewspace-563050/，如需轉載，請註明出處，否則將追究法律責任。

相關文章