日常检查AWR报告会发现一些慢的SQL语句,需要做性能调优,这个时候需要把内存中的真实执行计划和绑定变量的值弄出来。下面就是通用脚本:
sqlplus TEST/TEST@10.10.15.25
set serveroutput on size 100000
spool d:/result.txt
set pagesize 200
set linesize 800
declare
cursor c_cursor is select hash_value, child_number from gv$sql s
where s.SQL_ID in('c6yba8xcph0cb') order by hash_value;
c_row c_cursor%rowtype;
TYPE t_arry_plan IS VARRAY(1000) OF VARCHAR2(200);
array_plan t_arry_plan;
begin
DBMS_OUTPUT.ENABLE(buffer_size=>null);
for c_row in c_cursor loop
select plan_table_output bulk collect into array_plan from
table(dbms_xplan.display_cursor(c_row.hash_value, c_row.child_number, 'advanced'));
FOR i IN array_plan.FIRST .. array_plan.LAST LOOP
DBMS_OUTPUT.PUT_LINE(array_plan(i));
END LOOP;
DBMS_OUTPUT.PUT_LINE('**************************************************************************');
DBMS_OUTPUT.PUT_LINE(chr(10)||chr(10)||chr(10));
end loop;
end;
/
spool off;
出来的结果是:
SQL> declare
2 cursor c_cursor is select hash_value, child_number from v$sql s
3 where s.SQL_ID in('fbbm59qban13m','5n0t3uxw307bd') order by hash_value;
4 c_row c_cursor%rowtype;
5 TYPE t_arry_plan IS VARRAY(1000) OF VARCHAR2(200);
6 array_plan t_arry_plan;
7 begin
8 DBMS_OUTPUT.ENABLE(buffer_size=>null);
9 for c_row in c_cursor loop
10 select plan_table_output bulk collect into array_plan from
11 table(dbms_xplan.display_cursor(c_row.hash_value, c_row.child_number, 'advanced'));
12 FOR i IN array_plan.FIRST .. array_plan.LAST LOOP
13 DBMS_OUTPUT.PUT_LINE(array_plan(i));
14 END LOOP;
15 DBMS_OUTPUT.PUT_LINE('*****************************************************');
16 DBMS_OUTPUT.PUT_LINE(chr(10)||chr(10)||chr(10));
17 end loop;
18 end;
19 /
HASH_VALUE 2016419181, child number 0
--------------------------------------
delete from ind$ where bo#=:1
Plan hash value: 3511996411
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | | | 2 (100)| |
| 1 | DELETE | IND$ | | | | |
| 2 | TABLE ACCESS CLUSTER| IND$ | 2 | 20 | 2 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - DEL$1
2 - DEL$1 / IND$@DEL$1
3 - DEL$1 / IND$@DEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
OUTLINE_LEAF(@"DEL$1")
INDEX(@"DEL$1" "IND$"@"DEL$1" "I_OBJ#")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("BO#"=:1)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
2 - (cmp=2; cpy=3) "IND$".ROWID[ROWID,10], "BO#"[NUMBER,22],
"IND$"."OBJ#"[NUMBER,22]
3 - "IND$".ROWID[ROWID,10]
*****************************************************
HASH_VALUE 2016419181, child number 1
--------------------------------------
delete from ind$ where bo#=:1
Plan hash value: 3511996411
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | | | 2 (100)| |
| 1 | DELETE | IND$ | | | | |
| 2 | TABLE ACCESS CLUSTER| IND$ | 2 | 20 | 2 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - DEL$1
2 - DEL$1 / IND$@DEL$1
3 - DEL$1 / IND$@DEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
OUTLINE_LEAF(@"DEL$1")
INDEX(@"DEL$1" "IND$"@"DEL$1" "I_OBJ#")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("BO#"=:1)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
2 - (cmp=2; cpy=3) "IND$".ROWID[ROWID,10], "BO#"[NUMBER,22],
"IND$"."OBJ#"[NUMBER,22]
3 - "IND$".ROWID[ROWID,10]
*****************************************************
sqlplus TEST/TEST@10.10.15.25
set serveroutput on size 100000
spool d:/result.txt
set pagesize 200
set linesize 800
declare
cursor c_cursor is select hash_value, child_number from gv$sql s
where s.SQL_ID in('c6yba8xcph0cb') order by hash_value;
c_row c_cursor%rowtype;
TYPE t_arry_plan IS VARRAY(1000) OF VARCHAR2(200);
array_plan t_arry_plan;
begin
DBMS_OUTPUT.ENABLE(buffer_size=>null);
for c_row in c_cursor loop
select plan_table_output bulk collect into array_plan from
table(dbms_xplan.display_cursor(c_row.hash_value, c_row.child_number, 'advanced'));
FOR i IN array_plan.FIRST .. array_plan.LAST LOOP
DBMS_OUTPUT.PUT_LINE(array_plan(i));
END LOOP;
DBMS_OUTPUT.PUT_LINE('**************************************************************************');
DBMS_OUTPUT.PUT_LINE(chr(10)||chr(10)||chr(10));
end loop;
end;
/
spool off;
出来的结果是:
SQL> declare
2 cursor c_cursor is select hash_value, child_number from v$sql s
3 where s.SQL_ID in('fbbm59qban13m','5n0t3uxw307bd') order by hash_value;
4 c_row c_cursor%rowtype;
5 TYPE t_arry_plan IS VARRAY(1000) OF VARCHAR2(200);
6 array_plan t_arry_plan;
7 begin
8 DBMS_OUTPUT.ENABLE(buffer_size=>null);
9 for c_row in c_cursor loop
10 select plan_table_output bulk collect into array_plan from
11 table(dbms_xplan.display_cursor(c_row.hash_value, c_row.child_number, 'advanced'));
12 FOR i IN array_plan.FIRST .. array_plan.LAST LOOP
13 DBMS_OUTPUT.PUT_LINE(array_plan(i));
14 END LOOP;
15 DBMS_OUTPUT.PUT_LINE('*****************************************************');
16 DBMS_OUTPUT.PUT_LINE(chr(10)||chr(10)||chr(10));
17 end loop;
18 end;
19 /
HASH_VALUE 2016419181, child number 0
--------------------------------------
delete from ind$ where bo#=:1
Plan hash value: 3511996411
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | | | 2 (100)| |
| 1 | DELETE | IND$ | | | | |
| 2 | TABLE ACCESS CLUSTER| IND$ | 2 | 20 | 2 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - DEL$1
2 - DEL$1 / IND$@DEL$1
3 - DEL$1 / IND$@DEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
OUTLINE_LEAF(@"DEL$1")
INDEX(@"DEL$1" "IND$"@"DEL$1" "I_OBJ#")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("BO#"=:1)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
2 - (cmp=2; cpy=3) "IND$".ROWID[ROWID,10], "BO#"[NUMBER,22],
"IND$"."OBJ#"[NUMBER,22]
3 - "IND$".ROWID[ROWID,10]
*****************************************************
HASH_VALUE 2016419181, child number 1
--------------------------------------
delete from ind$ where bo#=:1
Plan hash value: 3511996411
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | | | 2 (100)| |
| 1 | DELETE | IND$ | | | | |
| 2 | TABLE ACCESS CLUSTER| IND$ | 2 | 20 | 2 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - DEL$1
2 - DEL$1 / IND$@DEL$1
3 - DEL$1 / IND$@DEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
OUTLINE_LEAF(@"DEL$1")
INDEX(@"DEL$1" "IND$"@"DEL$1" "I_OBJ#")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("BO#"=:1)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
2 - (cmp=2; cpy=3) "IND$".ROWID[ROWID,10], "BO#"[NUMBER,22],
"IND$"."OBJ#"[NUMBER,22]
3 - "IND$".ROWID[ROWID,10]
*****************************************************