In the previous post I wrote about strangely behaving V$SQL. For some reason there were duplicate rows leading to wrong results issue when running DBMS_XPLAN.DISPLAY_CURSOR for a particular child cursor. I tried to reproduce the issue using simple test case – and it was reproduced.
Here it is. I’m starting to execute a simple query against DUAL while constantly changing optimizer environment, forcing Oracle to build a new child cursor for each execution:
The column is there for a long time – even 9i documentation have it. I’ve never thought about it until today when I caought something extraordinary on 220.127.116.11 instance.
Starting with 10g oracle introduced SQL_ID for simplicity; it is used in combination with CHILD_NUMBER to locate a particular row in the V$SQL. The documentation is clear (bold is mine):
V$SQL lists statistics on shared SQL areas without the GROUP BY clause and contains one row for each child of the original SQL text entered