很久以前遇到的一个问题简单记录一下，之前一个数据库出现了很高的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 ======================='
-- Set multiple hints:
DECLARE
hints sys.sqlprof_attr := sys.sqlprof_attr(
('LEADING(@"SEL$1" "CO"@"SEL$1" "CH"@"SEL$1")')
, ('OPT_ESTIMATE(@"SEL$1", TABLE, "CU"@"SEL$1", SCALE_ROWS=100000)')
);
BEGIN
DBMS_SQLTUNE.IMPORT_SQL_PROFILE(sql_text=>:sql_fulltext, profile=> hints, name=> 'MANUAL_PROFILE_1ka5g0kh4h6pc');
END;
/
SQL PROFILE数据存在哪？
在字典表 sys.sqlobj$data, 如查看hint
SELECT
hint outline_hints
FROM (
SELECT p.name, p.signature, p.category, ROW_NUMBER()
OVER (PARTITION BY d.signature, d.category ORDER BY d.signature) row_num,
EXTRACTVALUE(VALUE(t), '/hint') hint
FROM
sys.sqlobj$data d
, dba_sql_profiles p,
TABLE(XMLSEQUENCE(EXTRACT(XMLTYPE(d.comp_data), '/outline_data/hint'))) t
WHERE
d.obj_type = 1
AND p.signature = d.signature
AND p.category = d.category
AND p.name LIKE ('&1'))
ORDER BY
name
, row_num
/
— enjoy —