Re: Fw: sql with SQL_OPCODE=0
- From: Karl Arao <karlarao@xxxxxxxxx>
- To: denis.sun@xxxxxxxxx
- Date: Wed, 1 Dec 2010 22:55:29 +0800
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
Other related posts:
