Oracle SQL Tuning

1 vote, average: 5.00 out of 51 vote, average: 5.00 out of 51 vote, average: 5.00 out of 51 vote, average: 5.00 out of 51 vote, average: 5.00 out of 5
Please Log in or register to rate

Oracle SQL Tuning


This training explores different methods for tuning and identifying inefficient SQL queries. In this course students learn how to retrieve details about the Oracle database execution plan, and to determine the root causes of inefficient SQL statements. In this course, each module is reinforced with structured hands-on exercises.


Target Audience: 
This course is mainly intended for Database Administrators, Database Developers, Business Intelligence professionals, QA professionals, Data Analysts, and other roles responsible for tuning SQL statements.

– Familiarity with PL/SQL is recommended but not mandatory
– Familiarity with the SQL language concepts and syntax
– Basic working knowledge of administering Oracle databases

Course Topics

Module 1 – SQL Statement Processing:
Reuse of Execution Plans, Use of Bind Variables within PL/SQL program (Implicit Bind Variables), Use of Bind Variables within Dynamic SQL (Explicit Bind Variables), SQL Statement Processing Phases, Shared Cursor, V$LIBRARYCACHE, V$SQLAREA, Adaptive Cursor Sharing in Oracle Database 11g.

EXPLAIN PLAN Command, using plan table, using dbms_xplan.display, SQL*Plus AUTOTRACE, dbms_xplan.display_cursor, AUTOTRACE vs  DBMS_XPLAN.DISPLAY_CURSOR

Module 3 – SQL Trace and TKPROF:
Enable / Disable Tracing, Using ALTER SESSION / INSTANCE, Formatting Your Trace Files – TKPROF, How to identify my trace file ?, Interpreting the results.

Module 4 – Introduction to the Oracle Optimizer:
Functions of the Oracle Optimizer, Steps in Optimizer Operations, Choice of optimizer approaches – Rule Based Optimizer / Cost Based Optimizer, Optimizer Mode (RULE / CHOOSE / FIRST_ROWS_n /ALL ROWS  ), Choice of access paths, OPTIMIZER_FEATURES_ENABLE, OPTIMIZER_INDEX_COST_ADJ.

Module 5 – Basic Index Concepts:
Selectivity and Cardinality, The Clustering Factor, Indexes and Performance, Indexes and Data Dictionary, Suppressing Indexes, Using Functions, Comparing Mismatched Data Types, Concatenated Indexes, BTREE Indexes.

Module 6 – Optimizer Statistics:
Gathering Statistics, Retention Period (Historical stats), Histograms – column statistics, Histogram Types, Estimating the statistics – Schema level and Data Dictionary Info, GATHER STALE, DYNAMIC SAMPLING, Segment level statistics, System statistics, Index statistics, export/import stats, Pending stats, Optimizer advanced options

Module 7 – Influencing the Optimizer(Hints):
Hints for query transformations, Hints for optimization approaches and goals, Hints for access paths, Hints for Join Orders, Hints for Join Operation, Hints for Parallel Execution, Other hints.

Module 8 – Sorts and Joins:
SORTS – Related Parameters, Monitoring Sort Operations, Temporary Tablespace, Work Area Usage, V$SQL_WORKAREA_HISTOGRAM, Avoiding Sort Operations, JOINS – Nested Loops, Hash Joins, Sort/Merge Joins, Use the 10053 Event to Trace CBO Choices, SubQueries, IN vs EXISTS, anti-join, STAR JOIN.

Module 9 – Plan Stability (Outlines):
Creating a stored outline, Automatic method / Manual Method, Use stored_outlines at the session / system level, Drop outline.

Module 10 – Advanced Indexes:
Bitmap Indexes VS B-tree Indexes, BITMAP and DML.

Module 11 – Materialized Views:
Introduction, ENABLE QUERY REWRITE, Stale information in the Materialized view, query_rewrite_integrity, Refreshing the Materialized View.

Module 12 – Index Organized Tables:
Storage – IOT vs. Heap Tables, ROWID – IOT vs. Heap Tables, Advantages of an IOT, IOT Disadvantages, OVERFLOW, PCTTHRESHOLD, Mapping table.

Detailed Course Outline

Oracle SQL Tuning detailed syllabus

© Copyright - Skilit - Site by Dweb