Re: Help with our sql tuning

Are there indexes out there not being used?

Are there indexes that need to be created?

Daniel Morgan

Mike F wrote:

> 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
>
> CREATE OR REPLACE VIEW incident_info_view
> (
> direction_id,
> direction_short_desc,
> direction_desc,
> criticality_id,
> criticality_short_desc,
> criticality_desc,
> type_short_desc,
> type_id,
> building_id,
> metro_id,
> building_short_desc,
> building_desc,
> site_sort_code,
> incident_id,
> entry_time,
> description,
> department,
> initial_id,
> geoloc,
> start_time,
> end_time,
> last_updated_time,
> incident_sort_code,
> status_code,
> type_desc
> )
> AS
> SELECT direction.direction_id, direction.direction_short_desc,
> direction.direction_desc, criticality.criticality_id,
> criticality.criticality_short_desc, criticality.criticality_desc,
> incident_type.type_short_desc, incident_type.type_id,
> building.building_id,
> building.metro_id, building.building_short_desc,
> building.building_desc,
> building.site_sort_code, incident.incident_id, incident.entry_time,
> incident.description, incident.department, incident.initial_id,
> incident.geoloc, incident.start_time, incident.end_time,
> incident.last_updated_time, incident.incident_sort_code,
> incident.status_code, incident_type.type_desc
> FROM direction, criticality, incident_type, building, incident
> WHERE incident.direction_id = direction.direction_id(+) AND
> incident.criticality_id = criticality.criticality_id AND
> incident.type_id = incident_type.type_id(+) AND
> incident.buildingG_id = building.building_id
>
> 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
> This is a spam protected message. Please answer with reference header.
> Posted via http://www.usenet-replayer.com/cgi/content/new
Received on Wed Feb 13 2002 - 15:55:03 CST