RE: Sql Tuning Thoughts?
Hi Tracy, A few observations 1. Can we use a more selective where clause?( Currently we are going for a PK Range Scan. Can we change the where clause to go for a PK Uniq scan?) 2. The Fetch time is very high.I guessthe fetch is a single row fetch. We can tune the code for bulk fetch by fetching say 1000 to 5000 rows at a time? Best Regards Sriram Kumar From: Tracy Rahmlow [mailto:[EMAIL PROTECTED] Sent: Thursday, January 22, 2004 10:54 PMTo: Multiple recipients of list ORACLE-LSubject: Sql Tuning Thoughts? This statement is from a batch program within a pl/sql procedure. (Also, I have many similar ones within the process) The policy table has approximately 6.2 million rows. The procedure is to incrementally(daily) build an extract table from multiple tables. The extract table is then used for reporting purposes. The statement performs well per policy, however it is being executed 43,000+ times. Is there a design option available to me to reduce the number of executions and be more scaleable? I am considering the creation of an index to incorporate both the policy_number and the pol_eff_date hopefully eliminating the table access. We are currently on 8.1.7. *** SELECT MIN(P.POL_EFF_DATE) FROM PHXADM.POLICY P WHERE P.POLICY_NUMBER = :b1 call countcpu elapseddisk query currentrows --- -- -- -- -- -- -- Parse 1 0.000.01 0 0 0 0 Execute 43814 1.95 1.57 0 0 0 0 Fetch 43814 55.88 599.11 408248 568098 043814 --- -- -- -- -- -- -- total 87629 57.83 600.69 408248 568098 0 43814 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 547 (RPTADM) (recursive depth: 1) Rows Execution Plan --- ---0 SELECT STATEMENT GOAL: CHOOSE0 SORT (AGGREGATE)0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'POLICY'0 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'POLICY_PK' (UNIQUE) American Express made the followingannotations on 01/22/2004 10:24:24 AM--**"This message and any attachments are solely for the intended recipient and may contain confidential or privileged information. If you are not the intended recipient, any disclosure, copying, use, or distribution of the information included in this message and any attachments is prohibited. If you have received this communication in error, please notify us by reply e-mail and immediately and permanently delete this message and any attachments. Thank you."**==DISCLAIMER:This message contains privileged and confidential information and is intended only for the individual named.If you are not the intended recipient you should not disseminate,distribute,store,print, copy or deliver this message.Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system.E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted,corrupted,lost,destroyed,arrive late or incomplete or contain viruses.The sender therefore does not accept liability for any errors or omissions in the contents of this message which arise as a result of e-mail transmission. If verification is required please request a hard-copy version.
RE: Sql Tuning Thoughts?
Tracy, Take a look at the thing calling this 43,814 times. Can this query be used as an inline view for the thing using this querys result set? If so, then youll eliminate 87,629 database calls. As Tom Kyte says, Tune the QUESTION, not the query. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Nullius in verba Upcoming events: - Performance Diagnosis101: 1/27 Atlanta - SQL Optimization101: 2/16 Dallas - Hotsos Symposium 2004: March 710 Dallas - Visit www.hotsos.com for schedule details... -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Tracy Rahmlow Sent: Thursday, January 22, 2004 11:24 AM To: Multiple recipients of list ORACLE-L Subject: Sql Tuning Thoughts? This statement is from a batch program within a pl/sql procedure. (Also, I have many similar ones within the process) The policy table has approximately 6.2 million rows. The procedure is to incrementally(daily) build an extract table from multiple tables. The extract table is then used for reporting purposes. The statement performs well per policy, however it is being executed 43,000+ times. Is there a design option available to me to reduce the number of executions and be more scaleable? I am considering the creation of an index to incorporate both the policy_number and the pol_eff_date hopefully eliminating the table access. We are currently on 8.1.7. *** SELECT MIN(P.POL_EFF_DATE) FROM PHXADM.POLICY P WHERE P.POLICY_NUMBER = :b1 call countcpu elapsed disk query current rows --- -- -- -- -- -- -- Parse 1 0.000.01 0 0 0 0 Execute 43814 1.951.57 0 0 0 0 Fetch 43814 55.88 599.11 408248 568098 0 43814 --- -- -- -- -- -- -- total 87629 57.83 600.69 408248 568098 0 43814 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 547 (RPTADM) (recursive depth: 1) Rows Execution Plan --- --- 0 SELECT STATEMENT GOAL: CHOOSE 0 SORT (AGGREGATE) 0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'POLICY' 0 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'POLICY_PK' (UNIQUE) American Express made the following annotations on 01/22/2004 10:24:24 AM -- ** This message and any attachments are solely for the intended recipient and may contain confidential or privileged information. If you are not the intended recipient, any disclosure, copying, use, or distribution of the information included in this message and any attachments is prohibited. If you have received this communication in error, please notify us by reply e-mail and immediately and permanently delete this message and any attachments. Thank you. ** ==
RE: Sql Tuning Thoughts?
it depends on how the code is written ... maybe it is doing row operations ... care to show the code (at-least pseudo code) ?? Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message-From: Tracy Rahmlow [mailto:[EMAIL PROTECTED]Sent: Thursday, January 22, 2004 12:24 PMTo: Multiple recipients of list ORACLE-LSubject: Sql Tuning Thoughts?This statement is from a batch program within a pl/sql procedure. (Also, I have many similar ones within the process) The policy table has approximately 6.2 million rows. The procedure is to incrementally(daily) build an extract table from multiple tables. The extract table is then used for reporting purposes. The statement performs well per policy, however it is being executed 43,000+ times. Is there a design option available to me to reduce the number of executions and be more scaleable? I am considering the creation of an index to incorporate both the policy_number and the pol_eff_date hopefully eliminating the table access. We are currently on 8.1.7. *** SELECT MIN(P.POL_EFF_DATE) FROM PHXADM.POLICY P WHERE P.POLICY_NUMBER = :b1 call countcpu elapseddisk query currentrows --- -- -- -- -- -- -- Parse 1 0.000.01 0 0 0 0 Execute 43814 1.95 1.57 0 0 0 0 Fetch 43814 55.88 599.11 408248 568098 043814 --- -- -- -- -- -- -- total 87629 57.83 600.69 408248 568098 0 43814 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 547 (RPTADM) (recursive depth: 1) Rows Execution Plan --- --- 0 SELECT STATEMENT GOAL: CHOOSE0 SORT (AGGREGATE)0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'POLICY'0 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'POLICY_PK' (UNIQUE) American Express made the followingannotations on 01/22/2004 10:24:24 AM--**"This message and any attachments are solely for the intended recipient and may contain confidential or privileged information. If you are not the intended recipient, any disclosure, copying, use, or distribution of the information included in this message and any attachments is prohibited. If you have received this communication in error, please notify us by reply e-mail and immediately and permanently delete this message and any attachments. Thank you."**==**This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.**4
Re: Sql Tuning Thoughts?
Comments in line. On 01/22/2004 12:24:26 PM, Tracy Rahmlow wrote: This statement is from a batch program within a pl/sql procedure. (Also, I have many similar ones within the process) The policy table has approximately 6.2 million rows. The procedure is to incrementally(daily) build an extract table from multiple tables. The extract table is then used for reporting purposes. The statement performs well per policy, however it is being executed 43,000+ times. Is there a design option available to me to reduce the number of executions and be more scaleable? ALTER DATABSE ENABLE OPTIMAL [DW|OLTP] DESIGN; statement will work in Oracle 18e (E comes from expensive). Until then, I'd try materialized views. What you are trying to do is to build one table based on selecting records from several others. If you reformulate the previous sentence, you'll get the definition of a MV. I am considering the creation of an index to incorporate both the policy_number and the pol_eff_date hopefully eliminating the table access. We are currently on 8.1.7. *** SELECT MIN(P.POL_EFF_DATE) FROM PHXADM.POLICY P WHERE P.POLICY_NUMBER = :b1 call count cpuelapsed disk querycurrent rows --- -- -- -- -- -- -- Parse1 0.00 0.01 0 0 0 0 Execute 43814 1.95 1.57 0 0 0 0 Fetch43814 55.88 599.11 408248 568098 0 43814 --- -- -- -- -- -- -- total87629 57.83 600.69 408248 568098 0 43814 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 547 (RPTADM) (recursive depth: 1) Rows Execution Plan --- --- 0 SELECT STATEMENT GOAL: CHOOSE 0 SORT (AGGREGATE) 0TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'POLICY' 0 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'POLICY_PK' (UNIQUE) American Express made the following annotations on 01/22/2004 10:24:24 AM -- ** This message and any attachments are solely for the intended recipient and may contain confidential or privileged information. If you are not the intended recipient, any disclosure, copying, use, or distribution of the information included in this message and any attachments is prohibited. If you have received this communication in error, please notify us by reply e-mail and immediately and permanently delete this message and any attachments. Thank you. ** == -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala 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).
Re: Sql Tuning Thoughts?
Since you're doing an aggregate function, you may want to investigate using materialized views here. Since, I'm assuming, policy effective dates aren't something that changes on a minute-to-minute basis, you could set up a materialized view that refreshed every night and would answer this question in nothing flat. Justin Cave At 10:24 AM 1/22/2004, Tracy Rahmlow wrote: This statement is from a batch program within a pl/sql procedure. (Also, I have many similar ones within the process) The policy table has approximately 6.2 million rows. The procedure is to incrementally(daily) build an extract table from multiple tables. The extract table is then used for reporting purposes. The statement performs well per policy, however it is being executed 43,000+ times. Is there a design option available to me to reduce the number of executions and be more scaleable? I am considering the creation of an index to incorporate both the policy_number and the pol_eff_date hopefully eliminating the table access. We are currently on 8.1.7. *** SELECT MIN(P.POL_EFF_DATE) FROM PHXADM.POLICY P WHERE P.POLICY_NUMBER = :b1 call count cpu elapsed disk query current rows --- -- -- -- -- -- -- Parse 1 0.00 0.01 0 0 0 0 Execute 43814 1.95 1.57 0 0 0 0 Fetch 43814 55.88 599.11 408248 568098 0 43814 --- -- -- -- -- -- -- total 87629 57.83 600.69 408248 568098 0 43814 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 547 (RPTADM) (recursive depth: 1) Rows Execution Plan --- --- 0 SELECT STATEMENT GOAL: CHOOSE 0 SORT (AGGREGATE) 0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'POLICY' 0 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'POLICY_PK' (UNIQUE) American Express made the following annotations on 01/22/2004 10:24:24 AM -- ** This message and any attachments are solely for the intended recipient and may contain confidential or privileged information. If you are not the intended recipient, any disclosure, copying, use, or distribution of the information included in this message and any attachments is prohibited. If you have received this communication in error, please notify us by reply e-mail and immediately and permanently delete this message and any attachments. Thank you. ** ==
Re: SQL tuning...
Venu, Do your 10046 tracing and run it through tkprof of the Hotsos Profiler and you'll know exactly why your query is taking so long. Regards, Gudmundur Can anyone tell me whats wrong with the explain plan below#8230; this update is running for quite long time#8230;. Even without a single full-table access#8230; nbsp; Thank you in advance! nbsp; nbsp; UPDATE STATEMENT Optimizer=CHOOSE (Cost=83 Card=4893 Bytes=327831) UPDATE OF CCM_DEBIT_TBL SEQUENCE OF STAFFWARE_CASEID_S FILTER TABLE ACCESS (BY GLOBAL INDEX ROWID) OF CCM_DEBIT_TBL (Cost=83 Card=4893 Bytes=327831) INDEX (RANGE SCAN) OF IDX_DEBIT_DUE_DATE (NON-UNIQUE) (Cost=26 Card=4893) INDEX (RANGE SCAN) OF IDX_PLAN_DEBIT_CISDEBITDEAD (NON-UNIQUE) (Cost=3 Card=1 Bytes=26) INDEX (RANGE SCAN) OF IDX_PLAN_DEBIT_CISDEBITDEAD (NON-UNIQUE) (Cost=3 Card=1 Bytes=28) TABLE ACCESS (BY GLOBAL INDEX ROWID) OF CCM_ACCOUNT_TBL (Cost=1 Card=1 Bytes=9) INDEX (UNIQUE SCAN) OF PK_ACCOUNT (UNIQUE) (Cost=2 Card=1) Confidentiality Notice The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain confidential or privileged information. If you are not the intended recipient, please notify the sender at Wipro or [EMAIL PROTECTED] immediately and destroy all copies of this message and any attachments. Þessi póstur var sendur með vefpósti mi, http://www.mi.is -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gudmundur Josepsson 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).
RE: SQL tuning...
without knowing the requirement,index,statistics it is very hard to tell. -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]On Behalf Of Venu GopalSent: Thursday, December 18, 2003 5:29 AMTo: Multiple recipients of list ORACLE-LSubject: SQL tuning... Can anyone tell me whats wrong with the explain plan below this update is running for quite long time. Even without a single full-table access Thank you in advance! UPDATE STATEMENT Optimizer=CHOOSE (Cost=83 Card=4893 Bytes=327831)UPDATE OF CCM_DEBIT_TBLSEQUENCE OF STAFFWARE_CASEID_SFILTERTABLE ACCESS (BY GLOBAL INDEX ROWID) OF CCM_DEBIT_TBL (Cost=83 Card=4893 Bytes=327831)INDEX (RANGE SCAN) OF IDX_DEBIT_DUE_DATE (NON-UNIQUE) (Cost=26 Card=4893)INDEX (RANGE SCAN) OF IDX_PLAN_DEBIT_CISDEBITDEAD (NON-UNIQUE) (Cost=3 Card=1 Bytes=26)INDEX (RANGE SCAN) OF IDX_PLAN_DEBIT_CISDEBITDEAD (NON-UNIQUE) (Cost=3 Card=1 Bytes=28)TABLE ACCESS (BY GLOBAL INDEX ROWID) OF CCM_ACCOUNT_TBL (Cost=1 Card=1 Bytes=9)INDEX (UNIQUE SCAN) OF PK_ACCOUNT (UNIQUE) (Cost=2 Card=1) Confidentiality Notice The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain confidential or privileged information. If you are not the intended recipient, please notify the sender at Wipro or [EMAIL PROTECTED] immediately and destroy all copies of this message and any attachments.
RE: SQL Tuning Help
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(100071,100072,100073,100074) 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_READINGS221 K 2 M 151 TABLE ACCESS FULL EQUIPMENT_PM_PERFORMED 96 1 K TABLE ACCESS FULL EQUIPMENT_CLASS_PM_SCHEDULES2 K 22 K4 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
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. - 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(100071,100072,100073,100074) 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=CHOOSE8 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_READINGS221 K 2 M 151 TABLE ACCESS FULL EQUIPMENT_PM_PERFORMED 96 1 K TABLE ACCESS FULL EQUIPMENT_CLASS_PM_SCHEDULES2 K 22 K4 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).
RE: SQL Tuning Help
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(100071,100072,100073,100074) 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=CHOOSE8 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_READINGS221 K 2 M 151 TABLE ACCESS FULL EQUIPMENT_PM_PERFORMED 96 1 K TABLE ACCESS FULL EQUIPMENT_CLASS_PM_SCHEDULES2 K 22 K4 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
Re: SQL Tuning
Hamid Alavi wrote: Hi List, Is anybody have any idea for better performance for the following query: I just change the OR to UNION ALL Appreciate any idea. SELECT a.evh_event_id FROM event_history_evh a WHERE (a.evh_event_id = 2 AND a.evh_created_date = (SELECT max( b.evh_created_date ) FROM event_history_evh b WHERE b.evh_session_id = 1785619526 AND b.evh_task_list_id = a.evh_task_list_id AND (sysdate - b.evh_created_date )*1440 5 AND b.evh_task_list_id != 469602)) OR---UNIN ALL (a.evh_event_id = 2 AND a.evh_created_date = (SELECT max( c.evh_created_date ) FROM event_history_evh c WHERE c.evh_session_id != 1785619526 AND c.evh_task_list_id = a.evh_task_list_id AND (sysdate - c.evh_created_date)*1440 5 AND c.evh_task_list_id = 469602)) Hamid Alavi Office : 818-737-0526 Cell phone : 818-416-5095 May I hope you were given this to tune after an especially hard week and that you didn't write it yourself ? It's beginning to be late here and I am beginning to feel sleepy, but I think that (A = B and C != D) or (A != B and C = D) can be simplified into not (A = B and C = D) which makes the question 'OR or UNION ALL' a thing of the past. Which brings us to : SELECT a.evh_event_id FROM event_history_evh a WHERE (a.evh_event_id = 2 AND a.evh_created_date = (SELECT max( b.evh_created_date ) FROM event_history_evh b WHERE b.evh_task_list_id = a.evh_task_list_id AND (sysdate - b.evh_created_date )*1440 5 AND not (b.evh_session_id = 1785619526 and b.evh_task_list_id = 469602)) Now that it's a bit less hairy, it looks like it returns either '2' (possibly several ones) or nothing. Let's further our analysis, (sysdate - blahblah) looks ugly if you have an index on evh_created_date (which would help with both the max() function and the condition). (sysdate - b.evh_created_date) * 1440 5 would probably better be written as sysdate - 5 / 1440 b.evh_created_date (which I personnally understand better - created more than 5 minutes ago). What does remain ? Hmmm, your subquery is correlated, not too good if evh_event_id is not very discriminant. What about : SELECT a.evh_event_id FROM event_history_evh a WHERE a.evh_event_id = 2 AND (a.evh_task_list_id, a.evh_created_date) in (SELECT b.evh_task_list, max( b.evh_created_date ) FROM event_history_evh b WHERE sysdate - 5 / 1440 b.evh_created_date AND not (b.evh_session_id = 1785619526 and b.evh_task_list_id = 469602) group by b.evh_task_list) ? Either this or the correlated subquery,depending on volumes. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult 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).
Re: SQL tuning question.
At 11:26 AM 12/18/2002 -0800, Jibo John wrote:
Hello DBAs,
I am currently involved in improving the search performance for a tool
which queries a table having a million records (and the table is growing
at a rate of 3000 records per day).
So sounds like, in the next year, it will at least 2 million rows,
true?
Thought of introducing
Intermedia search for 4 columns in the search table.
Created CONTEXT indexes to three varchar (4000) columns as well as a
varchar(500) column and used the CONTAINS keyword.
This has really improved the performance speed (5 times improvement) than
the previous LIKE clause query if the number of records returned are less
than 2000.
How ever, for those search criteria which returns more than 10,000
records, the query with CONTAINS clause is slower than the query with
LIKE clause :(
I thought it can never go worse :)
Sounds like your hitting the scalability barrier for context searches,
whatever that may be. I am assuming you've looked into parallel
settings for the table, and that your SGA is sized adequately to handle
the many data blocks returned by such queries. We have seen marked
improvements in these types of queries by implementing partitioning of
large or quick growing tables. Especially when you can partition by
a date/time column.
Of course, the other possibility is to create a column based upon the
common strings your sample queries below seem to be referencing
(version). Of course, I realize, especially with purchased
software, this might be impossible.
Good luck and let me know if you try partitioning!
Here are the two
queries:
1. using index
---
SELECT id FROM search_table WHERE contains (product, '{product_name}')
0 and (contains (VERSION1, '{ 11.0(1) }') 0 OR contains
(VERSION2, '{ 11.0(1) }') 0 OR contains (VERSION3, '{ 11.0(1) }')
0)
2. Without using index
SELECT /*+ PARALLEL(SEARCH_TABLE, 10) */ id FROM search_table WHERE
PRODUCT like '%product_name%' and (VERSION1 like '% 11.0(1) %' OR
VERSION2 like '% 11.0(1) %' OR VERSION3 like '% 11.0(1) %')
Few facts
Re: SQL tuning question.
Michael, thanks for responding to my question. I understand that the possible solution would be to go with partitioning. Already started looking into that :) Thanks again, -Jibo Sounds like your hitting the scalability barrier for context searches, whatever that may be. I am assuming you've looked into parallel settings for the table, and that your SGA is sized adequately to handle the many data blocks returned by such queries. We have seen marked improvements in these types of queries by implementing partitioning of large or quick growing tables. Especially when you can partition by a date/time column. Of course, the other possibility is to create a column based upon the common strings your sample queries below seem to be referencing (version). Of course, I realize, especially with purchased software, this might be impossible. Good luck and let me know if you try partitioning! -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jibo John 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).
RE: sql tuning help
Tom, Apologies if this has been covered already but isn't this a prime candidate for the sum(decode()) trick with perhaps instr used to do the match. I think you could take count of all records and subtract the 1 or 0 for each of the specific ones. The select part would look horrendous but overall I think it would be faster as it would allow you to drive off the phy_id which I assume would be indexed. Iain Nicoll -Original Message- Sent: 06 December 2002 18:15 To: Multiple recipients of list ORACLE-L Tom, Actually it returns 1 record. Thanks Rick Mercadante, Thomas F To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] [EMAIL PROTECTED] cc: ate.ny.us Subject: RE: sql tuning help Sent by: [EMAIL PROTECTED] 12/06/2002 11:41 AM Please respond to ORACLE-L Rick, does this return *any* records at all? the only reason that I ask is that in the 'where' clause, it is saying: where p.phy_id = board_aaps.phy_id (+) and p.phy_id = board_aba.phy_id (+) and p.phy_id = board_abem.phy_id (+) and p.phy_id = board_abfp.phy_id (+) and p.phy_id = board_abim.phy_id (+) and p.phy_id = board_abp.phy_id (+) and p.phy_id = board_abr.phy_id (+) and p.phy_id = board_aobem.phy_id (+) and p.phy_id = board_aobfp.phy_id (+) and p.phy_id = board_aobim.phy_id (+) and p.phy_id = board_aobr.phy_id (+) and p.phy_id = board_other.phy_id (+) and p.phy_id = 1870; well, from my way of thinking, a single record from p.phy_id cannot be equal to all of the others at the same time. so no records should be returned at all. I think I would re-wright the entire mess using a set of UNIONS to accomplish the same thing. something like: select board_other.description strBrdNameOtherTHQuest ,decode(board_aaps.description, null,' ','X') ysnAAPSBoard ,decode(board_aba.description, null,' ','X') ysnABABoard ,decode(board_abem.description, null,' ','X') ysnABEMBoard ,decode(board_abfp.description, null,' ','X') ysnABFPoard ,decode(board_abim.description, null,' ','X') ysnABIMBoard ,decode(board_abp.description, null,' ','X') ysnABPBoard ,decode(board_abr.description, null,' ','X') ysnABRBoard ,decode(board_aobem.description, null,' ','X') ysnAOBEMBoard ,decode(board_aobfp.description, null,' ','X') ysnAOBFPBoard ,decode(board_aobim.description, null,' ','X') ysnAOBIMBAoard ,decode(board_aobr.description, null,' ','X') ysnAOBRBoard ,decode(board_other.description, null,' ','X') ysnOtherBoard from phy_boards pb, boards b, physicians p where p.phy_id = pb.phy_id and pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN ASSOCIATION OF PHYSICIAN SPECIALIST%' union select board_other.description strBrdNameOtherTHQuest ,decode(board_aaps.description, null,' ','X') ysnAAPSBoard ,decode(board_aba.description, null,' ','X') ysnABABoard ,decode(board_abem.description, null,' ','X') ysnABEMBoard ,decode(board_abfp.description, null,' ','X') ysnABFPoard ,decode(board_abim.description, null,' ','X') ysnABIMBoard ,decode(board_abp.description, null,' ','X') ysnABPBoard ,decode(board_abr.description, null,' ','X') ysnABRBoard ,decode(board_aobem.description, null,' ','X') ysnAOBEMBoard ,decode(board_aobfp.description, null,' ','X') ysnAOBFPBoard ,decode(board_aobim.description, null,' ','X') ysnAOBIMBAoard ,decode(board_aobr.description, null,' ','X') ysnAOBRBoard ,decode(board_other.description, null,' ','X') ysnOtherBoard from phy_boards pb, boards b, physicians p where p.phy_id = pb.phy_id and pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN BOARD OF ANESTHESIOLOGY%' etc. / Does this make sense? Hope this helps. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Friday, December 06, 2002 7:54 AM To: Multiple recipients of list ORACLE-L Hi, Oracle 8.1.6 NT 4.0 I have a rather complex query a developer gave to me to try to improve performance. There are 3 tables used. All relevant columns used are indexed. The tables have been analyzed SQLWKS select count(*) from physicians; COUNT(*) -- 340043 1 row selected. SQLWKS select count(*) from boards; COUNT(*) -- 220 1 row selected. SQLWKS select count(*) from phy_boards; COUNT(*) -- 450674 Below is the sql statement and explain plan. I see one FTS on 440,000+ records but cannot tell exactly what statement it is and how to resolve Any suggestions
Re: sql tuning help
My first thought would be that the plan shows a full table scan for phy_boards for each in-line view. This is almost certainly due to the 'pb.expiration_Date is null' condition in the where clause of each in-line view. Since Oracle does not store NULLs in an index (except for bitmaps), that condition requires a full table scan. -Mark On Fri, 2002-12-06 at 07:53, [EMAIL PROTECTED] wrote: Hi, Oracle 8.1.6 NT 4.0 I have a rather complex query a developer gave to me to try to improve performance. There are 3 tables used. All relevant columns used are indexed. The tables have been analyzed SQLWKS select count(*) from physicians; COUNT(*) -- 340043 1 row selected. SQLWKS select count(*) from boards; COUNT(*) -- 220 1 row selected. SQLWKS select count(*) from phy_boards; COUNT(*) -- 450674 Below is the sql statement and explain plan. I see one FTS on 440,000+ records but cannot tell exactly what statement it is and how to resolve Any suggestions on how to optimize is appreciated. Thanks Rick select board_other.description strBrdNameOtherTHQuest ,decode(board_aaps.description, null,' ','X') ysnAAPSBoard ,decode(board_aba.description, null,' ','X') ysnABABoard ,decode(board_abem.description, null,' ','X') ysnABEMBoard ,decode(board_abfp.description, null,' ','X') ysnABFPoard ,decode(board_abim.description, null,' ','X') ysnABIMBoard ,decode(board_abp.description, null,' ','X') ysnABPBoard ,decode(board_abr.description, null,' ','X') ysnABRBoard ,decode(board_aobem.description, null,' ','X') ysnAOBEMBoard ,decode(board_aobfp.description, null,' ','X') ysnAOBFPBoard ,decode(board_aobim.description, null,' ','X') ysnAOBIMBAoard ,decode(board_aobr.description, null,' ','X') ysnAOBRBoard ,decode(board_other.description, null,' ','X') ysnOtherBoard from physicians p ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN ASSOCIATION OF PHYSICIAN SPECIALIST%') board_aaps ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN BOARD OF ANESTHESIOLOGY%') board_aba ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN BOARD OF EMERGENCY MEDICINE%') board_abem ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN BOARD OF FAMILY PRACTICE%') board_abfp ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN BOARD OF INTERNAL MEDICINE%') board_abim ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN BOARD OF PEDIATRICS%') board_abp ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN BOARD OF RADIOLOGY%') board_abr ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN OSTEOPATHIC BOARD OF EMERGENCY MEDICINE%') board_aobem ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN OSTEOPATHIC BOARD OF FAMILY PHYSICIANS%') board_aobfp ,(select distinct pb.phy_id, b.name, b.description from
RE: sql tuning help
Hmmm - this is a Friday afternoon, you know. My suggestion is to forget it until Monday - don't spoil your weekend peter edinburgh -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: 06 December 2002 12:54 To: Multiple recipients of list ORACLE-L Subject: sql tuning help Hi, Oracle 8.1.6 NT 4.0 I have a rather complex query a developer gave to me to try to improve performance. There are 3 tables used. All relevant columns used are indexed. The tables have been analyzed SQLWKS select count(*) from physicians; COUNT(*) -- 340043 1 row selected. SQLWKS select count(*) from boards; COUNT(*) -- 220 1 row selected. SQLWKS select count(*) from phy_boards; COUNT(*) -- 450674 Below is the sql statement and explain plan. I see one FTS on 440,000+ records but cannot tell exactly what statement it is and how to resolve Any suggestions on how to optimize is appreciated. Thanks Rick select board_other.description strBrdNameOtherTHQuest ,decode(board_aaps.description, null,' ','X') ysnAAPSBoard ,decode(board_aba.description, null,' ','X') ysnABABoard ,decode(board_abem.description, null,' ','X') ysnABEMBoard ,decode(board_abfp.description, null,' ','X') ysnABFPoard ,decode(board_abim.description, null,' ','X') ysnABIMBoard ,decode(board_abp.description, null,' ','X') ysnABPBoard ,decode(board_abr.description, null,' ','X') ysnABRBoard ,decode(board_aobem.description, null,' ','X') ysnAOBEMBoard ,decode(board_aobfp.description, null,' ','X') ysnAOBFPBoard ,decode(board_aobim.description, null,' ','X') ysnAOBIMBAoard ,decode(board_aobr.description, null,' ','X') ysnAOBRBoard ,decode(board_other.description, null,' ','X') ysnOtherBoard from physicians p ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN ASSOCIATION OF PHYSICIAN SPECIALIST%') board_aaps ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN BOARD OF ANESTHESIOLOGY%') board_aba ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN BOARD OF EMERGENCY MEDICINE%') board_abem ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN BOARD OF FAMILY PRACTICE%') board_abfp ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN BOARD OF INTERNAL MEDICINE%') board_abim ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN BOARD OF PEDIATRICS%') board_abp ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN BOARD OF RADIOLOGY%') board_abr ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN OSTEOPATHIC BOARD OF EMERGENCY MEDICINE%') board_aobem ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN OSTEOPATHIC BOARD OF FAMILY PHYSICIANS%') board_aobfp ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id =
Re: sql tuning help
|BOARD_NAM | 3 | 207 | | TABLE ACCESS BY INDEX ROWID |PHY_BOARD | 443K| 5M| | INDEX RANGE SCAN |PBRD_BOAR | 443K| | | VIEW| | 8K| 922K| | SORT UNIQUE| | 8K| 649K| |NESTED LOOPS | | 8K| 649K| | INDEX FAST FULL SCAN |BOARD_NAM | 3 | 207 | | TABLE ACCESS BY INDEX ROWID |PHY_BOARD | 443K| 5M| | INDEX RANGE SCAN|PBRD_BOAR | 443K| | | VIEW | | 8K| 922K| | SORT UNIQUE | | 8K| 649K| | NESTED LOOPS | | 8K| 649K| |INDEX FAST FULL SCAN |BOARD_NAM | 3 | 207 | |TABLE ACCESS BY INDEX ROWID |PHY_BOARD | 443K| 5M| | INDEX RANGE SCAN |PBRD_BOAR | 443K| | |VIEW | | 8K| 922K| | SORT UNIQUE | | 8K| 649K| | NESTED LOOPS| | 8K| 649K| | INDEX FAST FULL SCAN |BOARD_NAM | 3 | 207 | | TABLE ACCESS BY INDEX ROWID|PHY_BOARD | 443K| 5M| |INDEX RANGE SCAN |PBRD_BOAR | 443K| | | VIEW | | 443K| 48M| |SORT UNIQUE | | 443K| 34M| | HASH JOIN| | 443K| 34M| | INDEX FAST FULL SCAN|BOARD_NAM | 190 | 12K| | TABLE ACCESS FULL |PHY_BOARD | 443K| 5M| Rick Mark J. Bobak To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] [EMAIL PROTECTED] cc: t Subject: Re: sql tuning help Sent by: [EMAIL PROTECTED] om 12/06/2002 10:19 AM Please respond to ORACLE-L My first thought would be that the plan shows a full table scan for phy_boards for each in-line view. This is almost certainly due to the 'pb.expiration_Date is null' condition in the where clause of each in-line view. Since Oracle does not store NULLs in an index (except for bitmaps), that condition requires a full table scan. -Mark On Fri, 2002-12-06 at 07:53, [EMAIL PROTECTED] wrote: Hi, Oracle 8.1.6 NT 4.0 I have a rather complex query a developer gave to me to try to improve performance. There are 3 tables used. All relevant columns used are indexed. The tables have been analyzed SQLWKS select count(*) from physicians; COUNT(*) -- 340043 1 row selected. SQLWKS select count(*) from boards; COUNT(*) -- 220 1 row selected. SQLWKS select count(*) from phy_boards; COUNT(*) -- 450674 Below is the sql statement and explain plan. I see one FTS on 440,000+ records but cannot tell exactly what statement it is and how to resolve Any suggestions on how to optimize is appreciated. Thanks Rick select board_other.description strBrdNameOtherTHQuest ,decode(board_aaps.description, null,' ','X') ysnAAPSBoard ,decode
RE: sql tuning help
Title: RE: sql tuning help Something like this might help ... SELECT p.phy_id ,CASE WHEN (b.description LIKE 'AMERICAN ASSOCIATION OF PHYSICIAN SPECIALIST%' AND cnt 0 ) THEN 'X' ELSE ' ' END ,CASE WHEN (b.description LIKE 'AMERICAN BOARD OF ANESTHESIOLOGY%' AND cnt 0 ) THEN 'X' ELSE ' ' END ,CASE WHEN (b.description LIKE 'AMERICAN BOARD OF EMERGENCY MEDICINE%' AND cnt 0 ) THEN 'X' ELSE ' ' END ,CASE WHEN (b.description LIKE 'AMERICAN BOARD OF FAMILY PRACTICE%' AND cnt 0 ) THEN 'X' ELSE ' ' END ,CASE WHEN (b.description LIKE 'AMERICAN BOARD OF INTERNAL MEDICINE%' AND cnt 0 ) THEN 'X' ELSE ' ' END ,CASE WHEN (b.description LIKE 'AMERICAN BOARD OF PEDIATRICS%' AND cnt 0 ) THEN 'X' ELSE ' ' END ,CASE WHEN (b.description LIKE 'AMERICAN BOARD OF RADIOLOGY%' AND cnt 0 ) THEN 'X' ELSE ' ' END ,CASE WHEN (b.description LIKE 'AMERICAN OSTEOPATHIC BOARD OF EMERGENCY MEDICINE%' AND cnt 0 ) THEN 'X' ELSE ' ' END ,CASE WHEN (b.description LIKE 'AMERICAN OSTEOPATHIC BOARD OF FAMILY PHYSICIANS%' AND cnt 0 ) THEN 'X' ELSE ' ' END ,CASE WHEN (b.description LIKE 'AMERICAN OSTEOPATHIC BOARD OF INTERNAL MEDICINE%' AND cnt 0 ) THEN 'X' ELSE ' ' END ,CASE WHEN (b.description LIKE 'AMERICAN OSTEOPATHIC BOARD OF RADIOLOGY%' AND cnt 0 ) THEN 'X' ELSE ' ' END ,CASE WHEN (b.description LIKE 'NO BOARDS%' ) THEN 'X' ELSE ' ' END FROM( SELECT p.phy_id ,b.description ,pb.COUNT(board_id) cnt FROM physicians p ,phy_boards pb ,boards b WHERE p.phy_id = 1870 AND NVL(pb_expiration_date, SYSDATE+1) = SYSDATE GROUP BY p.phy_id, b.description ) / I haven't tested this ... buit should be pretty close. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Friday, December 06, 2002 7:54 AM To: Multiple recipients of list ORACLE-L Subject: sql tuning help Hi, Oracle 8.1.6 NT 4.0 I have a rather complex query a developer gave to me to try to improve performance. There are 3 tables used. All relevant columns used are indexed. The tables have been analyzed SQLWKS select count(*) from physicians; COUNT(*) -- 340043 1 row selected. SQLWKS select count(*) from boards; COUNT(*) -- 220 1 row selected. SQLWKS select count(*) from phy_boards; COUNT(*) -- 450674 Below is the sql statement and explain plan. I see one FTS on 440,000+ records but cannot tell exactly what statement it is and how to resolve Any suggestions on how to optimize is appreciated. Thanks Rick select board_other.description strBrdNameOtherTHQuest ,decode(board_aaps.description, null,' ','X') ysnAAPSBoard ,decode(board_aba.description, null,' ','X') ysnABABoard ,decode(board_abem.description, null,' ','X') ysnABEMBoard ,decode(board_abfp.description, null,' ','X') ysnABFPoard ,decode(board_abim.description, null,' ','X') ysnABIMBoard ,decode(board_abp.description, null,' ','X') ysnABPBoard ,decode(board_abr.description, null,' ','X') ysnABRBoard ,decode(board_aobem.description, null,' ','X') ysnAOBEMBoard ,decode(board_aobfp.description, null,' ','X') ysnAOBFPBoard ,decode(board_aobim.description, null,' ','X') ysnAOBIMBAoard ,decode(board_aobr.description, null,' ','X') ysnAOBRBoard ,decode(board_other.description, null,' ','X') ysnOtherBoard from physicians p ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN ASSOCIATION OF PHYSICIAN SPECIALIST%') board_aaps ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN BOARD OF ANESTHESIOLOGY%') board_aba ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN BOARD OF EMERGENCY MEDICINE%') board_abem ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN BOARD OF FAMILY PRACTICE%') board_abfp ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN BOARD OF INTERNAL MEDICINE%') board_abim ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date
RE: sql tuning help
I think the problem (without really getting into how they coded this) is the nested-nested loops. try this hint: /*+ no_merge use_hash(board_aaps) use_hash(board_aba) use_hash(board_abem) use_hash(board_abfp) use_hash(board_abim) use_hash(board_abp) use_hash(board_abr) use_hash(board_aobem) use_hash(board_aobfp) use_hash(board_aobim) use_hash(board_aobr) use_hash(board_other) */ HTH Waleed -Original Message- Sent: Friday, December 06, 2002 7:54 AM To: Multiple recipients of list ORACLE-L Hi, Oracle 8.1.6 NT 4.0 I have a rather complex query a developer gave to me to try to improve performance. There are 3 tables used. All relevant columns used are indexed. The tables have been analyzed SQLWKS select count(*) from physicians; COUNT(*) -- 340043 1 row selected. SQLWKS select count(*) from boards; COUNT(*) -- 220 1 row selected. SQLWKS select count(*) from phy_boards; COUNT(*) -- 450674 Below is the sql statement and explain plan. I see one FTS on 440,000+ records but cannot tell exactly what statement it is and how to resolve Any suggestions on how to optimize is appreciated. Thanks Rick select board_other.description strBrdNameOtherTHQuest ,decode(board_aaps.description, null,' ','X') ysnAAPSBoard ,decode(board_aba.description, null,' ','X') ysnABABoard ,decode(board_abem.description, null,' ','X') ysnABEMBoard ,decode(board_abfp.description, null,' ','X') ysnABFPoard ,decode(board_abim.description, null,' ','X') ysnABIMBoard ,decode(board_abp.description, null,' ','X') ysnABPBoard ,decode(board_abr.description, null,' ','X') ysnABRBoard ,decode(board_aobem.description, null,' ','X') ysnAOBEMBoard ,decode(board_aobfp.description, null,' ','X') ysnAOBFPBoard ,decode(board_aobim.description, null,' ','X') ysnAOBIMBAoard ,decode(board_aobr.description, null,' ','X') ysnAOBRBoard ,decode(board_other.description, null,' ','X') ysnOtherBoard from physicians p ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN ASSOCIATION OF PHYSICIAN SPECIALIST%') board_aaps ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN BOARD OF ANESTHESIOLOGY%') board_aba ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN BOARD OF EMERGENCY MEDICINE%') board_abem ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN BOARD OF FAMILY PRACTICE%') board_abfp ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN BOARD OF INTERNAL MEDICINE%') board_abim ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN BOARD OF PEDIATRICS%') board_abp ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN BOARD OF RADIOLOGY%') board_abr ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN OSTEOPATHIC BOARD OF EMERGENCY MEDICINE%') board_aobem ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN OSTEOPATHIC BOARD OF FAMILY PHYSICIANS%') board_aobfp ,(select distinct pb.phy_id, b.name, b.description from
RE: sql tuning help
Rick, does this return *any* records at all? the only reason that I ask is that in the 'where' clause, it is saying: where p.phy_id = board_aaps.phy_id (+) and p.phy_id = board_aba.phy_id (+) and p.phy_id = board_abem.phy_id (+) and p.phy_id = board_abfp.phy_id (+) and p.phy_id = board_abim.phy_id (+) and p.phy_id = board_abp.phy_id (+) and p.phy_id = board_abr.phy_id (+) and p.phy_id = board_aobem.phy_id (+) and p.phy_id = board_aobfp.phy_id (+) and p.phy_id = board_aobim.phy_id (+) and p.phy_id = board_aobr.phy_id (+) and p.phy_id = board_other.phy_id (+) and p.phy_id = 1870; well, from my way of thinking, a single record from p.phy_id cannot be equal to all of the others at the same time. so no records should be returned at all. I think I would re-wright the entire mess using a set of UNIONS to accomplish the same thing. something like: select board_other.description strBrdNameOtherTHQuest ,decode(board_aaps.description, null,' ','X') ysnAAPSBoard ,decode(board_aba.description, null,' ','X') ysnABABoard ,decode(board_abem.description, null,' ','X') ysnABEMBoard ,decode(board_abfp.description, null,' ','X') ysnABFPoard ,decode(board_abim.description, null,' ','X') ysnABIMBoard ,decode(board_abp.description, null,' ','X') ysnABPBoard ,decode(board_abr.description, null,' ','X') ysnABRBoard ,decode(board_aobem.description, null,' ','X') ysnAOBEMBoard ,decode(board_aobfp.description, null,' ','X') ysnAOBFPBoard ,decode(board_aobim.description, null,' ','X') ysnAOBIMBAoard ,decode(board_aobr.description, null,' ','X') ysnAOBRBoard ,decode(board_other.description, null,' ','X') ysnOtherBoard from phy_boards pb, boards b, physicians p where p.phy_id = pb.phy_id and pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN ASSOCIATION OF PHYSICIAN SPECIALIST%' union select board_other.description strBrdNameOtherTHQuest ,decode(board_aaps.description, null,' ','X') ysnAAPSBoard ,decode(board_aba.description, null,' ','X') ysnABABoard ,decode(board_abem.description, null,' ','X') ysnABEMBoard ,decode(board_abfp.description, null,' ','X') ysnABFPoard ,decode(board_abim.description, null,' ','X') ysnABIMBoard ,decode(board_abp.description, null,' ','X') ysnABPBoard ,decode(board_abr.description, null,' ','X') ysnABRBoard ,decode(board_aobem.description, null,' ','X') ysnAOBEMBoard ,decode(board_aobfp.description, null,' ','X') ysnAOBFPBoard ,decode(board_aobim.description, null,' ','X') ysnAOBIMBAoard ,decode(board_aobr.description, null,' ','X') ysnAOBRBoard ,decode(board_other.description, null,' ','X') ysnOtherBoard from phy_boards pb, boards b, physicians p where p.phy_id = pb.phy_id and pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN BOARD OF ANESTHESIOLOGY%' etc. / Does this make sense? Hope this helps. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Friday, December 06, 2002 7:54 AM To: Multiple recipients of list ORACLE-L Hi, Oracle 8.1.6 NT 4.0 I have a rather complex query a developer gave to me to try to improve performance. There are 3 tables used. All relevant columns used are indexed. The tables have been analyzed SQLWKS select count(*) from physicians; COUNT(*) -- 340043 1 row selected. SQLWKS select count(*) from boards; COUNT(*) -- 220 1 row selected. SQLWKS select count(*) from phy_boards; COUNT(*) -- 450674 Below is the sql statement and explain plan. I see one FTS on 440,000+ records but cannot tell exactly what statement it is and how to resolve Any suggestions on how to optimize is appreciated. Thanks Rick select board_other.description strBrdNameOtherTHQuest ,decode(board_aaps.description, null,' ','X') ysnAAPSBoard ,decode(board_aba.description, null,' ','X') ysnABABoard ,decode(board_abem.description, null,' ','X') ysnABEMBoard ,decode(board_abfp.description, null,' ','X') ysnABFPoard ,decode(board_abim.description, null,' ','X') ysnABIMBoard ,decode(board_abp.description, null,' ','X') ysnABPBoard ,decode(board_abr.description, null,' ','X') ysnABRBoard ,decode(board_aobem.description, null,' ','X') ysnAOBEMBoard ,decode(board_aobfp.description, null,' ','X') ysnAOBFPBoard ,decode(board_aobim.description, null,' ','X') ysnAOBIMBAoard ,decode(board_aobr.description, null,' ','X') ysnAOBRBoard ,decode(board_other.description, null,' ','X') ysnOtherBoard from physicians p ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id
RE: sql tuning help
Title: RE: sql tuning help That is very wise advice. Don't touch production on Fridays has been a rule in previous shops I worked at. Happy Friday all! pow Lisa Koivu Oracle Database Supermom to 4 Boys. Fairfield Resorts, Inc. 5259 Coconut Creek Parkway Ft. Lauderdale, FL, USA 33063 -Original Message- From: Robson, Peter [SMTP:[EMAIL PROTECTED]] Sent: Friday, December 06, 2002 10:30 AM To: Multiple recipients of list ORACLE-L Subject: RE: sql tuning help Hmmm - this is a Friday afternoon, you know. My suggestion is to forget it until Monday - don't spoil your weekend peter edinburgh -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: 06 December 2002 12:54 To: Multiple recipients of list ORACLE-L Subject: sql tuning help Hi, Oracle 8.1.6 NT 4.0 I have a rather complex query a developer gave to me to try to improve performance. There are 3 tables used. All relevant columns used are indexed. The tables have been analyzed SQLWKS select count(*) from physicians; COUNT(*) -- 340043 1 row selected. SQLWKS select count(*) from boards; COUNT(*) -- 220 1 row selected. SQLWKS select count(*) from phy_boards; COUNT(*) -- 450674 Below is the sql statement and explain plan. I see one FTS on 440,000+ records but cannot tell exactly what statement it is and how to resolve Any suggestions on how to optimize is appreciated. Thanks Rick select board_other.description strBrdNameOtherTHQuest ,decode(board_aaps.description, null,' ','X') ysnAAPSBoard ,decode(board_aba.description, null,' ','X') ysnABABoard ,decode(board_abem.description, null,' ','X') ysnABEMBoard ,decode(board_abfp.description, null,' ','X') ysnABFPoard ,decode(board_abim.description, null,' ','X') ysnABIMBoard ,decode(board_abp.description, null,' ','X') ysnABPBoard ,decode(board_abr.description, null,' ','X') ysnABRBoard ,decode(board_aobem.description, null,' ','X') ysnAOBEMBoard ,decode(board_aobfp.description, null,' ','X') ysnAOBFPBoard ,decode(board_aobim.description, null,' ','X') ysnAOBIMBAoard ,decode(board_aobr.description, null,' ','X') ysnAOBRBoard ,decode(board_other.description, null,' ','X') ysnOtherBoard from physicians p ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN ASSOCIATION OF PHYSICIAN SPECIALIST%') board_aaps ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN BOARD OF ANESTHESIOLOGY%') board_aba ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN BOARD OF EMERGENCY MEDICINE%') board_abem ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN BOARD OF FAMILY PRACTICE%') board_abfp ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN BOARD OF INTERNAL MEDICINE%') board_abim ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN BOARD OF PEDIATRICS%') board_abp ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN BOARD OF RADIOLOGY%') board_abr ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN OSTEOPATHIC BOARD OF EMERGENCY MEDICINE%') board_aobem ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN OSTEOPATHIC BOARD OF FAMILY PHYSICIANS%') board_aobfp ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN OSTEOPATHIC BOARD OF INTERNAL MEDICINE%') board_aobim ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id
RE: sql tuning help
I expand that rule slightly: no changes to production on the day before I will be absent from the office. Makes for so many fewer emergency phone calls on my day off --- Koivu, Lisa [EMAIL PROTECTED] wrote: That is very wise advice. Don't touch production on Fridays has been a rule in previous shops I worked at. Happy Friday all! pow Lisa Koivu Oracle Database Supermom to 4 Boys. Fairfield Resorts, Inc. 5259 Coconut Creek Parkway Ft. Lauderdale, FL, USA 33063 -Original Message- From: Robson, Peter [SMTP:[EMAIL PROTECTED]] Sent: Friday, December 06, 2002 10:30 AM To: Multiple recipients of list ORACLE-L Subject:RE: sql tuning help Hmmm - this is a Friday afternoon, you know. My suggestion is to forget it until Monday - don't spoil your weekend peter edinburgh -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: 06 December 2002 12:54 To: Multiple recipients of list ORACLE-L Subject: sql tuning help Hi, Oracle 8.1.6 NT 4.0 I have a rather complex query a developer gave to me to try to improve performance. There are 3 tables used. All relevant columns used are indexed. The tables have been analyzed SQLWKS select count(*) from physicians; COUNT(*) -- 340043 1 row selected. SQLWKS select count(*) from boards; COUNT(*) -- 220 1 row selected. SQLWKS select count(*) from phy_boards; COUNT(*) -- 450674 Below is the sql statement and explain plan. I see one FTS on 440,000+ records but cannot tell exactly what statement it is and how to resolve Any suggestions on how to optimize is appreciated. Thanks Rick select board_other.description strBrdNameOtherTHQuest ,decode(board_aaps.description, null,' ','X') ysnAAPSBoard ,decode(board_aba.description, null,' ','X') ysnABABoard ,decode(board_abem.description, null,' ','X') ysnABEMBoard ,decode(board_abfp.description, null,' ','X') ysnABFPoard ,decode(board_abim.description, null,' ','X') ysnABIMBoard ,decode(board_abp.description, null,' ','X') ysnABPBoard ,decode(board_abr.description, null,' ','X') ysnABRBoard ,decode(board_aobem.description, null,' ','X') ysnAOBEMBoard ,decode(board_aobfp.description, null,' ','X') ysnAOBFPBoard ,decode(board_aobim.description, null,' ','X') ysnAOBIMBAoard ,decode(board_aobr.description, null,' ','X') ysnAOBRBoard ,decode(board_other.description, null,' ','X') ysnOtherBoard from physicians p ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN ASSOCIATION OF PHYSICIAN SPECIALIST%') board_aaps ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN BOARD OF ANESTHESIOLOGY%') board_aba ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN BOARD OF EMERGENCY MEDICINE%') board_abem ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN BOARD OF FAMILY PRACTICE%') board_abfp ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN BOARD OF INTERNAL MEDICINE%') board_abim ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN BOARD OF PEDIATRICS%') board_abp ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate
RE: sql tuning help
| | INDEX FAST FULL SCAN |BOARD_NAM | 3 | 207 | | TABLE ACCESS BY INDEX ROWID|PHY_BOARD | 443K| 5M| |INDEX RANGE SCAN |PBRD_BOAR | 443K| | | VIEW | | 8K| 922K| |SORT UNIQUE | | 8K| 649K| | NESTED LOOPS | | 8K| 649K| | INDEX FAST FULL SCAN|BOARD_NAM | 3 | 207 | | TABLE ACCESS BY INDEX ROWID |PHY_BOARD | 443K| 5M| | INDEX RANGE SCAN |PBRD_BOAR | 443K| | | VIEW| | 8K| 922K| | SORT UNIQUE| | 8K| 649K| |NESTED LOOPS | | 8K| 649K| | INDEX FAST FULL SCAN |BOARD_NAM | 3 | 207 | | TABLE ACCESS BY INDEX ROWID |PHY_BOARD | 443K| 5M| | INDEX RANGE SCAN|PBRD_BOAR | 443K| | | VIEW | | 8K| 922K| | SORT UNIQUE | | 8K| 649K| | NESTED LOOPS | | 8K| 649K| |INDEX FAST FULL SCAN |BOARD_NAM | 3 | 207 | |TABLE ACCESS BY INDEX ROWID |PHY_BOARD | 443K| 5M| | INDEX RANGE SCAN |PBRD_BOAR | 443K| | |VIEW | | 8K| 922K| | SORT UNIQUE | | 8K| 649K| | NESTED LOOPS| | 8K| 649K| | INDEX FAST FULL SCAN |BOARD_NAM | 3 | 207 | | TABLE ACCESS BY INDEX ROWID|PHY_BOARD | 443K| 5M| |INDEX RANGE SCAN |PBRD_BOAR | 443K| | | VIEW | | 443K| 48M| |SORT UNIQUE | | 443K| 34M| | HASH JOIN| | 443K| 34M| | INDEX FAST FULL SCAN|BOARD_NAM | 190 | 12K| | TABLE ACCESS FULL |PHY_BOARD | 443K| 5M| Rick Mark J. Bobak To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] [EMAIL PROTECTED] cc: t Subject: Re: sql tuning help Sent by: [EMAIL PROTECTED] om 12/06/2002 10:19 AM Please respond to ORACLE-L My first thought would be that the plan shows a full table scan for phy_boards for each in-line view. This is almost certainly due to the 'pb.expiration_Date is null' condition in the where clause of each in-line view. Since Oracle does not store NULLs in an index (except for bitmaps), that condition requires a full table scan. -Mark On Fri, 2002-12-06 at 07:53, [EMAIL PROTECTED] wrote: Hi, Oracle 8.1.6 NT 4.0 I have a rather complex query a developer gave to me to try to improve performance. There are 3 tables used. All relevant columns used are indexed. The tables have been analyzed SQLWKS select count(*) from physicians; COUNT(*) -- 340043 1 row selected. SQLWKS select count(*) from boards; COUNT(*) -- 220 1 row selected. SQLWKS select count(*) from phy_boards; COUNT(*) -- 450674 Below is the sql statement and explain plan. I see one FTS on 440,000+ records but cannot tell exactly what statement it is and how to resolve Any suggestions on how to optimize is appreciated. Thanks Rick select board_other.description strBrdNameOtherTHQuest ,decode(board_aaps.description, null,' ','X') ysnAAPSBoard ,decode(board_aba.description, null,' ','X') ysnABABoard ,decode(board_abem.description, null,' ','X') ysnABEMBoard ,decode(board_abfp.description, null,' ','X') ysnABFPoard ,decode(board_abim.description, null,' ','X') ysnABIMBoard ,decode(board_abp.description, null,' ','X') ysnABPBoard ,decode(board_abr.description, null,' ','X') ysnABRBoard ,decode(board_aobem.description, null,' ','X') ysnAOBEMBoard ,decode(board_aobfp.description, null,' ','X') ysnAOBFPBoard ,decode(board_aobim.description, null,' ','X') ysnAOBIMBAoard ,decode(board_aobr.description, null,' ','X') ysnAOBRBoard ,decode(board_other.description, null,' ','X') ysnOtherBoard from physicians p ,(select distinct pb.phy_id, b.name, b.description
RE: sql tuning help
Title: RE: sql tuning help bad news for me I guess - doing a Production Install right now! -Original Message-From: Koivu, Lisa [mailto:[EMAIL PROTECTED]]Sent: Friday, December 06, 2002 11:29 AMTo: Multiple recipients of list ORACLE-LSubject: RE: sql tuning help That is very wise advice. Don't touch production on Fridays has been a rule in previous shops I worked at. Happy Friday all! pow Lisa Koivu Oracle Database Supermom to 4 Boys. Fairfield Resorts, Inc. 5259 Coconut Creek Parkway Ft. Lauderdale, FL, USA 33063 -Original Message- From: Robson, Peter [SMTP:[EMAIL PROTECTED]] Sent: Friday, December 06, 2002 10:30 AM To: Multiple recipients of list ORACLE-L Subject: RE: sql tuning help Hmmm - this is a Friday afternoon, you know. My suggestion is to forget it until Monday - don't spoil your weekend peter edinburgh -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: 06 December 2002 12:54 To: Multiple recipients of list ORACLE-L Subject: sql tuning helpHi, Oracle 8.1.6 NT 4.0 I have a rather complex query a developer gave to me to try to improve performance. There are 3 tables used. All relevant columns used are indexed. The tables have been analyzed SQLWKS select count(*) from physicians; COUNT(*) -- 340043 1 row selected. SQLWKS select count(*) from boards; COUNT(*) -- 220 1 row selected. SQLWKS select count(*) from phy_boards; COUNT(*) -- 450674 Below is the sql statement and explain plan. I see one FTS on 440,000+ records but cannot tell exactly what statement it is and how to resolve Any suggestions on how to optimize is appreciated. Thanks Rick select board_other.description strBrdNameOtherTHQuest ,decode(board_aaps.description, null,' ','X') ysnAAPSBoard ,decode(board_aba.description, null,' ','X') ysnABABoard ,decode(board_abem.description, null,' ','X') ysnABEMBoard ,decode(board_abfp.description, null,' ','X') ysnABFPoard ,decode(board_abim.description, null,' ','X') ysnABIMBoard ,decode(board_abp.description, null,' ','X') ysnABPBoard ,decode(board_abr.description, null,' ','X') ysnABRBoard ,decode(board_aobem.description, null,' ','X') ysnAOBEMBoard ,decode(board_aobfp.description, null,' ','X') ysnAOBFPBoard ,decode(board_aobim.description, null,' ','X') ysnAOBIMBAoard ,decode(board_aobr.description, null,' ','X') ysnAOBRBoard ,decode(board_other.description, null,' ','X') ysnOtherBoard from physicians p ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN ASSOCIATION OF PHYSICIAN SPECIALIST%') board_aaps ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN BOARD OF ANESTHESIOLOGY%') board_aba ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN BOARD OF EMERGENCY MEDICINE%') board_abem ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN BOARD OF FAMILY PRACTICE%') board_abfp ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN BOARD OF INTERNAL MEDICINE%') board_abim ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN BOARD OF PEDIATRICS%') board_abp ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN BOARD OF RADIOLOGY%') board_abr ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null
RE: sql tuning help
Tom, Actually it returns 1 record. Thanks Rick Mercadante, Thomas F To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] [EMAIL PROTECTED] cc: ate.ny.us Subject: RE: sql tuning help Sent by: [EMAIL PROTECTED] 12/06/2002 11:41 AM Please respond to ORACLE-L Rick, does this return *any* records at all? the only reason that I ask is that in the 'where' clause, it is saying: where p.phy_id = board_aaps.phy_id (+) and p.phy_id = board_aba.phy_id (+) and p.phy_id = board_abem.phy_id (+) and p.phy_id = board_abfp.phy_id (+) and p.phy_id = board_abim.phy_id (+) and p.phy_id = board_abp.phy_id (+) and p.phy_id = board_abr.phy_id (+) and p.phy_id = board_aobem.phy_id (+) and p.phy_id = board_aobfp.phy_id (+) and p.phy_id = board_aobim.phy_id (+) and p.phy_id = board_aobr.phy_id (+) and p.phy_id = board_other.phy_id (+) and p.phy_id = 1870; well, from my way of thinking, a single record from p.phy_id cannot be equal to all of the others at the same time. so no records should be returned at all. I think I would re-wright the entire mess using a set of UNIONS to accomplish the same thing. something like: select board_other.description strBrdNameOtherTHQuest ,decode(board_aaps.description, null,' ','X') ysnAAPSBoard ,decode(board_aba.description, null,' ','X') ysnABABoard ,decode(board_abem.description, null,' ','X') ysnABEMBoard ,decode(board_abfp.description, null,' ','X') ysnABFPoard ,decode(board_abim.description, null,' ','X') ysnABIMBoard ,decode(board_abp.description, null,' ','X') ysnABPBoard ,decode(board_abr.description, null,' ','X') ysnABRBoard ,decode(board_aobem.description, null,' ','X') ysnAOBEMBoard ,decode(board_aobfp.description, null,' ','X') ysnAOBFPBoard ,decode(board_aobim.description, null,' ','X') ysnAOBIMBAoard ,decode(board_aobr.description, null,' ','X') ysnAOBRBoard ,decode(board_other.description, null,' ','X') ysnOtherBoard from phy_boards pb, boards b, physicians p where p.phy_id = pb.phy_id and pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN ASSOCIATION OF PHYSICIAN SPECIALIST%' union select board_other.description strBrdNameOtherTHQuest ,decode(board_aaps.description, null,' ','X') ysnAAPSBoard ,decode(board_aba.description, null,' ','X') ysnABABoard ,decode(board_abem.description, null,' ','X') ysnABEMBoard ,decode(board_abfp.description, null,' ','X') ysnABFPoard ,decode(board_abim.description, null,' ','X') ysnABIMBoard ,decode(board_abp.description, null,' ','X') ysnABPBoard ,decode(board_abr.description, null,' ','X') ysnABRBoard ,decode(board_aobem.description, null,' ','X') ysnAOBEMBoard ,decode(board_aobfp.description, null,' ','X') ysnAOBFPBoard ,decode(board_aobim.description, null,' ','X') ysnAOBIMBAoard ,decode(board_aobr.description, null,' ','X') ysnAOBRBoard ,decode(board_other.description, null,' ','X') ysnOtherBoard from phy_boards pb, boards b, physicians p where p.phy_id = pb.phy_id and pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN BOARD OF ANESTHESIOLOGY%' etc. / Does this make sense? Hope this helps. Tom Mercadante Oracle Certified
RE: SQL tuning help
Title: RE: SQL tuning help Sergei, How many records in each table? What indexes are in these tables? What version of Oracle? What do you mean by 'began to hang'? I'd try making the attempts in the WHERE clause into a Between. I'd also try grouping by f.subsite_id. You could always throw a Rule hint at it and see what happens. Jerry Whittle ACIFICS DBA NCI Information Systems Inc. [EMAIL PROTECTED] 618-622-4145 -Original Message- From: Sergei [SMTP:[EMAIL PROTECTED]] Hello everybody, I have the following query that runs every week. UPDATE tmp_brian_metareward1 tmp SET offers_seen = (SELECT count(f.fastcash_id) FROM metareward.fastcash f WHERE f.subsite_id = tmp.subsite_id and attempt = trunc(sysdate-1) and attempt trunc(sysdate) group by tmp.subsite_id); This week it began to hang and I can't figure out why. No changes were made to a database. Please advise me on how I can tune it, which hints to add, or anything else I can do. Thank you Sergei
Re: SQL tuning help
Sergei, When the query is running try to collect some stats, especially session waits, from v$session_wait and see where the waits are happening. Or you could do this from command line alter session set event '10046 trace name context forever, level 8'; your query alter session set event '10046 trace name context off'; This will produce a trace file in user_dump_dest directory. Tkprof that fiel to see the explain plans and all, see if everything is as per expectation. From the raw trace file you could see the wait events occuring and where they occur. My guess is you have seen buffer busy waits on most cases. Increase the initrans, maxtrans, freelist and freelist groups parameter of the indexes used in this query and rebuild them. This will alleviate several problems. Did someone chaneg the optimizer_goal? Did you have RULE before and CHOOSE now? If you use RULE, did someone analyzed any of the tables, including SYS owner tables? HTH Arup Nanda www.proligence.com - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, November 26, 2002 1:54 PM Hello everybody, I have the following query that runs every week. UPDATE tmp_brian_metareward1 tmp SET offers_seen = (SELECT count(f.fastcash_id) FROM metareward.fastcash f WHERE f.subsite_id = tmp.subsite_id and attempt = trunc(sysdate-1) and attempt trunc(sysdate) group by tmp.subsite_id); This week it began to hang and I can't figure out why. No changes were made to a database. Please advise me on how I can tune it, which hints to add, or anything else I can do. Thank you Sergei -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sergei 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.com -- Author: Arup Nanda 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).
Re: SQL tuning help
Remove the group by clause. It does nothing. Also, if the cardinality for subsite_id in the table tmp_brian_metareward1 is low, you may use a PL/SQL block instead of a single update statement. HTH, Krishna - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, November 26, 2002 10:54 AM Hello everybody, I have the following query that runs every week. UPDATE tmp_brian_metareward1 tmp SET offers_seen = (SELECT count(f.fastcash_id) FROM metareward.fastcash f WHERE f.subsite_id = tmp.subsite_id and attempt = trunc(sysdate-1) and attempt trunc(sysdate) group by tmp.subsite_id); This week it began to hang and I can't figure out why. No changes were made to a database. Please advise me on how I can tune it, which hints to add, or anything else I can do. Thank you Sergei -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Krishna Rao Kakatur 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).
Re: SQL tuning help
Sergei, By hang I'm going to assume that I can replace that with the phrase running really slow. If it is actually hanging then I think a call to Oracle is in order. I'm guessing that perhaps statistics were updated or one of the tables changed in size enough to convince the optimisor to take a different approach to the query. Try to get an explain plan for the query. Looking at the query I have a couple of other questions... 1) Since the subquery is updating a single row and joins to that row using subsite_id, I see no need for the group by clause - there is only 1 subsite_id that's going to appear. If there is something I don't understand here let me know - I have seen this type of query before and questioned its significance. 2) It looks like the ideal indexes are metareward.fastcash(subsite_id) and metareward.fastcash(attempt), or perhaps a concatenated index with subsite_id as the leading column. What indexes currently exist? Is the query using them (hence the need for an explain plan)? Have you analyzed the tables recently? Hopefully I have given you something to work from. To help you further we'd need the explain plan, the row counts and perhaps table/index statistics (details in user_tables and user_indexes, etc). It's difficult to suggest a hint without knowing further details. Finally how many different subsite_id's exist in fastcash and how many of those are you gathering details about? Perhaps a CTAS that calculates details for every subsite_id at once would be much faster if you are looking at most of the fastcash table anyway. Regards, Mark. Sergei sergei@netfli To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] p.com cc: Sent by: Subject: SQL tuning help [EMAIL PROTECTED] om 27/11/2002 05:54 Please respond to ORACLE-L Hello everybody, I have the following query that runs every week. UPDATE tmp_brian_metareward1 tmp SET offers_seen = (SELECT count(f.fastcash_id) FROM metareward.fastcash f WHERE f.subsite_id = tmp.subsite_id and attempt = trunc(sysdate-1) and attempt trunc(sysdate) group by tmp.subsite_id); This week it began to hang and I can't figure out why. No changes were made to a database. Please advise me on how I can tune it, which hints to add, or anything else I can do. Thank you Sergei -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sergei 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). Privileged/Confidential information may be contained in this message. If you are not the addressee indicated in this message (or responsible for delivery of the message to such person), you may not copy or deliver this message to anyone. In such case, you should destroy this message and kindly notify the sender by
RE: SQL tuning help
Title: RE: SQL tuning help Let me clearfy. I am running Oracle 8.1.6 on solaris 8 Fastcash has 50M record tmp_brian_metareward1 has 600 records. I was able to tune the query so it runs fast now. I created a combined index on subsite_id and attempt and I added a hint UPDATE tmp_brian_metareward1 tmp SET offers_seen = ( SELECT /*+ INDEX(f IN_FASTCASH_SIDATMP) */ count(f.fastcash_id) FROM metareward.fastcash f WHERE f.subsite_id = tmp.subsite_id and attempt = trunc(sysdate-1) and attempt trunc(sysdate) group by tmp.subsite_id); I am still having a problem with a similar query below: IN_FASTCASH_SIDFIDVER index is for subsite_id fastcash_id and verified UPDATE tmp_brian_metareward1 tmp SET revenue = (SELECT /*+ INDEX(f IN_FASTCASH_SIDFIDVER) */ sum(f.mr_amount)/100 FROM metareward.fastcash f, metareward.transaction_fastcash tf WHERE f.subsite_id = tmp.subsite_id and f.fastcash_id = tf.fastcash_id and f.verified = trunc(sysdate-1) and f.verified trunc(sysdate) group by tmp.subsite_id); transaction_fastcash table has 2.5M records fastcash table has 50M record Thank you Sergei -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of Whittle Jerome Contr NCI Sent: Tuesday, November 26, 2002 12:04 PM To: Multiple recipients of list ORACLE-L Subject: RE: SQL tuning help Sergei, How many records in each table? What indexes are in these tables? What version of Oracle? What do you mean by 'began to hang'? I'd try making the attempts in the WHERE clause into a Between. I'd also try grouping by f.subsite_id. You could always throw a Rule hint at it and see what happens. Jerry Whittle ACIFICS DBA NCI Information Systems Inc. [EMAIL PROTECTED] 618-622-4145 -Original Message- From: Sergei [SMTP:[EMAIL PROTECTED]] Hello everybody, I have the following query that runs every week. UPDATE tmp_brian_metareward1 tmp SET offers_seen = (SELECT count(f.fastcash_id) FROM metareward.fastcash f WHERE f.subsite_id = tmp.subsite_id and attempt = trunc(sysdate-1) and attempt trunc(sysdate) group by tmp.subsite_id); This week it began to hang and I can't figure out why. No changes were made to a database. Please advise me on how I can tune it, which hints to add, or anything else I can do. Thank you Sergei
RE: SQL tuning help
This week it began to hang and I can't figure out why. The first thing I would check are locks. The statement is trying to update a table. Try something like the following while the statement appears to be hung. These are two different ways (and certainly not the only ways) of checking for lockers and waiters. SELECT substr(s1.username,1,12)WAITING User, substr(s1.osuser,1,8)OS User, substr(to_char(w.session_id),1,5)Sid, P1.spid PID, substr(s2.username,1,12)HOLDING User, substr(s2.osuser,1,8)OS User, substr(to_char(h.session_id),1,5)Sid, P2.spid PID FROM sys.v_$process P1, sys.v_$process P2, sys.v_$session S1, sys.v_$session S2, sys.dba_lock w, sys.dba_lock h WHERE h.mode_held= 'None' ANDh.mode_held= 'Null' ANDw.mode_requested != 'None' ANDw.lock_type (+)= h.lock_type ANDw.lock_id1 (+)= h.lock_id1 ANDw.lock_id2 (+)= h.lock_id2 ANDw.session_id = S1.sid (+) ANDh.session_id = S2.sid (+) ANDS1.paddr = P1.addr (+) ANDS2.paddr = P2.addr (+) / - set lines 150 set pages 600 col mode_held for a12 col mode_requested for a12 select /*+ all_rows */ a.osuser waiter, nvl(b.osuser,'NOBODY') blocker, w.lock_type, h.mode_held, w.mode_requested -- w.lock_id1, w.lock_id2 from dba_locks w, dba_locks h, v$session a, v$session b where h.blocking_others = 'Blocking' and h.mode_held != 'None' and h.mode_held != 'Null' and w.mode_requested != 'None' and w.lock_type = h.lock_type and w.lock_id1 = h.lock_id1 and w.lock_id2 = h.lock_id2 and w.session_id in (select sid from v$session where last_call_et 100 and sid 10 and osuser is not null) and w.session_id = a.sid and h.session_id = b.sid; -- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephen Lee 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).
Re: SQL tuning help
check out the status in v$sess Regards, Sathyanarayanan |+--- || Sergei | || sergei@netfl| || ip.com | || | || 27/11/2002 | || 00:24| || Please | || respond to | || ORACLE-L | || | |+--- --| | | | To: Multiple recipients of list ORACLE-L | | [EMAIL PROTECTED] | | cc: (bcc: Sathyanaryanan K/VGIL) | | Subject: SQL tuning help | --| Hello everybody, I have the following query that runs every week. UPDATE tmp_brian_metareward1 tmp SET offers_seen = (SELECT count(f.fastcash_id) FROM metareward.fastcash f WHERE f.subsite_id = tmp.subsite_id and attempt = trunc(sysdate-1) and attempt trunc(sysdate) group by tmp.subsite_id); This week it began to hang and I can't figure out why. No changes were made to a database. Please advise me on how I can tune it, which hints to add, or anything else I can do. Thank you Sergei -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sergei 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.com -- Author: 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).
RE: SQL Tuning - How to avoid TOCHAR function against a date
Ron, That's an idea. Easy to implement and test. I'll give it a try tonight to see if it helps. It is a small table. Cherie Ron Rogers RROGERS@galot To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] tery.orgcc: Sent by: Subject: RE: SQL Tuning - How to avoid TOCHAR function against a date [EMAIL PROTECTED] om 04/08/02 03:23 PM Please respond to ORACLE-L Tom, I realize that there would not be an index but I was trying to eliminate some overhead by using the TRUNC function as compaired to the to_char for the fields. Cherie, If the table is not to large how about pinning it to save on disk reads? Ron ROR mª¿ªm [EMAIL PROTECTED] 04/08/02 03:35PM Ron, the TRUNC function will also prevent the use of an index on the oracle_date column. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Monday, April 08, 2002 2:54 PM To: Multiple recipients of list ORACLE-L Cherie, How about using the TRUNC function on the date field. That will use only thre ,MM,DD of the ORACLE_DATE column. Then you will be comparing like columns without going through the to_char conversion. WHERE TRUNC(ORACLE_DATE) = TRUNC(:b1) Ron ROR mª¿ªm [EMAIL PROTECTED] 04/08/02 01:56PM I've got the following SQL statement that is running very long on a nightly data load. The problem is the TO_CHAR function which is preventing me from using the index on this small (20,000-row table). This is an 8.0.4 database so it is not possible for me to use make this a function-based index. The problem is that the date field has minutes, etc. included and those need to be eliminated before the comparison can be made. That's why I can't just eliminate the TO_CHAR from both sides of the equation. Isn't there a way that I can pull this function out of the select statement and do it in a preceeding statement? Then I could just pass in both variables to this statement without the TO_CHAR and use my index. Is this realistic? How, exactly could it be done? SELECT DATE_KEY FROM DATE_DIM WHERE TO_CHAR(ORACLE_DATE,'DD-MON-') = TO_CHAR(:b1,'DD-MON-') SQL desc date_dim; NameNull?Type --- DATE_KEYNOT NULL NUMBER(5) ORACLE_DATE NOT NULL DATE DATACOM_DATE NUMBER(6) DATACOM_REVERSE_DATE NUMBER(6) DAY_OF_WEEK NOT NULL VARCHAR2(30) DAY_NUMBER_IN_MONTH NOT NULL NUMBER(3) DAY_NUMBER_OVERALL NOT NULL NUMBER(9) WEEK_NUMBER_IN_YEAR NOT NULL NUMBER(3) WEEK_NUMBER_OVERALL NOT NULL NUMBER(7) MONTH NOT NULL VARCHAR2(30) MONTH_NUMBER_OVERALLNOT NULL NUMBER(7) YEARNOT NULL NUMBER(5) WEEKDAY_IND NOT NULL CHAR(1) LAST_DAY_IN_MONTH_IND NOT NULL CHAR(1) DATA_WAREHOUSE_MOD_DATETIME NOT NULL DATE DATA_MART_MOD_DATETIME NOT NULL DATE SQL select oracle_date from date_dim where rownum=1; ORACLE_DA - 01-JAN-70 Thanks in advance for any help. Cherie Machler Oracle DBA Gelco Information Network -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists
RE: SQL Tuning - How to avoid TOCHAR function against a date
I am jumping in the middle of this thread so execuse me if I am repeating the past suggestions I haven't read yet. In a DW you'd have a date dim of dates only (no time component to date) and a time_dim (down to seconds). Your fact table should have a date_key and a time_key if both date and time components are significant. In that case your query SELECT DATE_KEY FROM DATE_DIM WHERE TO_CHAR(ORACLE_DATE,'DD-MON-') = TO_CHAR(:b1,'DD-MON-') would be transformed to: SELECT DATE_KEY FROM DATE_DIM WHERE ORACLE_DATE = TRUNC(:b1); You should perhaps update the oracle_date column in date_dim to TRUNC(oracle_date,'DD') and then rebuild the index on oracle_date column and run the above mentioned query. - Sundeep --- [EMAIL PROTECTED] wrote: Ron, That's an idea. Easy to implement and test. I'll give it a try tonight to see if it helps. It is a small table. Cherie Ron Rogers RROGERS@galot To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] tery.orgcc: Sent by: Subject: RE: SQL Tuning - How to avoid TOCHAR function against a date [EMAIL PROTECTED] om 04/08/02 03:23 PM Please respond to ORACLE-L Tom, I realize that there would not be an index but I was trying to eliminate some overhead by using the TRUNC function as compaired to the to_char for the fields. Cherie, If the table is not to large how about pinning it to save on disk reads? Ron ROR mª¿ªm [EMAIL PROTECTED] 04/08/02 03:35PM Ron, the TRUNC function will also prevent the use of an index on the oracle_date column. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Monday, April 08, 2002 2:54 PM To: Multiple recipients of list ORACLE-L Cherie, How about using the TRUNC function on the date field. That will use only thre ,MM,DD of the ORACLE_DATE column. Then you will be comparing like columns without going through the to_char conversion. WHERE TRUNC(ORACLE_DATE) = TRUNC(:b1) Ron ROR mª¿ªm [EMAIL PROTECTED] 04/08/02 01:56PM I've got the following SQL statement that is running very long on a nightly data load. The problem is the TO_CHAR function which is preventing me from using the index on this small (20,000-row table). This is an 8.0.4 database so it is not possible for me to use make this a function-based index. The problem is that the date field has minutes, etc. included and those need to be eliminated before the comparison can be made. That's why I can't just eliminate the TO_CHAR from both sides of the equation. Isn't there a way that I can pull this function out of the select statement and do it in a preceeding statement? Then I could just pass in both variables to this statement without the TO_CHAR and use my index. Is this realistic? How, exactly could it be done? SELECT DATE_KEY FROM DATE_DIM WHERE TO_CHAR(ORACLE_DATE,'DD-MON-') = TO_CHAR(:b1,'DD-MON-') SQL desc date_dim; NameNull?Type --- DATE_KEYNOT NULL NUMBER(5) ORACLE_DATE NOT NULL DATE DATACOM_DATE NUMBER(6) DATACOM_REVERSE_DATE NUMBER(6) DAY_OF_WEEK NOT NULL VARCHAR2(30) DAY_NUMBER_IN_MONTH NOT NULL NUMBER(3) DAY_NUMBER_OVERALL NOT NULL NUMBER(9
RE: SQL Tuning - How to avoid TOCHAR function against a date
I have not got a system to test this out on at the moment but can you do a substr on the to_char so that the format matches the date_key Something like substr((TO_CHAR(:b1,'DD-MON-'),11) John -Original Message- [EMAIL PROTECTED] Sent: 08 April 2002 18:57 To: Multiple recipients of list ORACLE-L I've got the following SQL statement that is running very long on a nightly data load. The problem is the TO_CHAR function which is preventing me from using the index on this small (20,000-row table). This is an 8.0.4 database so it is not possible for me to use make this a function-based index. The problem is that the date field has minutes, etc. included and those need to be eliminated before the comparison can be made. That's why I can't just eliminate the TO_CHAR from both sides of the equation. Isn't there a way that I can pull this function out of the select statement and do it in a preceeding statement? Then I could just pass in both variables to this statement without the TO_CHAR and use my index. Is this realistic? How, exactly could it be done? SELECT DATE_KEY FROM DATE_DIM WHERE TO_CHAR(ORACLE_DATE,'DD-MON-') = TO_CHAR(:b1,'DD-MON-') SQL desc date_dim; NameNull?Type --- DATE_KEYNOT NULL NUMBER(5) ORACLE_DATE NOT NULL DATE DATACOM_DATE NUMBER(6) DATACOM_REVERSE_DATE NUMBER(6) DAY_OF_WEEK NOT NULL VARCHAR2(30) DAY_NUMBER_IN_MONTH NOT NULL NUMBER(3) DAY_NUMBER_OVERALL NOT NULL NUMBER(9) WEEK_NUMBER_IN_YEAR NOT NULL NUMBER(3) WEEK_NUMBER_OVERALL NOT NULL NUMBER(7) MONTH NOT NULL VARCHAR2(30) MONTH_NUMBER_OVERALLNOT NULL NUMBER(7) YEARNOT NULL NUMBER(5) WEEKDAY_IND NOT NULL CHAR(1) LAST_DAY_IN_MONTH_IND NOT NULL CHAR(1) DATA_WAREHOUSE_MOD_DATETIME NOT NULL DATE DATA_MART_MOD_DATETIME NOT NULL DATE SQL select oracle_date from date_dim where rownum=1; ORACLE_DA - 01-JAN-70 Thanks in advance for any help. Cherie Machler Oracle DBA Gelco Information Network -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: John Hallas INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
RE: SQL Tuning - How to avoid TOCHAR function against a date
Cherie, Couldn't you do SELECT DATE_KEY FROM DATE_DIM WHERE ORACLE_DATE = trunc(:b1) and oracle_date trunc(:b1) + 1 which should at least give a range scan. Iain Nicoll -Original Message- Sent: Monday, April 08, 2002 6:57 PM To: Multiple recipients of list ORACLE-L I've got the following SQL statement that is running very long on a nightly data load. The problem is the TO_CHAR function which is preventing me from using the index on this small (20,000-row table). This is an 8.0.4 database so it is not possible for me to use make this a function-based index. The problem is that the date field has minutes, etc. included and those need to be eliminated before the comparison can be made. That's why I can't just eliminate the TO_CHAR from both sides of the equation. Isn't there a way that I can pull this function out of the select statement and do it in a preceeding statement? Then I could just pass in both variables to this statement without the TO_CHAR and use my index. Is this realistic? How, exactly could it be done? SELECT DATE_KEY FROM DATE_DIM WHERE TO_CHAR(ORACLE_DATE,'DD-MON-') = TO_CHAR(:b1,'DD-MON-') SQL desc date_dim; NameNull?Type --- DATE_KEYNOT NULL NUMBER(5) ORACLE_DATE NOT NULL DATE DATACOM_DATE NUMBER(6) DATACOM_REVERSE_DATE NUMBER(6) DAY_OF_WEEK NOT NULL VARCHAR2(30) DAY_NUMBER_IN_MONTH NOT NULL NUMBER(3) DAY_NUMBER_OVERALL NOT NULL NUMBER(9) WEEK_NUMBER_IN_YEAR NOT NULL NUMBER(3) WEEK_NUMBER_OVERALL NOT NULL NUMBER(7) MONTH NOT NULL VARCHAR2(30) MONTH_NUMBER_OVERALLNOT NULL NUMBER(7) YEARNOT NULL NUMBER(5) WEEKDAY_IND NOT NULL CHAR(1) LAST_DAY_IN_MONTH_IND NOT NULL CHAR(1) DATA_WAREHOUSE_MOD_DATETIME NOT NULL DATE DATA_MART_MOD_DATETIME NOT NULL DATE SQL select oracle_date from date_dim where rownum=1; ORACLE_DA - 01-JAN-70 Thanks in advance for any help. Cherie Machler Oracle DBA Gelco Information Network -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Nicoll, Iain (Calanais) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
RE: SQL Tuning - How to avoid TOCHAR function against a date
Could you maybe calculate a range of date values that encompasses the period you want and use BETWEEN on the raw date column? I'm thinking something along the lines of: SELECT DATE_KEY FROM DATE_DIM WHERE ORACLE_DATE BETWEEN TRUNC(:b1) AND TRUNC(:b1) + .9 ; but like, more elegant. 8^) HTH, -Roy Roy Pardee Programmer/Analyst SWFPAC Lockheed Martin IT Extension 8487 -Original Message- Sent: Monday, April 08, 2002 10:57 AM To: Multiple recipients of list ORACLE-L I've got the following SQL statement that is running very long on a nightly data load. The problem is the TO_CHAR function which is preventing me from using the index on this small (20,000-row table). This is an 8.0.4 database so it is not possible for me to use make this a function-based index. The problem is that the date field has minutes, etc. included and those need to be eliminated before the comparison can be made. That's why I can't just eliminate the TO_CHAR from both sides of the equation. Isn't there a way that I can pull this function out of the select statement and do it in a preceeding statement? Then I could just pass in both variables to this statement without the TO_CHAR and use my index. Is this realistic? How, exactly could it be done? SELECT DATE_KEY FROM DATE_DIM WHERE TO_CHAR(ORACLE_DATE,'DD-MON-') = TO_CHAR(:b1,'DD-MON-') SQL desc date_dim; NameNull?Type --- DATE_KEYNOT NULL NUMBER(5) ORACLE_DATE NOT NULL DATE DATACOM_DATE NUMBER(6) DATACOM_REVERSE_DATE NUMBER(6) DAY_OF_WEEK NOT NULL VARCHAR2(30) DAY_NUMBER_IN_MONTH NOT NULL NUMBER(3) DAY_NUMBER_OVERALL NOT NULL NUMBER(9) WEEK_NUMBER_IN_YEAR NOT NULL NUMBER(3) WEEK_NUMBER_OVERALL NOT NULL NUMBER(7) MONTH NOT NULL VARCHAR2(30) MONTH_NUMBER_OVERALLNOT NULL NUMBER(7) YEARNOT NULL NUMBER(5) WEEKDAY_IND NOT NULL CHAR(1) LAST_DAY_IN_MONTH_IND NOT NULL CHAR(1) DATA_WAREHOUSE_MOD_DATETIME NOT NULL DATE DATA_MART_MOD_DATETIME NOT NULL DATE SQL select oracle_date from date_dim where rownum=1; ORACLE_DA - 01-JAN-70 Thanks in advance for any help. Cherie Machler Oracle DBA Gelco Information Network -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Pardee, Roy E INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
RE: SQL Tuning - How to avoid TOCHAR function against a date
John, I will test it out. Thanks for your helpful recommendation. Cherie John Hallas john.hallas@hcresour To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] ces.co.uk cc: Sent by:Subject: RE: SQL Tuning - How to avoid TOCHAR function against a date [EMAIL PROTECTED] 04/08/02 01:20 PM Please respond to ORACLE-L I have not got a system to test this out on at the moment but can you do a substr on the to_char so that the format matches the date_key Something like substr((TO_CHAR(:b1,'DD-MON-'),11) John -Original Message- [EMAIL PROTECTED] Sent: 08 April 2002 18:57 To: Multiple recipients of list ORACLE-L I've got the following SQL statement that is running very long on a nightly data load. The problem is the TO_CHAR function which is preventing me from using the index on this small (20,000-row table). This is an 8.0.4 database so it is not possible for me to use make this a function-based index. The problem is that the date field has minutes, etc. included and those need to be eliminated before the comparison can be made. That's why I can't just eliminate the TO_CHAR from both sides of the equation. Isn't there a way that I can pull this function out of the select statement and do it in a preceeding statement? Then I could just pass in both variables to this statement without the TO_CHAR and use my index. Is this realistic? How, exactly could it be done? SELECT DATE_KEY FROM DATE_DIM WHERE TO_CHAR(ORACLE_DATE,'DD-MON-') = TO_CHAR(:b1,'DD-MON-') SQL desc date_dim; NameNull?Type --- DATE_KEYNOT NULL NUMBER(5) ORACLE_DATE NOT NULL DATE DATACOM_DATE NUMBER(6) DATACOM_REVERSE_DATE NUMBER(6) DAY_OF_WEEK NOT NULL VARCHAR2(30) DAY_NUMBER_IN_MONTH NOT NULL NUMBER(3) DAY_NUMBER_OVERALL NOT NULL NUMBER(9) WEEK_NUMBER_IN_YEAR NOT NULL NUMBER(3) WEEK_NUMBER_OVERALL NOT NULL NUMBER(7) MONTH NOT NULL VARCHAR2(30) MONTH_NUMBER_OVERALLNOT NULL NUMBER(7) YEARNOT NULL NUMBER(5) WEEKDAY_IND NOT NULL CHAR(1) LAST_DAY_IN_MONTH_IND NOT NULL CHAR(1) DATA_WAREHOUSE_MOD_DATETIME NOT NULL DATE DATA_MART_MOD_DATETIME NOT NULL DATE SQL select oracle_date from date_dim where rownum=1; ORACLE_DA - 01-JAN-70 Thanks in advance for any help. Cherie Machler Oracle DBA Gelco Information Network -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: John Hallas INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list
Re: SQL Tuning - How to avoid TOCHAR function against a date
I don't think you can do it.. I mean, you could change it to trunc the oracle_date field (that eliminates the minutes) and then do a to_date of :b1 but you will still be operating on the oracle_date field. Okay, I HATE to suggest this, but since the table is small: add another field to the table oracle_date_2 as a date field. Update the table set oracle_date_2=trunc(oracle_date) add a trigger to fill in oracle_date_2 when you insert a row or update the oracle_date column create an index on oracle_date_2 and change the query to use that column --- [EMAIL PROTECTED] wrote: I've got the following SQL statement that is running very long on a nightly data load. The problem is the TO_CHAR function which is preventing me from using the index on this small (20,000-row table). This is an 8.0.4 database so it is not possible for me to use make this a function-based index. The problem is that the date field has minutes, etc. included and those need to be eliminated before the comparison can be made. That's why I can't just eliminate the TO_CHAR from both sides of the equation. Isn't there a way that I can pull this function out of the select statement and do it in a preceeding statement? Then I could just pass in both variables to this statement without the TO_CHAR and use my index. Is this realistic? How, exactly could it be done? SELECT DATE_KEY FROM DATE_DIM WHERE TO_CHAR(ORACLE_DATE,'DD-MON-') = TO_CHAR(:b1,'DD-MON-') SQL desc date_dim; NameNull?Type --- DATE_KEYNOT NULL NUMBER(5) ORACLE_DATE NOT NULL DATE DATACOM_DATE NUMBER(6) DATACOM_REVERSE_DATE NUMBER(6) DAY_OF_WEEK NOT NULL VARCHAR2(30) DAY_NUMBER_IN_MONTH NOT NULL NUMBER(3) DAY_NUMBER_OVERALL NOT NULL NUMBER(9) WEEK_NUMBER_IN_YEAR NOT NULL NUMBER(3) WEEK_NUMBER_OVERALL NOT NULL NUMBER(7) MONTH NOT NULL VARCHAR2(30) MONTH_NUMBER_OVERALLNOT NULL NUMBER(7) YEARNOT NULL NUMBER(5) WEEKDAY_IND NOT NULL CHAR(1) LAST_DAY_IN_MONTH_IND NOT NULL CHAR(1) DATA_WAREHOUSE_MOD_DATETIME NOT NULL DATE DATA_MART_MOD_DATETIME NOT NULL DATE SQL select oracle_date from date_dim where rownum=1; ORACLE_DA - 01-JAN-70 Thanks in advance for any help. Cherie Machler Oracle DBA Gelco Information Network -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). __ Do You Yahoo!? Yahoo! Tax Center - online filing with TurboTax http://taxes.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
RE: SQL Tuning - How to avoid TOCHAR function against a date
Iain, I will do some tests. Theoretically, yes, a range scan should be better than a full table scan. Thanks for your helpful recommendation. Cherie Nicoll, Iain (Calanais)To: '[EMAIL PROTECTED]' [EMAIL PROTECTED] iain.nicoll@cal cc: '[EMAIL PROTECTED]' [EMAIL PROTECTED] anais.com Subject: RE: SQL Tuning - How to avoid TOCHAR function against a date 04/08/02 12:37 PM Cherie, Couldn't you do SELECT DATE_KEY FROM DATE_DIM WHERE ORACLE_DATE = trunc(:b1) and oracle_date trunc(:b1) + 1 which should at least give a range scan. Iain Nicoll -Original Message- Sent: Monday, April 08, 2002 6:57 PM To: Multiple recipients of list ORACLE-L I've got the following SQL statement that is running very long on a nightly data load. The problem is the TO_CHAR function which is preventing me from using the index on this small (20,000-row table). This is an 8.0.4 database so it is not possible for me to use make this a function-based index. The problem is that the date field has minutes, etc. included and those need to be eliminated before the comparison can be made. That's why I can't just eliminate the TO_CHAR from both sides of the equation. Isn't there a way that I can pull this function out of the select statement and do it in a preceeding statement? Then I could just pass in both variables to this statement without the TO_CHAR and use my index. Is this realistic? How, exactly could it be done? SELECT DATE_KEY FROM DATE_DIM WHERE TO_CHAR(ORACLE_DATE,'DD-MON-') = TO_CHAR(:b1,'DD-MON-') SQL desc date_dim; NameNull?Type --- DATE_KEYNOT NULL NUMBER(5) ORACLE_DATE NOT NULL DATE DATACOM_DATE NUMBER(6) DATACOM_REVERSE_DATE NUMBER(6) DAY_OF_WEEK NOT NULL VARCHAR2(30) DAY_NUMBER_IN_MONTH NOT NULL NUMBER(3) DAY_NUMBER_OVERALL NOT NULL NUMBER(9) WEEK_NUMBER_IN_YEAR NOT NULL NUMBER(3) WEEK_NUMBER_OVERALL NOT NULL NUMBER(7) MONTH NOT NULL VARCHAR2(30) MONTH_NUMBER_OVERALLNOT NULL NUMBER(7) YEARNOT NULL NUMBER(5) WEEKDAY_IND NOT NULL CHAR(1) LAST_DAY_IN_MONTH_IND NOT NULL CHAR(1) DATA_WAREHOUSE_MOD_DATETIME NOT NULL DATE DATA_MART_MOD_DATETIME NOT NULL DATE SQL select oracle_date from date_dim where rownum=1; ORACLE_DA - 01-JAN-70 Thanks in advance for any help. Cherie Machler Oracle DBA Gelco Information Network -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
RE: SQL Tuning - How to avoid TOCHAR function against a date
Something like: WHERE ORACLE_DATE between trunc(:b1) and trunc(:b1) + 1 - 1/(24*60*60) -Original Message- Sent: Monday, April 08, 2002 12:57 PM To: Multiple recipients of list ORACLE-L I've got the following SQL statement that is running very long on a nightly data load. The problem is the TO_CHAR function which is preventing me from using the index on this small (20,000-row table). This is an 8.0.4 database so it is not possible for me to use make this a function-based index. The problem is that the date field has minutes, etc. included and those need to be eliminated before the comparison can be made. That's why I can't just eliminate the TO_CHAR from both sides of the equation. Isn't there a way that I can pull this function out of the select statement and do it in a preceeding statement? Then I could just pass in both variables to this statement without the TO_CHAR and use my index. Is this realistic? How, exactly could it be done? SELECT DATE_KEY FROM DATE_DIM WHERE TO_CHAR(ORACLE_DATE,'DD-MON-') = TO_CHAR(:b1,'DD-MON-') SQL desc date_dim; NameNull?Type --- DATE_KEYNOT NULL NUMBER(5) ORACLE_DATE NOT NULL DATE DATACOM_DATE NUMBER(6) DATACOM_REVERSE_DATE NUMBER(6) DAY_OF_WEEK NOT NULL VARCHAR2(30) DAY_NUMBER_IN_MONTH NOT NULL NUMBER(3) DAY_NUMBER_OVERALL NOT NULL NUMBER(9) WEEK_NUMBER_IN_YEAR NOT NULL NUMBER(3) WEEK_NUMBER_OVERALL NOT NULL NUMBER(7) MONTH NOT NULL VARCHAR2(30) MONTH_NUMBER_OVERALLNOT NULL NUMBER(7) YEARNOT NULL NUMBER(5) WEEKDAY_IND NOT NULL CHAR(1) LAST_DAY_IN_MONTH_IND NOT NULL CHAR(1) DATA_WAREHOUSE_MOD_DATETIME NOT NULL DATE DATA_MART_MOD_DATETIME NOT NULL DATE SQL select oracle_date from date_dim where rownum=1; ORACLE_DA - 01-JAN-70 Thanks in advance for any help. Cherie Machler Oracle DBA Gelco Information Network -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Norrell, Brian INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
Re: SQL Tuning - How to avoid TOCHAR function against a date
Cherie, How about using the TRUNC function on the date field. That will use only thre ,MM,DD of the ORACLE_DATE column. Then you will be comparing like columns without going through the to_char conversion. WHERE TRUNC(ORACLE_DATE) = TRUNC(:b1) Ron ROR mª¿ªm [EMAIL PROTECTED] 04/08/02 01:56PM I've got the following SQL statement that is running very long on a nightly data load. The problem is the TO_CHAR function which is preventing me from using the index on this small (20,000-row table). This is an 8.0.4 database so it is not possible for me to use make this a function-based index. The problem is that the date field has minutes, etc. included and those need to be eliminated before the comparison can be made. That's why I can't just eliminate the TO_CHAR from both sides of the equation. Isn't there a way that I can pull this function out of the select statement and do it in a preceeding statement? Then I could just pass in both variables to this statement without the TO_CHAR and use my index. Is this realistic? How, exactly could it be done? SELECT DATE_KEY FROM DATE_DIM WHERE TO_CHAR(ORACLE_DATE,'DD-MON-') = TO_CHAR(:b1,'DD-MON-') SQL desc date_dim; NameNull?Type --- DATE_KEYNOT NULL NUMBER(5) ORACLE_DATE NOT NULL DATE DATACOM_DATE NUMBER(6) DATACOM_REVERSE_DATE NUMBER(6) DAY_OF_WEEK NOT NULL VARCHAR2(30) DAY_NUMBER_IN_MONTH NOT NULL NUMBER(3) DAY_NUMBER_OVERALL NOT NULL NUMBER(9) WEEK_NUMBER_IN_YEAR NOT NULL NUMBER(3) WEEK_NUMBER_OVERALL NOT NULL NUMBER(7) MONTH NOT NULL VARCHAR2(30) MONTH_NUMBER_OVERALLNOT NULL NUMBER(7) YEARNOT NULL NUMBER(5) WEEKDAY_IND NOT NULL CHAR(1) LAST_DAY_IN_MONTH_IND NOT NULL CHAR(1) DATA_WAREHOUSE_MOD_DATETIME NOT NULL DATE DATA_MART_MOD_DATETIME NOT NULL DATE SQL select oracle_date from date_dim where rownum=1; ORACLE_DA - 01-JAN-70 Thanks in advance for any help. Cherie Machler Oracle DBA Gelco Information Network -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Ron Rogers INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
RE: SQL Tuning - How to avoid TOCHAR function against a date
Ron, the TRUNC function will also prevent the use of an index on the oracle_date column. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Monday, April 08, 2002 2:54 PM To: Multiple recipients of list ORACLE-L Cherie, How about using the TRUNC function on the date field. That will use only thre ,MM,DD of the ORACLE_DATE column. Then you will be comparing like columns without going through the to_char conversion. WHERE TRUNC(ORACLE_DATE) = TRUNC(:b1) Ron ROR mª¿ªm [EMAIL PROTECTED] 04/08/02 01:56PM I've got the following SQL statement that is running very long on a nightly data load. The problem is the TO_CHAR function which is preventing me from using the index on this small (20,000-row table). This is an 8.0.4 database so it is not possible for me to use make this a function-based index. The problem is that the date field has minutes, etc. included and those need to be eliminated before the comparison can be made. That's why I can't just eliminate the TO_CHAR from both sides of the equation. Isn't there a way that I can pull this function out of the select statement and do it in a preceeding statement? Then I could just pass in both variables to this statement without the TO_CHAR and use my index. Is this realistic? How, exactly could it be done? SELECT DATE_KEY FROM DATE_DIM WHERE TO_CHAR(ORACLE_DATE,'DD-MON-') = TO_CHAR(:b1,'DD-MON-') SQL desc date_dim; NameNull?Type --- DATE_KEYNOT NULL NUMBER(5) ORACLE_DATE NOT NULL DATE DATACOM_DATE NUMBER(6) DATACOM_REVERSE_DATE NUMBER(6) DAY_OF_WEEK NOT NULL VARCHAR2(30) DAY_NUMBER_IN_MONTH NOT NULL NUMBER(3) DAY_NUMBER_OVERALL NOT NULL NUMBER(9) WEEK_NUMBER_IN_YEAR NOT NULL NUMBER(3) WEEK_NUMBER_OVERALL NOT NULL NUMBER(7) MONTH NOT NULL VARCHAR2(30) MONTH_NUMBER_OVERALLNOT NULL NUMBER(7) YEARNOT NULL NUMBER(5) WEEKDAY_IND NOT NULL CHAR(1) LAST_DAY_IN_MONTH_IND NOT NULL CHAR(1) DATA_WAREHOUSE_MOD_DATETIME NOT NULL DATE DATA_MART_MOD_DATETIME NOT NULL DATE SQL select oracle_date from date_dim where rownum=1; ORACLE_DA - 01-JAN-70 Thanks in advance for any help. Cherie Machler Oracle DBA Gelco Information Network -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Ron Rogers INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
RE: SQL Tuning - How to avoid TOCHAR function against a date
let's face it Rachel, the date column is probably incorrect as the table was designed. knowing that it is important in queries, and that the minutes cause problems during query, your suggestion should have been incorporated in the original design (or truncing the oracle_date field via a trigger). both the blessing and curse of the DATE column. great for performing date math, but a pain when it comes to queries. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Monday, April 08, 2002 2:54 PM To: Multiple recipients of list ORACLE-L I don't think you can do it.. I mean, you could change it to trunc the oracle_date field (that eliminates the minutes) and then do a to_date of :b1 but you will still be operating on the oracle_date field. Okay, I HATE to suggest this, but since the table is small: add another field to the table oracle_date_2 as a date field. Update the table set oracle_date_2=trunc(oracle_date) add a trigger to fill in oracle_date_2 when you insert a row or update the oracle_date column create an index on oracle_date_2 and change the query to use that column --- [EMAIL PROTECTED] wrote: I've got the following SQL statement that is running very long on a nightly data load. The problem is the TO_CHAR function which is preventing me from using the index on this small (20,000-row table). This is an 8.0.4 database so it is not possible for me to use make this a function-based index. The problem is that the date field has minutes, etc. included and those need to be eliminated before the comparison can be made. That's why I can't just eliminate the TO_CHAR from both sides of the equation. Isn't there a way that I can pull this function out of the select statement and do it in a preceeding statement? Then I could just pass in both variables to this statement without the TO_CHAR and use my index. Is this realistic? How, exactly could it be done? SELECT DATE_KEY FROM DATE_DIM WHERE TO_CHAR(ORACLE_DATE,'DD-MON-') = TO_CHAR(:b1,'DD-MON-') SQL desc date_dim; NameNull?Type --- DATE_KEYNOT NULL NUMBER(5) ORACLE_DATE NOT NULL DATE DATACOM_DATE NUMBER(6) DATACOM_REVERSE_DATE NUMBER(6) DAY_OF_WEEK NOT NULL VARCHAR2(30) DAY_NUMBER_IN_MONTH NOT NULL NUMBER(3) DAY_NUMBER_OVERALL NOT NULL NUMBER(9) WEEK_NUMBER_IN_YEAR NOT NULL NUMBER(3) WEEK_NUMBER_OVERALL NOT NULL NUMBER(7) MONTH NOT NULL VARCHAR2(30) MONTH_NUMBER_OVERALLNOT NULL NUMBER(7) YEARNOT NULL NUMBER(5) WEEKDAY_IND NOT NULL CHAR(1) LAST_DAY_IN_MONTH_IND NOT NULL CHAR(1) DATA_WAREHOUSE_MOD_DATETIME NOT NULL DATE DATA_MART_MOD_DATETIME NOT NULL DATE SQL select oracle_date from date_dim where rownum=1; ORACLE_DA - 01-JAN-70 Thanks in advance for any help. Cherie Machler Oracle DBA Gelco Information Network -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). __ Do You Yahoo!? Yahoo! Tax Center - online filing with TurboTax http://taxes.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists
RE: SQL Tuning - How to avoid TOCHAR function against a date
I suppose if you wanted to collect statistics about hourly usage, then the minutes info would be necessary but then, most people don't think about how they really want to use the date when they add a date field --- Mercadante, Thomas F [EMAIL PROTECTED] wrote: let's face it Rachel, the date column is probably incorrect as the table was designed. knowing that it is important in queries, and that the minutes cause problems during query, your suggestion should have been incorporated in the original design (or truncing the oracle_date field via a trigger). both the blessing and curse of the DATE column. great for performing date math, but a pain when it comes to queries. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Monday, April 08, 2002 2:54 PM To: Multiple recipients of list ORACLE-L I don't think you can do it.. I mean, you could change it to trunc the oracle_date field (that eliminates the minutes) and then do a to_date of :b1 but you will still be operating on the oracle_date field. Okay, I HATE to suggest this, but since the table is small: add another field to the table oracle_date_2 as a date field. Update the table set oracle_date_2=trunc(oracle_date) add a trigger to fill in oracle_date_2 when you insert a row or update the oracle_date column create an index on oracle_date_2 and change the query to use that column --- [EMAIL PROTECTED] wrote: I've got the following SQL statement that is running very long on a nightly data load. The problem is the TO_CHAR function which is preventing me from using the index on this small (20,000-row table). This is an 8.0.4 database so it is not possible for me to use make this a function-based index. The problem is that the date field has minutes, etc. included and those need to be eliminated before the comparison can be made. That's why I can't just eliminate the TO_CHAR from both sides of the equation. Isn't there a way that I can pull this function out of the select statement and do it in a preceeding statement? Then I could just pass in both variables to this statement without the TO_CHAR and use my index. Is this realistic? How, exactly could it be done? SELECT DATE_KEY FROM DATE_DIM WHERE TO_CHAR(ORACLE_DATE,'DD-MON-') = TO_CHAR(:b1,'DD-MON-') SQL desc date_dim; NameNull?Type --- DATE_KEYNOT NULL NUMBER(5) ORACLE_DATE NOT NULL DATE DATACOM_DATE NUMBER(6) DATACOM_REVERSE_DATE NUMBER(6) DAY_OF_WEEK NOT NULL VARCHAR2(30) DAY_NUMBER_IN_MONTH NOT NULL NUMBER(3) DAY_NUMBER_OVERALL NOT NULL NUMBER(9) WEEK_NUMBER_IN_YEAR NOT NULL NUMBER(3) WEEK_NUMBER_OVERALL NOT NULL NUMBER(7) MONTH NOT NULL VARCHAR2(30) MONTH_NUMBER_OVERALLNOT NULL NUMBER(7) YEARNOT NULL NUMBER(5) WEEKDAY_IND NOT NULL CHAR(1) LAST_DAY_IN_MONTH_IND NOT NULL CHAR(1) DATA_WAREHOUSE_MOD_DATETIME NOT NULL DATE DATA_MART_MOD_DATETIME NOT NULL DATE SQL select oracle_date from date_dim where rownum=1; ORACLE_DA - 01-JAN-70 Thanks in advance for any help. Cherie Machler Oracle DBA Gelco Information Network -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). __ Do You Yahoo!? Yahoo! Tax Center - online filing with TurboTax http://taxes.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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
RE: SQL Tuning - How to avoid TOCHAR function against a date
Tom, I realize that there would not be an index but I was trying to eliminate some overhead by using the TRUNC function as compaired to the to_char for the fields. Cherie, If the table is not to large how about pinning it to save on disk reads? Ron ROR mª¿ªm [EMAIL PROTECTED] 04/08/02 03:35PM Ron, the TRUNC function will also prevent the use of an index on the oracle_date column. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Monday, April 08, 2002 2:54 PM To: Multiple recipients of list ORACLE-L Cherie, How about using the TRUNC function on the date field. That will use only thre ,MM,DD of the ORACLE_DATE column. Then you will be comparing like columns without going through the to_char conversion. WHERE TRUNC(ORACLE_DATE) = TRUNC(:b1) Ron ROR mª¿ªm [EMAIL PROTECTED] 04/08/02 01:56PM I've got the following SQL statement that is running very long on a nightly data load. The problem is the TO_CHAR function which is preventing me from using the index on this small (20,000-row table). This is an 8.0.4 database so it is not possible for me to use make this a function-based index. The problem is that the date field has minutes, etc. included and those need to be eliminated before the comparison can be made. That's why I can't just eliminate the TO_CHAR from both sides of the equation. Isn't there a way that I can pull this function out of the select statement and do it in a preceeding statement? Then I could just pass in both variables to this statement without the TO_CHAR and use my index. Is this realistic? How, exactly could it be done? SELECT DATE_KEY FROM DATE_DIM WHERE TO_CHAR(ORACLE_DATE,'DD-MON-') = TO_CHAR(:b1,'DD-MON-') SQL desc date_dim; NameNull?Type --- DATE_KEYNOT NULL NUMBER(5) ORACLE_DATE NOT NULL DATE DATACOM_DATE NUMBER(6) DATACOM_REVERSE_DATE NUMBER(6) DAY_OF_WEEK NOT NULL VARCHAR2(30) DAY_NUMBER_IN_MONTH NOT NULL NUMBER(3) DAY_NUMBER_OVERALL NOT NULL NUMBER(9) WEEK_NUMBER_IN_YEAR NOT NULL NUMBER(3) WEEK_NUMBER_OVERALL NOT NULL NUMBER(7) MONTH NOT NULL VARCHAR2(30) MONTH_NUMBER_OVERALLNOT NULL NUMBER(7) YEARNOT NULL NUMBER(5) WEEKDAY_IND NOT NULL CHAR(1) LAST_DAY_IN_MONTH_IND NOT NULL CHAR(1) DATA_WAREHOUSE_MOD_DATETIME NOT NULL DATE DATA_MART_MOD_DATETIME NOT NULL DATE SQL select oracle_date from date_dim where rownum=1; ORACLE_DA - 01-JAN-70 Thanks in advance for any help. Cherie Machler Oracle DBA Gelco Information Network -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Ron Rogers INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Ma il 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.com -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Ron Rogers INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego,
RE: SQL Tuning - How to avoid TOCHAR function against a date
Tom, It is probably too late for this original design but it is not too late for a new data warehouse that is in development. Jared has made a recommendation for better date columns that may help eliminate these problems. I have forwarded that table design on to the application owner. Thanks for your reply. Cherie Mercadante, Thomas F To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] [EMAIL PROTECTED] cc: ate.ny.us Subject: RE: SQL Tuning - How to avoid TOCHAR function against a date Sent by: [EMAIL PROTECTED] 04/08/02 02:35 PM Please respond to ORACLE-L let's face it Rachel, the date column is probably incorrect as the table was designed. knowing that it is important in queries, and that the minutes cause problems during query, your suggestion should have been incorporated in the original design (or truncing the oracle_date field via a trigger). both the blessing and curse of the DATE column. great for performing date math, but a pain when it comes to queries. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Monday, April 08, 2002 2:54 PM To: Multiple recipients of list ORACLE-L I don't think you can do it.. I mean, you could change it to trunc the oracle_date field (that eliminates the minutes) and then do a to_date of :b1 but you will still be operating on the oracle_date field. Okay, I HATE to suggest this, but since the table is small: add another field to the table oracle_date_2 as a date field. Update the table set oracle_date_2=trunc(oracle_date) add a trigger to fill in oracle_date_2 when you insert a row or update the oracle_date column create an index on oracle_date_2 and change the query to use that column --- [EMAIL PROTECTED] wrote: I've got the following SQL statement that is running very long on a nightly data load. The problem is the TO_CHAR function which is preventing me from using the index on this small (20,000-row table). This is an 8.0.4 database so it is not possible for me to use make this a function-based index. The problem is that the date field has minutes, etc. included and those need to be eliminated before the comparison can be made. That's why I can't just eliminate the TO_CHAR from both sides of the equation. Isn't there a way that I can pull this function out of the select statement and do it in a preceeding statement? Then I could just pass in both variables to this statement without the TO_CHAR and use my index. Is this realistic? How, exactly could it be done? SELECT DATE_KEY FROM DATE_DIM WHERE TO_CHAR(ORACLE_DATE,'DD-MON-') = TO_CHAR(:b1,'DD-MON-') SQL desc date_dim; NameNull?Type --- DATE_KEYNOT NULL NUMBER(5) ORACLE_DATE NOT NULL DATE DATACOM_DATE NUMBER(6) DATACOM_REVERSE_DATE NUMBER(6) DAY_OF_WEEK NOT NULL VARCHAR2(30) DAY_NUMBER_IN_MONTH NOT NULL NUMBER(3) DAY_NUMBER_OVERALL NOT NULL NUMBER(9) WEEK_NUMBER_IN_YEAR NOT NULL NUMBER(3) WEEK_NUMBER_OVERALL NOT NULL NUMBER(7) MONTH NOT NULL VARCHAR2(30) MONTH_NUMBER_OVERALLNOT NULL NUMBER(7) YEARNOT NULL NUMBER(5) WEEKDAY_IND NOT NULL CHAR(1) LAST_DAY_IN_MONTH_IND
Re: SQL tuning advice
If the tables are all analyzed, and cost-based optimizer is enabled, the order in the FROM clause does not matter (it would matter only if you added an /*+ ORDERED(...) */ hint. I'm not sure what you mean by your question about guidelines in the predicate. I assume you're trying to tune the query. You have indices on every column mentioned? How large are these tables? --- [EMAIL PROTECTED] wrote: Hi DBAs, Oracle 8.1 We have the following query where each field name is a separate index. My question is does it matter the order of table names in the from clause. I assume that phy_contracts is the driving table. Also what guidelines should I use in the predicate,i.e.,cardinality,etc.? The tables are analyzed. Thanks Rick SELECT COUNT(a.phy_contract_id) FROM accrued_and_paid a, phy_contracts b WHERE a.hold_payment_flag = 'Y' AND b.phy_contract_id = a.phy_contract_id AND b.company_id = 16 ANDb.contract_type = 'IC'; -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). = Paul Baumgartel, Adept Computer Associates, Inc. [EMAIL PROTECTED] __ Do You Yahoo!? Yahoo! Sports - live college hoops coverage http://sports.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Paul Baumgartel INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
Re: SQL Tuning
Greg Moore wrote: That's a great SQL tuning presentation, but I don't follow what he says in slide #91: --- No WHERE clause with an ORDER BY Often a screen will return rows with a predefined order, e.g., ORDER BY NAME. The user is expected to enter a NAME or part of a name, NAME=SMI. If the user presses Enter for the query without entering a name, all table rows must be sorted. So always have a WHERE NAME CHR(1). What does the extra WHERE clause do for you? I have not checked the detail of the presentation but this is an old trick with the rule based optimizer. When using the RBO Oracle will not use an index if the indexed column is not referenced in the WHERE clause (no fast full scan, which you are likely to get with the CBO and a relatively recent Oracle release). By asking for values greater than something known to be smaller than all entries, Oracle will use the (sorted) index and avoid the cost of a sort. -- Regards, Stephane Faroult Oriole Corporation Voice: +44 (0) 7050-696-269 Fax:+44 (0) 7050-696-449 Performance Tools Free Scripts -- http://www.oriole.com, designed by Oracle DBAs for Oracle DBAs -- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
RE: SQL tuning / optimization problem - IS NOT NULL - Clarify
Create index containing 3 columns in the view. After that this query will read only index and not table - fast full index scan - should be much faster - especially if you are on 8.1.6 or higher and can use compress - CASE_WIP2WIPBIN is a very good candidate. I would created index like - create index xxx on table table_case (CASE_WIP2WIPBIN, CASE_REPORTER2SITE, objid) compress 1 (if you are on 8.1.6 or higher) Alex Hillman -Original Message- Sent: Wednesday, August 08, 2001 2:48 PM To: Multiple recipients of list ORACLE-L I have been struggling with a SQL statement that is generated by a help desk application called Clarify. The code is all canned so I can't change it (well maybe the view if that's the only way). So far I have been tuning this application by adding indexes, histograms, etc. However, I've hit the wall with the following SQL statement. I've been messing around with adding indexes to the table to no avail. The best I've been able to get it to do is a full index scan. The situation is complicated by the bind variable, the existence of the view, and the IS NOT NULL clause which I haven't tuned before (and haven't been able to find much tuning documentation on). Version is 8.0.4 and Sun Solaris 2.6. CASE table has about 115,000 rows in it. From tkprof output file: select wip_objid, elm_objid, site_objid from table_site2case_view WHERE ( site_objid = :B1 ) call count cpuelapsed disk querycurrent rows --- -- -- -- -- -- -- Parse1 0.01 0.03 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch1 3.18 13.98 21184 21960 5 0 --- -- -- -- -- -- -- total3 3.19 14.01 21184 21960 5 0 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 96 (SA) Rows Execution Plan --- --- 0 SELECT STATEMENT GOAL: CHOOSE 114904 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'TABLE_CASE' SQL select text from dba_views where view_name='TABLE_SITE2CASE_VIEW'; TEXT select table_case.case_wip2wipbin, table_case.objid, table_case.case_reporter2site from table_case where table_case.case_wip2wipbin IS NOT NULL AND table_case.case_reporter2site IS NOT NULL COLUMN_NAME NUM_NULLS NUM_DISTINCT ---- OBJID 0 114450 CASE_REPORTER2SITE0 1418 CASE_WIP2WIPBIN113615 88 SQL desc table_case NameNull?Type --- --- OBJIDNUMBER TITLEVARCHAR2(80) S_TITLE VARCHAR2(80) ID_NUMBERVARCHAR2(255) CREATION_TIMEDATE INTERNAL_CASENUMBER HANGUP_TIME DATE ALT_PHONE_NUMVARCHAR2(20) PHONE_NUMVARCHAR2(20) PICKUP_EXT VARCHAR2(8) CASE_HISTORY LONG TOPICS_TITLE VARCHAR2(255) YANK_FLAGNUMBER SERVER_STATUSVARCHAR2(2) SUPPORT_TYPE VARCHAR2(2) WARRANTY_FLAGVARCHAR2(2) SUPPORT_MSG VARCHAR2(80) ALT_LAST_NAMEVARCHAR2(30) ALT_FAX_NUMBER VARCHAR2(20) ALT_E_MAIL VARCHAR2(80) ALT_SITE_NAMEVARCHAR2(80) ALT_ADDRESS VARCHAR2(200) ALT_CITY VARCHAR2(30) ALT_STATEVARCHAR2(30) ALT_ZIPCODE VARCHAR2(20) FCS_CC_NOTIFYNUMBER SYMPTOM_CODE VARCHAR2(10) CURE_CODEVARCHAR2(10) SITE_TIMEDATE ALT_PROD_SERIAL VARCHAR2(30) MSG_WAIT_COUNT NUMBER REPLY_WAIT_COUNT NUMBER REPLY_STATE NUMBER OPER_SYSTEM VARCHAR2(20) CASE_SUP_TYPEVARCHAR2(2) PAYMENT_METHOD VARCHAR2(30) REF_NUMBER
RE: SQL tuning / optimization problem - IS NOT NULL - Clarify
Alex, I had achieved a full index scan before and was disappointed with the test results because it was showing a full scan on the index. However, after I got your email, I turned on timed_statistics and had the user do a real test for me on the test database and the actual times where significantly faster (at least ten-fold). So even though the explain plan shows it scanning the full number of rows in the table, because it's going against the index only, the real elapsed time was still impressive. Thanks for taking time to reply. Cherie Hillman, Alex Alex.Hillman@usmint.To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] treas.gov cc: Sent by: Subject: RE: SQL tuning / optimization problem - IS NOT NULL - [EMAIL PROTECTED] Clarify 08/08/01 02:43 PM Please respond to ORACLE-L Create index containing 3 columns in the view. After that this query will read only index and not table - fast full index scan - should be much faster - especially if you are on 8.1.6 or higher and can use compress - CASE_WIP2WIPBIN is a very good candidate. I would created index like - create index xxx on table table_case (CASE_WIP2WIPBIN, CASE_REPORTER2SITE, objid) compress 1 (if you are on 8.1.6 or higher) Alex Hillman -Original Message- Sent: Wednesday, August 08, 2001 2:48 PM To: Multiple recipients of list ORACLE-L I have been struggling with a SQL statement that is generated by a help desk application called Clarify. The code is all canned so I can't change it (well maybe the view if that's the only way). So far I have been tuning this application by adding indexes, histograms, etc. However, I've hit the wall with the following SQL statement. I've been messing around with adding indexes to the table to no avail. The best I've been able to get it to do is a full index scan. The situation is complicated by the bind variable, the existence of the view, and the IS NOT NULL clause which I haven't tuned before (and haven't been able to find much tuning documentation on). Version is 8.0.4 and Sun Solaris 2.6. CASE table has about 115,000 rows in it. From tkprof output file: select wip_objid, elm_objid, site_objid from table_site2case_view WHERE ( site_objid = :B1 ) call count cpuelapsed disk querycurrent rows --- -- -- -- -- -- -- Parse1 0.01 0.03 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch1 3.18 13.98 21184 21960 5 0 --- -- -- -- -- -- -- total3 3.19 14.01 21184 21960 5 0 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 96 (SA) Rows Execution Plan --- --- 0 SELECT STATEMENT GOAL: CHOOSE 114904 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'TABLE_CASE' SQL select text from dba_views where view_name='TABLE_SITE2CASE_VIEW'; TEXT select table_case.case_wip2wipbin, table_case.objid, table_case.case_reporter2site from table_case where table_case.case_wip2wipbin IS NOT NULL AND table_case.case_reporter2site IS NOT NULL COLUMN_NAME NUM_NULLS NUM_DISTINCT ---- OBJID 0 114450 CASE_REPORTER2SITE0 1418 CASE_WIP2WIPBIN113615
Re: SQL Tuning question?
Hello, I think, there are 3 factors in performance tuning: - Time - Amount - Speed Most imporatnt factor in performance tuning is the time. Of course others are important, too. But, others are indirect indicator. For example: - 1 block 1000 ms - 1000 block 1 ms As we see above, second one takes less time although it gest more blocks. we can't say that problem is in second one(if there is a problem in one of them). And, speed is indirect indicator, too. nobody asks the speed of cars in formula races, but asks time spent in races. I think, time is the most important factor. itrprof is based on time. you can run itrprof to see bottlenecks. it's at http://www.unal-bilisim.com/products/itrprof/itrprof.html regards... Seema Singh wrote: Hi Gurus When I run one complex query I get the following statistics. Statistics -- 832 recursive calls 4 db block gets 98502 consistent gets 0 physical reads 0 redo size 995 bytes sent via SQL*Net to client 4306 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 427 sorts (memory) 0 sorts (disk) 0 rows processed Is this statistics good for executed sql statment?After looking the above statistics what we can say? Thanks -SEEMA _ Get your FREE download of MSN Explorer at http://explorer.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seema Singh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). -- Use itrprof SQL Analyzer. It formats SQL_TRACE/Event10046 traces and gives tuning advises. It's web based, no download, no configuration. Just click http://www.unal-bilisim.com/products/itrprof/itrprof_index.html -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Danisment Gazi Unal (Unal Bilisim) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
RE: Sql Tuning help
Matt,
have you tried replacing the IN statement with an EXISTS
statement?
-Original Message-From:
[EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]]Sent: Wednesday, February 07,
2001 8:31 AMTo: Multiple recipients of list
ORACLE-LSubject: Sql Tuning help
I have been having
some problems with this statement
SELECT
to_char(NVL(SUM(bet_amount),0))FROM sb_betsWHERE processed_DATE =
add_months(TO_DATE('07011999 00','MMDD HH24MISS'),19-1)
AND processed_DATE add_months(TO_DATE('07011999 00','MMDD
HH24MISS'),19) AND
customer_id in (select customer_idfrom
customerswhere
customers.customer_id=sb_bets.customer_id and
LICENSEE_ID=6130)
Both tables are full access no indexes
used. There is an index on sb_bets.processed_date and
customers.customer_id is a primary key and customers.licensee_id has an index
also. Of course this query may just pull too many customer ids to bother
with an index. But that is not too bad only 20 records in customers
but over 12 million in sb_bets. Is there a better way of writing this
query? I have tried hints but still nothing changed. Any ideas
would be greatly appreciated.
Please email me for any further info
thanks.
Matt
Southcott
DBA
Starnetsystems
(268) 480
1734
RE: Sql Tuning help
Here's
some thoughts.
I
don't know if between is faster, but it might be, and won't a straight join do
the same as your subquery?
SELECT
to_char(NVL(SUM(bet_amount),0))
FROM
sb_bets
WHERE
processed_DATE between add_months( .) and
add_months( ..) AND
customers.customer_id = sb_bets.customer_id AND
customer.licensee_id = 6130;
Dan
"Just my thought"
-Original Message-From:
[EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]]Sent: Wednesday, February 07,
2001 8:31 AMTo: Multiple recipients of list
ORACLE-LSubject: Sql Tuning help
I have been having
some problems with this statement
SELECT
to_char(NVL(SUM(bet_amount),0))FROM sb_betsWHERE processed_DATE =
add_months(TO_DATE('07011999 00','MMDD HH24MISS'),19-1)
AND processed_DATE add_months(TO_DATE('07011999 00','MMDD
HH24MISS'),19) AND
customer_id in (select customer_idfrom
customerswhere
customers.customer_id=sb_bets.customer_id and
LICENSEE_ID=6130)
Both tables are full access no indexes
used. There is an index on sb_bets.processed_date and
customers.customer_id is a primary key and customers.licensee_id has an index
also. Of course this query may just pull too many customer ids to bother
with an index. But that is not too bad only 20 records in customers
but over 12 million in sb_bets. Is there a better way of writing this
query? I have tried hints but still nothing changed. Any ideas
would be greatly appreciated.
Please email me for any further info
thanks.
Matt
Southcott
DBA
Starnetsystems
(268) 480
1734
_This
message has been checked for all known viruses by UUNET delivered through
the MessageLabs Virus Control Centre. For further information visithttp://www.uk.uu.net/products/security/virus/
RE: Sql Tuning help
Try joining the 2 tables as below. I think the IN will do a FTS always.
Correct me if I'm wrong.
Rick
SELECT to_char(NVL(SUM(a.bet_amount),0))
FROM sb_bets a, customer b
WHERE a.processed_DATE = add_months(TO_DATE('07011999 00','MMDD
HH24MISS'),19-1)
AND a.processed_DATE add_months(TO_DATE('07011999 00','MMDD
HH24MISS'),19)
AND a.customer_id = b.customer_id
AND b.licensee_id = 6130;
-Original Message-
From: [EMAIL PROTECTED] [SMTP:[EMAIL PROTECTED]]
Sent: Wednesday, February 07, 2001 8:31 AM
To: Multiple recipients of list ORACLE-L
Subject: Sql Tuning help
I have been having some problems with this statement
SELECT to_char(NVL(SUM(bet_amount),0))
FROM sb_bets
WHERE processed_DATE = add_months(TO_DATE('07011999 00','MMDD
HH24MISS'),19-1) AND processed_DATE add_months(TO_DATE('07011999
00','MMDD HH24MISS'),19) AND customer_id in (select customer_id
from customers
where customers.customer_id=sb_bets.customer_id and LICENSEE_ID=6130)
Both tables are full access no indexes used. There is an index on
sb_bets.processed_date and customers.customer_id is a primary key and
customers.licensee_id has an index also. Of course this query may just
pull too many customer ids to bother with an index. But that is not too
bad only 20 records in customers but over 12 million in sb_bets. Is
there a better way of writing this query? I have tried hints but still
nothing changed. Any ideas would be greatly appreciated.
Please email me for any further info thanks.
Matt Southcott
DBA
Starnetsystems
(268) 480 1734
File: Matthew Southcott.vcf
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Cale, Rick T (Richard)
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
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).
Re: Sql Tuning help
Instead of customer_id in...try
where exists (select 'x' from customers
where sb_bets.customer_id = customers.customer_id
and sb_bets.customer_id and LICENSEE_ID=6130)
[EMAIL PROTECTED] 02/07/01 08:30AM
I have been having some problems with this statement
SELECT to_char(NVL(SUM(bet_amount),0))
FROM sb_bets
WHERE processed_DATE = add_months(TO_DATE('07011999 00','MMDD
HH24MISS'),19-1) AND processed_DATE add_months(TO_DATE('07011999
00','MMDD HH24MISS'),19) AND customer_id in (select customer_id
from customers
where customers.customer_id=sb_bets.customer_id and LICENSEE_ID=6130)
Both tables are full access no indexes used. There is an index on
sb_bets.processed_date and customers.customer_id is a primary key and
customers.licensee_id has an index also. Of course this query may just pull
too many customer ids to bother with an index. But that is not too bad only
20 records in customers but over 12 million in sb_bets. Is there a
better way of writing this query? I have tried hints but still nothing
changed. Any ideas would be greatly appreciated.
Please email me for any further info thanks.
Matt Southcott
DBA
Starnetsystems
(268) 480 1734
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Tim Sawmiller
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
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).
