| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: SQL Tuning Help
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,
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
 |
 |