RE: Parsing Problem - I think
- From: Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
- To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 25 Mar 2014 13:24:54 +0000
Your "non-hard" parse time look pretty high given the small number of "soft"
parses you are doing, so the first thing I'd do is look at the number of CPU
cores you actually have. It's possible that you're multi-threading on a much
smaller number of cores than Oracle is reporting as "CPUs" - with the result
that an parse contention (latches, mutexes) is being exaggerated as soon as the
CPU load gets anywhere near the number of cores.
What does the full set of OS Stats" look like ?
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
@jloracle
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on behalf
of Fred Tilly [ftilly@xxxxxxxxxxxxxx]
Sent: 25 March 2014 12:29
To: oracle-l@xxxxxxxxxxxxx
Subject: Parsing Problem - I think
Hi All,
Looking at an AWR report from a customer site where they have had some very
slow performance.
Server is 32 CPU and 26GB running Oracle 11.2.0.3
AWR is over 1 hour and shows:
Parses: 22.6 0.5
Hard parses: 5.1 0.1
Elapsed: 59.37 (mins)
DB Time: 587.12 (mins)
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 99.91 In-memory Sort %: 100.00
Library Hit %: 94.89 Soft Parse %: 77.60
Execute to Parse %: 92.71 Latch Hit %: 99.95
Parse CPU to Parse Elapsd %: 57.49 % Non-Parse CPU: 54.03
Top 5 Timed Foreground Events
Event Waits Time(s) Avg wait (ms) % DB time Wait Class
DB CPU 22,086 62.70
cursor: pin S wait on X 12 705 58760 2.00 Concurrency
log file sync 142,183 469 3 1.33 Commit
db file sequential read 48,259 155 3 0.44 User I/O
buffer busy waits 2,144 129 60 0.37 Concurrency
Statistic Name Time (s) % of DB Time
DB CPU 22,086.13 62.70
parse time elapsed 19,559.05 55.52
hard parse elapsed time 18,490.89 52.49
sql execute elapsed time 15,126.32 42.94
From the information in the AWR report I do not appear to be parsing a lot of
statements per second, but my hard parse time seems really high.
What else do I need to look at to get to the bottom of this issue.
Thanks
Fred
Other related posts:
