more child cursors in query

All posts relating to Oracle database administration.

Moderator: Tim...

more child cursors in query

Postby shrinika » Fri Oct 04, 2013 4:23 pm

Tim, I see lot of child cursors in my oracle 11.2.0.2 database.

SQL> select count(*)
2 from v$sql
3 where sql_id='91b43q7rdqwpd';

Here is the output of the above query ==> 207

the query has no bind variable and all values are hardcoded in the where clause. Is that the reason it caused these many
child cursors? any input? please...
shrinika
Advisor
 
Posts: 255
Joined: Fri Jun 13, 2008 3:18 pm

Re: more child cursors in query

Postby Tim... » Fri Oct 04, 2013 5:00 pm

Hi.

This might help explain:

https://blogs.oracle.com/optimizer/entr ... lar_in_10g

What are your CURSOR_SHARING settings?

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17937
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: more child cursors in query

Postby shrinika » Fri Oct 04, 2013 6:17 pm

thanks Tim! I read your link.

cursor_sharing is EXACT.

This also must be one of the reason!

What do you think?
shrinika
Advisor
 
Posts: 255
Joined: Fri Jun 13, 2008 3:18 pm

Re: more child cursors in query

Postby Tim... » Sat Oct 05, 2013 10:17 am

Hi.

I would only expect to see this in the cases listed by Maria. I would not expect to see this with cursor_sharing=exact, as there are no bind variables, so ACS can not kick in.

Sounds like a question for Oracle Support to me.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17937
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: more child cursors in query

Postby shrinika » Sat Oct 05, 2013 7:18 pm

Thanks Tim!
shrinika
Advisor
 
Posts: 255
Joined: Fri Jun 13, 2008 3:18 pm

Re: more child cursors in query

Postby Tim... » Sun Oct 06, 2013 9:15 am

:)
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17937
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK


Return to Oracle Database Administration

Who is online

Users browsing this forum: No registered users and 3 guests