In ORACLE 11G onwards, the GTT (Global Temporary Table) can has storage option to define tablespace, and no longer only can store the GTT in account default temp tablespace as old version. It makes GTT can have separate tablespace with sorting area.

However, GTT with defined tablespace will switch back to account default temp tablespace to allocate segment if the DML is on parallel mode.

Below is the test on this.

SQL> select tablespace_name, block_size, INITIAL_EXTENT, NEXT_EXTENT, CONTENTS , ALLOCATION_TYPE
 from dba_tablespaces where tablespace_name like '%TEMP%';

TABLESPACE_NAME               BLOCK_SIZE INITIAL_EXTENT NEXT_EXTENT CONTENTS ALLOCATIO
------------------------------ ---------- -------------- ----------- --------- ---------
TEMP1                               8192       1048576     1048576 TEMPORARY UNIFORM
TEMP2                               8192       2097152     2097152 TEMPORARY UNIFORM

SQL> select username, TEMPORARY_TABLESPACE from dba_users where username='USER1';
USERNAME                       TEMPORARY_TABLESPACE
------------------------------ ------------------------------
USER1                          TEMP2


CREATE GLOBAL TEMPORARY TABLE GTT_TAB
ON COMMIT PRESERVE ROWS TABLESPACE TEMP1
as select * from PERM_TAB;

==== session 1111 ===========
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> alter session enable parallel dml;
Session altered.
SQL> insert /*+ append */ into GTT_TAB
2 select /*+ parallel */ * from PERM_TAB;

399967 rows created.

SQL> commit;
Commit complete.

==== session 629 ===========
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> insert /*+ append */ into GTT_TAB
select /*+ parallel */ * from PERM_TAB;

399967 rows created

SQL> commit;
Commit complete.

=== Monitoring session =====
SQL> select inst_id, username, sql_id,session_num, tablespace, contents, segtype, extents, blocks from gv$tempseg_usage;

INST_ID USERNAME   SQL_ID       SESSION_NUM TABLESPACE                 CONTENTS SEGTYPE     EXTENTS     BLOCKS
---------- ----------- ------------- ----------- -------------------------- --------- --------- ---------- ----------
1 USER1       5aqvsvamq14t4       1111 TEMP2                     TEMPORARY DATA             201     51456
1 USER1       5aqvsvamq14t4        629 TEMP1                     TEMPORARY DATA             402     51456

For parallel DML on GTT, it is not officially supported by ORACLE. This is may be the reason that ORACLE does not check GTT definition when DML by pass the buffer cache and direct store into default temp tablespace for GTT.

May 20, 2013 by Fred Sim

Dead lock due to library cache pin and library cache lock

We are understood the objects be called and be altered can be cause the dead lock, specially, when you execute package and package has dynamic sql to “alter” self. however sometime, even no any “alter object” happened in the backend, the dead lock on library cache pin and libarary cache lock still happened.

CASE for library cache lock/pin deadlock.

There was dead lock between two sessions;
Session one doing the : insert .. select .. from .., the session had one “library cache lock” on one table, and required same lock on another table.
Session two doing nothing just try “execute SPx ..”, but with contention on “library cache pin”.
At end, system generated the dead lock between two session, with event “library cache lock” waiting in session one and “library cache pin” waiting in session two !

Any dead lock has to be two resources and two session lock each other.

When object access from remote database, ORACLE will do the validation on the object status.
1. Access the remote objects, database will compare the last validation date/time stamp stored in local Database with object last DDL date/time stamp in remote database.

2. If the Validation time stamp is before Last DDL time stamp in remote, then re-validation is required. The re-validation will set flag to invalid if the store procedure still in cache. And will requests the exclusive lock on the store procedure, and shared locks on all the dependency include remote objects – library cache pin.

Teh re-compile request was trigger by remote object be accessed within store procedure, Store procedure depends on local and remote objects, and remote objects had been changed after store procedure last be called. So when such situation happened, one simple insert .. from need dictionary — library cache lock on two tables, and execu SPx need shared lock to pin all the dependency, – Dead lock was there!

To manage the cross databases applications, change management must cover directly and indirectly dependency. Otherwise, such impaction may not happen immediate, and surprise may after a few days when remote depended object need be re-validation.

May 20, 2013 by Fred Sim

Toad add the extra lock for connected session only doing “select”

CASE description :

Application database simple “delete from tab1” hang forever, and meanwhile one session from TOAD in inactive status but with “TX “ lock on the table.
“Delete from .. ” is waiting on “enq: TX – row lock contention ”
After force kill the TOAD session, the transaction completed immediately.

Why TOAD need “TX lock” and why the TX on transaction level needed for to the base table in the query ?

Noticed when the user login into the TOAD, TOAD would capture all the user view of whole database and add lock on the objects in the whole user view. User view of database includes all_tables, all_views, all_indexes, etc. TOAD cached those user view dictionary for developers/DB users to fast view.

When login user opened the table from TOAD, it actually start the transaction and DDL lock as well. So TX lock will add on the selected opened table and lock only release after edit table window closed.
Even user just tried to run the query, since the table be selected in open tab, the TX lock will be added on the object by TOAD, so above CASE would happened.

TOAD is powerful tools but should not be recommended as production operation tools! even the user does not have the DML/DDL privs on the table, the lock still be added by TOAD!

April 15, 2013 by Fred Sim

Parallel DML and rollback segment

In ORACLE transaction, rollback segment is used to store the before data block image. Inserting record will require min rollback segment , but delete and update will ask for more rollback space since old impages need be stored for rollback transaction. Transaction size for update/delete need be count in when config the undo tablespace in ORACLE database. If undo tablespace is manual management, then No. of rollback segments and Size of rollback segments need be config according to No. of concurrent transaction and size of transaction. Application need pick up the suitable rollback segments for their transactions. however, when parallel DML be turn on, rollback segments managment will be more complex.

From ORACLE 9i, auto undo management (AUM ) had been launched, ORACLE can allocate the rollback segments to the transaction and handle parallel DML transaction as well. But, when DB config as AUM, the application lost the control on the rollback segment specially for big transaction. And application will hit the “ORA-01628: max # extents (32765) reached for rollback segment _SYSxxxx” more frequencely.

Parallel DML will performs data block change in multiplex sessions, and transaction will distrobute to child transaction, each child transaction will be one rollback segment and auto allocated by ORACLE under AUM mode. When the transaction size is huge and has high chance to hit “ORA-01628”, the possiable soultion is enable the parallel DML in the session and undo blocks will be distributed to available rollback segments depends on the degree of parallel the system can run for the transaction

1. Rollback segmentss usage in the serial DML and parallel DML

For the serial DML, the rollback segment will be only one for one transaction, and size should be same as changed blocks in the transaction.

SQL> merge /*+ parallel(a) parallel */ into par_object_list a using type_index b on (a.TYPE_ID=b.TYPE_ID)
  2  when matched then update set a.LOOP_ID = b.type_id , a.OTHER_DESC=b.TYPE_NAME;
 
881199 rows merged.
 
Elapsed: 00:00:51.47
 
SQL> r
  1  select b.name "UNDO Segment Name", b.inst# "Instance ID", b.status$ STATUS, a.ktuxesiz "UNDO Blocks", a.ktuxeusn, a.ktuxeslt xid_slot, a.ktuxesqn xid_seq, a.ktuxecfl
  2  from x$ktuxe a, undo$ b
  3* where a.ktuxesta = 'ACTIVE' and a.ktuxeusn = b.us#
 
UNDO Segment Name              Instance ID     STATUS UNDO Blocks   KTUXEUSN   XID_SLOT    XID_SEQ KTUXECFL
------------------------------ ----------- ---------- ----------- ---------- ---------- ---------- ------------------------
_SYSSMU13_896945249$                     1          3       24431         13         15     369740 NONE
 
Elapsed: 00:00:00.01

the merge DML change 881199 records and used 24431 blocks (8124 byte one block) in one rollback segment, (the Max size of segment is 8*32765 blocks).

For the same transaction, if enable parallel DML and add the parallel hint in the SQL, it ran on parallel DML mode.

SQL> alter session enable parallel dml;
 
Session altered.
 
Elapsed: 00:00:00.00
SQL> merge /*+ parallel(a) parallel */ into par_object_list a using type_index b on (a.TYPE_ID=b.TYPE_ID)
  2  when matched then update set a.LOOP_ID = b.type_id , a.OTHER_DESC=b.TYPE_NAME;
 
881199 rows merged.
 
Elapsed: 00:00:17.11
 
 
SQL> r
  1  select b.name "UNDO Segment Name", b.inst# "Instance ID", b.status$ STATUS, a.ktuxesiz "UNDO Blocks", a.ktuxeusn, a.ktuxeslt xid_slot, a.ktuxesqn xid_seq, a.ktuxecfl
  2  from x$ktuxe a, undo$ b
  3* where a.ktuxesta = 'ACTIVE' and a.ktuxeusn = b.us#
 
UNDO Segment Name              Instance ID     STATUS UNDO Blocks   KTUXEUSN   XID_SLOT    XID_SEQ KTUXECFL
------------------------------ ----------- ---------- ----------- ---------- ---------- ---------- ------------------------
_SYSSMU1_3780397527$                     2          3        1568          1         15     339161 NONE
_SYSSMU2_2232571081$                     2          3        1594          2         26     334765 NONE
_SYSSMU3_2097677531$                     2          3        1598          3         14     296724 NONE
_SYSSMU4_1152005954$                     2          3        1486          4          5     335262 NONE
_SYSSMU6_2443381498$                     2          3        1386          6          5     338601 NONE
_SYSSMU7_3286610060$                     2          3        1462          7         27     335753 NONE
_SYSSMU8_2012382730$                     2          3        1531          8          7     296203 NONE
_SYSSMU9_1424341975$                     2          3        1657          9          2     333536 NONE
_SYSSMU11_3261146618$                    1          3        1389         11          6     327540 NONE
_SYSSMU13_896945249$                     1          3        1495         13         11     369631 NONE
_SYSSMU14_275913025$                     1          3        1539         14         10     372703 NONE
_SYSSMU15_4154100692$                    1          3           0         15         32     376512 NONE
_SYSSMU16_1795321349$                    1          3        1501         16         26     334989 NONE
_SYSSMU17_3464266284$                    1          3        1594         17          5     531840 NONE
_SYSSMU18_3575979404$                    1          3        1612         18          2     363924 NONE
_SYSSMU19_2056677713$                    1          3        1481         19         16     366822 NONE
_SYSSMU20_3160523878$                    1          3        1549         20         22     364987 NONE
 
17 rows selected.

the parallel DML changed same records, and used 24442 blocks in total, however, it distributed to 16 rollback segments between 2 instances. And it used 1/3 serial time to completed. for this transaction, the max size of rollback (or transaction size) can be 16*8*32765 blocks if distributing evenly.

2. Is you SQL runs on parallel mode ?

As descriped above, one way to find out the transaction is on the parallel mode or not, is to check the No. of rollback segments be used. But, it need on line enquiring on the fix tables or system view. and information is instance. when transaction commit/rollback, the information would gone. In the application deployment, check execution plan is the way to confirm whether such transaction can be on parallel mode or not.

In below is the execution plan for the “MERGE” statement ran when session enable the parallel DML.

SQL> explain plan for
merge /*+  parallel(a) parallel */ into par_object_list a using type_index b on (a.TYPE_ID=b.TYPE_ID)
when matched then update set a.LOOP_ID = b.type_id , a.OTHER_DESC=b.TYPE_NAME;  2    3
 
Explained.
 
SQL>  select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1043000430
 
------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name            | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | MERGE STATEMENT                 |                 |  1210K|   208M|   188   (3)| 00:00:01 |       |       |        |      |            |
|   1 |  PX COORDINATOR                 |                 |       |       |            |          |       |       |        |      |            |
|   2 |   PX SEND QC (RANDOM)           | :TQ10002        |  1210K|   213M|   188   (3)| 00:00:01 |       |       |  Q1,02 | P->S | QC (RAND)  |
|   3 |    MERGE                        | PAR_OBJECT_LIST |       |       |            |          |       |       |  Q1,02 | PCWP |            |
|   4 |     PX RECEIVE                  |                 |  1210K|   213M|   188   (3)| 00:00:01 |       |       |  Q1,02 | PCWP |            |
|   5 |      PX SEND HYBRID (ROWID PKEY)| :TQ10001        |  1210K|   213M|   188   (3)| 00:00:01 |       |       |  Q1,01 | P->P | HYBRID (ROW|
|   6 |       VIEW                      |                 |       |       |            |          |       |       |  Q1,01 | PCWP |            |
|*  7 |        HASH JOIN                |                 |  1210K|   213M|   188   (3)| 00:00:01 |       |       |  Q1,01 | PCWP |            |
|   8 |         PX RECEIVE              |                 |     8 |   136 |     2   (0)| 00:00:01 |       |       |  Q1,01 | PCWP |            |
|   9 |          PX SEND BROADCAST LOCAL| :TQ10000        |     8 |   136 |     2   (0)| 00:00:01 |       |       |  Q1,00 | P->P | BCST LOCAL |
|  10 |           PX BLOCK ITERATOR     |                 |     8 |   136 |     2   (0)| 00:00:01 |       |       |  Q1,00 | PCWC |            |
|  11 |            TABLE ACCESS FULL    | TYPE_INDEX      |     8 |   136 |     2   (0)| 00:00:01 |       |       |  Q1,00 | PCWP |            |
|  12 |         PX BLOCK ITERATOR       |                 |  1210K|   193M|   185   (3)| 00:00:01 |KEY(SQ)|KEY(SQ)|  Q1,01 | PCWC |            |
|  13 |          TABLE ACCESS FULL      | PAR_OBJECT_LIST |  1210K|   193M|   185   (3)| 00:00:01 |KEY(SQ)|KEY(SQ)|  Q1,01 | PCWP |            |
------------------------------------------------------------------------------------------------------------------------------------------------

W/O session enable parallel DML, then the execution plan will showed MERGE run path was out of parallel path. and position of Merge will move up (ID=3 will move to ID=1). It means Merge self on the DB block changes is not running on parallel mode.

3. Conclusion

The DML runs on serial mode only can consume one rollback segment, the transaction size will depend on the MAX size of single rollback segment; Parallel DML can distribute the transaction into diff rollback segments depends on the parallel degree. The limition of transaction will be MIN of (MAX sub-stransactions, MAX size of single rollback segment). For the partition table with full table changes, the MAX of partition size should not exceed the MAX size of parallel segments(either MAX extends or MAX size).

The parallel DML has to be turn on the parallel DML in the session, it can be global wise turn on or ad-hoc enable within the session. In most of case, parallel hint or append hint (for insert) is be advised to add in. For some DML, parallel hint may need add into sub-query level as well.

April 13, 2013 by Fred Sim

Call ORACLE store procedure from MS Sql server via openquery

Background:

Before deploy application in Windows 64 bits OS, normally used “MS provider for ORACLE OLE DB 32 bits” in all the 32 bit OS environments. After move to Windows OS to 64 bits for MS SQL Server 2008 on wards. however, Microsoft had stopped to release the 64 bits provides for ORACLE OLE DB, And only ORACLE’s 64 bits provider for OLE DB is available to connect to ORACLE database via Link Server.

The functions and interfaces between ORACLE providers and MS providers is not fully compatible, Hence SQL server codes and ORACLE store procedure codes need be changed in order can use new ORACLE provider performs the remote calling and pass back the collection data in table/cursor type.

Existing calling interface:

1. Existing method in SQL Sever vis MS provider to call ORACLE store procedure.

In ORACLE DB , one interface — store procedure be created for MS SQL Server to call and return the data from ORACLE in record/table type,

Interface to between SQL Server and ORACLE is used “IN” and “OUT” parameter, the “OUT” is on the record/table type.

create or replace  package pkg_sqlserver as
procedure pr_sqlserver_execute(p_filter_v in varchar2, p_output_v in varchar2, p_status_t out typ_return_tbl, p_ret_code_t out typ_return_tbl);

2. In SQL Server, under MS provider for ORACLE OLE DB (32 bits)

select * from openquery(oradb, '{ call pkg_sqlserver. pr_sqlserver_execute\
''DATA_LVL=IDX|RETURN_TYPE=1|IDX_PRFM_TYPE=1|DATA_FM=20090101|DATA_TO=20090131'',
{resultset 1, p_status_t},{resultset 1, p_ret_code_t})  }' )

ISSUE:

When application deployed into Windows 64 bits, No MS provider (64 bits) for ORACLE OLE DB is available, SQL Server link server only able to call ORACLE provider – 64 bits for OLE DB, however the method to run the openquery as listed above in MS provider does not be supported in ORACLE provide-64 bits , and when switch to the ORACLE provide, the error will happen as in below:

Msg 7357, Level 16, State 2, Line 1
Cannot process the object "{ call pkg_sqlserver. pr_sqlserver_execute
('DATA_FM=20090101|DATA_TO=20080331|RETURN_TYPE=1|IDX_PRFM_TYPE=2|IDX_CODE=ERDUSMABPHARMM|IDX_CODE=FIAGG0107|IDX_CODE=GICEQXSGNIWT|IDX_CODE=GICFIAGG_C|IDX_CODE=SBG5|IDX_CODE=SBG59612|IDX_CODE=YGSCFIX|DATA_LVL=IDX|DATA_FREQ=D',
'DATATTR=IDX_DT|DATATTR=IDX_CODE',
{resultset 1, p_status_t},{resultset 1, p_ret_code_t})  }".

The OLE DB provider “OraOLEDB.Oracle” for linked server “gist” indicates that either the object has no columns or the current user does not have permissions on that object.

SOLUTION:

If application is conformable to deploy MS 32 bits providers for ORACLE OLE DB, then nothing need be changed in ORACLE and SQL Server.

If application need 64 bits provider and has to be switch to ORACLE provider, then following solution for ORACLE procedure and SQL Server openquery can be applied in application coding.

Use “PIPE ROW” function in PL/SQL to pass over the record set from ORACLE to SQL Server, function with return record type with PIPELINED.

In ORACLE, the store procedure(function) to return table/array need be wrapped and transfer back thru the “pipe” .

Simple Example :

In ORACLE database which store the source of data:

(1). create the EMP table with (empno, ename, job, mgr, hiredate, sal, comm, deptno)

create table emp
(
empno number(4),
ename VARCHAR2 (10),
job varchar2(9),
mgr number(4),
hiredate date,
sal number(7,2),
comm number(7,2),
deptno number(2)
);

(2). create the user type in ORACLE DB for defined the record data.

create or replace type emp_type as object
(
empno number(4),
ename VARCHAR2 (10),
job varchar2(9),
mgr number(4),
hiredate date,
sal number(7,2),
comm number(7,2),
deptno number(2)
);
/

create or replace type pemp_type as table of emp_type;
/

(3). create the wrapped function and return value by “PIPELINED”. The function will pass into the dynamic “where” clause for dynamic “SQL”.

create or replace FUNCTION select_emp(i_query in sys_refcursor) return pemp_type PIPELINED AS
t_emp emp%rowtype;
BEGIN
loop
fetch i_query into t_emp;
exit when (i_query%notfound);
pipe row (emp_type(t_emp.empno,t_emp.ename,t_emp.job,t_emp.mgr, t_emp.hiredate,t_emp.sal,t_emp.comm,t_emp.deptno));
end loop;
close i_query;
return;
EXCEPTION
when others then
dbms_output.put_line('ERROR INSIDE PIPELINE FUNCTION');
if i_query%isopen then
close i_query;
end if;
END select_emp;
/

(4), Create normal store procedure for application .

CREATE or replace PACKAGE emp_data AS
TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE;
function get_emp_cv (v_deptno INT) return empcurtyp;
END emp_data;
/

CREATE or replace PACKAGE BODY emp_data AS
function get_emp_cv (v_where varchar2) return empcurtyp
IS
emp_cv sys_refcursor;
sql_str varchar2(200);
BEGIN
sql_str := 'SELECT * FROM emp WHERE' || v_where;
OPEN emp_cv FOR sql_str;
return emp_cv;
END;
END emp_data;
/

In SQL Server :

(5), Calling template from SQL Server

In SQL Server: create one link server to the ORACLE DB (LINKSERVERNAME) with the name.

select * from openquery (LINKSERVERNAME,
'select * from TABLE(select_emp(emp_data.get_emp_cv(10)))')

LINKSERVERNAME build on ORACLE Provider 64 bits

March 3, 2013 by Fred Sim

Bind variable peeking in distributed query

When query pass the bind variable to the remote sub-query, the query will be parsed by local database optimizer and sub-query will be parsed/run in remote database. If the local DB and remote DB are in difference versions, then bind peeking behalves will be diffs.

When local database in 11G, as showed in above, when bind variable identified as “sensitive”, then execute plan would switching or re-gen even w/o statistics gathering happened for underline objects; however if DB is 10G, the plan only be switching/re-gen after statistics (exact to say, should dependency of plan) be gathering and “_optimizer_invalidation_period” is over.

When object’s statistics in local 11g database be updated, remote sub-query would be not triggered to switching the plan or generate new plan if the DB version on 10G; and sub-query would do plan switch and generate new version only for bind variable peeking with marked as “sensitive”.

When object’s statistics in remote database be updated, sub-query in remote DB would be triggered for plan switching /generate new version for 10G, and only generate new version of plan for 11G as descript in Part 1. If the parent plan in local DB is not depended to remote objects.

Below is the test case, repeated to run ‘6t5c5czfxq3g1′ and gathered statistic in local DB 11G, and only gathered statistics in remote DB 10G at frist time, monitor in remote DB. Bind data in local query showed NULL, and new version of execution plan would be generated after underline statistics be gathered in local 11G DB. In the remote 10G DB, even just gathered statistics only once, the execution plan would keep switch and final new version of plan be re-gen. Last round, query be called in 11G DB within “invalid interval” after statistics gathering, so no new version of plan in 11G be gathered, and in remote 10G DB, since no statistics be gathered, last execution plan be re-used.

============== before call in local ==============================

SQL> select SQL_ID, EXECUTIONS, HASH_VALUE, OLD_HASH_VALUE, PLAN_HASH_VALUE, CHILD_NUMBER, REMOTE,
LAST_ACTIVE_TIME, BIND_DATA, LAST_LOAD_TIME, IS_OBSOLETE,IS_BIND_SENSITIVE, IS_BIND_AWARE, IS_SHAREABLE
from v$sql where sql_id='6t5c5czfxq3g1';  2    3
 
SQL_ID        EXECUTIONS HASH_VALUE OLD_HASH_VALUE PLAN_HASH_VALUE CHILD_NUMBER R LAST_ACTIVE_TIME
------------- ---------- ---------- -------------- --------------- ------------ - --------------------
BIND_DATA                  LAST_LOAD_TIME                           I I I I
--------------------------- ---------------------------------------- - - - -
6t5c5czfxq3g1          3 3722120673     4132145321               0            0 Y 13-FEB-2013:17:18:10
2013-02-13/17:14:21                      N N N N
 
6t5c5czfxq3g1          1 3722120673     4132145321               0            1 Y 13-FEB-2013:17:31:00
2013-02-13/17:30:58                      N N N N
 
6t5c5czfxq3g1          4 3722120673     4132145321               0            2 Y 13-FEB-2013:17:38:18
2013-02-13/17:31:58                      N N N N
 
6t5c5czfxq3g1          1 3722120673     4132145321               0            3 Y 13-FEB-2013:17:41:25
2013-02-13/17:41:25                      N N N N
 
6t5c5czfxq3g1          1 3722120673     4132145321               0            4 Y 13-FEB-2013:17:41:53
2013-02-13/17:41:52                      N N N N
 
6t5c5czfxq3g1          6 3722120673     4132145321               0            5 Y 13-FEB-2013:17:55:13
2013-02-13/17:41:58                      N N N Y
 
 
6 rows selected.
 
================ after re-run 5 times =========================
 
SQL> r
  1  select SQL_ID, EXECUTIONS, HASH_VALUE, OLD_HASH_VALUE, PLAN_HASH_VALUE, CHILD_NUMBER, REMOTE,
  2  LAST_ACTIVE_TIME, BIND_DATA, LAST_LOAD_TIME, IS_OBSOLETE,IS_BIND_SENSITIVE, IS_BIND_AWARE, IS_SHAREABLE
  3* from v$sql where sql_id='6t5c5czfxq3g1'
 
SQL_ID        EXECUTIONS HASH_VALUE OLD_HASH_VALUE PLAN_HASH_VALUE CHILD_NUMBER R LAST_ACTIVE_TIME
------------- ---------- ---------- -------------- --------------- ------------ - --------------------
BIND_DATA                                LAST_LOAD_TIME                     I I I I
----------------------------------       ---------------------------------- - - - -
6t5c5czfxq3g1          3 3722120673     4132145321               0            0 Y 13-FEB-2013:17:18:10
2013-02-13/17:14:21                N N N N
 
6t5c5czfxq3g1          1 3722120673     4132145321               0            1 Y 13-FEB-2013:17:31:00
2013-02-13/17:30:58                N N N N
 
6t5c5czfxq3g1          4 3722120673     4132145321               0            2 Y 13-FEB-2013:17:38:18
2013-02-13/17:31:58                N N N N
 
6t5c5czfxq3g1          1 3722120673     4132145321               0            3 Y 13-FEB-2013:17:41:25
                      2013-02-13/17:41:25                N N N N
 
6t5c5czfxq3g1          1 3722120673     4132145321               0            4 Y 13-FEB-2013:17:41:53
2013-02-13/17:41:52                N N N N
 
6t5c5czfxq3g1          6 3722120673     4132145321               0            5 Y 13-FEB-2013:17:55:13
2013-02-13/17:41:58                N N N N
 
6t5c5czfxq3g1          1 3722120673     4132145321               0            6 Y 13-FEB-2013:17:56:49
2013-02-13/17:56:49                N N N N
 
6t5c5czfxq3g1          1 3722120673     4132145321               0            7 Y 13-FEB-2013:17:59:28
2013-02-13/17:59:27                N N N N
 
6t5c5czfxq3g1          1 3722120673     4132145321               0            8 Y 13-FEB-2013:18:03:40
2013-02-13/18:03:40                N N N N
 
6t5c5czfxq3g1          2 3722120673     4132145321               0            9 Y 13-FEB-2013:18:08:41
2013-02-13/18:06:09                N N N Y
 
 
10 rows selected.
 
SQL>
 
 
============== in DB 10G, remote site, before remote call ==============================
 
SQL> r
  1  select SQL_ID, EXECUTIONS, HASH_VALUE, OLD_HASH_VALUE, PLAN_HASH_VALUE, CHILD_NUMBER, REMOTE, LAST_ACTIVE_TIME, BIND_DATA, LAST_LOAD_TIME, IS_OBSOLETE
  2* from v$sql where sql_id='fva6vxg5vdn10'
 
SQL_ID        EXECUTIONS HASH_VALUE OLD_HASH_VALUE PLAN_HASH_VALUE CHILD_NUMBER R LAST_ACTIVE_TIME
------------- ---------- ---------- -------------- --------------- ------------ - --------------------
BIND_DATA                                                                  LAST_LOAD_TIME   I
-------------------------------------------------------------------------------  -----------------  -
fva6vxg5vdn10          5 3417755680     3646009071      3214516708            0 N 13-FEB-2013:17:41:25
BEDA0A200300511B5D52000101F0018003691432372D4445432D323031323A31303A35323A3333   2013-02-13/17:14:21 N
 
fva6vxg5vdn10          6 3417755680     3646009071      3214516708            1 N 13-FEB-2013:17:41:58
BEDA0A200300511B5D8E000101F0018003691432372D4445432D323031323A31303A35323A3333   2013-02-13/17:31:59 N
 
fva6vxg5vdn10          1 3417755680     3646009071      3214516708            2 N 13-FEB-2013:17:43:07
BEDA0A200300511B602B000101F0018003691432362D4A414E2D323031333A31303A35323A3034   2013-02-13/17:43:07 N
 
 
SQL> select distinct  t.sql_text SQL_TEXT,  t.PLAN_HASH_VALUE, b.name BIND_NAME,  b.value_string BIND_STRING, b.LAST_CAPTURED, b.WAS_CAPTURED
  2  from  v$sql t  join v$sql_bind_capture b  using (sql_id)
  3  where b.value_string is not null and sql_id='fva6vxg5vdn10';
 
SQL_TEXT
-------------------------------------------------------------------------------------------------------------PLAN_HASH_VALUE BIND_NAME             BIND_STRING                LAST_CAPTURED        WAS
--------------- ----------------- -------------------------- -------------------- ---
SELECT MAX("A1"."OBJECT_ID")-MIN("A1"."OBJECT_ID") FROM "PT1_V" "A1" WHERE "A1"."CREATED"=:VDATE
     3214516708 :VDATE                   27-DEC-2012:10:52:33       13-FEB-2013:17:30:58 YES
 
SELECT MAX("A1"."OBJECT_ID")-MIN("A1"."OBJECT_ID") FROM "PT1_V" "A1" WHERE "A1"."CREATED"=:VDATE
     3214516708 :VDATE                   26-JAN-2013:10:52:04       13-FEB-2013:17:43:07 YES
 
SELECT MAX("A1"."OBJECT_ID")-MIN("A1"."OBJECT_ID") FROM "PT1_V" "A1" WHERE "A1"."CREATED"=:VDATE
     3214516708 :VDATE                   27-DEC-2012:10:52:33       13-FEB-2013:17:31:58 YES
 
 
============== after remote call 5 times ===================
SQL> r
  1  select SQL_ID, EXECUTIONS, HASH_VALUE, OLD_HASH_VALUE, PLAN_HASH_VALUE, CHILD_NUMBER, REMOTE, LAST_ACTIVE_TIME, BIND_DATA, LAST_LOAD_TIME, IS_OBSOLETE
  2* from v$sql where sql_id='fva6vxg5vdn10'
 
SQL_ID        EXECUTIONS HASH_VALUE OLD_HASH_VALUE PLAN_HASH_VALUE CHILD_NUMBER R LAST_ACTIVE_TIME
------------- ---------- ---------- -------------- --------------- ------------ - --------------------
BIND_DATA                                                                  LAST_LOAD_TIME     I
-------------------------------------------------------------------------------  -----------------   -
fva6vxg5vdn10          6 3417755680     3646009071      3214516708            0 N 13-FEB-2013:17:56:49
BEDA0A200300511B635E000101F0018003691432312D5345502D323031323A30303A30303A3030   2013-02-13/17:14:21 N
 
fva6vxg5vdn10          7 3417755680     3646009071      3214516708            1 N 13-FEB-2013:17:59:25
BEDA0A200300511B63FD000101F0018003691432312D5345502D323031323A30303A30303A3030   2013-02-13/17:31:59 N
 
fva6vxg5vdn10          6 3417755680     3646009071      3214516708            2 N 13-FEB-2013:18:03:40
BEDA0A200300511B64F9000101F0018003691432312D5345502D323031323A30303A30303A3030   2013-02-13/17:43:07 N
 
fva6vxg5vdn10          2 3417755680     3646009071      3214516708            3 N 13-FEB-2013:18:08:40
BEDA0A200300511B658F000101F0018003691432312D5345502D323031323A30303A30303A3030   2013-02-13/18:06:09 N
 
 
SQL> select distinct t.sql_text SQL_TEXT,  t.PLAN_HASH_VALUE, b.name BIND_NAME,  b.value_string BIND_STRING, b.LAST_CAPTURED, b.WAS_CAPTURED
  2  from  v$sql t  join v$sql_bind_capture b  using (sql_id)
  3  where b.value_string is not null and sql_id='fva6vxg5vdn10';
 
SQL_TEXT
-------------------------------------------------------------------------------------------------------------PLAN_HASH_VALUE BIND_NAME             BIND_STRING                LAST_CAPTURED        WAS
--------------- ----------------- -------------------------- -------------------- ---
SELECT MAX("A1"."OBJECT_ID")-MIN("A1"."OBJECT_ID") FROM "PT1_V" "A1" WHERE "A1"."CREATED"=:VDATE
     3214516708 :VDATE                   21-SEP-2012:00:00:00       13-FEB-2013:18:06:07 YES
 
SELECT MAX("A1"."OBJECT_ID")-MIN("A1"."OBJECT_ID") FROM "PT1_V" "A1" WHERE "A1"."CREATED"=:VDATE
     3214516708 :VDATE                   21-SEP-2012:00:00:00       13-FEB-2013:18:03:37 YES
 
SELECT MAX("A1"."OBJECT_ID")-MIN("A1"."OBJECT_ID") FROM "PT1_V" "A1" WHERE "A1"."CREATED"=:VDATE
     3214516708 :VDATE                   21-SEP-2012:00:00:00       13-FEB-2013:17:56:46 YES
 
SELECT MAX("A1"."OBJECT_ID")-MIN("A1"."OBJECT_ID") FROM "PT1_V" "A1" WHERE "A1"."CREATED"=:VDATE
     3214516708 :VDATE                   21-SEP-2012:00:00:00       13-FEB-2013:17:59:25 YES

We did the same test in query in DB 10G and remote DB is 11G, the remote sub-query be created as “sensitive”. At this round, we just changed the bind variable value w/o gathering statistics on the local object to invalid the execution plan(in 10G, the new version of plan only generated after all the cached plan be re-tried). Test case showed, remote 11G had same behalves as local query with bind peeking “sensitive”, and two plan for sub-query final on re-useable status and severed for bind variable changed in local 10G DB. In 10G DB since, since no any action to invalidate the execution plan, it just re-used one version of execution plan.

In Local 10G DB :

SQL> select SQL_ID, EXECUTIONS, HASH_VALUE, OLD_HASH_VALUE, PLAN_HASH_VALUE, CHILD_NUMBER, REMOTE, LAST_ACTIVE_TIME, BIND_DATA, LAST_LOAD_TIME, IS_OBSOLETE
  2  from v$sql where sql_id='0v188ja00rw4a';
 
SQL_ID        EXECUTIONS HASH_VALUE OLD_HASH_VALUE PLAN_HASH_VALUE CHILD_NUMBER R LAST_ACTIVE_TIME
------------- ---------- ---------- -------------- --------------- ------------ - --------------------
BIND_DATA                  LAST_LOAD_TIME           I
-------------------------  -----------------      -
0v188ja00rw4a          8 2148266122     3380594699               0            0 Y 14-FEB-2013:18:28:01
2013-02-14/18:12:36      N

In remote 11G DB, two shareable plan for this sub-query with two bind values.

SQL> r
  1  select SQL_ID, EXECUTIONS, HASH_VALUE, OLD_HASH_VALUE, PLAN_HASH_VALUE, CHILD_NUMBER, REMOTE,
  2  LAST_ACTIVE_TIME, BIND_DATA, LAST_LOAD_TIME, IS_OBSOLETE,IS_BIND_SENSITIVE, IS_BIND_AWARE, IS_SHAREABLE
  3* from v$sql where sql_id='1sup3wx6g52r8'
 
SQL_ID        EXECUTIONS HASH_VALUE OLD_HASH_VALUE PLAN_HASH_VALUE CHILD_NUMBER R LAST_ACTIVE_TIME
------------- ---------- ---------- -------------- --------------- ------------ - --------------------
BIND_DATA                                LAST_LOAD_TIME          I I I I
-------------------------------------           ----------------------- - - - -
1sup3wx6g52r8          3 1290963688      258167858       599033881            0 N 14-feb-2013:18:25:21
BEDA0B200100511CB894000101C0021602C102   2013-02-14/18:12:36     N Y N N
 
1sup3wx6g52r8          3 1290963688      258167858        46243151            1 N 14-feb-2013:18:27:46
BEDA0B200100511CBB91000101C002160180            2013-02-14/18:25:22     N Y Y Y
 
1sup3wx6g52r8          2 1290963688      258167858       599033881            2 N 14-feb-2013:18:28:02
BEDA0B200100511CBBD7000101C0021602C102   2013-02-14/18:26:31     N Y Y Y

When the query and remote sub query have same bind variable, “peeking sensitive” may has diffs in local and remote database.

Appendix — Test case on Local ORACLE 10G and remote 11g with Bind variable peeking

---- In local 10G, 
 
SQL> r
  1* select max(object_id) from object_list@tio2 where type_id = :vtype
 
MAX(OBJECT_ID)
--------------
        152388
 
---- In remote 11G DB
 
SQL> select SQL_ID, EXECUTIONS, HASH_VALUE, OLD_HASH_VALUE, PLAN_HASH_VALUE, CHILD_NUMBER, REMOTE,
LAST_ACTIVE_TIME, BIND_DATA, LAST_LOAD_TIME, IS_OBSOLETE,IS_BIND_SENSITIVE, IS_BIND_AWARE, IS_SHAREABLE
from v$sql where sql_id='1sup3wx6g52r8'  2    3  ;
 
SQL_ID        EXECUTIONS HASH_VALUE OLD_HASH_VALUE PLAN_HASH_VALUE CHILD_NUMBER R LAST_ACTIVE_TIME
------------- ---------- ---------- -------------- --------------- ------------ - --------------------
BIND_DATA                                LAST_LOAD_TIME          I I I I
-------------------------------------           ----------------------- - - - -
1sup3wx6g52r8          3 1290963688      258167858       599033881            0 N 14-feb-2013:18:20:23
BEDA0B200100511CB894000101C0021602C102       2013-02-14/18:12:36     N Y N Y
 
 
---- In local 10G 
 
SQL> exec :vtype := 0;
 
PL/SQL procedure successfully completed.
 
SQL> select max(object_id) from object_list@tio2 where type_id = :vtype;
 
MAX(OBJECT_ID)
--------------
        152389
 
---- in remote 11G 
 
SQL> r
  1  select SQL_ID, EXECUTIONS, HASH_VALUE, OLD_HASH_VALUE, PLAN_HASH_VALUE, CHILD_NUMBER, REMOTE,
  2  LAST_ACTIVE_TIME, BIND_DATA, LAST_LOAD_TIME, IS_OBSOLETE,IS_BIND_SENSITIVE, IS_BIND_AWARE, IS_SHAREABLE
  3* from v$sql where sql_id='1sup3wx6g52r8'
 
SQL_ID        EXECUTIONS HASH_VALUE OLD_HASH_VALUE PLAN_HASH_VALUE CHILD_NUMBER R LAST_ACTIVE_TIME
------------- ---------- ---------- -------------- --------------- ------------ - --------------------
BIND_DATA                                LAST_LOAD_TIME          I I I I
-------------------------------------           ----------------------- - - - -
1sup3wx6g52r8          3 1290963688      258167858       599033881            0 N 14-feb-2013:18:25:21
BEDA0B200100511CB894000101C0021602C102   2013-02-14/18:12:36     N Y N Y
 
1sup3wx6g52r8          1 1290963688      258167858        46243151            1 N 14-feb-2013:18:25:21
BEDA0B200100511CBB91000101C002160180            2013-02-14/18:25:22     N Y Y Y
 
 
--- in local 10G
 
SQL> exec :vtype := 1;
 
PL/SQL procedure successfully completed.
 
SQL> r
  1* select max(object_id) from object_list@tio2 where type_id = :vtype
 
MAX(OBJECT_ID)
--------------
        152388
 
--- in remote 11g, and gather statistics 
 
SQL> r
  1  select SQL_ID, EXECUTIONS, HASH_VALUE, OLD_HASH_VALUE, PLAN_HASH_VALUE, CHILD_NUMBER, REMOTE,
  2  LAST_ACTIVE_TIME, BIND_DATA, LAST_LOAD_TIME, IS_OBSOLETE,IS_BIND_SENSITIVE, IS_BIND_AWARE, IS_SHAREABLE
  3* from v$sql where sql_id='1sup3wx6g52r8'
 
SQL_ID        EXECUTIONS HASH_VALUE OLD_HASH_VALUE PLAN_HASH_VALUE CHILD_NUMBER R LAST_ACTIVE_TIME
------------- ---------- ---------- -------------- --------------- ------------ - --------------------
BIND_DATA                                LAST_LOAD_TIME          I I I I
-------------------------------------           ----------------------- - - - -
1sup3wx6g52r8          3 1290963688      258167858       599033881            0 N 14-feb-2013:18:25:21
BEDA0B200100511CB894000101C0021602C102   2013-02-14/18:12:36     N Y N N
 
1sup3wx6g52r8          1 1290963688      258167858        46243151            1 N 14-feb-2013:18:26:31
BEDA0B200100511CBB91000101C002160180            2013-02-14/18:25:22     N Y Y Y
 
1sup3wx6g52r8          1 1290963688      258167858       599033881            2 N 14-feb-2013:18:26:31
BEDA0B200100511CBBD7000101C0021602C102   2013-02-14/18:26:31     N Y Y Y
 
--- in local 10G
 
SQL> exec :vtype := 0;
 
PL/SQL procedure successfully completed.
 
SQL> r
  1* select max(object_id) from object_list@tio2 where type_id = :vtype
 
MAX(OBJECT_ID)
--------------
        152389
 
--- in remote 11G
SQL> r
  1  select SQL_ID, EXECUTIONS, HASH_VALUE, OLD_HASH_VALUE, PLAN_HASH_VALUE, CHILD_NUMBER, REMOTE,
  2  LAST_ACTIVE_TIME, BIND_DATA, LAST_LOAD_TIME, IS_OBSOLETE,IS_BIND_SENSITIVE, IS_BIND_AWARE, IS_SHAREABLE
  3* from v$sql where sql_id='1sup3wx6g52r8'
 
SQL_ID        EXECUTIONS HASH_VALUE OLD_HASH_VALUE PLAN_HASH_VALUE CHILD_NUMBER R LAST_ACTIVE_TIME
------------- ---------- ---------- -------------- --------------- ------------ - --------------------
BIND_DATA                                LAST_LOAD_TIME          I I I I
-------------------------------------           ----------------------- - - - -
1sup3wx6g52r8          3 1290963688      258167858       599033881            0 N 14-feb-2013:18:25:21
BEDA0B200100511CB894000101C0021602C102   2013-02-14/18:12:36      N Y N N
 
1sup3wx6g52r8          2 1290963688      258167858        46243151            1 N 14-feb-2013:18:27:14
BEDA0B200100511CBB91000101C002160180            2013-02-14/18:25:22      N Y Y Y
 
1sup3wx6g52r8          1 1290963688      258167858       599033881            2 N 14-feb-2013:18:27:14
BEDA0B200100511CBBD7000101C0021602C102   2013-02-14/18:26:31      N Y Y Y
 
 
--- in local 10G
 
SQL> r
  1* select max(object_id) from object_list@tio2 where type_id = :vtype
 
MAX(OBJECT_ID)
--------------
        152389
 
--- in remote 11g
SQL> r
  1  select SQL_ID, EXECUTIONS, HASH_VALUE, OLD_HASH_VALUE, PLAN_HASH_VALUE, CHILD_NUMBER, REMOTE,
  2  LAST_ACTIVE_TIME, BIND_DATA, LAST_LOAD_TIME, IS_OBSOLETE,IS_BIND_SENSITIVE, IS_BIND_AWARE, IS_SHAREABLE
  3* from v$sql where sql_id='1sup3wx6g52r8'
 
SQL_ID        EXECUTIONS HASH_VALUE OLD_HASH_VALUE PLAN_HASH_VALUE CHILD_NUMBER R LAST_ACTIVE_TIME
------------- ---------- ---------- -------------- --------------- ------------ - --------------------
BIND_DATA                                LAST_LOAD_TIME          I I I I
-------------------------------------           ----------------------- - - - -
1sup3wx6g52r8          3 1290963688      258167858       599033881            0 N 14-feb-2013:18:25:21
BEDA0B200100511CB894000101C0021602C102   2013-02-14/18:12:36     N Y N N
 
1sup3wx6g52r8          3 1290963688      258167858        46243151            1 N 14-feb-2013:18:27:46
BEDA0B200100511CBB91000101C002160180            2013-02-14/18:25:22     N Y Y Y
 
1sup3wx6g52r8          1 1290963688      258167858       599033881            2 N 14-feb-2013:18:27:46
BEDA0B200100511CBBD7000101C0021602C102   2013-02-14/18:26:31     N Y Y Y
 
 
--- in local 10G 
 
SQL> exec :vtype := 1;
 
PL/SQL procedure successfully completed.
 
SQL> r
  1* select max(object_id) from object_list@tio2 where type_id = :vtype
 
MAX(OBJECT_ID)
--------------
        152388
 
------ in remote 11G
 
SQL> r
  1  select SQL_ID, EXECUTIONS, HASH_VALUE, OLD_HASH_VALUE, PLAN_HASH_VALUE, CHILD_NUMBER, REMOTE,
  2  LAST_ACTIVE_TIME, BIND_DATA, LAST_LOAD_TIME, IS_OBSOLETE,IS_BIND_SENSITIVE, IS_BIND_AWARE, IS_SHAREABLE
  3* from v$sql where sql_id='1sup3wx6g52r8'
 
SQL_ID        EXECUTIONS HASH_VALUE OLD_HASH_VALUE PLAN_HASH_VALUE CHILD_NUMBER R LAST_ACTIVE_TIME
------------- ---------- ---------- -------------- --------------- ------------ - --------------------
BIND_DATA                                LAST_LOAD_TIME          I I I I
-------------------------------------           ----------------------- - - - -
1sup3wx6g52r8          3 1290963688      258167858       599033881            0 N 14-feb-2013:18:25:21
BEDA0B200100511CB894000101C0021602C102   2013-02-14/18:12:36     N Y N N
 
1sup3wx6g52r8          3 1290963688      258167858        46243151            1 N 14-feb-2013:18:27:46
BEDA0B200100511CBB91000101C002160180            2013-02-14/18:25:22     N Y Y Y
 
1sup3wx6g52r8          2 1290963688      258167858       599033881            2 N 14-feb-2013:18:28:02
BEDA0B200100511CBBD7000101C0021602C102   2013-02-14/18:26:31     N Y Y Y
 
 
 
---- check the SQL plan version in local 10G 
 
SQL>  select * from v$sql_bind_capture where sql_id='0v188ja00rw4a';
 
no rows selected
 
SQL> select SQL_ID, EXECUTIONS, HASH_VALUE, OLD_HASH_VALUE, PLAN_HASH_VALUE, CHILD_NUMBER, REMOTE, LAST_ACTIVE_TIME, BIND_DATA, LAST_LOAD_TIME, IS_OBSOLETE
  2  from v$sql where sql_id='0v188ja00rw4a';
 
SQL_ID        EXECUTIONS HASH_VALUE OLD_HASH_VALUE PLAN_HASH_VALUE CHILD_NUMBER R LAST_ACTIVE_TIME
------------- ---------- ---------- -------------- --------------- ------------ - --------------------
BIND_DATA                  LAST_LOAD_TIME           I
-------------------------  -----------------      -
0v188ja00rw4a          8 2148266122     3380594699               0            0 Y 14-FEB-2013:18:28:01
2013-02-14/18:12:36      N

March 1, 2013 by Fred Sim

Bind variable peeking with column histogram on stewed value

In 10G, even bind variable peeking plus stewed column data, the bind variable values changing would not trigger SQL plan switching or re-gen. Execution re-gen/switch is triggered by statistics, and when it just switches, the current value of bind variables would not be used by optimizer, only new version of plan will be affected by the current captured values (captured interval trigger system to capture current bind variable value).

In 11G, Optimizer add one attribute to check the bind variable is sensitive or not, in v$SQL view, a few columns be add in to host the bind variable new attributes : IS_BIND_SENSITIVE, IS_BIND_AWARE, IS_SHAREABLE

If IS_BIND_SENSITIVE = ‘N’, then bind variable current value would not change the execution plans. ORACLE only add SQL with bind variable sensitive for simple filter as <column i> = <variable> and <column i> had stewed data with histogram statistics. Otherwise, Optimizer handle execution plan as Non bind variable SQL, and current bind variables’ values only be used when new version of plan be re-gen.

For “sensitive” bind variable, new version of plan base on the actual value would be generated when value be changed. The existing execution plans in cache has one attribute “IS_SHAREABLE”, when “sensitive” bind variable value changed, Optimizer will re-use the plan if column statistics shows the value has same distribution bulk with the captured bind variable value for this version/child number of SQL ID. If the captured time exceeds the “_cursor_bind_capture_interval”, then new captured value for this version of SQL on the execution plan will be logged, and be used in future for plan switching.

Run :

select max(object_id),min(object_id) from object_list where type_id < :vtype + 1

 Check the bind value and execution plan:

SQL> r

  1  select SQL_ID, EXECUTIONS, HASH_VALUE, OLD_HASH_VALUE, PLAN_HASH_VALUE, CHILD_NUMBER, REMOTE,
  2  LAST_ACTIVE_TIME, BIND_DATA, LAST_LOAD_TIME, IS_OBSOLETE,IS_BIND_SENSITIVE, IS_BIND_AWARE, IS_SHAREABLE
  3* from v$sql where sql_id= '8abcnfd53uwpn'
 SQL_ID        EXECUTIONS HASH_VALUE OLD_HASH_VALUE PLAN_HASH_VALUE CHILD_NUMBER R LAST_ACTIVE_TIME
------------- ---------- ---------- -------------- --------------- ------------ - --------------------
BIND_DATA                                LAST_LOAD_TIME                    I I I I
---------------------------------------  -------------------------- - - - -
8abcnfd53uwpn          5 1245541044     2104998663        46243151            0 N 08-FEB-2013:09:36:54
BEDA0B20010051145657000101C002160180            2013-02-08/09:35:19           N N N Y

For Table “object_list”, the “TYPE_ID” had stewed data and histogram had been created. The bind variable peeking only be defined as “SENSITIVE”, if the column filter expression is directly compare with bind variable and the column data is stewed distributed, and the column had histogram statistics.

SQL> select * from DBA_TAB_HISTOGRAMS where table_name=’OBJECT_LIST’ and column_name=’TYPE_ID’;

 OWNER     TABLE_NAME       COLUMN_NAME   ENDPOINT_NUMBER ENDPOINT_VALUE    ENDPOINT_ACTUAL_VALUE
--------- -----------------   ------------  --------------- -------------- -----------------------
DBA1     OBJECT_LIST       TYPE_ID                                 16              0
DBA1     OBJECT_LIST       TYPE_ID               1281200              1

Run below with /* vtyp in (0,1,2,3) */

select max(object_id) from object_list where type_id = :vtyp

 

SQL> r
  1  select SQL_ID, EXECUTIONS, HASH_VALUE, OLD_HASH_VALUE, PLAN_HASH_VALUE, CHILD_NUMBER, REMOTE,
  2  LAST_ACTIVE_TIME, BIND_DATA, LAST_LOAD_TIME, IS_OBSOLETE,IS_BIND_SENSITIVE, IS_BIND_AWARE, IS_SHAREABLE
  3  from v$sql where sql_id= 'ck03fdksfnrtw'
  4*
 
SQL_ID        EXECUTIONS HASH_VALUE OLD_HASH_VALUE PLAN_HASH_VALUE CHILD_NUMBER R LAST_ACTIVE_TIME
------------- ---------- ---------- -------------- --------------- ------------ - --------------------
BIND_DATA                                LAST_LOAD_TIME             I I I I
----------------------------------------     --------------------   - - - -                                                         
ck03fdksfnrtw          2 2968149820      847886462       599033881            0 N 26-FEB-2013:18:10:55
BEDA0B200100512C8A2F000101C0021602C102   2013-02-26/10:50:01    N Y N Y
 
ck03fdksfnrtw          4 2968149820      847886462        46243151            1 N 26-FEB-2013:18:15:19
BEDA0B200100512C8AA5000101C002160180            2013-02-26/18:12:53    N Y N N
 
ck03fdksfnrtw          2 2968149820      847886462       599033881            2 N 26-FEB-2013:18:16:46
BEDA0B200100512C8B37000101C0021602C102   2013-02-26/18:15:18    N Y Y Y
 
ck03fdksfnrtw          1 2968149820      847886462        46243151            3 N 26-FEB-2013:18:16:27
BEDA0B200100512C8B53000101C0021602C104   2013-02-26/18:15:46    N Y Y N
 
ck03fdksfnrtw          3 2968149820      847886462        46243151            4 N 26-FEB-2013:18:18:41
BEDA0B200100512C8B7B000101C002160180            2013-02-26/18:16:27    N Y Y Y
 
SQL> select distinct t.CHILD_NUMBER,t.PLAN_HASH_VALUE, b.name BIND_NAME,  b.value_string BIND_STRING,
            b.LAST_CAPTURED, b.WAS_CAPTURED
     from  v$sql t , v$sql_bind_capture b
     where b.value_string is not null and t.sql_id= 'ck03fdksfnrtw'
       and t.sql_id=b.sql_id and t.CHILD_NUMBER = b.CHILD_NUMBER order by b.LAST_CAPTURED;
 
CHILD_NUMBER PLAN_HASH_VALUE BIND_NAME        BIND_STRING            LAST_CAPTURED        WAS
------------ --------------- -------------      ---------------     -------------------- ---
           0       599033881 :VTYP                     1      26-FEB-2013:18:10:55 YES
           1        46243151 :VTYP                     0      26-FEB-2013:18:12:53 YES
           2       599033881 :VTYP                     1      26-FEB-2013:18:15:19 YES
           3        46243151 :VTYP                     3      26-FEB-2013:18:15:47 YES
           4        46243151 :VTYP                     0      26-FEB-2013:18:16:27 YES

In ORACLE 11G, new view be created for store “validate” plan selectivity for the execution plans.

SQL> select  hash_value, sql_id, child_number, range_id, low, high, predicate
   from  v$sql_cs_selectivity
    where sql_id='ck03fdksfnrtw';
 
HASH_VALUE SQL_ID        CHILD_NUMBER   RANGE_ID LOW        HIGH       PREDICATE
---------- ------------- ------------ ---------- ---------- ---------- ------------------------------
2968149820 ck03fdksfnrtw            4          0 0.000006   0.000014   =VTYP
2968149820 ck03fdksfnrtw            3          0 0.000006   0.000007   =VTYP
2968149820 ck03fdksfnrtw            2          0 0.899988   1.099986   =VTYP

Another new view store the histogram for execution times, and each version/child has three buckets

SQL> select hash_value, sql_id, child_number, bucket_id, count
     from  v$sql_cs_histogram
     where sql_id='ck03fdksfnrtw';
  2    3
HASH_VALUE SQL_ID        CHILD_NUMBER  BUCKET_ID      COUNT
---------- ------------- ------------ ---------- ----------
2968149820 ck03fdksfnrtw            4          0          3
2968149820 ck03fdksfnrtw            4          1          0
2968149820 ck03fdksfnrtw            4          2          0
 
2968149820 ck03fdksfnrtw            3          0          1
2968149820 ck03fdksfnrtw            3          1          0
2968149820 ck03fdksfnrtw            3          2          0
 
2968149820 ck03fdksfnrtw            2          0          0
2968149820 ck03fdksfnrtw            2          1          0
2968149820 ck03fdksfnrtw            2          2          2
 
2968149820 ck03fdksfnrtw            1          0          3
2968149820 ck03fdksfnrtw            1          1          0
2968149820 ck03fdksfnrtw            1          2          1
 
2968149820 ck03fdksfnrtw            0          0          1
2968149820 ck03fdksfnrtw            0          1          0
2968149820 ck03fdksfnrtw            0          2          1
 
15 rows selected.

ORACLE 11g also store the running time resource usage statistics such as return rows, buffer usage CPU in the view

SQL> select hash_value, sql_id, child_number, bind_set_hash_value, peeked, executions, rows_processed, buffer_gets, cpu_time
from v$sql_cs_statistics
where sql_id='ck03fdksfnrtw';  2    3
 
HASH_VALUE SQL_ID        CHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS ROWS_PROCESSED BUFFER_GETS   CPU_TIME
---------- ------------- ------------ ------------------- - ---------- -------------- ----------- ----------
2968149820 ck03fdksfnrtw            4          1475158189 Y          1             33          19          0
2968149820 ck03fdksfnrtw            3          2706503459 Y          1              1           3          0
2968149820 ck03fdksfnrtw            2          2342552567 Y          1        1281185        9087          0
2968149820 ck03fdksfnrtw            1          1475158189 Y          1             33          19          0
2968149820 ck03fdksfnrtw            0          1475158189 Y          1             17        9087          0

ORACLE 11G has owe rule to set SQL as “SENSITIVE”, ORACLE 11g only manage those SQL marked as “SENSITIVE” bind variable as above.

Appendix  ---  Test case on ORACLE 10G for stewed data and Bind variable peeking

 alter system set "_optimizer_invalidation_period" = 60 scope=memory;
 SQL> var vdate number;
SQL> exec :vdate := 20;
 
PL/SQL procedure successfully completed.
 
SQL> select max(object_id), min(object_id) from pt1 where created > sysdate - :vdate;
 
MAX(OBJECT_ID) MIN(OBJECT_ID)
-------------- --------------
         46478              2
 
SQL> select SQL_ID, EXECUTIONS, HASH_VALUE, OLD_HASH_VALUE, PLAN_HASH_VALUE, CHILD_NUMBER, REMOTE,    
            LAST_ACTIVE_TIME, BIND_DATA, LAST_LOAD_TIME, IS_OBSOLETE
     from v$sql where sql_id='bbjqg2f62vz6z';
 
SQL_ID        EXECUTIONS HASH_VALUE OLD_HASH_VALUE PLAN_HASH_VALUE CHILD_NUMBER R LAST_ACTIVE_TIME
------------- ---------- ---------- -------------- --------------- ------------ - --------------------
BIND_DATA                                LAST_LOAD_TIME             I      
-----------------------------------------    --------------------- - 
bbjqg2f62vz6z          1 2351824095     3130352737      3347831050            0 N 01-FEB-2013:18:26:59
BEDA0A200300510B9873000101C0021602C115   2013-02-01/18:27:00   N
 
 
SQL> select distinct t.sql_id, t.sql_text SQL_TEXT,  t.PLAN_HASH_VALUE, b.name BIND_NAME,  
            b.value_string  BIND_STRING, b.LAST_CAPTURED, b.WAS_CAPTURED, , b.CHILD_NUMBER
      from  v$sql t , v$sql_bind_capture b
      where b.value_string is not null and t.sql_id='bbjqg2f62vz6z' and 
            t.sql_id = b.sql_id and t.CHILD_NUMBER = b.CHILD_NUMBER
 
SQL_ID        SQL_TEXT
------------- ------------------------------------------------------------------------------------------
PLAN_HASH_VALUE BIND_NAME         BIND_STRING                LAST_CAPTURED        WAS  CHILD_NUMBER
--------------- ---------------      --------------------------      -------------------- ---  ------------
bbjqg2f62vz6z select max(object_id), min(object_id) from pt1 where created > sysdate - :vdate
     3347831050 :VDATE                   20                         01-FEB-2013:18:26:59 YES   0
 
 
 
SQL> exec :vdate := 200;
 
PL/SQL procedure successfully completed.
 
SQL> select max(object_id), min(object_id) from pt1 where created > sysdate - :vdate;
 
MAX(OBJECT_ID) MIN(OBJECT_ID)
-------------- --------------
         46479              2
 
SQL> select SQL_ID, EXECUTIONS, HASH_VALUE, OLD_HASH_VALUE, PLAN_HASH_VALUE, CHILD_NUMBER, REMOTE,    
            LAST_ACTIVE_TIME, BIND_DATA, LAST_LOAD_TIME, IS_OBSOLETE
     from v$sql where sql_id='bbjqg2f62vz6z';
 
SQL_ID        EXECUTIONS HASH_VALUE OLD_HASH_VALUE PLAN_HASH_VALUE CHILD_NUMBER R LAST_ACTIVE_TIME
------------- ---------- ---------- -------------- --------------- ------------ - --------------------
BIND_DATA                                LAST_LOAD_TIME             I      
-----------------------------------------    --------------------- - 
bbjqg2f62vz6z          2 2351824095     3130352737      3347831050            0 N 01-FEB-2013:18:30:47
BEDA0A200300510B9873000101C0021602C115   2013-02-01/18:27:00   N
 
 
SQL> select distinct t.sql_id, t.sql_text SQL_TEXT,  t.PLAN_HASH_VALUE, b.name BIND_NAME,  
            b.value_string  BIND_STRING, b.LAST_CAPTURED, b.WAS_CAPTURED, , b.CHILD_NUMBER
      from  v$sql t , v$sql_bind_capture b
      where b.value_string is not null and t.sql_id='bbjqg2f62vz6z' and 
            t.sql_id = b.sql_id and t.CHILD_NUMBER = b.CHILD_NUMBER
 
SQL_ID        SQL_TEXT
------------- ------------------------------------------------------------------------------------------
PLAN_HASH_VALUE BIND_NAME         BIND_STRING                LAST_CAPTURED        WAS  CHILD_NUMBER
--------------- ---------------      --------------------------      -------------------- ---  ------------
bbjqg2f62vz6z select max(object_id), min(object_id) from pt1 where created > sysdate - :vdate
     3347831050 :VDATE                   200                        01-FEB-2013:18:30:47 YES   0
 
 
SQL> exec dbms_stats.gather_table_stats('SCOTT','PT1');  /* Make cursor plan in cache invalid */
 
PL/SQL procedure successfully completed.
SQL> r
  1* select max(object_id), min(object_id) from pt1 where created > sysdate - :vdate
 
MAX(OBJECT_ID) MIN(OBJECT_ID)
-------------- --------------
         46479              2
 
SQL> select SQL_ID, EXECUTIONS, HASH_VALUE, OLD_HASH_VALUE, PLAN_HASH_VALUE, CHILD_NUMBER, REMOTE,    
            LAST_ACTIVE_TIME, BIND_DATA, LAST_LOAD_TIME, IS_OBSOLETE
     from v$sql where sql_id='bbjqg2f62vz6z';
 
SQL_ID        EXECUTIONS HASH_VALUE OLD_HASH_VALUE PLAN_HASH_VALUE CHILD_NUMBER R LAST_ACTIVE_TIME
------------- ---------- ---------- -------------- --------------- ------------ - --------------------
BIND_DATA                                LAST_LOAD_TIME             I      
-----------------------------------------    --------------------- - 
bbjqg2f62vz6z          3 2351824095     3130352737      3347831050            0 N 01-FEB-2013:18:33:38
BEDA0A200300510B9873000101C0021602C115   2013-02-01/18:27:00   N
 
 
SQL> select distinct t.sql_id, t.sql_text SQL_TEXT,  t.PLAN_HASH_VALUE, b.name BIND_NAME,  
            b.value_string  BIND_STRING, b.LAST_CAPTURED, b.WAS_CAPTURED, , b.CHILD_NUMBER
      from  v$sql t , v$sql_bind_capture b
      where b.value_string is not null and t.sql_id='bbjqg2f62vz6z' and 
            t.sql_id = b.sql_id and t.CHILD_NUMBER = b.CHILD_NUMBER
 
SQL_ID        SQL_TEXT
------------- ------------------------------------------------------------------------------------------
PLAN_HASH_VALUE BIND_NAME         BIND_STRING                LAST_CAPTURED        WAS  CHILD_NUMBER
--------------- ---------------      --------------------------      -------------------- ---  ------------
bbjqg2f62vz6z select max(object_id), min(object_id) from pt1 where created > sysdate - :vdate
     3347831050 :VDATE                   200                        01-FEB-2013:18:33:35 YES  0
 
SQL> r
  1* select max(object_id), min(object_id) from pt1 where created > sysdate - :vdate
 
MAX(OBJECT_ID) MIN(OBJECT_ID)
-------------- --------------
         46479              2
 
SQL> select SQL_ID, EXECUTIONS, HASH_VALUE, OLD_HASH_VALUE, PLAN_HASH_VALUE, CHILD_NUMBER, REMOTE,    
            LAST_ACTIVE_TIME, BIND_DATA, LAST_LOAD_TIME, IS_OBSOLETE
     from v$sql where sql_id='bbjqg2f62vz6z';
 
SQL_ID        EXECUTIONS HASH_VALUE OLD_HASH_VALUE PLAN_HASH_VALUE CHILD_NUMBER R LAST_ACTIVE_TIME
------------- ---------- ---------- -------------- --------------- ------------ - --------------------
BIND_DATA                                LAST_LOAD_TIME             I      
-----------------------------------------    --------------------- - 
bbjqg2f62vz6z          3 2351824095     3130352737      3347831050            0 N 01-FEB-2013:18:33:38
BEDA0A200300510B9873000101C0021602C115   2013-02-01/18:27:00   N
 
bbjqg2f62vz6z          1 2351824095     3130352737      3347831050            1 N 01-FEB-2013:18:34:59
BEDA0A200300510B9A53000101C0021602C203   2013-02-01/18:35:01   N
 
 
SQL> select distinct t.sql_id, t.sql_text SQL_TEXT,  t.PLAN_HASH_VALUE, b.name BIND_NAME,  
            b.value_string  BIND_STRING, b.LAST_CAPTURED, b.WAS_CAPTURED, , b.CHILD_NUMBER
      from  v$sql t , v$sql_bind_capture b
      where b.value_string is not null and t.sql_id='bbjqg2f62vz6z' and 
            t.sql_id = b.sql_id and t.CHILD_NUMBER = b.CHILD_NUMBER
 
SQL_ID        SQL_TEXT
------------- ------------------------------------------------------------------------------------------
PLAN_HASH_VALUE BIND_NAME         BIND_STRING                LAST_CAPTURED        WAS  CHILD_NUMBER
--------------- ---------------      --------------------------      -------------------- ---  ------------
bbjqg2f62vz6z select max(object_id), min(object_id) from pt1 where created > sysdate - :vdate
     3347831050 :VDATE                   200                        01-FEB-2013:18:34:59 YES   1
 
bbjqg2f62vz6z select max(object_id), min(object_id) from pt1 where created > sysdate - :vdate
     3347831050 :VDATE                   20                         01-FEB-2013:18:33:35 YES    0

March 1, 2013 by Fred Sim

Bind variable peeking in 10G and 11G

SQL ID with bind variable, when generate or refresh the execution plan, it would use the captured value of bind variable, this captured value of bind variables link to the SQL ID. Even value of bind variable value had been changed when execution plan be generated, it still look back to the captured value of bind variables. And the captured value of bind variable is controlled by “_cursor_bind_capture_interval” (900 seconds as default ) and re-capture triggered if the last capture time for the SQL ID is “_cursor_bind_capture_interval” .

Find out the value of captured bind variable :

========== Execute plan with Bind variable in 10g/11g ======================

SQL> select distinct sql_id,  t.sql_text SQL_TEXT,  t.PLAN_HASH_VALUE, b.name BIND_NAME,  b.value_string BIND_STRING, b.LAST_CAPTURED, b.WAS_CAPTURED
  2   from  v$sql t  join v$sql_bind_capture b  using (sql_id)
  3   where b.value_string is not null and sql_id='4k29quznbg6pa';

SQL_ID        SQL_TEXT                                          PLAN_HASH_VALUE  BIND_NAME
------------- ---------------------------------------------------   ---------------  ------------
BIND_STRING          LAST_CAPTURED        WAS
-------------------   ----------------      --------
4k29quznbg6pa select * from pt1 where created > sysdate - :vdate            1267018005 :VDATE
800                  23-JAN-2013:14:39:54  YES

=== changed :vdate value and captured after _ cursor_bind_capture_interval ==========

SQL> r
  1  select distinct sql_id,  t.sql_text SQL_TEXT,  t.PLAN_HASH_VALUE, b.name BIND_NAME,  b.value_string BIND_STRING, b.LAST_CAPTURED, b.WAS_CAPTURED
    from  v$sql t  join v$sql_bind_capture b  using (sql_id)
 where b.value_string is not null and sql_id='4k29quznbg6pa'
 
SQL_ID        SQL_TEXT                                          PLAN_HASH_VALUE  BIND_NAME
------------- ---------------------------------------------------   ---------------  ------------
BIND_STRING          LAST_CAPTURED        WAS
-------------------   ----------------      --------
4k29quznbg6pa select * from pt1 where created > sysdate - :vdate            1267018005 :VDATE
900                  23-JAN-2013:15:08:51 YES

In ORACLE 10G, for ALL bind variables in “where” clause with ANY operator on the column(s), current captured bind variables would not trigger the new execution plan or plan switch even value(s) had been changed and existing execution plan might not be optimized. Execution plan switching or re-gen only control by “invalidation interval” as described above. Below examples is to show no dependency on the captured value and execution plan.

=== Can find bind data in v$SQL also, but in binary format =====

=== Make two diff SQL ID with add <space> in front, to find out how the existing plan changes ===

SQL> r

  1  select SQL_ID, OBJECT_STATUS, IS_OBSOLETE, FIRST_LOAD_TIME, LAST_LOAD_TIME, LAST_ACTIVE_TIME, BIND_DATA, PLAN_HASH_VALUE  from v$sql
  2* where sql_id in ('4k29quznbg6pa','50msx60hgkxjt')
 
SQL_ID        OBJECT_STATUS       I FIRST_LOAD_TIME       LAST_LOAD_TIME        LAST_ACTIVE_TIME
------------- ------------------- - ----------------      --------------------  --------------------- 
BIND_DATA                                             PLAN_HASH_VALUE      
--------------------------------------------------------   ----------------  
50msx60hgkxjt VALID               N 2013-01-23/12:11:01   2013-01-23/12:11:01   23-JAN-2013:15:05:54                                                    
BEDA0A20030050FF8BCC000101C0021602C20A                2821530121     
 
4k29quznbg6pa VALID               N 2013-01-23/14:39:56   2013-01-23/14:39:56   23-JAN-2013:15:08:54                                                       
BEDA0A20030050FF8C83000101C0021602C20A                1267018005

SQL> select * from table(dbms_xplan.display_cursor('50msx60hgkxjt',null,'advanced'));
 PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------
SQL_ID  50msx60hgkxjt, child number 0
-------------------------------------
select * from pt1 where created > sysdate - :vdate
 
Plan hash value: 2821530121
 
----------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |       |       |     3 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| PT1      |     1 |    93 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | PT1_IDX1 |     1 |       |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$1 / PT1@SEL$1
   2 - SEL$1 / PT1@SEL$1
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
      OPT_PARAM('_complex_view_merging' 'false')
      OPT_PARAM('star_transformation_enabled' 'true')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "PT1"@"SEL$1" ("PT1"."CREATED"))
      END_OUTLINE_DATA
  */
 
Peeked Binds (identified by position):
--------------------------------------
   1 - :VDATE (NUMBER): 10
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("CREATED">SYSDATE@!-:VDATE)
 
Column Projection Information (identified by operation id):
-----------------------------------------------------------
 
   1 - "PT1"."OWNER"[VARCHAR2,30], "PT1"."OBJECT_NAME"[VARCHAR2,128],
       "PT1"."SUBOBJECT_NAME"[VARCHAR2,30], "PT1"."OBJECT_ID"[NUMBER,22],
       "PT1"."DATA_OBJECT_ID"[NUMBER,22], "PT1"."OBJECT_TYPE"[VARCHAR2,19],
       "CREATED"[DATE,7], "PT1"."LAST_DDL_TIME"[DATE,7],
       "PT1"."TIMESTAMP"[VARCHAR2,19], "PT1"."STATUS"[VARCHAR2,7],
       "PT1"."TEMPORARY"[VARCHAR2,1], "PT1"."GENERATED"[VARCHAR2,1],
       "PT1"."SECONDARY"[VARCHAR2,1]
   2 - "PT1".ROWID[ROWID,10], "CREATED"[DATE,7]
 
 
57 rows selected.
 
SQL> r
  1* select * from table(dbms_xplan.display_cursor('4k29quznbg6pa',null,'advanced'))
 
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------
SQL_ID  4k29quznbg6pa, child number 0
-------------------------------------
 select * from pt1 where created > sysdate - :vdate
 
Plan hash value: 1267018005
 
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |   129 (100)|          |
|*  1 |  TABLE ACCESS FULL| PT1  | 45031 |  4089K|   129   (4)| 00:00:02 |
--------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$1 / PT1@SEL$1
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
      OPT_PARAM('_complex_view_merging' 'false')
      OPT_PARAM('star_transformation_enabled' 'true')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "PT1"@"SEL$1")
      END_OUTLINE_DATA
  */
 
Peeked Binds (identified by position):
--------------------------------------
 
   1 - :VDATE (NUMBER): 800
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("CREATED">SYSDATE@!-:VDATE)
 
Column Projection Information (identified by operation id):
-----------------------------------------------------------
 
   1 - "PT1"."OWNER"[VARCHAR2,30], "PT1"."OBJECT_NAME"[VARCHAR2,128],
       "PT1"."SUBOBJECT_NAME"[VARCHAR2,30], "PT1"."OBJECT_ID"[NUMBER,22],
       "PT1"."DATA_OBJECT_ID"[NUMBER,22], "PT1"."OBJECT_TYPE"[VARCHAR2,19],
       "CREATED"[DATE,7], "PT1"."LAST_DDL_TIME"[DATE,7],
       "PT1"."TIMESTAMP"[VARCHAR2,19], "PT1"."STATUS"[VARCHAR2,7],
       "PT1"."TEMPORARY"[VARCHAR2,1], "PT1"."GENERATED"[VARCHAR2,1],
       "PT1"."SECONDARY"[VARCHAR2,1]
 
 
54 rows selected.

In 10G, after object statistics gathering, when SQL re-run after “invalid interval”, SQL plan would switch to existing plan first and final generate the new version of plan (may just duplicated from history). All the bind variable peeking value in history plan would be updated to currently even execution plan actually generated using old peeking value.

============== bind peeking in 10G with statistics re-gathering =========

1 select SQL_ID, EXECUTIONS, HASH_VALUE, PLAN_HASH_VALUE, CHILD_NUMBER, REMOTE, LAST_ACTIVE_TIME, BIND_DATA, LAST_LOAD_TIME, IS_OBSOLETE

  2* from v$sql where sql_id ='276x596m9384u'
  3  ;
 
SQL_ID        EXECUTIONS HASH_VALUE PLAN_HASH_VALUE CHILD_NUMBER R LAST_ACTIVE_TIME     
------------- ---------- ---------- --------------- ------------ - -------------------- 
BIND_DATA                                LAST_LOAD_TIME       I
---------------------------------------- -------------------- -
276x596m9384u         10 2794561690      2734209795            0 N 15-FEB-2013:16:04:27 
BEDA0A200300511DEC0B000101C0021602C102   2013-02-15/11:04:29  N
276x596m9384u         10 2794561690      3214516708            1 N 15-FEB-2013:16:05:51 
BEDA0A200300511DEC5E000101C0021602C102   2013-02-15/11:23:59  N
276x596m9384u          6 2794561690      2734209795            2 N 15-FEB-2013:16:15:36 
BEDA0A200300511DEEA8000101C0021602C102   2013-02-15/11:57:05  N
276x596m9384u          9 2794561690      3214516708            3 N 15-FEB-2013:16:13:42 
BEDA0A200300511DEE36000101C0021602C102   2013-02-15/15:11:50  N
276x596m9384u          2 2794561690      2734209795            4 N 15-FEB-2013:16:09:06 
BEDA0A200300511DED22000101C0021602C102   2013-02-15/15:59:23  N
276x596m9384u          1 2794561690      3214516708            5 N 15-FEB-2013:17:46:38 
BEDA0A200300511E03FE000101C0021602C102   2013-02-15/17:46:39  N
 
6 rows selected.
 
r
  1  select distinct t.CHILD_NUMBER,t.PLAN_HASH_VALUE, b.name BIND_NAME,  b.value_string BIND_STRING, b.LAST_CAPTURED, b.WAS_CAPTURED
  2  from  v$sql t , v$sql_bind_capture b
  3  where b.value_string is not null and t.sql_id= '276x596m9384u'
  4*    and t.sql_id=b.sql_id and t.CHILD_NUMBER = b.CHILD_NUMBER order by b.LAST_CAPTURED
 
CHILD_NUMBER PLAN_HASH_VALUE BIND_NAME  BIND_STRIN LAST_CAPTURED        WAS
------------ --------------- ---------- ---------- -------------------- ---
           0      2734209795 :VID       1          15-FEB-2013:16:04:27 YES
           1      3214516708 :VID       1          15-FEB-2013:16:05:50 YES
           4      2734209795 :VID       1          15-FEB-2013:16:09:06 YES
           3      3214516708 :VID       1          15-FEB-2013:16:13:42 YES
           2      2734209795 :VID       1          15-FEB-2013:16:15:36 YES
           5      3214516708 :VID       1          15-FEB-2013:17:46:38 YES
 
6 rows selected.

In 11G, Execution plan version processing had description as above, it would not loop back to existing versions/children plan in order to generate new version of plan. When bind variable peeking happens in 11G, 11G had improved the optimizer to handle this. It will identify whether such bind variable peeking is “sensitive” or not. If it is “sensitive”, Optimizer will check the current value of variables and compare with history of “peeking” values to pick up the correct plan or generate new plan. If bind variable is not “sensitive”, it will use last peeking value and execution plan until new version of plan handling be trigger, and at that time, the current value of bind variables will be used to generate new version of execution plan, and existing plan history would not be changed on the bind variables peeking value, the history of plan will remain the “peeking” values.

Below is the case of NOT sensitive “Bind Variable Peeking” in 11G (11.2.0.2), The execution plans will not switch or re-gen until trigger (statistics gathering) for re-gen (not switching) happen.

Change vtyp value on the sequence 0, 1,2,0 , re-run the SQL after bind variable changed, and with gather statistics on the object_list, re-run SQL after “ invalid interval”

Type_ID is the column with stewed data and histogram statistics. When column histogram statistics is not captured, then the execution plan will not changed by bind variable value changed and statistics re-gathered. Repeated run below in one session , Check version and bind variable values.

select max(object_id) from object_list where type_id = :vtyp – 1

 SQL> r
  1  select SQL_ID, EXECUTIONS, HASH_VALUE, OLD_HASH_VALUE, PLAN_HASH_VALUE, CHILD_NUMBER, REMOTE,
  2  LAST_ACTIVE_TIME, BIND_DATA, LAST_LOAD_TIME, IS_OBSOLETE,IS_BIND_SENSITIVE, IS_BIND_AWARE, IS_SHAREABLE
  3* from v$sql where sql_id='9nhany360wxs1'
 
SQL_ID        EXECUTIONS HASH_VALUE OLD_HASH_VALUE PLAN_HASH_VALUE CHILD_NUMBER R LAST_ACTIVE_TIME
------------- ---------- ---------- -------------- --------------- ------------ - --------------------
BIND_DATA                                LAST_LOAD_TIME                    I I I I
----------------------------------------     --------------------------    - - - -
9nhany360wxs1          2 3423500033     4199617170       599033881            0 N 26-FEB-2013:11:11:41
BEDA0B200100512C27EC000101C002160180            2013-02-26/10:48:39           N N N Y
 
9nhany360wxs1          4 3423500033     4199617170        46243151            1 N 26-FEB-2013:11:17:55
BEDA0B200100512C2801000101C0021602C102   2013-02-26/11:12:01           N N N Y
 
9nhany360wxs1          3 3423500033     4199617170        46243151            2 N 26-FEB-2013:11:30:26
BEDA0B200100512C2AA5000101C0021602C102   2013-02-26/11:23:16           N N N Y
 
9nhany360wxs1          3 3423500033     4199617170       599033881            3 N 26-FEB-2013:11:40:00
BEDA0B200100512C2D58000101C0021602C103   2013-02-26/11:34:49           N N N Y
 
9nhany360wxs1          1 3423500033     4199617170        46243151            4 N 26-FEB-2013:11:42:17
BEDA0B200100512C2F19000101C002160180            2013-02-26/11:42:17           N N N Y
 
 
SQL> select distinct t.CHILD_NUMBER,t.PLAN_HASH_VALUE, b.name BIND_NAME,  b.value_string BIND_STRING, b.LAST_CAPTURED, b.WAS_CAPTURED
  2  from  v$sql t , v$sql_bind_capture b
  3  where b.value_string is not null and t.sql_id= '9nhany360wxs1'
  4  and t.sql_id=b.sql_id and t.CHILD_NUMBER = b.CHILD_NUMBER order by b.LAST_CAPTURED;
 
CHILD_NUMBER PLAN_HASH_VALUE BIND_NAME   BIND_STRING          LAST_CAPTURED        WAS
------------ --------------- ------------       -------------------- -------------------- ---
           0       599033881 :VTYP       0                    26-FEB-2013:11:11:40 YES
           1        46243151 :VTYP       1                    26-FEB-2013:11:12:01 YES
           2        46243151 :VTYP       1                    26-FEB-2013:11:23:17 YES
           3       599033881 :VTYP       2                    26-FEB-2013:11:34:48 YES
           4        46243151 :VTYP       0                    26-FEB-2013:11:42:17 YES

Even 11G had improved on the bind peeking problem in 10G, but the limition for new imrovment still cause many problem special with histogram statistics.

Appendix   Test case on ORACLE 10G for stewed data and execution plan 
 
alter system set "_optimizer_invalidation_period" = 60 scope=memory;
 
SQL> r
  1* select max(object_id), min(object_id) from pt1 where created > sysdate - 20
MAX(OBJECT_ID) MIN(OBJECT_ID)
-------------- --------------
         46479             20
 
Pause 1 minute
 
SQL> r
  1* select max(object_id), min(object_id) from pt1 where created > sysdate - 20
 
MAX(OBJECT_ID) MIN(OBJECT_ID)
-------------- --------------
         46479             20
 
Pause 1 minute
 
SQL> r
  1* select max(object_id), min(object_id) from pt1 where created > sysdate - 20
 
MAX(OBJECT_ID) MIN(OBJECT_ID)
-------------- --------------
         46479             20
 
SQL> r
  1  select SQL_ID, EXECUTIONS, HASH_VALUE, PLAN_HASH_VALUE, CHILD_NUMBER, REMOTE, LAST_ACTIVE_TIME, LAST_LOAD_TIME, IS_OBSOLETE
  2* from v$sql where sql_id='9fx3nzvp2scyn'
 
SQL_ID        EXECUTIONS HASH_VALUE PLAN_HASH_VALUE CHILD_NUMBER R LAST_ACTIVE_TIME      LAST_LOAD_TIME            I
------------- ---------- ---------- --------------- ------------ - --------------------  ------------------        -
9fx3nzvp2scyn          5 3928765396      3347831050            0 N 01-FEB-2013:17:57:34  2013-02-01/17:42:04   N                        9fx3nzvp2scyn          3 3928765396      3347831050            1 N 01-FEB-2013:18:01:01  2013-02-01/17:46:34   N                                                       9fx3nzvp2scyn          2 3928765396      3347831050            2 N 01-FEB-2013:17:59:10  2013-02-01/17:54:35   N                                                  9fx3nzvp2scyn          1 3928765396      3347831050            3 N 01-FEB-2013:18:03:13  2013-02-01/18:03:14   N                                                         
 
Now, all the child plans are same, in this case using “index” since created>sysdate -20 only has 3 records
Update the table to exchange the histogram for each data point.
 
 
SQL> update pt1 set created = sysdate - 40 where created > sysdate - 20;
3 rows updated.

SQL> commit;
Commit complete.
 
SQL> update pt1 set created = sysdate -10 where created > sysdate - 40;
45028 rows updated.

SQL> commit;
exec dbms_stats.gather_table_stats('SCOTT','PT1');
 
The correct plan should be changed from “index scan” to full table scan since 45028 of 45031 records need be accessed, however, the correct execution plan only be generated after fifth call (each call pause for 1  minute) !!
 
SQL> r
  1* select max(object_id), min(object_id) from pt1 where created > sysdate - 20
MAX(OBJECT_ID) MIN(OBJECT_ID)
-------------- --------------
         46478              2
 
Pause 1 minute
 
SQL> r
  1* select max(object_id), min(object_id) from pt1 where created > sysdate - 20
 
MAX(OBJECT_ID) MIN(OBJECT_ID)
-------------- --------------
         46478              2
 
Pause 1 minute

SQL> r
  1* select max(object_id), min(object_id) from pt1 where created > sysdate - 20
 
MAX(OBJECT_ID) MIN(OBJECT_ID)
-------------- --------------
         46478              2
 
Pause 1 minute
 
SQL> r
  1* select max(object_id), min(object_id) from pt1 where created > sysdate - 20
 
MAX(OBJECT_ID) MIN(OBJECT_ID)
-------------- --------------
         46478              2
 
Pause 1 minute
 
SQL> r
  1* select max(object_id), min(object_id) from pt1 where created > sysdate - 20
 
MAX(OBJECT_ID) MIN(OBJECT_ID)
-------------- --------------
         46478              2
 
===== check execution plans history ===== 
SQL> r
  1  select SQL_ID, EXECUTIONS, HASH_VALUE, OLD_HASH_VALUE, PLAN_HASH_VALUE, CHILD_NUMBER, REMOTE, LAST_ACTIVE_TIME, BIND_DATA, LAST_LOAD_TIME, IS_OBSOLETE
  2* from v$sql where sql_id='9fx3nzvp2scyn'
 
SQL_ID        EXECUTIONS HASH_VALUE PLAN_HASH_VALUE CHILD_NUMBER R LAST_ACTIVE_TIME      LAST_LOAD_TIME   I
------------- ---------- ---------- --------------  ------------ - --------------------  -------------------   -
9fx3nzvp2scyn          6 3928765396      3347831050            0 N 01-FEB-2013:18:09:32  2013-02-01/17:42:04       N                                               
9fx3nzvp2scyn          4 3928765396      3347831050            1 N 01-FEB-2013:18:13:05  2013-02-01/17:46:34       N
9fx3nzvp2scyn          3 3928765396      3347831050            2 N 01-FEB-2013:18:16:02  2013-02-01/17:54:35       N
9fx3nzvp2scyn          2 3928765396      3347831050            3 N 01-FEB-2013:18:14:11  2013-02-01/18:03:14       N
9fx3nzvp2scyn          1 3928765396      3214516708            4 N 01-FEB-2013:18:17:44  2013-02-01/18:17:45       N

March 1, 2013 by Fred Sim

SQL execution plan in cache and objects statistics gathering

Before ORACLE 10G, object statistics gathering will immediately invalid all the underline execution plans in cache, plan re-generate will trigger when SQL be called again. After ORACLE 10G, underline parameter “_optimizer_invalidation_period” will control when the execution plan can be expired.

More frequency of object statistics gathering, more versions of SQL plan in cache. In 10G (tested in 10.2.0.3), Same plan may has diff version or child number of plan. Testing showed : For one SQL ID, any underline object statistics will trigger the optimizer re-gen the execution plan, and it was controlled by “_optimizer_invalidation_period” with default value = 5 hours.

After object statistics gathering with no_validation = AUTO (default), when underline (affected) SQL be called, Optimizer will pick latest valid execution plan and run. If the running time exceed the “_optimizer_invalidation_period” then after running the latest plan will be marked as “expired”. In 11G, this “expired” plan will not be used in future (except plan had dependency on bind variable) ; in 10G, it will not be expired and just marked as “switch plan/re-gen plan” in next calling.

In 10G, execution plans in cache will be used at least once and new children (new version of plan) will be generated in final. Switching or Re-gen will happen after “_optimizer_invalidation_period” from last calling time if plan parse timing is before underline objects statistics gathering.

In 11G, latest execution plans (need match to bind variable peeking value if have) will be call by default, and it will “marked” as “expired” if calling time exceed “_optimizer_invalidation_period” after object statistics gathering. New version of execution will be generated if re-run SQL after “_optimizer_invalidation_period” from “marked” “expired”.

If object statistics gathering with option no_validation = false, then underline execution plan(s) will flush out from cache. New plan will be re-gen in next time call.

Cases in below all tested on the columns with stewed values , and with histogram statistics. In 10G, after statistics gathering, new or best optimized plan may need re-run query more than once (depended how many children in the cache for this SQL), and some old bad plan in cache will be call at least once before right plan be used !! In 10G, it may need extra handle to retire the SQL plan in cache more frequently or config gathering statistics with no_validation = false (immediate invalidate plan after statistics be gathered).

In the tests, we changed the invalid period from 5 hours to 1 minute.

alter system set “_optimizer_invalidation_period” = 60 scope=memory

And gather table statistics

exec dbms_stats.gather_table_stats('SCOTT','PT1',cascade=>true);

Then, re-run the following SQL until new child of execution plan be generated.

SQL> r
  1* select max(object_id), min(object_id) from pt1 where created > sysdate - 20

MAX(OBJECT_ID) MIN(OBJECT_ID)
-------------- --------------
         46479             20

During the each call, monitored the V$SQL for this SQL ID.

………………………………

In 10G, Repeated same SQL until new version generated, if re-call SQL with in 1 minute (“_optimizer_invalidation_period”), then plan would not switch. If the data changes to diff histogram, and optimized plan should be “3214516708”, but each “3347831050” with diff child_number cached in shared pool would be used at least once (depend on how many times called during “_optimizer_invalidation_period”). It would be worse off when bind variable peeking involved. See appendix A for detail in ORACLE 10.2.0.3

SQL> r
  1  select SQL_ID, EXECUTIONS, HASH_VALUE, PLAN_HASH_VALUE, CHILD_NUMBER, REMOTE, LAST_ACTIVE_TIME, LAST_LOAD_TIME, IS_OBSOLETE
  2* from v$sql where sql_id='9fx3nzvp2scyn'
 
SQL_ID        EXECUTIONS HASH_VALUE PLAN_HASH_VALUE CHILD_NUMBER R LAST_ACTIVE_TIME      LAST_LOAD_TIME       I
------------- ---------- ---------- --------------- ------------ - --------------------  ------------------   -
9fx3nzvp2scyn          7 3928765396      3347831050            0 N 04-FEB-2013:17:47:11  2013-02-01/17:42:04  N
9fx3nzvp2scyn          7 3928765396      3347831050            1 N 04-FEB-2013:17:46:08  2013-02-01/17:46:34  N
9fx3nzvp2scyn          4 3928765396      3347831050            2 N 04-FEB-2013:17:45:11  2013-02-01/17:54:35  N
9fx3nzvp2scyn          3 3928765396      3347831050            3 N 04-FEB-2013:17:44:17  2013-02-01/18:03:14  N
9fx3nzvp2scyn          4 3928765396      3214516708            4 N 04-FEB-2013:17:41:32  2013-02-01/18:17:45  N
9fx3nzvp2scyn          6 3928765396      3347831050            5 N 05-FEB-2013:09:26:46  2013-02-04/17:48:58  N
 
6 rows selected.
 
== after 9 times calls , 3 times called with 1 minute == 
 
SQL> r
  1  select SQL_ID, EXECUTIONS, HASH_VALUE, PLAN_HASH_VALUE, CHILD_NUMBER, REMOTE, LAST_ACTIVE_TIME, LAST_LOAD_TIME, IS_OBSOLETE
  2* from v$sql where sql_id='9fx3nzvp2scyn'
 
SQL_ID        EXECUTIONS HASH_VALUE PLAN_HASH_VALUE CHILD_NUMBER R LAST_ACTIVE_TIME      LAST_LOAD_TIME       I
------------- ---------- ---------- --------------- ------------ - --------------------  ------------------   -
9fx3nzvp2scyn          8 3928765396      3347831050            0 N 05-FEB-2013:09:46:29  2013-02-01/17:42:04  N
9fx3nzvp2scyn         10 3928765396      3347831050            1 N 05-FEB-2013:09:40:41  2013-02-01/17:46:34  N
9fx3nzvp2scyn          5 3928765396      3347831050            2 N 05-FEB-2013:09:39:32  2013-02-01/17:54:35  N
9fx3nzvp2scyn          4 3928765396      3347831050            3 N 05-FEB-2013:09:37:04  2013-02-01/18:03:14  N
9fx3nzvp2scyn          5 3928765396      3214516708            4 N 05-FEB-2013:09:35:01  2013-02-01/18:17:45  N
9fx3nzvp2scyn          7 3928765396      3347831050            5 N 05-FEB-2013:09:48:47  2013-02-04/17:48:58  N
9fx3nzvp2scyn          1 3928765396      3214516708            6 N 05-FEB-2013:09:49:50  2013-02-05/09:49:51  N

7 rows selected.

In 11G, no loop on the older version of execution plan, and It had improved on the bind variable peeking too. The LAST_ACTIVE_TIME in 11G always happened before LAST_LOAD_TIME of next version.

SQL> r

  1  select SQL_ID, EXECUTIONS, HASH_VALUE, PLAN_HASH_VALUE, CHILD_NUMBER, REMOTE, LAST_ACTIVE_TIME, LAST_LOAD_TIME, IS_OBSOLETE
  2* from v$sql where sql_id='1s1033aqhf1q4'
 
SQL_ID        EXECUTIONS HASH_VALUE PLAN_HASH_VALUE CHILD_NUMBER R LAST_ACTIVE_TIME      LAST_LOAD_TIME         I
------------- ---------- ---------- --------------- ------------ - --------------------  -------------------    -
1s1033aqhf1q4          4 2902918852       599033881            0 N 05-FEB-2013:16:42:06  2013-02-05/16:39:47    N
1s1033aqhf1q4          3 2902918852       599033881            1 N 05-FEB-2013:17:08:16  2013-02-05/16:43:57    N
1s1033aqhf1q4          3 2902918852      3077296115            2 N 05-FEB-2013:17:20:05  2013-02-05/17:18:40    N

== after twice calls ==

SQL> r

1 select SQL_ID, EXECUTIONS, HASH_VALUE, PLAN_HASH_VALUE, CHILD_NUMBER, REMOTE, LAST_ACTIVE_TIME, LAST_LOAD_TIME, IS_OBSOLETE

2* from v$sql where sql_id=’1s1033aqhf1q4′

SQL_ID        EXECUTIONS HASH_VALUE PLAN_HASH_VALUE CHILD_NUMBER R LAST_ACTIVE_TIME      LAST_LOAD_TIME         I
------------- ---------- ---------- --------------- ------------ - --------------------  -------------------    -
1s1033aqhf1q4          4 2902918852       599033881            0 N 05-FEB-2013:16:42:06  2013-02-05/16:39:47    N
1s1033aqhf1q4          3 2902918852       599033881            1 N 05-FEB-2013:17:08:16  2013-02-05/16:43:57    N
1s1033aqhf1q4          4 2902918852      3077296115            2 N 05-FEB-2013:17:22:09  2013-02-05/17:18:40    N
1s1033aqhf1q4          1 2902918852      3077296115            3 N 05-FEB-2013:17:24:27  2013-02-05/17:24:26    N

So, When find execution plan changed due to the statistics idel, and manual gathering the statistics, if you do not invalidate the execution immediate, the new plan or correct plan may not be generated or re-use. In 10G, it even need goes thru every version of execution plan in invalidation period which default value is 5 hours, before create the new version plan.

February 28, 2013 by Fred Sim

Query with remote objects access and join

How the SQL look like in remote DB ?

Query with remote objects access and join with local objects called as “distributed query”, ORACLE optimizer will re-arrange the remote objects access and forms self-contain sub query and pass to remote database via database link.

2. New feature in 11G for remote/distributed query

It can show the execute plan for “full remote statements.
In the local execute plan, it shows the exact SQL statements of self-contain sub-query than passed to remote DB in transformed format.

3. SQL Transform in remote DB

When distributed query is not transferred as “Full remote statement”, each of remote accesses will turn to one connection(may share same session if access in sequence), and plan path will re-org the remote sub-query and send to remote DB, if the joining for remote objects can combine into one sub-query = “full remote statement”, then optimizer will send the full sub-query to remote DB to let remote objects join happened in remote DB internal. Otherwise, joining for remote DB objects only can be happened in local.

First : distributed query is similar as parallel query, however it distributes processes between instances/databases instead of within instance/database as parallel query.

Second : optimizer will re-org the distribute query into self-contain query to reduce the query result transfer between DB/Instances.

===== Full remote statement ========

 SQL> select a.owner, a.object_name, b.object_id, b.created from view_1@db1 a, view_2@db1 b
   2  where a.object_id = b.object_id;
8011 rows selected.
Elapsed: 00:00:45.75

Execution Plan

----------------------------------------------------------
Plan hash value: 2810430781
-----------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name       | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT REMOTE       |            |     1 |   240 |   806   (1)| 00:00:10 |        |
|*  1 |  FILTER                       |            |       |       |            |          |        |
|   2 |   NESTED LOOPS                |            |       |       |            |          |        |
|   3 |    NESTED LOOPS               |            |     3 |   720 |   386   (2)| 00:00:05 |        |
|   4 |     NESTED LOOPS              |            |     3 |   603 |   377   (2)| 00:00:05 |        |
|*  5 |      HASH JOIN                |            |     3 |   510 |   371   (2)| 00:00:05 |        |
|   6 |       VIEW                    | VW_SQ_1    |  3996 |   359K|    31   (4)| 00:00:01 | TIOID2 |
|   7 |        HASH GROUP BY          |            |  3996 |   117K|    31   (4)| 00:00:01 |        |
|   8 |         TABLE ACCESS FULL     | TEST_1     |  7995 |   234K|    30   (0)| 00:00:01 | TIOID2 |
|*  9 |       HASH JOIN               |            |  7994 |   608K|   339   (1)| 00:00:05 |        |
|  10 |        TABLE ACCESS FULL      | TEST_1     |  7995 |   265K|    30   (0)| 00:00:01 | TIOID2 |
|  11 |        TABLE ACCESS FULL      | TEST_R     | 78662 |  3380K|   308   (1)| 00:00:04 | TIOID2 |
|* 12 |      INDEX RANGE SCAN         | IND_TEST_2 |     1 |    31 |     2   (0)| 00:00:01 | TIOID2 |
|* 13 |     INDEX RANGE SCAN          | IND_TEST_2 |     1 |       |     2   (0)| 00:00:01 | TIOID2 |
|  14 |    TABLE ACCESS BY INDEX ROWID| TEST_2     |     1 |    39 |     3   (0)| 00:00:01 | TIOID2 |
|  15 |   HASH UNIQUE                 |            |     1 |    20 |   309   (1)| 00:00:04 |        |
|* 16 |    TABLE ACCESS FULL          | TEST_R     |     1 |    20 |   308   (1)| 00:00:04 | TIOID2 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("A"."LAST_DDL_TIME"= (SELECT DISTINCT "LAST_DDL_TIME" FROM TEST_B."TEST_R" "C"
              WHERE "C"."OBJECT_ID"=:B1 AND "C"."OWNER"=:B2))
   5 - access("A"."LAST_DDL_TIME"="MAX(LAST_DDL_TIME)" AND "ITEM_1"="A"."OBJECT_NAME" AND
              "ITEM_2"="A"."OWNER")
   9 - access("A"."OBJECT_ID"="A"."OBJECT_ID")
  12 - access("A"."OWNER"="B"."OWNER" AND "A"."OBJECT_NAME"="B"."OBJECT_NAME")
  13 - access("A"."OWNER"="B"."OWNER" AND "A"."OBJECT_NAME"="B"."OBJECT_NAME")
  16 - filter("C"."OBJECT_ID"=:B1 AND "C"."OWNER"=:B2)
Note
-----
   - fully remote statement
Statistics
----------------------------------------------------------
        181  recursive calls
          1  db block gets
         29  consistent gets
          3  physical reads
        440  redo size
     241876  bytes sent via SQL*Net to client
       6398  bytes received via SQL*Net from client
        536  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       8011  rows processed

===== reformat the self-contain sub remote query by optimizer ======

SQL> select a.owner, a.object_name, b.object_id, b.created, c.id from view_1@db1 a, view_2@db1 b, t1 c
  2  where a.object_id = b.object_id and (a.object_id=c.id or c.id=b.object_id
  3  and a.created < sysdate - 100;
4700 rows selected.
Elapsed: 00:01:31.27

Execution Plan
----------------------------------------------------------
Plan hash value: 1092617826
----------------------------------------------------------------------------------------------
| Id  | Operation           | Name   | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |        |  4130 |   496K|   839   (4)| 00:00:04 |        |      |
|   1 |  CONCATENATION      |        |       |       |            |          |        |      |
|*  2 |   HASH JOIN         |        |  3933 |   472K|   419   (4)| 00:00:02 |        |      |
|   3 |    TABLE ACCESS FULL| T1     |  1000 |  4000 |     3   (0)| 00:00:01 |        |      |
|   4 |    REMOTE           |        |  3933 |   372K|   211   (6)| 00:00:01 |    DB1 | R->S |
|*  5 |   HASH JOIN         |        |   197 | 24231 |   419   (4)| 00:00:02 |        |      |
|   6 |    TABLE ACCESS FULL| T1     |  1000 |  4000 |     3   (0)| 00:00:01 |        |      |
|*  7 |    HASH JOIN        |        |  3933 |   457K|   416   (4)| 00:00:02 |        |      |
|   8 |     REMOTE          | VIEW_1 |  3933 |   372K|   211   (6)| 00:00:01 |    DB1 | R->S |
|   9 |     REMOTE          | VIEW_2 | 78664 |  1690K|   203   (2)| 00:00:01 |    DB1 | R->S |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("C"."ID"="B"."OBJECT_ID")
   5 - access("A"."OBJECT_ID"="C"."ID")
       filter(LNNVL("C"."ID"="B"."OBJECT_ID"))
   7 - access("A"."OBJECT_ID"="B"."OBJECT_ID")
Remote SQL Information (identified by operation id):
----------------------------------------------------
   4 - SELECT "A1"."OWNER","A1"."OBJECT_NAME","A1"."OBJECT_ID","A1"."OBJECT_ID","A1"."
       CREATED","A2"."OBJECT_ID","A2"."CREATED","A2"."OBJECT_ID","A2"."OBJECT_ID" FROM
       "VIEW_1" "A1","VIEW_2" "A2" WHERE "A1"."OBJECT_ID"="A2"."OBJECT_ID" AND
       "A1"."CREATED"<SYSDATE@!-100 (accessing 'DB1' )
   8 - SELECT "OWNER","OBJECT_NAME","OBJECT_ID","CREATED" FROM "VIEW_1" "A" WHERE
       "CREATED"<:1-:2 (accessing 'DB1' )
   9 - SELECT "OBJECT_ID","CREATED" FROM "VIEW_2" "B" (accessing 'DB1' )
Statistics
----------------------------------------------------------
        104  recursive calls
          0  db block gets
         33  consistent gets
          8  physical reads
          0  redo size
     118571  bytes sent via SQL*Net to client
       3967  bytes received via SQL*Net from client
        315  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       4700  rows processed

4. Re-write query into self-contain sub query for remote access and join

To avoid the multiplex access remote database, re-write the query and manually enforce Self-contain sub query for remote access is normal practise, however it may not enough. Optimizer still need compare cost to decide merge or no-merge sub query. Force optimizer does not merge self-contain remote sub query, NO_MERGE hint may need be add-in.

When optimizer generated self-contain remote sub query, if remote result still need multiplex join with local objects, then multiplex remote session still be created and return back same result set from remote DB to local DB. (for “or” operator, the session may be only one in remote, but fetch result twice, no cache in local ).

 ==== coding query using self-contain sub query for remote access === 
SQL> select d.owner, d.object_name, d.object_id, d.created, c.id 2  from t1 c,
        (select a.owner, a.object_name, b.object_id, b.created, a.object_id a_id, b.object_id b_id
        from view_1@db1 a, view_2@db1 b
        where a.object_id = b.object_id and a.created < sysdate - 100) d
where c.id = d.a_id or c.id=d.b_id;
4700 rows selected.
Elapsed: 00:01:31.31

Execution Plan
----------------------------------------------------------
Plan hash value: 1092617826
----------------------------------------------------------------------------------------------
| Id  | Operation           | Name   | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |        |  4130 |   496K|   839   (4)| 00:00:04 |        |      |
|   1 |  CONCATENATION      |        |       |       |            |          |        |      |
|*  2 |   HASH JOIN         |        |  3933 |   472K|   419   (4)| 00:00:02 |        |      |
|   3 |    TABLE ACCESS FULL| T1     |  1000 |  4000 |     3   (0)| 00:00:01 |        |      |
|   4 |    REMOTE           |        |  3933 |   372K|   211   (6)| 00:00:01 |    DB1 | R->S |
|*  5 |   HASH JOIN         |        |   197 | 24231 |   419   (4)| 00:00:02 |        |      |
|   6 |    TABLE ACCESS FULL| T1     |  1000 |  4000 |     3   (0)| 00:00:01 |        |      |
|*  7 |    HASH JOIN        |        |  3933 |   457K|   416   (4)| 00:00:02 |        |      |
|   8 |     REMOTE          | VIEW_1 |  3933 |   372K|   211   (6)| 00:00:01 |    DB1 | R->S |
|   9 |     REMOTE          | VIEW_2 | 78664 |  1690K|   203   (2)| 00:00:01 |    DB1 | R->S |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("C"."ID"="B"."OBJECT_ID")
   5 - access("C"."ID"="A"."OBJECT_ID")
       filter(LNNVL("C"."ID"="B"."OBJECT_ID"))
   7 - access("A"."OBJECT_ID"="B"."OBJECT_ID")
Remote SQL Information (identified by operation id):
----------------------------------------------------
   4 - SELECT "A1"."OBJECT_ID","A1"."OWNER","A1"."OBJECT_NAME","A1"."OBJECT_ID","A1"."
       CREATED","A2"."OBJECT_ID","A2"."OBJECT_ID","A2"."CREATED","A2"."OBJECT_ID" FROM
       "VIEW_1" "A1","VIEW_2" "A2" WHERE "A1"."OBJECT_ID"="A2"."OBJECT_ID" AND
       "A1"."CREATED"<SYSDATE@!-100 (accessing 'DB1' )
   8 - SELECT "OWNER","OBJECT_NAME","OBJECT_ID","CREATED" FROM "VIEW_1" "A" WHERE
       "CREATED"<:1-:2 (accessing 'DB1' )
   9 - SELECT "OBJECT_ID","CREATED" FROM "VIEW_2" "B" (accessing 'DB1' )
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         12  consistent gets
          0  physical reads
          0  redo size
     118571  bytes sent via SQL*Net to client
       3967  bytes received via SQL*Net from client
        315  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)

       4700  rows processed

===== Add NO_MERGE hint to force self-contain sub query in remote DB =======

SQL> select /*+ NO_MERGE(d) */ d.owner, d.object_name, d.object_id, d.created, c.id
  2  from t1 c,
  3       (select a.owner, a.object_name, b.object_id, b.created, a.object_id a_id, b.object_id b_id
  4        from view_1@db1 a, view_2@db1 b
  5        where a.object_id = b.object_id ) d
  6  where c.id = d.a_id or c.id=d.b_id;
4700 rows selected.
Elapsed: 00:01:33.72
Execution Plan
----------------------------------------------------------
Plan hash value: 1318589531
--------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      | 82597 |    23M|  1323   (2)| 00:00:06 |        |      |
|   1 |  CONCATENATION      |      |       |       |            |          |        |      |
|*  2 |   HASH JOIN         |      | 78664 |    21M|   662   (2)| 00:00:03 |        |      |
|   3 |    TABLE ACCESS FULL| T1   |  1000 |  4000 |     3   (0)| 00:00:01 |        |      |
|   4 |    VIEW             |      | 78664 |    21M|   657   (2)| 00:00:03 |        |      |
|   5 |     REMOTE          |      |       |       |            |          |    DB1 | R->S |
|*  6 |   HASH JOIN         |      |  3933 |  1125K|   662   (2)| 00:00:03 |        |      |
|   7 |    TABLE ACCESS FULL| T1   |  1000 |  4000 |     3   (0)| 00:00:01 |        |      |
|   8 |    VIEW             |      | 78664 |    21M|   657   (2)| 00:00:03 |        |      |
|   9 |     REMOTE          |      |       |       |            |          |    DB1 | R->S |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("C"."ID"="D"."B_ID")
   6 - access("C"."ID"="D"."A_ID")
       filter(LNNVL("C"."ID"="D"."B_ID"))
Remote SQL Information (identified by operation id):
----------------------------------------------------
   5 - EXPLAIN PLAN SET STATEMENT_ID='PLUS2432982' INTO PLAN_TABLE@! FOR SELECT
       "A2"."OWNER","A2"."OBJECT_NAME","A1"."OBJECT_ID","A1"."CREATED","A2"."OBJECT_ID","A1
       "."OBJECT_ID" FROM "VIEW_1" "A2","VIEW_2" "A1" WHERE
       "A2"."OBJECT_ID"="A1"."OBJECT_ID" (accessing 'DB1' )
   9 - EXPLAIN PLAN SET STATEMENT_ID='PLUS2432982' INTO PLAN_TABLE@! FOR SELECT
       "A2"."OWNER","A2"."OBJECT_NAME","A1"."OBJECT_ID","A1"."CREATED","A2"."OBJECT_ID","A1
       "."OBJECT_ID" FROM "VIEW_1" "A2","VIEW_2" "A1" WHERE
       "A2"."OBJECT_ID"="A1"."OBJECT_ID" (accessing 'DB1' )
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         12  consistent gets
          0  physical reads
          0  redo size
     118571  bytes sent via SQL*Net to client
       3967  bytes received via SQL*Net from client
        315  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       4700  rows processed

======= remove the “or” , then fetch only once =======

SQL> select /*+ NO_MERGE(d) */ d.owner, d.object_name, d.object_id, d.created, c.id
  2  from t1 c,
  3       (select a.owner, a.object_name, b.object_id, b.created, a.object_id a_id, b.object_id b_id
  4        from view_1@db1 a, view_2@db1 b
  5        where a.object_id = b.object_id ) d
  6  where c.id = d.a_id ;
4700 rows selected.
Elapsed: 00:00:47.48
Execution Plan
----------------------------------------------------------
Plan hash value: 2524810404
-------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      | 78664 |    21M|   662   (2)| 00:00:03 |        |      |
|*  1 |  HASH JOIN         |      | 78664 |    21M|   662   (2)| 00:00:03 |        |      |
|   2 |   TABLE ACCESS FULL| T1   |  1000 |  4000 |     3   (0)| 00:00:01 |        |      |
|   3 |   VIEW             |      | 78664 |    20M|   657   (2)| 00:00:03 |        |      |
|   4 |    REMOTE          |      |       |       |            |          |    DB1 | R->S |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("C"."ID"="D"."A_ID")
Remote SQL Information (identified by operation id):
----------------------------------------------------
   4 - EXPLAIN PLAN SET STATEMENT_ID='PLUS2432982' INTO PLAN_TABLE@! FOR SELECT
       "A2"."OWNER","A2"."OBJECT_NAME","A1"."OBJECT_ID","A1"."CREATED","A2"."OBJECT_ID"
       FROM "VIEW_1" "A2","VIEW_2" "A1" WHERE "A2"."OBJECT_ID"="A1"."OBJECT_ID"
       (accessing 'DB1' )
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          6  consistent gets
          0  physical reads
          0  redo size
     118571  bytes sent via SQL*Net to client
       3967  bytes received via SQL*Net from client
        315  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       4700  rows processed

5. Hint for self-contain remote sub-query.

Can dd the hint into self-contain remote sub query, however, query transformation will break out the join sequence and only apply the join method on individual objects, alias transform in hint will be handled by database optimizer (for 11g above).

Limitation for Hint on remote sub query :

Dynamic sampling will be ignored in the distributed queries
Drive site hint will be ignored in “ insert .. select .. from .. “ , “update from <query> …”

===== hint on remote sub-query lost join sequence ======

SQL> select /*+ NO_MERGE(d) */ d.owner, d.object_name, d.object_id, d.created, c.id
  2  from t1 c,
  3       (select /*+ use_nl(a,b) */ a.owner, a.object_name, b.object_id, b.created, a.object_id a_id, b.object_id b_id
  4        from view_1@db1 a, view_2@db1 b
  5        where a.object_id = b.object_id ) d
  6  where c.id = d.a_id  and c.id=d.b_id
  7  ;
4700 rows selected.
Elapsed: 00:00:42.57
Execution Plan
----------------------------------------------------------
Plan hash value: 2524810404
-------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      | 78664 |    21M|    15M  (2)| 18:51:16 |        |      |
|*  1 |  HASH JOIN         |      | 78664 |    21M|    15M  (2)| 18:51:16 |        |      |
|   2 |   TABLE ACCESS FULL| T1   |  1000 |  4000 |     3   (0)| 00:00:01 |        |      |
|   3 |   VIEW             |      | 78664 |    21M|    15M  (2)| 18:51:18 |        |      |
|   4 |    REMOTE          |      |       |       |            |          |    DB1 | R->S |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("C"."ID"="D"."A_ID" AND "C"."ID"="D"."B_ID")
Remote SQL Information (identified by operation id):
----------------------------------------------------
   4 - EXPLAIN PLAN SET STATEMENT_ID='PLUS2432982' INTO PLAN_TABLE@! FOR SELECT
       /*+ USE_NL ("A1") USE_NL ("A2") */ "A2"."OWNER","A2"."OBJECT_NAME","A1"."OBJECT_ID"
       ,"A1"."CREATED","A2"."OBJECT_ID","A1"."OBJECT_ID" FROM "VIEW_1" "A2","VIEW_2" "A1"
       WHERE "A2"."OBJECT_ID"="A1"."OBJECT_ID" (accessing 'DB1' )
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          6  consistent gets
          0  physical reads
          0  redo size
     118571  bytes sent via SQL*Net to client
       3967  bytes received via SQL*Net from client
        315  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       4700  rows processed

===== add leading to enforce join sequence in remote ======

SQL> select /*+ NO_MERGE(d) */ d.owner, d.object_name, d.object_id, d.created, c.id
  2  from t1 c,
  3       (select /*+ leading(b) use_hash(b a) */ a.owner, a.object_name, b.object_id, b.created,
           a.object_id a_id, b.object_id b_id
  4        from view_1@db1 a, view_2@db1 b
  5        where a.object_id = b.object_id ) d
  6  where c.id = d.a_id  and c.id=d.b_id;
4700 rows selected.
Elapsed: 00:00:47.74
Execution Plan
----------------------------------------------------------
Plan hash value: 2524810404
-------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      | 78664 |    21M|   662   (2)| 00:00:03 |        |      |
|*  1 |  HASH JOIN         |      | 78664 |    21M|   662   (2)| 00:00:03 |        |      |
|   2 |   TABLE ACCESS FULL| T1   |  1000 |  4000 |     3   (0)| 00:00:01 |        |      |
|   3 |   VIEW             |      | 78664 |    21M|   657   (2)| 00:00:03 |        |      |
|   4 |    REMOTE          |      |       |       |            |          |    DB1 | R->S |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("C"."ID"="D"."A_ID" AND "C"."ID"="D"."B_ID")
Remote SQL Information (identified by operation id):
----------------------------------------------------
   4 - EXPLAIN PLAN SET STATEMENT_ID='PLUS2432982' INTO PLAN_TABLE@! FOR SELECT
       /*+ LEADING ("A1") USE_HASH ("A1") USE_HASH ("A2") */
       "A2"."OWNER","A2"."OBJECT_NAME","A1"."OBJECT_ID","A1"."CREATED","A2"."OBJECT_ID","A
       1"."OBJECT_ID" FROM "VIEW_1" "A2","VIEW_2" "A1" WHERE
       "A2"."OBJECT_ID"="A1"."OBJECT_ID" (accessing 'DB1' )
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          6  consistent gets
          0  physical reads
          0  redo size
     118571  bytes sent via SQL*Net to client
       3967  bytes received via SQL*Net from client
        315  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       4700  rows processed

Fred's ORACLE DBA Works

Explain everything in ORACLE database using the theory!

Where is the Global Temporary Table stored?

Dead lock due to library cache pin and library cache lock

Toad add the extra lock for connected session only doing “select”

Parallel DML and rollback segment

Call ORACLE store procedure from MS Sql server via openquery

In ORACLE database which store the source of data:

(1). create the EMP table with (empno, ename, job, mgr, hiredate, sal, comm, deptno)

(2). create the user type in ORACLE DB for defined the record data.

(3). create the wrapped function and return value by “PIPELINED”. The function will pass into the dynamic “where” clause for dynamic “SQL”.

In SQL Server :

(5), Calling template from SQL Server

Bind variable peeking in distributed query

Bind variable peeking with column histogram on stewed value

Bind variable peeking in 10G and 11G

SQL execution plan in cache and objects statistics gathering

Query with remote objects access and join