Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: direct path read/write temp waits

RE: direct path read/write temp waits

From: Bobak, Mark <Mark.Bobak_at_il.proquest.com>
Date: Wed, 25 Apr 2007 14:13:08 -0400
Message-ID: <AA29A27627F842409E1D18FB19CDCF270C0BB8E3@AABO-EXCHANGE02.bos.il.pqe>


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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of genegurevich_at_discoverfinancial.com
Sent: Wednesday, April 25, 2007 1:08 PM
To: oracle-l_at_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 Received on Wed Apr 25 2007 - 13:13:08 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US