-none- 2007-10-03 - By Vlad Sadilovskiy
Back
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@(protected)> 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@(protected)> 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@(protected) > 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@(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';
> > > > >
> > > >
> > >
> > >
> >
>
<div>Not exact, but similar data on HPUX could be retrieved with:</div>
<div> </div>
<div>sar -d 1 10</div>
<div> </div>
<div>You'd need to map the names of devices onto datafiles that your query
is reading.</div>
<div> </div>
<div> <br>Vlad Sadilovskiy<br>Oracle Database Tools<br><a href="http://www
.fourthelephant.com/">http://www.fourthelephant.com</a><br> </div>
<div><span class="gmail_quote">On 10/2/07, <b class="gmail_sendername">qihua wu
</b> <<a href="mailto:staywithpin@(protected)">staywithpin@(protected)</a>>
wrote:</span>
<blockquote class="gmail_quote" style="PADDING-LEFT: 1ex; MARGIN: 0px 0px 0px 0
.8ex; BORDER-LEFT: #ccc 1px solid">The database is running on hpux. And the sql
is the exactly the same on test and production.<br><br>Thanks,
<br><span class="sg">Qihua</span>
<div><span class="e" id="q_1156307a3991c951_2"><br><br>
<div><span class="gmail_quote">On 10/2/07, <b class="gmail_sendername">Vlad
Sadilovskiy</b> <<a onclick="return top.js.OpenExtLink(window,event,this)"
href="mailto:vlovsky@(protected)" target="_blank"> vlovsky@(protected)
</a>> wrote:</span>
<blockquote class="gmail_quote" style="PADDING-LEFT: 1ex; MARGIN: 0pt 0pt 0pt 0
.8ex; BORDER-LEFT: rgb(204,204,204) 1px solid">
<div>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? </div><span>
<div> </div>
<div>Vlad Sadilovskiy<br>Oracle Database Tools<br><a onclick="return top.js
.OpenExtLink(window,event,this)" href="http://www.fourthelephant.com/" target="
_blank">http://www.fourthelephant.com</a><br><br> </div></span>
<div><span>
<div><span class="gmail_quote">On 9/30/07, <b class="gmail_sendername">qihua wu
</b> <<a onclick="return top.js.OpenExtLink(window,event,this)" href="mailto
:staywithpin@(protected)" target="_blank">staywithpin@(protected)</a>
> wrote:</span>
<blockquote class="gmail_quote" style="PADDING-LEFT: 1ex; MARGIN: 0px 0px 0px 0
.8ex; BORDER-LEFT: rgb(204,204,204) 1px solid">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 onclick="return top.js.OpenExtLink(window,event,this)" href=
"mailto:tony.adolph.dba@(protected)" target="_blank">tony.adolph.dba@(protected)
</a>> wrote:</span>
<blockquote class="gmail_quote" style="PADDING-LEFT: 1ex; MARGIN: 0pt 0pt 0pt 0
.8ex; BORDER-LEFT: rgb(204,204,204) 1px solid">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
<div><span><br><br><br>On 9/30/07, qihua wu <<a onclick="return top.js
.OpenExtLink(window,event,this)" href="mailto:staywithpin@(protected)" target="
_blank">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></span></div></blockquote></div><br><
/blockquote></div><br></span></div></blockquote></div><br></span></div><
/blockquote></div><br>
|
|
