Intresting Statistics -- DB FILE SEQUENTIAL READ waits

From: <sat0789_at_fastmail.fm>
Date: Fri, 27 Jun 2003 09:30:28 -0700
Message-ID: <F001.005BB2F4.20030627090951@fatcity.com>

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). Received on Fri Jun 27 2003 - 11:30:28 CDT