Introduction introduction
This document includes the most general tuning strategy. More specialized information about the part can be obtained by the link provided.
2. Shared pool and library cache Performance Tuning shared pool and library cache tuning
Oracle saves SQL statements, storage packages, object information, and many other items in a place called shared pool in SGA. This shared area is managed by a mature cache and heap manager. It has three basic questions to overcome:
1.) The unit of memory allocation is not a constant. The memory unit allocated from the pool may be from several bytes to thousands of bytes.
2.) When the user is completed, not all memory can be released because the target of the shared pool is to maximize information.
3.) There is no file with other conventional cache files to make a backup storage.
Only available information can be discarded from cache and recreate it when he is again needed.
The skill of sharing the pool tuning is:
- Brush (FLUSH) shared pool allows the small amount of memory to merge the memory. This can temporarily recover performance when the fragments of the shared pool are too much. Brush Shared Pool You can use statements: ALTER system flush shared_pool;
Note that this statement will cause temporary peaks of performance because the objects are reloaded. Therefore, it should be carried out in the case where the load load is not large.
- Ensure that online transaction (OLTP) applications use bind variables. This is not important for the decision support system (DSS).
- Make sure the hit rate of library cache> 95%
- Adding shared pools do not always solve the problem of excessive hit rate.
3. Buffer Cache Performance TUFFER CACHE Performance Tuning Database Coke Tuning
The database cache keeps the backup of the data block read from the disk.
Because the cache is usually limited by memory constraints, it is not possible to put it in the cache on the disk. When the cache is full, the later cache does not make Oracle to write data in the cache to the disk. Subsequent access to data written to the disk also causes the cache.
The skill of the database bundle tuning is as follows:
Avoid the following problems
- 'The most recently used (LRN) chain "(LRN) chain" (LRN) chain "is chain competition
- 'Average Write Queue' ("Average Write Queue")
- Excessive time spent waiting in 'Write waiting to wait "(" Write Complete Waits ")
- Excessive time spending waiting in 'buffer release waiting "" Free Buffer Waits ")
4. Latch Contention locks (latches) competition
The latching lock is a serialization mechanism that protects the low layer of the shared data structure in the SGA. The latch Latch is a class that can be obtained and released very quickly. The implementation of the latch lock is dependent on the operating system, especially if a process will wait for a lock, and how long.
There is a lock (latch) that requires tuning:
- Redo COPY / Allocation Latch Rewriting Log Copy / Associate Slices
- SHARED POLL LATCH sharing pool lasers
- Library Cache Latch Library Cache Plug
5. Redo Log Buffer Performance TUNING Tuning of Log Cuff
LGWR Write the rewrite item in the log buffer to the rewrite log file.
Once the LGWR copies these items into the rewrite log file, the user process can rewrite these items. Statistics "Redo Log Space Requests" reflects numbers that the user process waits for the time of the log buffer in the log buffer. Set some tips for rewriting logs:
- The value of "Redo Log Space Requests" should be close to 0.
- Set the size of the appropriate rewriting log, it is recommended to switch over a rewrite log every 15-30 minutes.
6. Query Performance Tuning Tuning Tuning
If the query is running slowly, consider these aspects:
- Do you want this query to run how fast and reason?
- Optimized mode OPTIMIZER_MODE setting?
- Is the index involved in querying are effective?
- Do you have other long-running queries in the database (large query)
For CBO (based on cost-based optimization) in case of CBO:
- Is there statistics on the table and index?
- Statistics are calculated or estimated?
For the performance adjustment of the query, two main debugging tools:
- TKPROF
- Autotrace
7. Rollback Segment Performance Tuning Tuning Tuning
Oracle Database provides read consistency on Select, INSERT, UPDATE, and DELETE operations on any database object. Rollback segments are used to save the cancel transactions generated by those who want to roll back actions or systems that need to generate one and a previous time.
The skill of setting the rollback segment is as follows:
- Recommendation least a rollback segment for every 4 transactions
- It is recommended to provide a big return segment for long-running big queries.
- The number of WRAP in V $ ROLLSTAT is close to 0. Otherwise, increase the extent size.
SELECT B.NAME, A.WRAPS from V $ ROLLSTAT A, V $ ROLLNAME B;
8. Temporary TableSpace Performance Tuning Temperature Temperature Temperature
Starting from RDBMS Release 7.3, Oracle introduces the concept of temporary table space. This table space is used to save temporary objects, such as sort segments. The sort segment uses the default storage parameters of its table space (DEFAULT Storage (NEXT) clause) as its own storage parameters.
The tuning skills of the temporary table space are as follows:
- If there is a lot of sort extent pool locks in a stable state, you should increase the size of the expansion block by modifying the NEXT value of the Default Storage clause of the temporary table space.
- If there are many sort extent pool locks competition and this waiting is caused by excessive concurrency sorting, you should increase the size of the sort_AREA_SIZE parameter so that more sort can be saved in memory. in.
- It is recommended to make the size of the expansion block and the sort_AREA_SIZE parameter.
Why is described in this example. Suppose your extent size = 500k and sort_area_size = 1mg.
Now if there is a sort of the disk, you have to do 2 500K extensions each time, which will result in a reduction in performance.
9. UTLBSTAT / UTLESTAT PERFORMANCE TUNING UTLBSTAT / UTLESTAT Tuning
Bstat / Estat is a bunch of SQL scripts stored in your $ ORACLE_HOME / RDBMS / Admin directory, which is very useful for the snapshot of database performance statistics for capture system ranges. UTLESTAT creates the second snapshot of these views and reports the difference between the two snapshots into the file 'report.txt'. BSTAT.SQL creates a range of tables and views under your SYS users, including the snapshot of the start of database performance statistics.
ESTAT.SQL creates a series of tables under your SYS user, including the snapshot of the database performance statistics and a file called 'Report.txt'.
Some techniques:
- Make sure you have set TIMED_STATSTICS to True (this only adds a very small overhead to the database operation).
- Make sure the database has started and runs for a while before running UTLBSTAT.
- Run UTBSTAT.SQL and UTLESTAT.SQL from SVRMGRL instead of SQL * PLUS.
- Make sure the database is not dropped when the script UTLBSTAT / ESTAT is running, otherwise the statistics generated are incorrect.
- When run UTLBSTAT / ESTAT for 1-3 hours, you will debug your database.
10. CheckPoint Performance Tuning Checkpoint Performance Tuning
CheckPoint is a database event that synchronizes the data blocks in the data file on the disk. There are two purposes of checkpoints:
(1) Consistency of establishing data (2) is faster in database recovery.
Tuning checkpoint process has the following skills:
- The CKPT process can significantly improve efficiency to reduce the time required for users to wait for a checkpoint.
- If the value of log_checkpoint_interval is larger than the size of the redo log, the checkpoint only occurs when logging logs from a group to another. This is exactly what we hope. This behavior has changed in Oracle 8i.
- When setting log_checkpoints_to_alert Set to True, check the checkpoint start and stop in the Alert log log. This is very helpful for you to determine if CheckPoint is being used in optimal frequencies.
- Ideally, checkpoint occurs only when logging only on logging.
11. Import Performance Tuning Data Load Performance Tuning
Nothing fixed information is about how to accelerate those who are slow to unbearable. Obviously IMPORT is cost to complete the time required, but something can shorten the time they spend.
