Query Tuning Presented by: Charles Pfeiffer CIO - PowerPoint PPT Presentation

slide1 n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Query Tuning Presented by: Charles Pfeiffer CIO PowerPoint Presentation
Download Presentation
Query Tuning Presented by: Charles Pfeiffer CIO

play fullscreen
1 / 76
Query Tuning Presented by: Charles Pfeiffer CIO
136 Views
Download Presentation
carlow
Download Presentation

Query Tuning Presented by: Charles Pfeiffer CIO

- - - - - - - - - - - - - - - - - - - - - - - - - - - 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)