Re: tracing makes the sql run faster
- From: Wolfgang Breitling <breitliw@xxxxxxxxxxxxx>
- To: Prasada.Gunda@xxxxxxxxxxxxxxxx
- Date: Wed, 3 Nov 2004 00:31:36 -0800
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
