help: hot sql problem

From: Mike F <u518615722_at_spawnkill.ip-mobilphone.net>
Date: Fri, 25 Jan 2002 02:53:03 GMT
Message-ID: <l.1011927183.1448516845@pool-151-197-238-45.phil.east.verizon.net>

We are using 8.1.7.2

One of our top sql statement seems to a huge disk read on our temp tablespace. The disk read is 699300 and buffer gets is 813062. The  statement usually returns only 10-20 results, but why it needs so many sorts? ( I guess the disk reads is from the sorting). But when I replace the bind value with hard coded value, the disk read is only 9,  buffer gets is 137, but why all the books suggests to use binding  value instead of hard coded statement?

I was trying to increase the sort_area_size from 650k to 2m, but the whole system become so slow and CPU idle become 0%, because all the fifty applications (JAVA) are issuing the same statement. If I kept sort_area_size to 650k, the hot file is always the temp tablespace with  the i/o almost 80% of all the i/o on database files.

The statement is as follows,
SELECT I.ID,

I.DESCRIPTION,
I.LOCATION,
I.ENTRY_TIME,
I.START_TIME,
I.END_TIME,
I.LAST_UPDATED_TIME,
I.ORDER_ID,
I.DEPT_ID,
I.DIRECTION_ID,
I.TYPE_ID,

NVL(I.ZONE_DESC,'Unspecified') ZONE_DESC, NVL(I.ZONE_SHORT_DESC,'Unspecified'ZONE_SHORT_DESC, I.CRITICALITY_SHORT_DESC CRITICALITY_DESC,

NVL(I.TYPE_SHORT_DESC,'Unspecified') TYPE_SHORT_DESC,
NVL(I.TYPE_DESC,'Unspecified') TYPE_DESC,
NVL(I.DIRECTION_SHORT_DESC,'Unspecified') DIRECTION_DESC,

(I.END_TIME - :b1 ) * (24 * 60 ) MINUTES_REMAINING, I.GEOLOC.SDO_POINT.X LNG,
I.GEOLOC.SDO_POINT.Y LAT
FROM ORDER_INFO_VIEW I
WHERE I.START_TIME < :b1

AND I.END_TIME > :b1  
AND I.STATUS_CODE = 'A'
AND I.METRO_ID = :b4 

ORDER BY I.CRITICALITY_SHORT_DESC Could somebody tell me how should I deal with the problem?

Thanks for you help.  

-- 
Sent by dbadba62  from hotmail piece from com
This is a spam protected message. Please answer with reference header.
Posted via http://www.usenet-replayer.com/cgi/content/new

Received on Thu Jan 24 2002 - 20:53:03 CST