This forum is currently locked. You can't register or post questions at this time. (read more)

more child cursors in query

All posts relating to Oracle database administration.

Moderator: Tim...

shrinika
Advisor
Posts: 260
Joined: Fri Jun 13, 2008 3:18 pm

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...

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

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

shrinika
Advisor
Posts: 260
Joined: Fri Jun 13, 2008 3:18 pm

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?

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

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

shrinika
Advisor
Posts: 260
Joined: Fri Jun 13, 2008 3:18 pm

Re: more child cursors in query

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

Thanks Tim!

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

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


Return to “Oracle Database Administration”

Who is online

Users browsing this forum: No registered users and 6 guests

cron