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

Not exact, but similar data on HPUX could be retrieved with:

sar -d 1 10

You'd need to map the names of devices onto datafiles that your query is reading.

Vlad Sadilovskiy
Oracle Database Tools
http://www.fourthelephant.com

On 10/2/07, qihua wu <staywithpin_at_gmail.com> wrote:
>
> 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_at_gmail.com> 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_at_gmail.com > 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_at_gmail.com > 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_at_gmail.com > 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';
> > > > >
> > > >
> > >
> > >
> >
>

--
http://www.freelists.org/webpage/oracle-l