Newbie DBA Problem: Hitting Temp Hard

From: <billmil_at_my-deja.com>
Date: 2000/05/11
Message-ID: <8ff03c$l3s$1@nnrp1.deja.com>#1/1

I'm a newish-dba with a potential performance problem: we're hitting our temporary data files pretty hard--especially compared to the index and data datafiles. (The output of one of our nightly queries is below).

I've gone through once and created indexes for our commonly run queries to reduce the need for on-the-fly sorting.

I've also considered increasing the sort_area_size from the default 64K to 256K (though I'm not sure that will help the problem).

My next step, I thought, would be to perform an sql_trace on a session, execute the the most commonly executed functions (by accessing the web pages) and then use TKPROF with Explain plan to check the actual execution plans, then creating more indexes if needed.

Is this the right approach? Do you have any other recommendations? Is this a problem after all?

bill milbratz

                            Database File IO
Weights
                                ordered by

Drive

DRIVE FILE_NAME                                  TOTAL_IO

WEIGHT

----- ---------------------------------------- ---------- -------

/opt/ /opt/oracle/oradata/LINP/temp01.dbf           25921
100.00


      /opt/oracle/oradata/LINP/lin_tab01.dbf         4425

17.07


      /opt/oracle/oradata/LINP/system01.dbf          3507

13.53


      /opt/oracle/oradata/LINP/lin_idx01.dbf         2122

8.19


      /opt/oracle/oradata/LINP/rbs01.dbf             1314

5.07


      /opt/oracle/oradata/LINP/tools01.dbf

61     .24
*****                                                     -------

sum
144.09

Query:
select
substr(DF.Name, 1,5) Drive,
DF.Name File_Name,
FS.Phyblkrd+FS.Phyblkwrt Total_IO,
100*(FS.Phyblkrd+FS.Phyblkwrt)/MaxIO Weight from V$FILESTAT FS, V$DATAFILE DF,
  (select MAX(Phyblkrd+Phyblkwrt) MaxIO
    from V$FILESTAT)
where DF.File# = FS.File#
order by Drive, Weight desc

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu May 11 2000 - 00:00:00 CDT