Adaptive Cursor Sharing not working as expected

We’ve been experiencing some issues with multiple child cursors for a given SQL statement, and I’ve just spent some time working on building a reproducible testcase of one problem that I thought I’d share in the hopes of documenting the behavior.

It is a python script connecting to an Oracle Database (tested against 12.2 and 19.6), so requires the cx_Oracle Python module.

import cx_Oracle
import argparse 

def setup_t1(con):
    cursor = con.cursor()
    try:
        cursor.execute("drop table t1")
    except Exception:
        pass
    cursor.execute("create table t1 (c1 number, n1 nchar(2), n2 nchar(2))")
    cursor.execute("insert into t1 select 1, to_char(round(rownum/10)),  to_char(mod(rownum, 2)) from all_objects where rownum <= 20")
    con.commit
    cursor.callproc("sys.dbms_stats.gather_table_stats", (None, 't1'))
    cursor.close()                    

def setup_t2(con):
    cursor = con.cursor()
    try:
        cursor.execute("drop table t2")
    except Exception:
        pass
    cursor.execute("create table t2(c1 number)")
    cursor.execute("create index i2 on t2(c1)")
    cursor.execute("insert into t2(c1) select rownum from all_objects where rownum <= 1000")
    con.commit
    cursor.callproc("sys.dbms_stats.gather_table_stats", \
                    keywordParameters = dict(ownname = None, tabname = "t2", method_opt = "for all columns size auto"))
    cursor.close()                    

def run_query(con, p1, p2):
    cursor = con.cursor()
    query = "select  count(*) from t1, t2 where t1.n1 = :1 and t1.n2 = :2 and t1.c1 = t2.c1"
    cursor.execute(query,[p1, p2])
    cursor.fetchall()
    cursor.close()

def run_query_0(con):
    run_query(con, '0','0')    

def run_query_0_space(con):
    run_query(con, '0 ','0 ')  

def get_sql_id(con):
    cursor = con.cursor()
    query = "select prev_sql_id from v$session where sid = userenv('SID')"
    cursor.execute(query)     
    row = cursor.fetchone()    
    cursor.close()
    return row[0]

def dump_sql(con, sql_id):
    cursor = con.cursor()    
    cursor.execute("select plan_hash_value, count(*) from v$sql where sql_id = :1 and is_shareable = 'Y' and is_obsolete = 'N' group by plan_hash_value", [sql_id])     
    rows = cursor.fetchall()    
    for row in rows:
        print (row)
    cursor.close()

parser = argparse.ArgumentParser()
parser.add_argument('--connection', type=str)
args = parser.parse_args()

con = cx_Oracle.connect(args.connection)
setup_t1(con)
setup_t2(con)
run_query_0(con)
run_query_0_space(con)
run_query_0(con)
sql_id = get_sql_id(con)
con.close

con = cx_Oracle.connect(args.connection)
for x in range(0, 80):
    run_query_0(con)
    run_query_0_space(con)

dump_sql(con, sql_id)
con.close

Results are as below, showing 81 active child cursors.

('66dbrch2wu34f', 3482066175, 1)
('66dbrch2wu34f', 2913850814, 80)

I’m not sure why it’s necessary to close and re-open the connection between the first three executions (when it becomes bind aware) and the subsequent executions, but it is. If both the nchar columns are char (or nvarchar2) the issue doesn’t reproduce.

It seems the database is not correctly handling the Cursor Selectivity Cubes:

SELECT
    predicate,
    range_id,
    low,
    high,
    COUNT(*)
FROM
    v$sql_cs_selectivity
WHERE
    sql_id = '66dbrch2wu34f'
GROUP BY
    predicate,
    range_id,
    low,
    high

PREDICATE                                  RANGE_ID LOW        HIGH         COUNT(*)
---------------------------------------- ---------- ---------- ---------- ----------
=1                                                0 0.300000   0.366667          317

SQL>
It also seems like the fact that the plan is adaptive is a factor. If I hint the query to not allow adaptive plans, with OPT_PARAM('_optimizer_adaptive_plans','false') I don't see the problem.
select * from dbms_xplan.display_cursor('66dbrch2wu34f', null, format=>'+adaptive')

-----------------------------------------------------------------------------------------
|   Id  | Operation                      | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|     0 | SELECT STATEMENT               |      |       |       |     4 (100)|          |
|     1 |  SORT AGGREGATE                |      |     1 |    17 |            |          |
|  *  2 |   HASH JOIN                    |      |     3 |    51 |     4   (0)| 00:00:01 |
|-    3 |    NESTED LOOPS                |      |     3 |    51 |     4   (0)| 00:00:01 |
|-    4 |     STATISTICS COLLECTOR       |      |       |       |            |          |
|  *  5 |      TABLE ACCESS STORAGE FULL | T1   |     3 |    39 |     2   (0)| 00:00:01 |
|- *  6 |     INDEX RANGE SCAN           | I2   |     1 |     4 |     2   (0)| 00:00:01 |
|     7 |    INDEX STORAGE FAST FULL SCAN| I2   |  1000 |  4000 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("T1"."C1"="T2"."C1")
   5 - storage(("T1"."N1"=SYS_OP_C2C(:1) AND "T1"."N2"=SYS_OP_C2C(:2)))
       filter(("T1"."N1"=SYS_OP_C2C(:1) AND "T1"."N2"=SYS_OP_C2C(:2)))
   6 - access("T1"."C1"="T2"."C1")

Note
-----
   - this is an adaptive plan (rows marked '-' are inactive)

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top