RE: Hard parse elapsed time in AWR

Hi,
The DB version is 11.2.0.3.

Thanks,
Amir
From: Ahmed Aangour [mailto:ahmed.aangour_at_gmail.com] Sent: Thursday, August 18, 2016 11:31 AM To: Hameed, Amir <Amir.Hameed_at_xerox.com> Cc: oracle-l <oracle-l_at_freelists.org> Subject: 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<mailto: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