Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Long wait times for db file sequential read

Re: Long wait times for db file sequential read

From: John Oracle DBA <jasnoke_at_msn.com>
Date: 18 Mar 2004 16:19:33 -0800
Message-ID: <aea439d2.0403181619.39c6c874@posting.google.com>


srivenu_at_hotmail.com (srivenu) wrote in message news:<1a68177.0403180125.1a48a28f_at_posting.google.com>...
> 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 ?

Thank you for your response. I made a mistake in the header. As you've recognized, the waits are for sequential, not scattered reads. So it's your opinion that these waits are not excessive. Please expand on this. Also, why do you think the waits are so much longer given the relatively minor increase in physical reads for run 2?

This is on a Sun v880, 4cpu's 8gb RAM with Storage Works SAN. I confirmed again with the Sysadmin he's not seeing significant waits or queueing at the OS level.

>
> 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 ?

Here's the query. I'm running 9.2 with optimizer=choose. The stats are up to date as well.

SELECT
WORLD.DMPRODUCT.PRODUCTID,
MAX(CASE
when EUROPE.DMLEGALRIGHTS.RIGHTSENDDATE is null AND EUROPE.DMLEGALRIGHTS.LIMITEDRIGHTSENDDATE is null THEN 'N' when EUROPE.DMLEGALRIGHTS.RIGHTSENDDATE is null AND EUROPE.DMLEGALRIGHTS.LIMITEDRIGHTSENDDATE is not null THEN 'Y' when EUROPE.DMLEGALRIGHTS.RIGHTSENDDATE is not null AND EUROPE.DMLEGALRIGHTS.REVRIGHTSDATE is not null THEN 'N' when EUROPE.DMLEGALRIGHTS.RIGHTSENDDATE is not null AND EUROPE.DMLEGALRIGHTS.REVRIGHTSDATE is null AND EUROPE.DMLEGALRIGHTS.TERMSTRT > sysdate THEN 'N' when EUROPE.DMLEGALRIGHTS.RIGHTSENDDATE is not null AND EUROPE.DMLEGALRIGHTS.REVRIGHTSDATE is null AND EUROPE.DMLEGALRIGHTS.RIGHTSENDDATE < sysdate THEN 'N' when EUROPE.DMLEGALRIGHTS.RIGHTSENDDATE is not null AND EUROPE.DMLEGALRIGHTS.REVRIGHTSDATE is null AND EUROPE.DMLEGALRIGHTS.TERMSTRT <= sysdate AND EUROPE.DMLEGALRIGHTS.RIGHTSENDDATE >= sysdate THEN 'Y' END)
FROM
WORLD.DMPRODUCT SeriesParentProduct,
EUROPE.DMPRODUCTFILTER,
LANGUAGE,
EUROPE.DM_REGIONTERRITORY Deal_WORLD_DMREGIONTERRITORY, Media AvailsMedia1,
Territory AvailsTerritory1,
WORLD.DMPRODUCT,
ProductCategory SeriesParentCategory,
EUROPE.DMLEGALRIGHTS
WHERE
( EUROPE.DMLEGALRIGHTS.MEDIAID=AvailsMedia1.MEDIAID )

AND  ( WORLD.DMPRODUCT.PRODUCTID=EUROPE.DMLEGALRIGHTS.PRODUCTID  )
AND  ( EUROPE.DMPRODUCTFILTER.PRODUCTID=WORLD.DMPRODUCT.PRODUCTID  )
AND  ( AvailsTerritory1.TERRITORYID=Deal_WORLD_DMREGIONTERRITORY.TERRITORYID
 )
AND ( LANGUAGE.LANGUAGEID=Deal_WORLD_DMREGIONTERRITORY.LANGUAGEID ) AND ( EUROPE.DMLEGALRIGHTS.LANGUAGEID=Deal_WORLD_DMREGIONTERRITORY.LANGUAGEID and EUROPE.DMLEGALRIGHTS.TERRITORYID=Deal_WORLD_DMREGIONTERRITORY.TERRITORYID  )
AND ( WORLD.DMPRODUCT.SERIESID=SeriesParentProduct.PRODUCTID(+) ) AND ( trim(SeriesParentProduct.PRODUCTLEVEL) IN ('Series','Series Two Level') )
AND ( SeriesParentProduct.PRODUCTID=SeriesParentCategory.PRODUCTID(+)  )
AND ( SeriesParentCategory.CATEGORYSYSTEMID(+)=78 ) AND (
trim(WORLD.DMPRODUCT.PRODUCTLEVEL) IN ('Season', 'Series Two Level')
AND  ( EUROPE.DMPRODUCTFILTER.FILTERFLAGID =255  )
AND  ( ( WORLD.DMPRODUCT.SERIESTITLE ) IN ('*')  or  '*'  in ('*')  )
AND  ( ( SeriesParentProduct.RUNTIMEMINS ) IN ('*') or '*' in  ('*')
)
AND ( ( trim(SeriesParentCategory.CATEGORYCODE) ) IN ('*') or '*' in ('*') )
AND ( ( trim(AvailsTerritory1.TERRITORYNAME) || decode(', ' || trim(LANGUAGE.LANGUAGENAME),',
All Languages',null,', ' || trim(LANGUAGE.LANGUAGENAME)) ) IN ('United Kingdom') )
AND  ( WORLD.DMPRODUCT.PRODUCTSTATUS in ('Active              ',
'Pending             ')  )

AND ( AvailsMedia1.MEDIAID in (22,26,54,517) ) AND WORLD.DMPRODUCT.CTITEPISODEFLAG = 'Y' )
GROUP BY
WORLD.DMPRODUCT.PRODUCTID
> These 2 steps are the most time consuming steps in the plan
> TABLE ACCESS BY INDEX ROWID OBJ# DMLEGALRIGHTS
> INDEX RANGE SCAN OBJ# AK7DMLEGALRIGHTS
Yes. Notice the vast majority of wait time is to retrieve the table blocks after the index has been scanned.

>
> 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 ?

No hints. The query is generated by Business Objects which means the analysts don't want it altered. I'll only pain you with an explanation for this request if you would like to know. Otherwise my choices would be narrowed to creating a more selective index and "hoping" the optimizer uses it, or altering DMLEGALRIGHTS to parallel with a default degree (I'm using parallel automatic tuning). But even without PQ you maybe right, a FTS may be faster due to multiblock reads (8k block, multiblock read count = 8, RAID stripe size = 64k). In any event, it does seem that scanning 8 million rows via an index to retrieve 45 thousand in the table is terribly inefficient. Why would the optimizer choose such a route?

>
> You have a non-mergeable view in the Plan.
>

Please expand on why this is important. It appears to me very little resources are being consumed when accessing the view. Thank you again for your interest.

John

> Better show the SQL.
>
> regards
> Srivenu
Received on Thu Mar 18 2004 - 18:19:33 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US