oracle optimization 4

2010-09-25  来源:本站原创  分类:Database  人气:104 

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)
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;
346 639

Elapsed time: 00: 00: 21.38

Execution Plan
... ... ... ... ... ... ... ...
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;
346 639

Elapsed time: 00: 00: 00.71

Execution Plan

0 SELECT STATEMENT Optimizer = FIRST_ROWS (Cost = 351 Card = 1)
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.

  • Recently I read 'old white' in Oracle optimization diary, a little emotion 2010-11-29

    Recently read the old white Oracle optimization diary, the old white indeed an expert. The book is good, a bit like reading the novel. Confrontation with the developers and customers is indeed intense. This has also allowed me some mixed feelings. Do

  • oracle optimization, data backup transfer 2011-03-21

    oracle optimization, data backup transfer

  • Oracle optimization and performance tuning 2010-10-13

    Oracle database performance analysis and evaluation of the main database throughput, the database user response time indices. Database user response time and service time can be divided into system and user wait time two, namely: System database user

  • A compilation personally feel good about the oracle optimization articles written 2010-11-10

    Fine optimization of five from the aspect of your Oracle In the past decade, Oracle has become the world's most professional databases. For the IT experts, is to ensure the use of Oracle's powerful features to improve their company's productivity. On

  • oracle optimization 4 2010-09-25

    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

  • A series personally feel good about oracle optimization articles written 2010-11-10

    Vertical and horizontal fine from five to optimize your Oracle In the past decade, Oracle has become the world's one of the most professional databases. For IT professionals, is to ensure that the use of Oracle's powerful features to improve their pr

  • oracle optimization experience sharing 2011-02-15

    Delete duplicate records in a table (ID is the unique key from the increase, duplicate records: the other fields are the same) (Large amount of data, high performance requirements) Table name: T Id name age 1 louis 20 2 louis 20 3 jimmy 30 4 louis 20

  • On the Oracle optimization 2011-03-02

    It is 23:37, not my turn to work, nothing else, just reasons for the slow induction database. DBA may be done for a long time, in my view, stability is of overriding the database, backup anything else. Do nothing good dry. Optimization is divided int

  • oracle optimization 10 points 2011-04-12

    A, SGA 1, Shared pool tunning Shared pool of optimization should be on the priority, because a cache miss occurs in the shared pool than in the data buffer in the situations that lead to higher costs, due to generally higher than the data dictionary

  • oracle optimization analysis 2011-04-25

    Record, to do database optimization and index analysis program Case: 1: In the partition table, the index may lead to a global index of failure, so the execution plan does not take the index, changing the local index of local 2: analysis of the table

  • An oracle optimization experience 2011-05-06

    Background: a user behavior analysis system, using the oracle database to record the user's visit the site each day, each time the user click a record will have a day off the entire site will probably have a ten million click record. System architect

  • oracle optimization attention 2011-08-16

    (1) Select the most efficient sequence table name (only in the rule-based optimizer effectively): Oracle's parser in accordance with the order processing from right to left in the FROM clause table name, FROM clause written in the final table (base t

  • ORACLE Optimization (1) 2010-02-14

    rebuild the index θ If the table record is deleted or inserted frequently, although the total records in the table remain unchanged, the index space usage will increase. Although the record is deleted from the index, but the record of the use of spat

  • Oracle Optimization record 2010-03-04

    Usually work less involved in the sql optimization, need to accumulate! Before taking a table and sort n records The usual formulation is: select * from table where rownum> = 100 order by something The optimized formulation: select * from (select * f

  • oracle Optimization Tips 2010-10-17

    I think the main aspects should be adjusted from 5: 1. Remove unnecessary large-table full table scan 2. Cached small table full-table scan 3. Testing the use of optimization index 4. Testing the connection optimization technology 5. Cost to minimize

  • [Transfer] Oracle Optimization Journey: Using leading, use_nl, rownum tuning example 2010-12-06

    Note: This article is posted, only to learn record. Original link 1, the use of leading and use_nl to set the table for the order to speed up queries, usually the small table is set to the first t

  • oracle optimization 2 2010-09-25

    1 use the ORACLE optimizer for the ORACLE optimizer total of three kinds: a. RULE (rule-based) b. COST (based on cost) c. CHOOSE (optional) Set the default optimizer, you can OPTIMIZER_MODE init.ora file parameters for the various statements, such as

  • oracle optimization 5 2010-09-25

    Oracle's query page is basically in accordance with the format given in this article apply to the paging query format: SELECT * FROM ( SELECT A. *, ROWNUM RN FROM (SELECT * FROM TABLE_NAME) A WHERE ROWNUM <= 40 ) WHERE RN> = 21 One of the most inner

  • [Transfer] Oracle optimization trip: Use leading, use_nl, rownum tuning examples 2010-12-06

    Note: This article is posted, just for the learning record. Original link 1, the use of leading and use_nl to set the table the query sequence to speed up queries, usually a small table to set the

  • oracle optimization 6 2011-03-21

    25 with the efficiency index Index is a conceptual part of the table, used to improve the efficiency of data retrieval. In fact, Oracle uses a complex self-balancing B-tree structure. Typically, the query data through the index faster than full table