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@xxxxxxxxxxxxxxxx <breitliw@centrex cc: ryan_gaffuri@xxxxxxxxxxx, oracle-l@xxxxxxxxxxxxx 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@xxxxxxxxxxxxxxxx: > > > > > 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@comc > > ast.net To: > Prasada.Gunda@xxxxxxxxxxxxxxxx, oracle-l@xxxxxxxxxxxxx > > 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 > > > > > > > > > ************************************************************************* > > PRIVILEGED AND CONFIDENTIAL: This communication, including attachments, > is > > for the exclusive use of addressee and may contain proprietary, > > confidential and/or privileged information. If you are not the intended > > recipient, any use, copying, disclosure, dissemination or distribution > is > > strictly prohibited. If you are not the intended recipient, please > notify > > the sender immediately by return e-mail, delete this communication and > > destroy all copies. > > > ************************************************************************* > > > > -- > > //www.freelists.org/webpage/oracle-l > > > -- > //www.freelists.org/webpage/oracle-l > -- regards Wolfgang Breitling Oracle 7,8,8i,9i OCP DBA Centrex Consulting Corporation www.centrexcc.com -- //www.freelists.org/webpage/oracle-l