Planned (Cursor) Obsolescence

I’ll start this blog-post by posing a question. Is it possible to have multiple records in v$sql for a given sql_id and child_number combination? While the title of this blog post may give you some clues, I’ll admit I’d always assumed that those values uniquely identified a child cursor.

As a bit of background we had a database availability situation this week, which we narrowed down to SGA issues, specifically bug 15881004 “Excessive SGA memory usage with Extended Cursor Sharing”. Some of our more complex SQL Statements were getting many (more than 700) child cursors. The reported reason for the child was “Bind mismatch(33)”.  Probably caused by bug 14176247 “Many child cursors using Adaptive Cursor Sharing with binds (due to BIND_EQUIV_FAILURE)”, although that is listed as fixed in 12.1 and this instance is running on 12.2.

We resolved the immediate issue by flushing the shared pool (admittedly not a great solution, but sometimes you got to do what you got to do), and created SQL Plan Baselines for those problem SQL statements so they would each just get one plan and child cursor.

We plan to monitor more closely for any SQL statements that do have many child cursors, however we need to make sure that even if that does happen it doesn’t break the system again.  One thing that seemed promising is the _cursor_obsolete_threshold parameter.  We had already reduced this parameter down to 1024 from it’s default of 8192 based on Mike Deitrich’s blog post but with this incident were considering reducing it further.  I think it’s wise to be wary of messing too much with underscore parameters but per Doc ID 2431353.1 Oracle Support say “the … parameter can be adjusted case-to-case basis should there be a problem”. For sure we had a significant problem with the setting at 1024 so plan to reduce further to 512.

We involved super consultant Stefan Koehler to review our findings and action plan, he was broadly in agreement, even recommending further reduction of the parameter value to 256.  However something puzzling me which I asked him was “What actually happens if the number of child cursors hit the value specified by this parameter”.  His answer “Well what happens is this … if your parent cursor got more than _cursor_obsolete_threshold child cursors it invalidates the parent (and in consequence all childs) and it starts from 0 again”

I was skeptical, my expectation was that Oracle would just invalidate the oldest unused child cursor and then re-use that child number.  Another thing puzzling me was happens if some of the child cursors were still held open?  Time to test this out for myself…
First let me demonstrate how I can get 4 child cursors for a given SQL Statement using different values of optimizer_index_cost_adj as a quick hack.
SQL> alter system flush shared_pool;
SQL> select count(*) from all_objects;
  COUNT(*)
     74807

SQL> select prev_sql_id from v$session where sid=sys_context('userenv','sid');
PREV_SQL_ID
9tz4qu4rj9rdp

SQL> alter session set optimizer_index_cost_adj=1;
SQL> select count(*) from all_objects;
  COUNT(*)
     74807

SQL> alter session set optimizer_index_cost_adj=2;
SQL> select count(*) from all_objects;
  COUNT(*)
     74807

SQL> alter session set optimizer_index_cost_adj=3;
SQL> select count(*) from all_objects;
  COUNT(*)
     74807

SQL> select sql_id, child_number, executions from v$sql where sql_id = '9tz4qu4rj9rdp';
SQL_ID            CHILD_NUMBER   EXECUTIONS
9tz4qu4rj9rdp                0            1
9tz4qu4rj9rdp                1            1
9tz4qu4rj9rdp                2            1
9tz4qu4rj9rdp                3            1

Let me reduce _cursor_obsolete_threshold at session level and re-run the test.

SQL> alter system flush shared_pool;
SQL> alter session set "_cursor_obsolete_threshold"=2;
SQL> alter session set optimizer_index_cost_adj=100;
SQL> select count(*) from all_objects;
  COUNT(*)
     74807

SQL> alter session set optimizer_index_cost_adj=1;
SQL> select count(*) from all_objects;
  COUNT(*)
     74807

SQL> alter session set optimizer_index_cost_adj=2;
SQL> select count(*) from all_objects;
  COUNT(*)
     74807

SQL> alter session set optimizer_index_cost_adj=3;
SQL> select count(*) from all_objects;
  COUNT(*)
     74807

SQL> select sql_id, child_number, executions from v$sql where sql_id = '9tz4qu4rj9rdp';
SQL_ID            CHILD_NUMBER   EXECUTIONS
9tz4qu4rj9rdp                0            1
9tz4qu4rj9rdp                1            1
9tz4qu4rj9rdp                0            1
9tz4qu4rj9rdp                1            1

Whoah…. each combination of sql_id and child number has two entries (not what I was expecting to see). To get a more full picture we need to look at a couple of additional fields, namely ‘address’ and ‘is_obsolete’.

SQL> select sql_id,  address, child_number, is_obsolete from v$sql where sql_id = '9tz4qu4rj9rdp';
SQL_ID          ADDRESS              CHILD_NUMBER IS_OBSOLETE
9tz4qu4rj9rdp   00000000610AB500                0 Y
9tz4qu4rj9rdp   00000000610AB500                1 Y
9tz4qu4rj9rdp   0000000073DDE788                0 N
9tz4qu4rj9rdp   0000000073DDE788                1 N

Although we tend to use sql_id as our handle for the parent cursor, Oracle actually uses the ‘Address’ field, and when the _cursor_obsolete_threshold value is exceeded, Oracle allocates a new parent cursor with a new ‘Address’.  This explains how Oracle copes when old child cursors are held open, they still stay in the shared pool, keeping their address, but are marked as obsolete, able to be aged out when they are no longer in use.

The other lessons here, firstly that Stefan knows his stuff, but also whenever someone tells you something, don’t just take it on trust, it’s normally easy to validate for yourself, and you may learn something about how Oracle works along the way

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s