----
Fix Oracle Stream AQ high CPU SQL. Tune SQL plan without change the source code(SQL text),
Story
-----
We are running 2 way master-master stream replication. One working database used 80~90% CPU,
The TOP SQL is related to Stream AQ process on table STRMADMIN.AQ$_CAPTURE_QUEUE_TABLE_C.
Normally, there are less than 100 rows in this busy table.
It is an IOT table, the data grows and then deletes, that creates lots of bee hive empty space in the index data block.
The CBO choosed FAST FULL SCAN for primary key leading portion predicate query.
Index range scan would be much better than FAST FULL SCAN.
There could be other solutions available too, e.g.
- Adjust the table and index statistics.
- Regularly compact and rebuild the IOT.
Note
----
We might not need to prepare binding variable for targeted SQL plan. I will test it later.
Ref
---
A:\SQL\CBO\tune_sql_by_baseline01.sql
| Modification History:
| Date Who What
| 18-Nov-2009: Charlie(Yi): Show binding variable value,
| 11-Nov-2009: Charlie(Yi): Create the file,
Solution
--------
* tune SQL execution plan by SQL plan baseline, load_plans_from_cursor_cache to
load the hint/changed resulting plan(s) into the SQL plan baseline of the original SQL statement,
and then remove old baseline,
After fixed the SQL, we moved the baseline to another replication database through dbms_spm.pack_stgtab_baseline,
-- Original SQL
SELECT enq_tid
FROM STRMADMIN.AQ$_CAPTURE_QUEUE_TABLE_C ctiot
WHERE enq_tid IN
(SELECT msg_enq_tid
FROM STRMADMIN.AQ$_CAPTURE_QUEUE_TABLE_I qidx1
WHERE qidx1.subscriber# = :1
AND qidx1.name = :2
AND qidx1.queue# = :3
)
AND ctiot.cscn >= :4
AND ctiot.cscn < :5
order by cscn,enq_tid;
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4 (100)| |
| 1 | SORT ORDER BY | | 1 | 91 | 4 (50)| 00:00:01 |
|* 2 | FILTER | | | | | |
| 3 | NESTED LOOPS | | 1 | 91 | 3 (34)| 00:00:01 |
| 4 | SORT UNIQUE | | 1 | 60 | 2 (0)| 00:00:01 |
|* 5 | INDEX FAST FULL SCAN| SYS_IOT_TOP_56264 | 1 | 60 | 2 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | AQ$_CAPTURE_QUEUE_TABLE_Y | 1 | 31 | 0 (0)| |
------------------------------------------------------------------------------------------------------
-- Hinted SQL
SELECT enq_tid
FROM STRMADMIN.AQ$_CAPTURE_QUEUE_TABLE_C ctiot
WHERE enq_tid IN
(SELECT --+ index(qidx1)
msg_enq_tid
FROM STRMADMIN.AQ$_CAPTURE_QUEUE_TABLE_I qidx1
WHERE qidx1.subscriber# = 2
AND qidx1.name = '0'
AND qidx1.queue# = 0
)
AND ctiot.cscn >= 1
AND ctiot.cscn < 91284204479
order by cscn,enq_tid;
----------------------------------------
| Operation | Name |
----------------------------------------
| SELECT STATEMENT | |
| INDEX RANGE SCAN| SYS_IOT_TOP_56264 |
----------------------------------------
Step
----
REM display current "bad" sql plan,
SQL>
set serveroutput off
set linesize 300
var l_bad_sql_id varchar2(30);
var l_sql_text varchar2(200);
exec :l_bad_sql_id := '52m1t23gdwdbb';
exec :l_sql_text := '"STRMADMIN"."AQ$_CAPTURE_QUEUE_TABLE_C"';
REM 2nd option to get SQL_ID,
SELECT b.executions EXEC, INST_ID, b.sql_id, CHILD_NUMBER,
ROUND(b.ROWS_PROCESSED/b.executions,1) rows_per,
ROUND(b.BUFFER_GETS/b.executions,1) gets_per,
ROUND(b.ELAPSED_TIME/b.executions/1000000,3) ela_per,
b.module, parse_calls, first_load_time, disk_reads,
b.sql_text
FROM gv$sql b
WHERE UPPER(b.sql_text) LIKE UPPER('%'||:l_sql_text||'%')
AND b.executions > 0
and b.sql_text not like 'SELECT b.executions EXEC%'
ORDER BY b.executions DESC;
REM show execution plan,
select * from table(dbms_xplan.display_awr(sql_id=>:l_bad_sql_id, format=>'basic rows cost partition note last'));
SELECT t.*
FROM v$sql s, table(DBMS_XPLAN.DISPLAY_CURSOR(s.sql_id, s.child_number, format=>'basic rows cost partition note last')) t
WHERE sql_id = :l_bad_sql_id;
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
REM - show binding variable value,
select name, position,datatype_string, last_captured, value_string, b.child_number, b.child_address, b.address
from v$sql_bind_capture b
where sql_id = :l_bad_sql_id
and was_captured <> 'NO';
select name, datatype_string, value_string
from v$sql_bind_capture b
where sql_id = :l_bad_sql_id
and was_captured <> 'NO';
REM create sql plan baseline for the SQL,
declare
l_pls number;
begin
l_pls := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (
sql_id => :l_bad_sql_id,
plan_hash_value => NULL,
fixed => 'NO',
enabled => 'NO'
);
u$err.trc('Number of plans loaded:'||l_pls);
end;
/
REM get sql text, sql_handle
select sql_text, sql_handle, plan_name, enabled, accepted, fixed, autopurge from dba_sql_plan_baselines
where sql_text like '%'||:l_sql_text||'%';
var l_sql_handle varchar2(30);
exec :l_sql_handle := 'SQL_2ff2efd831f30775';
REM change sql hint to tune SQL execution plan,
set serveroutput off
set linesize 300
var l_sql_id varchar2(30);
var l_sql_plan_hash_value number;
var l_sql_child_number number;
var B1 number;
exec :B1 := 2;
var B2 varchar2(30);
exec :B2 := '0';
var B3 number;
exec :B3 := 0;
var B4 number;
exec :B4 := 91284219049;
var B5 number;
exec :B5 := 91284219064;
SELECT enq_tid
FROM STRMADMIN.AQ$_CAPTURE_QUEUE_TABLE_C ctiot
WHERE enq_tid IN
(SELECT --+ index(qidx1)
msg_enq_tid
FROM STRMADMIN.AQ$_CAPTURE_QUEUE_TABLE_I qidx1
WHERE qidx1.subscriber# = :B1
AND qidx1.name = :B2
AND qidx1.queue# = :B3
)
AND ctiot.cscn >= :B4
AND ctiot.cscn < :B5
order by cscn,enq_tid;
REM capture new SQL_ID and plan_hash_value,
begin
with m as (select sid from v$mystat where rownum <= 1)
select prev_sql_id, prev_child_number
into :l_sql_id, :l_sql_child_number
from m, v$session s
where s.sid = m.sid;
select plan_hash_value into :l_sql_plan_hash_value from v$sql
where sql_id = :l_sql_id
and rownum <= 1;
end;
/
print :l_sql_id;
print :l_sql_plan_hash_value;
print :l_sql_child_number;
REM verify the new SQL plan,
select * from table(dbms_xplan.display_cursor(sql_id=>:l_sql_id, cursor_child_no=>:l_sql_child_number,
format=>'basic rows cost partition note last'));
REM load new sql plan into plan baseline, with same sql_handle,
declare
l_pls number;
begin
l_pls := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (
sql_id => :l_sql_id,
plan_hash_value => :l_sql_plan_hash_value,
sql_handle => :l_sql_handle,
fixed => 'NO',
enabled => 'NO'
);
u$err.trc('Number of plans:'||l_pls);
end;
/
REM display sql plan baseline,
SELECT *
FROM table(dbms_xplan.display_sql_plan_baseline(
sql_handle => :l_sql_handle,
format => 'typical'
))
;
PLAN_TABLE_OUTPUT
Plan name: SQL_PLAN_2zwrgv0sz61vp0ea25bc7 Plan id: 245521351
Enabled: NO Fixed: NO Accepted: YES Origin: MANUAL-LOAD
--------------------------------------------------------------------------------
|* 5 | INDEX RANGE SCAN| SYS_IOT_TOP_56264 | 1 | 60 | 2 (0)| 00:00:01 |
REM enable new plan in plan baseline,
REM changes an attribute of a single plan or all plans,
set serveroutput on
declare
l_pls number;
begin
l_pls := dbms_spm.ALTER_SQL_PLAN_BASELINE(
sql_handle => :l_sql_handle,
plan_name => 'SQL_PLAN_2zwrgv0sz61vp0ea25bc7',
attribute_name => 'enabled',
attribute_value => 'YES'
);
u$err.trc('Number of plans:'||l_pls);
end;
/
REM fixed new plan in plan baseline,
REM if it is realy a good plan,
declare
l_pls number;
begin
l_pls := dbms_spm.ALTER_SQL_PLAN_BASELINE(
sql_handle => :l_sql_handle,
plan_name => 'SQL_PLAN_2zwrgv0sz61vp0ea25bc7',
attribute_name => 'fixed',
attribute_value => 'YES'
);
u$err.trc('Number of plans:'||l_pls);
end;
/
REM verify attribute ENABLED and FIXED,
select sql_text, sql_handle, plan_name, enabled, accepted, fixed, autopurge
from dba_sql_plan_baselines
where sql_handle = :l_sql_handle;
REM verify new plan is picked up by CBO optimizer,
explain plan for
SELECT enq_tid
FROM STRMADMIN.AQ$_CAPTURE_QUEUE_TABLE_C ctiot
WHERE enq_tid IN
(SELECT
msg_enq_tid
FROM STRMADMIN.AQ$_CAPTURE_QUEUE_TABLE_I qidx1
WHERE qidx1.subscriber# = :B1
AND qidx1.name = :B2
AND qidx1.queue# = :B3
)
AND ctiot.cscn >= :B4
AND ctiot.cscn < :B5
order by cscn,enq_tid;
select * from table(dbms_xplan.display());
alter system flush shared_pool;
REM remove all baselines with same sql_text,
DECLARE
ret PLS_INTEGER;
BEGIN
ret := dbms_spm.drop_sql_plan_baseline('SQL_2ff2efd831f30775', 'SQL_PLAN_2zwrgv0sz61vp980bca0f');
end;
/
DECLARE
ret PLS_INTEGER;
BEGIN
FOR c IN (SELECT sql_handle, plan_name
FROM dba_sql_plan_baselines
WHERE creator = USER --'SH'
--AND created > systimestamp - to_dsinterval('0 00:15:00')
--AND enabled='NO'
and Upper(sql_text) like '%QIDX1.SUBSCRIBER# = %'
)
LOOP
ret := dbms_spm.drop_sql_plan_baseline(c.sql_handle, c.plan_name);
u$err.trc(c.sql_handle ||': '|| c.plan_name);
END LOOP;
END;
/
-- Moving SQL Plan Baselines,
exec dbms_spm.create_stgtab_baseline( table_name => 'MYSTGTAB', table_owner => 'OUTLN', tablespace_name => 'USERS');
DECLARE
ret PLS_INTEGER;
BEGIN
ret := dbms_spm.pack_stgtab_baseline( table_name => 'MYSTGTAB',
table_owner => 'OUTLN',
sql_handle => 'SQL_2ff2efd831f30775'
);
end;
/
exp system tables=outln.MYSTGTAB file=baseline_stg
imp system FROMUSER=outln TOUSER=outln tables=MYSTGTAB file=baseline_stg
DECLARE
ret PLS_INTEGER;
BEGIN
ret := dbms_spm.unpack_stgtab_baseline( table_name => 'MYSTGTAB',
table_owner => 'OUTLN',
sql_handle => 'SQL_2ff2efd831f30775'
);
end;
/
Thanks,
Charlie | Database Architect Developer
