Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: TKPROF: Long CPU time on Execute

Re: TKPROF: Long CPU time on Execute

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Tue, 20 Apr 2004 12:26:54 +0000 (UTC)
Message-ID: <c634ue$eeb$1@hercules.btinternet.com>

Note in-line

==
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

April 2004 Iceland http://www.index.is/oracleday.php June 2004 UK - Optimising Oracle Seminar July 2004 USA West Coast, Optimising Oracle Seminar August 2004 Charlotte NC, Optimising Oracle Seminar September 2004 USA East Coast, Optimising Oracle Seminar September2004 UK - Optimising Oracle Seminar

"Ryan Gaffuri" <rgaffuri_at_cox.net> wrote in message news:1efdad5b.0404200340.18226c30_at_posting.google.com...
> "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message
news:<c61pjh$f05$1_at_hercules.btinternet.com>...

> > Of course, you then need to find out why you
> > are re-optimising so often. A little background
> > to how the code arrives would help. And you
> > could check v$sql and v$sql_shared_cursor
> > to see if they give you any clues. (how many
> > child cursors are there for this SQL, and are
> > there any flagged reasons why Oracle couldn't
> > share).
> >
> > Regards
> what would lead to multiple child cursors?
>

The column names in v$sql_shared_cursor will give you clues about possible reasons for multiple child cursors. An easy one to demonstrate is the BIND_MISMATCH, although it's unlikely to be responsible for the thousands of optimisations shown here. If you use the dbms_sql package to open a cursor with a bind variable in it, then bind a variable to it, then execute it, you can get two child cursors by binding and executing twice - once with a varchar and once with a numeric. Received on Tue Apr 20 2004 - 07:26:54 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US