| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: tracing makes the sql run faster
Thanks Wolfgang for your response. It is very helpful to know that the trace is doing fresh parse.
Should there be a separate child number for each session that has the trace on? The reason I am asking is, I put the trace on for each user and there are around 12 users. In v$sql, I am only seeing three child numbers 0,1 and 2 for the same address and hash_value. And, I see the executions increasing on these so CBO is reusing these.
When does the plan_hash_value resets to 0? When it is set to 0, as we know, we can not get the plan from v$sql_plan. It may the normal behavior but I was curious why Oracle reset it to 0? Is it because the plan is aging out?
Best Regards,
Prasad
860 843 8377
Wolfgang
Breitling To: Prasada.Gunda_at_hartfordlife.com
<breitliw_at_centrex cc: ryan_gaffuri_at_comcast.net, oracle-l_at_freelists.org
cc.com> Subject: Re: tracing makes the sql run faster
11/02/2004 04:00
AM
Does the sql use bind variables on a column that has a histogram? In that
case
you may have been stuck with a plan based on the bind variable value from
the
very first parse and that plan is reused for all subsequent executions.
When
you trace your session you get a new child cursor and a fresh parse - but
only
for your session. All the others use the shared plan. You mentioned that
after
the bounce the plan is the fast one now. That would fit with this scenario
if
the now first parse used a different bind variable value which produced the
faster plan - faster for the majority of the sql at least.
Quoting Prasada.Gunda_at_hartfordlife.com:
>
>
>
>
> All the tables and indexes are analyzed. Only difference is, if trace is
> on, it runs faster. if trace is off, it runs slower.
>
> I set up the trace on/off thru logon/logoff trigger for that application
> user till the problem is resolved. I am cleaning up the udump directory
> using a script.
>
> Thanks.
>
> Best Regards,
> Prasad
> 860 843 8377
>
>
>
>
> ryan_gaffuri_at_comc
>
> ast.net To:
> Prasada.Gunda_at_hartfordlife.com, oracle-l_at_freelists.org
>
> cc:
>
> 11/01/2004 03:52 Subject: Re: tracing
makes
> the sql run faster
> PM
>
>
>
>
>
>
>
>
>
> when tables are not analyzed or your stats are old, the moon can make
> queries run at different speeds. thats usually what does it. reanalyzed
the
> tables.
>
> -------------- Original message --------------
>
> >
> >
> >
> >
> > Hello All,
> >
> > We have an application which uses Oracle Forms 6i. One particular
query
> > takes very long time when it runs in the forms application. When I
trace
>
> > the user session to see what was going on, surprisingly query runs
much
> > faster and tkprof output looks fine. This query runs faster when I run
> it
> > sqlplus even with out the trace on.
> >
> > How else I can trace to see what is going on? Did any one experience
> this
> > before? I am working with Oracle but wanted to check here as well.
> > Environment : 9.2.0.5 and Sun Solaris.
> >
> > Any thoughts or inputs are much appreciated.
> >
> > Thanks.
> >
> > Best Regards,
> > Prasad
> > 860 843 8377
> >
> >
> >
> >
>
-- regards Wolfgang Breitling Oracle 7,8,8i,9i OCP DBA Centrex Consulting Corporation www.centrexcc.com -- http://www.freelists.org/webpage/oracle-lReceived on Tue Nov 02 2004 - 14:35:16 CST
 |
 |