title
Please take a moment to fill out this form. We will get back to you as soon as possible.
All fields marked with an asterisk (*) are mandatory.
Oracle Database 11g: SQL Tuning
Duration
3.5 Days
Course
DBOR-932
Available Formats
Classroom Training, Online Training
Course Description
Overview
This Oracle Database 11g: SQL Tuning course is designed to equip database administrators and application developers with the skills needed to build efficient SQL statements and to tune database applications. Students 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.This course covers information relevant for the certification test EXAM 1Z0-054: ORACLE DATABASE 11G R2: PERFORMANCE TUNING.
Objectives
Upon completion of the Oracle Database 11g: SQL Tuning course, students will be able to:
- 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.
- Become trained in 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.
- Demonstrate 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.
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.
Prerequisites
- ORACLE DATABASE 11G R2: SQL FUNDAMENTALS – COMPLETE LIBRARY (DBOR-917)
- ORACLE DATABASE 11G R2: ARCHITECTURE & INTERNALS (DBOR-931)
Topics
- THE challenges of tuning
- PERFORMANCE metrics
- MANAGEMENT & advisory framework
- ADDM & AWR
- SQL tuning privileges
- ABOUT the execution plan
- COLLECTING performance statistics
- VIEWING the execution plan
- REAL-TIME SQL monitoring
- OPTIMIZATION methods
- OPTIMIZATION goals
- OPTIMIZER_MODE
- OPTIMIZER_FEATURES_ENABLE
- OPTIMIZER components
- EXECUTION plan operators
- TABLE access methods
- JOIN methods
- INDEX operations
- DATA operations
- 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_DATABASE_STATS()
- USING historical statistics
- DYNAMIC sampling
- LOCKING 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
- WHY are histograms needed?
- HISTOGRAMS internal structure
- MANUALLY managing histograms
- EXPRESSION statistics
- MULTICOLUMN statistics
- APPLICATION tracing packages
- SET_SQL_TRACE()
- TRACE_ENABLE()
- SET_IDENTIFIER()
- EM application monitoring & tuning
- REAL-TIME monitoring
- USING TRCSESS & TKPROF
- ADDM performance analysis
- USING the SQL tuning advisor
- AUTOMATIC SQL tuning
- CONFIGURING automatic SQL tuning
- USING the SQL access advisor
- SQL access advisor templates
- PERFORMING a quick tune task
- USING the SQL access advisor
- INDEX database parameters
- OPTIMIZER_INDEX_COST_ADJ
- OPTIMIZER_INDEX_CACHING
- SKIP_UNUSABLE_INDEXES
- OPTIMIZER_USE_INVISIBLE_INDEXES
- SQL management base architecture
- LOAD_PLANS_FROM_SQLSET()
- LOAD_PLANS_FROM_CURSOR_CACHE()
- USING plan baselines
- PLAN management using EM
- ABOUT cursor sharing
- BIND variables & cursor sharing
- THE cursor_sharing parameter
- FINDING candidates for rewrite
- WHAT are hints?
- HINT explanations
Related Courses
-
Oracle Database 11g R2 SQL Complete
DBOR-917- Duration: 5 Days
- Delivery Format: Classroom Training, Online Training
- Price: 3,500.00 USD
-
Oracle Database 11g: Advanced PL/SQL Programming & Tuning
DBOR-922- Duration: 5 Days
- Delivery Format: Classroom Training, Online Training
- Price: 3,500.00 USD
Self-Paced Training Info
Learn at your own pace with anytime, anywhere training
- Same in-demand topics as instructor-led public and private classes.
- Standalone learning or supplemental reinforcement.
- e-Learning content varies by course and technology.
- View the Self-Paced version of this outline and what is included in the SPVC course.
- Learn more about e-Learning
Course Added To Shopping Cart
bla
bla
bla
bla
bla
bla
Self-Paced Training Terms & Conditions
??spvc-wbt-warning??
Exam Terms & Conditions
??exam-warning??
??group-training-form-area??
??how-can-we-help-you-area??
??personalized-form-area??
??request-quote-area??
Sorry, there are no classes that meet your criteria.
Please contact us to schedule a class.
STOP! Before You Leave
Save 0% on this course!
Take advantage of our online-only offer & save 0% on any course !
Promo Code skip0 will be applied to your registration
Nothing yet
Purchase Information
??elearning-coursenumber?? ??coursename??
View Cart
title
Please take a moment to fill out this form. We will get back to you as soon as possible.
All fields marked with an asterisk (*) are mandatory.