Oracle DBA
Advanced Topics
© 2007-2016
by Burleson Corporation
* Learn SQL
Optimization.
* Look inside Oracle
Optimizers.
* Learn SGA
Tuning.
* Understand advanced
database administration.
|
|
Oracle DBA Advanced topics is an intensive three day
course intended to introduce the Oracle DBA to system internals and
advanced database administration. Topics will focus on SQL
optimization internals, SGA internals, and advanced DBA
administration and tuning topics.
 |
|
Oracle High-Performance SQL Tuning
Donald K. Burleson
Oracle Press, 2002
ISBN: 0072190582
|
|
|
|
 |
|
Creating a
Self-Tuning Oracle Database
Automating Oracle9i
Dynamic SGA Performance
Donald
K. Burleson
Rampant TechPress,
2007
ISBN:
0-9727513-2-7
|
This course is designed for practicing
Oracle professionals who have basic experience with Oracle database
administration and wish to advance their skills in advanced
topics.
|
This course was designed by Donald K.
Burleson, an acknowledge leader in Oracle database
administration. Author or more than 20 database books,
Burleson was chosen by Oracle Press to write five authorized
editions, including Oracle High-Performance SQL
tuning. Burleson has over a decade of real-world DBA
experience in Oracle features and shares his Oracle secrets in this
intense Oracle training.
Oracle DBA Advanced Topics
Syllabus
© 2007-2016 by Donald K.
Burleson
|
Day 1: SQL
Optimization
Inside the Oracle
Optimizers
- The “best” execution plan
(first_rows vs. all_rows) - (page 129-130)
- Rule-based optimization -
(page 129)
- Cost-based optimization - (page
334)
- Gathering statistics -
(page 340)
- Using histograms - (page 279,
305)
- When the optimizers fail
- (page 85)
- Exercise – Change the optimizer
mode
Table joining
internals
- Sort-merge joins - (page
341)
- Nested Loop joins -
(page
401)
- Hash joins - (page
390-392)
- STAR joins - (page
401)
- Bitmap joins - (page
291)
- Exercise – Change
table join techniques & evaluate
performance
Oracle Index
Optimization
- B-Tree indexes -
(page
233)
- Bitmap Indexes - (page 80,
127)
- Function-based
Indexes - (page 36,
61)
- Clustered indexes - (page
87)
- Index-only
tables - (page
468)
- Exercise – Create and use bitmap
index
Day 2: SGA
Tuning
Inside the Shared
pool
- Shared Pool
Internals
- Using the library
cache
- Using cursor
sharing
- Implementing optimizer
plan stability
- Monitoring the shared
pool
- Monitoring the library
cache
- Using shared pool
advice
|
|
Oracle sorting
internals
- Sorting vs. index
access
- Sorting in
sort_area_size
- Sorting with the multi-threaded
server
- sorting with
pga_aggregate_target
Oracle data buffer
management
- Internals of data buffer
RAM
- Using the KEEP
pool
- Using the RECYCLE
pool
- Defining multiple blocksize
buffers
- Monitoring the data
buffers
Day 3: Advanced database
administration
Inside the Data
Dictionary structure
- Querying against the
fixed x$ tables
- Queries with the v$
views
- Queries using the DBA
dictionary views
Oracle monitoring with
STATSPACK
- Basics of proactive
tuning
- Installing
STATSPACK
- Collecting data with
STATSPACK
- Using STATSPACK for exception
reporting
- Using STATSPACK for trend
analysis
Oracle
Internals
- Automated segment space management (bitmap
freelists)
- Using multiple freelists and freelist
groups
- Managing space
fragmentation
- Using online
reorgs
- defining external tables to read OS flat
files
- Creating a self-tuning
database
- Using multiple
blocksizes
- Using advanced indexing (function-based and bitmap
indexes)
|
Please note that while the knowledge gained from this Oracle
training may be valuable when preparing for Oracle certification
exams, the content of this course is not for Oracle Certification,
including the Certified Professional (OCP) or Oracle Certified
Associate (OCA) programs.
This is a BC Oracle training
course (c) 2007-2016
|
