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 <DWILLIAMS_at_LIFETOUCH.COM> 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.
> dwilliams_at_lifetouch.com
>
>
> -----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
>
> __________________________________________________
> 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: sundeep_maini_at_yahoo.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).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: DENNIS WILLIAMS
> INET: DWILLIAMS_at_LIFETOUCH.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).
>
Sundeep Maini
Consultant
Currently on Assignement at Caterpillar Peoria
sundeep_maini_k_at_cat.com
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: sundeep_maini_at_yahoo.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 Thu Feb 06 2003 - 11:04:04 CST
