Lao Li share: Oracle performance Tuning Overview translation

Poptest is the only training institute for developing Test and development engineers in China, aiming at the ability of the trainees to be competent in automated testing, performance testing and testing tools development. If you are interested in the course, please consult qq:908821478, call 010-84505200. 1 Overview of Performance optimizations

This chapter provides an introduction Toperformance tuning and contains the following sections:

This section provides an introduction to performance optimization, which contains a few parts.

• Introduction to Performance Tuning
• Introduction to Performance optimization
• Introduction to Performance Tuning Features and Tools
• Introduction to performance optimization tools and features

1.1 Introduction to Performance optimization

This guide provides information the tuning Anoracle Database system for performance. Topics discussed in the This guide include:

This section provides knowledge of performance optimizations for an Oracle database system. Include a few topics

• Performance planning
• Performance planning
• Instance Tuning
• Instance optimization
• SQL Tuning
• SQL optimization

1.1.1 Performance Planning

Before starting on the instance or SQL tuningsections of this guide, make sure you have read part II, "Performance plannin G ".

Before you learn the examples and SQL Optimizations section of this section, read Part 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 system of performance-related methodologies. This section describes a clear and simple scenario that can significantly improve system performance. This scenario involves a topic:

• Understanding Investment Options
• Understand all the factors that can play a role in tuning
• Understanding Scalability
• Understanding Extensibility
• System Architecture
• System architecture
• Application Design Principles
• Application Design Guidelines
• Workload testing, Modeling, and implementation
• Load test builds a model to achieve
• Deploying NEW Applications
• Deploy a new app

1.1.2 Instance Optimization

Part III, "Optimizing Instance Performance" Ofthis Guide discusses the factors involved in the tuning and optimizing of an Oracle database instance.

Part III of this section, "Optimizing Instance Performance" discusses various factors that relate to tuning and optimization of Oracle DB instances.

When considering instance tuning, care mustbe taken on the initial design of the database system to avoid bottlenecks that Could leads to performance problems. In addition, you need to consider:

When considering instance optimization, the database system initialization design must be careful to prevent some bottlenecks, which may lead to performance problems. Also you need to consider the points:

• allocating memory to database structures
• allocating memory to a database
• Determining I/O requirements of different parts of the database
• Determine the I/O requirements for different parts of the database
• Tuning the operating system for optimal performance of the database
• Tuning the operating system enables the database system to achieve the best

After the database instance have beeninstalled and configured, you need to monitor the database as it is running Tocheck fo R performance-related problems.

After installing and configuring the DB instance, you need to monitor the database because it is running at all times to check for performance-related issues.

1.1.2.1 Performance Guidelines

Performance tuning requires a Different,although related, method to the initial configuration of a system. Configuringa system involves allocating resources in a ordered manner so then the Initialsystem configuration is function Al.

Performance tuning requires a different, albeit related, approach to the initialization configuration of the system. To allocate resources in an orderly manner to configure a system so that the system's initialization configuration will work.

Tuning is driven by identifying the mostsignificant bottleneck and making the appropriate changes to reduce oreliminate th E effect of that bottleneck. Usually, tuning was performedreactively, either while the system was preproduction or after it was live.

Drive tuning by finding the most important bottlenecks and making appropriate changes to reduce or eliminate the impact of bottlenecks. In general, tuning needs to be validated by a response, which is either in a trial run or is already running in production.

1.1.2.2 Baseline

The most effective-tune is to has anestablished performance baseline that can being used for comparison if Aperforman Ce issue arises. Most database administrators (DBAs) know their Systemwell and can easily identify peak usage periods. For example, the peak periodscould is between 10.00am and 12.00pm and also between 1.30pm and 3.00pm. Thiscould include a batch window of 12.00am midnight to 6am.

The most effective way to tune is to create a baseline of performance that can be used to compare performance problems when they occur. Most of the database administrators are familiar with their systems and can easily determine the peak hours of system use. For example, the peak period may be 10:00 to 12:00, or it may be

1.30 to 3.00 in the afternoon. This may include the batch processing window from 12:00 to 6:00.

It is important to identify these peakperiods at the site and install a monitoring tool that gathers performance datafor t Hose high-load times. Optimally, data gathering should be configured Fromwhen the application are in its initial trial phase during the QA cycle. Otherwise, this should was configured when the system was first in production.

It is important to find these peak times and to install a monitoring tool that collects performance data for high-load periods. Ideally, data acquisition should be configured from the initial pilot phase of the application's quality assurance life cycle. Otherwise, it should be configured when the system is first applied to production.

Ideally, baseline data gathered shouldinclude the following:

Ideally, the baseline data should include the following:

• Application statistics (transaction volumes, response time)
• Application statistics (volume, Response time)
• Database Statistics
• Database statistics Information
• Operating System Statistics
• Operating system statistical information
• Disk I/O statistics
• Disk input and output statistics
• Network Statistics
• Network statistics information

In the Automatic Workload Repository,baselines is identified by a range of snapshots that is preserved for Futurecompari Sons. See "Overview of the Automatic Workload Repository".

In the automated workload knowledge Base, baselines are formed by a series of snapshots that are saved for later comparison. See "Automated Workload Library Overview".

1.1.2.3 Symptoms and problems

A common pitfall in performance tuning are tomistake the symptoms of a problem for the actual problem itself. It Isimportant to recognize this many performance statistics indicate the symptoms,and that identifying the symptom are not Sufficient data to implement a remedy. For example:

A common pitfall of performance tuning is to mistake the symptom (performance) of the problem as the substance of the problem. It is important to recognize that a large number of performance statistics indicate the symptoms of the problem. Of course, it is also important to recognize that symptoms do not provide sufficient data to remedy the problem.

• Slow Physical I/O
• Slow physical input and output

Generally, this is caused bypoorly-configured disks. However, it could also is caused by a significantamount of unnecessary physical I/O on those disks issued by poorly-tuned Sql.

Typically, this is caused by a disk that is configured more than once. However, he may also be caused by a large amount of unnecessary disk physical input and output, which is caused by garbage SQL.

• Latch contention
• Latch contention

Rarely is latch contention tunable byreconfiguring the instance. Rather, latch contention usually is resolvedthrough application changes.

The issue of latch contention is limited by reconfiguring instances, whereas latch contention is usually resolved by altering the application.

• Excessive CPU usage
• High CPU Usage

Excessive CPU usage usually means that thereis little idle CPU on the system. This could is caused by a inadequately-sizedsystem, by untuned SQL statements, or by inefficient application programs.

High CPU usage usually means that the system will have very little idle CPU. This can be caused by an inappropriate or poorly-tuned SQL or inefficient application of the system.

1.1.2.4 when tuning

There is distinct types of tuning:

Tuning in two different ways

• Proactive Monitoring
• Active monitoring
• Bottleneck elimination
• Eliminate bottlenecks

1.1.2.4.1 Active Monitoring

Proactive monitoring usually occurs 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 regular schedule interval where a certain number of performance statistics are detected to determine whether the system is running and the resource utilization has been changed. Proactive monitoring can also be seen as active tuning.

Usually, monitoring does not result inconfiguration changes to the system, unless the monitoring exposes a Seriousproblem That's developing. In some situations, experienced performanceengineers can identify potential problems through statistics alone, ALTHOUGHACC Ompanying performance degradation is usual.

Experimenting with or tweaking a system whenthere are no apparent performance degradation as a proactive action can be Adan Gerous activity, resulting in unnecessary performance drops. Tweaking Asystem should is considered reactive tuning, and the steps for reactive tuningshould is followed.

Monitoring is usually part of a largercapacity planning exercise, where resource consumption are examined to seechanges in The application is being used, and the the Applicationis using the database and host resources.

Typically, monitoring does not change the configuration of the system unless he exposes some serious problems that are occurring. In some cases, experienced performance engineers are able to solve potential problems only through statistical information, albeit accompanied by a decrease in performance. When there is no significant performance degradation, the active adjustment and testing system is dangerous, resulting in unnecessary performance degradation of the system. The adjustment system needs to consider the reaction tuning, and the reaction tuning steps to be tracked.

Monitoring is usually part of a larger planning effort, in which the resource consumption is detected to observe how the application is being used and how the database and host resources are changed.

1.1.2.4.2 Bottleneck Elimination

Tuning usually implies fixing a performanceproblem. However, tuning should is part of the that life cycle of a application-through the analysis, design, coding,production, and MA Intenance stages. Oftentimes, the tuning phase is a left untilthe system was in production. At this time, tuning becomes a reactivefire-fighting exercise, where the most important bottleneck is identified andfixed.

Usually, the purpose for tuning are to reduceresource consumption or to reduce the elapsed time for a operation Tocomplete . Either, the goal is to improve the effective use of a particularresource. In general, performance problems is caused by the over-use of Aparticular resource. That resource was the bottleneck in the system. There is anumber of distinct phases in identifying the bottleneck and the potentialfixes. These is discussed in the sections that follow.

Tuning often means fixing performance issues. However, tuning should be part of the entire lifecycle of an application, including analysis, design, coding, production, and maintenance phases. It is often the case that the tuning phase is retained until the system production runs before leaving. At that time, tuning has become a responsive work, and most important bottlenecks have been identified and repaired.

Typically, tuning is designed to reduce resource consumption or to reduce the time-to-performance of an operation. Either way, the goal is to improve the effective use of a particular resource. In general, performance problems are caused by excessive use of a particular resource, which is the bottleneck of the system. There are many different stages in identifying bottlenecks and potential problems. These things will be discussed in the following sections:

Remember that the different forms ofcontention is symptoms that can is fixed by making changes in the followingplaces:

Keep in mind that the different forms of competition are symptoms that can be altered to repair,

• Changes in the application, or the the-the-application is used
• Changes to the application, or changes in how the application is used
• Changes in Oracle
• Changes to Oracle
• Changes in the host hardware configuration
• Changes to the host hardware configuration.

Often, the most effective-on-the-resolving abottleneck is-to-change the application.

Often, the most effective way to solve bottlenecks is to change an application.

1.1.3 SQL Optimization

Part IV, "Optimizing SQL Statements" Ofthis Guide discusses the process of tuning and optimizing SQL statements.

The IV section of this section discusses tuning processes and SQL optimizations.

Many client/server application Programmersconsider SQL a messaging language, because queries is issued and data Isreturne D. 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 to high transactionprocessing systems.

Many client and server application developers treat SQL as a message language because the query is initiated and the result data is returned. However, client tools often produce some inefficient SQL. Therefore, a good understanding of the database SQL processing mechanism is necessary to write the optimal SQL. This is especially useful for high-quality 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 is required, then Oracle can construct an accurate plan toaccess those rows E Fficiently through the shortest possible path. In Decisionsupport system (DSS) environments, selectivity are less important, because Theyoften access most of a table ' s RO Ws. In such situations, full table scans Arecommon, and indexes is not even used. This book is primarily focussed Onoltp-type applications. For detailed information in DSS and mixed Environments,see the Oracle Database Data warehousing Guide.

Typically, SQL statements initiated by an online transaction processing system operate with a relatively small number of rows at the same time. If an index can locate the exact rows it needs, Oracle can create an accurate plan to fetch those rows through the shortest possible path. In decision support system environments, queries are secondary because they are always getting most of the rows in the table. In this case full-table scanning is common, and the index may not even be used. The main focus here is on the online transaction processing system. For more detailed information on decision support systems and hybrid environments, see the Oracle Database Data Warehouse Guide.

1.1.3.1 query optimizer and execution plan

When a SQL statement was executed on a oracledatabase, the Oracle query optimizer determines the most efficient EXECUTIONP LAN after considering many factors related to the objects referenced and theconditions specified in the query. This determination is a 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 decides which is the most efficient execution plan after considering many of the related factors of several objects involved and the specific circumstances of the query. This decision is an important step in the execution of any SQL statement and has a significant impact on execution time.

During The evaluation process, the Queryoptimizer reviews statistics gathered on the system to determine the best dataacce SS 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 examines the statistics collected by the system to determine the best way to take the data and other considerations. You can override the query optimizer's execution plan by inserting a hint hint in the SQL statement.

Lao Li share: Oracle performance Tuning Overview translation