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 --
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/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/519536/viewspace-563050/