db file scattered read

[rgaskell]

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.

[PAVB]

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.

[malay_biswal]

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.