Re: Parsing Problem - I think
Date: Thu, 27 Mar 2014 09:04:16 -0000
Message-ID: <1B2483CC580E417CB4F3BE7A129D4D46_at_Primary>
So CORES=4 and CPUs = 8, and load at end of period = 8.
I'm always cautious about trusting the capacity of multi-threading CPUs, and with your figures and symptoms I'd spend a little time looking at "SQL ordered by CPU" to see if the latch/mutex/library cache problem was a side effect of CPU starvation due to CPU-intensive SQL.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com/all-postings
Author: Oracle Core (Apress 2011)
http://www.apress.com/9781430239543
- Original Message ----- From: "Fred Tilly" <ftilly_at_btinternet.com> To: <jonathan_at_jlcomp.demon.co.uk> Sent: Tuesday, March 25, 2014 2:02 PM Subject: Re: Parsing Problem - I think
Hi Jonathan,
Here is what the OS Stats say.
Operating System Statistics
* *TIME statistic values are diffed. All others display actual values. End
Value is displayed if different
* ordered by statistic type (CPU Use, Virtual Memory, Hardware Config),
Name
Statistic
Value
End Value
AVG_BUSY_TIME 85,477 AVG_IDLE_TIME 270,473 AVG_SYS_TIME 71,901 AVG_USER_TIME 13,402
BUSY_TIME 2,740,786
IDLE_TIME 8,659,884
SYS_TIME 2,306,362
USER_TIME 434,424
LOAD 1 8
OS_CPU_WAIT_TIME 1,920,500
RSRC_MGR_CPU_WAIT_TIME 0
VM_IN_BYTES 65,536
PHYSICAL_MEMORY_BYTES 27,917,287,424
NUM_CPUS 32 NUM_CPU_CORES 4 NUM_CPU_SOCKETS 1 TCP_RECEIVE_SIZE_DEFAULT 49,152 TCP_RECEIVE_SIZE_MAX 1,048,576 TCP_SEND_SIZE_DEFAULT 49,152 TCP_SEND_SIZE_MAX 1,048,576
Thanks
Fred
From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> To: "oracle-l_at_freelists.org" <oracle-l_at_freelists.org> Sent: Tuesday, 25 March 2014, 13:24
Subject: RE: Parsing Problem - I think
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
No virus found in this message.
Checked by AVG - www.avg.com
Version: 2014.0.4354 / Virus Database: 3722/7247 - Release Date: 03/25/14
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Mar 27 2014 - 10:04:16 CET