Goal: TEST Adaptive cursor sharing functionality
create table scott.tst_acs (id,nam) tablespace test_assm
as
select decode(mod(object_id,3),0,9999999,object_id) id,object_name nam
from dba_objects
order by id;
create index scott.tst_acs_idx on scott.tst_acs (id);
exec dbms_stats.gather_table_stats('SCOTT','TST_ACS');
set lines 300
set pages 3000
col owner for a10
col table_name for a10
col index_name for a20
col INDEX_TYPE for a10
select a.owner,a.table_name,a.num_rows,a.last_analyzed,b.index_name,b.index_type,b.uniqueness,b.clustering_factor,b.num_rows
from dba_tables a,dba_indexes b
where a.owner=b.owner
and a.table_name=b.table_name
and a.table_name like 'TST_%'
and a.owner='SCOTT'
order by owner,table_name,index_name;
alter session set nls_date_format='DD/MM/YYYY HH24:MI:SS';
OWNER TABLE_NAME NUM_ROWS LAST_ANALYZED INDEX_NAME INDEX_TYPE UNIQUENES CLUSTERING_FACTOR NUM_ROWS
---------- ---------- ---------- ------------------- -------------------- ---------- --------- ----------------- ----------
SCOTT TST_ACS 91830 14/09/2018 20:45:44 TST_ACS_IDX NORMAL NONUNIQUE 447 91830
Query to use to examine ACS usage:
query 1 - selecting only the indexed column.
var objid number;
exec :objid:=&numbr;
value: 9999999
---ideally this should contribute to 1/3 of the table.
select count(id)
from tst_acs
where id=:objid;
select sql_id,child_number,is_bind_sensitive,is_bind_aware,is_shareable,plan_hash_value,executions from v$sql where sql_id='0j06tw291x3m5';
SQL> select sql_id,child_number,is_bind_sensitive,is_bind_aware,is_shareable,plan_hash_value,executions from v$sql where sql_id='0j06tw291x3m5';
SQL_ID CHILD_NUMBER I I I PLAN_HASH_VALUE EXECUTIONS
------------- ------------ - - - --------------- ----------
0j06tw291x3m5 0 Y N Y 1292819319 1
SQL>
select * from table(dbms_xplan.display_cursor(sql_id=>'0j06tw291x3m5',cursor_child_no=>0,format=>'ADVANCED'));
SQL> select * from table(dbms_xplan.display_cursor(sql_id=>'0j06tw291x3m5',cursor_child_no=>0,format=>'ADVANCED'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 0j06tw291x3m5, child number 0
-------------------------------------
select count(id) from tst_acs where id=:objid
Plan hash value: 1292819319
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
| 1 | SORT AGGREGATE | | 1 | 6 | | |
|* 2 | INDEX RANGE SCAN| TST_ACS_IDX | 1 | 6 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / TST_ACS@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('12.1.0.1')
DB_VERSION('12.1.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX(@"SEL$1" "TST_ACS"@"SEL$1" ("TST_ACS"."ID"))
END_OUTLINE_DATA
*/
Peeked Binds (identified by position):
--------------------------------------
1 - :OBJID (NUMBER): 9999999
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=:OBJID)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) COUNT("ID")[22]
2 - "ID"[NUMBER,22]
50 rows selected.
=====> it picked index range scan this itself.
select sql_id,child_number,is_bind_sensitive,is_bind_aware,is_shareable,plan_hash_value,executions,rows_processed,BUFFER_GETS,DISK_READS,PHYSICAL_READ_REQUESTS,PHYSICAL_READ_BYTES,PHYSICAL_WRITE_REQUESTS,PHYSICAL_WRITE_BYTES,OPTIMIZED_PHY_READ_REQUESTS from v$sql where sql_id='0j06tw291x3m5';
SQL_ID CHILD_NUMBER I I I PLAN_HASH_VALUE EXECUTIONS ROWS_PROCESSED BUFFER_GETS DISK_READS PHYSICAL_READ_REQUESTS PHYSICAL_READ_BYTES PHYSICAL_WRITE_REQUESTS PHYSICAL_WRITE_BYTES OPTIMIZED_PHY_READ_REQUESTS
------------- ------------ - - - --------------- ---------- -------------- ----------- ---------- ---------------------- ------------------- ----------------------- -------------------- ---------------------------
0j06tw291x3m5 0 Y N Y 1292819319 1 1 74 0 0 0 0 0 0
Even with a different bind it it still able to reuse the cursor...
SQL> select sql_id,child_number,is_bind_sensitive,is_bind_aware,is_shareable,plan_hash_value,executions,rows_processed from v$sql where sql_id='0j06tw291x3m5';
SQL_ID CHILD_NUMBER I I I PLAN_HASH_VALUE EXECUTIONS ROWS_PROCESSED
------------- ------------ - - - --------------- ---------- --------------
0j06tw291x3m5 0 Y N Y 1292819319 2 2
--------------ignore here - put it wrong sqlid
select * from v$sql_cs_statistics
where sql_id='8ja94dx96jtxq'
order by child_number;
select * from v$sql_cs_selectivity
where sql_id='8ja94dx96jtxq'
order by child_number;
select * from v$sql_cs_histogram where sql_id='8ja94dx96jtxq';
SQL> select * from v$sql_cs_histogram where sql_id='8ja94dx96jtxq';
no rows selected
SQL> select * from v$sql_cs_statistics
where sql_id='8ja94dx96jtxq'
order by child_number; 2 3
no rows selected
SQL> select * from v$sql_cs_selectivity
where sql_id='8ja94dx96jtxq'
order by child_number; 2 3
no rows selected
SQL>
=> so none of this kicked off. Looking @ v$sql_shared_cursor I noticed oracle generated new cursor, the only difference I observed was
--------------ignore here - put it wrong sqlid.
Ideally we will have v$sql_cs_histogram kicking in.
SQL> select * from v$sql_shared_cursor where sql_id='0j06tw291x3m5';
SQL_ID ADDRESS CHILD_ADDRESS CHILD_NUMBER U S O O S L F E B P I S T A B D L T B I I R L I O E M U T N F A P T D L D B P C S C P T M B M R O P M F L P L A F L R L H P B U REASON CON_ID
------------- ---------------- ---------------- ------------ - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -------------------------------------------------------------------------------- ----------
0j06tw291x3m5 000000007D8C2BE0 000000007D8ACD70 0 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N Y N N N N N N N N N <ChildNode><ChildNumber>0</ChildNumber><ID>40</ID><reason>Bind mismatch(25)</rea 0
0j06tw291x3m5 000000007D8C2BE0 00000000A1890930 1 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N 0
The only difference is L which translates to LOAD_OPTIMIZER_STATS set to Y for cursor 0 and N for cursor 1.
But this ideally should be the case for a new cursor, so why the next one didnt pickup the load_optimizer_state column.
As per oracle document... "A hard parse is forced to initialize extended cursor sharing", which is the case with first parsing fine, but why for second case.
Let us examine what happens with a new cursor (changed sqltext), what all are set/unset.
new sqltext:
select count(nam)
from tst_acs
where id=:objid;
SQL> select * from v$sql_shared_cursor where sql_id='65ma369shw3s2';
SQL_ID ADDRESS CHILD_ADDRESS CHILD_NUMBER U S O O S L F E B P I S T A B D L T B I I R L I O E M U T N F A P T D L D B P C S C P T M B M R O P M F L P L A F L R L H P B U REASON CON_ID
------------- ---------------- ---------------- ------------ - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -------------------------------------------------------------------------------- ----------
65ma369shw3s2 000000007D5A5AC0 000000007DB1EA08 0 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N 0
Hmm this means that oracle is intelligently marking the old child cursor when it creates the new child cursor with the reason for it in the new child cursor. Where the cursor 0 is updated with L (LOAD_OPTIMIZER_STATS set to Y and bind mismatch).
May be we can selectively remove that cursor and retry this to confirm.
select * from v$sql_cs_statistics
where sql_id='65ma369shw3s2'
order by child_number;
select * from v$sql_cs_selectivity
where sql_id='65ma369shw3s2'
order by child_number;
select * from v$sql_cs_histogram where sql_id='65ma369shw3s2';
select * from table(dbms_xplan.display_cursor(sql_id=>'65ma369shw3s2',cursor_child_no=>0,format=>'ADVANCED'));
SQL> select * from v$sql_shared_cursor where sql_id='65ma369shw3s2';
SQL_ID ADDRESS CHILD_ADDRESS CHILD_NUMBER U S O O S L F E B P I S T A B D L T B I I R L I O E M U T N F A P T D L D B P C S C P T M B M R O P M F L P L A F L R L H P B U REASON CON_ID
------------- ---------------- ---------------- ------------ - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -------------------------------------------------------------------------------- ----------
65ma369shw3s2 000000007D5A5AC0 000000007DB1EA08 0 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N 0
SQL> select sql_id,child_number,is_bind_sensitive,is_bind_aware,is_shareable,plan_hash_value,executions,rows_processed from v$sql where sql_id='65ma369shw3s2';
SQL_ID CHILD_NUMBER I I I PLAN_HASH_VALUE EXECUTIONS ROWS_PROCESSED
------------- ------------ - - - --------------- ---------- --------------
65ma369shw3s2 0 Y N Y 2961755162 1 1
SQL> select * from v$sql_cs_statistics
where sql_id='65ma369shw3s2'
order by child_number; 2 3
no rows selected
SQL> select * from v$sql_cs_selectivity
where sql_id='65ma369shw3s2'
order by child_number; 2 3
no rows selected
SQL> select * from v$sql_cs_histogram where sql_id='65ma369shw3s2';
ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT CON_ID
---------------- ---------- ------------- ------------ ---------- ---------- ----------
000000007D5A5AC0 1896746754 65ma369shw3s2 0 0 1 0
000000007D5A5AC0 1896746754 65ma369shw3s2 0 1 0 0
000000007D5A5AC0 1896746754 65ma369shw3s2 0 2 0 0
SQL> select * from table(dbms_xplan.display_cursor(sql_id=>'65ma369shw3s2',cursor_child_no=>0,format=>'ADVANCED'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 65ma369shw3s2, child number 0
-------------------------------------
select count(nam) from tst_acs where id=:objid
Plan hash value: 2961755162
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 30 | | |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| TST_ACS | 1 | 30 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | TST_ACS_IDX | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / TST_ACS@SEL$1
3 - SEL$1 / TST_ACS@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('12.1.0.1')
DB_VERSION('12.1.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "TST_ACS"@"SEL$1" ("TST_ACS"."ID"))
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "TST_ACS"@"SEL$1")
END_OUTLINE_DATA
*/
Peeked Binds (identified by position):
--------------------------------------
1 - :OBJID (NUMBER): 98
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("ID"=:OBJID)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) COUNT("NAM")[22]
2 - "NAM"[VARCHAR2,128]
3 - "TST_ACS".ROWID[ROWID,10]
54 rows selected.
SQL>
Looking at this report, oracle now has populated the sql_cs_histogram with 0,1,2 bucket details.
Let us continue testing both the scenarios we spoke...
The cursor is no more in sqlarea. So we decided to run the query again to put it back in shared pool.
SQL> select sql_id,child_number,is_bind_sensitive,is_bind_aware,is_shareable,plan_hash_value,executions from v$sql where sql_id='0j06tw291x3m5';
SQL_ID CHILD_NUMBER I I I PLAN_HASH_VALUE EXECUTIONS
------------- ------------ - - - --------------- ----------
0j06tw291x3m5 0 Y N Y 1292819319 1
SQL> select sid,serial#,username,status,state,sql_id,event,seconds_in_wait from v$session where username='SCOTT';
SID SERIAL# USERNAME STATUS STATE SQL_ID EVENT SECONDS_IN_WAIT
---------- ---------- ------------------------------ -------- ------------------- ------------- ---------------------------------------------------------------- ---------------
29 553 SCOTT INACTIVE WAITING 0j06tw291x3m5 SQL*Net message from client 40
SQL> select * from v$sql_shared_cursor where sql_id='0j06tw291x3m5';
SQL_ID ADDRESS CHILD_ADDRESS CHILD_NUMBER U S O O S L F E B P I S T A B D L T B I I R L I O E M U T N F A P T D L D B P C S C P T M B M R O P M F L P L A F L R L H P B U REASON CON_ID
------------- ---------------- ---------------- ------------ - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -------------------------------------------------------------------------------- ----------
0j06tw291x3m5 000000007DD472C8 000000007D1E4770 0 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N 0
From the above it is evident when we had our next parse only oracle decided to create a new cursor with comments added to the existing one. So it is evident we should look at the previous cursor comments to know the reason for creation of this cursor.
=============> Move on to original topic....
Let us load more data to the table...
insert into scott.TST_ACS
select 9999999,object_id from dba_objects;
SQL>
SQL> insert into scott.TST_ACS
select 9999999,object_id from dba_objects; 2
91834 rows created.
SQL> insert into scott.TST_ACS
select 9999999,object_id from dba_objects; 2
91834 rows created.
SQL> commit;
Commit complete.
SQL>
SQL>
SQL> exec dbms_stats.gather_table_stats('SCOTT','TST_ACS');
PL/SQL procedure successfully completed.
SQL> select a.owner,a.table_name,a.num_rows,a.last_analyzed,b.index_name,b.index_type,b.uniqueness,b.clustering_factor,b.num_rows
2 from dba_tables a,dba_indexes b
3 where a.owner=b.owner
4 and a.table_name=b.table_name
5 and a.table_name like 'TST_%'
6 and a.owner='SCOTT'
7 order by owner,table_name,index_name;
OWNER TABLE_NAME NUM_ROWS LAST_ANAL INDEX_NAME INDEX_TYPE UNIQUENES CLUSTERING_FACTOR NUM_ROWS
---------- ---------- ---------- --------- -------------------- ---------- --------- ----------------- ----------
SCOTT TST_ACS 367332 15-SEP-18 TST_ACS_IDX NORMAL NONUNIQUE 1088 367332
SQL>
Let us purge the existing cursor...
exec sys.dbms_shared_pool.purge('&address, &hash_value','c');
SQL> select sql_id,child_number,is_bind_sensitive,is_bind_aware,is_shareable,plan_hash_value,executions from v$sql where sql_id='0j06tw291x3m5';
SQL_ID CHILD_NUMBER I I I PLAN_HASH_VALUE EXECUTIONS
------------- ------------ - - - --------------- ----------
0j06tw291x3m5 0 Y N Y 1292819319 1
SQL> select address, hash_value from v$sqlarea
where sql_id='0j06tw291x3m5'; 2
ADDRESS HASH_VALUE
---------------- ----------
000000007DD472C8 2451476069
SQL>
SQL>
SQL> exec sys.dbms_shared_pool.purge('&address, &hash_value','c');
Enter value for address: 000000007DD472C8
Enter value for hash_value: 2451476069
PL/SQL procedure successfully completed.
SQL> select sql_id,child_number,is_bind_sensitive,is_bind_aware,is_shareable,plan_hash_value,executions from v$sql where sql_id='0j06tw291x3m5';
no rows selected
SQL>
select * from v$sql_cs_statistics
where sql_id='0j06tw291x3m5'
order by child_number;
select * from v$sql_cs_selectivity
where sql_id='0j06tw291x3m5'
order by child_number;
select * from v$sql_cs_histogram where sql_id='0j06tw291x3m5';
SQL> select * from v$sql_cs_statistics
where sql_id='0j06tw291x3m5'
order by child_number; 2 3
no rows selected
SQL> select * from v$sql_cs_selectivity
where sql_id='0j06tw291x3m5'
order by child_number; 2 3
no rows selected
SQL> select * from v$sql_cs_histogram where sql_id='0j06tw291x3m5';
ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT CON_ID
---------------- ---------- ------------- ------------ ---------- ---------- ----------
000000007DD472C8 2451476069 0j06tw291x3m5 0 0 0 0
000000007DD472C8 2451476069 0j06tw291x3m5 0 1 1 0
000000007DD472C8 2451476069 0j06tw291x3m5 0 2 0 0
SQL>
==> Last time I made a mistake to query a wrong sql. Anyway now it looks like the histogram gets populated for all bind sqls.
SQL> select sql_id,child_number,is_bind_sensitive,is_bind_aware,is_shareable,plan_hash_value,executions from v$sql where sql_id='0j06tw291x3m5';
SQL_ID CHILD_NUMBER I I I PLAN_HASH_VALUE EXECUTIONS
------------- ------------ - - - --------------- ----------
0j06tw291x3m5 0 Y N Y 2221297138 2
SQL> select * from v$sql_cs_statistics
where sql_id='0j06tw291x3m5'
order by child_number; 2 3
no rows selected
SQL> select * from v$sql_cs_selectivity
where sql_id='0j06tw291x3m5'
order by child_number; 2 3
no rows selected
SQL> select * from v$sql_cs_histogram where sql_id='0j06tw291x3m5';
ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT CON_ID
---------------- ---------- ------------- ------------ ---------- ---------- ----------
000000007DD472C8 2451476069 0j06tw291x3m5 0 0 1 0
000000007DD472C8 2451476069 0j06tw291x3m5 0 1 1 0 <<< INEFFICIENT
000000007DD472C8 2451476069 0j06tw291x3m5 0 2 0 0
SQL> select * from v$sql_shared_cursor where sql_id='0j06tw291x3m5';
SQL_ID ADDRESS CHILD_ADDRESS CHILD_NUMBER U S O O S L F E B P I S T A B D L T B I I R L I O E M U T N F A P T D L D B P C S C P T M B M R O P M F L P L A F L R L H P B U REASON CON_ID
------------- ---------------- ---------------- ------------ - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -------------------------------------------------------------------------------- ----------
0j06tw291x3m5 000000007DD472C8 000000007D1E4770 0 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N Y N N N N N N N N N 0
SQL> select sql_id,child_number,is_bind_sensitive,is_bind_aware,is_shareable,plan_hash_value,executions,rows_processed,BUFFER_GETS,DISK_READS,PHYSICAL_READ_REQUESTS,PHYSICAL_READ_BYTES,PHYSICAL_WRITE_REQUESTS,PHYSICAL_WRITE_BYTES,OPTIMIZED_PHY_READ_REQUESTS from v$sql where sql_id='0j06tw291x3m5';
SQL_ID CHILD_NUMBER I I I PLAN_HASH_VALUE EXECUTIONS ROWS_PROCESSED BUFFER_GETS DISK_READS PHYSICAL_READ_REQUESTS PHYSICAL_READ_BYTES PHYSICAL_WRITE_REQUESTS PHYSICAL_WRITE_BYTES OPTIMIZED_PHY_READ_REQUESTS
------------- ------------ - - - --------------- ---------- -------------- ----------- ---------- ---------------------- ------------------- ----------------------- -------------------- ---------------------------
0j06tw291x3m5 0 Y N Y 2221297138 2 2 2270 0 0 0 0 0 0
SQL> select * from table(dbms_xplan.display_cursor(sql_id=>'0j06tw291x3m5',cursor_child_no=>0,format=>'ADVANCED'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 0j06tw291x3m5, child number 0
-------------------------------------
select count(id) from tst_acs where id=:objid
Plan hash value: 2221297138
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 313 (100)| |
| 1 | SORT AGGREGATE | | 1 | 6 | | |
|* 2 | TABLE ACCESS FULL| TST_ACS | 302K| 1773K| 313 (1)| 00:00:01 |
------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / TST_ACS@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('12.1.0.1')
DB_VERSION('12.1.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "TST_ACS"@"SEL$1")
END_OUTLINE_DATA
*/
Peeked Binds (identified by position):
--------------------------------------
1 - :OBJID (NUMBER): 9999999
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("ID"=:OBJID)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) COUNT("ID")[22]
2 - (rowset=200) "ID"[NUMBER,22]
50 rows selected.
SQL>
===> But still other views arent populated.. examining with max object_id which should have 1 hit...
SQL> select sql_id,child_number,is_bind_sensitive,is_bind_aware,is_shareable,plan_hash_value,executions,rows_processed,BUFFER_GETS,DISK_READS,PHYSICAL_READ_REQUESTS,PHYSICAL_READ_BYTES,PHYSICAL_WRITE_REQUESTS,PHYSICAL_WRITE_BYTES,OPTIMIZED_PHY_READ_REQUESTS from v$sql where sql_id='0j06tw291x3m5';
SQL_ID CHILD_NUMBER I I I PLAN_HASH_VALUE EXECUTIONS ROWS_PROCESSED BUFFER_GETS DISK_READS PHYSICAL_READ_REQUESTS PHYSICAL_READ_BYTES PHYSICAL_WRITE_REQUESTS PHYSICAL_WRITE_BYTES OPTIMIZED_PHY_READ_REQUESTS
------------- ------------ - - - --------------- ---------- -------------- ----------- ---------- ---------------------- ------------------- ----------------------- -------------------- ---------------------------
0j06tw291x3m5 0 Y N N 2221297138 2 2 2270 0 0 0 0 0 0
0j06tw291x3m5 1 Y Y Y 1292819319 1 1 3 0 0 0 0 0 0
SQL> select sql_id,child_number,is_bind_sensitive,is_bind_aware,is_shareable,plan_hash_value,executions from v$sql where sql_id='0j06tw291x3m5';
SQL_ID CHILD_NUMBER I I I PLAN_HASH_VALUE EXECUTIONS
------------- ------------ - - - --------------- ----------
0j06tw291x3m5 0 Y N N 2221297138 2
0j06tw291x3m5 1 Y Y Y 1292819319 1
==> New cursor is created as expected, plan changed. Also the first cursor is marked not sharable.
select * from table(dbms_xplan.display_cursor(sql_id=>'0j06tw291x3m5',cursor_child_no=>1,format=>'ADVANCED'));
SQL> select * from table(dbms_xplan.display_cursor(sql_id=>'0j06tw291x3m5',cursor_child_no=>1,format=>'ADVANCED'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 0j06tw291x3m5, child number 1
-------------------------------------
select count(id) from tst_acs where id=:objid
Plan hash value: 1292819319
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | SORT AGGREGATE | | 1 | 6 | | |
|* 2 | INDEX RANGE SCAN| TST_ACS_IDX | 1 | 6 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / TST_ACS@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('12.1.0.1')
DB_VERSION('12.1.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX(@"SEL$1" "TST_ACS"@"SEL$1" ("TST_ACS"."ID"))
END_OUTLINE_DATA
*/
Peeked Binds (identified by position):
--------------------------------------
1 - :OBJID (NUMBER): 93211
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=:OBJID)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) COUNT("ID")[22]
2 - "ID"[NUMBER,22]
50 rows selected.
SQL>
SQL> select * from v$sql_cs_statistics
where sql_id='0j06tw291x3m5'
order by child_number; 2 3
no rows selected
SQL> select * from v$sql_cs_selectivity
where sql_id='0j06tw291x3m5'
order by child_number; 2 3
ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER PREDICATE RANGE_ID LOW HIGH CON_ID
---------------- ---------- ------------- ------------ ---------------------------------------- ---------- ---------- ---------- ----------
000000007DD472C8 2451476069 0j06tw291x3m5 1 =OBJID 0 0.000003 0.000003 0 <<<< Selectivity is accurate 1/367332 apprx 2.7*E-6
SQL> select * from v$sql_cs_histogram where sql_id='0j06tw291x3m5';
ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT CON_ID
---------------- ---------- ------------- ------------ ---------- ---------- ----------
000000007DD472C8 2451476069 0j06tw291x3m5 1 0 1 0
000000007DD472C8 2451476069 0j06tw291x3m5 1 1 0 0
000000007DD472C8 2451476069 0j06tw291x3m5 1 2 0 0
000000007DD472C8 2451476069 0j06tw291x3m5 0 0 1 0
000000007DD472C8 2451476069 0j06tw291x3m5 0 1 1 0
000000007DD472C8 2451476069 0j06tw291x3m5 0 2 0 0
6 rows selected.
SQL>
So this is done the ACS works as expected, when we queried most records of the table we got a FTS and when we tried querying only 1 record acs came for rescue, though we had 1 iteration running into an issue.
thanks
create table scott.tst_acs (id,nam) tablespace test_assm
as
select decode(mod(object_id,3),0,9999999,object_id) id,object_name nam
from dba_objects
order by id;
create index scott.tst_acs_idx on scott.tst_acs (id);
exec dbms_stats.gather_table_stats('SCOTT','TST_ACS');
set lines 300
set pages 3000
col owner for a10
col table_name for a10
col index_name for a20
col INDEX_TYPE for a10
select a.owner,a.table_name,a.num_rows,a.last_analyzed,b.index_name,b.index_type,b.uniqueness,b.clustering_factor,b.num_rows
from dba_tables a,dba_indexes b
where a.owner=b.owner
and a.table_name=b.table_name
and a.table_name like 'TST_%'
and a.owner='SCOTT'
order by owner,table_name,index_name;
alter session set nls_date_format='DD/MM/YYYY HH24:MI:SS';
OWNER TABLE_NAME NUM_ROWS LAST_ANALYZED INDEX_NAME INDEX_TYPE UNIQUENES CLUSTERING_FACTOR NUM_ROWS
---------- ---------- ---------- ------------------- -------------------- ---------- --------- ----------------- ----------
SCOTT TST_ACS 91830 14/09/2018 20:45:44 TST_ACS_IDX NORMAL NONUNIQUE 447 91830
Query to use to examine ACS usage:
query 1 - selecting only the indexed column.
var objid number;
exec :objid:=&numbr;
value: 9999999
---ideally this should contribute to 1/3 of the table.
select count(id)
from tst_acs
where id=:objid;
select sql_id,child_number,is_bind_sensitive,is_bind_aware,is_shareable,plan_hash_value,executions from v$sql where sql_id='0j06tw291x3m5';
SQL> select sql_id,child_number,is_bind_sensitive,is_bind_aware,is_shareable,plan_hash_value,executions from v$sql where sql_id='0j06tw291x3m5';
SQL_ID CHILD_NUMBER I I I PLAN_HASH_VALUE EXECUTIONS
------------- ------------ - - - --------------- ----------
0j06tw291x3m5 0 Y N Y 1292819319 1
SQL>
select * from table(dbms_xplan.display_cursor(sql_id=>'0j06tw291x3m5',cursor_child_no=>0,format=>'ADVANCED'));
SQL> select * from table(dbms_xplan.display_cursor(sql_id=>'0j06tw291x3m5',cursor_child_no=>0,format=>'ADVANCED'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 0j06tw291x3m5, child number 0
-------------------------------------
select count(id) from tst_acs where id=:objid
Plan hash value: 1292819319
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
| 1 | SORT AGGREGATE | | 1 | 6 | | |
|* 2 | INDEX RANGE SCAN| TST_ACS_IDX | 1 | 6 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / TST_ACS@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('12.1.0.1')
DB_VERSION('12.1.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX(@"SEL$1" "TST_ACS"@"SEL$1" ("TST_ACS"."ID"))
END_OUTLINE_DATA
*/
Peeked Binds (identified by position):
--------------------------------------
1 - :OBJID (NUMBER): 9999999
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=:OBJID)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) COUNT("ID")[22]
2 - "ID"[NUMBER,22]
50 rows selected.
=====> it picked index range scan this itself.
select sql_id,child_number,is_bind_sensitive,is_bind_aware,is_shareable,plan_hash_value,executions,rows_processed,BUFFER_GETS,DISK_READS,PHYSICAL_READ_REQUESTS,PHYSICAL_READ_BYTES,PHYSICAL_WRITE_REQUESTS,PHYSICAL_WRITE_BYTES,OPTIMIZED_PHY_READ_REQUESTS from v$sql where sql_id='0j06tw291x3m5';
SQL_ID CHILD_NUMBER I I I PLAN_HASH_VALUE EXECUTIONS ROWS_PROCESSED BUFFER_GETS DISK_READS PHYSICAL_READ_REQUESTS PHYSICAL_READ_BYTES PHYSICAL_WRITE_REQUESTS PHYSICAL_WRITE_BYTES OPTIMIZED_PHY_READ_REQUESTS
------------- ------------ - - - --------------- ---------- -------------- ----------- ---------- ---------------------- ------------------- ----------------------- -------------------- ---------------------------
0j06tw291x3m5 0 Y N Y 1292819319 1 1 74 0 0 0 0 0 0
Even with a different bind it it still able to reuse the cursor...
SQL> select sql_id,child_number,is_bind_sensitive,is_bind_aware,is_shareable,plan_hash_value,executions,rows_processed from v$sql where sql_id='0j06tw291x3m5';
SQL_ID CHILD_NUMBER I I I PLAN_HASH_VALUE EXECUTIONS ROWS_PROCESSED
------------- ------------ - - - --------------- ---------- --------------
0j06tw291x3m5 0 Y N Y 1292819319 2 2
--------------ignore here - put it wrong sqlid
select * from v$sql_cs_statistics
where sql_id='8ja94dx96jtxq'
order by child_number;
select * from v$sql_cs_selectivity
where sql_id='8ja94dx96jtxq'
order by child_number;
select * from v$sql_cs_histogram where sql_id='8ja94dx96jtxq';
SQL> select * from v$sql_cs_histogram where sql_id='8ja94dx96jtxq';
no rows selected
SQL> select * from v$sql_cs_statistics
where sql_id='8ja94dx96jtxq'
order by child_number; 2 3
no rows selected
SQL> select * from v$sql_cs_selectivity
where sql_id='8ja94dx96jtxq'
order by child_number; 2 3
no rows selected
SQL>
=> so none of this kicked off. Looking @ v$sql_shared_cursor I noticed oracle generated new cursor, the only difference I observed was
--------------ignore here - put it wrong sqlid.
Ideally we will have v$sql_cs_histogram kicking in.
SQL> select * from v$sql_shared_cursor where sql_id='0j06tw291x3m5';
SQL_ID ADDRESS CHILD_ADDRESS CHILD_NUMBER U S O O S L F E B P I S T A B D L T B I I R L I O E M U T N F A P T D L D B P C S C P T M B M R O P M F L P L A F L R L H P B U REASON CON_ID
------------- ---------------- ---------------- ------------ - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -------------------------------------------------------------------------------- ----------
0j06tw291x3m5 000000007D8C2BE0 000000007D8ACD70 0 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N Y N N N N N N N N N <ChildNode><ChildNumber>0</ChildNumber><ID>40</ID><reason>Bind mismatch(25)</rea 0
0j06tw291x3m5 000000007D8C2BE0 00000000A1890930 1 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N 0
The only difference is L which translates to LOAD_OPTIMIZER_STATS set to Y for cursor 0 and N for cursor 1.
But this ideally should be the case for a new cursor, so why the next one didnt pickup the load_optimizer_state column.
As per oracle document... "A hard parse is forced to initialize extended cursor sharing", which is the case with first parsing fine, but why for second case.
Let us examine what happens with a new cursor (changed sqltext), what all are set/unset.
new sqltext:
select count(nam)
from tst_acs
where id=:objid;
SQL> select * from v$sql_shared_cursor where sql_id='65ma369shw3s2';
SQL_ID ADDRESS CHILD_ADDRESS CHILD_NUMBER U S O O S L F E B P I S T A B D L T B I I R L I O E M U T N F A P T D L D B P C S C P T M B M R O P M F L P L A F L R L H P B U REASON CON_ID
------------- ---------------- ---------------- ------------ - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -------------------------------------------------------------------------------- ----------
65ma369shw3s2 000000007D5A5AC0 000000007DB1EA08 0 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N 0
Hmm this means that oracle is intelligently marking the old child cursor when it creates the new child cursor with the reason for it in the new child cursor. Where the cursor 0 is updated with L (LOAD_OPTIMIZER_STATS set to Y and bind mismatch).
May be we can selectively remove that cursor and retry this to confirm.
select * from v$sql_cs_statistics
where sql_id='65ma369shw3s2'
order by child_number;
select * from v$sql_cs_selectivity
where sql_id='65ma369shw3s2'
order by child_number;
select * from v$sql_cs_histogram where sql_id='65ma369shw3s2';
select * from table(dbms_xplan.display_cursor(sql_id=>'65ma369shw3s2',cursor_child_no=>0,format=>'ADVANCED'));
SQL> select * from v$sql_shared_cursor where sql_id='65ma369shw3s2';
SQL_ID ADDRESS CHILD_ADDRESS CHILD_NUMBER U S O O S L F E B P I S T A B D L T B I I R L I O E M U T N F A P T D L D B P C S C P T M B M R O P M F L P L A F L R L H P B U REASON CON_ID
------------- ---------------- ---------------- ------------ - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -------------------------------------------------------------------------------- ----------
65ma369shw3s2 000000007D5A5AC0 000000007DB1EA08 0 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N 0
SQL> select sql_id,child_number,is_bind_sensitive,is_bind_aware,is_shareable,plan_hash_value,executions,rows_processed from v$sql where sql_id='65ma369shw3s2';
SQL_ID CHILD_NUMBER I I I PLAN_HASH_VALUE EXECUTIONS ROWS_PROCESSED
------------- ------------ - - - --------------- ---------- --------------
65ma369shw3s2 0 Y N Y 2961755162 1 1
SQL> select * from v$sql_cs_statistics
where sql_id='65ma369shw3s2'
order by child_number; 2 3
no rows selected
SQL> select * from v$sql_cs_selectivity
where sql_id='65ma369shw3s2'
order by child_number; 2 3
no rows selected
SQL> select * from v$sql_cs_histogram where sql_id='65ma369shw3s2';
ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT CON_ID
---------------- ---------- ------------- ------------ ---------- ---------- ----------
000000007D5A5AC0 1896746754 65ma369shw3s2 0 0 1 0
000000007D5A5AC0 1896746754 65ma369shw3s2 0 1 0 0
000000007D5A5AC0 1896746754 65ma369shw3s2 0 2 0 0
SQL> select * from table(dbms_xplan.display_cursor(sql_id=>'65ma369shw3s2',cursor_child_no=>0,format=>'ADVANCED'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 65ma369shw3s2, child number 0
-------------------------------------
select count(nam) from tst_acs where id=:objid
Plan hash value: 2961755162
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 30 | | |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| TST_ACS | 1 | 30 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | TST_ACS_IDX | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / TST_ACS@SEL$1
3 - SEL$1 / TST_ACS@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('12.1.0.1')
DB_VERSION('12.1.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "TST_ACS"@"SEL$1" ("TST_ACS"."ID"))
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "TST_ACS"@"SEL$1")
END_OUTLINE_DATA
*/
Peeked Binds (identified by position):
--------------------------------------
1 - :OBJID (NUMBER): 98
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("ID"=:OBJID)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) COUNT("NAM")[22]
2 - "NAM"[VARCHAR2,128]
3 - "TST_ACS".ROWID[ROWID,10]
54 rows selected.
SQL>
Looking at this report, oracle now has populated the sql_cs_histogram with 0,1,2 bucket details.
Let us continue testing both the scenarios we spoke...
The cursor is no more in sqlarea. So we decided to run the query again to put it back in shared pool.
SQL> select sql_id,child_number,is_bind_sensitive,is_bind_aware,is_shareable,plan_hash_value,executions from v$sql where sql_id='0j06tw291x3m5';
SQL_ID CHILD_NUMBER I I I PLAN_HASH_VALUE EXECUTIONS
------------- ------------ - - - --------------- ----------
0j06tw291x3m5 0 Y N Y 1292819319 1
SQL> select sid,serial#,username,status,state,sql_id,event,seconds_in_wait from v$session where username='SCOTT';
SID SERIAL# USERNAME STATUS STATE SQL_ID EVENT SECONDS_IN_WAIT
---------- ---------- ------------------------------ -------- ------------------- ------------- ---------------------------------------------------------------- ---------------
29 553 SCOTT INACTIVE WAITING 0j06tw291x3m5 SQL*Net message from client 40
SQL> select * from v$sql_shared_cursor where sql_id='0j06tw291x3m5';
SQL_ID ADDRESS CHILD_ADDRESS CHILD_NUMBER U S O O S L F E B P I S T A B D L T B I I R L I O E M U T N F A P T D L D B P C S C P T M B M R O P M F L P L A F L R L H P B U REASON CON_ID
------------- ---------------- ---------------- ------------ - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -------------------------------------------------------------------------------- ----------
0j06tw291x3m5 000000007DD472C8 000000007D1E4770 0 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N 0
From the above it is evident when we had our next parse only oracle decided to create a new cursor with comments added to the existing one. So it is evident we should look at the previous cursor comments to know the reason for creation of this cursor.
=============> Move on to original topic....
Let us load more data to the table...
insert into scott.TST_ACS
select 9999999,object_id from dba_objects;
SQL>
SQL> insert into scott.TST_ACS
select 9999999,object_id from dba_objects; 2
91834 rows created.
SQL> insert into scott.TST_ACS
select 9999999,object_id from dba_objects; 2
91834 rows created.
SQL> commit;
Commit complete.
SQL>
SQL>
SQL> exec dbms_stats.gather_table_stats('SCOTT','TST_ACS');
PL/SQL procedure successfully completed.
SQL> select a.owner,a.table_name,a.num_rows,a.last_analyzed,b.index_name,b.index_type,b.uniqueness,b.clustering_factor,b.num_rows
2 from dba_tables a,dba_indexes b
3 where a.owner=b.owner
4 and a.table_name=b.table_name
5 and a.table_name like 'TST_%'
6 and a.owner='SCOTT'
7 order by owner,table_name,index_name;
OWNER TABLE_NAME NUM_ROWS LAST_ANAL INDEX_NAME INDEX_TYPE UNIQUENES CLUSTERING_FACTOR NUM_ROWS
---------- ---------- ---------- --------- -------------------- ---------- --------- ----------------- ----------
SCOTT TST_ACS 367332 15-SEP-18 TST_ACS_IDX NORMAL NONUNIQUE 1088 367332
SQL>
Let us purge the existing cursor...
exec sys.dbms_shared_pool.purge('&address, &hash_value','c');
SQL> select sql_id,child_number,is_bind_sensitive,is_bind_aware,is_shareable,plan_hash_value,executions from v$sql where sql_id='0j06tw291x3m5';
SQL_ID CHILD_NUMBER I I I PLAN_HASH_VALUE EXECUTIONS
------------- ------------ - - - --------------- ----------
0j06tw291x3m5 0 Y N Y 1292819319 1
SQL> select address, hash_value from v$sqlarea
where sql_id='0j06tw291x3m5'; 2
ADDRESS HASH_VALUE
---------------- ----------
000000007DD472C8 2451476069
SQL>
SQL>
SQL> exec sys.dbms_shared_pool.purge('&address, &hash_value','c');
Enter value for address: 000000007DD472C8
Enter value for hash_value: 2451476069
PL/SQL procedure successfully completed.
SQL> select sql_id,child_number,is_bind_sensitive,is_bind_aware,is_shareable,plan_hash_value,executions from v$sql where sql_id='0j06tw291x3m5';
no rows selected
SQL>
select * from v$sql_cs_statistics
where sql_id='0j06tw291x3m5'
order by child_number;
select * from v$sql_cs_selectivity
where sql_id='0j06tw291x3m5'
order by child_number;
select * from v$sql_cs_histogram where sql_id='0j06tw291x3m5';
SQL> select * from v$sql_cs_statistics
where sql_id='0j06tw291x3m5'
order by child_number; 2 3
no rows selected
SQL> select * from v$sql_cs_selectivity
where sql_id='0j06tw291x3m5'
order by child_number; 2 3
no rows selected
SQL> select * from v$sql_cs_histogram where sql_id='0j06tw291x3m5';
ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT CON_ID
---------------- ---------- ------------- ------------ ---------- ---------- ----------
000000007DD472C8 2451476069 0j06tw291x3m5 0 0 0 0
000000007DD472C8 2451476069 0j06tw291x3m5 0 1 1 0
000000007DD472C8 2451476069 0j06tw291x3m5 0 2 0 0
SQL>
==> Last time I made a mistake to query a wrong sql. Anyway now it looks like the histogram gets populated for all bind sqls.
SQL> select sql_id,child_number,is_bind_sensitive,is_bind_aware,is_shareable,plan_hash_value,executions from v$sql where sql_id='0j06tw291x3m5';
SQL_ID CHILD_NUMBER I I I PLAN_HASH_VALUE EXECUTIONS
------------- ------------ - - - --------------- ----------
0j06tw291x3m5 0 Y N Y 2221297138 2
SQL> select * from v$sql_cs_statistics
where sql_id='0j06tw291x3m5'
order by child_number; 2 3
no rows selected
SQL> select * from v$sql_cs_selectivity
where sql_id='0j06tw291x3m5'
order by child_number; 2 3
no rows selected
SQL> select * from v$sql_cs_histogram where sql_id='0j06tw291x3m5';
ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT CON_ID
---------------- ---------- ------------- ------------ ---------- ---------- ----------
000000007DD472C8 2451476069 0j06tw291x3m5 0 0 1 0
000000007DD472C8 2451476069 0j06tw291x3m5 0 1 1 0 <<< INEFFICIENT
000000007DD472C8 2451476069 0j06tw291x3m5 0 2 0 0
SQL> select * from v$sql_shared_cursor where sql_id='0j06tw291x3m5';
SQL_ID ADDRESS CHILD_ADDRESS CHILD_NUMBER U S O O S L F E B P I S T A B D L T B I I R L I O E M U T N F A P T D L D B P C S C P T M B M R O P M F L P L A F L R L H P B U REASON CON_ID
------------- ---------------- ---------------- ------------ - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -------------------------------------------------------------------------------- ----------
0j06tw291x3m5 000000007DD472C8 000000007D1E4770 0 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N Y N N N N N N N N N 0
SQL> select sql_id,child_number,is_bind_sensitive,is_bind_aware,is_shareable,plan_hash_value,executions,rows_processed,BUFFER_GETS,DISK_READS,PHYSICAL_READ_REQUESTS,PHYSICAL_READ_BYTES,PHYSICAL_WRITE_REQUESTS,PHYSICAL_WRITE_BYTES,OPTIMIZED_PHY_READ_REQUESTS from v$sql where sql_id='0j06tw291x3m5';
SQL_ID CHILD_NUMBER I I I PLAN_HASH_VALUE EXECUTIONS ROWS_PROCESSED BUFFER_GETS DISK_READS PHYSICAL_READ_REQUESTS PHYSICAL_READ_BYTES PHYSICAL_WRITE_REQUESTS PHYSICAL_WRITE_BYTES OPTIMIZED_PHY_READ_REQUESTS
------------- ------------ - - - --------------- ---------- -------------- ----------- ---------- ---------------------- ------------------- ----------------------- -------------------- ---------------------------
0j06tw291x3m5 0 Y N Y 2221297138 2 2 2270 0 0 0 0 0 0
SQL> select * from table(dbms_xplan.display_cursor(sql_id=>'0j06tw291x3m5',cursor_child_no=>0,format=>'ADVANCED'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 0j06tw291x3m5, child number 0
-------------------------------------
select count(id) from tst_acs where id=:objid
Plan hash value: 2221297138
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 313 (100)| |
| 1 | SORT AGGREGATE | | 1 | 6 | | |
|* 2 | TABLE ACCESS FULL| TST_ACS | 302K| 1773K| 313 (1)| 00:00:01 |
------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / TST_ACS@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('12.1.0.1')
DB_VERSION('12.1.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "TST_ACS"@"SEL$1")
END_OUTLINE_DATA
*/
Peeked Binds (identified by position):
--------------------------------------
1 - :OBJID (NUMBER): 9999999
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("ID"=:OBJID)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) COUNT("ID")[22]
2 - (rowset=200) "ID"[NUMBER,22]
50 rows selected.
SQL>
===> But still other views arent populated.. examining with max object_id which should have 1 hit...
SQL> select sql_id,child_number,is_bind_sensitive,is_bind_aware,is_shareable,plan_hash_value,executions,rows_processed,BUFFER_GETS,DISK_READS,PHYSICAL_READ_REQUESTS,PHYSICAL_READ_BYTES,PHYSICAL_WRITE_REQUESTS,PHYSICAL_WRITE_BYTES,OPTIMIZED_PHY_READ_REQUESTS from v$sql where sql_id='0j06tw291x3m5';
SQL_ID CHILD_NUMBER I I I PLAN_HASH_VALUE EXECUTIONS ROWS_PROCESSED BUFFER_GETS DISK_READS PHYSICAL_READ_REQUESTS PHYSICAL_READ_BYTES PHYSICAL_WRITE_REQUESTS PHYSICAL_WRITE_BYTES OPTIMIZED_PHY_READ_REQUESTS
------------- ------------ - - - --------------- ---------- -------------- ----------- ---------- ---------------------- ------------------- ----------------------- -------------------- ---------------------------
0j06tw291x3m5 0 Y N N 2221297138 2 2 2270 0 0 0 0 0 0
0j06tw291x3m5 1 Y Y Y 1292819319 1 1 3 0 0 0 0 0 0
SQL> select sql_id,child_number,is_bind_sensitive,is_bind_aware,is_shareable,plan_hash_value,executions from v$sql where sql_id='0j06tw291x3m5';
SQL_ID CHILD_NUMBER I I I PLAN_HASH_VALUE EXECUTIONS
------------- ------------ - - - --------------- ----------
0j06tw291x3m5 0 Y N N 2221297138 2
0j06tw291x3m5 1 Y Y Y 1292819319 1
==> New cursor is created as expected, plan changed. Also the first cursor is marked not sharable.
select * from table(dbms_xplan.display_cursor(sql_id=>'0j06tw291x3m5',cursor_child_no=>1,format=>'ADVANCED'));
SQL> select * from table(dbms_xplan.display_cursor(sql_id=>'0j06tw291x3m5',cursor_child_no=>1,format=>'ADVANCED'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 0j06tw291x3m5, child number 1
-------------------------------------
select count(id) from tst_acs where id=:objid
Plan hash value: 1292819319
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | SORT AGGREGATE | | 1 | 6 | | |
|* 2 | INDEX RANGE SCAN| TST_ACS_IDX | 1 | 6 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / TST_ACS@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('12.1.0.1')
DB_VERSION('12.1.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX(@"SEL$1" "TST_ACS"@"SEL$1" ("TST_ACS"."ID"))
END_OUTLINE_DATA
*/
Peeked Binds (identified by position):
--------------------------------------
1 - :OBJID (NUMBER): 93211
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=:OBJID)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) COUNT("ID")[22]
2 - "ID"[NUMBER,22]
50 rows selected.
SQL>
SQL> select * from v$sql_cs_statistics
where sql_id='0j06tw291x3m5'
order by child_number; 2 3
no rows selected
SQL> select * from v$sql_cs_selectivity
where sql_id='0j06tw291x3m5'
order by child_number; 2 3
ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER PREDICATE RANGE_ID LOW HIGH CON_ID
---------------- ---------- ------------- ------------ ---------------------------------------- ---------- ---------- ---------- ----------
000000007DD472C8 2451476069 0j06tw291x3m5 1 =OBJID 0 0.000003 0.000003 0 <<<< Selectivity is accurate 1/367332 apprx 2.7*E-6
SQL> select * from v$sql_cs_histogram where sql_id='0j06tw291x3m5';
ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT CON_ID
---------------- ---------- ------------- ------------ ---------- ---------- ----------
000000007DD472C8 2451476069 0j06tw291x3m5 1 0 1 0
000000007DD472C8 2451476069 0j06tw291x3m5 1 1 0 0
000000007DD472C8 2451476069 0j06tw291x3m5 1 2 0 0
000000007DD472C8 2451476069 0j06tw291x3m5 0 0 1 0
000000007DD472C8 2451476069 0j06tw291x3m5 0 1 1 0
000000007DD472C8 2451476069 0j06tw291x3m5 0 2 0 0
6 rows selected.
SQL>
So this is done the ACS works as expected, when we queried most records of the table we got a FTS and when we tried querying only 1 record acs came for rescue, though we had 1 iteration running into an issue.
thanks
No comments:
Post a Comment