Hello,
in a Standard Edition database of version 11.2.0.3 I see a strange behaviour that I can't explain at the moment: if I issue a simple query on dba_temp_free_space I get multiple child cursors for the first execution:
-- first execution of the query (that returns 3 rows)
select /* test: cursor duplicate */ *
from dba_temp_free_space;
select child_number, open_versions, fetches, rows_processed
, executions, px_servers_executions, parse_calls, buffer_gets
from v$sql
where sql_id = '69azwxdshhffc'
order by child_number;
CHILD_NUMBER OPEN_VERSIONS FETCHES ROWS_PROCESSED EXECUTIONS PX_SERVERS_EXECUTIONS PARSE_CALLS BUFFER_GETS
------------ ------------- ---------- -------------- ---------- --------------------- ----------- -----------
0 1 2 3 1 0 1 7
1 0 0 0 0 1 1 55
2 0 0 0 0 1 1 2
select VERSION_COUNT, LOADED_VERSIONS, EXECUTIONS, PX_SERVERS_EXECUTIONS, PARSE_CALLS, BUFFER_GETS, ROWS_PROCESSED
from v$sqlarea
where sql_id = '69azwxdshhffc'
VERSION_COUNT LOADED_VERSIONS EXECUTIONS PX_SERVERS_EXECUTIONS PARSE_CALLS BUFFER_GETS ROWS_PROCESSED
------------- --------------- ---------- --------------------- ----------- ----------- --------------
3 3 1 2 3 64 3
-- second execution
select /* test: cursor duplicate */ *
from dba_temp_free_space;
select child_number, open_versions, fetches, rows_processed
, executions, px_servers_executions, parse_calls, buffer_gets
from v$sql
where sql_id = '69azwxdshhffc'
order by child_number;
CHILD_NUMBER OPEN_VERSIONS FETCHES ROWS_PROCESSED EXECUTIONS PX_SERVERS_EXECUTIONS PARSE_CALLS BUFFER_GETS
------------ ------------- ---------- -------------- ---------- --------------------- ----------- -----------
0 0 2 3 1 0 1 7
1 0 0 0 0 1 1 55
2 0 0 0 0 1 1 2
3 1 2 3 1 0 1 7
4 0 0 0 0 1 1 55
5 0 0 0 0 1 1 2
select VERSION_COUNT, LOADED_VERSIONS, EXECUTIONS, PX_SERVERS_EXECUTIONS, PARSE_CALLS, BUFFER_GETS, ROWS_PROCESSED
from v$sqlarea
where sql_id = '69azwxdshhffc'
VERSION_COUNT LOADED_VERSIONS EXECUTIONS PX_SERVERS_EXECUTIONS PARSE_CALLS BUFFER_GETS ROWS_PROCESSED
------------- --------------- ---------- --------------------- ----------- ----------- --------------
6 6 2 4 6 128 6
Just for the sake of completeness: the given system is a two-node-RAC cluster.
So every following execution creates new child cursors:
- I don't understand why I get three child cursors for one execution.
- I don't understand why the following executions don't reuse an existing cursor.
Taking a look at v$sql_shared_cursor I see the reasons the optimizer mentions for not reusing the cursors - but again I fail to understand
select child_number
, PX_MISMATCH
, USE_FEEDBACK_STATS
, TOP_LEVEL_RPI_CURSOR
from v$sql_shared_cursor
where sql_id = '69azwxdshhffc'
order by child_number;
CHILD_NUMBER P U T
------------ - - -
0 N Y N
1 Y N N
2 Y N N
3 N N Y
4 Y N N
5 Y N N
But again I fail to see why these reasons should be valid. If I issue the query frequently enough I see strange plan renderings when I use dbms_xplan.display_cursor - as described by Timur Akhmadeev in https://timurakhmadeev.wordpress.com/2012/03/19/obsolete-cursors/.
So the question is: has someone seen something like this before? And is there an explanation why the server stops to reuse cursors?
Thanks for your answers in advance.
Regards
Martin Preiss