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

Home -> Community -> Mailing Lists -> Oracle-L -> same udpate statement takes same cpu time but significant different "sequential read wait time"

same udpate statement takes same cpu time but significant different "sequential read wait time"

From: qihua wu <staywithpin_at_gmail.com>
Date: Sat, 29 Sep 2007 15:15:53 -0700
Message-ID: <2689c1070709291515g754dc213yb353acb299265aef@mail.gmail.com>


We have one test database another production database, the data volumn nearly the small. But a single update statement takes about 2,000 seconds on test database, but 7,000 seconds on the productoin database. For the report of OEM, both test database and production database take about 1,500 seconds on CPU. But the test database only takes 500 seconds on "sequential read" and production database take 4,500 seconds on "sequential read".

So I ran the following sql on the both database, and found that single sequential read wait time on production is much longer than test database. And I am wondering whether the IO subsystem in production is not as good as test. What's your opinion on the big difference on "sequential read'?

BTW,The unix team and SAN team are not easy to appoach, so I must gather evidence to please them look into the IO subsystem. The sql result is only from database level and they won't look at any evidence from database level. Is there any standard unix tool that can test the "sequential read' speed?

select

   sum(a.time_waited_micro)/sum(a.total_waits)/1000000 c1,    sum(b.time_waited_micro)/sum(b.total_waits)/1000000 c2, from

   dba_hist_system_event a,
   dba_hist_system_event b
where

   a.snap_id = b.snap_id
and

   a.event_name = 'db file scattered read' and

   b.event_name = 'db file sequential read';

--
http://www.freelists.org/webpage/oracle-l
Received on Sat Sep 29 2007 - 17:15:53 CDT

Original text of this message

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