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

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Intresting Statistics -- DB FILE SEQUENTIAL READ waits

Re: Intresting Statistics -- DB FILE SEQUENTIAL READ waits

From: Binley Lim <Binley.Lim_at_xtra.co.nz>
Date: Fri, 27 Jun 2003 15:33:32 -0700
Message-ID: <F001.005BB881.20030627151943@fatcity.com>


The ordering of the temp (driving) table to line up with the fact table index-key order is a very powerful technique indeed.

If the driving table is random wrt to the index ordering, it would have to look up an index block for each row, pin the block, do the update for just a key or two, and repeat the process for the next row. If the rows are ordered, then it will only have to pin the block only once, and update all the rows in one pass and never visit that block again. Whereas jumping all round memory in random order is very expensive in terms of the code-path that Oracle has to travel. Contrary to what you might have read about HIT-RATIOS, the ratio of "buffer is pinned/not pinned" count gives a good indication you might have a problem in the respect, especially in DSS environments where such updates are not uncommon.

The temp table ordering alone may not help all that much. Its because you also ordered the fact table according to index key-order! Same concept here - access the table block once (or fewer times). Unfortunately, the fact table ordering will deteriorate over time with DMLs. Now, if only a future Oracle release can do this re-ordering incrementally and quietly in the background by SMON perhaps?...

> -- Sorry the earlier post was incomplete.
>
> We were running a serial update on a fact table (45 mill rows) using the
> old tech of
> declare
> cursor .......table temp
> begin
> for c1rec in c1 loop
> update fact
> where period_key = c1rec.period_key and loan_key = c1rec.loan_key
> ----commit every 10,000 rows
> end loop;
> end;
>
> fACT table partitioned on period key (per month) and there was a unique
> local index on period_key and loan_key
> the update was going at a rate of 10,000 rows every 1.5 min. very
> slow...
> Myself and Madhavan(another avid reader of this list) came up with
> something....
> When we looked at the cluster factor the index..it was close to the
> number of rows..so we decided to rearrange the fact table with period_key
> and mortgage_loan_key. The cluster factor of the index now came close to
> the number of blocks per partition but On running the above query, the
> waits on sequential file reads were still very significant.
> Then we did one last thing were we arranged the temp table(table in the
> cursor) also with period key and mortgage loan key. and Voila the query
> was running at 10,000 rows every 4 sec.
> unbelivable..db_file_sequential_read waits very minimal and disks reads
> also very minial..query ran from 69 hours to 5 hours.
>
> Thought i shall share with you folks...
>
> Sathish.
>
>
>
> --
> http://www.fastmail.fm - Access all of your messages and folders
> wherever you are
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author:
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Binley Lim
  INET: [EMAIL PROTECTED]

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Received on Fri Jun 27 2003 - 17:33:32 CDT

Original text of this message

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