ISBN: 0-9823061-2-1 ISBN13: 978-0-9823061-2-3 Library of Congress Number: 2009934294 200 pages Perfect bind - 9x7 PD 909 Shelving: Databases/Oracle Oracle In-Focus Series #35

Exploring Oracle Internals Tips and Tricks for the Oracle DBA Ben Prusinski Retail Price $29.95 / £24.95 Order now at 30% off and get access to the code depot! Only $20.95 (30% off) Key Features About the Author Table of Contents Index Reader Comments Errata Until now, Oracle database internals have been available previously only to internal Oracle support engineers and not available to outside of the Oracle Corporation database internals support group. This text explains hot issues for resolving the most difficult performance and recovery issues for Oracle 11g databases. It will allow the experienced Oracle professional to become an expert on how the Oracle database engine really works and provide the Oracle beginner with the solid core knowledge of the theory and practice behind the Oracle database.

About the Author:

Ben Prusinski

Ben Prusinski is an Oracle Certified Professional and Oracle ACE with more than 10 years of full-time experience as a database administrator and has written numerous articles and white papers on database management. Ben is also an active member of the IOUG, OAUG, and SROAUG user group communities. As a top Oracle expert, Ben is a frequent speaker and presenter at major technical conferences including Oracle OpenWorld, IOUG, and CLOUG. Ben has worked with databases including Oracle, Microsoft SQL Server, IBM DB2 UDB, and MySQL since 1996. As a certified Oracle RAC expert, Ben has designed robust architectures for high availability with Oracle RAC, Streams, and Data Guard for Oracle E-Business Applications and data warehouse environments. Furthermore, he has accumulated over a decade of practical knowledge and experience with complex database migrations and support, focusing mainly on how to best achieve results with large database migrations to the Oracle platform.

Table of Contents:

All examples are elaborated in detail and compared with the other techniques and tools introduced in the book. Using the Online Code Depot Conventions Used in this Book Acknowledgements Chapter 1: Introduction to Oracle 11g Database Insider Secrets New Features Overview for Oracle 11g Release New Feature: Invisible Index Partitioning New Features System Partitioning Interval Partitioning Virtual Column Based Partitioning Reference Partitioning New Feature: Active Data Guard New Feature: ADR Monitoring Tools Automatic Memory Management Oracle 11g Internals Summary The Oracle 11g Kernel Architecture Undocumented Tools Poorly Documented Tools Summary Chapter 2: Oracle 11g Database Insider Secrets: Memory Oracle 11g Memory Oracle 11g Database Architecture Inside the Oracle 11g System Global Area (SGA) Automatic Memory Management (AMM) Oracle 11g Advisors for SGA and PGA Memory Shared Pool Streams Pool Large Pool Java Pool Other Components Within the Oracle 11g SGA Inside the Oracle 11g Program Global Area (PGA) Semaphores and Oracle 11g Mutexes and Oracle 11g Wait Events and Mutexes for Oracle 11g How Mutexes in Oracle 11g Differ from Operating System Mutexes Windows Memory and Threads and Oracle 11g UNIX and Linux Shared Memory and Oracle 11g Stack Traces for Oracle 11g Shared Memory Understanding the Oracle 11g Memory Call Stack Summary Chapter 3: Insider Secrets: Locks and Oracle 11g Introduction What Are Locks? Database Isolation Levels and Serialization for Transactions Types of Locks within Oracle 11g DML Locks Lock Management and Escalation Lock Management for Oracle RAC Environments Locking Mechanism for Oracle 11g RAC Enhancements to Lock Management with Oracle 11g Serializing Locks with Oracle 11g Locking Tables Explicitly Useful Tips and Tricks for Locking Problems Oracle 11g Database Waits: Insufficient ITL Slots Avoiding Deadlock Conditions Lock Contention Issues and Solutions Summary Chapter 4: Insider Secrets: Latches What are Latches? How Do Latches Work with Oracle 11g? Types of Latches within Oracle 11g Exploring Latches with Dynamic Performance Views How Latches Differ From Locks Tips for Latch Contention Issues with Oracle 11g Tuning Latch Contention Issues for LRU Chain Latches Additional Tips for Tuning Oracle 11g Latches Summary Chapter 5: Hidden Parameters in Oracle 11g Purpose of Hidden Parameters for Oracle 11g How to Locate Hidden Parameters for Oracle 11g Explanation of Key Hidden Parameters for Oracle 11g New Features for 11g and Hidden Parameters Summary Chapter 6: Exploring New v$ Tables in Oracle 11g New Features for the v$ Tables and Oracle 11g Data Dictionary Purpose of v$ Views for Oracle 11g Categories for Oracle 11g Database Administration with v$ Views General Oracle 11g Database Administration Performance Tuning Oracle 11g Comprehensive Listing of Oracle 11g Dynamic v$ Performance Views Investigating Oracle 11g Internals with V$ Views Scripts and Tips for v$ and Data Dictionary - Oracle 11g Summary Chapter 7: Inside the x$ Tables of Oracle 11g Classification of x$ Tables Exploring Oracle 11g Database Internals with x$ Tables Oracle 11g New Features – Useful x$ Tables Using the X$ Tables for Oracle 11g Analysis Scripts using X$ Tables with Oracle 11g Summary Book Conclusion Appendix A: Internal Tools for Oracle 11g DBX Using DBX for Debugging Oracle Using DBX for Tracing Oracle 11g Memory Process: Truss Appendix B: Oracle 11g Trace Events Oracle 11g Database Trace Level Events Appendix C: ORA-0600 Troubleshooting Appendix D: References Chapter 1: Introduction Chapter 2: Oracle 11g Memory Chapter 3: Database Locks Chapter 4: Database Latches Chapter 5: Hidden Oracle 11g Parameters Chapter 6: V$ Views Chapter 7: X$ Tables for 11g Appendix F: Oracle 11g Database Internal Toolkit Appendix G: Oracle 11g Trace Events Appendix H: ORA-0600 Appendix I: Strace Appendix J: Using GDB for Oracle 11g Appendix K: Orakill for Windows and Oracle 11g Appendix L: Using Strace Strace for Oracle 11g and Linux Command Options for Strace with Linux for Oracle 11g Appendix M: Using GDB with Oracle 11g Introduction to GDB with Oracle 11g Oracle 11g Trace Stack with GDB Appendix N: Orakill for Windows Orakill for Windows Index About the Author Index: $ $database_block_corruption / /dev/shm _ _diag_hm_tc_enabled _hang_detection _hang_resolution _hm_analysis_oradebug_node_dump_level _hm_analysis_output_disk 1 11g ASM 11g Concurrency and SQL Tuning 11g Data Guard 11g Health Monitor 11g Memory Tuning 11g Streams 11gRAC A ACID model ADR Memory Monitor adrci adrci> show home alert.log alert.log file alter index…invisible alter index…visible archive control latches attach Automatic Diagnostic Database Monitor Automatic Diagnostic Repository automatic segment space management Automatic Workload Repository B background_dump_dest Backup and Recovery block media recovery bt C cache buffers chain latch cache buffers LRU chain latch catalog.sql catldr.sql core_dump_dest Cost Based Optimizer cpu_count cursor:mutex cursor:pin D data dictionary data dictionary cache Data Guard Data Recovery Advisor database buffer cache database locks db_block_buffers db_block_hash_buckets db_block_lru_latches db_block_size db_cache_size dba_extents dba_hist_latch dba_objects.object_id dba_segments dbms_repair dbms_xplan DBVERIFY DBWR DBX ddl_lock_timeout deadlock diag_adr_auto_purge diag_adr_enabled diag_adr_test_param diag_hm_rc_enabled diagnostic_dest dirty reads DML locks dra_bmr_number_threshold dra_bmr_percent_threshold dra_enable_offline_dictionary dumpsga E EM Support Workbench Enterprise Manager exclusive explicit locking F fk_empno flashback memory buffer fuzzy reads G GDB global cache services global enqueue services global lock conflicts gv$dlm_latch gv$latch gv$lock gv$resource H health monitor help help extended hm_analysis_oradebug_sys_dump_level I index-organized tables initrans interested transaction list interprocess communication model interval partitioning invisible index ipc ipcs ips create package ips show J Java pool buffer K Kernal Database Layers Kernel Access Kernel Cache Kernel Compilation Kernel Data Kernel Distributed Transaction Kernel Execution Kernel Generic Kernel Locking Kernel Query Kernel Security Kernel Services Kernel Transactions kgl_latch_count kgllkreq kglnaobj L large pool buffer large_pool_size latch contention issues latch hit ratio latch request mode latch_wait_posting latches Latches level library cache library cache latch local lock conflicts Locally Managed Tablespaces lock escalation lock table locks log.xml log_buffer log_small_entry_max_size long wait latches LRU LRU chain latches M Maximum Availability Architecture maxtrans memory management Memory Management Units memory_max_target memory_target mutex mutex_type mutexes N Network Attached Storage NFS configuration nowait no-wait mode null O optimizer_use_invisible_indexes ORA-0600 Oracle Call Interface Oracle SGA Orakill P pct_free pct_used pga_aggregate_target pga_target phantom reads physical address area pk_emp invisible pmap PMON process Program Global Area prop_old_enabled pstack Q qslice.exe Quickslice R Recovery Manager redo allocation latches Redo copy latch redo copy latches redo log buffer reference partitioning reference_emp result cache row cache objects latch row exclusive row share row-level locks S serialization mechanisms sga_max sga_max_size sga_target share share locks share row exclusive shared pool shared pool latches Short wait latches show alert spin count spin_count SQL*Plus SQL*Plus interface statistics_level Storage Area Network Strace tool Streams Pool streams_pool_size SYS level privileges sysresv System Global Area system partitioning T table locks Trace Level Events transaction locks Truss U undocumented parameters User Program Interface user_dump_dest user_part_tables user_tab_partitions V v$ dynamic performance views v$ views v$db_cache_advice v$diag_info v$fixed_table v$fixed_view_definition v$ir_repair v$java_pool_advice v$javapool v$latch v$latchholder v$latchname v$library_cache_memory v$librarycache v$lock v$memory_dynamic_components v$memory_target_advice v$mutex_sleep v$mutex_sleep_history v$pgastat v$rowcache v$segment_statistics v$sga_target_advice v$shared_pool_advice v$streams_pool_advice virtual virtual column based partitioning W wait events willing-to-wait request mode Windows Virtual Memory Manager X x$ tables x$dbgalertext x$dbgdirext x$diag_info x$ir_repair_option x$kcbfwait x$kgllk x$kglpn x$kq x$kqfvi X$KS x$ksmsp x$ksppi x$kzspr x$kzsrt Y yearly_sal