Oracle Tuning Power Scripts : With 100+ High Performance SQL Scripts

by Unknown

• ISBN13: 9780974448671
• ISBN10: 0974448672
• Format: Trade Paper
• Copyright: 2006-01-01
• Publisher: Rampant Techpress

Summary

Targeted at Oracle professionals who need fast and accurate working examples of complex issues, Oracle In-focus books target specific areas of Oracle technology in a concise manner. Plenty of working code is provided without a lot of theory, allowing database managers to solve their problems quickly without reviewing data that they already know. All code scripts are available for instant download from a companion web site.

Author Biography

Don 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 32 books, published more than 100 articles in National Magazines, and serves as Editor-in-Chief of Oracle Internals. Don is a popular lecturer and teacher and is a frequent speaker at Oracle Openworld and other international database conferences. Mike Ault is 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. 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	(1)
Conventions Used in this Book	2	(2)
Acknowledgements	4	(2)
Preface	6	(1)
CPU, Enqueue, and Wait Event Monitoring	7	(31)
CPU Usage Statistics	7	(2)
SQL by CPU Usage	9	(5)
Enqueues	11	(3)
Monitoring Events	14	(2)
System Events by Percent	16	(3)
System Events by Percent - Sample Report	18	(1)
SYS_EVENTS	19	(2)
Events Related to ORA-4031 Error	21	(2)
Snap_delta_sys_events_pct90	23	(3)
Oracle 10g Wait Events	26	(2)
Oracle 10g Enqueues	28	(1)
dba_hist_enqueue_stat	28	(3)
10g Time Model Statistics dba_hist Views	31	(1)
dba_hist_sys_time_model	32	(5)
Conclusion	37	(1)
File I/O Monitoring	38	(57)
Monitoring I/O	38	(1)
Global Basic Queries	39	(5)
DATAFILE I/O	44	(4)
PL/SQL to Calculate I/O per Second Data	48	(2)
I/O Timing Analysis	50	(2)
Calculate I/O Timing Values for Datafiles	50	(2)
SNAP FILE I/O	52	(2)
Find the Current I/O Session Bandits	54	(6)
Report on 9i DBWR Statistics	60	(2)
Oracle 10g FILE I/O	62	(3)
New Analysis Techniques for Oracle 10g and Above	62	(3)
Oracle 10g Segment Statistics	65	(4)
Oracle 10g Datafile I/O Statistics	69	(25)
Conclusion	94	(1)
Monitoring Locks, Latches, and Waits	95	(72)
Latches	95	(4)
Locks	99	(8)
Monitoring Sessions Causing Blocked Locks	100	(2)
Monitoring DDL and DML Locks	102	(2)
Monitoring Internal Locks	104	(3)
Monitoring Waits	107	(2)
Data Block Waits	109	(8)
Monitoring Oracle 10g	117	(7)
dba_hist_system_event	124	(3)
dba_hist_event_name	127	(1)
dba_hist_waitstat	128	(2)
dba_hist_waitclassmet_history	130	(2)
dba_hist_latch	132	(3)
dba_hist_latch_misses_summary	135	(4)
Oracle 10g Instance Wait Tuning	139	(2)
Oracle 10g Wait Event Tuning	141	(2)
Not all Events are Created Equal	141	(2)
Event Wait Analysis with ASH	143	(2)
Inside the Active Session History Tables	145	(4)
Signature Analysis of Wait Events	149	(16)
Conclusion	165	(2)
Monitoring Users and Processes	167	(33)
Monitoring Currently Logged-in User Processes	167	(3)
Locating Top Resource Sessions	170	(13)
Session Bottleneck Analysis	176	(7)
Investigating Session Memory Usage	183	(7)
Examining Background Processes	185	(3)
Monitoring Rollback Activity	188	(2)
Oracle 10g	190	(5)
Session Wait Analysis in Oracle 10g	190	(5)
dba_hist_sessmetric_history	195	(1)
dba_hist_bg_event_summary	196	(3)
Conclusion	199	(1)
Objects	200	(55)
Determine Global Object Access Patterns	200	(8)
Removing Storage-Related Performance Vampires	208	(8)
Detecting Space-Related Object Performance Problems	208	(8)
Locating Hot I/O Objects	216	(4)
Oracle 10g	220	(34)
Viewing Table and Index Access with AWR	225	(1)
Tracking SQL Nested Loop Joins	225	(8)
Counting Index Usage Inside SQL	233	(7)
Tracking Full Scan Access	240	(14)
Conclusion	254	(1)
SGA Monitoring	255	(74)
Importance of Proper Configuration	255	(2)
Getting a Handle on Memory Usage	257	(17)
Understanding the SGA	260	(2)
Gaining Insight into Memory Use	262	(4)
More on Memory Ratios	266	(3)
How to Keep Data Where It Belongs	269	(5)
Structure of the Shared Pool	274	(2)
Monitoring and Tuning the Shared Pool	276	(30)
Looking into the Shared Pool	277	(1)
When Does Less Become More?	278	(17)
Conclusions about the Shared Pool	295	(11)
Examining the Log Buffer	306	(1)
Miscellaneous Memory Issues	306	(2)
Buffer Busy Waits	306	(2)
Oracle 10g	308	(4)
Oracle 10g Automatic Memory Management	308	(1)
Ratio Coverage in Oracle 10g	308	(2)
dba_hist_librarycache	310	(2)
dba_hist_rowcache_summary	312	(3)
dba_bist_buffer_pool_stat	315	(9)
Plotting the Data Buffer Usage by Hour of the Day	320	(2)
Plotting the DBHR by Day of the Week	322	(2)
Automating Keep Pool Assignment	324	(3)
Conclusion	327	(2)
SQL	329	(76)
Sorting in Oracle9i and Above	329	(5)
Pinpointing Sessions with Problem SQL	334	(4)
What is Bad SQL?	338	(2)
Pinpointing Bad SQL	340	(6)
Historical SQL Analysis	340	(5)
Current SQL Analysis	345	(1)
New Techniques for Analyzing SQL Execution	346	(22)
Interrogating SQL Execution Plans	351	(5)
SQL Tuning Roadmap	356	(1)
Understand the Query and Dependent Objects	356	(6)
Look for SQL Rewrite Possibilities	362	(2)
Look for Object-Based Solutions	364	(4)
Oracle 10g	368	(36)
SQL Statistics	368	(1)
dba_hist_sqlstat	369	(7)
Oracle 10g SQL Tuning Scripts	376	(8)
dba_hist_sqlstat	384	(6)
dba_hist_sql_plan	390	(3)
Interrogating Table Join Methods	393	(11)
Conclusion	404	(1)
Storage and Space Management	405	(30)
Avoiding Database Downtime	405	(19)
Automatic Growth	406	(4)
Unlimited Object Extents	410	(11)
Correcting Space-Related Object Performance Problems	421	(3)
Oracle 10g	424	(1)
Oracle 10g Storage Diagnostics	424	(1)
Oracle 10g ASSM	425	(7)
Segment Space Growth Prediction	432	(2)
Conclusion	434	(1)
Miscellaneous Scripts	435	(23)
Workspaces in Oracle9i	435	(2)
pga_aggregate_target in Oracle9i	437	(1)
Uncovering Security Holes	438	(5)
Finding Storage Hogs	443	(4)
Oracle 10g	447	(10)
Metric dba_hist Views	447	(1)
dba_hist_sysmetric_history	448	(1)
dba_hist_sysmetric_summary	449	(2)
System Statistics	451	(1)
dba_bist_sysstat	452	(2)
Operating System Statistics	454	(3)
Conclusion	457	(1)
Index	458	(1)
About Harry Conway	458	(7)
About Mike Ault	465	(1)
About Don Burleson	466	(1)
About Mike Reed	467