In-Reply-To: <AA29A27627F842409E1D18FB19CDCF270C0BB8E3@AABO-EXCHANGE02.bos.il.pqe>
Subject: RE: direct path read/write temp waits
To: Mark.Bobak@il.proquest.com, oracle-l@freelists.org
Message-ID: <OF641EECB8.DBECA743-ON862572C9.00616E15-862572C9.0061A324@discoverfinancial.com>
From: genegurevich@discoverfinancial.com
Date: Thu, 26 Apr 2007 12:46:30 -0500
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Sender: oracle-l-bounce@freelists.org
Errors-to: oracle-l-bounce@freelists.org
Precedence: normal
Reply-to: genegurevich@discoverfinancial.com

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@il.pr                                             
             oquest.com>                                                To 
             Sent by:                  <genegurevich@discoverfinancial.com 
             oracle-l-bounce@f         >, <oracle-l@freelists.org>         
             reelists.org                                               cc 
                                                                           
                                                                   Subject 
             04/25/2007 01:13          RE: direct path read/write temp     
             PM                        waits                               
                                                                           
                                                                           
             Please respond to                                             
             Mark.Bobak@il.pro                                             
                 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@freelists.org
[mailto:oracle-l-bounce@freelists.org] On Behalf Of
genegurevich@discoverfinancial.com
Sent: Wednesday, April 25, 2007 1:08 PM
To: oracle-l@freelists.org
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


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


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


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