首页 » ORACLE » How to force a hint without touch sql text? 手动SQL Profile固定执行计划

How to force a hint without touch sql text? 手动SQL Profile固定执行计划

很久以前遇到的一个问题简单记录一下,之前一个数据库出现了很高的I/O 负载,影响了全省的业务, 因为SQL 使用了错误的执行计划,当前修改app 重上线已来不及, 此时可以使用像sql profile,outline, sql baseline来指定个执行计划,以前写过11G 使用SQL baseline的,这篇使用sql profile.

SQL> @a 
A-Script: Display active sessions...

  COUNT(*) SQL_ID        STATE   EVENT
---------- ------------- ------- ----------------------------------------------------------------
         9 dyj8myphsk4h8 WAITING read by other session
         2 dyj8myphsk4h8 WAITING db file sequential read
         1 6632mq9xqtx5z WAITING db file scattered read
         1 00qjfhu5x4m9f WAITING db file sequential read
         1 6632mq9xqtx5z WAITING read by other session


SQL> @snapper ash,ash1=sid+sql_id 5 1 all
Sampling SID all with interval 5 seconds, taking 1 snapshots...

-- Session Snapper v4.11 BETA - by Tanel Poder ( http://blog.tanelpoder.com ) - Enjoy the Most Advanced Oracle Troubleshooting Script on the Planet! :)


----------------------------------------------------------------------------------------------------
Active% | INST | SQL_ID          | SQL_CHILD | EVENT                               | WAIT_CLASS
----------------------------------------------------------------------------------------------------
   744% |    1 | dyj8myphsk4h8   | 0         | read by other session               | User I/O
   176% |    1 | dyj8myphsk4h8   | 0         | db file sequential read             | User I/O
   165% |    1 | dyj8myphsk4h8   | 0         | ON CPU                              | ON CPU
    94% |    1 | 00qjfhu5x4m9f   | 0         | db file sequential read             | User I/O
    44% |    1 | 97sm3fbsyc50g   | 0         | ON CPU                              | ON CPU
    21% |    1 | 3y3qj19bmf39n   | 1         | ON CPU                              | ON CPU
    15% |    1 | dyj8myphsk4h8   | 0         | latch: cache buffers chains         | Concurrency
     6% |    1 | 00qjfhu5x4m9f   | 0         | ON CPU                              | ON CPU
     3% |    1 |                 |           | ON CPU                              | ON CPU
     3% |    1 | 4kq0cmj5t525v   | 0         | ON CPU                              | ON CPU

----------------------------------
Active% |    SID | SQL_ID
----------------------------------
   100% |    641 | 00qjfhu5x4m9f
   100% |   1010 | dyj8myphsk4h8
   100% |    243 | dyj8myphsk4h8
   100% |    546 | dyj8myphsk4h8
   100% |    643 | dyj8myphsk4h8
   100% |    259 | dyj8myphsk4h8
   100% |    357 | dyj8myphsk4h8
   100% |   1086 | dyj8myphsk4h8
   100% |    684 | dyj8myphsk4h8
   100% |    227 | dyj8myphsk4h8

SQL> @xi dyj8myphsk4h8 %
eXplain the execution plan for sqlid dyj8myphsk4h8 child %...

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  dyj8myphsk4h8, child number 0
-------------------------------------
SELECT PROCESSINSTANCEID,ACTIVITYDEFINITIONID,ACTIVITYINSTANCEID,NAME,ST
ATE,STARTEDDATE,COMPLETEDDATE,DUEDATE,PRIORITY,ITEMCOMPLETED,ITEMSUM,TAC
HE_ID,SYN_MESSAGE,DIRECTION,WORKITEMID,ATOM_ACTIVITYINSTANCE_ID,ATOM_ACT
IVITYDEFINITIONID FROM UOS_ACTIVITYINSTANCE WHERE PROCESSINSTANCEID=:1
AND STATE IN (0,1,2)  ORDER BY ACTIVITYINSTANCEID

Plan hash value: 4220788823

------------------------------------------------------------------------
| Id  | Operation                   | Name                    | E-Rows |
------------------------------------------------------------------------
|*  1 |  TABLE ACCESS BY INDEX ROWID| UOS_ACTIVITYINSTANCE    |  12750 |
|   2 |   INDEX FULL SCAN           | PK_UOS_ACTIVITYINSTANCE |    127M|
------------------------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
   1 - (NUMBER): 90970928
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(("PROCESSINSTANCEID"=:1 AND INTERNAL_FUNCTION("STATE")))

TIP:
因为我们在view的外层order by 了pk列,CBO 选择使用了FULL SCAN pk index,(可能是统计信息有问题,这里要求不让收集统计信息让人无奈,所以能看到动态采样), 下面我们手动hint index一个相对正确的索引对比一下。

SQL> set autot trace
SQL> var v1 number;
SQL> exec :v1:=90958189;
SQL> SELECT /*+index(t IDX_ACTINST_PROINSTID)*/
  2  PROCESSINSTANCEID, ACTIVITYDEFINITIONID,ACTIVITYINSTANCEID,NAME,STATE,STARTEDDATE,COMPLETEDDATE,DUEDATE,PRIORITY,ITEMCOMPLETED,ITEMSUM,TACHE_ID,SYN_MESSAGE,DIRECTION,WORKITEMID,ATOM_ACTIVITYINSTANCE_ID,ATOM_ACTIVITYDEFINITIONID FROM iom.UOS_ACTIVITYINSTANCE t WHERE PROCESSINSTANCEID=:v1
  3  AND STATE IN (0,1,2)  ORDER BY ACTIVITYINSTANCEID;

Execution Plan
----------------------------------------------------------
Plan hash value: 2650077656

------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                       |   250 | 65250 |     6  (17)| 00:00:01 |
|   1 |  SORT ORDER BY               |                       |   250 | 65250 |     6  (17)| 00:00:01 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| UOS_ACTIVITYINSTANCE  |   250 | 65250 |     5   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IDX_ACTINST_PROINSTID |   509K|       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("STATE"=0 OR "STATE"=1 OR "STATE"=2)
   3 - access("PROCESSINSTANCEID"=TO_NUMBER(:V1))
Note
-----
   - dynamic sampling used for this statement
Statistics
----------------------------------------------------------
          7  recursive calls
          0  db block gets
        112  consistent gets
         46  physical reads
          0  redo size
       1726  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

TIP:
可以看到一致读还是蛮小的,下面我们测试一下如何使用sql profile来不修改SQL的情况下强制使用指定索引

SQL> set autot trace 

SQL> SELECT /*weejar*/
  2  PROCESSINSTANCEID, ACTIVITYDEFINITIONID,ACTIVITYINSTANCEID,NAME,STATE,STARTEDDATE,COMPLETEDDATE,DUEDATE,PRIORITY,ITEMCOMPLETED,ITEMSUM,TACHE_ID,SYN_MESSAGE,DIRECTION,WORKITEMID,ATOM_ACTIVITYINSTANCE_ID,ATOM_ACTIVITYDEFINITIONID FROM iom.UOS_ACTIVITYINSTANCE t WHERE PROCESSINSTANCEID=90958189  
  3  AND STATE IN (0,1,2)  ORDER BY ACTIVITYINSTANCEID
  4  ;
  
 -- wait 5 minutes, no output, ctrl +c  to cancel of current operation
 

SQL> SELECT /*+ index(t IDX_ACTINST_PROINSTID)*/
  2  PROCESSINSTANCEID, ACTIVITYDEFINITIONID,ACTIVITYINSTANCEID,NAME,STATE,STARTEDDATE,COMPLETEDDATE,DUEDATE,PRIORITY,ITEMCOMPLETED,ITEMSUM,TACHE_ID,SYN_MESSAGE,DIRECTION,WORKITEMID,ATOM_ACTIVITYINSTANCE_ID,ATOM_ACTIVITYDEFINITIONID FROM iom.UOS_ACTIVITYINSTANCE t WHERE PROCESSINSTANCEID=90958189  
  3  AND STATE IN (0,1,2)  ORDER BY ACTIVITYINSTANCEID;


Execution Plan
----------------------------------------------------------
Plan hash value: 2650077656

--------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                       | 12750 |  3249K|       |  8326   (1)| 00:01:40 |
|   1 |  SORT ORDER BY               |                       | 12750 |  3249K|  7576K|  8326   (1)| 00:01:40 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| UOS_ACTIVITYINSTANCE  | 12750 |  3249K|       |  7603   (1)| 00:01:32 |
|*  3 |    INDEX RANGE SCAN          | IDX_ACTINST_PROINSTID | 25026 |       |       |    52   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("STATE"=0 OR "STATE"=1 OR "STATE"=2)
   3 - access("PROCESSINSTANCEID"=90958189)

下面手动来创建一个sql profile

SQL> @sqlt weejar

HASH_VALUE SQL_ID             CHLD# OPT_MODE   SQL_TEXT
---------- ------------- ---------- ---------- ----------------------------------------------------------------------------------------------------
2630103662 6ctm9vkfc8cmf          0 ALL_ROWS   select  hash_value,     sql_id, -- old_hash_value,  child_number chld#, -- plan_hash_value
                                               plan_hash,  optimizer_mode opt_mode,  sql_text sqlt_sql_text from  v$sql where  lower(sql_text) like
                                               lower('%weejar%') --and hash_value != (select sql_hash_value from v$session where sid = (select sid
                                               from v$mystat where rownum = 1))
2409925098 fwbyjka7u92ga          0 ALL_ROWS   SELECT /*weejar*/ PROCESSINSTANCEID,



SQL> @xi fwbyjka7u92ga  %
eXplain the execution plan for sqlid fwbyjka7u92ga child %...

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  fwbyjka7u92ga, child number 0
-------------------------------------
SELECT /*weejar*/ PROCESSINSTANCEID,
ACTIVITYDEFINITIONID,ACTIVITYINSTANCEID,NAME,STATE,STARTEDDATE,COMPLETED
DATE,DUEDATE,PRIORITY,ITEMCOMPLETED,ITEMSUM,TACHE_ID,SYN_MESSAGE,DIRECTI
ON,WORKITEMID,ATOM_ACTIVITYINSTANCE_ID,ATOM_ACTIVITYDEFINITIONID FROM
iom.UOS_ACTIVITYINSTANCE t WHERE PROCESSINSTANCEID=90958189 AND STATE
IN (0,1,2)  ORDER BY ACTIVITYINSTANCEID

Plan hash value: 4220788823
------------------------------------------------------------------------
| Id  | Operation                   | Name                    | E-Rows |
------------------------------------------------------------------------
|*  1 |  TABLE ACCESS BY INDEX ROWID| UOS_ACTIVITYINSTANCE    |  12750 |
|   2 |   INDEX FULL SCAN           | PK_UOS_ACTIVITYINSTANCE |    127M|
------------------------------------------------------------------------

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

   1 - filter(("PROCESSINSTANCEID"=90958189 AND
              INTERNAL_FUNCTION("STATE")))

SET verify off
colu operation format A20
colu options format A25
colu object_name format A20
colu object_alias format A20
SQL> SELECT operation,options,object_name,object_alias
     FROM v$sql_plan
     WHERE sql_id='&sqlid'
     AND child_number='&cn'
     /
Enter value for sqlid: fwbyjka7u92ga
Enter value for cn: 0

OPERATION                             OPTIONS         OBJECT_NAME                    OBJECT_ALIAS
------------------------------------- --------------- ------------------------------ ---------------
SELECT STATEMENT
TABLE ACCESS                          BY INDEX ROWID  UOS_ACTIVITYINSTANCE           T@SEL$1
INDEX                                 FULL SCAN       PK_UOS_ACTIVITYINSTANCE        T@SEL$1

SQL> DECLARE
  2   SQL_FTEXT CLOB;
  3  BEGIN
  4  SELECT SQL_FULLTEXT INTO SQL_FTEXT FROM V$SQLAREA WHERE SQL_ID = 'fwbyjka7u92ga';
  5  
  6  DBMS_SQLTUNE.IMPORT_SQL_PROFILE(
  7    SQL_TEXT => SQL_FTEXT,
  8    PROFILE => SQLPROF_ATTR('INDEX(@"SEL$1" "T"@"SEL$1" "IDX_ACTINST_PROINSTID")'),
  9    NAME => 'PROFILE_fwbyjka7u92ga',
 10    REPLACE => TRUE,
 11    FORCE_MATCH => TRUE
 12  );
 13  END;
 14  /
PL/SQL procedure successfully completed.


SQL> col sql_text for a40 trunc
SQL> select name,sql_text,status from dba_sql_profiles;

NAME                           SQL_TEXT                                 STATUS
------------------------------ ---------------------------------------- --------
fixed_com_count(1)             SELECT COUNT(ID) FROM INF_MSG A WHERE (A ENABLED
PROFILE_fwbyjka7u92ga          SELECT /*weejar*/                        ENABLED   <<< ... SQL> SELECT /*weejar*/
  2  PROCESSINSTANCEID, ACTIVITYDEFINITIONID,ACTIVITYINSTANCEID,NAME,STATE,STARTEDDATE,COMPLETEDDATE,DUEDATE,PRIORITY,ITEMCOMPLETED,ITEMSUM,TACHE_ID,
  SYN_MESSAGE,DIRECTION,WORKITEMID,ATOM_ACTIVITYINSTANCE_ID,ATOM_ACTIVITYDEFINITIONID 
  FROM iom.UOS_ACTIVITYINSTANCE t WHERE PROCESSINSTANCEID=90958189  
  3  AND STATE IN (0,1,2)  ORDER BY ACTIVITYINSTANCEID;


Execution Plan
----------------------------------------------------------
Plan hash value: 2650077656

--------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                       | 12750 |  3249K|       |  8326   (1)| 00:01:40 |
|   1 |  SORT ORDER BY               |                       | 12750 |  3249K|  7576K|  8326   (1)| 00:01:40 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| UOS_ACTIVITYINSTANCE  | 12750 |  3249K|       |  7603   (1)| 00:01:32 |
|*  3 |    INDEX RANGE SCAN          | IDX_ACTINST_PROINSTID | 25026 |       |       |    52   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------

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

   2 - filter("STATE"=0 OR "STATE"=1 OR "STATE"=2)
   3 - access("PROCESSINSTANCEID"=90958189)

Note
-----
   - dynamic sampling used for this statement
   - SQL profile "PROFILE_fwbyjka7u92ga" used for this statement

TIP:
上面已手动给SQL指定了HINT Index,也可能用尝试用dbms_sqltune包创建SQL Profile

 
var tuning_task varchar2(100);  
DECLARE  
   l_sql_id v$session.prev_sql_id%TYPE;  
   l_tuning_task VARCHAR2(30);  
 BEGIN  
   l_sql_id:='dyj8myphsk4h8';  
   l_tuning_task := dbms_sqltune.create_tuning_task(sql_id => l_sql_id);  
   :tuning_task:=l_tuning_task;  
   dbms_sqltune.execute_tuning_task(l_tuning_task);  
   dbms_output.put_line(l_tuning_task);  
 END;  
 /  

上面的方法可以很好的自定议,如果数据库里有一个好的执行计划和一个坏的执行计划,参考好的绑定坏的,除了使用COE脚本,也可以使用解析V$SQL_PLAN的OTHER_XML来生成SQL PROFILE.

SQL>@create_profile_code.sql
Enter good SQL ID:- 33fndgzsas09k
Enter child number of good SQL:- 0
Enter bad SQL ID to be fixed:- 33fndgzsas09k
Enter bad SQL plan_hash_value:- 3225275398
...
...
...


-- file: create_profile_code.sql --
-- purpose: output a profile from a good sql cursor, to fix another bad sql cursor with sql profile.
-- 
accept HINTED_SQL_ID prompt 'Enter good SQL ID:- '
accept CHILD_NO prompt 'Enter child number of good SQL:- '
accept BAD_SQL_ID prompt 'Enter bad SQL ID to be fixed:- '
accept PLAN_HASH_VALUE prompt 'Enter bad SQL plan_hash_value:- '
set pagesize 0
set line 9999
set verify off;
set heading off;
set feedback off;
set echo off;
set pagesize 0
prompt '======================= OUTPUT ======================='
select CHR(10) from dual;
select  'declare'
        ||CHR(10)||CHR(9)
        ||'ar_profile_hints sys.sqlprof_attr;'
        ||CHR(10)
        ||'begin'||CHR(10)||CHR(9)
        ||'ar_profile_hints := sys.sqlprof_attr('||CHR(10)||CHR(9)
        ||'''BEGIN_OUTLINE_DATA'','
from    dual;
select  CHR(9)||''''
        ||regexp_replace(extractvalue(value(d),'/hint'),'''','''''')
        || ''','
from    xmltable('/*/outline_data/hint'
                passing (select     xmltype(other_xml) as xmlval
                        from        v$sql_plan
                        where       sql_id = '33fndgzsas09k'
                        and         CHILD_NUMBER = 0
                        and         other_xml is not null)) d;
select  CHR(9)
        ||'''END_OUTLINE_DATA'');'||CHR(10)||CHR(9)
        ||'for sql_rec in ('||CHR(10)||CHR(9)
        ||'select t.sql_id, t.sql_text'||CHR(10)||CHR(9)
        ||'from dba_hist_sqltext t, dba_hist_sql_plan p'||CHR(10)||CHR(9)
        ||'where t.sql_id = p.sql_id'||CHR(10)||CHR(9)
        ||'and p.sql_id = '''||'&BAD_SQL_ID'||'''' ||CHR(10)||CHR(9)
        ||'and p.plan_hash_value = '||&PLAN_HASH_VALUE ||CHR(10)||CHR(9)
        ||'and p.parent_id is null'||CHR(10)||') loop' ||CHR(10)
        ||'DBMS_SQLTUNE.IMPORT_SQL_PROFILE(' ||CHR(10)||CHR(9)
        ||'sql_text    => sql_rec.sql_text,'||CHR(10)||CHR(9)
        ||'profile     => ar_profile_hints,' ||CHR(10)||CHR(9)
        ||'name        => ''PROFILE_'||'&BAD_SQL_ID'||'''); '||CHR(10)||CHR(9)
        || 'end loop;'||CHR(10)|| 'end;'|| CHR(10)||'/'
from    dual;
select CHR(10) from dual;
prompt '======================= OUTPUT ======================='

— enjoy —

打赏

对不起,这篇文章暂时关闭评论。