How to adjust Oracle Performance

Oracle performance adjustment:

1. data access mechanisms, such as table full scan, index range scan, index full scan, and fast index scan. These contents allow me to understand how SQL statements interact with Oracle, how data is returned to the interface from a physical data file is required to adjust SQL statements.

2. The connection modes between tables are nested loog, hash join, and merge sort. To understand the operation mechanisms of each connection mode, it is necessary to adjust the SQL statements for multi-table queries.

3. index structure, understanding the data distribution of the index B tree, the relationship between the data distribution and the physical table, the impact of various DML on the index space, and the bitmap index in Oracle, function Index and reverse Index

4. oracle's spatial storage mechanism, differences between local tablespace management and dictionary management, ASSM features, What are the relationships between freelist, pctfree, pctused, and HWM in the table, their impact on DML Operation Performance

5. oracle memory structure, SGA area, including db_cache, shared_pool, larger_pool, java_pool, PGA area, setting, adjusting, and monitoring the internal size of each area, db_cache learns the LRU data buffer mechanism, divides buffer blocks of different sizes, and uses the keep and recycle pools. shared_pool includes its internal structure, Bucket division, variable binding, and SQL query plan, execution tree, hard parsing, soft parsing, etc. PGA adjustment includes size setting, PGA usage of connection sessions, and disk sorting.

6. the adjustment of various wait events (OWI), disk IO, mainly db file sequential read, db file scattered read, direct path read, etc, the adjustment of these events depends on the length of the waiting time, because the waiting time cannot disappear. According to the P1, P2, and P3 parameters, you can obtain the specific waiting database objects and combine the SQL statements, use SQL _TRACE or EXPLAIN for specific adjustments

In terms of locking (enqueue), it mainly determines why the session is blocked, what statements are being executed by the blocked session, what the locked object is, and what the lock type is, frequent enqueue waits for the design of the estimation system.

Latch, which is about Memory Locking. It is tricky to adjust. You need to understand the access, modification, and scanning mechanisms of the memory, such as creating a large db_cache area, in 9I, the mechanism is roughly as follows, there are a certain number of hash latch in the cache. Each hash latch is attached to several hash buckets, and each hash bucket is connected to a hash chain. The content of the hash chain is the data block cache header linked up, such a hash latch corresponds to multiple hash buckets, and each hash bucket corresponds to a hash chain. When Oracle wants to access a certain block, it needs to know which data file the block belongs, he is the first few blocks. Based on the information, he performs Hash operations on the blocks to obtain the corresponding hash bucket memory address, then locates the bucket in the memory, and then scans the following hash chain, if it is found, it will be used directly. If it cannot be found, it will link a new block to the hash chain. The premise of this process is that the process must obtain the latch that controls the bucket, if there are multiple processes, the data blocks they access are exactly hashed to the same Latch jurisdiction, and there will be competition for Latch under this jurisdiction. The latch wait is the cache buffer chains.

For another example, for the buffer busy wait event, session A reads data blocks C, and C is not in the memory. In this case, session B also reads data blocks C, will the physical IO of the C data block be generated twice? A: NO. session A will pre-set the memory space in the memory to accommodate the C data block and temporarily lock it for subsequent disk IO, session B waits for the end of the lock until Block C is read into the memory. At this time, a BUFFER BUSY wait event is recorded, of course, there will be no obstacles to access Block C in the future, because it has been cached.

Other major latch Waits include library cache and shared pool. for this part of learning, I recommend the Oracle8i Internal Service for Waits, Latches, Locks and Memory compiled by Steve Adams, the masterpiece of the world's top experts is a pity. If you are not good at English, please bring your own Kingsoft Overlord

7. SQL _TRACE/TKPROF: this is an excellent tool for adjustment. In particular, the adjustment is not a self-written program. It can track any session that you think can be used, capture the SQL statements of the Session, and query the plan, CPU usage, time consumption, consistent reads, and specific wait events are very valuable. If you want to make full use of this information, you 'd better understand the original trace file, instead of using tkprof for translation.

8. statspack is a good database adjustment tool. It collects the system information of the database, generates snapshots, and generates reports by comparing the differences between the two snapshots to reflect the system operation during this period, including disk IO, CPU, memory, lock, wait Event, 10 SQL statements with the maximum buffer gets, SQL statements with the most execution times, tablespace, and data file IO distribution, if you cannot find out the problem, I will use it. It is also a good system information collection tool.

9. The operating mechanism of UNIX virtual paging and some messy OS performance adjustment tools are not very good at learning. I don't know why.