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)