book

Oracle Database 12c Release 2 Performance Tuning Tips & Techniques

Name: Oracle Database 12c Release 2 Performance Tuning Tips & Techniques
Author: Richard Niemiec
ISBN: 9781259589690

by Richard Niemiec

March 2017

Intermediate to advanced

848 pages

31h 14m

English

McGraw Hill Computing

Read now

Unlock full access

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 NVARCHAR2Partial IndexesInvisible ColumnsMultiple Indexes on the Same Column ListFetch First x RowsPluggable Databases (PDBs)Oracle Database Cloud Service (Database as a Service)PDB Level: MEMORY_LIMIT and MEMORY_MINIMUM (12cR2)Change Compression at Import TimeAdaptive Query OptimizationPGA_AGGREGATE_LIMITConcurrent Execution for UNION/UNION ALLInvoker Rights Function Can Be Results CachedNew DBMS_UTILITY.EXPAND_SQL_TEXTDefault for Columns Based on SequenceMultiple SSD Devices for Smart Flash CacheConcurrent Cost-Based Optimizer Statistics GatheringEnhanced System StatisticsResource Manager for Runaway QueriesAutomatic Data Optimization (ADO)Global Index Maintenance: Drop and Truncate Partition OperationsASM Disk ScrubbingOnline Capability ImprovementsData Guard ImprovementsRMAN ImprovementsOracle Database 12cR1 (12.1.0.2)In-Memory DatabaseAdvanced Index CompressionAutomatic Big Table CachingFDA Support for Container DatabasesFull Database CachingJSON SupportFIPS 140 Parameter for EncryptionPDB Subset CloningRapid Home Provisioning—Creating “Gold Images”Oracle Database 12cR2 (12.2)Application DevelopmentEnhanced Features to Reduce Costs and Issues for Migration to OracleAvailabilityBig DataCompression and ArchivingOracle RAC and Grid InfrastructureSecurityNew Background Processes in 12cExadata—New with Exadata X6!Version Comparison ChartNew Features ReviewReferences
2 Basic Index Principles (Beginner Developer and Beginner DBA)
Basic Index ConceptsInvisible IndexesMultiple Types of Indexes on the Same Column(s)Concatenated IndexesSuppressing IndexesUsing the NOT EQUAL Operators: <>, !=Using IS NULL or IS NOT NULLUsing LIKEUsing FunctionsComparing Mismatched Data TypesSelectivityThe Clustering FactorThe Binary HeightAdditional Details Concerning BLEVEL and Index HeightUsing HistogramsFast Full ScansSkip-ScansTypes of IndexesB-Tree IndexesBitmap IndexesHash IndexesIndex-Organized TablesReverse Key IndexesFunction-Based IndexesPartitioned IndexesNew 12cR2 Features for Partitioned Indexes Bitmap Join IndexesFast Index RebuildingRebuilding Indexes OnlineTips ReviewReferences

3 Pluggable Databases, Disk Implementation Methodology, and ASM (DBA)
Pluggable Databases (New in Oracle 12c)CDB or PDB Created ObjectsCreating a PDB: Many Ways to Do ItGreat Pluggable Database CommandsALTER SYSTEM While in a PDB and Other Nice CommandsUsing In-Memory (IM) with Pluggable DatabasesOther 12cR2 Features with Pluggable DatabasesSubset Standby (New in Oracle 12cR2)Disk ArraysUse Disk Arrays to Improve Performance and AvailabilityHow Many Disks Do You Need?What Are Some of the RAID Levels Available?The Newer RAID 5Solid-State DisksASM Storage Management (Striping/Mirroring)Setup and Maintenance of the Traditional FilesystemWhat Is the Cost?Storing Data and Index Files in Separate LocationsAvoiding I/O Disk ContentionThe 12c Heat Map and Automatic Data Optimization (ADO)12c I/O Performance Tracking Views (Outliers)Oracle Bigfile TablespacesASM IntroductionCommunication Across IT RolesASM InstancesASM Initialization ParametersASM Installation in 12cSrvctl EnhancementsASM Disk ScrubbingASM Rebalance EnhancementsASM Fast Mirror ResyncASM Filter DriverASM and PrivilegesASM and MultipathingBigfile and ASMAvoiding Disk Contention by Using PartitionsGetting More Information About PartitionsOther Types of PartitioningPartitioned Indexes (Local)Partial IndexesGlobal Index Maintenance: Drop and Truncate Partition OperationsOther Partitioning OptionsIndex PartitioningExporting PartitionsEliminating Fragmentation (Only If Needed—Careful!)Using the Correct Extent SizeAvoiding Chaining by Setting PCTFREE CorrectlyUsing Automatic Segment Space Management (ASSM)Increasing the Log File Size and LOG_CHECKPOINT_INTERVAL for SpeedDetermining If Redo Log File Size Is a ProblemDetermining the Size of Your Log Files and Checkpoint IntervalOther Helpful Redo Log CommandsStoring Multiple Control Files on Different Disks and ControllersOther Disk I/O Precautions and TipsIssues to Consider in the Planning StagesTips ReviewReferences
4 Tuning the Database with Initialization Parameters (DBA)
When Upgrading to Oracle Database 12cUsing SEC_CASE_SENSITIVE_LOGONCrucial Memory Initialization Parameters for PerformancePDB Level: MEMORY_LIMIT and MEMORY_MINIMUMIn-Memory Database (INMEMORY_SIZE)Changing the Initialization Parameters Without a RestartModifying an Initialization Parameter at the PDB LevelInsight into the Initialization Parameters from Oracle UtilitiesViewing the Initialization Parameters with Enterprise ManagerIncreasing Performance by Tuning the DB_CACHE_SIZEUsing V$DB_CACHE_ADVICE in Tuning DB_CACHE_SIZEMonitoring the V$SQLAREA View to Find Bad QueriesSetting DB_BLOCK_SIZE to Reflect the Size of Your Data ReadsSetting SGA_MAX_SIZE to 25 to 50 Percent of the Size Allocated to Main MemoryTuning the SHARED_POOL_SIZE for Optimal PerformanceUsing Stored Procedures for Optimal Use of the Shared SQL AreaSetting the SHARED_POOL_SIZE High Enough to Fully Use the DB_CACHE_SIZEKeeping the Data Dictionary Cache Objects CachedKeeping the Library Cache Reload Ratio at 0 and the Hit Ratio Above 95 PercentUsing Available Memory to Determine If the SHARED_POOL_SIZE Is Set CorrectlyUsing the X$KSMSP Table to Get a Detailed Look at the Shared PoolPoints to Remember About Cache SizeWaits Related to Initialization ParametersUsing Oracle Multiple Buffer PoolsPools Related to DB_CACHE_SIZE and Allocating Memory for DataModifying the LRU AlgorithmPools Related to SHARED_POOL_SIZE and Allocating Memory for StatementsTuning PGA_AGGREGATE_TARGET and PGA_AGGREGATE_LIMITModifying the Size of Your SGA to Avoid Paging and SwappingUnderstanding the Oracle OptimizerHow Optimization Looks at the DataCreating Enough DispatchersHave 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 ParametersInitialization Parameters over the YearsFinding Undocumented Initialization ParametersUnderstanding the Typical ServerModeling a Typical ServerSizing the Oracle Applications DatabaseTips ReviewReferences
5 Tuning with Enterprise Manager Cloud Control (DBA and Developer)
Oracle Enterprise Manager Basics and Accessing OEM via Oracle CloudStarting with All Targets and Other GroupingsMonitoring and Tuning Using the OEM Performance MenuPerformance Tab: Top ActivityPerformance Tab: SQL | SQL Performance AnalyzerPerformance Tab: Real-Time ADDMPerformance Tab: SQL | Access AdvisorPerformance Tab: Manage Optimizer StatisticsPerformance Tab: AWR | AWR AdministrationPerformance Tab: ASH AnalyticsMonitoring and Tuning Using the OEM Administration MenuDatabase Administration Tab: Storage|TablespacesDatabase Administration Tab: In-Memory Central and Initialization ParametersDatabase Administration Tab: All Initialization ParametersDatabase Administration Tab: Resource Manager (Consumer Groups)Monitoring and Tuning Using the OEM Database or Cluster Database MenuDatabase Tab: Job ActivityCluster Database Tab: Configuration|Database TopologyMonitoring the HostsMonitoring the Application Servers and Web ApplicationsReal Application Testing (Database Replay)SummaryTips ReviewReferences
6 Using EXPLAIN, TRACE, and SQL Plan Management (Developer and DBA)
The Oracle SQL TRACE UtilitySimple Steps for SQL TRACE with a Simple QueryThe Sections of a TRACE OutputDigging into the TKPROF OutputUsing DBMS_MONITORSetting Trace Based on Session ID and Serial NumberSetting Trace Based on Client IdentifierSetting Trace for the Service Name/Module Name/Action NameEnabled Tracing ViewsTRCSESS Multiple Trace Files into One FileUsing EXPLAIN PLAN AloneAn Additional EXPLAIN Example for a Simple QueryEXPLAIN PLAN—Read It Top to Bottom or Bottom to Top?Tracing/Explaining Problem Queries in Developer ProductsImportant Columns in the PLAN_TABLE TableUsing DBMS_XPLANInitialization Parameters for Undocumented TRACEUsing Stored OutlinesDropping Stored OutlinesUsing SQL Plan Management (SPM) and SPM ExampleSPM TermsUsing SPMUsing Fixed SQL Plan BaselinesDropping a PlanConverting from Stored Outlines to SQL Plan ManagementAdaptive Plans (12c New Feature) and SPMTips ReviewReferences
7 Basic Hint Syntax (Developer and DBA)
Top Hints UsedUse Hints SparinglyFix the Design FirstAvailable Hints and GroupingsExecution PathAccess MethodsQuery Transformation HintsJoin OperationsParallel ExecutionOther HintsSpecifying a HintSpecifying Multiple HintsWhen Using an Alias, Hint the Alias, Not the TableThe HintsThe Oracle Demo Sample HR SchemaThe FIRST_ROWS HintThe ALL_ROWS HintThe FULL HintThe INDEX HintThe NO_INDEX HintThe INDEX_JOIN HintThe INDEX_COMBINE HintThe INDEX_ASC HintThe INDEX_DESC HintThe INDEX_FFS HintThe ORDERED HintThe LEADING HintThe NO_EXPAND HintThe DRIVING_SITE HintThe USE_MERGE HintThe USE_NL HintThe USE_HASH HintThe QB_NAME HintThe PUSH_SUBQ HintThe PARALLEL HintThe NO_PARALLEL HintThe PARALLEL_INDEX HintThe APPEND HintThe NOAPPEND HintThe CACHE HintThe NOCACHE HintThe RESULT_CACHE HintThe CURSOR_SHARING_EXACT HintThe INMEMORY and NO_INMEMORY and Other IM HintsThe USE_INVISIBLE_INDEXES HintThe CONTAINERS HintThe WITH_PLSQL HintSome Miscellaneous Hints and NotesUndocumented HintsUsing Hints with ViewsNotes on Hints and Stored Outlines (or SQL Plan Baselines)Why Isn’t My Hint Working?Hints at a GlanceTips ReviewReferences
8 Query Tuning (Developer and Beginner DBA)
Which Queries Do I Tune? Querying V$SQLAREA and V$SQL ViewsSelecting from the V$SQLAREA View to Find the Worst QueriesSelecting from the V$SQL View to Find the Worst QueriesOracle 12c Views for Locating Resource-Intensive Sessions and QueriesSelecting from V$SESSMETRIC to Find Current Resource-Intensive SessionsViewing Available AWR SnapshotsSelecting from the DBA_HIST_SQLSTAT View to Find the Worst QueriesWhen Should I Use an Index?Selecting Query Text from the DBA_HIST_SQLTEXT ViewSelecting Query EXPLAIN PLAN from the DBA_HIST_SQL_PLAN ViewWhat If I Forget the Index?Creating an IndexInvisible IndexChecking the Index on a TableIs the Column Properly Indexed?What If I Create a Bad Index?Exercising Caution When Dropping an IndexIndexing the Columns Used in the SELECT and WHEREUsing the Fast Full ScanMaking the Query “Magically” FasterCaching a Table in MemoryUsing the Result CacheChoosing Among Multiple Indexes (Use the Most Selective)The Index MergeIndexes That Can Get SuppressedFunction-Based IndexesVirtual ColumnsThe “Curious” ORUsing the EXISTS Function and the Nested SubqueryThat Table Is Actually a View!SQL and Grand Unified TheoryTuning Changes in Oracle Database 12cOracle 12c Adaptive Query OptimizationAdaptive StatisticsOracle 12c Changes in Statistics Gathering and Two New HistogramsOracle 12c Changes in SQL Plan ManagementOracle Automatic SQL TuningEnsuring the Tuning User Has Access to the APIsCreating the Tuning TaskMaking Sure the Task Can Be Seen in the Advisor LogExecuting the SQL Tuning TaskChecking Status of the Tuning TaskDisplaying the SQL Tuning Advisor ReportReviewing the Report OutputTuning SQL Statements Automatically Using SQL Tuning AdvisorEnabling Automatic SQL Tuning AdvisorConfiguring Automatic SQL Tuning AdvisorViewing Automatic SQL Tuning ResultsUsing SQL Performance Analyzer (SPA)Tips ReviewReferences
9 Table Joins and Other Advanced Tuning (Advanced DBA and Developer)
Database Replay (capture/replay)Set Up Source Database for Database Replay CapturePrepare to Capture WorkloadCapture the WorkloadPrepare the Workload for ReplayProcess the Workload for ReplayPrepare to Replay the WorkloadExecute the Workload ReplaySQL Performance AnalyzerCreate a SQL Tuning SetCreate an Analysis TaskExecute Analysis TaskQuery SQL Performance Analyzer Advisor TasksCancel an Executing SQL Performance Analyzer Analysis TaskRemove SQL Performance Analyzer Analysis TaskDetermine Active SQL Tuning SetsRemove SQL Tuning SetDrop SQL Tuning SetJoin MethodsNESTED LOOPS JoinsSORT-MERGE JoinsCLUSTER JoinsHASH JoinsINDEX-MERGE JoinsTable Join Initialization ParametersSORT-MERGE and HASH Join ParametersA Two-Table Join: Equal-Sized Tables (Cost-Based)A Two-Table INDEXED Join: Equal-Sized Tables (Cost-Based)Forcing a Specific Join MethodEliminating Join Records (Candidate Rows) in Multitable JoinsA Two-Table Join Between a Large and Small TableThree-Table Joins: Not as Much FunBitmap Join IndexesBitmap IndexesBitmap Join IndexBest Uses for the Bitmap Join IndexThird-Party Product TuningExample 1Example 2Example 3Tuning Distributed QueriesWhen You Have Everything TunedMiscellaneous Tuning SnippetsExternal TablesSnapshot Too Old: Developer Coding IssueSet Event to Dump Every Wait14 Hours to 30 Seconds with the EXISTS OperatorTuning at the Block Level (Advanced)Key Sections of a Block DumpA Brief Look at an Index Block DumpTuning Using Simple Mathematical TechniquesTraditional Mathematical AnalysisSeven-Step MethodologyDeriving Performance EquationsPattern InterpretationMathematical Techniques ConclusionsTips ReviewReferences
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 ExpressionsIdentity 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 CallsSimplify Loops with the CONTINUE StatementLeverage Compile-Time Warnings to Catch Programming Mistakes (Improved in 12c)Increase Performance with Native CompilationMaximize Performance with the Optimizing CompilerUse DBMS_APPLICATION_INFO for Real-Time MonitoringLog Timing Information in a Database TableReduce PL/SQL Program Unit Iterations and Iteration TimeUse ROWID for Iterative ProcessingStandardize on Data Types, IF Statement Order, and PLS_INTEGEREnsure the Same Data Types in Comparison OperationsOrder IF Conditions Based on the Frequency of the ConditionUse the PLS_INTEGER PL/SQL Data Type for Integer OperationsReduce the Calls to SYSDATEReduce the Use of the MOD FunctionImprove Shared Pool Use by Pinning PL/SQL ObjectsPinning (Caching) PL/SQL Object Statements into MemoryPinning All PackagesIdentify PL/SQL Objects That Need to Be PinnedUse and Modify DBMS_SHARED_POOL.SIZESFind Large ObjectsGet Detailed Object Information from DBA_OBJECT_SIZEGet Contiguous Space Currently in the Shared PoolFind Invalid ObjectsFind Disabled TriggersUse PL/SQL Associative Arrays for Fast Reference Table LookupsFind and Tune the SQL When Objects Are UsedConsider Time Component When Working with DATE Data TypesUse PL/SQL to Tune PL/SQLUnderstand the Implications of PL/SQL Object LocationUse Rollback Segments to Open Large CursorsActive Transaction Management: Process Large Quantities of DataUse Temporary Database Tables for Increased PerformanceLimit the Use of Dynamic SQLUse Pipelined Table Functions to Build Complex Result SetsLeave Those Debugging Commands Alone!The “Look and Feel”: Just for the BeginnersPL/SQL ExampleCreate a Procedure ExampleExecute the Procedure from PL/SQL ExampleCreate a Function ExampleExecute the GET_CUST_NAME Function from SQL ExampleCreate a Package ExampleDatabase Trigger Example Using PL/SQLTips ReviewReferences
11 Oracle Cloud, Exadata, Tuning RAC, and Using Parallel Features
The March to the Cloud (Past and Present)The Oracle CloudExadata Database MachineExadata Terminology and the BasicsExadata StatisticsExadata Storage Expansion Rack BrieflySmart ScansFlash CacheStorage IndexesHybrid Columnar CompressionI/O Resource ManagementUse All Oracle Security Advantages with ExadataBest PracticesSummary: Exadata = Paradigm Shift!Oracle Database Appliance (ODA)SuperCluster Using the M7 SPARC ChipOther Oracle Hardware to ConsiderOracle Big Data Appliance X6-2ZFS Storage ServersStorageTek Modular Library SystemParallel DatabasesReal Application Clusters (RAC)Oracle RAC ArchitectureInternal Workings of the Oracle RAC SystemRAC Performance Tuning OverviewRAC Cluster Interconnect PerformanceFinding RAC Wait Events—Sessions WaitingRAC Wait Events and Interconnect StatisticsCluster Interconnect Tuning—Hardware TierBasic Concepts of Parallel OperationsBasic Concepts of Parallel OperationsParallel DML and DDL Statements and OperationsManaging Parallel Server Resources and Parallel Statement QueuingParallelism and PartitionsInter- and Intra-operation ParallelizationExamples of Using Inter- and Intra-operations (PARALLEL and NO_PARALLEL Hints)Creating Table and Index Examples Using Parallel OperationsMonitoring Parallel Operations via the V$ ViewsUsing EXPLAIN PLAN and AUTOTRACE on Parallel OperationsUsing the SET AUTOTRACE ON/OFF CommandTuning Parallel Execution and the Initialization ParametersParallel LoadingOptimizing Parallel Operations in RACObjectives of Parallel OperationsRAC Parallel Usage ModelsParallel Initialization ParametersV$ Views for Viewing Parallel StatisticsCreate Table AsParallel Index BuildsPerformance Considerations and SummaryOther Parallel NotesOracle Documentation Is OnlineTips ReviewReferences
12 The V$ Views (Developer and DBA)
Creating and Granting Access to V$ ViewsObtaining a Count and Listing of All V$ ViewsGetting a Listing for the X$ Scripts That Make Up the V$ ViewsExamining the Underlying Objects That Make Up the DBA_ ViewsUsing Helpful V$ ScriptsBasic Database InformationBasic Automatic Workload Repository (AWR) InformationBasic Licensing InformationDatabase Options Installed in Your DatabaseSummary of Memory Allocated (V$SGA)Querying V$IM_SEGMENTS After Setting the INMEMORY_SIZEAutomatic Memory Management and MEMORY_TARGETDetailed Memory Allocated (V$SGASTAT)Detailed Memory Allocated (V$SGASTAT) for a PDB vs. Root CDBFinding spfile.ora/init.ora Settings in V$PARAMETERModifying an Initialization Parameter at PDB LevelDetermining 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 InformationQuerying V$CONTAINERS When Using Pluggable DatabasesQuerying V$PDBS for Pluggable Database InformationUsing the Result CacheIdentifying PL/SQL Objects That Need to Be Kept (Pinned)Finding Problem Queries by Monitoring V$SESSION_LONGOPSFinding Problem Queries by Querying V$SQLAREAFinding Out What Users Are Doing and Which Resources They Are UsingFinding Out Which Objects a User Is AccessingGetting Detailed User InformationUsing IndexesIdentifying Locking IssuesKilling the Problem SessionFinding Users with Multiple SessionsQuerying for Current ProfilesFinding Disk I/O IssuesChecking for Privileges and RolesWait Events V$ ViewsSome of the Major V$ View CategoriesTips ReviewReferences
13 The X$ Tables and Internals Topics (Advanced DBA)
Introducing the X$ TablesMisconceptions About the X$ TablesGranting Access to View the X$ TablesCreating V$ Views and X$ Tables in 12cThe X$ Tables Comprising the V$ ViewsObtaining a List of All the X$ Tables in 12cObtaining a List of All the X$ Indexes in 12cUsing Hints with X$ Tables and IndexesMonitoring Space Allocations in the Shared PoolCreating Queries to Monitor the Shared PoolORA-04031 ErrorsLarge Allocations Causing ContentionShared Pool FragmentationLow Free Memory in Shared and Java PoolsLibrary Cache Memory UseHigh Number of Hard ParsesMutex/Latch Waits and/or SleepsMiscellaneous X$ Table NotesObtaining Information About Redo Log FilesSetting Initialization ParametersCase 1Case 2Case 3Case 4Case 5Exploring Buffer Cache/Data Block DetailsBuffer StatusesSegments Occupying Block BuffersHot Data Blocks and the Causes of Latch Contention and Wait EventsObtaining Database- and Instance-Specific InformationEffective X$ Table Use and StrategyOracle Internals TopicsTracesDBMS_TRACE PackageEventsDumpsORADEBUGtrcsess UtilityReading the Trace FileWait Information and Response TimeRecursive CallsModule InfoCommitUnmapBind VariablesErrorsSome Common X$ Table GroupsSome Common X$ Table and Non-V$ Fixed View AssociationsCommon X$ Table JoinsX$ Table Naming Conventions (My Favorite Section of This Book!)X$ Table Naming Conventions with CON_ID, and INMEMORYFuture Version Impact in 12cR2Tips ReviewReferences
14 Using Statspack and the AWR Report to Tune Waits, Latches, and Mutexes
What’s New in 12cR2 (12.2) Statspack and the AWR ReportInstalling StatspackSecurity of the PERFSTAT AccountPost-InstallationGathering StatisticsRunning the Statistics ReportThe Automatic Workload Repository (AWR) and the AWR ReportManually Managing the AWRAWR Automated SnapshotsAWR Snapshot ReportsRun the AWR Report in Oracle Enterprise Manager Cloud ControlInterpreting the Statspack and AWR Report OutputThe Header Information and Cache SizesThe Load ProfileInstance EfficiencyShared Pool StatisticsTop Wait EventsOracle BugsThe Life of an Oracle Shadow ProcessRAC Wait Events and Interconnect StatisticsTop SQL StatementsInstance Activity StatisticsTablespace and File I/O StatisticsSegment StatisticsAdditional Memory StatisticsUNDO StatisticsLatch and Mutex StatisticsTuning and Viewing at the Block Level (Advanced)Dictionary and Library Cache StatisticsSGA Memory StatisticsNondefault Initialization ParametersTop 15 Things to Look for in AWR Report and Statspack OutputManaging the Statspack DataUpgrading StatspackDeinstalling StatspackQuick Notes on the New ADDM ReportScripts in 12cR2Tips ReviewReferences
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 TunedTop 10 “Memory Abusers” as a Percent of All StatementsBuffer Cache Hit RatioDictionary Cache Hit RatioLibrary Cache Hit RatioPGA Memory Sort RatioPercentage of Data Buffers Still FreeUsing the Result Cache EffectivelyPinning/Caching ObjectsDisk Performance Index (DPI)Top 25 “Disk-Read Abuser” Statements TunedTop 10 Disk-Read Abusers as Percentage of All StatementsTables/Indexes Separated or Using ASMMission-Critical Table ManagementKey Oracle Files SeparatedAutomatic Undo ManagementUsing Pluggable Databases EffectivelyTotal Performance Index (TPI)Overall System Review ExampleRating SystemExample System Review Rating CategoriesItems Requiring Immediate ActionOther Items Requiring ActionSystem Information ListMemory-Related ValuesDisk-Related ValuesCPU-Related ValuesBackup- and Recovery-Related InformationNaming Conventions and/or Standards and Security Information QuestionsDBA Knowledge RatingOther Items to Consider in Your TPI and System ReviewTips ReviewReferences
16 Monitor the System Using Unix Utilities (DBA)
Unix/Linux UtilitiesUsing the sar Command to Monitor CPU Usagesar –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/SwappingUsing sar –p to Report Paging ActivitiesUsing sar –w to Report Swapping and Switching ActivitiesUsing sar –r to Report Free Memory and Free SwapUsing sar –g to Report Paging ActivitiesUsing sar –wpgr to Report on Memory ResourcesFinding the Worst User on the System Using the top CommandMonitoring ToolsUsing the uptime Command to Monitor CPU LoadUsing the mpstat Command to Identify CPU BottlenecksCombining ps with Selected V$ ViewsCPU/Memory Monitoring Tool (Task Manager) on WindowsUsing the iostat Command to Identify Disk I/O BottlenecksUsing iostat –d for Disk Drives sd15, sd16, sd17, and sd18Using iostat –DUsing iostat –xCombining iostat –x with Logic in a Shell ScriptUsing the ipcs Command to Determine Shared MemoryUsing the vmstat Command to Monitor System LoadMonitoring Disk Free SpaceThe df CommandThe du CommandMonitoring Network Performance with netstatModifying the Configuration Information FileOther Factors That Affect PerformanceOther Sources to Improve PerformanceTips ReviewReferences
A Key Initialization Parameters (DBA)
Obsoleted/Desupported Initialization ParametersDeprecated Initialization ParametersTop 25 Initialization ParametersTop 20 Initialization Parameters Not to ForgetTop 13 Undocumented Initialization Parameters (As I See It)Bonus 11 Undocumented Initialization ParametersListing of Documented Initialization Parameters (V$PARAMETER)Listing of Undocumented Initialization Parameters (X$KSPPI/X$KSPPCV)Additional Oracle Applications NotesConcurrent ManagersApplications—Finding Module-Specific PatchesDiagnostics Data Collection: EBS AnalyzersWeb Server TuningTimeoutsDatabase Initialization Parameter SizingTop 10 Reasons Not to Write a BookTips ReviewReferences
B The V$ Views (DBA and Developer)
Creation of V$ and GV$ Views and X$ TablesA List of Oracle 12c (12.2.0.0.1) GV$ ViewsA List of Oracle 12c (12.2.0.0.1) V$ ViewsOracle 12c Scripts for the X$ Tables Used to Create the V$ Views
C The X$ Tables (DBA)
Oracle 12cR2 X$ Tables Ordered by NameOracle 12cR2 X$ IndexesOracle 12cR2 V$ Views Cross-Referenced to the X$ Tables
Index

Content preview from Oracle Database 12c Release 2 Performance Tuning Tips & Techniques

Images

CHAPTER

16 Monitor the System Using Unix Utilities (DBA)

Part of being able to solve performance problems includes being able to use operating system utilities effectively. Using the correct utilities to find CPU, memory, and disk I/O issues is crucial to identifying where performance problems exist. Today’s DBAs and system managers increasingly include performance management as part of their duties. This chapter focuses on a few key and basic commands that will help you look at Oracle. It is meant for DBAs who are not as familiar with Unix or Linux.

Basically, two main categories of activities are needed for system management. The first, accounting ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Oracle Database 12c Release 2 In-Memory: Tips and Techniques for Maximum Performance

Publisher Resources

ISBN: 9781259589690

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

Oracle Database 12c Release 2 Performance Tuning Tips & Techniques

by Richard Niemiec

CHAPTER

16

Monitor the System Using Unix Utilities (DBA)

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.