| Oracle? Database Performance Tuning Guide 10GRelease 2 (10.2) B14211-03 |
|
|
PDF · Mobi · ePub |
This guide provides information on tuning anOracle Database system for performance. Topics discussed in this guide include:
This section provides the knowledge of optimizing the performance of an oracle database system. Including several topics
Performance PlanningBefore starting on the instance or SQL tuningsections of this guide, make sure you have read Part II, "Performance Planning ".
Before learning the instance and SQL optimization sections in this section, read section II: performance planning.
Based on years of designing and performanceexperience, Oracle has designed a performance methodology. This brief sectionexplains clear and simple activities that can dramatically improve systemperformance. It discusses the following topics:
Based on years of experience in design and performance, oracle has designed a set of performance-related methods. This section describes a clear and simple solution that significantly improves system performance. This solution involves the following topics:
Understanding investors OptionsPart III, "Optimizing Instance Performance" ofthis guide discusses the factors involved in the tuning and optimizing of anOracle database instance.
In part III of this section, "optimizing instance performance" discusses various factors related to oracle database instance tuning and optimization.
When considering instance tuning, care mustbe taken in the initial design of the database system to avoid bottlenecks thatcocould lead to performance problems. In addition, you need to consider:
When you consider instance optimization, you must pay attention to some bottlenecks in the database system initialization design. These bottlenecks may cause performance problems. In addition, you need to consider the following points:
Allocating memory to database structuresAfter the database instance has beeninstalled and configured, you need to monitor the database as it is running tocheck for performance-related problems.
After installing and configuring a database instance, You need to monitor the database because it is always running to check performance-related issues.
1.1.2.1 performance criteriaPerformance tuning requires a different, although related, method to the initial configuration of a system. Using Inga system involves allocating resources in an ordered manner so that the initialsystem configuration is functional.
For system initialization configuration, performance tuning requires a different method, though relevant. You must allocate resources in an orderly manner to configure a system so that the initial configuration of the system can take effect.
Tuning is driven by identifying the specified bottleneck and making the appropriate changes to reduce oreliminate the effect of that bottleneck. Usually, tuning is disabled, either while the system is preproduction or after it is live.
Find the most important bottleneck and make some appropriate changes to reduce or eliminate the impact of the bottleneck to drive optimization. Generally, tuning requires a response to verify that the system is either in trial run or already in production.
1.1.2.2 baselineThe most valid tive way to tune is to have anestablished performance baseline that can be used for comparison if aperformance issue arises. most database administrators (DBAs) know their systemwell and can easily identify peak usage periods. for example, the peak periodscocould be between 10.00am and 12.00pm and also between 1.30pm and 3.00pm. thiscocould include a batch window of 12.00am midnight to 6 am.
The most effective tuning method is to establish a performance baseline that can be used to compare performance problems. Most database administrators are familiar with their systems and can easily determine the peak periods used by the system. For example, the peak hours may be from AM to PM, or
PM to PM. This may include the batch processing window from AM to AM.
It is important to identify these peakperiods at the site and install a monitoring tool that gathers performance datafor those high-load times. optimally, data gathering shoshould be configured fromwhen the application is in its initial trial phase during the QA cycle. otherwise, this shoshould be configured when the system is first in production.
Finding these peak periods and installing a monitoring tool that can collect performance data during high load periods is very important. Ideally, data collection should start from the initial test phase in the application's quality assurance life cycle. Otherwise, it should be configured when the system is applied to production for the first time.
Ideally, baseline data gathered shouldinclude the following:
Ideally, the baseline data should include the following:
Application statistics (transaction volumes, response time)In the Automatic Workload Repository, baselines are identified by a range of snapshots that are preserved for futurecomparisons. See "Overview of the Automatic Workload Repository ".
In the automatic workload knowledge base, baselines are formed by a series of snapshots, which are saved for future comparison. For more information, see "Overview of automatic workload libraries ".
1.1.2.3 symptoms and ProblemsA common pitfall in performance tuning is tomistake the symptoms of a problem for the actual problem itself. it isimportant to recognize that implements performance statistics indicate the symptoms, and that identifying the symptom is not sufficient data to implement a remedy. for example:
A common trap of performance tuning is to treat the symptoms (performance) of the problem as the essence of the problem. It is important to recognize that a large amount of Performance Statistics indicate symptoms of problems. Of course, it is also important to realize that the symptoms do not provide sufficient data to remedy the problem.
Slow physical I/OGenerally, this is caused bypoorly-configured disks. However, it cocould also be caused by a significantamount of unnecessary physical I/O on those disks issued by poorly-tuned SQL.
Generally, this is caused by a disk with a higher configuration. However, it may also be caused by a large number of unnecessary physical input and output from the disk, which is caused by junk SQL.
Latch contentionRarely is latch contention tunable byrecycling the instance. Rather, latch contention usually is resolvedthrough application changes.
It is rare to adjust the use of latches by reconfiguration instances. On the contrary, the use of latches is usually solved by changing applications.
Excessive CPU usageExcessive CPU usage usually means that thereis little idle CPU on the system. This cocould be caused by an inadequately-sizedsystem, by untuned SQL statements, or by inefficient application programs.
High cpu usage usually means that the system has very few idle CPUs. This may be caused by an inappropriate system size or unoptimized SQL or inefficient application.
1.1.2.4 when to tuneThere are two distinct types of tuning:
Optimization in two different ways
Proactive MonitoringProactive monitoring usually failed on aregularly scheduled interval, where a number of performance statistics areexamined to identify whether the system behavior and resource usage haschanged. Proactive monitoring can also be considered as proactive tuning.
Active monitoring usually occurs at a scheduled interval, where a certain amount of Performance Statistics are detected to determine whether the system operation and resource utilization have been changed. Active monitoring can also be considered as active optimization.
Usually, monitoring does not result inconfiguration changes to the system, unless the monitoring exposes a seriousproblem that is developing. in some situations, experienced performanceengineers can identify potential problems through statistics alone, althoughaccompanying performance degradation is usual.
Experimenting with or tweaking a system whenthere is no apparent performance degradation as a proactive action can be adangerous activity, resulting in unnecessary performance drops. tweaking asystem shocould be considered reactive tuning, and the steps for reactive tuningshocould be followed.
Monitoring is usually part of a largercapacity planning exercise, where resource consumption is examined to seechanges in the way the application is being used, and the way the applicationis using the database and host resources.
In general, monitoring does not change the system configuration unless it exposes some serious problems that are occurring. In some cases, experienced performance engineers can solve potential problems only through statistical information, despite the performance degradation. When there is no significant performance reduction, it is dangerous to actively adjust and test the system, which may cause unnecessary system performance degradation. To adjust the system, you need to consider the reaction tuning, and the reaction tuning steps should be tracked.
Monitoring is usually part of a larger planning process, where resource consumption is detected to observe how applications are used and changes in database and host resources.
1.1.2.4.2 eliminate bottlenecksTuning usually implies fixing a performanceproblem. however, tuning shocould be part of the life cycle of an application-through the analysis, design, coding, production, and maintenance stages. oftentimes, the tuning phase is left untilthe system is in production. at this time, tuning becomes a reactivefire-fighting exercise, where the most important bottleneck is identified andfixed.
Usually, the purpose for tuning is to export ceresource consumption or to reduce the elapsed time for an operation tocomplete. either way, the goal is to improve the valid tive use of a particle resource. in general, performance problems are caused by the over-use of aparticular resource. that resource is the bottleneck in the system. there are anumber of distinct phases in identifying the bottleneck and the potentialfixes. these are discussed in the sections that follow.
Tuning usually means fixing performance problems. However, optimization should be part of the entire application lifecycle, including the analysis, design, coding, production, and maintenance phases. It is often said that the optimization phase will be retained until production and running of the system. At that time, tuning has become a response, and most important bottlenecks have been identified and fixed.
In general, the purpose of optimization is to reduce resource consumption or reduce the time consumed by an operation. Either way, the objective is to improve the effective use of a special resource. In general, performance problems are caused by the overuse of a special resource, which is exactly the bottleneck of the system. Identifying bottlenecks and potential problems have many different stages. These things will be discussed in the following sections:
Remember that the different forms ofcontention are symptoms that can be fixed by making changes in the followingplaces:
Remember that different forms of competition are some symptoms that can be changed in the following ways to fix,
Changes in the application, or the way the application is usedOften, the most alternative tive way of resolving abottleneck is to change the application.
Often, the most effective way to solve the bottleneck is to change an application.
1.1.3 SQL OptimizationPart IV, "Optimizing SQL Statements" ofthis guide discusses the process of tuning and optimizing SQL statements.
In part IV of this section, the optimization process and SQL optimization are discussed.
Expose client/server application programmersconsider SQL a messaging language, because queries are issued and data isreturned. however, client tools often generate inefficient SQL statements. therefore, a good understanding of the database SQL processing engine isnecessary for writing optimal SQL. this is especially true for high transactionprocessing systems.
Many client and server application developers regard SQL as a messaging language, because the query will return results after it is initiated. However, client tools often produce inefficient SQL statements. Therefore, it is necessary to have a good understanding of the Database SQL processing mechanism and write the optimal SQL statements. This is especially useful for high transaction processing systems.
Typically, SQL statements issued by OLTPapplications operate on relatively few rows at a time. if an index can point tothe exact rows that are required, then Oracle can construct an accurate plan toaccess those rows efficiently through the shortest possible path. in decisionsupport system (DSS) environments, selectivity is less important, because theyoften access most of a table's rows. in such situations, full table scans arecommon, and indexes are not even used. this book is primarily focussed onOLTP-type applications. for detailed information on DSS and mixed environments, see the Oracle Database Data Warehousing Guide.
Generally, SQL statements initiated by the online transaction processing system operate on fewer rows at the same time. If an index can locate the exact rows it requires, oracle can establish an accurate plan through the possible shortest path to obtain those rows. In the decision support system environment, queries are secondary because they always need to obtain most rows in the table .. In this case, full table scan is common, and indexes may not even be used. The main focus here is on the online transaction processing system. For more details about the decision support system and hybrid environment, see the oracle database data warehouse guide.
1.1.3.1 query optimizer and execution planWhen a SQL statement is executed on an Oracledatabase, the Oracle query optimizer determines the most efficient executionplan after considering into factors related to the objects referenced and thecondispecified in the query. this determination is an important step inthe processing of any SQL statement and can greatly affect execution time.
When an SQL statement is executed on an oracle database, the oracle query optimizer determines which execution plan is the most efficient after considering many related factors of several objects involved and some specific situations during the query. This decision is an important step in the execution of any SQL statement, and has a great impact on the execution time.
During the evaluation process, the queryoptimizer reviews statistics gathered on the system to determine the best dataaccess path and other considerations. You can override the execution plan ofthe query optimizer with hints inserted in SQL statement.
During the evaluation, the query optimizer checks the statistical information collected by the system to determine the best way to fetch data and other considerations. You can insert a hint in an SQL statement to overwrite the execution plan of the query optimizer.
1.2 Performance Tuning features and toolsValid data collection and analysis isessential for identifying and correcting performance problems. oracle providesa number of tools that allow a performance engineer to gather informationregarding database performance. in addition to gathering data, Oracle providestools to monitor performance, diagnose problems, and tune applications.
Effective data collection and analysis is necessary to identify and correct performance problems. Oracle provides a large number of tools for database performance tuning engineers to collect information. in addition to data collection, oracle provides tools to monitor performance, diagnose problems, and optimize applications.
The Oracle gathering and monitoring featuresare mainly automatic, managed by an Oracle background processes. to enableautomatic statistics collection and automatic performance features, the STATISTICS_LEVEL initialization parameter must be setto TYPICAL or ALL. you can administer and display the output ofthe gathering and tuning tools with Oracle Enterprise Manager, or with APIs andviews. for details of use and to take advantage of its numerous automatic monitoringand diagnostic tools, Oracle Enterprise Manager Database Control isrecommended.
Oracle's collection and monitoring features are automated and managed by oracle background processes. To automate statistical collection and performance functions, the initialization parameter of the statistical level must be set to either Typical or complete. You can use the oracle Enterprise Manager or API or view to display and manage the output results of the statistical information collection and optimization tool. To make it easier to use and use a large number of automatic monitoring and diagnosis tools, oracle Enterprise Manager Database Control is recommended.
See Also:
Additional reading:
Oracle Database 2 Day DBA for information about using Oracle Enterprise Manager to manage Oracle DatabaseThe Oracle automatic performance tuningfeatures include:
Oracle automatic performance tuning features include:
Automatic Workload Repository (AWR) collects, processes, and maintains performance statistics for problem detection and self-tuning purposes. See "Overview of the Automatic Workload Repository ".This section describes additional Oracletools that can be used for determining performance problems.
This section describes additional tools that can be used to determine oracle performance issues.
Performance View starting with 1.2.2.1 V $The V $ views are the performanceinformation sources used by all Oracle performance tuning tools. the V $ views are based on memory structures initialized at instancestartup. the memory structures, and the views that represent them, areautomatically maintained by Oracle throughout the life of the instance. see Chapter 10, "Instance Tuning Using PerformanceViews" for information diagnosing tuning problems usingthe V $ performance views.
The V $ view is the source of the performance information used by all oracle performance tuning tools. The V $ view is based on the memory structure initialized when the instance is started. The memory structure and view representing them will be automatically maintained by oracle throughout the lifecycle of the instance. Read section 10 "instance optimization with performance View" to learn more about how to use V $ performance view to diagnose optimization problems.
See Also:
Oracle Database Reference for informationabout dynamic performance views
See the following for details:
For more information about the dynamic performance view, see the oracle database guide.
Note:
Note:
Oracle recommends using the AutomaticWorkload Repository to gather performance data. These tools have been designedto capture all of the data needed for performance analysis.
Oracle recommends that you use an automatic work information library to collect performance data. These tools have been designed to capture all the data required for performance analysis.
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
1 on 1 presale consultation
24/7 Technical Support 6 Free Tickets per Quarter Faster Response
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.
A comprehensive suite of global cloud computing services to power your business
Payment Methods We Support
