-
db file scattered read
Scenario: Oracle 9.2.0.4 on Solaris 10 connected by fibre cards to
3PAR SAN - 256k stripe (max is 512k)
Oracle Params :Blocksize is 8k
MBRC = 128
buffer cache = 1GB
filesystem where the table resides is RAID10 (12 disks inside)
running a FTS against a table (22 million rows, 6 million blocks) - table is 47GB
select count(*)
from x;
(takes 25 mins)
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 601.35 1053.03 5459054 5586666 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 601.35 1053.04 5459054 5586666 0 1
Misses in library cache during parse: 0
Optimizer goal: RULE
Parsing user id: 61
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=5586666 r=5459054 w=0 time=886688445 us)
22587395 TABLE ACCESS FULL OBJ#(38848) (cr=5586666 r=5459054 w=0 time=1041281069 us)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
db file sequential read 22 0.00 0.00
db file scattered read 42706 3.13 827.96
SQL*Net message from client 2 696.65 696.65
iostat during:
extended device statistics
r/s w/s kr/s kw/s wait actv wsvc_t asvc_t %w %b device
46.6 0.0 43589.9 0.0 0.0 1.1 0.0 24.1 0 81 c3t50002AC0005D04ABd0
I'm thinking this is an issue with the SAN subsystem....
Any thoughts? I'm stumped.
-
Why do you think you have an issue in your SAN?
Scattered reads are multiblock reads; exactly what you expect to see when doing a Full Table Scan like your query does.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
do this
SQL>set autot traceonly
SQL> select * form x
The consistent gets should be around (number of blocks in dba_table against X)+(total number of rows/15).
Since by default araysize is 15.
If the consitent gets is too high as comapred to previous math, then table had lot of deletes.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|