Oracle SQL Performance Optimization 1

ORACLE stores the executed SQL statements in the shared buffer pool, which can be shared by all database users. When you execute an SQL statement (sometimes called a cursor), if it is exactly the same as the previously executed statement,
ORACLE can quickly obtain parsed statements and the best execution path. This function greatly improves SQL Execution performance and saves memory usage.
Www.2cto.com
To avoid repeated parsing of the same SQL statement, Oracle stores the SQL statement in memory after the first parsing. The memory in the shared buffer pool of the SGA system global area can be shared by all database users. Therefore, when you execute an SQL statement (sometimes called a cursor), if it is exactly the same as the previously executed statement, oracle can quickly obtain parsed statements and the best execution scheme. This function greatly improves SQL Execution performance and saves memory usage.
Unfortunately, Oracle only provides high-speed buffer (cache buffering) for simple tables. This function is not applicable to multi-table join queries. The database administrator must set the appropriate parameters for this region in the startup parameter file. When the memory area is larger, more statements can be reserved. Of course, the possibility of sharing is higher. When you submit an SQL statement to Oracle, Oracle first searches for the same statement in the memory.

Three conditions for SQL sharing:
1. The statements currently executed must be identical to those in the sharing pool (including case, space, and line feed)
2. The objects referred to by the two statements must be exactly the same (synonyms and tables are different objects)
3. bind variables with the same name must be used in two SQL statements)

Oracle adopts a strict matching policy for the two to achieve sharing. SQL statements must be identical (including spaces and line breaks ). The statement that can be shared must meet three conditions:
Www.2cto.com
① Character-level comparison. The statements currently executed must be the same as those in the shared pool.
For example, SELECT * from atable; is different FROM each of the following SQL statements:
SELECT * from ATABLE
Select * From Atable;
② The objects indicated by the statement must be identical. That is, the database objects operated by the two SQL statements must be the same.
③ Bind variables with the same name in the statement. For example, the two SQL statements in the first group are the same and can be shared. The two statements in the second group are different, even if different bind variables are assigned the same value at run time:
● Select pin, name from people where pin =: blk1.pin;
Select pin, name from people where pin =: blk1.pin;
● Select pin, name from people where pin =: blk1.ot _ jnd;
Select pin, name from people where pin =: blk1.ov _ jnd;

 



SQL parse and shared SQL statement:

After an Oracle instance receives an SQL statement
1. Create a Cursor to Create a Cursor
2. Parse the Statement Analysis Statement
3. Describe Results of a Query describes the Query result set.
4. Define Output of a Query defines the Output data of the Query.
5. Bind Any Variables Bind the variable
6. Parallelize the Statement parallel Statement execution
7. Run the Statement running Statement
8. Fetch Rows of a Query retrieves the queried row
9. Close the Cursor to Close the Cursor.

The following statement needs to be hard resolved once in the share pool every time it is executed. 1 million of users are 1 million times, consuming CPU and memory. If the business volume is large, it may lead to database downtime ......

If you bind a variable, you only need to parse it once and call it again.
Select * from dConMsg
Where contract_no = 32013484095139

ORACLE optimizer mode:
Oracle optimizer has three modes: RULE (based on rules), COST (based on COST), and CHOOSE (based on selection ).
You can set the default OPTIMIZER by selecting various declarations of the OPTIMIZER _ MODE parameter in the startup parameter file, such as RULE, COST, CHOOSE, ALL _ ROWS, and FIRST _ ROWS. Of course, you can overwrite SQL statements or sessions.
To use the Cost-Based Optimizer (CBO, Cost-Based Optimizer), you must run the analyze command frequently to increase the accuracy of object statistics in the database. If the optimizer mode of the database is set to selection-based, the actual optimizer mode is related to whether the analyze command has been run. If the data table has been analyzed, the optimizer mode will automatically switch to CBO. Otherwise, the database will adopt the RULE optimizer. By default, Oracle uses the CHOOSE optimizer. To avoid unnecessary full table scans, you must avoid using the CHOOSE optimizer instead of the rule-based or cost-based optimizer.

Factors that affect database system performance:
1. host CPU, RAM, and storage system;
2. OS parameter configuration and ORACLE parameter configuration;
3. Application: database design and SQL programming quality
An Application System with excellent performance needs:
1. Good hardware configuration;
2. Correct and reasonable configuration of database and middleware parameters;
3. Rational Database Design;
4. Good SQL programming;
5. Performance Optimization during runtime

Key Points of SQL Tunning:
SQL: insert, update, delete, select (select is the main concern)
How to locate the data location with minimum hardware resource consumption and minimum response time

General principles of SQL optimization:
1. Objective:
Reduce server resource consumption (mainly disk IO );
2. Design:
Use oracle optimizer as much as possible and provide conditions for it;
Suitable indexes, Double Effects of indexes, and column selectivity;
3. encoding:
Use indexes to avoid full table scan for large tables;
Rational use of temporary tables;
Avoid writing too complex SQL statements, not necessarily requiring one SQL statement to solve the problem;
Reduce the transaction granularity without affecting the business;

Optimization Overview:
● When creating a table. Create a primary key as much as possible, and adjust the PCTFREE and PCTUSED parameters of the data table based on actual needs. delete a large data table and replace delete with truncate table.

● Use indexes properly. do not have too many indexes for a table in OLTP applications. Do not create a binary tree index for columns with large data duplicates. Bitmap indexes can be used. The column sequence of the composite index should be consistent with the column sequence of the query condition. For tables with frequent data operations, indexes need to be rebuilt regularly to reduce invalid indexes and fragments.

● Use a definite column name as much as possible for query, and use less * numbers. Select count (key) from tab where key> 0 superior to select count (*) from tab;

When you want to list all columns in the SELECT clause, using dynamic SQL COLUMN reference '*' is a convenient method. unfortunately, this is a very inefficient method. in fact, ORACLE converts '*' into all column names in sequence during parsing. This is done by querying the data dictionary, which means it takes more time;

Use as few nested subqueries as possible. Such queries consume a large amount of CPU resources. For queries with more or operations, we recommend that you divide them into multiple queries and join them with union all; select the most efficient table name order in the query statement for multi-table queries. The Oracle parser parses tables from right to left, so the tables with fewer records are placed on the right.

● Use the commit statement whenever possible to submit transactions, which can release resources, unlock, release log space, and reduce management costs in a timely manner. Avoid remote access when performing frequent data operations with high performance requirements, for example, database links. frequently accessed tables can be stored in the memory: alter table... cache;

● Dynamically execute SQL statements in Oracle. execute is recommended instead of the dbms_ SQL package.