Skip to Main Content
  • Questions
  • When there are multiple children for shared SQL, how can a specific session know which child belongs to it?

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Heath.

Asked: May 13, 2005 - 5:22 pm UTC

Last updated: November 11, 2011 - 8:05 am UTC

Version: 10.1.0.2

Viewed 1000+ times

You Asked

I was experimenting with your dynamic_plan_table view to dynamically determine execution plans for SQL (the "Use DBMS_XPLAN and V$SQL_PLAN" section in Chapter 2, pages 90-92 of Effective Oracle By Design). This works as explained for some basic examples, but I suspected that it wouldn't work when the same SQL text had multiple children.

My test case:

-- Create user foo
SYS@ora10g>create user foo identified by bar;

User created.

SYS@ora10g>grant create session to foo;

Grant succeeded.

SYS@ora10g>grant create table to foo;

Grant succeeded.

SYS@ora10g>grant create view to foo;

Grant succeeded.

SYS@ora10g>grant select on V_$SQL_PLAN to foo with grant option;

Grant succeeded.

SYS@ora10g>grant select on v_$sql to foo;

Grant succeeded.

SYS@ora10g>grant create public synonym to foo;

Grant succeeded.

SYS@ora10g>alter user foo quota unlimited on users;

User altered.

SYS@ora10g>

-- Create user bar
SYS@ora10g>create user bar identified by foo;

User created.

SYS@ora10g>grant create session to bar;

Grant succeeded.

SYS@ora10g>grant create table to bar;

Grant succeeded.

SYS@ora10g>grant create view to bar;

Grant succeeded.

SYS@ora10g>grant select on v_$sql_plan to bar;

Grant succeeded.

SYS@ora10g>grant select on v_$sql to bar;

Grant succeeded.

SYS@ora10g>alter user bar quota unlimited on users;

User altered.

SYS@ora10g>

-- Create the schemas
SYS@ora10g>connect foo/bar@ora10g
Connected.
FOO@ora10g>create table yyy( a number );

Table created.

FOO@ora10g>insert into yyy values( 1 );

1 row created.

FOO@ora10g>commit;

Commit complete.

FOO@ora10g>create or replace view dynamic_plan_table as
2 select rawtohex(address) || '_' || child_number statement_id,
3 sysdate timestamp, operation, options, object_node,
4 object_owner, object_name, 0 object_instance,
5 optimizer, search_columns, id, parent_id, position,
6 cost, cardinality, bytes, other_tag, partition_start,
7 partition_stop, partition_id, other, distribution,
8 cpu_cost, io_cost, temp_space, access_predicates,
9 filter_predicates
10 from v$sql_plan;

View created.

FOO@ora10g>create public synonym dynamic_plan_table for dynamic_plan_table;

Synonym created.

FOO@ora10g>grant select on dynamic_plan_table to bar;

Grant succeeded.

FOO@ora10g>connect bar/foo@ora10g
Connected.
BAR@ora10g>create table yyy( a number );

Table created.

BAR@ora10g>insert into yyy values( 1 );

1 row created.

BAR@ora10g>insert into yyy values( 2 );

1 row created.

BAR@ora10g>commit;

Commit complete.

-- Generate the multiple versions of the shared SQL
BAR@ora10g>connect foo/bar@ora10g
Connected.
FOO@ora10g>select * from yyy;

A
----------
1

1 row selected.

FOO@ora10g>connect bar/foo@ora10g
Connected.
BAR@ora10g>select * from yyy;

A
----------
1
2

2 rows selected.

-- Now try to get my explain plan using the dynamic_plan_table view
BAR@ora10g>select plan_table_output
2 from TABLE( dbms_xplan.display(
3 'dynamic_plan_table',
4 ( select rawtohex(address) || '_' || child_number x
5 from v$sql
6 where sql_text = 'select * from yyy' ),
7 'serial' ) );
( select rawtohex(address) || '_' || child_number x
*
ERROR at line 4:
ORA-01427: single-row subquery returns more than one row


BAR@ora10g>

The ORA-01427 is expected because there are two rows in V$SQL for the same SQL text.

BAR@ora10g>select count(*) from V$SQL where sql_text = 'select * from yyy';

COUNT(*)
----------
2

1 row selected.

BAR@ora10g>

My question is what exactly can I use from V$SQL or any other data dictionary table joined back to V$SQL that would allow me to identify which specific SQL belongs to my session so that I can use this technique to get my execution plan?

Is the PARSING_USER_ID column in V$SQL sufficient for me to know which SQL belongs to my session? Rewriting the query to use that column works for my example, but I'd feel better to know that is the correct approach to take.

BAR@ora10g>select plan_table_output
2 from TABLE( dbms_xplan.display(
3 'dynamic_plan_table',
4 ( select rawtohex(address) || '_' || child_number x
5 from v$sql
6 where sql_text = 'select * from yyy'
7 and parsing_user_id = (
8 select user_id
9 from all_users
10 where username = user) ),
11 'serial' ) );

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------

---------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)|
| 1 | TABLE ACCESS FULL| YYY | 2 | 26 | 3 (0)|
---------------------------------------------------------------

Note
-----
- 'DYNAMIC_PLAN_TABLE' is old version

11 rows selected.

BAR@ora10g>

Thanks in advance.

and Tom said...

Ok, let's start by creating a pair of child cursors by having different optimizer environments:


ops$tkyte@ORA10G> create table t (x int);
Table created.

ops$tkyte@ORA10G> variable x refcursor
ops$tkyte@ORA10G> variable y refcursor
ops$tkyte@ORA10G> alter system flush shared_pool;
System altered.

ops$tkyte@ORA10G> alter session set optimizer_mode=all_rows;
Session altered.

ops$tkyte@ORA10G> exec open :x for 'select * from t';
PL/SQL procedure successfully completed.

ops$tkyte@ORA10G> alter session set optimizer_mode=first_rows;
Session altered.

ops$tkyte@ORA10G> exec open :y for 'select * from t';
PL/SQL procedure successfully completed.

ops$tkyte@ORA10G> select sql_id, sql_text
2 from v$open_cursor
3 where sid = (select sid from v$mystat where rownum=1 )
4 and sql_text like '%select * from t%';

SQL_ID SQL_TEXT
------------- --------------------
89km4qj1thh13 select * from t
89km4qj1thh13 select * from t

I know there are two child cursors now -- different optimizer modes...

ops$tkyte@ORA10G> select distinct a.sid, a.sql_id, a.sql_text, b.child_number
2 from v$open_cursor a, v$sql b, dba_kgllock c
3 where b.child_address = c.kgllkhdl
4 and a.saddr = c.kgllkuse
5 and a.address = b.address
6 and a.sid = (select sid from v$mystat where rownum=1)
7 and a.sql_text like '%select * from t%';

SID SQL_ID SQL_TEXT CHILD_NUMBER
---------- ------------- -------------------- ------------
262 89km4qj1thh13 select * from t 0
262 89km4qj1thh13 select * from t 1

and we are using both...

ops$tkyte@ORA10G>
ops$tkyte@ORA10G> disconnect
Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - Production
With the Partitioning, OLAP and Data Mining options
ops$tkyte@ORA10G> connect /
Connected.
ops$tkyte@ORA10G> alter session set optimizer_mode=all_rows;

Session altered.

ops$tkyte@ORA10G> exec open :x for 'select * from t';

PL/SQL procedure successfully completed.

ops$tkyte@ORA10G> select distinct a.sid, a.sql_id, a.sql_text, b.child_number
2 from v$open_cursor a, v$sql b, dba_kgllock c
3 where b.child_address = c.kgllkhdl
4 and a.saddr = c.kgllkuse
5 and a.address = b.address
6 and a.sid = (select sid from v$mystat where rownum=1)
7 and a.sql_text like '%select * from t%';

SID SQL_ID SQL_TEXT CHILD_NUMBER
---------- ------------- -------------------- ------------
262 89km4qj1thh13 select * from t 0

using cursor 0 in this session and

ops$tkyte@ORA10G>
ops$tkyte@ORA10G> disconnect
Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - Production
With the Partitioning, OLAP and Data Mining options
ops$tkyte@ORA10G> connect /
Connected.
ops$tkyte@ORA10G> alter session set optimizer_mode=first_rows;

Session altered.

ops$tkyte@ORA10G> exec open :x for 'select * from t';

PL/SQL procedure successfully completed.

ops$tkyte@ORA10G> select distinct a.sid, a.sql_id, a.sql_text, b.child_number
2 from v$open_cursor a, v$sql b, dba_kgllock c
3 where b.child_address = c.kgllkhdl
4 and a.saddr = c.kgllkuse
5 and a.address = b.address
6 and a.sid = (select sid from v$mystat where rownum=1)
7 and a.sql_text like '%select * from t%';

SID SQL_ID SQL_TEXT CHILD_NUMBER
---------- ------------- -------------------- ------------
262 89km4qj1thh13 select * from t 1

cursor one in that session, but remember, you can be using BOTH


Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Which child?

Atta Gianno, November 10, 2011 - 6:42 pm UTC

Last phrase says, 'cursor one in that session, but remember, you can be using BOTH'. Does this means that independently of the optimizer's environment settings, the session can choose any of the two already existing child cursors? If yes, how Oracle chooses which one of the two. Is Oracle version important to that?

Thanks in advance.
Tom Kyte
November 11, 2011 - 8:05 am UTC

Does this means that independently of the optimizer's environment
settings,


no, it means "dependent on the optimizer's environment settings", not independent.

There are two child cursors - one for optimizer environment 1, and one for optimizer environment 2.

You will use the one that matches your current environment, but remember also that you control this environment.


Look at the example - one session, two cursors - two different child cursors - one each for each of the environments they were opened in.

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.