SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Prod
PL/SQL Release 10.1.0.5.0 - Production
CORE 10.1.0.5.0 Production
TNS for Linux: Version 10.1.0.5.0 - Production
NLSRTL Version 10.1.0.5.0 - Production
h1. PROBLEM DESCRIPTION
We have a simple and fast (less than 1 second) query that sometimes takes more than 30 seconds.
We have created a simple script to collect:
- trace
- iostat statistics
We've noticed that
- the query has the
same explain plan in both cases
- the same wait event: db file sequential read
- the iostat %util is near to 100% when the query is slow.
- the device with 100% contains the table I_JOURNAL: the only table in the query explain plan.
h2. DETAILS
From the trace:
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 1.82 38.14 24625 28237 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 1.82 38.14 24625 28237 0 1
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 50
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=28237 pr=24625 pw=0 time=38140509 us)
29282 HASH JOIN RIGHT OUTER (cr=28237 pr=24625 pw=0 time=38090881 us)
6947 INDEX RANGE SCAN PBK_GET_NORM_CIN_UK (cr=34 pr=30 pw=0 time=35924 us)(object id 45195)
29282 HASH JOIN (cr=28203 pr=24595 pw=0 time=37883321 us)
15 TABLE ACCESS FULL LIU_TYPES (cr=7 pr=0 pw=0 time=121 us)
29282 TABLE ACCESS BY INDEX ROWID I_JOURNAL (cr=28196 pr=24595 pw=0 time=37706089 us)
29282 INDEX RANGE SCAN IJL_LIN_FK_IX (cr=203 pr=201 pw=0 time=541294 us)(object id 50735)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 7 0.00 0.00
SQL*Net message from client 7 0.00 0.00
db file sequential read 24625 0.50 36.87
From the iostat:
Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s rkB/s wkB/s avgrq-sz avgqu-sz await svctm %util
..
dm-11 0.00 0.00 181.44 37.11 2573.20 296.91 1286.60 148.45 13.13 1.02 4.97 4.49 98.04
dm-11 0.00 0.00 169.39 28.57 2318.37 228.57 1159.18 114.29 12.87 0.93 4.57 4.72 93.37
dm-11 0.00 0.00 176.77 16.16 2424.24 129.29 1212.12 64.65 13.24 0.99 5.24 4.95 95.56
dm-11 0.00 0.00 163.27 32.65 2269.39 261.22 1134.69 130.61 12.92 0.98 5.17 4.95 96.94
..
dm-11 0.00 0.00 136.00 25.00 1864.00 200.00 932.00 100.00 12.82 1.21 7.52 5.83 93.90
dm-11 0.00 0.00 220.62 0.00 3101.03 0.00 1550.52 0.00 14.06 0.98 4.37 4.34 95.77
dm-11 0.00 0.00 290.82 0.00 4400.00 0.00 2200.00 0.00 15.13 1.01 3.45 3.41 99.08
dm-11 0.00 0.00 391.84 0.00 6171.43 0.00 3085.71 0.00 15.75 0.97 2.53 2.48 97.35
h1. QUESTION
The problem is related to the wrong storage layout.
Our server has three physical device, and we have to re-reorganize the data to have a good balance.
- Do you have any suggestions to do that?
- Should I move the I_JOURNAL into a new tablespace into a dedicated physical device?
- what else?