I. Set the appropriate SGA
Complaints about the server hardware is good, but Oracle is very slow. Is likely to be caused by the irrational allocation of memory.
1) assuming memory is 512M, which is usually a small application is recommended that the Oracle SGA about 240M,
Where: shared pool (SHARED_POOL_SIZE) can be set 60M to 80M, according to the actual number of users, queries, and so to be.
The data block buffer can be broadly assigned 120M-150M:
8i under the need to set DB_BLOCK_BUFFERS, DB_BLOCK_BUFFER * DB_BLOCK_SIZE equal to the data block buffer size.
9i under the data buffer can be used to directly assign db_cache_size.
2) assuming memory is 1G, Oracle's SGA to consider allocation of 500M: 100M shared pool allocated to the 150M, 300M allocated data buffer to 400M.
3) memory 2G, SGA can be considering the allocation of 1.2G, shared pool of 300M to 500M, the rest to the data block buffer.
4) more than 2G of memory: shared pool of 300M to 500M is enough, more is not much help;
(Biti_rainy with special reference) data buffer is as large as possible, but must pay attention to two issues:
First, operating systems and other applications to give enough memory left,
The second is for 32-bit operating system, Oracle's SGA has 1.75G restrictions. Some 32-bit operating system can break the limit
II. Analyze tables and indexes, change the optimization mode
Oracle default optimization mode is CHOOSE, in this case, if the table has not been analyzed, often results in queries use full table scan instead of index, which usually result in disk I / O too much, which led to the query is very slow, if not using the execution plan stability, you should analyze the tables and indexes are about, this may make inquiries directly to the speed increased dramatically. analysis table with the ANALYZE TABLE command can be analyzed with the ANALYZE INDEX command can be indexed for less than 100 million of the table, you can consider the analysis of the entire table, for large tables, can be analyzed by percentage, but the percentage is not too low, otherwise the resulting statistics may be inaccurate. LAST_ANALYZED by DBA_TABLES the table column to see whether through analysis or analysis time, the index can By DBA_INDEXES of LAST_ANALYZED column.
The following examples illustrate the analysis by the speed before and after contrast (Table USER approximately 35 million data, primary key) to open automatically the first in SQLPLUS query execution plan feature.
(First secondary execution \ RDBMS \ ADMIN \ utlxplan.sql to create PLAN_TABLE this table)
SQL> SET AUTOTRACE ON
SQL> SET TIMING ON
By SET AUTOTRACE ON to see the statement execution plan, by SET TIMING ON to see the statement run time.
SQL> select count (*) from USER;
Elapsed time: 00: 00: 21.38
0 SELECT STATEMENT Optimizer = CHOOSE
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'USER'
... ... ... ... ... ... ... ...
Please note that the above analysis TABLE ACCESS (FULL), indicating that the statement executes a full table scan. And the query used 21.38 seconds. At this time table has not yet been analyzed.
Now we come to the table for analysis:
SQL> analyze table USER compute statistics;
Table analyzed. Elapsed time: 00: 05: 357.63.
Then check again:
SQL> select count (*) from USER;
Elapsed time: 00: 00: 00.71
0 SELECT STATEMENT Optimizer = FIRST_ROWS (Cost = 351 Card = 1)
1 0 SORT (AGGREGATE)
2 1 INDEX (FAST FULL SCAN) OF 'PK_USERID' (UNIQUE) (Cost = 351 Card = 346351)
... ... ... ... ... ... ... ... ... ...
Please note, this time in just 0.71 seconds! Thanks INDEX (FAST FULL SCAN). By analyzing the tables, it uses the PK_USERID index, disk I / O significantly reduced, the speed has increased dramatically! Following useful statement analysis of a user used to generate all the tables and indexes, assuming the user is ORA9I:
SQL> set pagesize 0
SQL> spool d: \ analyze_tables.sql;
SQL> select 'analyze table' | | owner ||'.'|| table_name | | 'compute statistics;' from dba_tables where owner = 'ORA9I';
SQL> spool off
SQL> spool spool d: \ analyze_indexes.sql;
SQL> select 'analyze index' | | owner ||'.'|| index_name | | 'compute statistics;' from dba_indexes where owner = 'ORA9I';
SQL> spool off
SQL> @ d: \ analyze_tables.sql
SQL> @ d: \ analyze_indexes.sql
Explanation: The above statement generates two sql files, respectively, of all ORA9I tables and indexes, if necessary in accordance with the percentage of the table, you can modify the script.
Through the above steps, we completed the analysis of tables and indexes, you can test the speed of improvement is recommended on a regular basis run the above statement, especially after a large number of data updates.
Of course, you can also dbms_stats to analyze tables and indexes, and more convenient, but I still used the above method, because the success or failure will directly prompt out.
Three would be used a small table, the index nail in the data cache memory on the KEEP pool the data read speed is far faster than the hard to read, allegedly, in-memory data read 14,000 times the speed of the hard drive! If resources were more rich,
The commonly used small, and often full-table scan the table to the nail in memory, of course, is better. Simply by ALTER TABLE tablename CACHE to achieve,
After the ORACLE8i can ALTER TABLE table STORAGE (BUFFER_POOL KEEP). In general, consider the 200 block of data within the table on the keep pool,
Of course, according to memory size should be determined.
On how to find out which table or index eligible to use this article provided access.sql and access_report.sql. These two scripts are well-known Oracle experts, Burleson write, you can also read the case of adjusted according to actual situation at the script for the index, you can ALTER INDEX indexname STORAGE (BUFFER_POOL KEEP) to nail in the KEEP pool.
Set the table in the KEEP pool to do some preparation work for ORACLE9i need to set DB_KEEP_CACHE_SIZE, for 8i, need to set buffer_pool_keep.
In 8i, but also to modify db_block_lru_latches, the parameter default is 1, can not be used buffer_pool_keep. This parameter should be less than 2 * 3 * CPU a small number of
But greater than 1, to set DB_KEEP_CACHE_BUFFER. Buffer_pool_keep db_block_buffers allocated from, so have less than db_block_buffers.
Set these parameters, you can put frequently used objects in memory permanently nail.
IV. Adjust sort parameters
1) SORT_AREA_SIZE: The default size is used to sort the SORT_AREA_SIZE 32K, often seemed a bit small, generally consider the set 1M (1048576). This parameter can not be set too large, because each connection must be assigned the same sort of memory.
2) SORT_MULTIBLOCK_READ_COUNT: Increasing this parameter can improve the performance of the temporary sort table space, the parameter default is 2, can be sorted into 32 to compare the query time. Note that the maximum value of this parameter has a relationship with the platform.