Re: Re: Parsing Problem - I think
Date: Tue, 25 Mar 2014 21:12:40 +0800
Message-ID: <201403252112377563608_at_gmail.com>
Hi, Kenny
Base on my knowledgement, if this cause by DDL statement, 'library cache lock' might show on top events. So I prefer to say it might cause by hard parse. If this issue ocurred in the past, v$active_session_history might be another choice to investigate the 'cursor: pin S wait on X' event. Correct me if I am wrong.
Thanks very much.
Best Regards,
Milo
From: Kenny Payton
Date: 2014-03-25 20:57
To: xueyuan.luo
CC: ftilly; Oracle Discussion List
Subject: Re: Parsing Problem - I think
The ‘cursor: pin S wait on X’ appears to be a contributing factor but from these stats it appears to only be a small portion of time spent parsing ( 705s of 19,559s ). BTW, this wait event could be caused by almost any DDL, including the stats gathering.
If you have sufficiently diverse sessions you could narrow down the sessions with poor parse time by looking at v$sess_time_model where stat_name = ‘parse time elapsed’. I don’t know of anywhere the parse time is recorded at a statement level but that would be nice.
Once narrowed down pick a session and trace it, tkprof will show you parse times per statement.
Kenny
On Mar 25, 2014, at 8:40 AM, Milo <xueyuan.luo_at_gmail.com> wrote:
Hi, Fred
In my opinion, you should really check the hard parse issue in your db.
The soft parse percentage and hard pase time take up most of parse time.
And the most possible reason for wait event 'cursor: pin S wait on X' might really cause by this.
Best Regards,
Milo
From: Fred Tilly
Date: 2014-03-25 20: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.60.5
Hard parses:5.10.1
Elapsed: 59.37 (mins)
DB Time: 587.12 (mins)
Buffer Nowait %:100.00Redo NoWait %:100.00
Buffer Hit %:99.91In-memory Sort %:100.00
Library Hit %:94.89Soft Parse %:77.60
Execute to Parse %:92.71Latch Hit %:99.95
Parse CPU to Parse Elapsd %:57.49% Non-Parse CPU:54.03
Top 5 Timed Foreground Events
EventWaitsTime(s)Avg wait (ms)% DB timeWait Class
DB CPU 22,086 62.70
cursor: pin S wait on X12705587602.00Concurrency
log file sync142,18346931.33Commit
db file sequential read48,25915530.44User I/O
buffer busy waits2,144129600.37Concurrency
Statistic NameTime (s)% of DB Time
DB CPU22,086.1362.70
parse time elapsed19,559.0555.52
hard parse elapsed time18,490.8952.49
sql execute elapsed time15,126.3242.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-lReceived on Tue Mar 25 2014 - 14:12:40 CET