Re: Long wait times for db file scattered read

John,
Yes it is the difference in reading the blocks from the table DMLEGALRIGHTS that has caused the timing differences between Run 1 and Run2.
| 45140 .........TABLE ACCESS BY INDEX ROWID OBJ# DMLEGALRIGHTS
(cr=1674600 pr=514665 pw=0 time=739.31)

| 45140 .........TABLE ACCESS BY INDEX ROWID OBJ# DMLEGALRIGHTS
(cr=1674600 pr=617178 pw=0 time=1559.46)

In RUN1 you had 517427 sequential reads in 635.85 secs at the rate of 1.2 milli second per read (which is very good). In RUN2 you had 619315 sequential reads in 1444.65 secs at the rate of 2.3 milli second per read (which is also good). Whats your server and what disks do you use ?

It would have been better if you shown the SQL as well. Whats your Oracle version and do you have stats on all tables ? and whats your optimizer mode ?
These 2 steps are the most time consuming steps in the plan TABLE ACCESS BY INDEX ROWID OBJ# DMLEGALRIGHTS  INDEX RANGE SCAN OBJ# AK7DMLEGALRIGHTS Fo example in Run1
45140 .........TABLE ACCESS BY INDEX ROWID OBJ# DMLEGALRIGHTS
(cr=1674600 pr=514665 pw=0 time=739.31)

8080060 ..........INDEX RANGE SCAN OBJ# AK7DMLEGALRIGHTS (cr=30342 pr=30205 pw=0 time=33.35)

Did you hint the use of the index AK7DMLEGALRIGHTS ? I feel that a full table scan of DMLEGALRIGHTS would have been better insteand of scanning 1674600 blocks of that table using index scans. Isnt there any better filter that you cant apply on that table ?

You have a non-mergeable view in the Plan.

Better show the SQL.

regards
Srivenu Received on Thu Mar 18 2004 - 03:25:45 CST