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. ** ==
