u518615722_at_spawnkill.ip-mobilphone.net (Mike F) wrote in message news:<l.1011927183.1448516845_at_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.
To get reliable suggestions you need to post the explain plan for the
query along with available index information. Since the query runs
much better when you substitute a constant for the bind variable it
may help if you also post the explain plan for this for comparison
purposes.
Received on Fri Jan 25 2002 - 08:18:09 CST
