Hi,
The DB version is 11.2.0.3.
Thanks,
Amir
From: Ahmed Aangour [mailto:ahmed.aangour@xxxxxxxxx]
Sent: Thursday, August 18, 2016 11:31 AM
To: Hameed, Amir <Amir.Hameed@xxxxxxxxx>
Cc: oracle-l <oracle-l@xxxxxxxxxxxxx>
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@xxxxxxxxx<mailto:Amir.Hameed@xxxxxxxxx>> 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
