Example of using VPD with an Application Context

SQL> CREATE OR REPLACE PROCEDURE vpdtest_context_procedure(object_type IN VARCHAR2)                                                                                                           
  2  IS                                                                                                                                                                                       
  3  BEGIN                                                                                                                                                                                    
  4     DBMS_SESSION.SET_CONTEXT('vpdtest_context', 'object_type', object_type);                                                                                                              
  5  END;                                                                                                                                                                                     
  6  /                                                                                                                                                                                        
                                                                                                                                                                                              
Procedure created.                                                                                                                                                                            
                                                                                                                                                                                              
SQL>                                                                                                                                                                                          
SQL> CREATE CONTEXT vpdtest_context using vpdtest_context_procedure;                                                                                                                          
                                                                                                                                                                                              
Context created.                                                                                                                                                                              
                                                                                                                                                                                              
SQL>                                                                                                                                                                                          
SQL> CREATE TABLE vpdtest_table AS SELECT * FROM dba_objects;                                                                                                                                 
                                                                                                                                                                                              
Table created.                                                                                                                                                                                
                                                                                                                                                                                              
SQL>                                                                                                                                                                                          
SQL> BEGIN                                                                                                                                                                                    
  2     DBMS_RLS.ADD_POLICY(object_name => 'vpdtest_table',                                                                                                                                   
  3                         policy_name => 'vpdtest_policy',                                                                                                                                  
  4                         policy_function => 'vpdtest_policy_function');                                                                                                                    
  5  END;                                                                                                                                                                                     
  6  /                                                                                                                                                                                        
                                                                                                                                                                                              
PL/SQL procedure successfully completed.                                                                                                                                                      
                                                                                                                                                                                              
SQL>                                                                                                                                                                                          
SQL> CREATE OR REPLACE FUNCTION vpdtest_policy_function(schema VARCHAR2,                                                                                                                      
  2                                                     tab VARCHAR2) RETURN VARCHAR2                                                                                                         
  3  IS                                                                                                                                                                                       
  4  BEGIN                                                                                                                                                                                    
  5     RETURN 'OBJECT_TYPE = sys_context( ''vpdtest_context'', ''object_type'' )';                                                                                                           
  6  END;                                                                                                                                                                                     
  7  /                                                                                                                                                                                        
                                                                                                                                                                                              
Function created.                                                                                                                                                                             
                                                                                                                                                                                              
SQL>                                                                                                                                                                                          
SQL> EXEC vpdtest_context_procedure('TABLE');                                                                                                                                                 
                                                                                                                                                                                              
PL/SQL procedure successfully completed.                                                                                                                                                      
                                                                                                                                                                                              
SQL> SELECT DISTINCT object_type FROM vpdtest_table;                                                                                                                                          
                                                                                                                                                                                              
OBJECT_TYPE                                                                                                                                                                                   
-----------------------                                                                                                                                                                       
TABLE                                                                                                                                                                                         
                                                                                                                                                                                              
SQL>                                                                                                                                                                                          
SQL> EXEC vpdtest_context_procedure('VIEW');                                                                                                                                                  
                                                                                                                                                                                              
PL/SQL procedure successfully completed.                                                                                                                                                      
                                                                                                                                                                                              
SQL> SELECT DISTINCT object_type FROM vpdtest_table;                                                                                                                                          
                                                                                                                                                                                              
OBJECT_TYPE                                                                                                                                                                                   
-----------------------                                                                                                                                                                       
VIEW                                                                                                                                                                                          

“enq: TX – allocate ITL entry” wait event on index block

Over the weekend, some processes one of our databases locked up.  Support killed some sessions to get things back running, but on Monday I wanted to try to drill into what had occurred.

Querying dba_hist_active_sess_history I identified most problem sessions were waiting on the event “enq: TX – allocate ITL entry”.

For more details about what this event is, and why it happens, Arup Nanda has a good blog post (with pictures) about why it occurs.  In summary if a session wants to update a block, but there is no  remaining space in the block to create a new ITL entry, then it has to wait for one of the sessions currently holding an existing entry to commit (or rollback).

I drilled in deeper on one row from dba_hist_active_sess_history.  The current_obj# column indicated that the object involved was  an index on a STATUS field, and I used the current_file#, and current_block# to dump the block.  Below are some extracts from the block dump:

 seg/obj: 0x4bf2e  csc: 0x49.7a33a31d  itc: 169  flg: E  typ: 2 - INDEX
     brn: 0  bdba: 0x70490 ver: 0x01 opc: 0
     inc: 0  exflg: 0

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0069.004.00000004  0x00c01ce8.0001.41  C---    0  scn 0x0049.78de4f73
0x02   0x012a.000.00000004  0x00c0f996.0002.22  --U-    1  fsc 0x000d.7a33aa37
0x03   0x016c.003.0000000b  0x00c0584a.0010.07  --U-    1  fsc 0x000d.7a35cd79
0x04   0x0144.00e.0000000b  0x00c0e3fa.0004.2e  --U-    1  fsc 0x000d.7a33aa24
0x05   0x0130.00c.00000005  0x00c033cf.0000.34  C---    0  scn 0x0049.7a2ec617
0x06   0x0005.009.0006b8f0  0x00c0f190.8b4b.1c  --U-    1  fsc 0x000d.7a33a967
0x07   0x0160.011.00000004  0x00c04e80.0001.0c  C---    0  scn 0x0049.7a2ec71a
0x08   0x0115.002.00000005  0x00c03082.0001.02  --U-    1  fsc 0x000d.7a33a9f8
0x09   0x0028.001.0000000e  0x00c0e93b.0010.01  C---    0  scn 0x0049.7a2eca94
0x0a   0x00d4.005.00000005  0x00c0275d.0001.1e  --U-    1  fsc 0x000d.7a33aa49
0x0b   0x014c.005.00000007  0x00c0ee84.0003.25  C---    0  scn 0x0049.7a2eb3fb
0x0c   0x00a4.010.0000000b  0x00c0e6ad.0010.40  --U-    1  fsc 0x000d.7a35ce41
...
0xa6   0x000f.015.0006bd01  0x00c25946.7676.21  --U-    1  fsc 0x000d.7a33a763
0xa7   0x0025.002.00000011  0x00c051b2.0011.17  --U-    1  fsc 0x000d.7a33a711
0xa8   0x0151.004.00000004  0x00c04afe.0000.09  --U-    1  fsc 0x000d.7a33a8c4
0xa9   0x00fc.01d.00000003  0x00c02b51.0001.0e  --U-    1  fsc 0x000d.7a33a785
...
row#0[1109] flag: ---D---, lock: 169, len=11
col 0; len 1; (1):  30
col 1; len 6; (6):  00 09 c8 8b 00 5a
row#1[1098] flag: ---D---, lock: 164, len=11
col 0; len 1; (1):  30
col 1; len 6; (6):  00 09 c8 8b 00 5b
row#2[1087] flag: ---D---, lock: 75, len=11
col 0; len 1; (1):  30
col 1; len 6; (6):  00 09 c8 8b 00 5c
row#3[3969] flag: ---DS--, lock: 0, len=11
col 0; len 1; (1):  30
col 1; len 6; (6):  00 09 c8 93 00 05
row#4[1307] flag: ---D---, lock: 155, len=11
col 0; len 1; (1):  30
col 1; len 6; (6):  00 09 c8 9e 00 51
...
row#173[2330] flag: -------, lock: 0, len=11
col 0; len 1; (1):  35
col 1; len 6; (6):  00 07 04 8c 00 14
row#174[2319] flag: -------, lock: 0, len=11
col 0; len 1; (1):  35
col 1; len 6; (6):  00 07 04 8c 00 15
row#175[2308] flag: -------, lock: 0, len=11
col 0; len 1; (1):  35
col 1; len 6; (6):  00 07 04 8c 00 16

In particular note that the block contains 175 index entries and 169 ITL entries.  This makes me suspect that 168 transactions were accessing the block simultaneously (one ITL entry is reserved by Oracle for leaf node splits).

Time to look at the process and understand what is occurring.  A highly simplified version of the process is provided below:

INSERT RECORD INTO EVENT_TABLE STATUS 0
DO SOME STUFF
UPDATE RECORD SET STATUS = 9
DO SOME MORE STUFF
COMMIT

As this is process is provided via a web-service, many sessions can be performing this task at the same time.  At this point I had a pretty good idea about what was occurring:

  • Many sessions each insert a row into EVENT_TABLE at the same time
  • Index entries for all these new records are likely to be in the same block (the index is on status, all these rows have the same value for status).
  • Each session will need to grab an ITL entry from this same index block to perform the update (note index entry update is really done as a delete and then an insert)
  • Other sessions will need to wait until ITL entries are released

Based on this hypothesis, time to build a test case:

CREATE TABLE ITL_TEST_TABLE (ID NUMBER PRIMARY KEY,
                             STATUS VARCHAR2(2 CHAR) DEFAULT '0');
CREATE SEQUENCE IT_TEST_SEQUENCE;
CREATE INDEX ITL_TEST_STATUS ON ITL_TEST_TABLE(STATUS);

CREATE OR REPLACE PROCEDURE ITL_TEST_PROC(sleep number) 
AS
   INSERTED_ID NUMBER;
BEGIN
   INSERT INTO ITL_TEST_TABLE (ID, STATUS) VALUES (IT_TEST_SEQUENCE.NEXTVAL, NULL) RETURNING ID INTO INSERTED_ID;
   UPDATE ITL_TEST_TABLE SET STATUS = '9' WHERE ID = INSERTED_ID;
   DBMS_LOCK.SLEEP(sleep);
   COMMIT;
END;   
/
>cat test_itl.sql
exec itl_test_proc(60);
exit;
>cat test_itl.sh:
#!/bin/bash
for i in {1..200}; do 
   nohup sqlplus / as sysdba @test_itl &
done;

When I execute test_itl.sh and query v$session I can see there are 32 sessions waiting on ‘enq: TX – allocate ITL entry’.  Exactly the same as the real incident, 168 of these sessions can run this code simultaneously, the rest are blocked.

I am following up with the developers about resolving the issue, some of my ideas are:

  • Initially create record with status NULL, so initial index entry will not be created.
  • Optimize the logic in process so that transactions do not hold uncommitted data for so long
  • Hold off on creating row until end of process until status is known, and then just inserting rather than inserting and then updating.

Upcoming Adaptive Query Optimization Changes

I got a chance to discuss some of the issues we have been facing in this area with the CBO team at Oracle OpenWorld last week, and learned about some of the changes they have planned for the future.  Please don’t take this as fact, this is only based on my recollection of from an informal chat.

The parameter OPTIMIZER_ADAPTIVE_FEATURES will be split into two parts, OPTIMIZER_ADAPTIVE_PLANS and OPTIMIZER_ADAPTIVE_STATISTICS.  Franck Pachot has a blog post which goes into more details.

Worth noting is the fact that OPTIMIZER_ADAPTIVE_STATISTICS will be set to false by default.  This change will be implemented in 12.2, but also be made available as a patch for 12.1 (I believe again with the same defaults).

With OPTIMIZER_ADAPTIVE_STATISTICS set to false, SQL Plan Directives will still be created when assumptions made at parse time are discovered to be wrong at execution time, however  they will not automatically be used to trigger dynamic sampling or creation of column groups.  I imagine that the advantage for this is that these directives could be manually monitored, and root causes could be fixed by the DBA.

Specific to 12.2, if dynamic sampling is triggered, it will be a completely new mechanism for caching results instead of the current mechanism reliant on the result cache.  Results from dynamic sampling queries will be stored persistently (not lost after instance restart and available to all nodes in a RAC cluster).  There will be a mechanism to ensure that this data can be refreshed when it gets stale (somewhat similar to the existing mechanism for incremental statistics refresh where the database stores number of changed rows in base tables).

I am a little surprised at what appears like a partial back-track in this area from Oracle, however based on the number of issues we have found in this area I think this is the right thing to do.  My guess is that many customers have just been disabling ALL adaptive features when problems have been encountered during upgrade.  Separating the features out and providing more sensible default settings to encourage more customers to use at least some of these features should be beneficial to all.

 

Creating SQL Plan Baseline from a different SQL statement

Really just documenting this for myself because I couldn’t easily find this on the web.  Most of the steps are taken from this page:

Plan Stability using Sql Profiles and SQL Plan Management

Observe the following query doing a full table scan:

select * from table(dbms_xplan.display_cursor('8jng4anj19km4',
                                               format=>'peeked_binds'));

SELECT COUNT(1) FROM TOTS_QUE WHERE OTQUE_MODULE = :B3 AND OTQUE_DOCTYP 
= :B2 AND OTQUE_DOCRUNNUM = :B1 AND NVL(OTQUE_ERROR,'N') = 'N'

SQL_ID  8jng4anj19km4, child number 0
-------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |       |       |     3 (100)|          |
|   1 |  SORT AGGREGATE    |          |     1 |    16 |            |          |
|*  2 |   TABLE ACCESS FULL| TOTS_QUE |     1 |    16 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
   1 - :B3 (VARCHAR2(30), CSID=871): 'PR'
   2 - :B2 (VARCHAR2(30), CSID=871): 'PR'
   3 - :B1 (VARCHAR2(30), CSID=871): '8631426

Check the efficiency of the plan with the following query:

SELECT EXECUTIONS, ROUND(BUFFER_GETS/EXECUTIONS) FROM V$SQL 
   WHERE SQL_ID = '8jng4anj19km4';   
19808    29348

Executed 20,000 times since the last parse, average of 29,000 buffer gets per execution.  Note I am not going to go into the reasons why this inefficient plan is being chosen.

If we use the peeked bind variables to test, and force dynamic sampling, we can produce a more suitable plan:

SELECT /*+GATHER_PLAN_STATISTICS DYNAMIC_SAMPLING(11) */ COUNT(1) FROM TOTS_QUE 
WHERE OTQUE_MODULE = 'PR'  AND 
      OTQUE_DOCTYP = 'PR' AND 
      OTQUE_DOCRUNNUM = '8631426' AND 
      NVL(OTQUE_ERROR,'N') = 'N';

select * from table(dbms_xplan.display_cursor(FORMAT=>'ALLSTATS LAST'));

SQL_ID  87fcdy0wm5xhd, child number 0
-------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |            |      1 |        |      1 |00:00:00.01 |      59 |
|   1 |  SORT AGGREGATE                      |            |      1 |      1 |      1 |00:00:00.01 |      59 |
|*  2 |   TABLE ACCESS BY INDEX ROWID BATCHED| TOTS_QUE   |      1 |      1 |      0 |00:00:00.01 |      59 |
|*  3 |    INDEX RANGE SCAN                  | OTQUE_IDX1 |      1 |     13 |      0 |00:00:00.01 |      59 |
-------------------------------------------------------------------------------------------------------------

Note the SQL_ID here.  Observe that with the index access, only 59 buffer gets are required, compared to 29,000 using full table scan.

Lets create a sql plan baseline for the inefficient plan.  Note it is created as not enabled, so it won’t actually be used.

DECLARE
   dummy pls_integer;
BEGIN
   dummy := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
               sql_id => '8jng4anj19km4',
               enabled =>'NO');   
END;
/
SELECT sql_handle FROM DBA_SQL_PLAN_BASELINES ORDER BY CREATED DESC;
SQL_6d2f47d2acbf3c9b
..

The next bit it the important step, we can load the plan from the efficient plan (even though it has slightly different sql text, and a different sql_id) and associate it with the SQL_HANDLE  for the baseline we have just created.

DECLARE
   dummy pls_integer;
BEGIN
   dummy := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
               sql_id => '87fcdy0wm5xhd', 
               sql_handle =>'SQL_6d2f47d2acbf3c9b', 
               enabled => 'YES');   
END;
/
DECLARE
   dummy pls_integer;
BEGIN
   dummy := DBMS_SPM.ALTER_SQL_PLAN_BASELINE (sql_HANDLE => 'SQL_6d2f47d2acbf3c9b',
                                              ATTRIBUTE_NAME=>'AUTOPURGE', 
                                              ATTRIBUTE_VALUE=>'NO');   
END;
/
Note the AUTO_PURGE attribute change is just personal preference to prevent these records getting deleted.

SELECT child_number, 
       SQL_PLAN_BASELINE, 
       EXECUTIONS, 
       ROUND(BUFFER_GETS/EXECUTIONS) FROM V$SQL 
WHERE SQL_ID = '8jng4anj19km4';
CHILD_NUMBER SQL_PLAN_BASELINE              EXECUTIONS ROUND(BUFFER_GETS/EXECUTIONS)                                                                
------------ ------------------------------ ---------- -----------------------------                                                                
           0                                     20648                         29371                                                                
           1 SQL_PLAN_6ubu7uaqbyg4v1b716894       2252                            12                                                                                                                                                                                                                      


Note a new sql child cursor has been created, associated with the baseline, and that it is much more efficient than the previous one.

Adaptive Dynamic Sampling and the Result Cache: RC Latch

After we migrated one of our instances from Oracle 11.2.0.4 to 12.1.0.2 we ran into some serious latch contention that basically took down the instance.  From online research, and discussion with other DBAs I don’t think we are the only customer that has run into this problem, so I wanted to share my investigation and findings in the hope that it will help others, or to spur some fixes from Oracle in this area.

If you don’t want to drill into details, the TLDR is that Adaptive Dynamic Sampling (ADS) (the most likely way for this to be triggered if a SQL Plan Directive appears on a table, these are created automatically with default database settings) and frequent hard parsing (for example due to not using bind variables) do not play well together.

For an excellent introduction to ADS, I suggest you check out this presentation on the subject by Christian Antognini.

To reproduce the behavior that I was seeing, the script below creates a procedure that I can call to perform lots of hard parsing, and forcing ADS using a hint.  The purpose of the identifier parameter is to include it in the SQL text, so that each time I execute the procedure I pass in a different value, forcing a reparse on each execution.

create table t1 nologging as
select  rownum        id,
        rpad('x',100) padding
    from dual 
    connect by level <= 1e4;
               
create index i1 on t1(id);
 
CREATE OR REPLACE PROCEDURE lhps(identifier IN VARCHAR2, parse_count IN NUMBER) IS
BEGIN
   FOR LOOPY IN 1..PARSE_COUNT
   LOOP
      EXECUTE IMMEDIATE 'select /*+dynamic_sampling(11) '|| IDENTIFIER || ' */ * from t1 where id = ' || LOOPY;
   END LOOP;
END;
/

Using mystats package, we can see that in this case, for 100 hard parses, the RC Latch is taken 401 times.

SQL> exec mystats_pkg.ms_start; 
PL/SQL procedure successfully completed.

SQL> EXEC lhps('A', 100);                                                                               
PL/SQL procedure successfully completed.    
                                                             
SQL> exec mystats_pkg.ms_stop(p_statname_like=>'%RC%');
 ==========================================================================================
 MyStats report : 04-SEP-2016 11:18:49
 ==========================================================================================
 2. Statistics Report
 ----------------------------------------------------------------
 Type    Statistic Name                                     Value
 ------  --------------------------------------  ----------------
LATCH   Result Cache: RC Latch                                401             
 ----------------------------------------------------------------
 4. About
 ------------------------------------------------------------------------------------------
 - MyStats v3.0 by Adrian Billington (http://www.oracle-developer.net)
 - Based on the SNAP_MY_STATS utility by Jonathan Lewis
 ==========================================================================================

Examination of the SQL Trace shows that, as you would expect (from Christian’s presentation), the reason for the high RC Latch usage is to the internal ADS SQL statements.

SELECT /* DS_SVC */ /*+ dynamic_sampling(0) no_sql_tune no_monitoring optimizer_features_enable(default) no_parallel result_cache(snapshot=3600) */ SUM(C1) FROM (SELECT /*+ qb_name("innerQuery") NO_INDEX_FFS( "T1")  */ 1 AS C1 FROM "T1" "T1" WHERE ("T1"."ID"=1)) innerQuery

MOS note 2002089.1 describes this behavior, and suggests that this behavior can be disabled (obviously there are pros and cons in doing so) with the following setting:

alter session set "_optimizer_ads_use_result_cache" = FALSE;

As a side note it is slightly suprizing that there appears to be 4 RC Latch gets per each execution of the dynamic sampling SQL.  Note that this database in multi-tenant.  As there is only one RC Latch shared by all container databases, my guess is that when SQL is parsed, the RC Latch is taken once for each PDB.

Let’s retest after applying that underscore setting at session level:

SQL> alter session set "_optimizer_ads_use_result_cache" = FALSE;

SQL> exec mystats_pkg.ms_start;                                                                        
PL/SQL procedure successfully completed.                                                               

SQL> EXEC lhps('B', 100);                                                                             
PL/SQL procedure successfully completed.                                                               

SQL> exec mystats_pkg.ms_stop(p_statname_like=>'%RC%');
 ==========================================================================================
 MyStats report : 04-SEP-2016 11:28:53
 ==========================================================================================
 ------------------------------------------------------------------------------------------
 2. Statistics Report
 ------------------------------------------------------------
 Type    Statistic Name                                 Value
 ------  ----------------------------------  ----------------
 LATCH   Result Cache: RC Latch                            50
 ------------------------------------------------------------
 4. About
 ------------------------------------------------------------------------------------------
 - MyStats v3.0 by Adrian Billington (http://www.oracle-developer.net)
 - Based on the SNAP_MY_STATS utility by Jonathan Lewis
 ==========================================================================================

RC Latch usage is much reduced, but still seems to be quite high, it is taken 50 times for 100 hard parses.

Further analysis of the SQL Trace identifies the following SQL as the culprit:

SELECT /*+ RESULT_CACHE (SYSOBJ=TRUE SHELFLIFE=30) */ SNAP_ID,DBID,INSTANCE_NUMBER,SQL_ID,PARSING_SCHEMA_NAME,END_OF_FETCH_COUNT_DELTA,EXECUTIONS_DELTA,PX_SERVERS_EXECS_DELTA,BUFFER_GETS_DELTA,CPU_TIME_DELTA,ELAPSED_TIME_DELTA,FLAG,CON_ID FROM "SYS"."INT$DBA_HIST_SQLSTAT" "INT$DBA_HIST_SQLSTAT" WHERE ("INT$DBA_HIST_SQLSTAT"."SQL_ID"=:1 AND "INT$DBA_HIST_SQLSTAT"."PARSING_SCHEMA_NAME"=:2 AND ("INT$DBA_HIST_SQLSTAT"."CON_ID"=0 OR "INT$DBA_HIST_SQLSTAT"."CON_ID"=3) AND BITAND(NVL("INT$DBA_HIST_SQLSTAT"."FLAG",0),1)=0) AND ("INT$DBA_HIST_SQLSTAT".CON_ID=0 OR "INT$DBA_HIST_SQLSTAT".CON_ID=3)

Paraphrasing Christian’s presentation this lookup is performed to check from AWR whether SQL statement has been seen previously, to determine how long to spend on ADS during optimization.  I can’t see any method to disable Results Cache for this statement (apart from disabling Results Cache itself).  Note also, that I suspect this behavior is also related to multi-tenant, as I can’t reproduce this (either the RC Latch usage or even the lookup itself) on a non multi-tenant instance.

In our case have we disabled ADS at application schema level by setting the following in a login trigger, while we try to work with application teams to reduce hard parsing.

alter session set optimizer_dynamic_sampling = 0;

 

 

Incorrect Cardinality Adjustment caused by Dynamic Statistics

During migration testing to Oracle 12, one of our teams complained about regression of a particular query.
Drilling into what was going on identified some a problem with Adaptive Dynamic Sampling, and certainly not the first one we have encountered.
I will detail the steps to reproduce, and my understanding of what is going on.

create table idxsel as
select rownum id,
dbms_random.value(0, 1000)  r,
rpad('X', 1000, 'X') padding
from dual connect by level <= 10000;


CREATE INDEX idxsel_id ON idxsel(id);
CREATE INDEX idxsel_r_id ON idxsel(r, id);
EXEC DBMS_STATS.GATHER_TABLE_STATS(NULL, 'IDXSEL');

OK, so we have created a table with 10000 rows.  Column id is actually unique (not that the CBO knows this for sure), and column r is a random number between 1 and 1000.

Consider the following query:
SELECT NULL FROM IDXSEL WHERE id = 1 AND r > 500;

Amongst the options available to the optimizer to perform this query are:

  1.  Use a range scan on index IDXSEL_ID to get the enties with id equal to 1 (actually there will just be one), then for every index entry, access the table to eliminate those where r is greater than 500 (fifty percent chance for each entry).
  2. Use a range scan on IDXSEL_R_ID to get the entries those rows with r > 500 (this will match about half of the index entries), then as part of the index access operation, throw away all rows where id is not 1 (which will be all except maybe one row).

Intuitively (to me at least), option 1 is the better plan, and indeed you can see this is what happens without Dynamic Sampling.


SELECT /*+ GATHER_PLAN_STATISTICS DYNAMIC_SAMPLING(0)  */ NULL FROM IDXSEL WHERE id = 1 AND r > 500;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'ALLSTATS LAST'));
-----------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |           |      1 |        |      0 |00:00:00.01 |       3 |
|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| IDXSEL    |      1 |      1 |      0 |00:00:00.01 |       3 |
|*  2 |   INDEX RANGE SCAN                  | IDXSEL_ID |      1 |      1 |      1 |00:00:00.01 |       2 |
-----------------------------------------------------------------------------------------------------------

However as soon as we use adaptive dynamic sampling (in the real life case this kicked in because we had a SQL Plan Directive, but for simplicity of the test case I am forcing this with a hint) you can see that option 2 is chosen.
Note, the fact that nearly 10 times the number of buffer gets (28 instead of 3) does tend to confirm that this is not the optimal plan.

SELECT /*+ GATHER_PLAN_STATISTICS DYNAMIC_SAMPLING(11)  */ NULL FROM idxsel WHERE id = 1 AND r > 500;
 SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'ALLSTATS LAST'));
 ------------------------------------------------------------------------------------------
 | Id  | Operation        | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
 ------------------------------------------------------------------------------------------
 |   0 | SELECT STATEMENT |             |      1 |        |      0 |00:00:00.01 |      28 |
 |*  1 |  INDEX RANGE SCAN| IDXSEL_R_ID |      1 |      1 |      0 |00:00:00.01 |      28 |
 ------------------------------------------------------------------------------------------

Drilling into the CBO trace (10053) gives some clues as to what is going on.  Below are some extracts from the trace en evaluating IDXSEL_R_ID.
Firstly without Dynamic Sampling.  ix_sel and ix_sel_with_filters seem correct.
(Note, for more understanding of ix_sel and ix_sel_with_filters refer to Jonathan Lewis’s book Cost-Based Oracle Fundamentals).

****** Costing Index IDXSEL_R_ID
 Access Path: index (IndexOnly)
 Index: IDXSEL_R_ID
 ix_sel: 0.499931  ix_sel_with_filters: 4.9993e-05
 Cost: 27.037320  Resp: 27.037320  Degree: 1

However with Dynamic Sampling ix_sel_with_filters remains correct, but ix_sel is way off.
(Note for full disclosure this behaviour is slightly different on the real life data where I actually saw ix_sel and ix_sel_with_filters being adjusted to the same wrong value).
This brings the cost of this access path down to a value of 2, which causes it to be chosen ahead of that using IDXSEL_ID.

****** Costing Index IDXSEL_R_ID
 >> Index Card adjusted from 4999.313010 to 1.000000 due to adaptive dynamic sampling
 Access Path: index (IndexOnly)
 Index: IDXSEL_R_ID
 ix_sel: 1.0000e-04  ix_sel_with_filters: 4.9993e-05
 Cost: 2.000452  Resp: 2.000452  Degree: 1

Update:

Oracle support have created bug 24596139 – DYNAMIC SAMPLING LEADS TO INCORRECT INDEX SELECTION DUE TO WRONG COST.

Problems with opatchauto

We have been attempting to install the latest GI PSU on  a new AIX system, but having problems with opatchauto failing out with the error  below:

oracle.osysmodel.driver.sdk.productdriver.ProductDriverException:
   Unknown host name in config:hkxtsdbdv1
        at oracle.oplan.db.driver.crs.AbstractCrsProductDriver.findHostByName(AbstractCrsProductDriver.java:124)
        at oracle.oplan.db.driver.crs.AbstractCrsProductDriver.getLocalInfo(AbstractCrsProductDriver.java:774)
        at oracle.oplan.db.driver.crs.CrsProductDriver.buildSystemInstance(CrsProductDriver.java:225)
        at oracle.opatchauto.gi.GIPatchingUtil.generateOPatchAutoExecutionStep(GIPatchingUtil.java:202)
        at oracle.opatchauto.gi.GIPatchingUtil.generateapplysteps(GIPatchingUtil.java:286)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:60)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:37)
        at java.lang.reflect.Method.invoke(Method.java:611)
        at oracle.opatchauto.gi.GIPatching.processOPatchAutoUtility(GIPatching.java:217)
        at oracle.opatchauto.gi.GIPatching.process(GIPatching.java:275)
        at oracle.opatchauto.gi.OPatchauto.invokeGIPatching(OPatchauto.java:157)
        at oracle.opatchauto.gi.OPatchauto.main(OPatchauto.java:91)

Martin Bach has previously blogged about similar issues, although his solution didn’t work for us.

Eventually I realized that our hostname is actually set as uppercase (HKXTSDBDV1).  When I changed machine hostname to lower case then patch applied with no problems.

We are raising this a bug with Oracle support.