Help with our sql tuning

From: Mike F <u518615722_at_spawnkill.ip-mobilphone.net>
Date: Wed, 13 Feb 2002 20:56:31 GMT
Message-ID: <l.1013633792.1744781494@[64.94.198.252]>

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 - 14:56:31 CST