메뉴 건너뛰기

Korea Oracle User Group

Guru's Articles

SQL Tuning Workshop

명품관 2020.02.20 17:18 조회 수 : 132

SQL Tuning Workshop

Last week I had the pleasure of delivering a five-part SQL Tuning Workshop for my local Oracle User Group –  Northern California Oracle User Group. The workshop explains the fundamentals of the cost-based optimizer, the statistics that feed it, the hints that influence it and key tools you need to exam executions plans.

The workshop also provides a methodology for diagnosing and resolving the most common SQL execution performance problems. Given the volume of interest in this content, I want to share all of the material from the workshop here and give you links to additional material on each of the 5 topics.

Part 1 Understanding the Optimizer
The first part of the workshop covers the history of the Oracle Optimizer and explains the first thing the Optimizer does when it begins to optimize a query – query transformation.

Query transformations or the rewriting of the SQL statement into a semantically equivalent statement allows the Optimizer to consider alternative methods of processing or executing that query, which are often more efficient than the original SQL statement would allow. the majority of Oracle’s query transactions are now cost based, which means the Optimizer will cost the plan with and with the query transformation and pick the plan with the lowest cost. With the help of the Optimizer development team, I’ve already blogged about a number of these transformations including:

 

Part 2 Best Practices for Managing Optimizer Statistics
Part 2 of the workshop focuses on Optimizer Statistics and the best practices for managing them, including when and how to gather statistics, including fixed object statistics.

It also covers what additional information you may need to give the Optimizer such as histogramsextended statistics, as well as all of the techniques you can use to speed up statistics gathering including taking advantage of Incremental statisticsparallelism and concurrency. Finally we look at some guidance on when not to gather statistics. A lot of the topics covered in this presentation are discussed in details in the following two white papers:

 

Part 3 Explain the Explain Plan
Part 3 of the workshop examines the different aspects of an execution plan, from cardinality estimates to parallel execution and explains what information you should be gleaming from the plan and how it affects the execution. It offers insight into what caused the Optimizer to make the decision it did as well as a set of corrective measures that can be used to improve each aspect of the plan.

More information on displaying and reading execution plans can be found in my previous blog posts on DBMS_XPLAN.DISPLAY_CURSOR and using SQL Monitor. Or in the white paper Explain the Explain Plan.

 

Part 4 Influencing Execution Plans with Optimizer Hints
Part 4 is called “Harnessing the power of optimizer hints”. Although I am not a strong supporter of adding hints to SQL statements for a whole host of reasons, from time to time, it may become necessary to influence the plan the Optimizer chooses. The most powerful way to alter the plan chosen is via Optimizer hints. But knowing when and how to use Optimizer hints correctly is somewhat of a dark art. This session explains how Optimizer hints are interpreted, when and where they should be used, and why they sometimes appear to be ignored.

Part 5 SQL Tuning Tips and Tricks
The final part of the SQL Tuning workshop focuses on applying the techniques discussed in the previous sections to help diagnose and correct a number of problematic SQL statements and shows how you can use SQL Plan Management  or a SQL Patch to influence an execution plan.

Hope you find the materials useful!

 

출처 : https://sqlmaria.com/2020/02/19/sql-tuning-workshop/

번호 제목 글쓴이 날짜 조회 수
공지 Guru's Article 게시판 용도 ecrossoug 2015.11.18 144
40 SQL Window Functions Cheat Sheet 명품관 2020.05.26 72
» SQL Tuning Workshop 명품관 2020.02.20 132
38 New initialization parameters, data dictionary views & dynamic performance views in Oracle Database 19c 명품관 2019.02.08 964
37 More New Features in Oracle Database 19c 명품관 2019.02.08 333
36 New Features of Performance Tuning in Oracle Database 19c 명품관 2019.02.08 456
35 New Features of RAC & ASM in Oracle 19c 명품관 2019.02.08 541
34 New Features of Data Guard in Oracle Database 19c 명품관 2019.02.08 382
33 New Features of Security in Oracle Database 19c 명품관 2019.02.08 2319
32 Patch conflicts 명품관 2019.02.07 310
31 New Features in Oracle Multitenant 19c 명품관 2019.02.07 403
30 New Features of Backup & Recovery in Oracle Database 19c 명품관 2019.02.07 182
29 New Features in Oracle Database 19c 명품관 2019.02.02 299
28 Different MOS Notes for xTTS PERL scripts – Use V4 scripts 명품관 2019.01.29 74
27 V$EVENT_NAME 뷰의 Name 컬럼에 정의된 event name에서 오는 오해 명품관 2017.03.08 178
26 Can I apply a BP on top of a PSU? Or vice versa? 명품관 2016.06.01 223
25 How to change the database name in 12c 명품관 2016.05.31 238
24 How to Recover Data (Without a Backup!) 명품관 2016.05.11 265
23 On ROWNUM and Limiting Results (오라클 매거진 : AskTom) 명품관 2016.04.28 270
22 DDL Logging - 12c (조나단 루이스) 명품관 2016.04.26 1368
21 11.1.0.6 부터 지원하는 Oracle Online Patching New Feature 명품관 2016.04.22 338
위로