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> 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
