714. Oracle
10g/11g SQL Tuning
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.
Learning
Objectives
· 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
Target
Audience
· Senior
application designers and database developers
· PL/SQL
developer
· Database
administrators
· Web server
administrators
· System
administrators
· Implementation
specialists
· Data center
support engineers
Course
Outline
1. Tuning &
the oracle database advisory framework
a. The
challenges of tuning
b. Performance
metrics
c. Management
& advisory framework
d. Addm & awr
e. SQL tuning
privileges
2. Viewing &
monitoring the execution plan
a. About the
execution plan
b. Collecting
performance statistics
c. Viewing the
execution plan
d. Real-time
SQL monitoring
3. Understanding
the optimizer
a. Optimization
methods
b. Optimization
goals
c. Optimizer_mode
d. Optimizer_features_enable
e. Optimizer
components
f. Execution
plan operations
4. Execution
plan methods & operations
a. Table
access methods
b. Join
methods
c. Index
operations
d. Data
operations
5. Managing
optimizer statistics
a. More about
optimizer statistics
b. Automatic
maintenance tasks
c. Manually
gathering statistics
d. Gather_table_stats()
e. Gather_index_stats()
f. Gather_schema_stats()
g. Gather_database_stats()
h. Gather_sys parameter
i. Gather_dictionary_stats()
j. Gather_fixed_objects_stats()
k. Using
historical statistics
l. Dynamic
sampling
m. Locking
statistics
6. Enhanced
optimizer statistics
a. About
optimizer system statistics
b. Manage
system statistics
c. Create_stat_table(), drop_stat_table()
d. Create_stat_table()
e. Drop_stat_table()
f. Gather_system_stats()
g. Get_system_stats()
h. Set_system_stats()
i. Import_system_stats()
7. Export_system_stats()
a. Import_system_stats()
b. Export_system_stats()
c. Delete_system_stats()
d. Pending &
published statistic
8. Histograms
& extended statistics
a. Why are
histograms needed?
b. Histograms
internal structure
c. Manually
managing histograms
d. Expression
statistics
e. Multicolumn
statistics
9. Application
tracing
a. Application
tracing packages
b. Set_sql_trace()
c. Trace_enable()
d. Set_identifier()
e. Em application monitoring & tuning
f. Real-time
monitoring
g. Using trcsess & tkprof
10. Addm & the sql tuning advisor
a. Addm performance analysis
b. Using the
SQL tuning advisor
c. Automatic
SQL tuning
d. Configuring
automatic SQL tuning
11. The
SQL access advisor
a. Using the
SQL access advisor
b. SQL access
advisor templates
c. Performing
a quick tune task
d. Index
database parameters
e. Optimizer_index_cost_adj
f. Optimizer_index_caching
g. Skip_unusable_indexes
12. Plan
management
a. SQL
management base architecture
b. Load_plans_from_sqlset()
c. Load_plans_from_cursor_cache()
d. Using plan
baselines
13. Managing
cursor sharing
a. About
cursor sharing
b. About
cursor sharing
c. Bind
variables & cursor sharing
d. The cursor_sharing parameter
14. Optimizer
hints
a. What are
hints?
b. Hint explanations