Thursday, August 16, 2012

SQL Plan Baseline fix Oracle Stream AQ high CPU used

Goal
----
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

Tuesday, August 14, 2012

Oracle SQL*Developer features wanted

I'm a PL/SQL developer, I've been with Toad for 10 years. I got my MacBook Pro in 2012 April. Since there is no PL/SQL Developer release for Mac, and Toad is too expensive, I have to get my feet wet on SQL*Developer.
After 3 months use of SQL*Developer, here're something I hope I can get.

I hope below issues will be solved on future SQL*Developer release.

*******

Connections navigator: manipulate to sub-procedure/function, sub-sub<...>-procedures.
SQL Worksheet code edit mode:
 - Edit/Find... is often hanging.
 - Shortcut/hot keys to change upper/lower case.
 - block selection and edit/fill.
Save Package spec and body... :
 - sometimes saved mess/wrong code, cannot be compiled.
 - can I change the suffix to .sql from .pls ?
...
Some more is coming soon.


Notes.

Version 3.1.07.
Build MAIN-07.42


Thanks,
Charlie | Database Architect Developer

Thursday, August 02, 2012

Cassandra: how to create a COLUMN FAMILY with Leveled Compaction?

My stupid simple question is: how to create a COLUMN FAMILY with Leveled Compaction in Cassandra 1.1+?

There is no example in documentation:
http://www.datastax.com/docs/1.1/configuration/storage_configuration#compaction-strategy

I try it on Cassandra 1.1.0 and 1.1.2, both failed. The COLUMN FAMILY is still 'SizeTieredCompactionStrategy'.  :(

Here is my test and output:

@host01:/usr/share/cassandra>cqlsh host01 --cql3
Connected to BookCluster at host01:9160.
[cqlsh 2.2.0 | Cassandra 1.1.0 | CQL spec 3.0.0 | Thrift protocol 19.30.0]
Use HELP for help.
cqlsh>
cqlsh> use demo;

cqlsh:demo> CREATE COLUMNFAMILY book
        ... (isbn varchar,
        ...  book_id bigint,
        ...  price int,
        ...  obj varchar,
        ...  PRIMARY KEY (isbn, book_id)
        ... )
        ... WITH compaction_strategy_class='LeveledCompactionStrategy';
cqlsh:demo>
cqlsh:demo> describe COLUMNFAMILY book;

CREATE COLUMNFAMILY book (
  isbn text PRIMARY KEY
) WITH
  comment='' AND
  comparator='CompositeType(org.apache.cassandra.db.marshal.LongType,org.apache.cassandra.db.marshal.UTF8Type)'
AND
  read_repair_chance=0.100000 AND
  gc_grace_seconds=864000 AND
  default_validation=text AND
  min_compaction_threshold=4 AND
  max_compaction_threshold=32 AND
  replicate_on_write=True AND
  compaction_strategy_class='SizeTieredCompactionStrategy' AND
  compression_parameters:sstable_compression='org.apache.cassandra.io.compress.SnappyCompressor';

cqlsh:demo>

update:

it's been fixed since Cassandra 1.1.5


Thanks,
Charlie (@mujiang) 一个 木匠
=======
Data Architect Developer
http://mujiang.blogspot.com