Re: Hard Parses way more than executions!?

I learnt that the output of the Query below came a bit out of the format hence I am trying again which I belive reads OK :

Child#	PLAN_HASH	PARSES	H_PARSE	EXECS	FETCHES	ROWS_P	INVS
0	3675718404	1075537	1	9400055	9400074	8713889	0
1	3675718404	40488	1194	366464	366464	340475	0
2	3675718404	324	23481	2606	2606	2391	2
3	3675718404	13	1092	106	106	95	0
4	3675718404	98	4572	790	790	738	1
5	3675718404	55	276	438	438	383	0
6	3675718404	2	137708	37	37	34	20
7	3675718404	8	201010	75	75	68	53
8	3675718404	948	62178	7213	7213	6960	8
9	3675718404	33	14	367	367	354	0

Hope to get some answers now please :-)

Thanks.

On Wed, Nov 30, 2011 at 1:27 PM, PD Malik <pdthedba_at_gmail.com> wrote:
>
> Hello Oracle List,
>
> How can the number of Hard Parses (or Loads) be way more than the number of executions please  :
>
> SQL>select
>     child_number    child#,
>     plan_hash_value plan_hash,
>     parse_calls parses,
>     loads h_parses,
>     executions execs,
>     fetches,
>     rows_processed rows_p,
>     invalidations invs
> from
>     v$sql
>  where
>     sql_id = ('fmfdkztk8vx23')
>  and child_number like '&2'
>  and executions > 0
>  order by
>     sql_id,
>     hash_value,
>     child_number;
> old  17: and child_number like '&2'
> new  17: and child_number like '%'
>
> Child #PLAN_HASHPARSESH_PARSESEXECSFETCHESROWS_PINVS
> 03675718404107553719400055940007487138890
> 136757184044048811943664643664643404750
> 23675718404324234812606260623912
> 33675718404131092106106950
> 436757184049845727907907381
> 53675718404552764384383830
> 63675718404213770837373420
> 73675718404820101075756853
> 83675718404948621787213721369608
> 9367571840433143673673540
>
> I am almost convinced its got to be some sort of bug and before I fire it off to Oracle Support I thought I'll raise the question here in case someone can enlghten me with a completely new idea to me how it can happen please?
>
> RDBMS Version : 10.2.0.5
>
> Thanks.

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