Oracle SQL tuning procedure
The full name of SQL is Structured Query Language (Structured Query Language ). SQL is an industrial standard database query language used in the middle of 1980s. Do not confuse SQL with commercial products such as Microsoft SQL server or open-source MySQL. All SQL acronyms are part of the SQL standard.
For more information about Oracle, see Oracle topics page http://www.bkjia.com/topicnews.aspx? Tid = 12
I. adjustment before SQL tuning
The following rough method can save thousands of hours of tedious SQL tuning, because once adjusted, it will affect hundreds of SQL queries. Remember, you must adjust it first. Otherwise
Continuous optimizer parameter changes or statistical information may not help your SQL adjustments.
Remember, you should always give priority to system-level SQL tuning. Otherwise, adjusting SQL tuning may result in a loss of the SQL statements you have previously adjusted.
1. Optimize the system kernel
First, we should consider adjusting the disk and network I/O subsystems (such as RAID, DASD bandwidth, and network) to minimize the I/O time, network package size, and scheduling frequency.
2. Adjust the optimizer statistics
Statistics of the optimizer should be collected and stored on a regular basis so that the optimizer generates the best execution plan based on the distribution of data. In addition, the histogram helps optimize the table join and for Skewed
The where clause predicate information makes the correct access decision.
3. Adjust optimizer Parameters
The following optimizer parameters should be adjusted
Optimizer_mode, optimizer_index_caching, optimizer_index_cost_adj
4. Optimized instances
The following instance/session-level parameters will affect SQL Performance
Db_block_size, db_cache_size, and OS parameters (db_file_multiblock_read_count, cpu_count, & c ),
5. Adjust the SQL access load using indexes or materialized views
After 10 Gbit/s, Oracle can use SQL Access advisor to generate indexes or materialized views for SQL statements. You should always use indexes to optimize SQL statements, especially function-based indexes.
Oracle 11g improvement:
The SQL Performance Analyzer (SPA) added in Oracle 11G is a new feature that accelerates SQL adjustment as a whole.
Through SPA, once a load (called SQL tuning set or STS) is created, Oracle will use the complex prediction module to repeatedly execute the workload (
To obtain the best SQL Execution Plan for the current load. With SPA, we can predict the impact of an SQL load based on system changes, as well as the predicted image parameters.
Adjustment, system schema adjustment, hardware adjustment, operation adjustment, and response time of the Current SQL statement after Oracle Upgrade. For more details, see Oracle 11g New Feature.
When the runtime environment, Oracle instances, and objects are adjusted, more attention is given to a single SQL statement with the greatest impact on database performance. The following is an adjustment for a single SQL statement
Some general suggestions are provided to improve Oracle performance.
Ii. Oracle SQL tuning goals
Oracle SQL tuning is a complex topic. Oracle Tuning: The Definitive Reference describes The details of SQL tuning. Nevertheless,
To improve system performance, Oracle DBA should follow the following general guidelines.
1. SQL tuning target
It extracts more data rows to generate the best execution plan (physically read (PIO) and logical read (LIO) as much as possible) with minimal database access times ).
Guiding principles
Remove unnecessary large full table scans
Full table scanning of large tables will generate a large system I/O and degrade the performance of the entire database. Optimization experts first evaluate the number of rows returned by the current SQL query. The most common
To add an index to a large table scanned by the entire table. B-tree indexes, bitmap indexes, and function-based indexes can avoid full table scanning. Sometimes, you can scan unnecessary full tables.
You can add a prompt to avoid full table scan.
Cache small table full table Scan
Sometimes full table scan is the fastest way to access these tables. The Administrator should ensure that the dedicated data buffer (keep buffer cache, nk buffer cache) is available to these tables. In
Small tables can be forcibly cached to the keep pool after Oracle 8.
Use the best Index
Oracle access objects sometimes have more than one index option. Therefore, you should check each index on the current query object to ensure that Oracle uses the best index.
Materialized aggregation operations are calculated in a static table
SQL Access advisor, one of the 10 Gb features of Oracle, provides recommendations for indexing and materialized views. Materialized views can be used to pre-connect tables and pre-Digest Table data. (Translator)
Oracle can update the data in the materialized view in advance based on the specific update method. In the query, only the materialized view can be queried to obtain the final required statistical data.
Result. Materialized View is actually an entity table)
These summarize the objectives of SQL tuning. However, it seems simple and not easy to adjust, because it requires a thorough understanding of Oracle SQL. Next let's start from
Understand Oracle SQL optimization as a whole.
2. Oracle SQL Optimizer
Oracle DBA must first check the default optimizer mode of the current database. Oracle initialization parameters provide a lot of Cost-optimized optimizer modes and old obsolete rules-based
Optimizer mode (or hint). The cost-based optimizer mainly depends on the statistical information collected by table objects using the analyze command. Oracle depends on the table statistics.
And generate the most efficient execution plan for the current SQL statement. In some cases, cost Optimizer may make incorrect decisions. Cost-based optimizer is constantly improving,
However, there are still many scenarios where the rule-based optimizer can make queries more efficient.
Before Oracle 10 Gb, Oracle's default optimizer mode is CHOOSE. In this mode, if the table object lacks statistics, Oracle uses rule-based optimization.
If the statistical information exists, the cost-based optimizer is used. The hidden danger of using the CHOOSE mode is that some complex query objects have statistical information, while other objects
There is no statistical information.
Starting from Oracle 10 Gb, the default optimizer mode is ALL_ROWS, which helps full table scan better than index scan. The ALL_ROWS optimizer mode is designed to minimize computing resources and
Full table scan. Index scanning (first_rows_n) adds additional I/O overhead. But they can return data faster.
Therefore, first_rows, first_rows_100 or first_rows_10 are selected for most OLTP systems, so that Oracle uses index scanning to reduce the number of read blocks.
Note: Starting from Oracle 9i R2, the Oracle performance adjustment guide indicates that the first_rows optimizer mode has been deprecated and replaced by first_rows_n.
When only some tables contain CBO statistics, while others do not, Oracle uses the cost-based optimization mode to estimate the statistics of other tables at run time (that is, dynamic sampling ).
), Which affects the performance of a single query to a large extent.
In short, the Oracle database administrator should always try to change the optimizer mode as the first step of SQL tuning. The primary principle of Oracle SQL tuning is to avoid the terrible full table scan.
Description. One feature is that a non-efficient SQL statement uses all indexes to improve query performance. This is still a failed SQL statement.
Of course, it is appropriate to use full table scan in some cases, especially for aggregate operations such as sum and avg, because to obtain results, most data rows on the table must be read
Cache. SQL tuning experts should reasonably evaluate each full table scan and verify whether the index can improve the performance.
In most Oracle Systems, SQL statements retrieve only a subset of table data. The Oracle optimizer checks whether the index causes more I/O. However, if
For an inefficient query, it is difficult for the cost-based optimizer to select the optimal data access path. Instead, full table scan is preferred. Therefore, the Oracle database administrator should always review those steps
SQL statement for full table scan.
For more questions about full table scan and The correct optimization mode, see "Oracle Tuning: The Definitive Reference"
