Dennis, I tend to treat use of hints for exceptional cases only. With collections CASTed as tables, I seem to have a generalized problem of tables involved being scanned FULL (not using the available indexes) and query response being slow. I can't seem to build a query with collections and have it use the indexes.
I get a much better response if I: a)parse the input collection parameter and use dynamic SQL with IN list b) use the RULK hint We also Analyze all tables in the schema once a week and were hoping that the cast based optimizer would have detailed information available to use the correct access path. --- DENNIS WILLIAMS <[EMAIL PROTECTED]> wrote: > Sundeep - Have you tried other hints, like FIRST_ROWS? Or are you > trying to > avoid hints entirely? > > Dennis Williams > DBA, 40%OCP > Lifetouch, Inc. > [EMAIL PROTECTED] > > > -----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] > > __________________________________________________ > Do you Yahoo!? > Yahoo! Mail Plus - Powerful. Affordable. Sign up now. > http://mailplus.yahoo.com > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: sundeep maini > 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). > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: DENNIS WILLIAMS > 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). > ===== Sundeep Maini Consultant Currently on Assignement at Caterpillar Peoria [EMAIL PROTECTED] __________________________________________________ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: sundeep maini 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).
