|
|
|
|
|
|
Oracle Training Overview
The "Architecture & Internals" volume will reveal the underlying architecture of the Oracle database environment. This course is also intended to be of help to information technology (IT) managers and executives who require an understanding of the technology upon which they and their staff rely. This architectural discussion will be much more than just a presentation of theoretical concepts. After explaining in logical terms the architectural component under consideration, 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 textbook is a mandatory reference for any database administrator or SQL database application developer.
This textbook can be used for advanced self-study, or for instructor-led training in both in-class or virtual-class environments. Textbooks can also be used for ongoing reference long after the course is completed.
Oracle Training Objectives
The "Architecture & Internals" volume covers the following specific topics:
- Understanding the broader Oracle enterprise architecture, including the client, middleware and database tiers found within a multi-tiered enterprise environment.
- Understand key computing concepts such as parallelization, grid-based resource acquisition and clusters.
- Understanding the role of database machines, such as the Exadata database computing machines and storage machines.
- An in-depth exploration of the database architecture, including memory, process and data structures, and the composition of an Oracle database instance.
- Alternative modes of database operation, including dedicated server mode, shared servers mode, parallel execution and how one selects the appropriate mode for each environment.
- Consider the role of the Shared Global Area (SGA) and the various memory caches and subcaches which can be optionally configured within that space.
- Consider the complimentary role of the Program Global Area (PGA), also known as the Process Global Area.
- Discuss the role of the instance background processes, including those which are optional and those which can have additional instances optionally launched.
- Consider physical database storage structures such as tablespaces, data files, blocks and the internal format of rows.
- Consider the alternative row-level storage option of hybrid columnar compression.
- Compare & contrast internal storage of data segments, B-tree and bitmap index segments.
- Review all of the possible database object types which one may find within the logical application database.
- The schema and ownership context in which logical database objects reside.
- The editions context in which database objects can exist in different forms and how hot application upgrades are supported by database editions.
The "SQL Tuning" volume covers the following specific topics:
- 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.
Oracle Training 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
Oracle Training Certification
This course covers information relevant for the certification test EXAM 1Z0-054: ORACLE DATABASE 11G R2: PERFORMANCE TUNING.
Oracle Training Prerequisites
Specific prerequisites for this course are the following titles, or equivalent experience:
- DATA MODELING: LOGICAL DATABASE DESIGN
- ORACLE DATABASE 11G R2: SQL FUNDAMENTALS – COMPLETE LIBRARY
- ORACLE DATABASE 11G R2: THE COMPLETE INSTALLATION GUIDE FOR LINUX & WINDOWS
Suggested Next Courses
You may wish to consider the following courses which are also related to the subject of Oracle database performance tuning:
- ORACLE DATABASE 11G R2: RESOURCE MANAGER & SCHEDULER
- ORACLE DATABASE 11G R2: ADVANCED PL/SQL PROGRAMMING & TUNING
- ORACLE DATABASE 11G R2: PERFORMANCE TUNING
- ORACLE DATABASE 11G R2: IMPLEMENT PARALLEL SQL & PARTITIONING
Oracle Training Course duration
5 Days
Oracle Training Course outline
ARCHITECTURE & INTERNALS
THE BROADER ORACLE ENTERPRISE ARCHITECTURE
- ABOUT ENTERPRISE ARCHITECTURES
- ABOUT PARALLELIZATION
- SMP
- THE DATABASE SERVER STACK
- THE DATABASE SERVER SOFTWARE
- V$VERSION
EXPLORING THE ORACLE DATABASE INSTANCE
- DATABASE INSTANCE CONFIGURATIONS
- ABOUT THE SGA
- Using V$SGA_DYNAMIC_COMPONENTS
- ABOUT THE PGA
- MANAGING INSTANCE MEMORY
EXPLORING THE INSTANCE PROCESSES
- INSTANCE BACKGROUND PROCESSES
- SQL EXECUTION PROCESSES
- Using V$SESSION
- About The PGA
- SQL STATEMENT EXECUTION
EXPLORING LOGICAL STORAGE: DATABASE OBJECTS
- APPLICATION DATABASE OBJECTS
- SCHEMA & OWNERSHIP CONTEXT
- EDITIONS CONTEXT & REDEFINITION
EXPLORING PHYSICAL STORAGE: DATABASE FILES
- ABOUT THE DATABASE FILES
- DATABASE SERVER PARAMETER FILE
- CONTROL FILES
- ALERT LOG & TRACE FILES
EXPLORING PHYSICAL STORAGE: TABLESPACES
- TABLESPACES & DATA FILES
- AUTOEXTEND Options
- ABOUT TEMPORARY SEGMENTS
- ABOUT INDEX SEGMENTS
SQL TUNING
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
|
|
|
|
|
