How can I optimize the database to improve Oracle performance?

Today we will mainly introduce how to optimize the database to greatly improve the performance of Oracle. Below we will introduce several simple steps to greatly improve the performance of Oracle, the image of Walmart is like the three axes of database optimization. The discussion of database optimization is an eternal topic.

Senior Oracle optimizer usually asks the person who raises Oracle performance problems 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, network and hardware often cannot be changed, and applications cannot be modified. Therefore, we should focus on Database Configuration and data structure. First, let the database have a good configuration, then, consider optimizing some slow statements. In the process of optimizing my user system, I summarized some basic and simple 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 information such as database Oracle performance, 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 performance of Oracle be approximately 240 M, of which: Share 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 for Oracle performance 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 CASE_GA_AJZLZ table has about 0.35 million data records 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 statements can be used: