Re: same udpate statement takes same cpu time but significant different "sequential read wait time"
- From: "qihua wu" <staywithpin@xxxxxxxxx>
- To: "Vlad Sadilovskiy" <vlovsky@xxxxxxxxx>
- Date: Tue, 2 Oct 2007 16:23:05 -0700
The database is running on hpux. And the sql is the exactly the same on test
and production.
Thanks,
Qihua
On 10/2/07, Vlad Sadilovskiy <vlovsky@xxxxxxxxx> wrote:
>
> Sun iostat -cnmxPz 1 would show you the most of the information on IO
> subsystem throughput and lattency as well as the current load. What is OS
> you are operating on?
>
> Vlad Sadilovskiy
> Oracle Database Tools
> http://www.fourthelephant.com
>
>
> On 9/30/07, qihua wu <staywithpin@xxxxxxxxx> wrote:
> >
> > Hi,
> >
> > I can see both databases spend the majortiy of the time on 'db file
> > sequential read' from the AWR. Another possiblity is that there are more
> > disk contention on production than on the test database.
> >
> > Thanks,
> > Qihua
> >
> > On 9/30/07, Tony Adolph <tony.adolph.dba@xxxxxxxxx > wrote:
> > >
> > > Without too much thought, I'd say you're doing index lookups (db file
> > > sequential read') on one db and table scans (db file scattered read)
> > > on the other... are the stats up-to-date on both?
> > >
> > > Is there a particular update that's causing you a problem,...have you
> > > checked and compared the plans?
> > >
> > > I think I'd start there before going to the I/O system.
> > >
> > > HTH
> > > Tony
> > >
> > >
> > > On 9/30/07, qihua wu <staywithpin@xxxxxxxxx > wrote:
> > > > 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';
> > > >
> > >
> >
> >
>
Other related posts:
