|
ISBN
0-9744486-7-2
ISBN 13
978-0974448671 |
Library of Congress Number: 2005901260 |
480 pages:
Perfect bind - 9x7 |
PD 106 |
|
Shelving:
Database/Oracle |
Oracle In-Focus Series # 10 |
|
|
Oracle Tuning Power Scripts
With 100+ High Performance SQL Scripts
Mike Ault, Donald K. Burleson, Harry
Conway
Retail Price $27.95
/ £17.95
|
Order now and get
immediate online access to the code depot! |
Only $19.95
(30% off) |
 |
Get the Easy Oracle Pack
Five books
for $59.95 - A $110.00 value |
|
|
|
|
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 with
Burleson Consulting, and one of the
leading names in Oracle technology. 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.
|
|
|
|
|
eWeek
Magazine names Mike Ault among
"World's leading Oracle authors" |
|
|
|
|
|
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 1
Conventions Used in this Book 2
Acknowledgements 4
Preface 6
Chapter 1: CPU, Enqueue, and Wait Event
Monitoring 7
CPU Usage Statistics 7
SQL by CPU Usage 9
Enqueues 11
Monitoring Events 14
System Events by Percent 16
System Events by Percent - Sample Report 18
SYS_EVENTS 19
Events Related to ORA-4031 Error 21
Snap_delta_sys_events_pct90 23
Oracle10g Wait Events 26
Oracle10g Enqueues 28
dba_hist_enqueue_stat 28
10g Time Model Statistics dba_hist Views 31
dba_hist_sys_time_model 32
Conclusion 37
Chapter 2: File I/O Monitoring 38
Monitoring I/O 38
Global Basic Queries 39
DATAFILE I/O 44
PL/SQL to Calculate I/O per Second Data 48
I/O Timing Analysis 50
Calculate I/O Timing Values for Datafiles 50
SNAP FILE I/O 52
Find the Current I/O Session Bandits 54
Report on 9i DBWR Statistics 60
Oracle 10g FILE I/O 62
New Analysis Techniques for Oracle10g and Above 62
Oracle 10g Segment Statistics 65
Oracle 10g Datafile I/O Statistics 69
Conclusion 94
Chapter 3: Monitoring Locks, Latches,
and Waits 95
Latches 95
Locks 99
Monitoring Sessions Causing Blocked Locks 100
Monitoring DDL and DML Locks 102
Monitoring Internal Locks 104
Monitoring Waits 107
Data Block Waits 109
Monitoring Oracle 10g 117
dba_hist_system_event 124
dba_hist_event_name 127
dba_hist_waitstat 128
dba_hist_waitclassmet_history 130
dba_hist_latch 132
dba_hist_latch_misses_summary 135
Oracle 10g Instance Wait Tuning 139
Oracle10g Wait Event Tuning 141
Not all Events are Created Equal 141
Event Wait Analysis with ASH 143
Inside the Active Session History Tables 145
Signature Analysis of Wait Events 149
Conclusion 165
|
Chapter 4:
Monitoring Users and Processes 167
Monitoring Currently Logged-in User Processes 167
Locating Top Resource Sessions 170
Session Bottleneck Analysis 176
Investigating Session Memory Usage 183
Examining Background Processes 185
Monitoring Rollback Activity 188
Oracle 10g 190
Session Wait Analysis in Oracle10g 190
dba_hist_sessmetric_history 195
dba_hist_bg_event_summary 196
Conclusion 199
Chapter 5: Objects 200
Determine Global Object Access Patterns 200
Removing Storage-Related Performance Vampires 208
Detecting Space-Related Object Performance Problems 208
Locating Hot I/O Objects 216
Oracle10g 220
Viewing Table and Index Access with AWR 225
Tracking SQL Nested Loop Joins 225
Counting Index Usage Inside SQL 233
Tracking Full Scan Access 240
Conclusion 254
Chapter 6: SGA Monitoring 255
Importance of Proper Configuration 255
Getting a Handle on Memory Usage 257
Understanding the SGA 260
Gaining Insight into Memory Use 262
More on Memory Ratios 266
How to Keep Data Where It Belongs 269
Structure of the Shared Pool 274
Monitoring and Tuning the Shared Pool 276
Looking into the Shared Pool 277
When Does Less Become More? 278
Conclusions about the Shared Pool 295
Examining the Log Buffer 306
Miscellaneous Memory Issues 306
Buffer Busy Waits 306
Oracle 10g 308
Oracle10g Automatic Memory Management 308
Ratio Coverage in Oracle10g 308
dba_hist_librarycache 310
dba_hist_rowcache_summary 312
dba_hist_buffer_pool_stat 315
Plotting the Data Buffer Usage by Hour of the Day 320
Plotting the DBHR by Day of the Week 323
Automating KEEP Pool Assignment 325
Conclusion 328
|
Chapter 7: SQL 329
Sorting in Oracle9i and Above 329
Pinpointing Sessions with Problem SQL 334
What is Bad SQL? 338
Pinpointing Bad SQL 340
Historical SQL Analysis 340
Current SQL Analysis 345
New Techniques for Analyzing SQL Execution 346
Interrogating SQL Execution Plans 351
SQL Tuning Roadmap 356
Understand the Query and Dependent Objects 356
Look for SQL Rewrite Possibilities 362
Look for Object-Based Solutions 364
Oracle 10g 368
SQL Statistics 368
dba_hist_sqlstat 369
Oracle10g SQL Tuning Scripts 376
dba_hist_sqlstat 384
dba_hist_sql_plan 390
Interrogating Table Join Methods 393
Conclusion 404
Chapter 8: Storage and Space Management
405
Avoiding Database Downtime 405
Automatic Growth 406
Unlimited Object Extents 410
Correcting Space-Related Object Performance Problems 421
Oracle 10g 424
Oracle10g Storage Diagnostics 424
Oracle 10g ASSM 425
Segment Space Growth Prediction 432
Conclusion 434
Chapter 9: Miscellaneous Scripts 435
Workspaces in Oracle9i 435
pga_aggregate_target in Oracle9i 437
Uncovering Security Holes 438
Finding Storage Hogs 443
Oracle10g 447
Metric dba_hist Views 447
dba_hist_sysmetric_history 448
dba_hist_sysmetric_summary 449
System Statistics 451
dba_hist_sysstat 452
Operating System Statistics 454
Conclusion 457
Index 458
About Harry Conway 458
About Mike Ault 465
About Don Burleson 466
About Mike Reed 467 |
|
|
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:
Correction in the following script:
* CPU.SQL
rem
rem CPU.SQL
rem Mike Ault
rem
rem SQL by CPU Usage (v$sqlarea)
rem
column sql_text format a40 word_wrapped heading 'SQL|Text'
column cpu_time heading 'CPU|Time'
column elapsed_time heading 'Elapsed|Time'
column disk_reads heading 'Disk|Reads'
column buffer_gets heading 'Buffer|Gets'
column rows_processed heading 'Rows|Processed'
set pages 55 lines 132
ttitle 'SQL By CPU Usage'
spool cpu
select * from
(select sql_text,
cpu_time/1000000000 cpu_time,
elapsed_time/1000000000 elapsed_time,
disk_reads,
buffer_gets,
rows_processed
from v$sqlarea
order by cpu_time desc, disk_reads desc
)
where rownum < 21
/
spool off
set pages 22 lines 80
ttitle off
CORRECTION:
cpu_time/1000000000 cpu_time,
elapsed_time/1000000000
elapsed_time,
to
cpu_time/1000000 cpu_time,
elapsed_time/1000000 elapsed_time,
|
