Oracle Database 12c Release 2 Performance Tuning Tips & Techniques

Name: Oracle Database 12c Release 2 Performance Tuning Tips & Techniques
Brand: McGraw Hill LLC
Price: 156.99 EUR
Availability: OnlineOnly

Richard Niemiec(Autor*in)

McGraw Hill LLC (Verlag)

1. Auflage

Erschienen am 22. März 2017

848 Seiten

E-Book

ePUB mit Adobe-DRM

Systemvoraussetzungen

E-Book

PDF mit Adobe-DRM

Systemvoraussetzungen

978-1-259-58969-0 (ISBN)

156,99 €inkl. 7% MwSt.

Systemvoraussetzungen

für ePUB mit Adobe-DRM und PDF mit Adobe-DRM

(Hinweis: Die Auswahl des von Ihnen gewünschten Dateiformats und des Kopierschutzes erfolgt erst im System des E-Book Anbieters)

E-Book Einzellizenz

Als Download verfügbar

Beschreibung

Weitere Details

Weitere Ausgaben

Inhalt

Cover
Title Page
Copyright Page
About the Author
Contents at a Glance
Contents
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 EQUAL Operators: &&, !=
Using IS NULL or IS NOT NULL
Using LIKE
Using Functions
Comparing Mismatched Data Types
Selectivity
The Clustering Factor
The Binary Height
Additional Details Concerning BLEVEL and 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_INTERVAL for 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 SQL Area
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 DDL Statements 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 | SQL Performance 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 SQL TRACE Utility
Simple Steps for SQL TRACE 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 SQL Plan Management (SPM) and SPM Example
SPM Terms
Using SPM
Using Fixed SQL Plan Baselines
Dropping a Plan
Converting from Stored Outlines to SQL Plan 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 FULL Hint
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_NL Hint
The USE_HASH Hint
The QB_NAME Hint
The PUSH_SUBQ Hint
The PARALLEL Hint
The NO_PARALLEL Hint
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_PLSQL Hint
Some Miscellaneous Hints and Notes
Undocumented Hints
Using Hints with Views
Notes on Hints and Stored Outlines (or SQL Plan 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$SQL Views
Selecting from the V$SQLAREA View to Find the Worst Queries
Selecting from the V$SQL View 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!
SQL and 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 SQL Plan Management
Oracle Automatic SQL Tuning
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 SQL Tuning Task
Checking Status of the Tuning Task
Displaying the SQL Tuning Advisor Report
Reviewing the Report Output
Tuning SQL Statements Automatically Using SQL Tuning Advisor
Enabling Automatic SQL Tuning Advisor
Configuring Automatic SQL Tuning Advisor
Viewing Automatic SQL Tuning Results
Using SQL Performance 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
SQL Performance Analyzer
Create a SQL Tuning Set
Create an Analysis Task
Execute Analysis Task
Query SQL Performance Analyzer Advisor Tasks
Cancel an Executing SQL Performance Analyzer Analysis Task
Remove SQL Performance Analyzer Analysis Task
Determine Active SQL Tuning Sets
Remove SQL Tuning Set
Drop SQL Tuning 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/SQL Function Result Cache to Improve Performance (Improved in 12c)
Define PL/SQL Subprograms in a SQL Statement (New in 12c)
Reference Sequences Directly in PL/SQL Expressions
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 SQL Statements (New in 12c)
Use Named Parameters in SQL Function 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/SQL Program 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/SQL Data Type for Integer Operations
Reduce the Calls to SYSDATE
Reduce the Use of the MOD Function
Improve Shared Pool Use by Pinning PL/SQL Objects
Pinning (Caching) PL/SQL Object Statements into Memory
Pinning All Packages
Identify PL/SQL Objects 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/SQL Associative Arrays for Fast Reference Table Lookups
Find and Tune the SQL When Objects Are Used
Consider Time Component When Working with DATE Data Types
Use PL/SQL to Tune PL/SQL
Understand the Implications of PL/SQL Object 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/SQL Example
Create a Procedure Example
Execute the Procedure from PL/SQL Example
Create a Function Example
Execute the GET_CUST_NAME Function from SQL Example
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 DML and DDL Statements 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 (PARALLEL and NO_PARALLEL Hints)
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 SQL and 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/SQL Objects 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 SQL Statements
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

Systemvoraussetzungen

Dateiformat: ePUB
Kopierschutz: Adobe-DRM (Digital Rights Management)

Systemvoraussetzungen:

Computer (Windows; MacOS X; Linux): Installieren Sie bereits vor dem Download die kostenlose Software Adobe Digital Editions (siehe E-Book Hilfe).
Tablet/Smartphone (Android; iOS): Installieren Sie bereits vor dem Download die kostenlose App Adobe Digital Editions oder die App PocketBook (siehe E-Book Hilfe).
E-Book-Reader: Bookeen, Kobo, Pocketbook, Sony, Tolino u.v.a.m. (nicht Kindle)

Das Dateiformat ePUB ist sehr gut für Romane und Sachbücher geeignet – also für „fließenden” Text ohne komplexes Layout. Bei E-Readern oder Smartphones passt sich der Zeilen- und Seitenumbruch automatisch den kleinen Displays an.
Mit Adobe-DRM wird hier ein „harter” Kopierschutz verwendet. Wenn die notwendigen Voraussetzungen nicht vorliegen, können Sie das E-Book leider nicht öffnen. Daher müssen Sie bereits vor dem Download Ihre Lese-Hardware vorbereiten.

Bitte beachten Sie: Wir empfehlen Ihnen unbedingt nach Installation der Lese-Software diese mit Ihrer persönlichen Adobe-ID zu autorisieren!

Weitere Informationen finden Sie in unserer E-Book Hilfe.

Dateiformat: PDF
Kopierschutz: Adobe-DRM (Digital Rights Management)

Systemvoraussetzungen:

Computer (Windows; MacOS X; Linux): Installieren Sie bereits vor dem Download die kostenlose Software Adobe Digital Editions (siehe E-Book Hilfe).
Tablet/Smartphone (Android; iOS): Installieren Sie bereits vor dem Download die kostenlose App Adobe Digital Editions oder die App PocketBook (siehe E-Book Hilfe).
E-Book-Reader: Bookeen, Kobo, Pocketbook, Sony, Tolino u.v.a.m. (nicht Kindle)

Das Dateiformat PDF zeigt auf jeder Hardware eine Buchseite stets identisch an. Daher ist eine PDF auch für ein komplexes Layout geeignet, wie es bei Lehr- und Fachbüchern verwendet wird (Bilder, Tabellen, Spalten, Fußnoten). Bei kleinen Displays von E-Readern oder Smartphones sind PDF leider eher nervig, weil zu viel Scrollen notwendig ist.
Mit Adobe-DRM wird hier ein „harter” Kopierschutz verwendet. Wenn die notwendigen Voraussetzungen nicht vorliegen, können Sie das E-Book leider nicht öffnen. Daher müssen Sie bereits vor dem Download Ihre Lese-Hardware vorbereiten.

Bitte beachten Sie: Wir empfehlen Ihnen unbedingt nach Installation der Lese-Software diese mit Ihrer persönlichen Adobe-ID zu autorisieren!

Weitere Informationen finden Sie in unserer E-Book Hilfe.

Als PDF speichern Als Link merken