RE: direct path read/write temp waits
- From: genegurevich@xxxxxxxxxxxxxxxxxxxxx
- To: Mark.Bobak@xxxxxxxxxxxxxxx, oracle-l@xxxxxxxxxxxxx
- Date: Thu, 26 Apr 2007 12:46:30 -0500
Mark,
I am seeing this:
505 sorts (disk)
0
505 sorts (rows)
0
505 sorts (memory)
0
515 sorts (disk)
0
515 sorts (rows)
0
515 sorts (memory)
0
519 sorts (rows)
0
519 sorts (disk)
0
519 sorts (memory)
0
545 sorts (disk)
0
545 sorts (memory)
0
545 sorts (rows)
0
576 sorts (disk)
0
576 sorts (rows)
0
576 sorts (memory)
0
580 sorts (rows)
0
580 sorts (memory)
0
580 sorts (disk)
0
642 sorts (memory)
1
642 sorts (disk)
1
642 sorts (rows)
1269
655 sorts (rows)
0
655 sorts (disk)
0
655 sorts (memory)
0
668 sorts (rows)
80243
668 sorts (disk)
3
668 sorts (memory)
37
679 sorts (memory)
1
679 sorts (disk)
1
679 sorts (rows)
1270
745 sorts (rows)
0
745 sorts (memory)
0
745 sorts (disk)
0
The two sids with the highest number of sorts though (688 and 642) are some
other sessions.
thank you
Gene Gurevich
Oracle MySQL Operations - OMO
224-405-4079
"Bobak, Mark"
<Mark.Bobak@xxxxx
oquest.com> To
Sent by: <genegurevich@xxxxxxxxxxxxxxxxxxxxx
oracle-l-bounce@f >, <oracle-l@xxxxxxxxxxxxx>
reelists.org cc
Subject
04/25/2007 01:13 RE: direct path read/write temp
PM waits
Please respond to
Mark.Bobak@xxxxxx
quest.com
Yeah, the 301 and 302 datafile ids means that you have db_files set to
300. Tempfiles start numbering w/ db_files+1.
As to the sort analysis, what do you get if you do:
select vsn.name, vss.value
from v$sesstat vss,
v$statname vsn
where vsn.name like '%sort%'
and vss.statistic# = vsn.statistic#
and vss.sid in(select vs.sid
from v$session vs,
v$sort_usage vsu
where vs.saddr=vsu.session_addr);
-Mark
--
Mark J. Bobak
Senior Oracle Architect
ProQuest/CSA
"There are 10 types of people in the world: Those who understand
binary, and those who don't."
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of
genegurevich@xxxxxxxxxxxxxxxxxxxxx
Sent: Wednesday, April 25, 2007 1:08 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: direct path read/write temp waits
Hi all
I'm trying to find out a way to deal with direct path read/write temp
waits. I am loading a table as a select from another table
insert /*+ append */ into table1 (select col1, col2, sum ... from table2
group by ...);
While this is running I see a number of the direct path read/write temp
waits in the v$session_waits table I have found a document dealing with
this waits on metalink
(http://download-east.oracle.com/docs/cd/B14117_01/server.101/b10752/ins
tance_tune.htm)
The file_id that I am seeing are 301 and 302. I only have 55 datafiles
in this database so this is definitely a TEMP tablespace. If I read that
document correctly, this means that the sorts are too large to fit in
memory and some data are written to disk. I have however checked the
sessstat table for the sessions listed in the tempseg_usage view and
found no data for sorts. I am not sure how to reconcile these two pieces
of data.
I am also doing a full scan of one partition in the table2. Both tables
are parallelized. My oracle version is 10.2.0.3
thanks for any suggestion
Gene Gurevich
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
