Oracle根据SQL_ID批量收集真实执行计划

比眉伴天荒 2022-08-09 00:38 202阅读 0赞

日常检查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\]  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*