Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: SQL Tuning Help

RE: SQL Tuning Help

From: Deshpande, Kirti <kirti.deshpande_at_verizon.com>
Date: Wed, 05 Feb 2003 18:09:09 -0800
Message-ID: <F001.00545475.20030205180909@fatcity.com>


Sundeep,
 Have you reviewed Tim Gorman's paper titled: 'The Search For Intelligent Life In The Cost-Based Optimizer'? Check it out at http://www.evdbt.com. It may help.

-----Original Message-----
Sent: Wednesday, February 05, 2003 5:30 PM To: Multiple recipients of list ORACLE-L

Oracle 8.1.7.4 on HP-UX

I am using collections to pass multiple values from client to the database to gather values for more than input values. The queries produce the results but the without the RULE hint the response is dramatically slower. Following is just one of the examples but I have many many queries which exhibit the same behavior.

Since RULE based optimization is headed for the chopping block we are wondering what is the alternative.

SELECT eqp.equipment_id,

	eqp.manufacturer_code, 
	eqp.model_num, 
	eqp.equipment_serial_num, 
	DECODE(SIGN(eqp.last_pm_performed_at_hrs -
eqp.current_meter_reading_hrs),1,
'SMU_ADJUSTED',eqp.last_pm_performed_at_hrs) smu_adjusted,
	eqp.productlink_equipment_code, 
	ecps.pm_schedule_name, 
	epp.performed_datetime, 
	DECODE(epp.comment_text,NULL,1,0) comments_available, 
	  emr.reading_date, 
	emr.meter_reading_value, 
	equipment_event_log.event_status(eqp.equipment_id,2), 
	equipments_next_pm_due.pms_due_list(eqp.equipment_id) pms_due_list, 
	equipments_next_pm_due.perform_at_hrs_cnt(eqp.equipment_id)
next_pm_hrs

   FROM equipments eqp,

        equipment_meter_readings emr, 
	equipment_pm_performed epp, 
	equipment_class_pm_schedules ecps, 
	TABLE(CAST(id_table_t(1000000071,1000000072,1000000073,1000000074)
AS id_table_t)) eqp_list
  WHERE eqp.epp_id_last_pm_performed = epp.epp_id (+) 
    AND eqp.emr_id_current_meter_reading = emr.emr_id (+) 
    AND epp.ecps_id = ecps.ecps_id (+) 
    AND eqp.equipment_id = eqp_list.column_value
/

Plan:

SELECT STATEMENT Hint=CHOOSE		8 K	 	510      
  HASH JOIN OUTER		8 K	582 K	510  	 	     
    HASH JOIN OUTER		8 K	510 K	497  	 	     
      HASH JOIN OUTER		8 K	390 K	489  	 	      
        HASH JOIN		8 K	279 K	287  	 	      
          COLLECTION ITERATOR CONSTRUCTOR FETCH		  	 
          TABLE ACCESS FULL	EQUIPMENTS	192 K	6 M	256
        TABLE ACCESS FULL	EQUIPMENT_METER_READINGS	221 K	2 M	151  	 	
     	             	 
      TABLE ACCESS FULL	EQUIPMENT_PM_PERFORMED	96  	1 K	
    TABLE ACCESS FULL	EQUIPMENT_CLASS_PM_SCHEDULES	2 K	22 K	4  

Following is the Plan with /*+ RULE */ hint has the expected fast response and the desired plan:

SELECT STATEMENT Hint=HINT: RULE

  NESTED LOOPS OUTER	  	 	 	 	      	 
    NESTED LOOPS OUTER		  	 	 	 	      
      NESTED LOOPS OUTER		  	 	 	 
        NESTED LOOPS		  	 	 	 	 
          COLLECTION ITERATOR CONSTRUCTOR FETCH		  	 
          TABLE ACCESS BY INDEX ROWID	EQUIPMENTS	  	 
            INDEX UNIQUE SCAN	EQP_PK	  	 	 	 
        TABLE ACCESS BY INDEX ROWID	EQUIPMENT_PM_PERFORMED	 
          INDEX UNIQUE SCAN	EPP_PK	  	 	 	 
      TABLE ACCESS BY INDEX ROWID	EQUIPMENT_CLASS_PM_SCHEDULES 
        INDEX UNIQUE SCAN	ECPMS_PK	  	 	 
    TABLE ACCESS BY INDEX ROWID	EQUIPMENT_METER_READINGS	  
      INDEX UNIQUE SCAN	EMR_PK	  

I have tried both versions IN (TABLE(CAST( as a predicate and as a pseudo-table in FROM (as in the query above) and it made no difference to the plan. I searched askTOM and heard similar sentiments about performance being echoed by other users but no solutions.

Any tips or insights as to how to avoid the full table scans (all of which are 10-100M in size) of the large table without the RULE hint. A more thorough explanation of what is happening and why would be a bonus.

TIA                  


Sundeep Maini
Consultant
Currently on Assignement at Caterpillar Peoria sundeep_maini_k_at_cat.com

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Deshpande, Kirti
  INET: kirti.deshpande_at_verizon.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Wed Feb 05 2003 - 20:09:09 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US