book

Oracle Database 10g Performance Tuning Tips & Techniques

by Richard Niemiec

July 2007

Intermediate to advanced

967 pages

25h 44m

English

McGraw Hill Computing

Read now

Unlock full access

Installation ImprovementsSYSAUX TablespaceAutomatic Storage ManagementCluster Ready Services (CRS)Server-Generated AlertsAutomatic Workload Repository (AWR)Automatic Database Diagnostic Monitor (ADDM)SQL Tuning AdvisorAutomatic Shared Memory Management (ASMM)Flash Recovery AreaRecycle BinRecovery Manager ChangesTransparent Data Encryption (10gR2)LogMiner ChangesNew DBMS_STATS OptionsTracing EnhancementsDBMS_SCHEDULERDefault (Permanent) TablespaceTemporary Tablespace GroupsRename TablespacesBigfile TablespacesShrinking SegmentsData PumpCross-Platform Transportable TablespacesWrite to External TableAutomatic Undo Retention TuningV$SESSION Include New InformationOEM ChangesGrid ControlNew Background Processes in 10gVersion Comparison TableNew Features ReviewReferences
Basic Index ConceptsConcatenated IndexesSuppressing IndexesUsing the NOT EQUAL Operators '<>', '!='Using IS NULL or IS NOT NULLUsing FunctionsComparing Mismatched Data TypesSelectivityThe Clustering FactorThe Binary HeightUsing HistogramsFast Full ScansSkip-ScansTypes of IndexesB-Tree IndexesBitmap IndexesHash IndexesIndex-Organized TablesReverse Key IndexesFunction-Based IndexesPartitioned IndexesBitmap Join IndexesFast Index RebuildingTips ReviewReferences
Disk Arrays: Not a Choice AnymoreUse Disk Arrays to Improve Performance and AvailabilityHow Many Disks Do We Need?What Are Some of the RAID Levels Available?The Newer RAID 5Setup and Maintenance of the Traditional File SystemWhat Is the Cost?Distributing "Key" Data Files Across Hardware DisksStoring Data and Index Files in Separate LocationsAvoiding I/O Disk ContentionMoving Data Files 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 init.ora ParametersASM InstallationASM Parameters and SGA SizingASM and PrivilegesASM DisksASM and MultipathingASM DiskGroupsASM Diskgroups and DatabasesASM Redundancy and Failure GroupsNew Space-Related Columns in Oracle Database 10g Release 2Cluster Synchronization ServicesDatabase Instances and ASMDatabase Consolidation and Clustering with ASMDatabase Processes to Support ASMBigfile and ASMDatabase Init.ora 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 PartitionsOther Partitioning OptionsIndex PartitioningExporting PartitionsEliminating FragmentationUsing the Correct Extent SizeCreate a New Tablespace and Move the Segments to ItExporting and Then Re-Importing the TableTo Avoid Chaining, Set Percents 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 IntervalFlash RecoveryIncreasing Chances of Recovery: Committing after Each BatchUsing Rollback SegmentsAvoiding Contention among Rollback SegmentsMonitoring Rollback Segment Waits and ContentionIncreasing Rollback SegmentsIsolating Large Transactions to Their Own Rollback SegmentsThe Simpler Approach: UNDO TablespaceMonitoring Undo SpaceKilling Problem SessionsDon't Sort in the SYSTEM or SYSAUX TablespacesHave Multiple Control Files on Different Disks and ControllersUsing Raw Devices to Improve I/O for Write-Intensive DataReasons for Using Raw DevicesDrawbacksOther Disk I/O Precautions and TipsIssues to Consider in the Planning StagesTips ReviewReferences

Identifying Crucial Initialization ParametersChanging the Initialization Parameters Without a RestartViewing 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 ReadsTuning the SHARED_POOL_SIZE for Optimal PerformanceUsing Oracle Multiple Buffer PoolsTuning the PGA_AGGREGATE_TARGET for Optimal Use of MemoryModifying the Size of Your SGA to Avoid Paging and SwappingUnderstanding the Cost-Based OptimizerCreating Enough Dispatchers25 Important Initialization Parameters to ConsiderFinding Undocumented Initialization ParametersUnderstanding the Typical ServerModeling a Typical ServerSizing the Oracle Applications DatabaseTips ReviewReferences
The Enterprise Manager (EM) BasicsStarting with All Targets and Other GroupingsPolicies (Violations) TabMonitoring the DatabaseDatabase Administration TabDatabase Administration Tab: TablespacesDatabase Administration Tab: Instance LevelDatabase Administration Tab: All Initialization ParametersDatabase Administration Tab: Manage Optimizer StatisticsDatabase Administration Tab, Instance Level: Resource Manager (Consumer Groups)Database Maintenance TabDatabase Topology TabDatabase Performance TabMonitoring the HostsMonitoring the Application ServersMonitoring the Web ApplicationsDeployments Tab (Patching Options)Jobs TabReports TabAutomatic Storage Management PerformanceSummaryTips ReviewReferences
The Oracle SQL TRACE UtilitySimple Steps for SQL TRACE with a Simple QueryThe Sections of a TRACE OutputA More Complex TKPROF OutputDigging into the TKPROF OutputUsing DBMS_MONITOR (10g New Feature)TRCSESS Multiple Trace Files into One File (10g New Feature)Using EXPLAIN PLAN AloneEXPLAIN PLAN—Read It Top to Bottom or Bottom to Top?Reading the EXPLAIN PLANUsing DBMS_XPLANYet 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 TableHelpful Oracle-Supplied PackagesInitialization Parameters for Undocumented TRACEUsing Stored OutlinesUsing Plan Stability (Stored Outlines)Tips ReviewReferences
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 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 PUSH_SUBQ HintThe PARALLEL HintThe NO_PARALLEL HintThe APPEND HintThe NOAPPEND HintThe CACHE HintThe NOCACHE HintThe CLUSTER HintThe HASH HintThe CURSOR_SHARING_EXACT HintThe QB_NAME HintSome Miscellaneous Hints and NotesNotes on Hints and Stored OutlinesWhy Isn't My Hint Working?Hints at a GlanceTips ReviewReferences
What Queries Do I Tune? Querying V$SQLAREASelecting from the V$SQLAREA View to Find the Worst QueriesSelecting from the V$SQL View to Find the Worst QueriesNew 10g 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_sqlstat ViewSelecting Query Explain Plan from the DBA_HIST_SQL_PLAN ViewWhen Should an Index Be Used?What Happens When I Forget the Index?Creating an IndexCheck the Index on a TableWhat If I Create a Bad Index?Caution Should Be Exercised When Dropping IndexesIndexing the Columns Used in the SELECT and WHEREThe Fast Full ScanA "Magically" Faster QueryCaching a Table in MemoryUsing Multiple Indexes (Use the Most Selective)The Index MergeIndexes That Get SuppressedFunction-Based IndexesThe "Curious" ORThe EXISTS FunctionThat Table Is Actually a View!SQL and Grand Unified TheoryTuning Changes in Oracle Database 10g10g Automatic SQL TuningEnsure That the Tuning User Has Access to the APICreate the Tuning TaskThe Task Can Be Seen in the Advisor LogExecute the SQL Tuning TaskCheck Status of the Tuning TaskDisplaying the SQL Tuning Advisor ReportReviewing the Report OutputTips ReviewReferences
Join MethodsNESTED LOOPS JoinsSORT-MERGE JoinsCLUSTER JoinsHASH JoinsIndex 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 Fun (Cost-Based)Bitmap Join IndexesBitmap IndexesThird-Party Product TuningTuning 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 DumpTuning Using Simple Mathematical TechniquesTraditional Mathematical AnalysisSeven-Step MethodologyPattern InterpretationMathematical Techniques ConclusionsJoin Tuning: Relational vs. Object-Relational PerformanceModels UsedResultsConclusionTips ReviewReferences
Use DBMS_APPLICATION_INFO for Real-Time MonitoringUse a Custom Replacement of DBMS_ APPLICATION_INFO for Real-Time Monitoring in a RAC EnvironmentLog 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 FunctionShared Pool and Pinning PL/SQL ObjectsPinning (Caching) PL/SQL Object Statements into MemoryPinning All PackagesIdentifying PL/SQL Objects That Need to Be PinnedUsing and Modifying DBMS_SHARED_POOL.SIZESFinding Large ObjectsGet Detailed Object Information from DBA_OBJECT_SIZEGetting Contiguous Space Currently in the Shared PoolFinding Invalid ObjectsFinding Disabled TriggersUse PL/SQL Associative Arrays for Fast Reference Table LookupsFinding and Tuning the SQL When Objects Are UsedThe Time Component When Working with DATE Data TypesTuning and Testing PL/SQLPL/SQL Object Location ImplicationsUse Rollback Segments to Open Large CursorsUse Active Transaction Management to Process Large Quantities of DataUse Temporary Database Tables for Increased PerformanceIntegrate a User Tracking Mechanism to Pinpoint Execution LocationLimit 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 SQLA Package ExampleDatabase Trigger Example Using PL/SQLTips ReviewReferences
Real Application Clusters (RAC)Parallel DatabasesArchitecture of Oracle RACInternal Workings of the Oracle RAC SystemSCN ProcessingRAC Performance Tuning OverviewRAC 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 Operations Since Oracle 9iParallelism and PartitionsInter- and Intraoperation ParallelizationExamples of Using Inter- and Intraoperations (PARALLEL and NO_PARALLEL 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 Oracle Initialization ParametersParallel LoadingPerformance Comparisons and Monitoring Parallel OperationsOptimizing Parallel Operations in RACObjectives of Parallel OperationsRAC Parallel Usage ModelsInitialization 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
V$ View Creation and AccessObtaining a Count and Listing of All V$ ViewsFinding the X$ Tables Used to Create the V$ ViewsFinding the Underlying Objects That Make Up the DBA_ viewsUsing Helpful V$ ScriptsSummary of Memory Allocated (V$SGA)Detail of Memory Allocated (V$SGASTAT)Finding Initialization Settings in V$PARAMETERDetermining Hit Ratio for Data (V$SYSSTAT)Determining Hit Ratio for the Data Dictionary (V$ROWCACHE)Determining Hit Ratio for the Shared SQL and PL/SQL (V$LIBRARYCACHE)Identifying PL/SQL Objects That Need to Be Kept (Pinned)Finding Problem Queries by Querying V$SQLAREAFinding Out What Users Are Doing and Which Resources They Are UsingFinding Out Which Objects a User Is AccessingUsing IndexesIdentifying Locking IssuesKilling the Problem SessionFinding Users with Multiple SessionsFinding Disk I/O IssuesFinding Rollback Segment ContentionDetermining Whether Freelists Are SufficientChecking Privileges and RolesWait Events V$ ViewsSome of the Major V$ View CategoriesTips ReviewReferences
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 IndexesShared PoolQueries 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 ParsesLatch Waits and/or SleepsMiscellaneousRedoInitialization ParametersCase 1Case 2Case 3Case 4Case 5Buffer Cache/Data BlocksBuffer StatusesSegments Occupying Block BuffersHot Data Blocks/Latch Contention and Wait EventsInstance/DatabaseEffective X$ Table Use and StrategyRelated Oracle Internals TopicsTracesDBMS_TRACE PackageEventsDumpsORADEBUGtrcsess UtilityReading the Trace FileWait Information and Response TimeRecursive CallsModule InformationCommitUnmapBind VariablesErrorsSome Common X$ Table GroupsSome Common X$ Table and Non-V$ Fixed View AssociationsCommon X$ Table JoinsNew Oracle 10gR1 X$ TablesNew Oracle 10gR2 X$ TablesX$ Table Naming ConventionsFuture Version ImpactTips ReviewReferences
What's New in 10gR2 (10.2) STATSPACKNew Features in 10gR2 (10.2) STATSPACKInstalling 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 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 StatisticsTuning and Viewing at the Block Level (Advanced)Dictionary and Library Cache StatisticsSGA Memory StatisticsNon-Default Initialization ParametersTop 10 Things to Look for in AWR Report and STATSPACK OutputManaging the STATSPACK DataUpgrading STATSPACKDeinstalling STATSPACKQuick Notes on the New ADDM ReportScripts 10gR2Tips ReviewReferences
Total Performance Index (TPI)Education Performance Index (EPI)System Performance Index (SPI)Memory Performance Index (MPI)Buffer Hit RatioDictionary Cache Hit RatioLibrary Cache Hit RatioPGA Memory Sort RatioPercent of Data Buffers Still FreeTop 10 "Memory Abusers" as a Percent of All StatementsTop 25 "Memory Abusers" Statements TunedPinning/Caching ObjectsDisk Performance Index (DPI)Top 25 "Disk-Read Abuser" Statements TunedTop 10 Disk-Read Abusers as Percent 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 InformationDBA Knowledge RatingOther Items to Consider in Your TPI and System ReviewTips ReviewReferences
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 the–p Switch of sar to Report Paging ActivitiesUsing the–w Switch of sar to Report Swapping and Switching ActivitiesUsing the–r Switch of sar to Report Free Memory and Free SwapUsing the–g Switch of sar to Report Paging ActivitiesUsing the–wpgr Switch of sarFinding 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 the–d Switch of iostat for Disk Drives sd15, sd16, sd17, and sd18Using the–D Switch of iostatUsing the–x Switch of iostatCombining–x Switch of iostat 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 PerformanceTips ReviewReferences
Desupported Initialization ParametersDeprecated Initialization ParametersTop 25 Initialization ParametersTop 10 Initialization Parameters Not to ForgetTop 13 Undocumented Initialization Parameters (As I See It)Listing of Documented Initialization Parameters (V$PARAMETER)Listing of Undocumented Initialization Parameters (x$ksppi/x$ksppcv)Oracle Applications 11i Recommendations (Note: 216205.1)Top 10 Reasons Not to Write a BookTips ReviewReferences
Creation of V$ and GV$ Views and X$ TablesA List of Oracle 10g (10.2.0.1) GV$ and V$ ViewsThe Oracle 10g (10.2.0.1) V$ ViewsOracle 10g Scripts of the x$ Tables Used to Create the V$ Views
Oracle 10g X$ Tables Ordered by NameOracle 10g X$ IndexesOracle 10g V$ Views Cross-Referenced to the X$ TablesOracle 10g X$ Tables Not Referenced by a GV$ View

Content preview from Oracle Database 10g Performance Tuning Tips & Techniques

CHAPTER 3Disk Implementation Methodology and ASM (DBA)

Oracle has changed the landscape of disk access in Oracle 10g with the release of Automatic Storage Management (ASM). There will be a heavy focus in this chapter on ASM in addition to the non-ASM-specific disk implementation methodology in Oracle.

In the last several years, it has seemed that disk configuration technique was reaching the point where there wasn't much more you could do to improve the performance of your system without greatly complicating your life as a DBA. If your system operated with some unique qualities or you chose to review the I/O activity on your tablespaces on a frequent basis, you might be able to achieve slightly better performance than simply mashing all your ...