book

Oracle Database 11g Release 2 Performance Tuning Tips & Techniques

Name: Oracle Database 11g Release 2 Performance Tuning Tips & Techniques
Author: Richard Niemiec
ISBN: 9780071780278

by Richard Niemiec

March 2012

Intermediate to advanced

1184 pages

30h 45m

English

McGraw Hill Computing

Read now

Unlock full access

Cover
Epigraph1
Title
Copyright
Epigraph2
About the Author
Contents
Acknowledgments
Introduction
1: Introduction to 11g R1 & R2 New Features (DBA and Developer)
Exadata Is What’s Next!Advanced CompressionAutomatic Diagnostic RepositoryAutomatic Shared Memory Management (ASMM) ImprovementsAutomatic Storage Management (ASM) ImprovementsASM Preferred Mirror ReadRolling Upgrades/PatchingFaster RebalanceASM Diskgroup CompatibilityASMCMD ExtensionsAutomatic SQL TuningData Guard EnhancementsSnapshot StandbyActive Data GuardMixed Platform SupportAdvanced Compression for Logical Standby (11gR2)Transparent Data Encryption Support for Logical StandbyData Pump Compression EnhancementsData Pump Encrypted Dump File SetsData Pump Legacy ModeEnhanced StatisticsEnhanced I/O StatisticsReducing the Gathering of Partitioned Objects StatisticsPending StatisticsMulticolumn StatisticsExpression StatisticsFlashback Data ArchiveHealth MonitorIncident Packaging Service (IPS)Invisible IndexesNew Partitioning FeaturesInterval PartitioningREF PartitioningSystem PartitioningVirtual Column-based PartitioningPartition AdvisorRead-Only TablesReal Application Clusters (RAC) One Node and RAC PatchingReal Application TestingDatabase ReplaySQL Performance Analyzer (SPA)Result CacheRMAN New FeaturesOptimized BackupsImproved Handling of Long-term BackupsParallel Backup of Very Large DatafilesFaster Backup Compression (Improved Compression)Active Database DuplicationBetter Recovery Catalog ManagementArchived Log Deletion Policy EnhancementsData Recovery AdvisorVirtual Private CatalogProactive Health CheckBlock Recovery (Flashback Logs)Block Recovery (Physical Standby)SecureFilesCompressionEncryptionDeduplicationEnhancements to Streams (Golden Gate Is the Future of Streams)XStream InXStream OutAdvanced Compression Support for Streams (11gR2)Shrink Temporary TablespaceTransparent Data Encryption (TDE) ImprovementsNew Background Processes in 11gVersion Comparison ChartNew Features ReviewReferences

2: Basic Index Principles (Beginner Developer and Beginner DBA)
Basic Index ConceptsInvisible IndexesConcatenated 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 IndexesBitmap Join IndexesFast Index RebuildingRebuilding Indexes OnlineTips ReviewReferences
3: Disk Implementation Methodology and ASM (DBA)
Disk Arrays: Not a Choice AnymoreUse Disk Arrays to Improve Performance and AvailabilityHow Many Disks Do You Need?What Are Some of the RAID Levels Available?The Newer RAID 5Setup and Maintenance of the Traditional FilesystemWhat Is the Cost?Distributing “Key” Datafiles Across Hardware DisksStoring Data and Index Files in Separate LocationsAvoiding I/O Disk ContentionMoving Datafiles to Balance File I/OLocally Managed TablespacesCreating Tablespaces as Locally ManagedMigrating Dictionary-Managed Tablespaces to Locally ManagedOracle Bigfile TablespacesOracle Managed FilesASM IntroductionCommunication Across IT RolesASM InstancesASM Initialization ParametersASM InstallationASM Parameters and SGA SizingASM and PrivilegesASM DisksASM and MultipathingASM DiskgroupsASM Diskgroups and DatabasesASM Redundancy and Failure GroupsNew Space-Related ColumnsCluster Synchronization ServicesDatabase Instances and ASMDatabase Consolidation and Clustering with ASMDatabase Processes to Support ASMBigfile and ASMDatabase Initialization Parameters to Support ASMASM and Database Deployment Best PracticesASM Storage Management and AllocationASM Rebalance and RedistributionAvoiding Disk Contention by Using PartitionsGetting More Information About PartitionsOther Types of PartitioningNew Partitioning Options in Oracle 11gR2Other Partitioning OptionsIndex PartitioningExporting PartitionsEliminating FragmentationUsing the Correct Extent SizeCreate a New Tablespace and Move the Segments to ItExporting and Then Reimporting the TableTo Avoid Chaining, Set PCTFREE CorrectlyRebuilding the DatabaseIncreasing 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 IntervalFast Recovery AreaIncreasing Chances of Recovery: Committing After Each BatchIsolating Large Transactions to Their Own Rollback SegmentsUsing the UNDO TablespaceMonitoring UNDO SpaceKilling Problem SessionsHave 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 11gR2Using SEC_CASE_SENSITIVE_LOGON (new in 11g)Identifying Crucial Initialization ParametersChanging the Initialization Parameters Without a RestartInsight 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_SIZEKeeping the Hit Ratio for the Data Cache Above 95 PercentMonitoring 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 Hit Ratio at or above 95 PercentKeeping 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 the PGA_AGGREGATE_TARGET for Optimal Use of MemoryModifying 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)25 Important Initialization Parameters to ConsiderTop 25 Initialization ParametersInitialization Parameters over the YearsFinding Undocumented Initialization ParametersUnderstanding the Typical ServerModeling a Typical ServerSizing the Oracle Applications DatabaseTips ReviewReferences
5: Enterprise Manager and Grid Control (DBA and Developer)
The Enterprise Manager (EM) BasicsStarting with All Targets and Other GroupingsSQL Performance Analyzer (SPA)Automatic Database Diagnostic Monitor (ADDM)Database Instance Server Tab and Database Administration TabDatabase Instance Server Tab: TablespacesDatabase Instance Server Tab: Instance Level FocusDatabase Instance Server Tab: All Initialization ParametersDatabase Instance Server Tab: Manage Optimizer StatisticsDatabase Instance Server Tab: Resource Manager (Consumer Groups)Database Maintenance TabDatabase Topology TabDatabase Performance TabMonitoring the HostsMonitoring the Application ServersMonitoring the Web ApplicationsSQL AdvisorsDeployments Tab (Patching Options)Scheduler Central and the Jobs TabReports TabAutomatic Storage Management PerformanceReal Application Testing (Database Replay)EM for ExadataSummaryTips ReviewReferences
6: Using EXPLAIN and SQL PLAN MANAGMENT (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_MONITORTRCSESS Multiple Trace Files into One FileUsing EXPLAIN PLAN AloneEXPLAIN PLAN—Read It Top to Bottom or Bottom to Top?Yet Another EXPLAIN PLAN Output Method: Building the Tree StructureAnother Example Using the Tree ApproachTracing/Explaining Problem Queries in Developer ProductsImportant Columns in the PLAN_TABLE TableInitialization Parameters for Undocumented TRACEUsing Stored OutlinesUsing SQL Plan Management (SPM) (11g New Feature)Converting from Stored Outlines to SQL Plan ManagementTips 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 PQ_DISTRIBUTE HintThe APPEND HintThe NOAPPEND HintThe CACHE HintThe NOCACHE HintThe RESULT_CACHE HintThe CURSOR_SHARING_EXACT HintSome Miscellaneous Hints and NotesUndocumented HintsUsing Hints with ViewsNotes on Hints and Stored Outlines (or SQL Plan Baselines in 11g)Why Isn’t My Hint Working?Hints at a GlanceTips ReviewReferences
8: Query Tuning: Developer and Beginner DBA
What 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 11g 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 QueriesSelecting Query Text from the DBA_HIST_SQLTEXT ViewSelecting Query EXPLAIN PLAN from the DBA_HIST_SQL_PLAN ViewWhen Should I Use an Index?What 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 New 11g 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 11gOracle 11g Automatic SQL TuningEnsuring the Tuning User Has Access to the APICreating the Tuning TaskMaking Sure the Task Can Be Seen in the Advisor LogExecuting the SQL Tuning TaskCheck 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)
Real Application TestingDatabase ReplaySQL Performance AnalyzerCreate a SQL Tuning SetCreate an Analysis TaskExecute Analysis TaskQuery 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 MethodologyPattern InterpretationMathematical Techniques ConclusionsJoin Tuning: Relational vs. Object-Relational PerformanceModels UsedResultsConclusionTips ReviewReferences
10: Using PL/SQL to Enhance Performance (Developer and DBA)
Leverage the PL/SQL Function Result Cache to Improve Performance (New in 11g)Reference Sequences Directly in PL/SQL Expressions (New in 11g)Use Named Parameters in SQL Function Calls (New in 11g)Simplify loops with the CONTINUE Statement (New in 11g)Leverage Compile-Time Warnings to Catch Programming Mistakes (Improved in 11g)Using Table Triggers (Improved in 11g)Increase Performance with Native Compilation (Improved in 11g)Maximize Performance with the Optimizing Compiler (Improved in 11g)Use 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 the 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 CursorsUse Active Transaction Management to 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: Exadata, Tuning RAC, and Using Parallel Features
Exadata Terminology and the BasicsExadata StatsExadata Storage Expansion Rack BrieflyExalogic BrieflySmart ScansFlash CacheStorage IndexesHybrid Columnar Compression (11.2)I/O Resource Management (IORM)Use All Oracle Security Advantages with ExadataBest PracticesSummary: Exadata = Paradigm Shift!Oracle Database ApplianceSPARC SuperClusterOracle Exalytics Business Intelligence MachineOther Oracle Hardware to ConsiderThe Oracle Big Data ApplianceZFS Storage ServersPillar Storage SystemStorageTek Modular Library SystemThe Oracle Public Cloud and the Oracle Social NetworkParallel DatabasesReal Application Clusters (RAC)Oracle RAC ArchitectureInternal Workings of the Oracle RAC SystemSCN ProcessingSummaryRAC Performance Tuning OverviewRAC Cluster Interconnect PerformanceFinding RAC Wait Events—Sessions WaitingRAC Wait Events and Interconnect StatisticsCluster Interconnect Tuning—Hardware TierTuning RAC Using Enterprise Manager Grid ControlBasic Concepts of Parallel OperationsParallel DML and DDL Statements and OperationsParallel DML Statements and OperationsManaging Parallel Server Resources and Parallel Statement QueuingParallelism and PartitionsInter- and Intra-operation ParallelizationExamples of Using Inter- and Intra-operations (PARALLEL and NOPARALLEL Hints)Creating Table and Index Examples Using Parallel OperationsReal-World Example of Distributing Data for Effective Parallel OperationsParallel DML Statements and ExamplesParallel DML RestrictionsParallel DML Statement ExamplesMonitoring Parallel Operations via the V$ ViewsV$PQ_TQSTATV$PQ_SYSSTATV$PQ_SESSTATUsing EXPLAIN PLAN and AUTOTRACE on Parallel OperationsTuning Parallel Execution and the Initialization ParametersParallel LoadingPerformance Comparisons and Monitoring Parallel OperationsOptimizing Parallel Operations in RACObjectives of Parallel OperationsRAC Parallel Usage ModelsINIT.ORA ParametersV$ Views for Viewing Parallel StatisticsParallel Configuration and Associated Baseline TestParallel Query Test ExamplesCreate Table AsIndex 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)Automatic Memory Management (AMM) and MEMORY_TARGETDetailed Memory Allocated (V$SGASTAT)Finding spfile.ora/init.ora Settings in V$PARAMETERDetermining Hit Ratio for Data (V$SYSSTAT & V$SYSMETRIC)Determining Hit Ratio for the Data Dictionary (V$ROWCACHE)Determining Hit Ratio for the Shared SQL and PL/SQL (V$LIBRARYCACHE)Using 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 SessionsFinding Disk I/O IssuesFinding Rollback Segment ContentionDetermining Whether Freelists Are SufficientChecking for Privileges and RolesWait Events V$ ViewsSome of the Major V$ View CategoriesTips ReviewReferences
13: The X$ Tables (Advanced DBA)
Introducing the X$ TablesMisconceptions about the X$ TablesGranting Access to View the X$ TablesCreating V$ Views and X$ TablesObtaining a List of the X$ Tables That Make Up the V$ ViewsObtaining a List of All the X$ TablesObtaining a List of All the X$ IndexesUsing 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 Hit RatioHigh Number of Hard ParsesMutex/Latch Waits and/or SleepsMiscellaneousObtaining Information about Redo Log FilesSetting Initialization ParametersCase 1Case 2Case 3Case 4Case 5Buffer 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 StrategyRelated Oracle 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 ConventionsFuture Version ImpactTips ReviewReferences
14: Using Statspack and the AWR Report to Tune Waits, Latches, and Mutexes
What’s New in 11gR2 (11.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 Grid ControlInterpreting the Statspack and AWR Report OutputThe Header InformationThe Load ProfileInstance EfficiencyTop 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 10 Things to Look for in AWR Report and Statspack OutputManaging the Statspack DataUpgrading StatspackDeinstalling StatspackQuick Notes on the New ADDM ReportScripts in 11gR2Tips 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 Abusers” 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 SeparatedMission-Critical Table ManagementKey Oracle Files SeparatedAutomatic UNDO ManagementTemporary Segment BalanceTotal 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$ ViewsUsing 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 CommandMonitoring Network PerformanceMonitoring Using the spray CommandMonitoring Network Performance with nfsstat –cMonitoring Network Performance with netstatDisplaying Current Values of Tunable ParametersModifying 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)Oracle Applications Release 12 Recommendations (Note: 396009.1)Database 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 11g (11.2.0.1.0) GV$ ViewsA List of Oracle 11g (11.2.0.1.0) V$ ViewsOracle 11g Scripts for the X$ Tables Used to Create the V$ Views
C: The X$ Tables (DBA)
Oracle 11gR2 X$ Tables Ordered by NameOracle 11g X$ IndexesOracle 11g V$ Views Cross-Referenced to the X$ Tables
Index

Content preview from Oracle Database 11g Release 2 Performance Tuning Tips & Techniques

CHAPTER

Using EXPLAIN and SQL PLAN MANAGMENT (Developer and DBA)

Finding and fixing problem queries has a lot to do with using the tools that are available. Different tools need to be used for different situations. The tools covered in this chapter are Oracle’s provided utilities: SQL TRACE (deprecated, but still works in 11g), TKPROF, EXPLAIN PLAN, and STORED OUTLINES (also known as PLAN STABILITY). With Oracle 10g, these tools were enhanced, including the addition of the DBMS_MONITOR and DBMS_SESSION packages and TRCSESS. The SQL tracing options ...

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 10g Performance Tuning Tips & Techniques

Publisher Resources

ISBN: 9780071780261

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

Oracle Database 11g Release 2 Performance Tuning Tips & Techniques

by Richard Niemiec

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.