Re: tracing makes the sql run faster
- From: Prasada.Gunda@xxxxxxxxxxxxxxxx
- To: Wolfgang Breitling <breitliw@xxxxxxxxxxxxx>
- Date: Wed, 3 Nov 2004 11:04:51 -0500
Thank you so much for your time and responses.
Best Regards,
Prasad
860 843 8377
Wolfgang
Breitling To:
Prasada.Gunda@xxxxxxxxxxxxxxxx
<breitliw@centrex cc: oracle-l@xxxxxxxxxxxxx
cc.com> Subject: Re: tracing makes the
sql run faster
11/03/2004 03:31
AM
Quoting Prasada.Gunda@xxxxxxxxxxxxxxxx:
>
> 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.
*************************************************************************
--
//www.freelists.org/webpage/oracle-l
Other related posts:
