【指令碼】通過hash_value直接獲得sql語句的執行計劃(9i-->10g過度)
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/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 指令碼:獲得現有語句的執行計劃指令碼
- 通過分析SQL語句的執行計劃優化SQL語句SQL優化
- 【AWR】通過AWR報告中記錄的 SQL Id獲得SQL語句的執行計劃SQL
- 通過分析SQL語句的執行計劃優化SQL 二SQL優化
- 根據SQL Id獲得SQL語句的執行計劃SQL
- 通過等待事件來獲得查詢SQl的執行計劃事件SQL
- 獲得目標SQL語句執行計劃的方法SQL
- 通過分析SQL語句的執行計劃優化SQL(總結)SQL優化
- 使用dbms_xplan包來獲得sql語句的執行計劃SQL
- 【檢視】使用V$SQL_PLAN檢視獲取曾經執行過的SQL語句執行計劃SQL
- 透過分析SQL語句的執行計劃最佳化SQL(總結)SQL
- MySQL——通過EXPLAIN分析SQL的執行計劃MySqlAI
- 通過shell指令碼來得到不穩定的執行計劃指令碼
- 清除SQL語句的執行計劃SQL
- [轉]透過分析SQL語句的執行計劃最佳化SQL(總結)SQL
- CoreData執行過程的sql語句SQL
- 剖析SQL語句的執行過程SQL
- Oracle SQL 語句的執行過程OracleSQL
- mysql執行sql語句過程MySql
- 獲取oracle sql語句詳細些執行計劃OracleSQL
- 通過日誌檢視mysql正在執行的SQL語句MySql
- oracle自定義過程來獲得完整的sql語句OracleSQL
- 通過spid,查詢執行慢的sql指令碼SQL指令碼
- 一條sql語句的執行過程SQL
- 淺談SQL語句的執行過程SQL
- 理解oracle執行sql語句的過程OracleSQL
- 通過shell指令碼監控sql執行頻率指令碼SQL
- SQL語句執行過程詳解SQL
- sql語句執行過程小結SQL
- 獲取oracle正在處於等待狀態的sql語句的執行計劃的語句OracleSQL
- 通過sql語句分析足彩SQL
- Mybatis原始碼分析(五)探究SQL語句的執行過程MyBatis原始碼SQL
- 使用 EXPLAIN PLAN 獲取SQL語句執行計劃 (R0.1)AISQL
- 查詢Oracle正在執行和執行過的SQL語句OracleSQL
- 10_SQL語句執行過程剖析SQL
- ORACLE sql 語句的執行過程(SQL效能調整)OracleSQL
- 通過 Redis 定時執行指令碼Redis指令碼
- 通過執行計劃中的CONCATENATION分析sql問題SQL