Course code Title Language Price # Unit Startdate Hour Enddate Location Signup
OR2079 Oracle Database 11g : SQL Tuning English €1200.00 3 Day(s) 11-12-2017 09u00 13-12-2017 Kontich Subscribe
OR2079 Oracle Database 11g : SQL Tuning Dutch €1200.00 3 Day(s) 16-04-2018 09u00 18-04-2018 Kontich Subscribe
OR2079 Oracle Database 11g : SQL Tuning on your request on your request Contact Us

Oracle Database 11g : SQL Tuning

Oracle Database 11g : SQL Tuning

Overview

Course code: 
OR2079
Duration: 
3
Time Unit: 
Day(s)
Overview: 

This course will equip database administrators and application developers to build efficient SQL statements and to tune database applications. When this effort is complemented by database server and PL/SQL application tuning, then a highly efficient application execution environment is created. One will learn about the internals of SQL statement execution, how to monitor the performance of such execution, and how one can influence the behavior of the database to achieve performance gains.

Objectives for this course :

• Consider the unique and differing tuning issues found in online database applications, enterprise resource and data warehouse environments and the important metrics of SQL statement performance.
• Learn about the internal mechanisms use for SQL statement execution within a database instance and how these can affect performance for good or bad, including the Optimizer facilities known as the Transformation Engine, Estimator and Plan Generator.
• Use a variety of techniques to examine the details of SQL statement execution, spotting trouble areas and bottlenecks which require tuning.
• Learn about the Auto-Task framework and how to manage the automatic collection of Optimizer statistics and automatic SQL tuning using both the programmatic and Enterprise Manager interfaces.
• Learn how statistic deficiencies can dramatically degrade performance, and how these problems are resolved through customized Optimizer statistics collection procedures using the DBMS_STATS() package, system statistics, histograms, expression statistics and MultiColumn statistics.
• Influence the behavior of the Optimizer by setting database parameters and other SQL tuning techniques.
• Utilize the database advisory framework and the SQL Tuning and SQL Access advisors.
• Use plan management to achieve plan stability which is adaptive and even dynamic.
• Understand the self-tuning infrastructure and the automatic SQL tuning capabilities found within the database.
• Employ SQL hints embedded into the statement text to resolve unique tuning challenges.
• Learn to identify poorly performing SQL statements using real-time SQL monitoring and application tracing techniques such as DBMS_MONITOR(), trcsess and tkprof.

Topics

Topics: 

TUNING & THE ORACLE DATABASE ADVISORY FRAMEWORK
• THE CHALLENGES OF TUNING
• PERFORMANCE METRICS
• MANAGEMENT & ADVISORY FRAMEWORK
• ADDM & AWR
• SQL TUNING PRIVILEGES

VIEWING & MONITORING THE EXECUTION PLAN
• ABOUT THE EXECUTION PLAN
• COLLECTING PERFORMANCE STATISTICS
• VIEWING THE EXECUTION PLAN
• REAL-TIME SQL MONITORING

UNDERSTANDING THE OPTIMIZER
• OPTIMIZATION METHODS
• OPTIMIZATION GOALS
• OPTIMIZER_MODE
• OPTIMIZER_FEATURES_ENABLE
• OPTIMIZER COMPONENTS
• EXECUTION PLAN OPERATIONS

EXECUTION PLAN METHODS & OPERATIONS
• TABLE ACCESS METHODS
• JOIN METHODS
• INDEX OPERATIONS
• DATA OPERATIONS

MANAGING OPTIMIZER STATISTICS
• MORE ABOUT OPTIMIZER STATISTICS
• AUTOMATIC MAINTENANCE TASKS
• MANUALLY GATHERING STATISTICS
• GATHER_TABLE_STATS()
• GATHER_INDEX_STATS()
• GATHER_SCHEMA_STATS()
• GATHER_DATABASE_STATS()
• GATHER_SYS Parameter
• GATHER_DICTIONARY_STATS()
• GATHER_FIXED_OBJECTS_STATS()
• USING HISTORICAL STATISTICS
• DYNAMIC SAMPLING
• LOCKING STATISTICS

ENHANCED OPTIMIZER STATISTICS
• ABOUT OPTIMIZER SYSTEM STATISTICS
• MANAGE SYSTEM STATISTICS
• CREATE_STAT_TABLE(), DROP_STAT_TABLE()
• CREATE_STAT_TABLE()
• DROP_STAT_TABLE()
• GATHER_SYSTEM_STATS()
• GET_SYSTEM_STATS()
• SET_SYSTEM_STATS()
• IMPORT_SYSTEM_STATS(), EXPORT_SYSTEM_STATS()
• IMPORT_SYSTEM_STATS()
• EXPORT_SYSTEM_STATS()
• DELETE_SYSTEM_STATS()
• PENDING & PUBLISHED STATISTICS

HISTOGRAMS & EXTENDED STATISTICS
• WHY ARE HISTOGRAMS NEEDED?
• HISTOGRAMS INTERNAL STRUCTURE
• MANUALLY MANAGING HISTOGRAMS
• EXPRESSION STATISTICS
• MULTICOLUMN STATISTICS

APPLICATION TRACING
• APPLICATION TRACING PACKAGES
• SET_SQL_TRACE()
• TRACE_ENABLE()
• SET_IDENTIFIER()
• EM APPLICATION MONITORING & TUNING
• REAL-TIME MONITORING
• USING TRCSESS & TKPROF

ADDM & THE SQL TUNING ADVISOR
• ADDM PERFORMANCE ANALYSIS
• USING THE SQL TUNING ADVISOR
• AUTOMATIC SQL TUNING
• CONFIGURING AUTOMATIC SQL TUNING

THE SQL ACCESS ADVISOR
• USING THE SQL ACCESS ADVISOR
• SQL ACCESS ADVISOR TEMPLATES
• PERFORMING A QUICK TUNE TASK
• INDEX DATABASE PARAMETERS
• OPTIMIZER_INDEX_COST_ADJ
• OPTIMIZER_INDEX_CACHING
• SKIP_UNUSABLE_INDEXES
• OPTIMIZER_USE_INVISIBLE_INDEXES

PLAN MANAGEMENT
• SQL MANAGEMENT BASE ARCHITECTURE
• LOAD_PLANS_FROM_SQLSET()
• LOAD_PLANS_FROM_CURSOR_CACHE()
• USING PLAN BASELINES
• PLAN MANAGEMENT USING EM

MANAGING CURSOR SHARING
• ABOUT CURSOR SHARING
• BIND VARIABLES & CURSOR SHARING
• THE CURSOR_SHARING PARAMETER
• FINDING CANDIDATES FOR REWRITE

OPTIMIZER HINTS
• WHAT ARE HINTS?
• HINT EXPLANATIONS

Prerequisites

Prerequisites: 

Course OR2069 : Oracle : SQL Basics

Audience

Audience: 

The primary target audiences for this course are:
• Senior application designers and database developers
• PL/SQL developer
• Database administrators
• Web server administrators
• System administrators
• Implementation specialists
• Data center support engineers