RE: Parsing Problem - I think

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Tue, 25 Mar 2014 13:24:54 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D901DE5B99_at_exmbx05.thus.corp>

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
_at_jloracle

---

From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on behalf of Fred Tilly [ftilly_at_btinternet.com] Sent: 25 March 2014 12:29
To: oracle-l_at_freelists.org
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

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Mar 25 2014 - 14:24:54 CET