Oracle Database Optimization-Basic Concepts

Oracle has poor performance. Check the hardware configuration of the database server first. Including memory parameter adjustment. After Oracle9i, you can check the memory and pool size in the Enterprise Manager routine-configuration-memory, and set it with the recommended value, this task should be checked every other time, because the pool settings are related to the actual data volume.
Generally, on Windows 32-bit servers, the memory allocated to Oracle does not exceed 1.7 GB, because the addressing capability is so large that it is a waste if it is redundant. 70% of the total physical memory should be allocated to Oracle.
In the routine-configuration-memory, you can see the share pool of the shared pool, which stores information such as package, procedure, function, common SQL, and Oracle Data dictionary. Because this information is commonly used, placing this memory can avoid frequent parsing and IO reading to improve performance. When the High-speed buffer zone (SGA) is used, the result set of the previous query is placed in the secondary memory. In this way, the result set can be directly read from the memory during the next query to reduce Io. there is also a PGA zone, which is related to the session connected to the user and the area used for sorting. Before 8i, no session is allocated a memory for sorting, and after 9i, all user sessions can be shared, so as to avoid the problem that one session's PGA is not enough while another user's PGA is wasted.

In general, the database tuning sequence is: disk (the less the number of reads, the better, so you need to change the query policy and optimize the query SQL to reduce the number of reads), network (when the database server interacts with the application server, if the network is used, the network bandwidth may be limited, because the bandwidth is divided up and down, and other network data will be transmitted, therefore, bandwidth is also a factor to consider), memory, CPU (if the latter two are too bad, the performance is definitely not good ).

Oracle Query Optimization Methods: RBO and CBO
RBO rule based optimize is previously used for rule-based optimization 8i. Therefore, pay attention to the table order in associated queries.
CBO cost based optimize is used after 8 I (included) cost-based optimization. It is irrelevant to the table sequence and the Field Sequence in where.

About indexes:
B-TREE index with the following structure:
Root
/|/
Branching ........
/| /......................
Leaf1 ......

The leaf node structure is as follows:
| Index header | Column Length | column content | rowid (s) |

The size of a leaf node is about 8192*8 bit. Therefore, if the length of a field is 50, a node can be divided into 100 subnodes, and 1 million of the records are recorded, you only need three levels of nodes to complete the index. Therefore, generally, the depth of B-tree cannot exceed four levels. In this way, you can find a record based on B-tree, you only need to traverse up to four nodes to find the rowid, and then find the disk based on the rowid to get the final record data.

If the number of results set records for a column query is usually less than 7%, you should add an index to the column. For B-tree, the where XX is null condition does not use indexes. Therefore, we recommend that you set the default value for this column to avoid null values in this column, similarly, if this column has a null index in group by, it may also be invalid.

Bitmap index:
The bitmap index structure is also a tree structure, but the structure of the leaf node is different from that of B-tree. The results of Bitmap leaf nodes are as follows:
<Key1 start-rowid end-rowid bitmap>
<Key2 start-rowid end-rowid bitmap>
......
The content of bitmap is a 01 combination of 110010100011100001. Its length is the same as the number of rowids contained between start-rowid and end-rowid. In this case, if the content of the column corresponding to the 9th rowids in the range is key1, the value of the 9th characters in the bitmap corresponding to kei1 is 1, otherwise it is 0. Similarly, the size of each block is 8192*8, so the leaf node of a bitmap index can index about 10000 records.
Bitmap indexes are still valid for null fields. Whether the value of null is 0 in bitmap or whether the value of keyx is null must be verified. The bitmap index has a very good query effect on the or condition. It does not adapt to frequent changes in the value of the index column. If the value of the index column changes frequently, the bitmap index will be disastrous because it will lock the blocks of all the related leaf nodes to update the bitmap value. It is suitable for decision support systems.

Function Index: a B-tree index.
Note that the format and case of the function mask used in the actual SQL statement must be the same as the format and case of the function mask in the resume index. Otherwise, the function index may be invalid. In addition, trunc (), trim [You can use ltrim (rtrim (col_xxx) to avoid bugs] The index may be abnormal. May be bug. You must have the query rewrite permission to create a function index.
Because functions are classified into deterministic and non-deterministic functions, function indexes can only be built on deterministic functions. A deterministic function indicates that a definite result is returned at any time and under any circumstances for the same input value.

Reverse index: reverse index is a function index.
It reverse records to obtain efficient query performance. This can be used in special columns.

After you add an index to a table and perform analyze analysis (the analysis method is described later), when you execute an SQL statement, the database determines based on the analysis results that it considers the optimal way to read the database to obtain the final result. This cost-based optimization is automatically completed by the database. If you want to force an SQL statement to take precedence over an index, you can add hint, that is, the SQL statement is written in the following format:
Select/* index (user1.index _ XXXX) */from table11 where .... in this case, the user1.index _ XXXX index is used in the actual execution. user1 indicates the owner of the index, and index_xxxx indicates the index name (instead of the index column name)

To optimize queries, a rule is to filter more data as soon as possible.

With the PLSQL Developer Software, view a table. On the General tab, You can see initial extent/next extent/% increase/MAX extent and other content, the content defines the scalability of the table. Initail extent indicates the initial size, and next extent indicates the next increase in extent size when the current extent is insufficient, % increase indicates the proportion to be increased, and Max extent indicates the maximum number of times to be extended. Note the settings of next extent and % increase, because they are related. If there are many extensions, you may find that the next extent is very large, even the upper G is possible.

In PLSQL developer, create an explain Plan window, input the SQL to be executed to the window, and press F5 or F8, the cost-based execution plan of the database is displayed. The description in the first column shows whether the actual query will pass full table scan or index. In general, full table scan will inevitably cause performance degradation. The last three columns are worth noting:
Cost indicates the cost, which is calculated based on database I/O access and CPU performance.
Cadinality is calculated based on the index traversal or full table scan records.
Bytes indicates the amount of data accessed
For an optimized SQL query, the smaller the three values, the better.

After Oracle10g, Oracle will automatically analyze each table, so that the optimal speed can be obtained for each query. The previous version must be analyzed manually (the analysis method will be mentioned later ).

Concept of high horizontal line:

Each table has a high horizontal line, which records the physical address of the last vertex of the last extent block of the table, such as rowid. when a full table scan is performed, all data blocks before the starting point of the table to the high horizontal line are scanned. Therefore, even if the delete operation is performed on the modified table, these delete records will mark the deletion, but the full table scan will still scan the deleted data blocks, therefore, performance is affected. Therefore, if you want to delete all records of a table, you should use the truncate keyword. If necessary, you can recreate the alter table xxx rebuild;

Analysis table analyze:
The statement for executing the analysis is
Full table analysis: Analyze table xxx compute statistics;
Analyze 5% of data (faster) and specific columns and indexes: Analyze table xxx estimate statistics sample 5 percent for table for all indexes for all indexed columns;
After analyze is executed, Oracle stores the analysis results in the data dictionary, and the data dictionary exists in the share pool, therefore, when SQL is executed, the data dictionary is queried immediately and the execution path is optimized to quickly obtain the query result.

Variable binding:
See the following code:

Declare
V_aa varchar2 (2 );
V_temp number: = 0;

V_aa: = '00 ';
Select col_22 into v_temp from table_xx where col_11 = v_aa; --- sql1
....
V_aa: = '01 ';
Select col_22 into v_temp from table_xx where col_11 = v_aa; --- sql2
....

In the above Code, v_aa is used to bind variables. As you can see, the above sql1 and sql2 are exactly the same. When oracle is actually executing, you only need to perform lexical, syntax, compilation, security check, and other analysis when executing sql1. When executing sql2, it will find that the SQL already exists in the share pool of the Sharing pool, therefore, analysis processes such as lexical, syntax, compilation, and security check are saved to improve performance. However, we often use the following method:
Select col_22 into v_temp from table_xx where col_11 = '00'; --- sql1
Select col_22 into v_temp from table_xx where col_11 = '01'; --- sql2
As a result, Oracle does not consider the two sqls to be the same. Therefore, before SQL 2 is executed, lexical, syntax, compilation, and security check are performed. Therefore, the performance is degraded.
In actual program development, for example, in Java, we recommend that you use perparedstatement. In Oracle procedural functions, cursors are considered to be variable binding by default. Therefore, if a cursor is used, you do not need to define another variable to use this policy.

However, sometimes binding variables may also lead to performance degradation. For example, a table contains 0.1 million records. The gender column is divided into two values: male and female. If the binding variable where gender = v_gender is used, Oracle may use the full table scan method to query (Why ?), If you use the hard-coded where gender = 'male' method, the index on the Gender column will be used very quickly.

Bar chart analysis
Perform a column graph analysis on a column to obtain the ratio of various values in the column. This avoids the inefficiency caused by variable binding.

About full index:
If the results of a query and the where condition columns are all in one index, you can use the full index method to achieve efficiency. For example:
Select A, B from t_xxxx where id = 'zz 'is very slow, and this SQL statement is frequently executed, you can add an index: ID,, B (the condition column ID must be placed first. In this way, you only need to retrieve the index to obtain the results, and the index query is very efficient. Before 9i, this index requires attention to the field order.

Materialized View: meterialized View
To create a materialized attempt, enable query rewrite is required.
Materialized refresh methods include fast and complete. Materialized View stores the result set of the view into a table and adds indexes to the table. This improves the performance. When using mview, you can directly query the solidified table corresponding to the mview, or query the original table consistent with the conditions of the mview statement creation statement. Oracle will automatically search the table corresponding to the mview, instead of retrieving the original table.

Combination of views: Crosstab chart and star chart
For example, if one of the four tables is joined, the actual join method may be
Tab1 ------ tab2
|
|
Tab3 ------ tab4
Or it may be
Tab1 ---- tab2 ---- tab3
|
|
Tab4
The speed may vary depending on the combination method.

There are several useful points in Toad:
For example
DBA-healthy check, which can query the data hit rate and cache loss rate.
The Maximum Cache hit rate should be greater than 95%. Otherwise, it must be adjusted.
Database cache and dictionary cache loss rate should be 0
Partition data usage <5%
DBA-server statistics
Tols-SGA-Trace, etc.

Data Partition:
Generally, the partition method should be considered for a table with more than 10 million records. Partitioning is equivalent to dividing a large table into multiple small tables.
Partition Methods: Hash (calculate the hash value of the column to partition based on the hash value)
Range (range partition) ----- List (extended from range)
Partitions are also carried out on a field, and the index is added after the partition. You must add a partition index instead of a full index.
When the partition is correct, you can use the first range and then hash, or the first list and then hash method. If the partition is consistent, you cannot use the first hash and then XX method.

Placing frequently accessed enumerations, dictionaries, and comparison tables in the buffer zone can improve performance.
Before 8i: alter table xxx cache;
After 8i: alter table storage (buffer_pool keap );
There are three caching methods:
Default: the default method. If the cache space is insufficient, the cache may be removed from the cache based on an algorithm.
Keep: always put into the keep pool, never cleared
Recycle: it is cleared if there is not enough space. Therefore, this method is easier to clear than the default method.