How to Improve the query speed of Oracle (zt)

Database optimization greatly improves Oracle Performance
Several Simple Steps greatly improve Oracle performance-I optimized the database three axes.
Database optimization is an eternal topic. Senior Oracle optimizer usually asks the person who raises performance questions to create a statspack for the database and paste the database configuration. Some people think that they should capture the slowest statement for optimization. However, the actual situation is that the person who raises the question may not understand the execution plan at all, let alone statspack. In my opinion, database optimization should first take into account the following aspects: Network, server hardware configuration, operating system configuration, Oracle server configuration, data structure organization, and then specific adjustments. In fact, the network and hardware often cannot be changed, and applications Program Generally, it cannot be modified. Therefore, we should focus on Database Configuration and data structure. First, let the database have a good configuration, and then consider optimizing some slow statements. In the process of optimizing my user system, I summarized some basic and easy-to-use methods to optimize the database. However, please note that these are not necessarily widely used, and some may even have side effects, but they are often effective for OLTP systems and cost-based databases. Try them. (Note: The attachment is a script written by Burleson to report database performance and other information, which is used in this article)
1. Set the appropriate SGA
Some people often complain that the server hardware is good, but oracle is very slow. It is likely that the memory allocation is unreasonable. (1) Suppose the memory is 512 MB, which is usually a small application. It is recommended that the SGA of Oracle be approximately 240 m, of which: The Shared Pool (shared_pool_size) can be set to 60 m to 80 m, depending on the actual number of users, queries, etc. The data block buffer can be roughly allocated 120 m-150 m. In 8i, db_block_buffers needs to be set, and db_block_buffer * db_block_size is equal to the data block buffer size. You can use db_cache_size to directly allocate the data buffer in 9i.
(2) If the memory is 1 GB, the SGA of Oracle can allocate 500 m: The Shared Pool is allocated 150 m to 400 m, and the data buffer is allocated m to M.
(3) The memory is 2 GB, And the SGA can allocate 1.2 GB, the sharing pool is between 500 mb and MB, and the rest is allocated to the data block buffer.
(4) 2 GB memory or above: it is enough to share a pool of MB to MB, and it does not help much. (biti_rainy has a special note) the data buffer is as large as possible, but you must pay attention to two problems: first, you need to leave enough memory for the operating system and other applications, and second, for a 32-bit operating system, Oracle's SGA has a limit of GB. Some 32-bit operating systems can break through this restriction. For more information about the method, see the masterpiece of Biti.
2. Analyze tables and indexes, and change the optimization mode
The default optimization mode of Oracle is choose. In this case, if the table is not analyzed, full table scan is often used for queries without indexing. This usually results in too many disk I/O, resulting in slow query. If the execution plan stability is not used, analyze both the table and index, which may directly increase the query speed. The analysis table command can use analyze table to analyze indexes. You can use the analyze Index Command. For tables with less than 1 million, you can analyze the entire table. For large tables, you can analyze them by percentage, but the percentage cannot be too low. Otherwise, the statistical information generated may be inaccurate. You can use the last_analyzed column of dba_tables to check whether the table has been analyzed or analyzed. You can use the last_analyzed column of dba_indexes for indexing.
The following example shows the speed comparison before and after analysis. (The table case_ga_ajzlz contains about 0.35 million data and has a primary key.) Enable the automatic query execution plan function in sqlplus. (For the first time, run \ RDBMS \ admin \ utlxplan. SQL to create the plan_table table)
SQL> set autotrace on
SQL> set timing on
You can use set autotrace on to view the statement execution plan and set timing on to view the statement running time.
SQL> select count (*) from case_ga_ajzlz;
Count (*)
----------
346639
Used time: 00: 00: 21.38
Execution Plan
0 SELECT statement optimizer = choose
1 0 sort (aggregate)
2 1 Table Access (full) of 'case _ ga_ajzlz'
........................
Note that table access (full) in the preceding analysis indicates that the statement performs a full table scan. In addition, the query takes 21.38 seconds. The table has not been analyzed yet. Next we will analyze the table:
SQL> analyze table case_ga_ajzlz compute statistics;
The table has been analyzed. Used time: 00: 05: 357.63. Then query:
SQL> select count (*) from case_ga_ajzlz;
Count (*)
----------
346639
Used time: 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 takes 0.71 seconds! This is due to index (Fast full scan ). Through the analysis table, the pk_ajzlz index is used for queries. the disk I/O is greatly reduced and the speed is greatly improved! The following practical statement can be used to generate and analyze all the tables and indexes of a user. Assume that 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 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 preceding statements generate two SQL files to analyze all 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 tables and indexes, and can test the speed improvement. We recommend that you run the preceding statements on a regular basis, especially when a large amount of data is updated.
Of course, you can use dbms_stats to analyze tables and indexes, which is more convenient. But I still get used to the above method, because the success or failure will be prompted directly.
In addition, we can modify the optimization mode. The value of optimizer_mode can be rule, choose, first_rows, and all_rows. For the OLTP system, you can change it to first_rows to request the query to return results as soon as possible. In this way, the query performance can be improved even if no analysis is required. However, the analysis of tables and indexes helps to find the most appropriate execution plan.
3. Set cursor_sharing = force or similar
This method is only available at the beginning of 8i, which is not supported by oracle805. By setting this parameter, You can forcibly share explain plans with different words. 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 problem of low buffer utilization and avoid re-interpretation of statements. This function can solve the performance degradation caused by hard parsing to a large extent. You may decide whether to change this parameter to force based on the actual situation of the system. This parameter is exact by default. Note that Oracle must be patched before modification. Otherwise, Oracle will occupy 100% of the CPU and cannot be used. For Oracle9i, you can set it to similar, which combines the advantages of force and exact. However, please use this function with caution. This parameter may also have a huge negative impact!
4. Store frequently used small tables and indexes in the data cache keep pool
The Data Reading speed in the memory is much faster than that in the hard disk. It is said that the data reading speed in the memory is 14000 times that in the hard disk! If the resources are rich, it is better to give the tables frequently used for full table scanning to the dingtalk memory. You can simply use alter table tablename cache. After Oracle8i, you can use alter table table Storage (buffer_pool keep ). In general, you can consider placing the tables within 200 data blocks in the keep pool, depending on the memory size and other factors. You can use the access. SQL and access_report. SQL provided in this article to find out which tables or indexes meet the conditions. These two scripts are written by the famous Oracle expert Burleson. You can also adjust the scripts based on your actual situation. You can use alter index indexname storage (buffer_pool keep) to pin indexes in the keep pool.
Prepare the table in the keep pool. Db_keep_cache_size must be set for Oracle9i, and buffer_pool_keep must be set for 8i. In 8i, you also need to modify db_block_lru_latches. The default value of this parameter is 1, and buffer_pool_keep cannot be used. This parameter should be less than 2*3 * CPU, but must be greater than 1 to set db_keep_cache_buffer. Buffer_pool_keep is allocated from db_block_buffers, so it must be smaller than db_block_buffers. After these parameters are set, you can pin common objects to the memory permanently.
5. Set optimizer_max_permutations
For multi-table join queries, if cost-based optimization (CBO) is adopted, Oracle will work out many operation solutions and select the optimal solution. This parameter is used to set the optimal solution for Oracle. If the value is too large, it takes a long time to calculate the optimal solution. The default values of oracle805 and 8i are 2000. We recommend that you change the value of 8. The default value of 9i is 2000.
6. Adjust sorting Parameters
(1) sort_area_size: the default sort_area_size used for sorting is 32 K, which is usually a little small and can be set to 1 m (1048576 ). This parameter cannot be set to an excessively large value because the same sort memory is allocated for each connection.
(2) sort_multiblock_read_count: increasing this parameter can improve the sorting performance of the temporary tablespace. The default value is 2. You can change it to 32 to compare the changes in the sorting query time. Note that the maximum value of this parameter is related to the platform.