Re: tracing makes the sql run faster

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

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