Oracle Tuning: The Definitive Reference
Customer Reviews: 2 Average Customer Rating:   Write a Review and tell the world about this title! People who purchase this book frequently purchase: Books on similar topics, in best-seller order:Books from the same publisher, in best-seller order:
Oracle10g has become the most complex database ever created. Incorporating the
principles of artificial intelligence, Oracle10g has developed a sophisticated
mechanism for capturing and tracking database performance over time periods. This
new complexity has introduced dozens of new v$ and DBA views, plus dozens of Automatic
Workload Repository (AWR) tables.
The AWR and its interaction with the Automatic Database Diagnostic Monitor
(ADDM) is a revolution in database tuning. By understanding the internal workings
of the AWR tables, the senior DBA can develop time-series tuning models to predict
upcoming outages and dynamically change the instance to accommodate the impending
resource changes.
This is not a book for beginners. Targeted at the senior Oracle DBA, this book
dives deep into the internals of the v$ views, the AWR table structures and
the new DBA history views. Packed with ready-to-run scripts, you can quickly
monitor and identify the most challenging performance issues.
- See how Oracle captures time-series performance data with AWR.
- Learn techniques for developing performance signatures over time.
- Use pre-written scripts for proactive time-series Oracle tuning.
- Understand how to interpret Oracle tuning metrics with the DBA_HIST views.
- Create customized performance alerts using AWR information.
- See how AWR allows intelligent Oracle performance optimization.
- Learn why the Oracle wait interface and 10046 dumps become obsolete
Table of Contents
CHAPTER 1 - Introduction to Oracle Tuning
Environmental review
Server Review
Network Review
Disk Review
Verifying RAID
Verifying Direct I/O
Instance Tuning
Object Tuning
SQL Tuning
CHAPTER 2 - Introduction to time-series tuning
Principles of Proactive Tuning
Developing signatures
Server Review
CHAPTER 3 - The Oracle10g Automated Workload Structures
New v$ views
New WR$ Views
-
WRM$ tables store metadata information for the Workload Repository.
-
WRH$ tables store historical data or snapshots.
-
WRI$ tables store data related to advisory functions.
How the AWR captures runtime metrics
CHAPTER x - Writing AWR Scripts
CHAPTER x - Server Tuning
CHAPTER x - Network Tuning
CHAPTER x - Disk Tuning
CHAPTER x - Instance Tuning
CHAPTER x - Object Tuning
CHAPTER x - SQL Tuning
CHAPTER x - Tuning with OPQ
CHAPTER x - Monitoring with AWR
CHAPTER x - Trend Analysis using AWR
CHAPTER x - Developing Predictive models with AWR
CHAPTER x - Oracle 10g wait event tuning
The 10046 wait interface becomes obsolete
v$session_wait
v$session_wait_history
v$active_session_history
v$waitclassmetric_history
v$system_wait_class
v$event_histogram
v$eventmetric
WRH wait event tables
wrh$_event_name
wrh$_system_event
wrh$_system_event_bl
wrh$_bg_event_summary
wrh$_waitclassmetric_history
wrh$active_session_history
Oracle10g DBA views for historical wait events.
dba_hist_waitclassmet_history
dba_hist_system_event
dba_hist_bg_event_summary
CHAPTER 6 - Oracle10g v$ SQL Views
go$sql_bind_capture
o$sql_bind_capture
v$client_stats
v$event_histogram
v$event_name
v$eventmetric
v$filemetric
v$filemetric_history
v$filestat
v$metricgroup
v$metricname
v$mystat
v$osstat
v$serv_mod_act_stats
v$service_event
v$service_stats
v$service_wait_class
v$session_event
v$session_wait_class
v$sessmetric
v$sesstat
v$sql
v$sql_bind_data
v$sql_bind_metadata
v$sql_cursor
v$sql_optimizer_env
v$sql_plan
v$sql_plan_statistics
v$sql_plan_statistics_all
v$sql_redirection
v$sql_shared_cursor
v$sql_shared_memory
v$sql_workarea
v$sql_workarea_active
v$sql_workarea_histogram
v$sqlarea
v$sqltext
v$sqltext_with_newlines
v$statistics_level
v$statname
v$svcmetric
v$svcmetric_history
v$sysmetric
v$sysmetric_history
v$sysmetric_summary
v$sysstat
v$system_event
v$system_wait_class
CHAPTER 7 - Historical AWR data Tables
wrh$_waitclassmetric_history
wrh$_active_session_history
wrh$_active_session_history_bl
wrh$_bg_event_summary
wrh$_event_name
wrh$_metric_name
wrh$_sessmetric_history
wrh$_sys_time_model
wrh$_sys_time_model_bl
wrh$_sysmetric_history
wrh$_sysmetric_summary
wrh$_sysstat
wrh$_sysstat_bl
wrh$_system_event
wrh$_system_event_bl
Other history tables
wrh$_buffer_pool_statistics
wrh$_datafile
wrh$_db_cache_advice
wrh$_db_cache_advice_bl
wrh$_dlm_misc
wrh$_enqueue_stat
wrh$_enqueue_stat_bl
wrh$_filemetric_history
wrh$_filestatxs
wrh$_filestatxs_bl
wrh$_instance_recovery
wrh$_java_pool_advice
wrh$_latch
wrh$_latch_bl
wrh$_latch_children
wrh$_latch_children_bl
wrh$_latch_misses_summary
wrh$_latch_misses_summary_bl
wrh$_latch_name
wrh$_latch_parent
wrh$_latch_parent_bl
wrh$_librarycache
wrh$_log
wrh$_mttr_target_advice
wrh$_optimizer_env
wrh$_osstat
wrh$_parameter
wrh$_parameter_bl
wrh$_parameter_name
wrh$_pga_target_advice
wrh$_pga_target_advice_bl
wrh$_pgastat
wrh$_pgastat_bl
wrh$_recovery_file_dest_stat
wrh$_resource_limit
wrh$_rman_performance
wrh$_rollstat
wrh$_rowcache_summary
wrh$_rowcache_summary_bl
wrh$_seg_stat
wrh$_seg_stat_bl
wrh$_seg_stat_obj
wrh$_sga
wrh$_sgastat
wrh$_sgastat_bl
wrh$_shared_pool_advice
wrh$_sql_plan
wrh$_sql_summary
wrh$_sql_workarea_histogram
wrh$_sqlbind
wrh$_sqlbind_bl
wrh$_sqlstat
wrh$_sqlstat_bl
wrh$_sqltext
wrh$_stat_name
wrh$_tablespace_space_usage
wrh$_tablespace_stat
wrh$_tablespace_stat_bl
wrh$_tempfile
wrh$_tempstatxs
wrh$_thread
wrh$_undostat
wrh$_waitstat
wrh$_waitstat_bl
CHAPTER 8 - Time Model Tables & Views
v$sys_time_model
v$sess_time_model
wrh$_sys_time_model
wrh$_sys_time_model_bl
dba_hist_sys_time_model
CHAPTER 9 - Oracle 10g Metrics Tables
v$eventmetric
v$waitclassmetric
v$waitclassmetric_history
v$metricgroup
v$metricname
v$sysmetric
v$sessmetric
v$filemetric
v$eventmetric
v$waitclassmetric
v$svcmetric
v$sysmetric_history
v$filemetric_history
v$waitclassmetric_history
v$svcmetric_history
v$sysmetric_summary
Metadata Tables
wrm$_baseline
wrm$_database_instance
wrm$_snap_error
wrm$_snapshot
wrm$_wr_control
CHAPTER 10 - DBA History Views
DBA Alert Tables
dba_hist_sysmetric_history
dba_hist_sysmetric_summary
dba_hist_sessmetric_history
dba_hist_filemetric_history
dba_hist_waitclassmet_history
CHAPTER 11 - DBA AWR History Views
dba_hist_database_instance
dba_hist_snapshot
dba_hist_snap_error
dba_hist_baseline
dba_hist_wr_control
dba_hist_datafile
dba_hist_filestatxs
dba_hist_tempfile
dba_hist_tempstatxs
dba_hist_sqlstat
dba_hist_sqltext
dba_hist_sql_summary
dba_hist_sql_plan
dba_hist_sqlbind
dba_hist_optimizer_env
dba_hist_event_name
dba_hist_system_event
dba_hist_bg_event_summary
dba_hist_waitstat
dba_hist_enqueue_stat
dba_hist_latch_name
dba_hist_latch
dba_hist_latch_children
dba_hist_latch_parent
dba_hist_latch_misses_summary
dba_hist_librarycache
dba_hist_db_cache_advice
dba_hist_buffer_pool_stat
dba_hist_rowcache_summary
dba_hist_sga
dba_hist_sgastat
dba_hist_pgastat
dba_hist_resource_limit
dba_hist_shared_pool_advice
dba_hist_sql_workarea_hstgrm
dba_hist_pga_target_advice
dba_hist_instance_recovery
dba_hist_java_pool_advice
dba_hist_thread - logswitches
dba_hist_stat_name
dba_hist_sysstat
dba_hist_sys_time_model
dba_hist_osstat_name
dba_hist_osstat
dba_hist_parameter_name
dba_hist_parameter
dba_hist_undostat
dba_hist_rollstat
dba_hist_seg_stat
dba_hist_seg_stat_obj
dba_hist_metric_name
dba_hist_sysmetric_history
dba_hist_sysmetric_summary
dba_hist_sessmetric_history
dba_hist_filemetric_history
dba_hist_waitclassmet_history
dba_hist_dlm_misc
dba_hist_rcvry_file_dest_stat
dba_hist_rman_performance
dba_hist_active_sess_history
dba_hist_tablespace_stat
dba_hist_log
dba_hist_mttr_target_advice
dba_hist_tbspc_space_usage
CHAPTER 12 - Oracle10g dbms packages
dbms_sqltune
dbms_advisor
dbms_workload_repository
About the Authors
Alexey B. Danchenkov holds a master's degree in computer science
from St. Petersburg State University of Aerospace Instrumentation He started
work as an Oracle database administrator at Leaves Inc and worked for several
years as a principal Oracle DBA at a large telecommunications company, where
he managed very large database and billing systems. Alexey is also the creator
of the STATSPACK Viewer software, a popular Oracle graphics package.
Alexey lines in St. Petersburg, Russia where he enjoys Baltic Sea sailing with
his wife Julia, and his two sons Michael and Daniel.
Donald K. Burleson is one of the world’s top Oracle Database
experts with more than 20 years of full-time DBA experience. He specializes
in creating database architectures for very large online databases and he has
worked with some of the world’s most powerful and complex systems.
A former Adjunct Professor, Don Burleson has written 30 books, published more
than 100 articles in National Magazines, and serves as Editor-in-Chief of Rampant
TechPress. Don is a popular lecturer and teacher and is a frequent speaker at
Oracle Openworld and other international database conferences.
Customer Reviews
Customer Reviews: 2 Average Customer Rating: Jul 31, 2008 Earl Shaffer, OracleMan Consulting from USA
Just plain GREAT tuning book
This is my ideal tuning book. To the point, and full of scripts. Forget endless "how to tune a DB" theory. This book concentrates on the key to tuning all 9i, 10g and 11g DB - AWR, ASH and DB waits. We no longer waste hours comparing hit ratios. The key is to see where the waits are, where they are coming from and (with the help of this book) what to do about them. Its long, and it is a challenging read. But I suggest you put in the time and add this info to your skill-set. It is like having an expert right in your hands.
Oct 16, 2006  Diana Prior from Indiana
Excellent Tuning book on Oracle
This is the first book I have read from Doanld Burleson and Rampant Press. The book is excellent. Burleson's approach to helping you understand the concepts are displayed well with code examples. You will read a passage and then see an example to really enforce the principle. The examples support the concepts and using both together helped me out a great deal.
The chapters on Tcp/ip and DSM SUBSYSTEM I found to be really helpful. This architecure chapter goes in much needed detail as so many books just glance over the different Oracle tuning. What the processes are used for and how they can be tuned is well documented. The ODBC chapter helped me understand how to configure and best to take its advantage its fullest level for a given situation. At the end of the sql/mx chapter Burleson has a section on specific tuning and this section should be required reading for all DBAs and developers.
Something else I got from the book are methods for writing SQL and tuning it. The SQL that the author puts together looks so easy and natural for him to write. He is a master of SQL*Plus and that is a side benefit to reading the book. The techniques I picked up from reading Gavin's examples will help me just as much or more so than content of the book. This book is an excellent addition to my Oracle collection of books and I highly recommend reading it.
|
