Little insight of child cursor part -1

Simple queries against demo schema HR under 10g database.

==============================================

SQL> select * from emp,dept where emp.ename=’SMITH’ and dept.deptno=emp.deptno;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC
———- ———- ——— ———- ——— ———- ———- ———- ———- ————– ————-
7369 SMITH CLERK 7902 17-DEC-80 800 20 20 RESEARCH DALLAS

SQL> select * from emp,dept where emp.ename=’CLARK’ and dept.deptno=emp.deptno;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC
———- ———- ——— ———- ——— ———- ———- ———- ———- ————– ————-
7782 CLARK MANAGER 7839 09-JUN-81 2450 10 10 ACCOUNTING NEW YORK

SQL> select * from emp,dept where emp.ename=’AMIT’ and dept.deptno=emp.deptno;

no rows selected

———————————————————————————————

Listing of child cursor attached to same parent cursor. Oracle creates child cursor for every set of bind variable pair. In that case I was changing ename with SMITH, CLARK, & AMIT. So every time oracle created one child cursor to hold bind variables. Oracle tries to prepare a different execution plan for

same query based on best access path available for the same.
###################################################################

SQL> select SQL_ID,HASH_VALUE,CHILD_NUMBER,ADDRESS,sql_text from v$sql where sql_text like ‘select%emp%dept%’;

SQL_ID HASH_VALUE CHILD_NUMBER ADDRESS SQL_TEXT
————- ———- ———— ——– ——————————————————————————————
9uby0yda1yr73 1411341539 0 36673A28 select * from emp,dept where emp.ename=:”SYS_B_0″ and dept.deptno=emp.deptno
9uby0yda1yr73 1411341539 1 36673A28 select * from emp,dept where emp.ename=:”SYS_B_0″ and dept.deptno=emp.deptno
9uby0yda1yr73 1411341539 2 36673A28 select * from emp,dept where emp.ename=:”SYS_B_0″ and dept.deptno=emp.deptno
9uby0yda1yr73 1411341539 3 36673A28 select * from emp,dept where emp.ename=:”SYS_B_0″ and dept.deptno=emp.deptno

##########################################Check the sql plans using dbms_xplan

######################Child -1

SQL> select * from table(dbms_xplan.display_cursor(‘9uby0yda1yr73’,1));

PLAN_TABLE_OUTPUT
—————————————————————————————————-
SQL_ID 9uby0yda1yr73, child number 1
————————————-
select * from emp,dept where emp.ename=:”SYS_B_0″ and
dept.deptno=emp.deptno

Plan hash value: 1123238657

—————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————
| 0 | SELECT STATEMENT | | | | 5 (100)| |
|* 1 | HASH JOIN | | 1 | 57 | 5 (20)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| EMP | 1 | 37 | 2 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| DEPT | 4 | 80 | 2 (0)| 00:00:01 |
—————————————————————————

Predicate Information (identified by operation id):
—————————————————

1 – access(“DEPT”.”DEPTNO”=”EMP”.”DEPTNO”)
2 – filter(“EMP”.”ENAME”=:SYS_B_0)

22 rows selected.
######################Child -2

SQL> select * from table(dbms_xplan.display_cursor(‘9uby0yda1yr73′,2));

PLAN_TABLE_OUTPUT
—————————————————————————————————-
SQL_ID 9uby0yda1yr73, child number 2
————————————-
select * from emp,dept where emp.ename=:”SYS_B_0″ and
dept.deptno=emp.deptno

Plan hash value: 1123238657

—————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————
| 0 | SELECT STATEMENT | | | | 5 (100)| |
|* 1 | HASH JOIN | | 1 | 57 | 5 (20)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| EMP | 1 | 37 | 2 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| DEPT | 4 | 80 | 2 (0)| 00:00:01 |
—————————————————————————

Predicate Information (identified by operation id):
—————————————————

1 – access(“DEPT”.”DEPTNO”=”EMP”.”DEPTNO”)
2 – filter(“EMP”.”ENAME”=:SYS_B_0)

22 rows selected.

#################################### V$SQL_PLAN_STATISTICS will give you detailed information for all sqls executed

SQL> select SQL_ID,CHILD_ADDRESS,CHILD_NUMBER,LAST_STARTS,STARTS, LAST_OUTPUT_ROWS,OUTPUT_ROWS,LAST_ELAPSED_TIME,ELAPSED_TIME from V$SQL_PLAN_STATISTICS where sql_id=’9uby0yda1yr73′;

SQL_ID CHILD_AD CHILD_NUMBER LAST_STARTS STARTS LAST_OUTPUT_ROWS OUTPUT_ROWS LAST_ELAPSED_TIME ELAPSED_TIME
————- ——– ———— ———– ———- —————- ———– —————– ————
9uby0yda1yr73 3A042AEC 3 1 1 0 0 264 264
9uby0yda1yr73 3A042AEC 3 1 1 0 0 97 97
9uby0yda1yr73 3A042AEC 3 0 0 0 0 0 0
9uby0yda1yr73 3A042D3C 2 1 1 0 0 193 193
9uby0yda1yr73 3A042D3C 2 1 1 0 0 48 48
9uby0yda1yr73 3A042D3C 2 0 0 0 0 0 0
9uby0yda1yr73 3666C78C 1 1 1 1 1 348 348
9uby0yda1yr73 3666C78C 1 1 1 1 1 48 48
9uby0yda1yr73 3666C78C 1 1 1 4 4 40 40
9uby0yda1yr73 36673944 0 1 2 1 2 635 38399
9uby0yda1yr73 36673944 0 1 2 1 2 48 25744
9uby0yda1yr73 36673944 0 1 2 4 8 132 10780

12 rows selected.

What was the value of bind variables ?

check v$sql_bind_capture, it will give you detail about bind variables. 🙂

SQL> select SQL_ID,CHILD_NUMBER,NAME,WAS_CAPTURED,LAST_CAPTURED,VALUE_STRING from V$SQL_BIND_CAPTURE where SQL_ID=’9uby0yda1yr73’;

SQL_ID CHILD_NUMBER NAME WAS LAST_CAPT VALUE_STRING
————- ———— —————————— — ——— —————
9uby0yda1yr73 2 :SYS_B_0 YES 03-FEB-11 AMIT
9uby0yda1yr73 1 :SYS_B_0 YES 03-FEB-11 CLARK
9uby0yda1yr73 0 :SYS_B_0 YES 03-FEB-11 SMITH

v$sql_bind_capture is good source to check what exactly used under bind variables.

I’ll come with more detail…….

Advertisements

Share Your Comment

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

Pierre blog

Pierre Forstmann Oracle Database blog

flashdba

Oracle databases, storage and the high-performance world of flash memory

Future Veterans

Ramblings about Oracle

Ranjeet Srivastava

Smile! You’re at the best blog ever

Kevin Closson's Blog: Platforms, Databases and Storage

Platform, Database and Storage Topics

Real Life Database / SQL Experiences : An Oracle Blog from Vivek Sharma

Being an Oracle Professional, I like to share all my Real Life Performance Tuning Challenges and Experiences. The Content and Views on this site are my own and not necessarily those of Oracle. While, I write on my real life experiences, the resolutions mentioned are solely mine. Comments / Criticisms are always a welcome.

Frits Hoogland Weblog

IT Technology; Oracle, linux, TCP/IP and other stuff I find interesting

OraStory

Dominic Brooks on Oracle Performance, Tuning, Data Quality & Sensible Design ... (Now with added Sets Appeal)

ASM Support Guy

Just Another Crazy Oracle DBA

Exadata Certification

Just Another Crazy Oracle DBA

Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Sangram keshari's Oracle Blog

The Fusion Middleware Administration & Database Administration Blog

Amit Saraswat

Just Another Crazy Oracle DBA

Oracle Scratchpad

Just another Oracle weblog

The Tom Kyte Blog

Just Another Crazy Oracle DBA

Hemant's Oracle DBA Blog

Just Another Crazy Oracle DBA

Uwe Hesse

about Database Technology

Richard Foote's Oracle Blog

Focusing Specifically On Oracle Indexes, Database Administration and Some Great Music

%d bloggers like this: