Re: tracing makes the sql run faster

Thank you so much for your time and responses.

Best Regards,
Prasad
860 843 8377

                                                                                                                                       
                      Wolfgang                                                                                                         
                      Breitling                To:       Prasada.Gunda_at_hartfordlife.com                                                
                      <breitliw_at_centrex        cc:       oracle-l_at_freelists.org                                                        
                      cc.com>                  Subject:  Re: tracing makes the sql run faster                                          
                                                                                                                                       
                      11/03/2004 03:31                                                                                                 
                      AM                                                                                                               
                                                                                                                                       
                                                                                                                                       

Quoting Prasada.Gunda_at_hartfordlife.com:
>
> I think method_opt was default when collected the stats using dbms_stats.
> All the columns have end point 0 and 1 in the dba_tab_histograms.
>

If all the columns have only endpoints 0 and 1 then you don't really have histograms. These are just for the min (LO) and max (HI) values of the column
values.
I was only thinking of skewed data distribution as the possibility to lead to
different execution plans with bind variables and peeking, but of course there
is the much simpler possibility of different value ranges. It could make quite
a difference in the access path if you are asking for data for the most recent
two days as opposed to data for the most recent two years.

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

I'm not the expert on child cursors but I assume you'll have at least a different child cursor for each different execution plan. There are several

checks/properties which determine if an existing child cursor can be shared and
reused or if a new one needs to be created. Obviously all objects in the new
sql need to resolve to the same objects in an existing child cursor for that to
be reused.

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

I am not sure the plan_hash_value gets actively "reset" to 0. I assume that the
memory area which is exposed as v$sql contains a pointer to the memory area

which is exposed as v$sql_plan with the latter aging out faster than the shared
cursors, invalidating that pointer, which then results in the plan_hash_value
being displayed as a 0.

--
regards

Wolfgang Breitling
Oracle 7,8,8i,9i OCP DBA
Centrex Consulting Corporation
www.centrexcc.com






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

--
http://www.freelists.org/webpage/oracle-l