Re: Hard Parses way more than executions!?
Date: Wed, 30 Nov 2011 16:39:44 +0000
Message-ID: <CAHgaR1Cx9gCoT29RgJyW-LDxoN11XqRrhEigOxgORjquc2D9RA_at_mail.gmail.com>
Hello All,
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-lReceived on Wed Nov 30 2011 - 10:39:44 CST