Oracle stores executed SQL statements in a shared pool of memory

Oracle SQL performance Optimization in layman's
Oracle holds executed SQL statements in a shared pool of memory, 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 previous executed statement,
Oracle can quickly get the parsed statement and the best execution path. This feature greatly improves the performance of SQL execution and saves memory usage.

In order not to parse the same SQL statements repeatedly, Oracle stores the SQL statements in memory after the first resolution. The memory in the shared buffer poo1, which is located in the system global region of the SGA (Systemglobal area), can be shared by all database users. So, when you execute an SQL statement (sometimes called a cursor), if it is exactly the same as the statement that was executed before, Oracle can quickly get the parsed statement and the best execution scenario. This feature of Oracle greatly improves the performance of SQL execution and saves memory usage.
Unfortunately, Oracle only provides caching (cache BUFFERIILG) for simple tables, which does not apply to multi-table connection queries. The database administrator must set the appropriate parameters for this zone in the startup parameters file, and the larger the memory area, the more statements can be preserved, and the greater the likelihood of being shared. When an SQL statement is submitted to Oracle, Oracle will first look for the same statement in this block of memory.

Three conditions for SQL sharing:
1, the statement that is currently executing and the statements in the shared pool must be identical (including case, space, newline, and so on)
2, two statements must have exactly the same object (synonyms and tables are different objects)
3, two SQL statements must use the same name as the binding variable (bind variables)

Oracle takes a strict matching strategy for both to achieve sharing. The SQL statements must be identical (including spaces, line breaks, and so on). The ability to use a shared statement must meet three conditions:

① character-level comparisons. The statements that are currently executed and those in the shared pool must be identical.
For example: SELECT * from ATABLE, and each of the following SQL statements are different:
SELECT *from ATABLE
Select * from Atable;
The ② statement must have exactly the same object. The database object that the two SQL statements operate on must be the same.
The same named binding variable must be used in the ③ statement. For example: The first group of two SQL statements are the same and can be shared, whereas two statements in the second group are different, even when the runtime assigns different binding variables to the same value:
First set of select Pin,name from people where pin =: blk1.pin;
Select Pin,name from people where pin =:blk1.pin;
The second set of select Pin,name from people where pin =:blk1.ot_jnd;
Select Pin,name from people where pin =: blk1.ov_jnd;

650) this.width=650; "Title=" Click to view the large image "alt=" 659x414 "src=" http://img.educity.cn/img_10/263/2014010505/21786054626. PNG "width=" 659 "height=" 414 "/>


650) this.width=650; "Title=" Click to view the large image "alt=" 832x520 "src=" http://img.educity.cn/img_10/263/2014010505/21789054626. PNG "width=" height= "437"/>

SQL parse with shared SQL statements:

When an Oracle instance receives a SQL
1, create a cursor
2. Parse the Statement analysis statement
3. Describe Results of a query describes the result set
4. Define output of a query defines the input data of the query
5. Bind any Variables bound variable
6. Parallelize The Statement parallel execution statement
7. Run the Statement statement
8, fetch rows of a query to fetch the row
9. Close the cursor off cursors


The following statement per execution needs to be share POOL hard parse once, 1 million users is 1 million times, consumes CPU and memory, if the volume of business is large, it is likely to lead to downtime ...

If you bind a variable, you only need to parse it hard once, and repeat the call to
SELECT * FROM Dconmsg
where contract_no = 32013484095139

ORACLE Optimizer mode:
There are 3 modes for Oracle's Optimizer: rule (rules-based), cost (based on costs), CHOOSE (based on selection).
The method of setting the default optimizer is to select the various declarations for the Optimizer_ mode parameter in the startup parameters file, such as rule, cost, CHOOSE, All_ rows, first_ rows. Of course, it can be overridden at the SQL statement level or at the session level.
In order to use the cost-based optimizer (cbo,cost-based Optimizer), you must run the Analyze command frequently to increase the accuracy of the object statistics (objects statistics) in the database. If the optimizer mode of the database is set to based on selection, then the actual optimizer mode will be related to whether the Analyze command has been run. If the data table has been analyze, the optimizer mode will automatically switch to the CBO, whereas the database will use the rule-form optimizer. By default, Oracle uses the Choose Optimizer. To avoid unnecessary full-table scans, you must try to avoid using the Choose Optimizer directly, using either a rule-based or cost-based optimizer.

Factors that affect database system performance:
1, host Cpu,ram, storage system;
2,os parameter configuration, oracle parameter configuration;
3, Application: Database design and the quality of SQL programming
An application system with excellent performance needs:
1, good hardware configuration;
2, the correct and reasonable database and middleware parameters configuration;
3, reasonable database design;
4, good SQL programming;
5. Performance optimization at run time

The focus of SQL tunning:
Sql:insert, UPDATE, delete, select (main concern is select)
The focus is on how to locate the data location with minimal hardware resource consumption and minimum response time.

general principles of SQL Optimization:
1, Target:
Reduce server resource consumption (mainly disk IO);
2, Design aspects:
Rely on Oracle's optimizer as much as possible and provide conditions for it;
The appropriate index, the double effect of the index, the selectivity of the column;
3, coding aspects:
Use the index to avoid large full table SCAN;
Reasonable use of temporary tables;
Avoid writing overly complex SQL, not necessarily a SQL to solve the problem;
Reduce the granularity of the transaction without impacting the business;

Optimization Summary:
When creating a table. Should try to establish the primary key, as far as possible according to the actual need to adjust the data table pctfree and pctused parameters; Big Data table Delete, use TRUNCATE table instead of delete.

With proper indexing, there are not too many indexes on a table in an OLTP application. Columns with large data repetition do not create a two-tree index, you can use a bitmap index, and the column order of the combined index is consistent with the order of the query criteria columns, and for tables with frequent data operations, indexes need to be rebuilt periodically to reduce failed indexes and fragmentation.


Query as far as possible with a definite column name, use less * number. Select COUNT (key) from tab where key> 0 performance is better than select COUNT (*) from Tab;

When you want to list all columns in the SELECT clause, it is a convenient way to refer to ' * ' Using a dynamic SQL column. Unfortunately, this is a very inefficient approach. In fact, in the process of parsing, Oracle will convert ' * ' to all column names, which is done by querying the data dictionary, which means more time is spent;

As little as possible, this query consumes a lot of CPU resources, and for queries with more or operations, it is suggested that the query be divided into multiple queries, together with union all, and in the query statement of the Multi-table query, select the most efficient table name order. The Oracle parser parses the table from right to left, so the table with fewer records is placed on the right.

As much as possible to commit transactions with commit statements, you can release resources, unlock, free log space, reduce management costs, in the frequent, high performance requirements of data operations, to avoid remote access, such as database chain, access to frequent tables can be resident memory: ALTER TABLE ... Cache

Execute SQL dynamically in Oracle, as much as you do without dbms_sql packages.

Oracle stores executed SQL statements in a shared pool of memory