Course Overview
This Oracle Database 11g R2: SQL Tuning, Architecture & Internals course is designed to provide students with an understanding of the underlying architecture of the Oracle database environment. It is also intended to help information technology (IT) managers and executives who require an understanding of the technology upon which they and their staff rely. This course module will frequently show the component as it is actually rendered by the Enterprise Manager graphical interface for database administrators. At other times this course will use the SQL command-line interface to explain the characteristics of the architectural component being discussed.
The ‘SQL Tuning’ volume 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.
This course covers information relevant for the certification test EXAM 1Z0-054: ORACLE DATABASE 11G R2: PERFORMANCE TUNING.
What you’ll learn
Upon completion of the Oracle Database 11g R2: SQL Tuning, Architecture & Internals course, students will be able to:
- Understand the component as it is actually rendered by the Enterprise Manager graphical interface for database administrators.
- Use the SQL command-line interface to explain the characteristics of the architectural component being discussed.
- Build efficient SQL statements and tune database applications.
- Learn about and monitor the performance of SQL statement execution.
- Influence the behavior of the database to achieve performance gains.
Requirements
- Data Modeling: Logical Database Design
- Oracle Database 11g R2: SQL Fundamentals - Complete Library (DBOR-917)
- Oracle Virtualization & Linux Administration QuickStart (DBOR-963)
Target audiences
- 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 and Data center support engineers.
Curriculum
- 1 Section
- 19 Lessons
- 5 Weeks
- Topics19
- 1.1ARCHITECTURE & INTERNALS THE BROADER ORACLE ENTERPRISE ARCHITECTURE ABOUT enterprise ABOUT parallelization SMP THE database server stack THE database server software V$VERSION
- 1.2EXPLORING THE ORACLE DATABASE INSTANCE DATABASE instance configurations ABOUT the SGA USING V$SGA_DYNAMIC_COMPONENTS ABOUT the PGA MANAGING instance memory
- 1.3EXPLORING THE INSTANCE PROCESSES INSTANCE background processes SQL execution processes USING V$SESSION ABOUT the PGA SQL statement execution
- 1.4EXPLORING LOGISTICAL STORAGE: DATABASE OBJECTS APPLICATION database objects SCHEMA & ownership context EDITIONS context & redefinition
- 1.5EXPLORING PHYSICAL STORAGE: DATABSE FILES ABOUT the database files DATABSE server parameter file CONTROL files ALERT log & trace files
- 1.6EXPLORING PHYSICAL STORAGE: TABLESPACES TABLES & data files AUTOEXTEND options ABOUT temporary segments ABOUT index segments
- 1.7SQL TUNING TUNING & THE ORACLE DATABASE ADVISORY FRAMEWORK THE challenges of tuning PERFORMANCE metrics MANAGEMENT & advisory framework ADDM & AWR SQL tuning privileges
- 1.8VIEWING & MONITORING THE EXECUTION PLAN ABOUT the execution plan COLLECTING performance statistics VIEWING the execution plan REAL-TIME SQL monitoring
- 1.9UNDERSTANDING THE OPTIMIZER OPTIMIZATION methods OPTIMIZATION goals OPTIMIZER_MODE OPTIMIZER_FEATURES_ENABLE OPTIMIZER components EXECUTION plan operations
- 1.10EXECUTION PLAN METHODS & OPERATIONS TABLE access methods JOIN methods INDEX operations DATA operations
- 1.11MANAGING OPTIMIZER STATISTICS MORE about optimizer statistics AUTOMATIC maintenance tasks MANUALLY gathering statistics GATHER_TABLE_STATS() GATHER_INDEX_STATS() GATHER_SCHEMA_STATS() GATHER_DATABASE_STATS() USING historical statistics DYNAMIC sampling LOCKING statistics
- 1.12ENHANCED 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
- 1.13HISTOGRAMS & EXTENDED STATISTICS WHY are histograms needed? HISTOGRAMS internal structure MANUALLY managing histograms EXPRESSION statistics MULTICOLUMN STATISTICS
- 1.14APPLICATION TRACING APPLICATION tracing packages SET_SQL_TRACE() TRACE_ENABLE() SET_IDENTIFIER() EM application monitoring & tuning REAL-TIME monitoring USING TRCSESS & TKPROF
- 1.15ADDM & THE SQL TUNING ADVISOR ADDM performance analysis USING the SQL tuning advisor AUTOMATIC SQL tuning CONFIGURING automatic SQL tuning
- 1.16THE 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
- 1.17PLAN MANAGEMENT SQL management base architecture LOAD_PLANS_FROM_SQLSET() LOAD_PLANS_FROM_CURSOR_CACHE() USING plan baselines PLAN management using EM
- 1.18MANAGING CURSOR SHARING ABOUT cursor sharing BIND variables & cursor sharing THE cursor_sharing parameter FINDING candidates for rewrite
- 1.19OPTIMIZER HINTS WHAT are hints? HINT explanations
Instructor
