1 / 76

Query Tuning Presented by: Charles Pfeiffer CIO

Query Tuning Presented by: Charles Pfeiffer CIO (888) 235-8916. Agenda. 0800 – 0815: Introduction 0815 – 0900: Access Path Tuning 0900 – 0945: Advanced Tuning 0945 – 1000: Break 1000 – 1015: Call Your DBA (Submit a Ticket)

carlow
Download Presentation

Query Tuning Presented by: Charles Pfeiffer CIO

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Query Tuning Presented by: Charles Pfeiffer CIO (888) 235-8916

  2. Agenda 0800 – 0815: Introduction 0815 – 0900: Access Path Tuning 0900 – 0945: Advanced Tuning 0945 – 1000: Break 1000 – 1015: Call Your DBA (Submit a Ticket) 1015 – 1030: Wrap Up 1030 – 1100: Final Q&A

  3. Query Tuning Introduction

  4. Meet The Presenter • Remote DBA Support for Liberty IT Staff • Consultant for 12 years • Several successful tuning engagements • Reduced runtime averages from approximately 4 hours minutes to approximately 1 minute for over 100 reports • Reduced runtime from 2 hours to 15 seconds for one query • Reduced load time from 15 hours to 30 minutes

  5. Who Are You? • Oracle Developers • Background in any other DBs? • Procedural Programming background? • Object Oriented Programming background?

  6. What Are We Talking About? • Make your queries run faster • The tools never work • What can you do? • What can the DBA do?

  7. Why Do You Care? • Get more done • Save time • Growth = exponential increase • Be a better neighbor!

  8. The Tools Never Work • Bad Tools • Crystal Reports • Application Forms • Web Forms • ReportWriter • Good Tools • SQL*Plus • OEM • SQL Navigator • Toad

  9. What Can Be Done? • What can you do? • Tune your query before releasing it into production • Most queries should complete in < 15 seconds. Many in < 1 minute • Save baselines and good explain plans • Re-use good code • What can the DBA do? • Help you identify the problem and tune the query • Tune the DB and the system • Look at the problem with a different perspective

  10. Query Tuning Access Path Tuning

  11. Response Time Typical Verbal CPU 1,000,000,000 /Sec 3 GHz Billions of cycles / sec Memory 1,000,000,000 of a Sec 10 – 50 ns (nano) Billionth of a sec Disk I/O 1,000 of a Sec 6 ms (milli transfer) Thousandth of a sec What Can We Tune? Speed of Hardware

  12. What Should We Tune? • Disk IO • Has the biggest impact on overall runtime • Known as access path tuning • Do less IO! • Do IO more efficiently

  13. Do Less IO • Use proper joins • Use proper indexing • Use views when appropriate • Don’t do unnecessary sorts! • Store common aggregate results – Materialized Views

  14. Understanding Growth • Linear growth • Perfect 45° line on a graph • Typical pattern • Runtime doubles as the input (data set) doubles

  15. Understanding Growth (continued) • Exponential growth • Growth increases at an increasing rate • Worst case scenario • Runtime increases by at least 4x as the input (data set) doubles

  16. Understanding Growth (continued) • Logarithmic growth • Growth increases at a decreasing rate • Best case scenario • Runtime increases by at least 4x as the input (data set) doubles

  17. Chart of Runtimes Table To Illustrate Growth

  18. Causes of Exponential Growth • Bad table joins • A = B and C= D • A/B are in one set, C/D in another • Nothing bridges the gap – Cartesian Product! • Heavy sort operations • Order by • Group by

  19. Achieving Logarithmic Growth • Primary key index access! • All tables should have useful primary keys • All table joins should try to be foreign key > primary key • All queries should try to use the primary key in the where clause

  20. Operations Rule • Operations • Any read or write is an operation • All operations take some amount of time • Most are minimal, but do add up • Simplify this argument: 1 operation = 1 unit (in time) • The best access path is the least costly one • Improve run time by reducing operations

  21. Tuners Riddle • What is the quickest way to fill in the blank? • Hint: Think mathematically rather than logically • Illustrates the false constraints we place on tuning sessions • Think outside the box Think about it - We’ll come back to it later

  22. Best Practices In Query Writing • Select only what you need • Stop doing select * • Use as many predicates as you can • Predicates are conditions in the where clause • Limit the result set • Better than having because they limit the data retrieved • Use AND, avoid OR • Avoid functions (to_date, upper, etc.) • Restructure data if necessary – Don’t live with bad designs

  23. Best Practices in Query Writing (continued) • Use literals • Where col1 = ‘ABC’ • Encourages index usage • Finds the right data faster

  24. Rules for Tuning • Don’t be afraid to try something (in Dev/Test/QA) • You can always make the problem worse • But you can also make it better • Tune one select at a time (sub-queries) • Know when to stop. What is good enough? • Review the explain plan • Positives • Index access for any table with more than 1,000 rows • Index unique access • Simplicity!

  25. Rules for Tuning (continued) • Review the explain plan (continued) • Negatives • Cartesian Join • Full Table Scan for tables with more than 1,000 rows • Index Full Scan (sometimes) • Complicated shape

  26. Rules for Tuning (continued) • Review the explain plan (continued) • Things to do • Compare the predicates in the query to the index used • Add an index if necessary • Use an index hint if necessary • Modify join order and/or join type

  27. Rules for Tuning (continued) • Indexes • Indexes grow Logarithmically • Can provide sorted output, sorts usually grow exponentially • Only good for highly selective predicates (< 20% table) • Indexes can contain multiple columns, but must match the query

  28. Rules for Tuning (continued) • Types of indexes • B*Tree: Great for highly selective columns • Bitmap: Better for not-so-highly selective columns • Indexes Null Values!!! • Function-based: Needed if you use functions on columns • Avoid using functions on columns if you can • Trunc(’2007-01-01 12:00:00’) > trunc(datestamp) Is the same as Trunc(‘2007-01-01 12:00:00’) > datestamp

  29. Rules for Tuning (continued) • Hints • RECOMMENDS a path for the optimizer • Use table aliases not table names • If Oracle doesn’t take your hint, STOP! • You are missing something

  30. Rules for Tuning (continued) • Common hints • /*+ INDEX(table index) */: use this index for this table • /*+ ORDERED */: read tables in the order of the from clause • /*+ LEADING(table) */: lead with this table • /*+ use_hash(table1, table2) */: use hash joins for these tables. Good for large data sets. Encourages full tablee scans. • /*+ use_nl(table1, table2) */: use nested loops to join these tables. Good for small data sets. Encourages index usage.

  31. Rules for Tuning (continued) • Join Order • Try to apply predicates in the most efficient manner • Optimizer picks the leading table based on: • Literal values in predicates • Indexes on literal columns • Table with the most selective index • Primary Key • Index that can avoid a sort

  32. Answering the Riddle What is the quickest way to fill in the blank?

  33. Answer • Do nothing • It’s a blank • It doesn’t need to have any content • The operation to add a NULL or space character is wasteful

  34. Query Tuning Advanced Tuning

  35. Example Query SELECT I.CUST_CODE, R.RCPT_NUM, R.RCPT_REF_NUM INVOICE,I.INVT_LEV1, R.RCPT_ALT_ REF1 LOC, I.INVT_LE V2,TO_CHAR(TRUNC(R.RCPT_CONF_DATE),'DD-MON-YYYY') RCPT_DATE,NVL(SUM(NVL(I.CHG_TO T,0) + NVL(I.CHG_TA X1,0) + NVL(I.CHG_TAX2,0) ),0) CHG_TOT FROM RECIPT R,INVT_ACCSS I WHERE I.COMP _CODE = 'W8' AND I .CUST_CODE LIKE NVL('SCFLEADS','%') AND TRUNC(R.RCPT_CONF_DATE) BETWEEN TRUNC(to_d ate('01-JAN-2007',' DD-MON-YYYY')) AND TRUNC(sysdate) AND UPPER(I.ACCSS_STAT) = 'A' AND I.INV_NUM IS NOT NU LL AND ((I.ACCSS_S RCE_REF_FLAG = 'R' AND I.ACCSS_SRCE_REF_CHG_TP_FLAG IN ( 'R','A','E','B' ) ) OR ('Y' = 'Y' AND (I.ACCSS_SRCE_REF_FLAG = 'E' AND I.ACCSS_SRCE_REF_CHG_TP_FLAG = 'E' ))) AND R .COMP_CODE = I.CO MP_CODE AND R.RCPT_NUM = I.ACCSS_SRCE_REF_NUM GROUP BY I.CUST_CODE,R.RCPT_N UM, R.RCPT_REF_NU M, I.INVT_LEV1,R.RCPT_ALT_REF1,I.INVT_LEV2, TO_CHAR(TRUNC(R.RCPT_CONF_DATE),'DD -MON-YYYY') HAVI NG NVL(SUM(NVL(I.CHG_TOT,0) + NVL(I.CHG_TAX1,0) + NVL(I.CHG_TAX2,0) ),0) > 0 ORD ER BY 1,2,3;

  36. Format The Query • Make it easy to read • Identify key parts of the query • Select – Typically useless • From – Each table on a separate line • Where – Each condition on a separate line • Group By – Sorts. Influences index usage • Having – Typically useless • Order By – Sorts. Influences index usage

  37. Formatted Example Query SELECT I.CUST_CODE, R.RCPT_NUM, R.RCPT_REF_NUM INVOICE, I.INVT_LEV1, R.RCPT_ALT_REF1 LOC, I.INVT_LEV2, TO_CHAR(TRUNC(R.RCPT_CONF_DATE), 'DD-MON-YYYY') RCPT_DATE, NVL(SUM(NVL(I.CHG_TOT,0) + NVL(I.CHG_TAX1,0) + NVL(I.CHG_TAX2,0) ),0) CHG_TOT FROM RECIPT R, INVT_ACCSS I WHERE I.COMP_CODE = 'W8' AND I.CUST_CODE LIKE NVL('SCFLEADS','%') AND TRUNC(R.RCPT_CONF_DATE) BETWEEN TRUNC(to_date('01-JAN-2007','DD-MON-YYYY')) AND TRUNC(sysdate) AND UPPER(I.ACCSS_STAT) = 'A' AND I.INV_NUM IS NOT NULL AND ((I.ACCSS_SRCE_REF_FLAG = 'R' AND I.ACCSS_SRCE_REF_CHG_TP_FLAG IN ( 'R','A','E','B' )) OR ('Y' = 'Y' AND (I.ACCSS_SRCE_REF_FLAG = 'E' AND I.ACCSS_SRCE_REF_CHG_TP_FLAG = 'E' ))) AND R.COMP_CODE = I.COMP_CODE AND R.RCPT_NUM = I.ACCSS_SRCE_REF_NUM GROUP BY I.CUST_CODE,R.RCPT_NUM, R.RCPT_REF_NUM, I.INVT_LEV1, R.RCPT_ALT_REF1, I.INVT_LEV2, TO_CHAR(TRUNC(R.RCPT_CONF_DATE),'DD-MON-YYYY') HAVING NVL(SUM(NVL(I.CHG_TOT,0) + NVL(I.CHG_TAX1,0) + NVL(I.CHG_TAX2,0) ),0) > 0 ORDER BY 1,2,3;

  38. Establish A Baseline And Explain Plan • SET TIMING ON • SET AUTOTRACE ON • Runs the query and displays the explain plan at the end • SET AUTOTRACE TRACE EXP • Just displays the explain plan

  39. Establish A Baseline And Explain Plan (continued) Initial Run Time: 10 minutes , 17 seconds Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=COST 1 0 FILTER 2 1 SORT (GROUP BY) 3 2 NESTED LOOPS 4 3 TABLE ACCESS (BY ROWID) OF 'INVT_ACCSS' 5 4 INDEX (RANGE SCAN) OF 'INV_ACS_IDX03' (NON-UNIQUE) 6 3 TABLE ACCESS (BY ROWID) OF 'RECIPT' 7 6 INDEX (UNIQUE SCAN) OF 'RECIPT_IDX03' (UNIQUE)

  40. Reading The Explain Plan Execution Plan --------------------------------------------------- 0 SELECT STATEMENT Optimizer=COST 1 0 |-FILTER 2 1 |-SORT (GROUP BY) 3 2 |-NESTED LOOPS | 4 3 |-TABLE ACCESS 5 4 | |-INDEX (RANGE SCAN) | 6 3 |-TABLE ACCESS 7 6 |-INDEX (UNIQUE SCAN)

  41. Reading The Explain Plan Execution Plan --------------------------------------------------- 0 SELECT STATEMENT Optimizer=COST 1 0 |-FILTER 2 1 |-SORT (GROUP BY) 3 2 |-NESTED LOOPS | 4 3 |-TABLE ACCESS 5 4 | |-INDEX (RANGE SCAN) | 6 3 |-TABLE ACCESS 7 6 |-INDEX (UNIQUE SCAN)

  42. Tune • Look for adequate table-joins • Confirm Proper Function Usage • Sufficient Index Usage • Use Hints if Needed

  43. Table Joins • You cannot have un-joined sets of data • For tables A, B, C, and D • GOOD • A – B – C – D • A – B A – C A – D • BAD • A – B C – D (LEADS TO A CARTESIAN!!!)

  44. Table Joins (continued) • FROM • RECIPT R • INVT_ACCSS I • WHERE • AND R.COMP_CODE = I.COMP_CODE • AND R.RCPT_NUM = I.ACCSS_SRCE_REF_NUM

  45. Functions • Avoid using functions on columns in the where clause • Interferes with index selection • Excessive function usage increases processing time • UPPER(I.ACCSS_STAT) = 'A‘ • I.ACCSS_STAT = 'A‘ • Another Solution • I.ACCSS_STAT IN ('A','a')

  46. Functions – New Explain Plan RUN TIME: 8 minutes, 41 seconds Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer= COST 1 0 FILTER 2 1 SORT (GROUP BY) 3 2 NESTED LOOPS 4 3 TABLE ACCESS (BY ROWID) OF 'INVT_ACCSS' 5 4 INDEX (RANGE SCAN) OF 'INV_ACS_IDX07' (NON-UNIQUE) 6 3 TABLE ACCESS (BY ROWID) OF 'RECIPT' 7 6 INDEX (UNIQUE SCAN) OF 'RECIPT_IDX03' (UNIQUE)

  47. Indexes • Column order counts. Lead with the most selective columns • Review explain plan to see what indexes are being used • Look at the query to see what columns should be indexed • INVT_ACCSS: COMP_CODE, CUST_CODE ACCSS_SRCE_REF_NUM, ACCSS_STAT, INV_NUM, ACCSS_SRCE_REF_FLAG, ACCSS_SRCE_REF_CHG_TP_FLAG • RECIPT: COMP_CODE, RCPT_NUM, RCPT_CONF_DATE

  48. Indexes (continued) • CREATE INDEX INVT_ACCSS _TEST_IDX on INVT_ACCSS(COMP_CODE, CUST_CODE, ACCSS_SRCE_REF_NUM, ACCSS_STAT, INV_NUM, ACCSS_SRCE_REF_FLAG, ACCSS_SRCE_REF_CHG_TP_FLAG); • CREATE INDEX RECIPT_TEST_IDX on RECIPT(RCPT_NUM, RCPT_CONF_DATE, COMP_CODE);

  49. Indexes – New Explain Plan RUN TIME: 7 minutes, 26 seconds Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer= COST 1 0 FILTER 2 1 SORT (GROUP BY) 3 2 NESTED LOOPS 4 3 TABLE ACCESS (BY ROWID) OF 'INVT_ACCSS' 5 4 INDEX (RANGE SCAN) OF 'INV_ACS_IDX07' (NON-UNIQUE) 6 3 TABLE ACCESS (BY ROWID) OF 'RECIPT' 7 6 INDEX (UNIQUE SCAN) OF 'RECIPT_TEST_IDX' (UNIQUE)

  50. DBA Had To Update Statistics – New Explain Plan Could have tried a hint! If it works then call for a stats update. If it doesn’t work something else is wrong RUN TIME: 5 minutes, 3 seconds Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer= COST 1 0 FILTER 2 1 SORT (GROUP BY) 3 2 NESTED LOOPS 4 3 TABLE ACCESS (BY ROWID) OF 'INVT_ACCSS' 5 4 INDEX (RANGE SCAN) OF 'INV_ACS_TEST_IDX' (NON-UNIQUE) 6 3 TABLE ACCESS (BY ROWID) OF 'RECIPT' 7 6 INDEX (UNIQUE SCAN) OF 'RECIPT_TEST_IDX' (UNIQUE)

More Related