Re: Hard parse elapsed time in AWR

Hi,

What is the version of your DB?
Not using bind variables is not the only cause of hard parse issues. ACS or 12c Adaptive feature can force queries to be reoptimized. Automatic Dynamic sampling can also increase significantly the time of hard parses.
Check the v$sql_shared_cursor view to see the reason for not sharing cursors.

Le 18 août 2016 16:56, "Hameed, Amir" <Amir.Hameed_at_xerox.com> a écrit :

> Hi,
>
> I am investigating a performance issue and looking at an AWR report from
> one of the two-node RAC instances. Below is the what I find interesting
> (highlighted in yellow).
>
> It seems that all of the DB CPU time is being spent on hard parsing but I
> am not finding any evidence that literal statements were being run during
> this interval.
>
>
>
> Thanks
>
>
>
>
>
> *Snap Id*
>
> *Snap Time*
>
> *Sessions*
>
> *Cursors/Session*
>
> Begin Snap:
>
> 36896
>
> 16-Aug-16 14:30:03
>
> 48
>
> 2.8
>
> End Snap:
>
> 36897
>
> 16-Aug-16 14:45:01
>
> 49
>
> 2.8
>
> Elapsed:
>
>
>
> 14.97 (mins)
>
>
>
>
>
> DB Time:
>
>
>
> 66.53 (mins)
>
>
>
>
>
>
>
> *Load Profile*
>
> *Per Second*
>
> *Per Transaction*
>
> *Per Exec*
>
> *Per Call*
>
> DB Time(s):
>
> 4.4
>
> 38.0
>
> 0.10
>
> 0.10
>
> DB CPU(s):
>
> 4.4
>
> 37.9
>
> 0.10
>
> 0.10
>
> Redo size:
>
> 2,107.4
>
> 18,023.9
>
>
>
>
>
> Logical reads:
>
> 232.6
>
> 1,988.9
>
>
>
>
>
> Block changes:
>
> 8.2
>
> 69.7
>
>
>
>
>
> Physical reads:
>
> 0.0
>
> 0.2
>
>
>
>
>
> Physical writes:
>
> 0.7
>
> 5.7
>
>
>
>
>
> User calls:
>
> 43.0
>
> 367.8
>
>
>
>
>
> Parses:
>
> 1.0
>
> 8.7
>
>
>
>
>
> Hard parses:
>
> 0.0
>
> 0.0
>
>
>
>
>
> W/A MB processed:
>
> 0.0
>
> 0.2
>
>
>
>
>
> Logons:
>
> 0.1
>
> 0.6
>
>
>
>
>
> Executes:
>
> 43.8
>
> 374.8
>
>
>
>
>
> Rollbacks:
>
> 0.0
>
> 0.0
>
>
>
>
>
> Transactions:
>
> 0.1
>
>
>
>
>
>
>
>
>
> *Instance Efficiency Percentages (Target 100%) *
>
> Buffer Nowait %:
>
> 100.00
>
> Redo NoWait %:
>
> 100.00
>
> Buffer Hit %:
>
> 99.99
>
> In-memory Sort %:
>
> 100.00
>
> Library Hit %:
>
> 99.99
>
> Soft Parse %:
>
> 99.89
>
> Execute to Parse %:
>
> 97.68
>
> Latch Hit %:
>
> 100.00
>
> Parse CPU to Parse Elapsd %:
>
>
>
> % Non-Parse CPU:
>
> 100.00
>
>
>
> *Time Model Statistics*
>
> - *Total time in database user-calls (DB Time): 3991.5s*
> - *Statistics including the word "background" measure background
> process time, and so do not contribute to the DB time statistic*
> - *Ordered by % or DB time desc, Statistic name*
>
> *Statistic Name*
>
> *Time (s)*
>
> *% of DB Time*
>
> sql execute elapsed time
>
> 3,988.16
>
> 99.92
>
> DB CPU
>
> 3,976.65
>
> 99.63
>
> parse time elapsed
>
> 3,970.81
>
> 99.48
>
> hard parse elapsed time
>
> 3,970.78
>
> 99.48
>
> PL/SQL execution elapsed time
>
> 0.09
>
> 0.00
>
> connection management call elapsed time
>
> 0.01
>
> 0.00
>
> sequence load elapsed time
>
> 0.00
>
> 0.00
>
> repeated bind elapsed time
>
> 0.00
>
> 0.00
>
> DB time
>
> 3,991.55
>
>
>
> background elapsed time
>
> 31.57
>
>
>
> background cpu time
>
> 8.21
>
>
>
>
>
>
>
> *Instance Activity Stats*
>
> - *Ordered by statistic name*
>
> *Statistic*
>
> *Total*
>
> *per Second*
>
> *per Trans*
>
> parse count (describe)
>
> 0
>
> 0.00
>
> 0.00
>
> parse count (failures)
>
> 0
>
> 0.00
>
> 0.00
>
> parse count (hard)
>
> 1
>
> 0.00
>
> 0.01
>
> parse count (total)
>
> 912
>
> 1.02
>
> 8.69
>
> parse time cpu
>
> 3
>
> 0.00
>
> 0.03
>
> parse time elapsed
>
> 0
>
> 0.00
>
> 0.00
>
>
>

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