Oracle 10g SQL Optimization Index

What solutions do you consider when the website speed slows down? The speed of my company's website is not ideal recently. The article pages can be opened for more than 10 s. The database uses Oracle 10g Express Edtion, and the table that stores the article exceeds 0.1 million of the data volume, similar to executing a select count (1) from res where class_id = 1 is usually more than 10 s. However, today I added an index to class_id, And the execution time of this statement is almost 0 s. The effect was remarkable, just as I expected. The following is a summary of Oracle 10g SQL optimization:

1. Monitor select statements

Go to the Database homepage in "Oracle Database 10g Express Edition" from the Start Menu)

Go to the "database homepage> Management> database monitor> session> SQL" Page, enter "select" in "Search", and click "start ". In this way, you can see the recently executed select statement. Click the SQL statement link in the "SQL" column to view the execution status. "CPU time" indicates the execution time, and "processed rows" indicates the number of rows in the query result. The following "SQL text" is the complete content of the SQL statement. The "Index" area shows the index used by the query.

Find the query with the "CUP time" exceeding 1 second to see if an index needs to be added.

2. Add an index

Go to the "database homepage> Object Browser" page, and there is a "CREATE" button on the top right. Click "CREATE", select "Index" in the menu that appears, enter "table name", "index type" and use "regular", and then click "Next ".

On the New Page, enter the index name and index column. If it is a non-unique field, it is best to set the "single value" to unique, which may help increase the search speed. Click "Next" and click "finish.

According to my understanding, what index column should be selected during index creation is determined by the where section of the select statement. For example, if where id = 1, you only need to select an index column id. If it is where id = 1 and name = 2, you need to select the id and name index columns to create an index. Only the primary key is indexed. Because it is unique, the "single value" is set to "unique ".

For tables with a large amount of data, you should create an index on their primary keys, and add other required indexes.

Generally, indexes cannot be used for fields in the order by statement. Therefore, try to minimize the number of rows in the query results before order.

To improve the execution efficiency of the order by statement, you can add the sort_area_size value (10240000 = 10 m) as appropriate ):

 
 

  
  
alter system set sort_area_size=10240000 scope=spfile; 
 
 

In this way, the index optimization of Oracle 10g SQL is realized.

Appendix 1: Add a primary key to a table

Go to the "database homepage> Object Browser" page, and select the table on the left to create a primary key. On the table page displayed on the right, click "constraints"-> "CREATE", and set "constraint type" to "primary key". Fill in other fields as required.

Appendix 2: Oracle configuration parameter query statement

 
 

  
  
SELECT NAME,VALUE  
  
  
FROM v$parameter  
  
  
WHERE NAME IN 
  
  
(’sga_max_size’,'db_cache_size’,  
  
  
’shared_pool_size’,’shared_pool_reserved_size’,'large_pool_size’,'java_pool_size’,  
  
  
‘db_block_size’,'db_block_buffers’,'log_buffer’,’sort_area_size’,’sort_area_retained_size’,  
  
  
‘hash_area_size’,’sessions’,'open_cursors’  
  
  
)ORDER BY NAME; 
 
 

Appendix 3: Other parameter setting commands

 
 

  
  
alter system set sga_max_size=700m scope=spfile;  
  
  
 
  
  
alter system set sga_target=700m scope=spfile;  
 
 

1. Oracle sorting Optimization
2. Oracle uses hash partition optimization analysis function Query
3. Optimize Oracle tablespace design to improve database performance
4. Database optimization greatly improves Oracle Performance
5. Oracle sets system parameters for Performance Optimization