SEARCH
Disclaimer: Authors have full rights over their works. Reproduction of any part of the content is prohibited without prior authorization.
BOOK ORACLE DATABASE 12C RELEASE 2 PERFORMANCE TUNING TIPS - TECHNIQUES
SUMMARY
-
Items Found: 773
- 1 Introduction to Oracle Database 12c R1 & R2 New Features (DBA and Developer)
- 2 Basic Index Principles (Beginner Developer and Beginner DBA)
- 3 Pluggable Databases, Disk Implementation Methodology, and ASM (DBA)
- 4 Tuning the Database with Initialization Parameters (DBA)
- 5 Tuning with Enterprise Manager Cloud Control (DBA and Developer)
- 6 Using EXPLAIN, TRACE, and SQL Plan Management (Developer and DBA)
- 7 Basic Hint Syntax (Developer and DBA)
- 8 Query Tuning (Developer and Beginner DBA)
- 9 Table Joins and Other Advanced Tuning (Advanced DBA and Developer)
- 10 Using PL/SQL to Enhance Performance (Developer and DBA)
- 11 Oracle Cloud, Exadata, Tuning RAC, and Using Parallel Features
- 12 The V$ Views (Developer and DBA)
- 13 The X$ Tables and Internals Topics (Advanced DBA)
- 14 Using Statspack and the AWR Report to Tune Waits, Latches, and Mutexes
- 15 Performing a Quick System Review (DBA)
- 16 Monitor the System Using Unix Utilities (DBA)
- A Key Initialization Parameters (DBA)
- B The V$ Views (DBA and Developer)
- C The X$ Tables (DBA)
- Index
- Acknowledgments
- Introduction
- 1 Introduction to Oracle Database 12c R1 & R2 New Features (DBA and Developer)
- Oracle Database 12cR1 (12.1.0.1)
- Increased Size Limit to 32K for VARCHAR2 and NVARCHAR2
- Partial Indexes
- Invisible Columns
- Multiple Indexes on the Same Column List
- Fetch First x Rows
- Pluggable Databases (PDBs)
- Oracle Database Cloud Service (Database as a Service)
- PDB Level: MEMORY_LIMIT and MEMORY_MINIMUM (12cR2)
- Change Compression at Import Time
- Adaptive Query Optimization
- PGA_AGGREGATE_LIMIT
- Concurrent Execution for UNION/UNION ALL
- Invoker Rights Function Can Be Results Cached
- New DBMS_UTILITY.EXPAND_SQL_TEXT
- Default for Columns Based on Sequence
- Multiple SSD Devices for Smart Flash Cache
- Concurrent Cost-Based Optimizer Statistics Gathering
- Enhanced System Statistics
- Resource Manager for Runaway Queries
- Automatic Data Optimization (ADO)
- Global Index Maintenance: Drop and Truncate Partition Operations
- ASM Disk Scrubbing
- Online Capability Improvements
- Data Guard Improvements
- RMAN Improvements
- Oracle Database 12cR1 (12.1.0.2)
- In-Memory Database
- Advanced Index Compression
- Automatic Big Table Caching
- FDA Support for Container Databases
- Full Database Caching
- JSON Support
- FIPS 140 Parameter for Encryption
- PDB Subset Cloning
- Rapid Home Provisioning—Creating “Gold Images”
- Oracle Database 12cR2 (12.2)
- Application Development
- Enhanced Features to Reduce Costs and Issues for Migration to Oracle
- Availability
- Big Data
- Compression and Archiving
- Oracle RAC and Grid Infrastructure
- Security
- New Background Processes in 12c
- Exadata—New with Exadata X6!
- Version Comparison Chart
- New Features Review
- References
- 2 Basic Index Principles (Beginner Developer and Beginner DBA)
- Basic Index Concepts
- Invisible Indexes
- Multiple Types of Indexes on the Same Column(s)
- Concatenated Indexes
- Suppressing Indexes
- Using the NOT EQUALOperators: <>, !=
- Using IS NULLor IS NOT NULL
- Using LIKE
- Using Functions
- Comparing Mismatched Data Types
- Selectivity
- The Clustering Factor
- The Binary Height
- Additional Details Concerning BLEVELand Index Height
- Using Histograms
- Fast Full Scans
- Skip-Scans
- Types of Indexes
- B-Tree Indexes
- Bitmap Indexes
- Hash Indexes
- Index-Organized Tables
- Reverse Key Indexes
- Function-Based Indexes
- Partitioned Indexes
- New 12cR2 Features for Partitioned Indexes
- Bitmap Join Indexes
- Fast Index Rebuilding
- Rebuilding Indexes Online
- Tips Review
- References
- 3 Pluggable Databases, Disk Implementation Methodology, and ASM (DBA)
- Pluggable Databases (New in Oracle 12c)
- CDB or PDB Created Objects
- Creating a PDB: Many Ways to Do It
- Great Pluggable Database Commands
- ALTER SYSTEM While in a PDB and Other Nice Commands
- Using In-Memory (IM) with Pluggable Databases
- Other 12cR2 Features with Pluggable Databases
- Subset Standby (New in Oracle 12cR2)
- Disk Arrays
- Use Disk Arrays to Improve Performance and Availability
- How Many Disks Do You Need?
- What Are Some of the RAID Levels Available?
- The Newer RAID 5
- Solid-State Disks
- ASM Storage Management (Striping/Mirroring)
- Setup and Maintenance of the Traditional Filesystem
- What Is the Cost?
- Storing Data and Index Files in Separate Locations
- Avoiding I/O Disk Contention
- The 12c Heat Map and Automatic Data Optimization (ADO)
- 12c I/O Performance Tracking Views (Outliers)
- Oracle Bigfile Tablespaces
- ASM Introduction
- Communication Across IT Roles
- ASM Instances
- ASM Initialization Parameters
- ASM Installation in 12c
- Srvctl Enhancements
- ASM Disk Scrubbing
- ASM Rebalance Enhancements
- ASM Fast Mirror Resync
- ASM Filter Driver
- ASM and Privileges
- ASM and Multipathing
- Bigfile and ASM
- Avoiding Disk Contention by Using Partitions
- Getting More Information About Partitions
- Other Types of Partitioning
- Partitioned Indexes (Local)
- Partial Indexes
- Global Index Maintenance: Drop and Truncate Partition Operations
- Other Partitioning Options
- Index Partitioning
- Exporting Partitions
- Eliminating Fragmentation (Only If Needed—Careful!)
- Using the Correct Extent Size
- Avoiding Chaining by Setting PCTFREE Correctly
- Using Automatic Segment Space Management (ASSM)
- Increasing the Log File Size and LOG_CHECKPOINT_INTERVALfor Speed
- Determining If Redo Log File Size Is a Problem
- Determining the Size of Your Log Files and Checkpoint Interval
- Other Helpful Redo Log Commands
- Storing Multiple Control Files on Different Disks and Controllers
- Other Disk I/O Precautions and Tips
- Issues to Consider in the Planning Stages
- Tips Review
- References
- 4 Tuning the Database with Initialization Parameters (DBA)
- When Upgrading to Oracle Database 12c
- Using SEC_CASE_SENSITIVE_LOGON
- Crucial Memory Initialization Parameters for Performance
- PDB Level: MEMORY_LIMIT and MEMORY_MINIMUM
- In-Memory Database (INMEMORY_SIZE)
- Changing the Initialization Parameters Without a Restart
- Modifying an Initialization Parameter at the PDB Level
- Insight into the Initialization Parameters from Oracle Utilities
- Viewing the Initialization Parameters with Enterprise Manager
- Increasing Performance by Tuning the DB_CACHE_SIZE
- Using V$DB_CACHE_ADVICE in Tuning DB_CACHE_SIZE
- Monitoring the V$SQLAREA View to Find Bad Queries
- Setting DB_BLOCK_SIZE to Reflect the Size of Your Data Reads
- Setting SGA_MAX_SIZE to 25 to 50 Percent of the Size Allocated to Main Memory
- Tuning the SHARED_POOL_SIZE for Optimal Performance
- Using Stored Procedures for Optimal Use of the Shared SQLArea
- Setting the SHARED_POOL_SIZE High Enough to Fully Use the DB_CACHE_SIZE
- Keeping the Data Dictionary Cache Objects Cached
- Keeping the Library Cache Reload Ratio at 0 and the Hit Ratio Above 95 Percent
- Using Available Memory to Determine If the SHARED_POOL_SIZE Is Set Correctly
- Using the X$KSMSP Table to Get a Detailed Look at the Shared Pool
- Points to Remember About Cache Size
- Waits Related to Initialization Parameters
- Using Oracle Multiple Buffer Pools
- Pools Related to DB_CACHE_SIZE and Allocating Memory for Data
- Modifying the LRU Algorithm
- Pools Related to SHARED_POOL_SIZE and Allocating Memory for Statements
- Tuning PGA_AGGREGATE_TARGET and PGA_AGGREGATE_LIMIT
- Modifying the Size of Your SGA to Avoid Paging and Swapping
- Understanding the Oracle Optimizer
- How Optimization Looks at the Data
- Creating Enough Dispatchers
- Have Enough Open Cursors (OPEN_CURSORS)
- Don’t Let Your DDLStatements Fail (DDL_LOCK_TIMEOUT)
- Two Important Exadata Initialization Parameters (Exadata Only)
- Top 25 Initialization Parameters
- Initialization Parameters over the Years
- Finding Undocumented Initialization Parameters
- Understanding the Typical Server
- Modeling a Typical Server
- Sizing the Oracle Applications Database
- Tips Review
- References
- 5 Tuning with Enterprise Manager Cloud Control (DBA and Developer)
- Oracle Enterprise Manager Basics and Accessing OEM via Oracle Cloud
- Starting with All Targets and Other Groupings
- Monitoring and Tuning Using the OEM Performance Menu
- Performance Tab: Top Activity
- Performance Tab: SQL| SQLPerformance Analyzer
- Performance Tab: Real-Time ADDM
- Performance Tab: SQL| Access Advisor
- Performance Tab: Manage Optimizer Statistics
- Performance Tab: AWR | AWR Administration
- Performance Tab: ASH Analytics
- Monitoring and Tuning Using the OEM Administration Menu
- Database Administration Tab: Storage|Tablespaces
- Database Administration Tab: In-Memory Central and Initialization Parameters
- Database Administration Tab: All Initialization Parameters
- Database Administration Tab: Resource Manager (Consumer Groups)
- Monitoring and Tuning Using the OEM Database or Cluster Database Menu
- Database Tab: Job Activity
- Cluster Database Tab: Configuration|Database Topology
- Monitoring the Hosts
- Monitoring the Application Servers and Web Applications
- Real Application Testing (Database Replay)
- Summary
- Tips Review
- References
- 6 Using EXPLAIN, TRACE, and SQL Plan Management (Developer and DBA)
- The Oracle SQLTRACE Utility
- Simple Steps for SQLTRACE with a Simple Query
- The Sections of a TRACE Output
- Digging into the TKPROF Output
- Using DBMS_MONITOR
- Setting Trace Based on Session ID and Serial Number
- Setting Trace Based on Client Identifier
- Setting Trace for the Service Name/Module Name/Action Name
- Enabled Tracing Views
- TRCSESS Multiple Trace Files into One File
- Using EXPLAIN PLAN Alone
- An Additional EXPLAIN Example for a Simple Query
- EXPLAIN PLAN—Read It Top to Bottom or Bottom to Top?
- Tracing/Explaining Problem Queries in Developer Products
- Important Columns in the PLAN_TABLE Table
- Using DBMS_XPLAN
- Initialization Parameters for Undocumented TRACE
- Using Stored Outlines
- Dropping Stored Outlines
- Using SQLPlan Management (SPM) and SPM Example
- SPM Terms
- Using SPM
- Using Fixed SQLPlan Baselines
- Dropping a Plan
- Converting from Stored Outlines to SQLPlan Management
- Adaptive Plans (12c New Feature) and SPM
- Tips Review
- References
- 7 Basic Hint Syntax (Developer and DBA)
- Top Hints Used
- Use Hints Sparingly
- Fix the Design First
- Available Hints and Groupings
- Execution Path
- Access Methods
- Query Transformation Hints
- Join Operations
- Parallel Execution
- Other Hints
- Specifying a Hint
- Specifying Multiple Hints
- When Using an Alias, Hint the Alias, Not the Table
- The Hints
- The Oracle Demo Sample HR Schema
- The FIRST_ROWS Hint
- The ALL_ROWS Hint
- The FULLHint
- The INDEX Hint
- The NO_INDEX Hint
- The INDEX_JOIN Hint
- The INDEX_COMBINE Hint
- The INDEX_ASC Hint
- The INDEX_DESC Hint
- The INDEX_FFS Hint
- The ORDERED Hint
- The LEADING Hint
- The NO_EXPAND Hint
- The DRIVING_SITE Hint
- The USE_MERGE Hint
- The USE_NLHint
- The USE_HASH Hint
- The QB_NAME Hint
- The PUSH_SUBQ Hint
- The PARALLELHint
- The NO_PARALLELHint
- The PARALLEL_INDEX Hint
- The APPEND Hint
- The NOAPPEND Hint
- The CACHE Hint
- The NOCACHE Hint
- The RESULT_CACHE Hint
- The CURSOR_SHARING_EXACT Hint
- The INMEMORY and NO_INMEMORY and Other IM Hints
- The USE_INVISIBLE_INDEXES Hint
- The CONTAINERS Hint
- The WITH_PLSQLHint
- Some Miscellaneous Hints and Notes
- Undocumented Hints
- Using Hints with Views
- Notes on Hints and Stored Outlines (or SQLPlan Baselines)
- Why Isn’t My Hint Working?
- Hints at a Glance
- Tips Review
- References
- 8 Query Tuning (Developer and Beginner DBA)
- Which Queries Do I Tune? Querying V$SQLAREA and V$SQLViews
- Selecting from the V$SQLAREA View to Find the Worst Queries
- Selecting from the V$SQLView to Find the Worst Queries
- Oracle 12c Views for Locating Resource-Intensive Sessions and Queries
- Selecting from V$SESSMETRIC to Find Current Resource-Intensive Sessions
- Viewing Available AWR Snapshots
- Selecting from the DBA_HIST_SQLSTAT View to Find the Worst Queries
- When Should I Use an Index?
- Selecting Query Text from the DBA_HIST_SQLTEXT View
- Selecting Query EXPLAIN PLAN from the DBA_HIST_SQL_PLAN View
- What If I Forget the Index?
- Creating an Index
- Invisible Index
- Checking the Index on a Table
- Is the Column Properly Indexed?
- What If I Create a Bad Index?
- Exercising Caution When Dropping an Index
- Indexing the Columns Used in the SELECT and WHERE
- Using the Fast Full Scan
- Making the Query “Magically” Faster
- Caching a Table in Memory
- Using the Result Cache
- Choosing Among Multiple Indexes (Use the Most Selective)
- The Index Merge
- Indexes That Can Get Suppressed
- Function-Based Indexes
- Virtual Columns
- The “Curious” OR
- Using the EXISTS Function and the Nested Subquery
- That Table Is Actually a View!
- SQLand Grand Unified Theory
- Tuning Changes in Oracle Database 12c
- Oracle 12c Adaptive Query Optimization
- Adaptive Statistics
- Oracle 12c Changes in Statistics Gathering and Two New Histograms
- Oracle 12c Changes in SQLPlan Management
- Oracle Automatic SQLTuning
- Ensuring the Tuning User Has Access to the APIs
- Creating the Tuning Task
- Making Sure the Task Can Be Seen in the Advisor Log
- Executing the SQLTuning Task
- Checking Status of the Tuning Task
- Displaying the SQLTuning Advisor Report
- Reviewing the Report Output
- Tuning SQLStatements Automatically Using SQLTuning Advisor
- Enabling Automatic SQLTuning Advisor
- Configuring Automatic SQLTuning Advisor
- Viewing Automatic SQLTuning Results
- Using SQLPerformance Analyzer (SPA)
- Tips Review
- References
- 9 Table Joins and Other Advanced Tuning (Advanced DBA and Developer)
- Database Replay (capture/replay)
- Set Up Source Database for Database Replay Capture
- Prepare to Capture Workload
- Capture the Workload
- Prepare the Workload for Replay
- Process the Workload for Replay
- Prepare to Replay the Workload
- Execute the Workload Replay
- SQLPerformance Analyzer
- Create a SQLTuning Set
- Create an Analysis Task
- Execute Analysis Task
- Query SQLPerformance Analyzer Advisor Tasks
- Cancel an Executing SQLPerformance Analyzer Analysis Task
- Remove SQLPerformance Analyzer Analysis Task
- Determine Active SQLTuning Sets
- Remove SQLTuning Set
- Drop SQLTuning Set
- Join Methods
- NESTED LOOPS Joins
- SORT-MERGE Joins
- CLUSTER Joins
- HASH Joins
- INDEX-MERGE Joins
- Table Join Initialization Parameters
- SORT-MERGE and HASH Join Parameters
- A Two-Table Join: Equal-Sized Tables (Cost-Based)
- A Two-Table INDEXED Join: Equal-Sized Tables (Cost-Based)
- Forcing a Specific Join Method
- Eliminating Join Records (Candidate Rows) in Multitable Joins
- A Two-Table Join Between a Large and Small Table
- Three-Table Joins: Not as Much Fun
- Bitmap Join Indexes
- Bitmap Indexes
- Bitmap Join Index
- Best Uses for the Bitmap Join Index
- Third-Party Product Tuning
- Example 1
- Example 2
- Example 3
- Tuning Distributed Queries
- When You Have Everything Tuned
- Miscellaneous Tuning Snippets
- External Tables
- Snapshot Too Old: Developer Coding Issue
- Set Event to Dump Every Wait
- 14 Hours to 30 Seconds with the EXISTS Operator
- Tuning at the Block Level (Advanced)
- Key Sections of a Block Dump
- A Brief Look at an Index Block Dump
- Tuning Using Simple Mathematical Techniques
- Traditional Mathematical Analysis
- Seven-Step Methodology
- Deriving Performance Equations
- Pattern Interpretation
- Mathematical Techniques Conclusions
- Tips Review
- References
- 10 Using PL/SQL to Enhance Performance (Developer and DBA)
- Leverage the PL/SQLFunction Result Cache to Improve Performance (Improved in 12c)
- Define PL/SQLSubprograms in a SQLStatement (New in 12c)
- Reference Sequences Directly in PL/SQLExpressions
- Identity Columns (New in 12c)
- Max Size Increase to 32K for VARCHAR2, NVARCHAR2, and RAW Data Types (New in 12c)
- Allow Binding PL/SQL-Only Data Types to SQLStatements (New in 12c)
- Use Named Parameters in SQLFunction Calls
- Simplify Loops with the CONTINUE Statement
- Leverage Compile-Time Warnings to Catch Programming Mistakes (Improved in 12c)
- Increase Performance with Native Compilation
- Maximize Performance with the Optimizing Compiler
- Use DBMS_APPLICATION_INFO for Real-Time Monitoring
- Log Timing Information in a Database Table
- Reduce PL/SQLProgram Unit Iterations and Iteration Time
- Use ROWID for Iterative Processing
- Standardize on Data Types, IF Statement Order, and PLS_INTEGER
- Ensure the Same Data Types in Comparison Operations
- Order IF Conditions Based on the Frequency of the Condition
- Use the PLS_INTEGER PL/SQLData Type for Integer Operations
- Reduce the Calls to SYSDATE
- Reduce the Use of the MOD Function
- Improve Shared Pool Use by Pinning PL/SQLObjects
- Pinning (Caching) PL/SQLObject Statements into Memory
- Pinning All Packages
- Identify PL/SQLObjects That Need to Be Pinned
- Use and Modify DBMS_SHARED_POOL.SIZES
- Find Large Objects
- Get Detailed Object Information from DBA_OBJECT_SIZE
- Get Contiguous Space Currently in the Shared Pool
- Find Invalid Objects
- Find Disabled Triggers
- Use PL/SQLAssociative Arrays for Fast Reference Table Lookups
- Find and Tune the SQLWhen Objects Are Used
- Consider Time Component When Working with DATE Data Types
- Use PL/SQLto Tune PL/SQL
- Understand the Implications of PL/SQLObject Location
- Use Rollback Segments to Open Large Cursors
- Active Transaction Management: Process Large Quantities of Data
- Use Temporary Database Tables for Increased Performance
- Limit the Use of Dynamic SQL
- Use Pipelined Table Functions to Build Complex Result Sets
- Leave Those Debugging Commands Alone!
- The “Look and Feel”: Just for the Beginners
- PL/SQLExample
- Create a Procedure Example
- Execute the Procedure from PL/SQLExample
- Create a Function Example
- Execute the GET_CUST_NAME Function from SQLExample
- Create a Package Example
- Database Trigger Example Using PL/SQL
- Tips Review
- References
- 11 Oracle Cloud, Exadata, Tuning RAC, and Using Parallel Features
- The March to the Cloud (Past and Present)
- The Oracle Cloud
- Exadata Database Machine
- Exadata Terminology and the Basics
- Exadata Statistics
- Exadata Storage Expansion Rack Briefly
- Smart Scans
- Flash Cache
- Storage Indexes
- Hybrid Columnar Compression
- I/O Resource Management
- Use All Oracle Security Advantages with Exadata
- Best Practices
- Summary: Exadata = Paradigm Shift!
- Oracle Database Appliance (ODA)
- SuperCluster Using the M7 SPARC Chip
- Other Oracle Hardware to Consider
- Oracle Big Data Appliance X6-2
- ZFS Storage Servers
- StorageTek Modular Library System
- Parallel Databases
- Real Application Clusters (RAC)
- Oracle RAC Architecture
- Internal Workings of the Oracle RAC System
- RAC Performance Tuning Overview
- RAC Cluster Interconnect Performance
- Finding RAC Wait Events—Sessions Waiting
- RAC Wait Events and Interconnect Statistics
- Cluster Interconnect Tuning—Hardware Tier
- Basic Concepts of Parallel Operations
- Basic Concepts of Parallel Operations
- Parallel DMLand DDLStatements and Operations
- Managing Parallel Server Resources and Parallel Statement Queuing
- Parallelism and Partitions
- Inter- and Intra-operation Parallelization
- Examples of Using Inter- and Intra-operations (PARALLELand NO_PARALLELHints)
- Creating Table and Index Examples Using Parallel Operations
- Monitoring Parallel Operations via the V$ Views
- Using EXPLAIN PLAN and AUTOTRACE on Parallel Operations
- Using the SET AUTOTRACE ON/OFF Command
- Tuning Parallel Execution and the Initialization Parameters
- Parallel Loading
- Optimizing Parallel Operations in RAC
- Objectives of Parallel Operations
- RAC Parallel Usage Models
- Parallel Initialization Parameters
- V$ Views for Viewing Parallel Statistics
- Create Table As
- Parallel Index Builds
- Performance Considerations and Summary
- Other Parallel Notes
- Oracle Documentation Is Online
- Tips Review
- References
- 12 The V$ Views (Developer and DBA)
- Creating and Granting Access to V$ Views
- Obtaining a Count and Listing of All V$ Views
- Getting a Listing for the X$ Scripts That Make Up the V$ Views
- Examining the Underlying Objects That Make Up the DBA_ Views
- Using Helpful V$ Scripts
- Basic Database Information
- Basic Automatic Workload Repository (AWR) Information
- Basic Licensing Information
- Database Options Installed in Your Database
- Summary of Memory Allocated (V$SGA)
- Querying V$IM_SEGMENTS After Setting the INMEMORY_SIZE
- Automatic Memory Management and MEMORY_TARGET
- Detailed Memory Allocated (V$SGASTAT)
- Detailed Memory Allocated (V$SGASTAT) for a PDB vs. Root CDB
- Finding spfile.ora/init.ora Settings in V$PARAMETER
- Modifying an Initialization Parameter at PDB Level
- Determining If Data Is in Memory (V$SYSSTAT & V$SYSMETRIC)
- Determining Memory for the Data Dictionary (V$ROWCACHE)
- Determining Memory for the Shared SQLand PL/SQL(V$LIBRARYCACHE)
- Querying V$CONTAINERS and V$PDBS for Container Information
- Querying V$CONTAINERS When Using Pluggable Databases
- Querying V$PDBS for Pluggable Database Information
- Using the Result Cache
- Identifying PL/SQLObjects That Need to Be Kept (Pinned)
- Finding Problem Queries by Monitoring V$SESSION_LONGOPS
- Finding Problem Queries by Querying V$SQLAREA
- Finding Out What Users Are Doing and Which Resources They Are Using
- Finding Out Which Objects a User Is Accessing
- Getting Detailed User Information
- Using Indexes
- Identifying Locking Issues
- Killing the Problem Session
- Finding Users with Multiple Sessions
- Querying for Current Profiles
- Finding Disk I/O Issues
- Checking for Privileges and Roles
- Wait Events V$ Views
- Some of the Major V$ View Categories
- Tips Review
- References
- 13 The X$ Tables and Internals Topics (Advanced DBA)
- Introducing the X$ Tables
- Misconceptions About the X$ Tables
- Granting Access to View the X$ Tables
- Creating V$ Views and X$ Tables in 12c
- The X$ Tables Comprising the V$ Views
- Obtaining a List of All the X$ Tables in 12c
- Obtaining a List of All the X$ Indexes in 12c
- Using Hints with X$ Tables and Indexes
- Monitoring Space Allocations in the Shared Pool
- Creating Queries to Monitor the Shared Pool
- ORA-04031 Errors
- Large Allocations Causing Contention
- Shared Pool Fragmentation
- Low Free Memory in Shared and Java Pools
- Library Cache Memory Use
- High Number of Hard Parses
- Mutex/Latch Waits and/or Sleeps
- Miscellaneous X$ Table Notes
- Obtaining Information About Redo Log Files
- Setting Initialization Parameters
- Case 1
- Case 2
- Case 3
- Case 4
- Case 5
- Exploring Buffer Cache/Data Block Details
- Buffer Statuses
- Segments Occupying Block Buffers
- Hot Data Blocks and the Causes of Latch Contention and Wait Events
- Obtaining Database- and Instance-Specific Information
- Effective X$ Table Use and Strategy
- Oracle Internals Topics
- Traces
- DBMS_TRACE Package
- Events
- Dumps
- ORADEBUG
- trcsess Utility
- Reading the Trace File
- Wait Information and Response Time
- Recursive Calls
- Module Info
- Commit
- Unmap
- Bind Variables
- Errors
- Some Common X$ Table Groups
- Some Common X$ Table and Non-V$ Fixed View Associations
- Common X$ Table Joins
- X$ Table Naming Conventions (My Favorite Section of This Book!)
- X$ Table Naming Conventions with CON_ID, and INMEMORY
- Future Version Impact in 12cR2
- Tips Review
- References
- 14 Using Statspack and the AWR Report to Tune Waits, Latches, and Mutexes
- What’s New in 12cR2 (12.2) Statspack and the AWR Report
- Installing Statspack
- Security of the PERFSTAT Account
- Post-Installation
- Gathering Statistics
- Running the Statistics Report
- The Automatic Workload Repository (AWR) and the AWR Report
- Manually Managing the AWR
- AWR Automated Snapshots
- AWR Snapshot Reports
- Run the AWR Report in Oracle Enterprise Manager Cloud Control
- Interpreting the Statspack and AWR Report Output
- The Header Information and Cache Sizes
- The Load Profile
- Instance Efficiency
- Shared Pool Statistics
- Top Wait Events
- Oracle Bugs
- The Life of an Oracle Shadow Process
- RAC Wait Events and Interconnect Statistics
- Top SQLStatements
- Instance Activity Statistics
- Tablespace and File I/O Statistics
- Segment Statistics
- Additional Memory Statistics
- UNDO Statistics
- Latch and Mutex Statistics
- Tuning and Viewing at the Block Level (Advanced)
- Dictionary and Library Cache Statistics
- SGA Memory Statistics
- Nondefault Initialization Parameters
- Top 15 Things to Look for in AWR Report and Statspack Output
- Managing the Statspack Data
- Upgrading Statspack
- Deinstalling Statspack
- Quick Notes on the New ADDM Report
- Scripts in 12cR2
- Tips Review
- References
- 15 Performing a Quick System Review (DBA)
- Total Performance Index (TPI)
- Education Performance Index (EPI)
- System Performance Index (SPI)
- Memory Performance Index (MPI)
- Top 25 “Memory Abuser” Statements Tuned
- Top 10 “Memory Abusers” as a Percent of All Statements
- Buffer Cache Hit Ratio
- Dictionary Cache Hit Ratio
- Library Cache Hit Ratio
- PGA Memory Sort Ratio
- Percentage of Data Buffers Still Free
- Using the Result Cache Effectively
- Pinning/Caching Objects
- Disk Performance Index (DPI)
- Top 25 “Disk-Read Abuser” Statements Tuned
- Top 10 Disk-Read Abusers as Percentage of All Statements
- Tables/Indexes Separated or Using ASM
- Mission-Critical Table Management
- Key Oracle Files Separated
- Automatic Undo Management
- Using Pluggable Databases Effectively
- Total Performance Index (TPI)
- Overall System Review Example
- Rating System
- Example System Review Rating Categories
- Items Requiring Immediate Action
- Other Items Requiring Action
- System Information List
- Memory-Related Values
- Disk-Related Values
- CPU-Related Values
- Backup- and Recovery-Related Information
- Naming Conventions and/or Standards and Security Information Questions
- DBA Knowledge Rating
- Other Items to Consider in Your TPI and System Review
- Tips Review
- References
- 16 Monitor the System Using Unix Utilities (DBA)
- Unix/Linux Utilities
- Using the sar Command to Monitor CPU Usage
- sar –u (Check for CPU Bogged Down)
- The sar –d Command (Find I/O Problems)
- The sar –b Command (Check the Buffer Cache)
- The sar –q Command (Check the Run Queue and Swap Queue Lengths)
- Using the sar and vmstat Commands to Monitor Paging/Swapping
- Using sar –p to Report Paging Activities
- Using sar –w to Report Swapping and Switching Activities
- Using sar –r to Report Free Memory and Free Swap
- Using sar –g to Report Paging Activities
- Using sar –wpgr to Report on Memory Resources
- Finding the Worst User on the System Using the top Command
- Monitoring Tools
- Using the uptime Command to Monitor CPU Load
- Using the mpstat Command to Identify CPU Bottlenecks
- Combining ps with Selected V$ Views
- CPU/Memory Monitoring Tool (Task Manager) on Windows
- Using the iostat Command to Identify Disk I/O Bottlenecks
- Using iostat –d for Disk Drives sd15, sd16, sd17, and sd18
- Using iostat –D
- Using iostat –x
- Combining iostat –x with Logic in a Shell Script
- Using the ipcs Command to Determine Shared Memory
- Using the vmstat Command to Monitor System Load
- Monitoring Disk Free Space
- The df Command
- The du Command
- Monitoring Network Performance with netstat
- Modifying the Configuration Information File
- Other Factors That Affect Performance
- Other Sources to Improve Performance
- Tips Review
- References
- A Key Initialization Parameters (DBA)
- Obsoleted/Desupported Initialization Parameters
- Deprecated Initialization Parameters
- Top 25 Initialization Parameters
- Top 20 Initialization Parameters Not to Forget
- Top 13 Undocumented Initialization Parameters (As I See It)
- Bonus 11 Undocumented Initialization Parameters
- Listing of Documented Initialization Parameters (V$PARAMETER)
- Listing of Undocumented Initialization Parameters (X$KSPPI/X$KSPPCV)
- Additional Oracle Applications Notes
- Concurrent Managers
- Applications—Finding Module-Specific Patches
- Diagnostics Data Collection: EBS Analyzers
- Web Server Tuning
- Timeouts
- Database Initialization Parameter Sizing
- Top 10 Reasons Not to Write a Book
- Tips Review
- References
- B The V$ Views (DBA and Developer)
- Creation of V$ and GV$ Views and X$ Tables
- A List of Oracle 12c (12.2.0.0.1) GV$ Views
- A List of Oracle 12c (12.2.0.0.1) V$ Views
- Oracle 12c Scripts for the X$ Tables Used to Create the V$ Views
- C The X$ Tables (DBA)
- Oracle 12cR2 X$ Tables Ordered by Name
- Oracle 12cR2 X$ Indexes
- Oracle 12cR2 V$ Views Cross-Referenced to the X$ Tables
- Index
