Re: Help with our sql tuning

Hello Mike,
Well, you said it yourself:

> TABLE ACCESS (FULL) of 'accident.INCIDENT_TYPE(1)
>
> The incident table is the largest table, has about 100,000 rows, all

A FULL table scan on 100000 rows is happening. Put an index on any of the following fields and things might speed up:

> WHERE I.START_TIME < :b1
> AND I.END_TIME > :b1
> AND I.STATUS_CODE = 'A'
> AND I.SITE_ID = :b4

If your STATUS_CODE has only 1000 'A' entries in the table then an index on this field might do the trick. Count them and try it. Otherwise the TIME fields or SITE_ID might do it. Good luck,

      Roelof Schierbeek

R. Schierbeek, Bytelife BV
email : info_at_bytelife.com
www : www.bytelife.com

Mike F <u518615722_at_spawnkill.ip-mobilphone.net> schreef in
> One of our sql statement is costing us 80% of I/O of the whole database.
>
> SELECT I.INCIDENT_ID,
> I.DESCRIPTION,
> I.DEPARTMENT,
> I.ENTRY_TIME,
> I.START_TIME,
> I.END_TIME,
> I.LAST_UPDATED_TIME,
> I.BUILDING_ID,
> I.CRITICALITY_ID,
> I.DIRECTION_ID,
> I.TYPE_ID,
> NVL(I.BUILDING_DESC,'Unspecified') BUILDING_DESC,
> NVL(I.BUILDING_SHORT_DESC,'Unspecified') BUILDING_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,
> to_number(I.END_TIME - :b1 ) * (24 * 60 ) MINUTES_REMAINING,
> I.GEOLOC.SDO_POINT.X LNG,
> I.GEOLOC.SDO_POINT.Y LAT
> FROM INCIDENT_INFO_VIEW I
> WHERE I.START_TIME < :b1
> AND I.END_TIME > :b1
> AND I.STATUS_CODE = 'A'
> AND I.SITE_ID = :b4 ORDER
> BY I.CRITICALITY_SHORT_DESC
>
> The view is defined as
>

....<snap>
...
> the explain plan is as follows:
>
> SELECT STATEMENT Cost = 35(35)
> SORT (ORDER BY) (35)
> HASH JOIN (OUTER)(32)
> HASH JOIN(OUTER)(30)
> HASH JOIN(28)
> HASH JOIN(26)
> TABLE ACCESS(FULL) of 'accident.building' (1)
> TABLE ACCESS(BY INDEX ROWID) OF 'accident.incident'(24)
> BITMAP CONVERSION(TO ROWIDS)(0)
> BITMAP CONVERSION(FROM ROWIDS)(0)
> SORT(ORDER BY)(0)
> INDEXS(RANGE SCAN)OF 'accident.XIEINCIDNT(4)
> BITMAP CONVERSION(FROM ROWIDS)(0)
> SORT(ORDER BY)(0)
> INDEXS(RANGE SCAN)OF 'accident.XIEINCIDNT(4)
> BITMAP CONVERSION(FROM ROWIDS)(0)
> SORT(ORDER BY)(0)
> INDEXS(RANGE SCAN)OF'accident.XIE1INCIDNT(4)
> BITMAP CONVERSION(FROM ROWIDS)(0)
> SORT(ORDER BY)(0)
> INDEX(RANGE SCAN)of 'accident.XIE2INCIDENT(4)
> BITMAP CONVERSION(FROM ROWIDS(0)
> SORT(ORDER BY(0)
> INDEX(RANGE SCAN) of 'accident.XIF26INCIDENT)(44)
> TABLE ACCESS(FULL) of 'accident.CRITICALITY(1)
> TABLE ACCESS(FULL) of 'accident.DIRECTION'(1)
> TABLE ACCESS (FULL) of 'accident.INCIDENT_TYPE(1)
>
> The incident table is the largest table, has about 100,000 rows, all
> the other table,CRITICALITY,DIRECTION,INCIDENT_TYPE has less than 100
> rows.
>
> Each day, the statistics is as follows
> rows processed 5064
> Buffer gets 246464
> Disk Reads 215223
> Sorts 2137008
> executions 844
>
> Could you help us out?
> Thank you very much.
> Sent by dbadba62 from hotmail subpart of com
Received on Thu Feb 14 2002 - 08:38:23 CST