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