-none- 2007-09-30 - By qihua wu
Back
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@(protected)> 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@(protected)> 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';
> >
>
Hi, <br><br>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.<br>
<br>Thanks,<br>Qihua<br><br><div><span class="gmail_quote">On 9/30/07, <b class
="gmail_sendername">Tony Adolph</b> <<a href="mailto:tony.adolph.dba@(protected)
.com">tony.adolph.dba@(protected)</a>> wrote:</span><blockquote class="gmail
_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0
.8ex; padding-left: 1ex;">
Without too much thought, I'd say you're doing index lookups (db file
<br>sequential read') on one db and table scans (db file scattered read)<br
>on the other... are the stats up-to-date on both?<br><br>Is there a particular
update that's causing you a problem,...have you
<br>checked and compared the plans?<br><br>I think I'd start there before
going to the I/O system.<br><br>HTH<br>Tony<br><br><br>On 9/30/07, qihua wu <
;<a href="mailto:staywithpin@(protected)">staywithpin@(protected)</a>
> wrote:<br>> We have one test database another production database, the
data volumn<br>> nearly the small. But a single update statement takes about
2,000 seconds on<br>> test database, but 7,000 seconds on the
productoin database. For the report
<br>> of OEM, both test database and production database take about 1,500
seconds<br>> on CPU. But the test database only takes 500 seconds on "
;sequential read"<br>> and production database take 4,500 seconds on
"sequential read".
<br>><br>> So I ran the following sql on the both database, and found
that single<br>> sequential read wait time on production is much longer than
test database.<br>> And I am wondering whether the IO subsystem in
production is not as good as
<br>> test. What's your opinion on the big difference on
"sequential read'?<br>><br>> BTW,The unix team and SAN team are
not easy to appoach, so I must gather<br>> evidence to please them look into
the IO subsystem. The sql result is only
<br>> from database level and they won't look at any evidence from
database level.<br>> Is there any standard unix tool that can test the "
;sequential read' speed?<br>><br>> select<br>>
sum(a.time_waited_micro
)/sum(a.total_waits)/1000000 c1,<br>> sum(b.time
_waited_micro)/sum(b.total_waits)/1000000 c2,<br>> from<br>>
dba_hist_system_event a,<br>> dba_hist
_system_event b<br>> where<br>> a.snap_id =
b.snap_id<br>> and<br>> a.event_name = 'db
file scattered read'<br>> and<br>> b.event
_name = 'db file sequential read';<br>><br></blockquote></div><br>
|
|
