Hard parse elapsed time in AWR

From: Hameed, Amir <Amir.Hameed_at_xerox.com>
Date: Thu, 18 Aug 2016 14:55:34 +0000
Message-ID: <AF02C941134B1A4AB5F61A726D08DCED20154F8C_at_USA7109MB012.na.xerox.net>



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
Received on Thu Aug 18 2016 - 16:55:34 CEST

Original text of this message