Oracle Database 12c Release 2 Performance Tuning Tips & Techniques

Oracle Database 12c Release 2 Performance Tuning Tips & Techniques

by Richard Niemiec
Released March 2017
Publisher(s): McGraw Hill Computing
ISBN: 9781259589690

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

Proven Database Optimization Solutions―Fully Updated for Oracle Database 12c Release 2

Systematically identify and eliminate database performance problems with help from Oracle Certified Master Richard Niemiec. Filled with real-world case studies and best practices, Oracle Database 12c Release 2 Performance Tuning Tips and Techniques details the latest monitoring, troubleshooting, and optimization methods. Find out how to identify and fix bottlenecks on premises and in the cloud, configure storage devices, execute effective queries, and develop bug-free SQL and PL/SQL code. Testing, reporting, and security enhancements are also covered in this Oracle Press guide.

• Properly index and partition Oracle Database 12c Release 2
• Work effectively with Oracle Cloud, Oracle Exadata, and Oracle Enterprise Manager
• Efficiently manage disk drives, ASM, RAID arrays, and memory
• Tune queries with Oracle SQL hints and the Trace utility
• Troubleshoot databases using V$ views and X$ tables
• Create your first cloud database service and prepare for hybrid cloud
• Generate reports using Oracle’s Statspack and Automatic Workload Repository tools
• Use sar, vmstat, and iostat to monitor operating system statistics

Table of contents

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

Product information

  • Title: Oracle Database 12c Release 2 Performance Tuning Tips & Techniques
  • Author(s): Richard Niemiec
  • Release date: March 2017
  • Publisher(s): McGraw Hill Computing
  • ISBN: 9781259589690