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.
- Kirti
-----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
[EMAIL PROTECTED]
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Deshpande, Kirti
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
