Query Question

From: UNoWho <no_at_way.com>
Date: 2000/07/24
Message-ID: <397C5F87.3BD7E6DD@REMOVEMEunisys.com>#1/1

Here's the scenario:

I have 2 DB's; DEVL and PROD. Both run on NT 4.0/SP4. Both DB's are version 8.0.5

DEVL

Total System Global Area                                      46637056

bytes
Fixed Size
49152 bytes
Variable Size
20299776 bytes
Database Buffers(blck size 8K x # of db buffers) 26214400 bytes Redo Buffers
73728 bytes

PROD
Total System Global Area
47742976 bytes
Fixed
Size
49152 bytes
Variable
Size
21307392 bytes
Database Buffers(blck size 8K x # of db buffers) 26214400 bytes Redo
Buffers
172032 bytes

When I run the following SQL statement in PROD, it runs fine. It returns results almost immediately. However, when I run it in DEVL, the query can run for over an hour and it never finishes. If I eliminate the ORDER BY clause, the query in DEVL will take about 5 minutes to process. Any ideas about what is going on? Is this a particulary complex query?

Here's the SQL:

SELECT INITCAP(a.tablespace_name),

       a.file_name,
       b.file#,
       b.phyblkrd,
       b.phyblkwrt,
       b.phyrds,
       b.phywrts,
       (b.phyrds+b.phywrts) total_io  ,
       readtim,
       writetim ,
       round(readtim/decode(phyrds,0,1,phyrds),2)    avgreadtime,
       round(writetim/decode(phywrts,0,1,phywrts),2) avgwritetime

FROM sys.v_$filestat b, sys.dba_data_files a WHERE b.file# = a.file_id
ORDER BY total_io desc

Are there any stats in V$SYSSTATS that I could look at to determine if my DEVL DB doesn't have enough memory to perform this operation efficiently? Any help would be greatly appreciated. Thanks!!!!!!!!!!!!

MLM Received on Mon Jul 24 2000 - 00:00:00 CDT