How to improve Oracle's performance

A Set the appropriate SGA

People often complain that server hardware is good, but Oracle is slow. It is likely that the memory allocation is unreasonable. (1) Assuming that the memory has 512M, this is usually a small application. Oracle's SGA is recommended for approximately 240M, where the shared pool (shared_pool_size) can be set to 60M to 80M, depending on the actual number of users, inquiries, and so on. The block buffer can be roughly allocated 120m-150m,8i to set the Db_block_buffers,db_block_buffer*db_block_size equal to the block buffer size. The data buffers under 9i can be allocated directly using Db_cache_size.

(2) Assume that the memory has a 1g,oracle SGA can be considered to allocate 500M: Shared pool allocation 100M to 150M, data buffer allocation 300M to 400M.

(3) Memory 2G,SGA can be considered to allocate 1.2G, shared pool 300M to 500M, and the rest to the data block buffer.

(4) More than 2G of memory: Shared pool 300M to 500M is enough, and no amount of help; (biti_rainy) The data buffer is as large as possible, but be sure to pay attention to two problems: one is to give the operating system and other applications to leave enough memory, the second is for 32-bit operating system, Oracle's SGA has a 1.75G limit. Some 32-bit operating systems can break through this limit, but also please see Biti's masterpiece.

Two Analyze tables and indexes, change optimization mode

The Oracle default optimization mode is choose, in which case a table that is not parsed often causes the query to use a full table scan instead of an index. This usually results in too much disk I/O and causes the query to be slow. If you do not use execution plan stability, you should analyze both the table and the index, which may lead to a significant increase in query speed. The parse Table command can use the Analyze table to parse the index using the Analyze Index command. For less than 1 million tables, consider analyzing the entire table, which can be analyzed as a percentage for large tables, but the percentages cannot be too low, otherwise the resulting statistics may not be accurate. You can see whether the table is parsed or parsed through the Dba_tables last_analyzed column, and the index can pass through the last_analyzed columns of dba_indexes.

The following examples illustrate the speed comparison before and after analysis. (table Case_ga_ajzlz has approximately 350,000 data, has a primary key) first opens the Automatic Query execution plan feature in Sqlplus. (The first time you want to perform rdbmsadminutlxplan.sql to create plan_table this table)

Sql> SET Autotrace on
Sql>set TIMING on

　　
View the execution plan of the statement through set autotrace on, and view the statement run time through set TIMING on.

Sql> Select COUNT (*) from Case_ga_ajzlz;
COUNT (*)
----------
346639
　　
Time used: 00:00:21.38
　　
Execution Plan
0 SELECT STATEMENT Optimizer=choose
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (full) ' Case_ga_ajzlz '
........................
Note the table ACCESS (full) in the analysis above, which shows that the statement performs a complete table scan. And the query used 21.38 seconds. The table has not yet been analyzed. Let's analyze the table below:

sql> Analyze table Case_ga_ajzlz compute statistics;

　　
The table is parsed. Time used: 00:05:357.63. And then to query:

Sql> Select COUNT (*) from Case_ga_ajzlz;
COUNT (*)
----------
346639
　　
Time used: 00:00:00.71
　　
Execution Plan
　
0 SELECT STATEMENT optimizer=first_rows (cost=351 card=1)
1 0 SORT (AGGREGATE)
2 1 INDEX (FAST full SCAN) of ' Pk_ajzlz ' (UNIQUE) (cost=351
card=346351)
..............................

　　
Please note that this time only took 0.71 seconds! This is thanks to index (FAST full SCAN). Through the analysis table, the query uses the PK_AJZLZ index, the disk I/O greatly reduces, the speed also greatly increases! The following utility statement can
　　
Used to generate all tables and indexes that analyze a user, assuming the user is GAXZUSR:

Sql> Set pagesize 0
Sql> spool D:analyze_tables.sql;
Sql> Select ' Analyze table ' | | owner| | '. ' | | table_name| | '
Compute statistics ' from dba_tables where owner= ' gaxzusr ';
Sql> Spool Off
Sql> spool spool d:analyze_indexes.sql;
Sql> Select ' Analyze index ' | | owner| | '. ' | | index_name| | '
Compute statistics ' from dba_indexes where owner= ' gaxzusr ';
Sql> Spool Off
Sql> @d:analyze_tables.sql
Sql> @d:analyze_indexes.sql

　　
Explanation: The above statement generates two SQL files, analyzing all the GAXZUSR tables and indexes, respectively. If you need to analyze the table by percentage, you can modify the script. Through the above steps, we have completed the analysis of the table and index, you can test the speed of improvement. It is recommended that the above statements be run on a regular basis, especially when data has been updated.

Of course, you can also use Dbms_stats to analyze tables and indexes, more convenient. But I am still accustomed to the above method, because success or not will be directly prompted.

In addition, we can modify the optimization mode. Optimizer_mode values can be rule, CHOOSE, first_rows, and All_rows. For OLTP systems, you can change to first_rows to require the query to return results as soon as possible. This can also improve query performance under normal circumstances, even without analysis. But tables and indexes are analyzed to help find the most appropriate execution plan.

Three Set Cursor_sharing=force or similar

This method is 8i to begin with, oracle805 does not support. By setting this argument, you can force a shared statement interpretation plan that has only a different text. For example, the following two statements can be shared:

Sql> SELECT * from MYTABLE WHERE name= ' Tom '
Sql> SELECT * from MYTABLE WHERE name= ' Turner '

　　
This method can greatly reduce the buffer utilization problem and avoid the explanation of the statement. Through this function, it can solve the problem of the performance degradation caused by hard resolution to a large extent. Personal feeling can be based on the actual situation of the system, decide whether to change this parameter to force. The default for this parameter is exact. However, it is important to note that before you modify, you must first patch Oracle, otherwise the Oracle will occupy 100% of the CPU, unusable. For oracle9i, it can be set to similar, which combines the advantages of force and exact. However, please use this function carefully, this parameter may also bring a great negative impact!

Four To pin a commonly used small table and index in a data cache keep pool

The data read on memory is much faster than reading on the hard disk.

, the data read in memory is 14,000 times times faster than the hard drive! If the resources are rich, the commonly used small, and often a full table scan of the table to the nail memory, of course, is better. Can be implemented simply by ALTER TABLE TableName cache, which can be used after oracle8i to use ALTER TABLE table STORAGE (Buffer_pool KEEP). In general, you can consider the table in the 200 data block in the Keep pool, of course, depending on the size of memory and other factors. You can use the Access.sql and Access_report.sql provided in this article on how to identify those tables or indexes that meet the criteria. These two scripts are written by a well-known Oracle expert, Burleson, and you can adjust the script to the actual situation as you read it. For indexes, it can be nailed to the KEEP pool through alter index IndexName STORAGE (Buffer_pool KEEP).

It takes some preparation to set the table in the keep pool. For oracle9i need to set db_keep_cache_size, for 8i, you need to set buffer_pool_keep. In 8i, you also modify db_block_lru_latches, which defaults to 1 and cannot use Buffer_pool_keep. This parameter should be less than the number of 2*3*cpu, but greater than 1 to set the Db_keep_cache_buffer. Buffer_pool_keep are allocated from the db_block_buffers and are therefore less than db_block_buffers. After setting these parameters, you can permanently pin the common objects in memory.

Five Set Optimizer_max_permutations

For a multiple-table join query, if the cost-optimized (CBO) is used, Oracle calculates a wide variety of running scenarios,
　　
Choose the best scheme from it. This parameter is to set the number of options for Oracle to choose the best. If the setting is too large, then the calculation of the optimal scheme process is also longer. The default for Oracle805 and 8i is 80000, 8 is recommended to 2000. For 9i, the default is 2000.

Six Adjust sorting parameters

(1) Sort_area_size: The default sort_area_size size for sorting is 32K, which is usually a bit small, and can generally be considered to be set to 1M (1048576). This parameter cannot be set too large because each connection is assigned the same sort of memory.

(2) Sort_multiblock_read_count: Increase this parameter can improve the temporary table space sorting performance, the default is 2, can be changed to 32来 comparison of sorting query time changes. Note that the maximum value of this parameter is related to the platform.