Computer Resource Team, Inc.

Transcript Computer Resource Team, Inc.

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) 1015 – 1030: Wrap Up 1030 – 1100: Final Q&A

Query Tuning

Introduction

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

Who Are You?

 Oracle Developers  Background in any other DBs?

 Procedural Programming background?

 Object Oriented Programming background?

What Are We Talking About?

 Make your queries run faster  The tools never work  What can you do?

 What can the DBA do?

Why Do You Care?

 Get more done  Save time  Growth = exponential increase  Be a better neighbor!

The Tools Never Work

  Bad Tools – – – – Crystal Reports Application Forms Web Forms ReportWriter Good Tools – SQL*Plus – – OEM SQL Navigator – Toad

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

Query Tuning

Access Path Tuning

CPU Memory Disk I/O

What Can We Tune?

Speed of Hardware

Response Time 1,000,000,00 0 /Sec 1,000,000,00 0 of a Sec 1,000 of a Sec Typical 3 GHz 10 – 50 ns (nano) 6 ms (milli transfer) Verbal Billions of cycles / sec Billionth of a sec Thousandth of a sec

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

Do Less IO

 Use proper joins  Use proper indexing   Use views when appropriate Don’t do unnecessary sorts!

 Store common aggregate results – Materialized Views

Understanding Growth

 Linear growth – – – Perfect 45° line on a graph Typical pattern Runtime doubles as the input (data set) doubles

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

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

Chart of Runtimes

Table To Illustrate Growth

1 10 100 1,000 10,000 100,000 1,000,000 10,000,000 100,000,000 Logarithmic 1 1 3 7 10 13 17 20 23 Linear 1 10 100 1,000 10,000 100,000 1,000,000 10,000,000 100,000,000 Exponential 1 100 10,000 1,000,000 100,000,000 10,000,000,000 1,000,000,000,000 100,000,000,000,000 10,000,000,000,000,000

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

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

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

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

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

Best Practices in Query Writing (continued)

 Use literals – Where col1 = ‘ABC’ – Encourages index usage – Finds the right data faster

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!

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

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

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

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

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

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.

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

Answering the Riddle

What is the quickest way to fill in the blank?

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

Query Tuning

Advanced Tuning

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;

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

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;

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

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)

Reading The Explain Plan

Reading The Explain Plan

Tune

 Look for adequate table-joins  Confirm Proper Function Usage  Sufficient Index Usage  Use Hints if Needed

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

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

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

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)

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

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

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)

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)

Hints

• • • Index / Full • Chose good index on its own after the statistics update Leading / Ordered • We’ll try it – Select /*+ ORDERED */ Hash / Nested Loop • We’ll try it if we still need help after Leading/Ordered hint

Hints – New Explain Plan

RUN TIME: 1 minute, 39 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 'RECIPT'

5 4

INDEX (UNIQUE SCAN) OF 'RECIPT_TEST_IDX' (UNIQUE)

6 3

TABLE ACCESS (BY ROWID) OF 'INVT_ACCSS'

7 6

INDEX (RANGE SCAN) OF 'INV_ACS_TEST_IDX' (NON-UNIQUE)

Hash Join vs. Nested Loop

• • Hash joins use a hashing algorithm to join tables • Select /*+ USE_HASH */ Nested Loop joins use a nested loop to join tables; each row in one table cycles through EVERY row of the next table • Select /*+ USE_NL */

Hash Join – New Explain Plan

Run Time: 23 seconds Execution Plan --------------------------------------------------------- 0 SELECT STATEMENT Optimizer= COST 1 0 FILTER 2 1 SORT (GROUP BY) 3 2

HASH JOIN (Cost=33 Card=1 Bytes=346

) 4 3 TABLE ACCESS (BY ROWID) OF 'RECIPT‘ (Cost=13 Card= 314 Bytes=36738) 5 4 INDEX (UNIQUE SCAN) OF 'RECIPT_TEST_IDX' (UNIQUE) 6 3 TABLE ACCESS (BY ROWID) OF 'INVT_ACCSS' (Cost=15 Card =71 Bytes=16259) 7 6 INDEX (RANGE SCAN) OF 'INV_ACS_TEST_IDX' (NON UNIQUE)

Record The Good Baseline

 Creates a record of the query’s improvement of run time  Gives a record of what the baseline SHOULD be if there are problems with the explain plan in the future.

Final Example Query

SELECT /*+ ORDERED USE_HASH */ 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 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

Keep In Mind

 Most queries only need one or two changes to significantly improve run time  This example is an extreme case  DBAs can help – update statistics, etc.

Query Tuning

Break!

See you in 15 minutes

Query Tuning

Call Your DBA: Submit A Ticket

Call Me

  Submit a ticket or contact me for help – (888) 235-8916 – [email protected]

We can see things you can’t – SQL Trace & Statspack – – – DB Parameters OS/Storage Configuration Advanced Explain Plan Analysis

We Have More Tools

 DBAs have additional tools to make things work – – – – More hints Statistics Complex views Materialized Views – Stored Outlines

SQL Trace & Statspack

 Identify exactly where the DB/query is spending time  Identify sources of contention  Tune the DB / Instance if needed  Identify lack of resources in the DB/server/storage

Statistics

  Tells Oracle how much data is in each structure Allows Oracle to choose the “best” access path  May be out of date  May not influence the DB properly

More Hints

 Oracle has over 100 hints  Many of them require knowledge of the DB and Optimizer  Many of them override init parameters  DBAs can gauge the impact of hints on the overall system

Complex Views

 Can be used in place of sub-queries  Allows Oracle to choose more orderly access paths  Can be used to organize the result set

Materialized Views

 Best used when you can pre-calculate aggregates  Store complex result sets to retrieve/join in other queries  Huge impact on computational queries – – – Large sum functions Having clause Large group by clause

Stored Outlines

 We only need to get it to work once – Oracle evaluates queries at run time – – Chooses a new access path each time a query runs Dependent on up-to-date statistics and volume changes  Find a good explain plan and store it – – – Once we have a good one we can assign it to a query Oracle will use it every time the query runs Ignores changes to data volume and statistics

Query Tuning

Wrap Up

Summary

 Be aware of tools that create poorly written queries  Growth = Exponential increase  Be a good neighbor!

 Tune queries before you release them into production – Does your query run in < 1 minute? < 15 seconds?

 Access Path Tuning  Advanced Tuning

Summary (continued)

 Access Path Tuning – Bottom line – Do less work (operations) – – – – – Use good table joins – A=B, B=C, C=D not A=B, C=D Use good indexes – Primary Key is best Use literals Hints can help Avoid sorts and functions

Summary (continued)

 Advanced Tuning – Format the query so it is easy to work with – Baselines and explain plans  Get a baseline and explain plan to start out  Get new baselines and explain plans when you make changes  Keep the final baseline and explain plan

Summary (continued)

 Advanced Tuning (continued) – Tune  Table joins –  Function usage  Indexes  Hints – Index – Order – Join Type Most Queries only need one change to bring them in line

Summary (continued)

 We solved two problem queries together!

– It really isn’t that hard – Follow a step-by-step approach to resolving the query – – Remember the goal - < 15 seconds. < 1 minute is OK.

Don’t get caught up with a 5X improvement  5 hours to 1 hour is good  5 hours to 1 15 seconds is better  It is possible!

Summary (continued)

 Your DBA can help – Look at the DB Parameters – – – – – – – Look at the OS and Storage Identify where you are spending time and contention Hints Statistics Complex views and materialized views Stored outlines Beat it with a stick!!!

This Presentation

 This document is not for commercial re-use or distribution without the consent of the author  Neither CRT, nor the author guarantee this document to be error free  Submit questions/corrections/comments to the author: – Charles Pfeiffer, [email protected]