Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "how to tune Oracle SQL". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

1 introduction to SQL tuning

SQL tuning is an attempt to diagnose and fix SQL statements that do not meet performance criteria.

1.1 about SQL tuning

SQL tuning is an iterative process that improves the performance of SQL statements to meet specific, measurable, and achievable goals.

SQL tuning means fixing problems in deployed and running applications.

1.2 purpose of SQL tuning

SQL statements become a problem when they cannot be executed according to predetermined and measurable standards.

After the problem has been identified, a typical tuning session has one of the following goals:

Reduce the user response time, reduce the time between the user issuing the statement and receiving the response

Improve throughput and process statements to access the minimum amount of resources required for all rows

1.3 prerequisites for SQL tuning

SQL performance tuning requires a foundation of database knowledge.

It is assumed that you have the knowledge and skills shown in the following table.

Table 1-1 required knowledge

The required knowledge shows that the database architecture is not just what the administrator needs to know. As a developer, you want to develop applications for Oracle databases in a minimum of time, which requires the use of database architecture and features. For example, a lack of understanding of Oracle database concurrency control and multi-version read consistency can cause applications to compromise data integrity, run slowly, and reduce scalability. SQL and PL/SQL because of the existence of GUI-based tools, you can create applications and manage databases without knowing SQL. However, if you don't know SQL, you can't adjust the application or database. The SQL tuning tool database generates performance statistics and provides SQL tuning tools that interpret these statistics. 1.4 tasks and tools for SQL tuning

After determining the goal of tuning the session, for example, reducing the user response time from three minutes to less than a second, the question becomes how to achieve this goal.

1.4.1 SQL tuning task

The details of tuning the session depend on many factors, including whether you tune actively or passively.

In active SQL tuning, you often use SQL Tuning Advisor to determine whether SQL statements can be executed better. In passive SQL tuning, you can correct SQL-related problems encountered by users.

Whether you are tuning actively or passively, a typical SQL tuning session involves all or most of the following tasks:

1. Identify high-load SQL statements

Look at the past execution history to find statements responsible for a large number of application workloads and system resources.

two。 Collect performance-related data

Optimizer statistics are critical to SQL tuning. If these statistics do not exist or are no longer accurate, the optimizer cannot generate the best execution plan. Other data related to SQL performance include the structure of the tables and views accessed by the statement, and the definition of the indexes available to the statement.

3. Determine the cause of the problem

In general, the causes of SQL performance problems include:

Design inefficient SQL statements

If you write SQL statements to perform unnecessary work, the optimizer cannot improve its performance. Examples of inefficient designs include:

Ignore adding Join conditions, which results in Cartesian connections

Use hint to specify a large table as the driver table in the connection

Specify UNION instead of UNION ALL

Execute a subquery for each row in an external query

Second-best execution plan

The query optimizer (also known as the optimizer) is internal software that determines which execution plan is the most effective. Sometimes the optimizer chooses a plan with a suboptimal access path, which is how the database retrieves data from the database. For example, plans for query predicates with low selectivity can use full table scans on large tables instead of indexes.

You can compare the execution plan that executes the best SQL statement with the second-best plan. This comparison, as well as information such as changes in the amount of data, can help determine the cause of performance degradation.

Missing SQL access structure

The lack of SQL access structures (for example, indexes and materialized views) is a typical reason for SQL's poor performance. The best access structure set can improve SQL performance by several orders of magnitude.

Obsolete optimizer statistics

When statistical maintenance operations (automatic or manual) fail to keep up with changes to table data caused by DML, the statistics collected by DBMS_STATS may become stale. Because the stale statistics on the table do not accurately reflect the table data, the optimizer may make decisions based on the error information and generate a suboptimal execution plan.

Hardware issu

Suboptimal performance may be related to memory, Iamp O, and CPU problems.

4. Define the scope of the problem

The scope of the solution must match the scope of the problem. Database-level issues and statement-level issues need to be considered. For example, the shared pool is too small, which can cause cursors to age quickly, resulting in a lot of hard parsing. Using initialization parameters to increase the shared pool size can fix database-level problems and improve the performance of all sessions. However, if a single SQL statement does not use a useful index, changing the optimizer initialization parameters for the entire database may damage overall performance. If there is a problem with a single SQL statement, then the appropriate range of solutions can only solve the problem through this statement.

5. Perform corrective actions for sub-optimal execution of SQL statements

These behaviors vary from environment to environment. For example, you can rewrite SQL statements to improve efficiency, and avoid unnecessary hard parsing by rewriting statements to use bound variables. You can also use equijoins to remove functions from the WHERE clause and decompose complex SQL statements into simple statements.

In some cases, you improve SQL performance not by rewriting statements but by refactoring schema objects. For example, you can partition a table, introduce derived values, or even change the database design.

6. Prevent SQL performance regression

To ensure the best SQL performance, verify that the execution plan continues to provide the best performance, and select a better plan, if available. You can use optimizer statistics, SQL configuration files, and SQL plan benchmarks to achieve these goals.

1.4.2 SQL tuning tool

The SQL tuning tool is automatic or manual.

In some cases, the tool is automated if the database itself can provide diagnoses, recommendations, or corrective actions. Manual tools require you to perform all of these actions.

All tuning tools rely on basic tools for dynamic performance views, statistics, and metrics collected by database instances. The database itself contains the data and metadata needed to adjust the SQL statement.

1.4.2.1 automatic SQL tuning tool

The Oracle database provides several advisors related to SQL tuning.

In addition, SQL plan management is a mechanism that prevents performance regression and can help you improve SQL performance.

All automatic SQL tuning tools can use the SQL tuning set as input. The SQL tuning set (STS) is a database object that includes one or more SQL statements and their execution statistics and execution context.

1.4.2.1.1 automatic Database Diagnostic Monitor (ADDM)

ADDM is a self-diagnostic software built into Oracle database.

ADDM can automatically locate the root cause of performance problems, provide corrective recommendations, and quantify expected benefits. ADDM can also identify areas where no action is required.

ADDM and other consultants use the Automated workload Repository (AWR), an infrastructure that provides services to database components to collect, maintain, and use statistics. ADDM examines and analyzes statistics in AWR to determine possible performance problems, including high-load SQL.

For example, you can configure ADDM to run nightly. In the morning, you can check the latest ADDM report to see the possible cause of the problem and whether there are recommended fixes. The report may show that specific SELECT statements take up a large amount of CPU and recommend that you run SQL tuning Advisor.

1.4.2.1.2 SQL tuning Advisor

SQL tuning Advisor is internal diagnostic software that identifies problematic SQL statements and recommends how to improve statement performance.

SQL tuning Advisor is called automatic SQL tuning Advisor when running as an automatic maintenance task during the database maintenance window.

SQL tuning Advisor takes one or more SQL statements as input and invokes the auto-tuning optimizer to perform SQL tuning on the statements. The consultant performs the following types of analysis:

Check for invalid or outdated statistics

Build SQL profile

SQL profile is a set of auxiliary information specific to SQL statements. SQL profile contains corrections to suboptimal optimizer estimates found during automatic SQL adjustment. This information improves the optimizer estimate of the cardinality, which is the estimated or actual number of rows returned by the operation in the execution plan, as well as selectivity. These improved estimates lead the optimizer to choose a better plan.

Explore whether different access paths can significantly improve performance

Identify the SQL statement that is suitable for the suboptimal plan

The output is in the form of reports or recommendations, as well as the reasons for each recommendation and its expected benefits. This recommendation involves the collection of object statistics, the creation of new indexes, the refactoring of SQL statements, or the creation of SQL profile. You can choose to accept the suggestion to complete the adjustment of the SQL statement.

1.4.2.1.3 SQL visiting consultant

SQL access consultant is an internal diagnostic software that recommends which materialized views, indexes, and materialized view logs are created, deleted, or retained.

The SQL access advisor takes the actual workload as input, or the consultant can get the hypothetical workload from the pattern. The SQL access consultant considers the tradeoff between space usage and query performance and recommends the most cost-effective configuration of new and existing materialized views and indexes. The consultant also made recommendations on zoning.

1.4.2.1.4 automatic indexing

The Oracle database can continuously monitor application workloads and automatically create and manage indexes.

Manually creating an index requires an in-depth understanding of the data model, application, and data distribution. DBA usually chooses which indexes to create and never modifies their choices. As a result, opportunities for improvement are lost, and unnecessary indexes may become performance bottlenecks. Automatic index management solves this problem by performing the following tasks:

Continuous monitoring of workload

Create a new index

Rebuild the index and mark it as unavailable or invisible

Delete index

Check the impact of automatic index management on performance

The index function is implemented as an automatic task that runs in the background at regular intervals. At each iteration, the task performs the following basic steps:

Identify candidate indexes based on column and column group usage.

Create a set of candidate indexes in unavailable and invisible modes. These indexes do not take up storage space.

Query the priority list of previously executed statements to determine whether the candidate index is worth rebuilding.

Compile the statement to determine whether the optimizer selects a new index, and then rebuild the index selected by the optimizer.

Execute statements that use automatic indexing

Do any of the following:

Mark the index as visible when the statement significantly improves its performance. The database does not change the schedule of statements in the workload until the index is validated and marked as visible. Until then, the database does not invalidate the cursor and continue to use the old execution plan.

Tag indexes cannot be used when providing insufficient performance advantages. This occurs in a delayed manner when there is a low probability of using other indexes or when there is space pressure.

Use SQL plan management to avoid regression. An index may benefit one statement, but can cause performance degradation in the second statement. In this case, the database optimizes the first statement by marking the index visible. To prevent the second statement from descending, the database uses SQL plan management to protect it.

Delete indexes that have not been used for a long time.

You can enable automatic indexing by performing the following procedure in the DBMS_AUTO_INDEX package:

EXEC DBMS_AUTO_INDEX.CONFIGURE ('AUTO_INDEX_MODE','IMPLEMENT')

You can also use the DBMS_AUTO_INDEX package to report automatic tasks and configure settings, such as how long to keep unused indexes.

1.4.2.1.5 SQL Program Management

SQL plan management is a preventive mechanism that enables the optimizer to automatically manage execution plans, ensuring that the database uses only known or validated plans.

This mechanism builds a SQL plan benchmark that contains one or more accepted plans for each SQL statement. By using baselines, SQL plan management prevents plans from returning to changes in the environment, while allowing the optimizer to discover and use better plans.

1.4.2.1.6 SQL performance Analyzer

The SQL performance Analyzer determines the impact of changes on SQL workloads by identifying performance differences in each SQL statement.

System changes, such as upgrading the database or adding indexes, may cause changes to the execution plan, which can affect SQL performance. By using the SQL performance Analyzer, you can accurately predict the impact of system changes on SQL performance. Using this information, you can adjust the database when SQL performance degrades, or verify and measure gain when SQL performance improves.

1.4.2.2 Manual SQL tuning tool

In some cases, you may need to run manual tools in addition to automation tools. Alternatively, you may not have access to automation tools.

1.4.2.2.1 execution Plan

Execution plans are the primary diagnostic tool for manual SQL tuning. For example, you can view the plan to determine whether the optimizer selects the expected plan or determines the effect of creating an index on the table.

You can display the execution plan in a number of ways. The following tools are the most commonly used:

DBMS_XPLAN

You can use the DBMS_XPLAN package method to display the execution plan generated by the EXPLAIN PLAN command and the query for V$SQL_PLAN.

EXPLAIN PLAN

With this SQL statement, you can view the execution plan that the optimizer uses to execute the SQL statement without actually executing the statement.

V$SQL_PLAN and related views

These views contain information about the executed SQL statements and their execution plans, which are still in the shared pool.

AUTOTRACE

The AUTOTRACE command in SQL * Plus generates execution plans and statistics about query performance. This command provides statistics such as disk reads and memory reads.

1.4.2.2.2 Real-time SQL monitoring and real-time database operation

The real-time SQL monitoring feature of the Oracle database allows you to monitor the performance of SQL statements during execution. By default, SQL monitoring starts automatically when a statement runs in parallel, or when at least 5 seconds of CPU or I hand O time is consumed in a single execution.

A database operation is a set of database tasks defined by the end user or application code, such as batch jobs or extract, transform, and load (ETL) processing. You can define, monitor, and report database operations. Real-time database operations provide the ability to automatically monitor composite operations. After execution starts, the database automatically monitors parallel queries, DML, and DDL statements.

Oracle Enterprise Manager Cloud Control (Cloud Control) provides easy-to-use SQL monitoring pages. Alternatively, you can use the V$SQL_MONITOR and V$SQL_PLAN_MONITOR views to monitor SQL-related statistics. You can use these views with the following views to get more information about the execution you are monitoring:

V$ACTIVE_SESSION_HISTORY

V$SESSION

V$SESSION_LONGOPS

V$SQL

V$SQL_PLAN

1.4.2.2.3 Application tracking

The SQL trace file provides performance information about individual SQL statements: parse counts, physical and logical reads, misses on the library cache, and so on.

Trace files can sometimes be used to diagnose SQL performance problems. You can use the DBMS_MONITOR or DBMS_SESSION package to enable and disable SQL tracing for a specific session. When you enable the trace mechanism, the Oracle database implements tracing by generating trace files for each server process.

The Oracle database provides the following command-line tools to analyze trace files:

TKPROF

This utility accepts a trace file generated by the SQL trace tool as input and then generates a formatted output file.

Trcsess

This utility merges trace output from multiple trace files based on conditions such as session ID, client ID, and service ID. After trcsess merges the trace information into a single output file, you can use TKPROF to format the output file. Trcsess is useful for merging session-specific traces for performance or debugging purposes.

End-to-end application tracking simplifies the process of diagnosing performance problems in a multi-tier environment. In these environments, the middle tier routes requests from the end client to different database sessions, making it difficult to track clients across database sessions. End-to-end application tracking uses client ID to uniquely track a specific final client through all layers of the database.

1.4.2.2.4 Optimizer Hint

Hint is an instruction that is passed to the optimizer through comments in the SQL statement.

Hint enables you to make decisions automatically, usually by the optimizer. In a test or development environment, Hint is useful for testing the performance of specific access paths. For example, you may know that specific indexes are more selective for certain queries. In this case, you can use Hint to instruct the optimizer to use a better execution plan, as shown in the following example:

SELECT / * + INDEX (employees emp_department_ix) * / employee_id, department_id FROM employeesWHERE department_id > 50

Sometimes, the database may not use hint because of spelling errors, invalid parameters, conflicting prompts, and invalid prompts through conversion. Starting with Oracle Database 19c, you can generate reports on which hint was used or not used during plan generation.

1.4.3 user interface of the SQL tuning tool

Cloud Control is a system management tool that can centrally manage the database environment. Cloud Control provides access to most tuning tools.

Through the combination of graphic console, Oracle management server, Oracle intelligent agent, general service and management tools, Cloud Control provides a comprehensive system management platform.

You can access all SQL tuning tools using the command line interface. For example, the DBMS_SQLTUNE package is the command line interface for SQL tuning Advisor.

Oracle recommends Cloud Control as the best interface for database management and tuning. But if the command-line interface better illustrates a specific concept or task, our example will also use the command line.

This is the end of "how to tune Oracle SQL". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.