Re: Fw: sql with SQL_OPCODE=0

From: Karl Arao <karlarao_at_gmail.com>
Date: Wed, 1 Dec 2010 22:55:29 +0800
Message-ID: <AANLkTim3XUWM3HyVNegxDoDNimJVHLkv3Z3vS6T+sO4q_at_mail.gmail.com>

Hi Denis,

Interesting.. from your last message you mentioned that you were

• adding datafile
• you have many concurrent sessions doing lob access for insert/update
• and then experienced system slowdown with lob operation

And then I saw your first post regarding the SQL_ID 6s8fdgnw2u49h having high "CPU + Wait for CPU" and from my experience once you started encountering this event you are likely to have high run queue that could be attributed by slow IO (high wait IO%) or simply because of CPU bottleneck.

Looking on your blog post, you have drilled down on the IO stats and the most relevant would be getting the latency numbers.. so the formula would be  latency(ms)=(readtim/phyreads)*10

and here's what I've derived from your data

Time latency (ms)

13:40:02 37.14
13:50:02 62.00
14:00:04 2.00
14:10:01 28.89
14:20:03 1.43
14:30:03 27.14
14:40:02 67.14
14:50:02 30.00
15:00:04 63.33
15:10:03 1428.57
15:20:10 1676.67
15:30:30 7455.00 <-- probably the peak with 7sec latency
15:40:35 755.00
15:50:17 3080.00
16:00:46 446.67
16:10:12 8.57
16:20:05 13.75 <-- ASH
16:30:05 45.26
16:40:03 8.18
16:50:01 8.75
17:00:04 16.25

You sent the ASH for 16:20-16:30... the interesting periods would be 15:10:03 to 16:00:46.
I suggest you also correlate this with your OS performance data probably SAR if you're in linux (use kSar to graph it) since you'd like to know what happened on those past periods..

It will also help if you can share to us how many CPUs, what disk subsystem is this running on, and some general info about your environment.

BTW, you can get the latency numbers from these views

dba_hist_filestatxs, dba_hist_tempstatxs <-- where the AWR pulls data, yours is already 10mins interval

v$filemetric_history                                            <-- 10 mins
intervals (fixed)
v$filestat and v$tempstat        <-- you can do 1 sec sampling from here if

you are worried about the averages normalizing the latency numbers

I've uploaded the scripts at
http://karlarao.wordpress.com/scripts-resources/ , all with the same column output..
awr_io_ts.sql, awr_io_file.sql
filemetric.sql
filestat.sql

-- 
Karl Arao
karlarao.wordpress.com
karlarao.tiddlyspot.com

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

Received on Wed Dec 01 2010 - 08:55:29 CST