|
ISBN
0-9916386-4-6
ISBN 13
978-0-9916386-4-2 |
Library of
Congress Number: 2014935541 |
480 pages:
Perfect bind - 9x7 |
PD 314 |
|
Shelving: Database/Oracle |
Oracle
In-Focus Series # 10 |
|
|
Oracle Tuning Power Scripts
With 100+ High Performance SQL Scripts
Second Edition
Mike Ault,
Donald K. Burleson,
Harry Conway
Retail Price $37.95
/ £17.95
Get the
Oracle Tuning Library
Save $100 - All four books for only $118.95 |
|
|
|
|
Written by the world's most widely-read Oracle script developers and authors of
over 50 best-selling Oracle books, Mike Ault, Don Burleson and
Harry Conway shares their personal arsenal of Oracle data
dictionary scripts in this comprehensive book. With over 50 years
of combined experience using Oracle monitoring scripts and Oracle
tuning scripts, this is the most comprehensive Oracle script
collection ever assembled.
Packed with over 100 ready-to-user Oracle scripts, this is the
definitive collection for every Oracle professional DBA. It would
take man-years to develop these scripts from scratch, making this
book the best value in the Oracle industry.
Caution - These are extremely powerful
undocumented Oracle data dictionary scripts. They should only be
used by a certified Oracle DBA, and after a careful review of the
Oracle data dictionary script functionality. These Oracle scripts
are not for beginners and knowledge of the Oracle data dictionary
is required to fully utilize these scripts. USE AT YOUR OWN RISK.
|
|
* Provides a code depot for
every SQL script in the powerful arsenal.
* Contains script for all version of Oracle, from Oracle7
through Oracle10g.
* Contains script for SQL tuning, Oracle monitoring and Oracle
SGA tuning.
* The product of thousands of hours of analysis.
* Costing less than a nickel per script, this is a must-have
for every Oracle DBA. |
About the Authors:
Donald Burleson |
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.
|
|
|
Mike Ault |
Mike Ault
is is a Senior Oracle Consultant. The author of
more than 20 Oracle books and hundreds of articles in national publications,
Mike Ault has five Oracle Masters Certificates and was the first popular Oracle author with his landmark
book "Oracle7 Administration and Management". Mike also
wrote several of the "Exam Cram" books, and enjoys a
reputation as a leading author and Oracle consultant. Mike
started working with computers in 1979 right out of a stint in
the Nuclear Navy. He began working with Oracle in 1990 and has
since become a World Renowned Oracle expert. Mike is currently
a Senior Technical Management Consultant and has two wonderful daughters.
Mike is kept out of trouble by his wife of three decades, Susan. |
|
|
|
|
Harry
Conway |
|
Harry Conway
is an experienced Oracle DBA and Database manager with more
than 25 years of full-time Information Technology experience.
Expert in both Oracle and IDMS, Harry is a seasoned DBA and
Database manager with that rare combination of exceptional
technical and management skills.
As a veteran US Marine and a former Director of Database
Development, Harry has outstanding leaderships and management
skills with over a decade of experience managing multi-million
dollar database projects. |
Table of Contents:
Using the Online Code Depot
Conventions Used in this Book
Acknowledgements
Preface
Chapter 1: CPU, Enqueue, and Wait Event Monitoring
CPU Usage Statistics
SQL by CPU Usage
Enqueues
Monitoring Events
System Events by Percent
System Events by Percent - Sample Report
SYS_EVENTS
Events Related to ORA-4031 Error
Snap_delta_sys_events_pct90
Oracle10g Wait Events
Oracle10g Enqueues
dba_hist_enqueue_stat
10g Time Model Statistics dba_hist Views
dba_hist_sys_time_model
Conclusion
Chapter 2: File I/O Monitoring
Monitoring I/O
Global Basic Queries
DATAFILE I/O
PL/SQL to Calculate I/O per Second Data
I/O Timing Analysis
Calculate I/O Timing Values for Datafiles
SNAP FILE I/O
Find the Current I/O Session Bandits
Report on 9i DBWR Statistics
Oracle 10g FILE I/O
New Analysis Techniques for Oracle10g and Above
Oracle 10g Segment Statistics
Oracle 10g Datafile I/O Statistics
Conclusion
Chapter 3: Monitoring Locks, Latches, and Waits
Latches
Locks
Monitoring Sessions Causing Blocked Locks
Monitoring DDL and DML Locks
Monitoring Internal Locks
Monitoring Waits
Data Block Waits
Monitoring Oracle 10g
dba_hist_system_event
dba_hist_event_name
dba_hist_waitstat
dba_hist_waitclassmet_history
dba_hist_latch
dba_hist_latch_misses_summary
Oracle 10g Instance Wait Tuning
Oracle10g Wait Event Tuning
Not all Events are Created Equal
Event Wait Analysis with ASH
Inside the Active Session History Tables
Signature Analysis of Wait Events
Conclusion
|
Chapter 4: Monitoring Users and Processes
Monitoring Currently Logged-in User Processes
Locating Top Resource Sessions
Session Bottleneck Analysis
Investigating Session Memory Usage
Examining Background Processes
Monitoring Rollback Activity
Oracle 10g
Session Wait Analysis in Oracle10g
dba_hist_sessmetric_history
dba_hist_bg_event_summary
Conclusion
Chapter 5: Objects
Determine Global Object Access Patterns
Removing Storage-Related Performance Vampires
Detecting Space-Related Object Performance Problems
Locating Hot I/O Objects
Oracle10g
Viewing Table and Index Access with AWR
Tracking SQL Nested Loop Joins
Counting Index Usage Inside SQL
Tracking Full Scan Access
Conclusion
Chapter 6: SGA Monitoring
Importance of Proper Configuration
Getting a Handle on Memory Usage
Understanding the SGA
Gaining Insight into Memory Use
More on Memory Ratios
How to Keep Data Where It Belongs
Structure of the Shared Pool
Monitoring and Tuning the Shared Pool
Looking into the Shared Pool
When Does Less Become More?
Conclusions about the Shared Pool
Examining the Log Buffer
Miscellaneous Memory Issues
Buffer Busy Waits
Oracle 10g
Oracle10g Automatic Memory Management
Ratio Coverage in Oracle10g
dba_hist_librarycache
dba_hist_rowcache_summary
dba_hist_buffer_pool_stat
Plotting the Data Buffer Usage by Hour of the Day
Plotting the DBHR by Day of the Week
Automating KEEP Pool Assignment
Conclusion
|
Chapter 7: SQL
Sorting in Oracle9i and Above
Pinpointing Sessions with Problem SQL
What is Bad SQL?
Pinpointing Bad SQL
Historical SQL Analysis
Current SQL Analysis
New Techniques for Analyzing SQL Execution
Interrogating SQL Execution Plans
SQL Tuning Roadmap
Understand the Query and Dependent Objects
Look for SQL Rewrite Possibilities
Look for Object-Based Solutions
Oracle 10g
SQL Statistics
dba_hist_sqlstat
Oracle10g SQL Tuning Scripts
dba_hist_sqlstat
dba_hist_sql_plan
Interrogating Table Join Methods
Conclusion
Chapter 8: Storage and Space Management
Avoiding Database Downtime
Automatic Growth
Unlimited Object Extents
Correcting Space-Related Object Performance Problems
Oracle 10g
Oracle10g Storage Diagnostics
Oracle 10g ASSM
Segment Space Growth Prediction
Conclusion
Chapter 9: Miscellaneous Scripts
Workspaces in Oracle9i
pga_aggregate_target in Oracle9i
Uncovering Security Holes
Finding Storage Hogs
Oracle10g
Metric dba_hist Views
dba_hist_sysmetric_history
dba_hist_sysmetric_summary
System Statistics
dba_hist_sysstat
Operating System Statistics
Conclusion
Index
About Harry Conway
About Mike Ault
About Don Burleson
About Mike Reed |
|
|
Index Topics:
&pBgnSnap
&pDbId
&pEndSnap
&pInstNum
_
_smm_max_size
A
Active Session History
alter system flush shared_pool
analyze
analyze_database
analyze_schema
application_wait_time
ARCH
ASH
ASM
ASSM
autoextend
Automatic Database Diagnostic Monitor
Automatic Memory Management
Automatic segment management
Automatic Segment Space Management
Automatic Storage Management
Automatic Workload Repository
average file read/write times
B
badstorage
begin_interval_time
blocks
Bubble fragmentation
Buffer busy waits
buffer cache
Buffer cache hit ratio
buffer_pool_keep
buffer_pool_recycle
C
cache buffer chains
cardinality
Cartesian join
CBO
chained/migrated rows
cluster_wait_time
Coalesce
concurrency_wait_time
cost-based optimizer
CPU parse time
CPU recursive time
cpu_costing
cpu_time
create_index_cost
create_table_cost
cum_wait_time
cursor_sharing
D
Data Buffer Hit Ratio
data dictionary cache
data dictionary cache hit ratio
Database Writer
db file scattered read
db file sequential read
db file sequential waits
db file waits
DB time
db_k_cache_size
db_block_buffers
db_cache_advice
db_cache_size
db_file_multi_block_read_count
db_keep_cache_size
db_nk_cache_size
db_recycle_cache_size
dba_blockers
dba_data_files
dba_ddl_locks
dba_dml_locks
dba_extents
dba_hist
dba_hist_active_sess_history
dba_hist_bg_event_summary
dba_hist_buffer_pool_stat
dba_hist_enqueue_stat
dba_hist_event_name
dba_hist_filemetric_history
dba_hist_filestatxs
dba_hist_latch
dba_hist_latch_misses_summary
dba_hist_librarycache
dba_hist_metric_name
dba_hist_osstat
dba_hist_rowcache_summary
dba_hist_seg_stat
dba_hist_sessmetric_history
dba_hist_snapshot
dba_hist_sql_plan
dba_hist_sql_plan
dba_hist_sqlstat
dba_hist_sqltext
dba_hist_stat_name
dba_hist_sys_time_model
dba_hist_sysmetric_history
dba_hist_sysmetric_summary
dba_hist_sysstat
dba_hist_system_event
dba_hist_tempstatxs
dba_hist_waitclassmet_history
dba_hist_waitstat
dba_internal_locks
dba_kgllock
dba_lock
dba_lock_internaldba_dml_locks
dba_locks
dba_objects
dba_tables
dba_users
dba_waiters |
DBHR
dbms_advisor
dbms_job
dbms_scheduler
dbms_shared_pool
dbms_space
dbms_stats
dbms_utility
DBWR
DDL
DDL command
Default buffer cache
dictionary-managed tablespace
direct_io_count
direct_writes
disk_reads
DML
E
elapsed_time
end_interval_time
enqueue
enqueue waits
EXPLAIN plan
F
FIFO
First In First Out
fragmentation
freelists
H
hard parse elapsed time
hash_area_size
hash_value
high-water mark
HW enqueues
HWM
I
index_caching
init.ora
initrans
Insert Update and Delete
IUD
J
Java pool
java_exec_time
java_pool_size
K
Keep buffer cache
KEEP pool
L
large pool
large_pool_size
latch free
latch immediate miss ratio
latch miss ratio
Latches
least recently used
LGWR
library cache
library cache hit ratio
library cache misses
LMT
locally-managed tablespaces
Locks
log_buffer
LRU
M
maxtrans
MTS
Multi-threaded Server
N
number of physical read/write operations
O
obj
Object fragmentation
optimizer_index_caching
ORA-
ORA-
ORA-
Oracle Enterprise Manager
P
paging
PARALLEL hint
parallelism
parse time elapsed
pctfree
pga_aggregate_target
phyrds
phywrts
PL/SQL execution elapsed time
plsql_exec_time
Program Global Area
R
ratio-based analysis
Real Application Clusters
Recycle buffer cache
Redo log buffer
redo log space requests
redo log wait time
rollback
rollback segments
Row chaining
rows_processed |
S
Sequential data Access via Metadata
SGA
sga_target
Shared Global Area
shared pool
shared_pool_size
SID
sleeps
snap_id
sort_area_retained_size
sort_area_size
sort_io_count
space_alloc
space_usage
spfile
SQL Access Advisor
SQL Advisor
sql execute elapsed time
SQL Scripts
datafileae.sql
globiostats.sql
sqlhitrate.sql
SQL Tuning Advisor
SQL*Plus
sql_id
ST enqueues
statistics_level
stats$buffer_pool_statistics
stats$event_histogram
stats$latch
stats$sql_summary
stats$sysstat
STATSPACK
swapping
sys.dbms_space_admin
System Metrics Long Duration
System Metrics Short Duration
T
table fetch continued row
Tablespace fragmentation
TM enqueues
TX enqueue waits
TX enqueues
U
UNDO tablespace
unlimited tablespace
use_hash
user_io_wait_time
V
v$active_session_history
v$datafile
v$db_cache_advice
v$enqueue_statistics
v$event_histogram
v$event_name
v$file_histogram
v$latch
v$librarycache
v$lock_dpt
v$metric_name
v$metricgroup
v$object_usage
v$osstat
v$pga_target_advice
v$rowcache
v$segment_statistics
v$segstat
v$segstat_name
v$session
v$session_event
v$session_wait
v$session_wait_history
v$sessstat
v$sgastat
v$sql
v$sql_plan
v$sql_plan_statistics
v$sqlarea
v$statname
v$sys_time_model
v$sysmetric_history
v$sysmetric_summary
v$sysstat
v$system_event
v$waitclassmetric_history
v$waitstat
v_$sql_plan
W
wait classes
Waits
workarea_size_policy
Workspace Manager
wrh$_active_session_history
wri$_alert_history
X
x$bh
x$kcbcbh
x$kcbfwait
x$kcbrbh |
Reviews:
Errata:
|
