psql.sh Tool to get session and SQL stats by process_id

Many a times, we want to get database session and SQL information based on the process ID.
Here is the one I've used for many years.


Next time I will show you how to troubleshooting Oracle database performance by this tool.

==
{code start}


#!/bin/bash
if [ $# -ne 1 ]
then
  echo "Usage: psql.sh <ProcessID>"
  exit
fi


sqlplus -S / as sysdba <<+++
set     wrap on
set     feedback off
set     pagesize 0
set termout off

set echo off
set verify off
set arraysize 200
set linesize 200
set trimspool on
set trimout on
set long 5000
set serveroutput on size 200000

/*
-- Get execution plan 
SELECT t.*
FROM v$sql s, table(DBMS_XPLAN.DISPLAY_CURSOR(s.sql_id, s.child_number, format=>'Advanced')) t
where s.sql_id = 'dcfkuagnmqx3y';

SELECT t.*
FROM v$sql s, table(DBMS_XPLAN.DISPLAY_CURSOR(s.sql_id, s.child_number, 'BASIC')) t
where s.sql_id = 'dcfkuagnmqx3y'
and s.last_load_time is not null
and loaded_versions > 0;

provides a way to display the SQL execution plan and SQL execution runtime statistics for cached SQL
cursors based on the information stored in the V$SQL_PLAN and V$SQL_PLAN_STATISTICS_ALL fixed views

*/

Begin

for s in
(select b.sid,b.serial#,b.username, b.machine,b.osuser,
b.program sprogram,c.program pprogram,c.terminal pterminal,b.terminal sterminal,PGA_ALLOC_MEM,
b.process,b.status,b.server,b.type,b.taddr,b.logon_time,b.LAST_CALL_ET,
b.lockwait, c.latchwait,c.latchspin,c.spid,
b.PREV_SQL_ID, b.sql_id,
EVENT, WAIT_TIME, SECONDS_IN_WAIT
from v\$session b, v\$process c
where  c.spid  = $1
and  c.addr  = b.paddr)
loop

--dbms_output.put_line('======================================================');
dbms_output.put_line('SID/Serial  : '|| s.sid||','||s.serial#);
dbms_output.put_line('SQL_ID      : '|| s.sql_id);
dbms_output.put_line('Foreground  : '|| 'PID: '||s.process||' - '||s.sprogram);
dbms_output.put_line('Shadow      : '|| 'PID: '||s.spid||' - '||s.pprogram);
dbms_output.put_line('Terminal    : '|| s.sterminal || '/ ' || s.pterminal);
dbms_output.put_line('OS User     : '|| s.osuser||' on '||s.machine);
dbms_output.put_line('Ora User    : '|| s.username);
dbms_output.put_line('Status Flags: '|| s.status||' '||s.server||' '||s.type);
dbms_output.put_line('Tran Active : '|| nvl(s.taddr, 'NONE'));
dbms_output.put_line('Login Time  : '|| to_char(s.logon_time, 'Day HH24:MI:SS'));
dbms_output.put_line('Last Call   : '|| to_char(sysdate-(s.last_call_et/60/60/24), 'Day HH24:MI:SS') || ' - ' || to_char(s.last_call_et/60, '990.0') || ' min');
dbms_output.put_line('Lock/ Latch : '|| nvl(s.lockwait, 'NONE')||'/ '||nvl(s.latchwait, 'NONE'));
dbms_output.put_line('Latch Spin  : '|| nvl(s.latchspin, 'NONE'));
dbms_output.put_line('PGA         : '|| s.PGA_ALLOC_MEM);
dbms_output.put_line('Wait        : '|| s.EVENT||': '||s.WAIT_TIME||', Seconds: '||s.SECONDS_IN_WAIT);

dbms_output.put_line('Current SQL statement:');
for c1 in ( select * from v\$sqltext
            where SQL_ID = s.sql_ID order by piece) loop
  dbms_output.put_line(chr(9)||c1.sql_text);
end loop;

for c1 in ( 
SELECT t.*
FROM v\$sql v, table(DBMS_XPLAN.DISPLAY_CURSOR(v.sql_id, v.child_number, 'TYPICAL')) t
where v.sql_id = s.sql_id
and v.loaded_versions > 0
) 
loop
  dbms_output.put_line(chr(9)||c1.PLAN_TABLE_OUTPUT);
end loop;
/*
SELECT *
FROM TABLE(CAST ( DBMS_XPLAN.display_cursor('s.sql_id') AS SYS.DBMS_XPLAN_TYPE_TABLE)) ;
*/
dbms_output.put_line('Previous SQL statement:');
/* for c1 in ( select * from v\$sqltext
            where SQL_ID = s.PREV_SQL_ID order by piece) loop
  dbms_output.put_line(chr(9)||c1.sql_text);
end loop;
*/

for c1 in ( 
/* SELECT * FROM TABLE(CAST ( DBMS_XPLAN.display_cursor(s.prev_sql_id) AS SYS.DBMS_XPLAN_TYPE_TABLE))
*/
SELECT tb.PLAN_TABLE_OUTPUT
FROM v\$sql v, table(CAST ( DBMS_XPLAN.DISPLAY_CURSOR(v.sql_id, v.child_number,'BASIC') AS SYS.DBMS_XPLAN_TYPE_TABLE)) tb
where v.sql_id = s.prev_sql_id
and v.loaded_versions > 0
) 
loop
  dbms_output.put_line(chr(9)||c1.PLAN_TABLE_OUTPUT);
end loop;

dbms_output.put_line('Session Waits:');
for c1 in ( select * from v\$session_wait where sid = s.sid) loop
  dbms_output.put_line(chr(9)||c1.state||': '||c1.event);
  dbms_output.put_line(chr(9)||'Wait time: '||c1.WAIT_TIME||', wait seconds: '||c1.SECONDS_IN_WAIT);
end loop;

dbms_output.put_line('Session History Waits:');
for c1 in ( select * from v\$session_wait_history where sid = s.sid order by seq# asc) loop
  dbms_output.put_line(chr(9)||c1.seq#||': '||c1.event||',Wait time: '||c1.WAIT_TIME);
end loop;

dbms_output.put_line('Session Statistics:');
/* Top session
for c1 in ( select a.statistic#,a.value, b.name, b.class, b.stat_id
 from V\$SESSTAT a, V\$STATNAME b
 where a.STATISTIC# = b.STATISTIC# and sid = s.sid and a.value>0) 
loop
  dbms_output.put_line(chr(9)||c1.statistic#||': '||c1.name||': '||c1.value);
end loop;
*/

End Loop;

END;
/

exit;
+++

{code end}

Example output:

$> psql.sh 26046

SID/Serial  : 33,32215

SQL_ID    : 98g72446puumc

Foreground  : PID: 26043 - sqlplus@mydb02.abc.com (TNS V1-V3)

Shadow    : PID: 26046 - oracle@mydb02.abc.com (TNS V1-V3)

Terminal    : / UNKNOWN

OS User     : oracle on mydb02.abc.com

Ora User    : SYS

Status Flags: ACTIVE DEDICATED USER

Tran Active : 00000000D9569228

Login Time  : Monday 14:27:33

Last Call   : Monday 14:27:33 -    0.3 min

Lock/ Latch : NONE/ NONE

Latch Spin  : NONE

PGA    : 34308772

Wait    : db file scattered read: 0, Seconds: 0

Current SQL statement:

select qt.msgid, e.local_transaction_id, e.source_commit_scn, e.

external_source_pos, e.aq_transaction_id, e.source_transaction_i

d, NVL(e.xidusn, 0), NVL(e.xidslt, 0), NVL(e.xidsqn, 0), NVL(e.f

lags, 0) from "STRMADMIN"."APPLY_QUEUE_TABLE" qt, sys.apply$_err

or e where e.apply#=:1 and qt.enq_tid(+)=e.aq_transaction_id and

qt.q_name(+)=:2 and e.local_transaction_id=:3 order by qt.step

_no

SQL_ID 98g72446puumc, child number 0

-------------------------------------

select qt.msgid, e.local_transaction_id, e.source_commit_scn,

e.external_source_pos, e.aq_transaction_id, e.source_transaction_id,

NVL(e.xidusn, 0), NVL(e.xidslt, 0), NVL(e.xidsqn, 0), NVL(e.flags, 0)

from "STRMADMIN"."APPLY_QUEUE_TABLE" qt, sys.apply$_error e where

e.apply#=:1 and qt.enq_tid(+)=e.aq_transaction_id and qt.q_name(+)=:2

and e.local_transaction_id=:3  order by qt.step_no

Plan hash value: 2690713002

----------------------------------------------------------------------------------------------------------

| Id  | Operation      | Name | Rows  | Bytes | Cost (%CPU)| Time |

----------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT      | | | |  3227 (100)| |

|   1 |  SORT ORDER BY      | |     1 |   132 |  3227   (1)| 00:00:39 |

|*  2 |   HASH JOIN OUTER      | |     1 |   132 |  3226   (1)| 00:00:39 |

|*  3 |    TABLE ACCESS BY INDEX ROWID| APPLY$_ERROR |     1 |    75 |     2   (0)| 00:00:01 |

|*  4 |     INDEX UNIQUE SCAN      | STREAMS$_APPLY_ERROR_UNQ |     1 | |     1   (0)| 00:00:01 |

|*  5 |    TABLE ACCESS FULL      | APPLY_QUEUE_TABLE |   566 | 32262 |  3224   (1)| 00:00:39 |

----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

  2 - access("QT"."ENQ_TID"="E"."AQ_TRANSACTION_ID")

  3 - filter("E"."APPLY#"=:1)

  4 - access("E"."LOCAL_TRANSACTION_ID"=:3)

  5 - filter("QT"."Q_NAME"=:2)

Note

-----

  - dynamic sampling used for this statement (level=2)

Previous SQL statement:

EXPLAINED SQL STATEMENT:

------------------------

select tab.rowid, tab.msgid, tab.corrid, tab.priority, tab.delay,

tab.expiration , tab.exception_qschema,   tab.exception_queue,

tab.chain_no, tab.local_order_no, tab.enq_time,

tab.time_manager_info, tab.state, tab.enq_tid, tab.step_no,

tab.sender_name, tab.sender_address, tab.sender_protocol,

tab.dequeue_msgid, tab.user_prop, tab.user_data  from

"STRMADMIN"."APPLY_QUEUE_TABLE" tab  where q_name = :1 and msgid = :2

Plan hash value: 4210003331

---------------------------------------------------------

| Id  | Operation    | Name |

---------------------------------------------------------

|   0 | SELECT STATEMENT    | |

|   1 |  TABLE ACCESS BY INDEX ROWID| APPLY_QUEUE_TABLE |

|   2 |   INDEX UNIQUE SCAN    | SYS_C004100 |

---------------------------------------------------------

Session Waits:

WAITED SHORT TIME: db file scattered read

Wait time: -1, wait seconds: 0

Session History Waits:

1: db file scattered read,Wait time: 0

2: db file scattered read,Wait time: 0

3: db file scattered read,Wait time: 0

4: db file scattered read,Wait time: 0

5: db file scattered read,Wait time: 0

6: db file scattered read,Wait time: 0

7: db file scattered read,Wait time: 0

8: db file scattered read,Wait time: 0

9: db file scattered read,Wait time: 0

10: db file scattered read,Wait time: 0

Session Statistics:

.

Next time I will show you how to troubleshooting Oracle database performance by this tool.

DDL to create table scott.emp and DML to load data

Purpose
=======
I often use emp (and dept) table to build test cases, to prove concept and explore new features.
It's not easy to find the original DDL and DML to create the table and load the data.
So I just get it by myself. I think these DDL/DML are useful to you too.

Notes: I hope you don't have tables named "dept" and "emp" in your application schema.

SQL>


drop table dept purge;

CREATE TABLE dept (
 deptno NUMBER(2),
 dname  VARCHAR2(15),
 loc    VARCHAR2(15),
 constraint dept_pk primary key (deptno)
);

INSERT INTO dept VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO dept VALUES (20, 'RESEARCH', 'DALLAS');
INSERT INTO dept VALUES (30, 'SALES', 'CHICAGO');
INSERT INTO dept VALUES (40, 'OPERATIONS', 'BOSTON');

COMMIT;

drop table emp purge;

CREATE TABLE EMP
( EMPNO NUMBER(4) NOT NULL,
  ENAME VARCHAR2(10),
  JOB VARCHAR2(9),
  MGR NUMBER(4),
  HIREDATE DATE,
  SAL NUMBER(7, 2),
  COMM NUMBER(7, 2),
  DEPTNO NUMBER(2),
  constraint emp_pk primary key (Empno),
  CONSTRAINT emp_fk1 FOREIGN KEY (deptno) REFERENCES dept
);

INSERT INTO EMP VALUES (7369, 'SMITH', 'CLERK',    7902, TO_DATE('17-DEC-1980', 'DD-MON-YYYY'), 800, NULL, 20);
INSERT INTO EMP VALUES (7499, 'ALLEN', 'SALESMAN', 7698, TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600, 300, 30);
INSERT INTO EMP VALUES (7521, 'WARD',  'SALESMAN', 7698, TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250, 500, 30);
INSERT INTO EMP VALUES (7566, 'JONES', 'MANAGER',  7839, TO_DATE('2-APR-1981',  'DD-MON-YYYY'), 2975, NULL, 20);
INSERT INTO EMP VALUES (7654, 'MARTIN', 'SALESMAN', 7698,TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30);
INSERT INTO EMP VALUES (7698, 'BLAKE', 'MANAGER', 7839,TO_DATE('1-MAY-1981', 'DD-MON-YYYY'), 2850, NULL, 30);
INSERT INTO EMP VALUES (7782, 'CLARK', 'MANAGER', 7839,TO_DATE('9-JUN-1981', 'DD-MON-YYYY'), 2450, NULL, 10);
INSERT INTO EMP VALUES (7788, 'SCOTT', 'ANALYST', 7566,TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO EMP VALUES (7839, 'KING', 'PRESIDENT', NULL,TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10);
INSERT INTO EMP VALUES (7844, 'TURNER', 'SALESMAN', 7698,TO_DATE('8-SEP-1981', 'DD-MON-YYYY'), 1500, 0, 30);
INSERT INTO EMP VALUES (7876, 'ADAMS', 'CLERK', 7788,TO_DATE('12-JAN-1983', 'DD-MON-YYYY'), 1100, NULL, 20);
INSERT INTO EMP VALUES (7900, 'JAMES', 'CLERK', 7698,TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 950, NULL, 30);
INSERT INTO EMP VALUES (7902, 'FORD', 'ANALYST', 7566,TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO EMP VALUES (7934, 'MILLER', 'CLERK', 7782,TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10);

commit;


Demo de-duplicate on emp.ename
=======

INSERT INTO EMP VALUES (7935, 'MILLER', 'ANALYST', 7782,TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10);
INSERT INTO EMP VALUES (7936, 'MILLER', 'SALESMAN', 7782,TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10);
commit;

A deduplicate will be like this,

DELETE FROM scott.emp
WHERE ROWID IN
 (
  SELECT rid
    FROM (SELECT /*x parallel(a) */
                 ROWID rid, ename,
                 ROW_NUMBER () OVER (PARTITION BY ename ORDER BY empno) rn
            FROM scott.emp a)
  WHERE rn <> 1
);


Gather table statistics 
=======
begin
    dbms_stats.gather_table_stats(
        ownname      => user,
        tabname      =>'EMP',
        estimate_percent => 0.2,
        method_opt   => 'for all columns size 1'
    );
end;
/

- 木匠

ORA-00911: invalid character

This one is simple, but useful.  :)

When compile  a VIEW, the error "ORA-00911: invalid character" poped up.

@biprd_abc.com> @/dev/SQL/v_nov09.sql

    AND f.COL_ABC not in

                          *

ERROR at line 444:

ORA-00911: invalid character

=====

 not in

 not in

========

Copy above 2 "not in" into a text editor, you'll see the invisible Special Character.

TextWrangler can show the invisible Special Characters.  I couldn't find this function in SubLime Text 2.

The solution is simple too: remove the string contains Special Character, and then type them again.

Thanks,

Charlie

runsql.sh shell tool to run a SQL file

Purpose: Run SQL or PL/SQL in a file.

Background:

For the SQL that takes a long time to finish, we usually let them run as a backend job in database server. E.g. rebuild index, Online Table Redefinition.
This shell tool will make it simple to call a SQL_file.
.
When doing low level things I prefer to run as sys via sqlplus on the local server (not via client sqlplus and never thru tool).  General good practice.  Eliminate workstation or network flaps etc from causing issues.
.

Notes: use full name when referencing the database objects. e.g. scott.emp;

cat  $SQLPATH/runsql.sh
.
{code start}

#Goal: run SQL in a file, so we can run it with nohup in background,


if [ $# != 1 ]
then
 echo Usage: $0 "SQL file_name"
 echo Usage 2: nohup $0 "SQL_file_name > 0.log &"
 exit
else
 echo $1
 sql_file=$1
fi

#my_path=$(pwd)
#. $HOME/.bash_profile
#echo $my_path

sqlplus -S / as sysdba  <<+++
set wrap off
--set feedback off
set pagesize 0
set verify off
set termout off

set echo off
set verify off
set arraysize 2000
set linesize 5000
set trimspool on
set long 5000
set serveroutput on
set timing on

column filename new_val filename

select '$sql_file'||'_'|| instance_name || to_char(sysdate, '_yyyymmdd_hh24miss')||'.log' filename
from v\$instance;

spool &filename

select sysdate from dual;

@$sql_file

exit;

+++

MAIL_TO=your_name@abc.com
CC=your_group_name@abc.com

tail "$sql_file"*.log | mail -s "SQL $sql_file done at $(hostname) $ORACLE_SID" -c $CC $MAIL_TO

{code end}
.

Ora-4068 Handler

What is an ORA-4068 Error?

An ORA-4068 error occurs when:

• you have a pl/sql package
• someone uses the package
• the package maintains a state
• you recreate the package (deploy a new version), killing their state

How is an ORA-4068 Error Handled?

The first time a pl/sql package is invoked it will throw an ORA-4068 error.  One simply needs to capture this error and retry the call to the pl/sql package.  This will 'clean' a flag on the DB connection and the ORA-4068 error should no longer be thrown.  When using a DB connection pool it is possible to migrate connections between retries and receive a connection that has not had the ORA-4068 'flag' cleared, thus requiring a max of NUM_CONNECTIONS_IN_DB_POOL + 1 retries.

Why should I care about handling ORA-4068 Errors?

By handling ORA-4068 errors you allow the database team to deploy new pl/sql package versions as much as they like without requiring a restart of your application (yay)!

Code Example:

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import oracle.jdbc.OracleDriver;
import java.sql.SQLException;
public class ExecutePackageProcedureTwice {
public  static void main(String[] args) throws Exception {
Connection conn = null;
CallableStatement cstmt = null;
long sleepInSecs = 20;
try {
    conn = getConnection();
    cstmt = conn.prepareCall("{call lib_history_pkg.log_book(?,?)}");
    cstmt.setString(1,"1");
    cstmt.setString(2,"1");
    executePkg(conn, cstmt);
    System.out.println("Sleeping for " + sleepInSecs + " seconds...");
    Thread.sleep(sleepInSecs*1000);
    System.out.println("Out of sleep...");
    executePkg(conn, cstmt);
} catch (SQLException e) {
            if(reExecutionRequired(e)){
                System.out.println("ORA-04068 detected - re-executing the package...");
                executePkg(conn, cstmt);
            } else
                throw e;    
} finally {
    try {
        if(cstmt != null)
            cstmt.close();
        if(conn != null)
            conn.close();
    } catch (Exception e) {
        e.printStackTrace();
    }
}
}
private static boolean reExecutionRequired(SQLException e) {
        return "72000".equals(e.getSQLState()) && e.getErrorCode() == 4068;
    }
private static Connection getConnection() throws Exception {
DriverManager.registerDriver(new OracleDriver());
return DriverManager.getConnection("jdbc:oracle:thin:@test_db01.abc.com:1521:test1", "app_user", "abc");
}
private static void executePkg(Connection conn,  CallableStatement cstmt) throws Exception {
System.out.println("Executing the package...");
cstmt.executeUpdate();
conn.commit();
}
}

Reference:

http://yong321.freeshell.org/oranotes/4068.txt

.

Polyglot persistence update 2012-Sept

This Polyglot Persistence broadcasting will be irregular.

[MongoDB]

* Pros *
 - Concurrency Improvements, from daemon global lock to DB Level Locking.
    -- it'll eventruall support Collection level locking in future release.
 - TTL Collections
 - Aggregation Framework,
 - Tag Aware Sharding
http://docs.mongodb.org/manual/release-notes/2.2/

* Cons *

Their arguments center around a couple of core themes:

- Product Maturity: low QA quality, sub-systems repeatedly breaking, driver inconsistencies, scattered and incomplete documentation, complex node management.

- Design Decisions: single write lock, memory-mapped files means that the server has little control over its performance, replication has no proxying causing ridiculous connection numbers, sharding is possible but very complex to implement b/c of several special nodes, sharding is unreliable, lots of pitfalls and gotchas.

The key thing to understand about MongoDB is that it's not a magic bullet. It has significant tradeoffs like everything else. At the end of the day, MongoDB kind of lives in its own little niche. It makes a lot of unique trade-offs that must be understood to use it effectively.

MongoDB is really a set of trade-offs. Many of the (NoSQL and SQL) databases are very specific in what they do. MongoDB is less specific but is serviceable for handling many different cases.

However, once you get to a certain scale, MongoDB will underperform the specialized solution. In fact, I'm seeing this at my day job where we are actively moving several sub-systems off MongoDB and onto better-suited products.

http://www.quora.com/MongoDB/Which-companies-have-moved-away-from-MongoDB-and-why

* Story *

Instagram was in the process of moving to MongoDB, but they eventually gave up and stuck with PostgreSQL.
- 2012-May-16


[PostgreSQL] 9.2

1.1 Index-only scans
1.2 Replication improvements
1.3 JSON datatype
1.4 Range Types

Many clients are excited about [JSON datatype], this could be another reason to move away from MongoDB.

http://wiki.postgresql.org/wiki/What's_new_in_PostgreSQL_9.2


[HBase]

* Cons *
Rows in HBase tables are sorted by row key. The sort is byte-ordered. All table accesses are via the table row key -- its primary key.

- byte-ordered, many sites have complained the inflexibility of this abstract sorting.

When desiging the key, we need to fully understand how the byte array is stored, put together the frequently accessed data set alone.

Example: lexicographical int sort results:
 1,10,100,11,12,13,14,15,16,17,18,19,2,20,21, ..., 9,91,92,93,9495,96,97,98,99.

To keep shaping the natural order, then the row keys must be left padding 0:
 001,002,...012,013,014,015,...099,100,101,...999.




Reference,

数据按照Row key的字典序(byte order)排序存储。设计key时，要充分排序存储这个特性，将经常一起读取的行存储放到一起。(位置相关性) 注意： 字典序对int排序的结果是1,10,100,11,12,13,14,15,16,17,18,19,2,20,21,…,9,91,92,93,94,95,96,97,98,99。要保持整形的自然序，行键必须用0作左填充。

http://www.cnblogs.com/wuren/


Thanks,
Charlie 木匠 | Database Architect Developer

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