oracle Log Buffer内部机制以及常见等待事件
重做产生于PGA，再由各个session的服务器进程将重做记录拷贝到SGA 的log buffer中，再由LGWR进程刷新到redo log文件中
重做产生于PGA，再由各个session的服务器进程将重做记录拷贝到SGA 的log buffer中，再由LGWR进程刷新到redo log文件中
涉及到的三个latch：
Redo copy latch
Redo allocation latch
Redo writing latch
Redo copy latch
redo copy latch的数量可以有多个，可以通过_log_simultaneous_copies参数来设定，，缺省值是两倍CPU的个数，
此latch保护日志缓存中的信息，主要用于从PGA拷贝重做到log buffer中，但是不允许对重做记录一边进行修改，
一边将重做记录写入磁盘。所以LGWR工作的时候，必须等待持有redo copy latch 的前台进程将要刷新的重做记录拷贝完毕
这里也就是说，LGWR从redo log buffer写到文件的时候，是无法写正在copy的redo log buffer，但是可以写不持有
Redo copy latch的log buffer。
Redo allocation latch
前台进程和LGWR都将持有该latch
Oracle把向log buffer中写缓存这样一个操作分做两个步骤：
1． 是先在log buffer中分配一块空间
2． 是向这块空间中实际的写入重做信息
当前台进行分配空间的时候，必须先持有该latch，但是该阶段该latch只有一个，所以前台进程这个时候会相互阻塞。
当LGWR进行刷新缓存时，持有该latch，当确定刷新的范围后，那么就会写到磁盘，写磁盘前会释放该latch
Redo writing latch
当日志缓存没空间分配时，前台进程必须通知LGWR刷新日志缓存，只有第一个得到此latch的进程通知LGWR，
用来阻止其他进程通知LGWR，通知后，马上释放该latch，不会一直占用。LGWR得到通知，持有该latch，
写入磁盘文件前释放该latch。
重做产生的流程：
1.先在PGA中生成重做记录,并计算出重做记录大小
2.由服务器进程申请redo copy latch如果成功的话继续
3.再去申请redo allocation,成功分配空间后
4.释放redo allocation
5.开始把PGA中的重做记录写往log buffer
6.记录写完后,释放redo copy latch
_log_io_size:如果使用的log buffer大小等于或者大于该值，那么就触发LGWR写磁盘，缺省大小为log buffer的1/3，上限值为1M
redo buffer等待事件：
LOG BUFFER SPACE：
redo copy的速度快于LGWR，造成free log buffer总是不够用
原因：
LOG BUFFER太小，总没有空间copy
LOG BUFFER太大，但是录入的太频繁
提高LGWR写的效率，以及磁盘的IO性能
log file parallel write
此等待事件是LGWR将log buffer写到在线日志文件，重用log buffer。
解决方法：
减少日志的生成(NOLOGGING)
减少日志组成员数
避免在备份模式下做大量的事务
尽量用最小的辅助日志模式(Supplemental Logging),如在LOGMINER下分析日志.
日志组成员分布在不同的物理磁盘上
不要将在线日志存放在RAID5上
尽量使用裸设备
Log file sync
事物提交时，一个进程创建一个重做记录，LGWR从log buffer写到磁盘，当再次发出commit，前面的LGWR还没有完成，会造成log file sync等待
原因：
过度频繁的提交
CPU使用过度
bug
如果log file sync接近log file parallel write，那么冲突可能是日志IO问题，如果远大于，则IO不是主要问题
Log file switch(checkpoint incomplete)
当日志切换的时候，要覆盖一个检查点未完成的的日志造成的等待
解决办法：
IO有严重问题，增加DBWR的效率，提高磁盘IO性能
增大日志文件
增加日志组
Log file switch (archiving needed)
如果是归档模式存在此等待，那就是归档的速度慢，可以调整归档日志所在磁盘的性能，调整log_archive_max_processes。
log file sequential read
当redo进行归档时，会顺序读取redo日志，会造成此等待
Hot AI Tools
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Undress AI Tool
Undress images for free
Clothoff.io
AI clothes remover
Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!
Hot Article
Hot Tools
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Hot Topics
1390
52
When might a full table scan be faster than using an index in MySQL?
Apr 09, 2025 am 12:05 AM
Full table scanning may be faster in MySQL than using indexes. Specific cases include: 1) the data volume is small; 2) when the query returns a large amount of data; 3) when the index column is not highly selective; 4) when the complex query. By analyzing query plans, optimizing indexes, avoiding over-index and regularly maintaining tables, you can make the best choices in practical applications.
Explain InnoDB Full-Text Search capabilities.
Apr 02, 2025 pm 06:09 PM
InnoDB's full-text search capabilities are very powerful, which can significantly improve database query efficiency and ability to process large amounts of text data. 1) InnoDB implements full-text search through inverted indexing, supporting basic and advanced search queries. 2) Use MATCH and AGAINST keywords to search, support Boolean mode and phrase search. 3) Optimization methods include using word segmentation technology, periodic rebuilding of indexes and adjusting cache size to improve performance and accuracy.
Can I install mysql on Windows 7
Apr 08, 2025 pm 03:21 PM
Yes, MySQL can be installed on Windows 7, and although Microsoft has stopped supporting Windows 7, MySQL is still compatible with it. However, the following points should be noted during the installation process: Download the MySQL installer for Windows. Select the appropriate version of MySQL (community or enterprise). Select the appropriate installation directory and character set during the installation process. Set the root user password and keep it properly. Connect to the database for testing. Note the compatibility and security issues on Windows 7, and it is recommended to upgrade to a supported operating system.
Difference between clustered index and non-clustered index (secondary index) in InnoDB.
Apr 02, 2025 pm 06:25 PM
The difference between clustered index and non-clustered index is: 1. Clustered index stores data rows in the index structure, which is suitable for querying by primary key and range. 2. The non-clustered index stores index key values and pointers to data rows, and is suitable for non-primary key column queries.
MySQL: Simple Concepts for Easy Learning
Apr 10, 2025 am 09:29 AM
MySQL is an open source relational database management system. 1) Create database and tables: Use the CREATEDATABASE and CREATETABLE commands. 2) Basic operations: INSERT, UPDATE, DELETE and SELECT. 3) Advanced operations: JOIN, subquery and transaction processing. 4) Debugging skills: Check syntax, data type and permissions. 5) Optimization suggestions: Use indexes, avoid SELECT* and use transactions.
The relationship between mysql user and database
Apr 08, 2025 pm 07:15 PM
In MySQL database, the relationship between the user and the database is defined by permissions and tables. The user has a username and password to access the database. Permissions are granted through the GRANT command, while the table is created by the CREATE TABLE command. To establish a relationship between a user and a database, you need to create a database, create a user, and then grant permissions.
Explain different types of MySQL indexes (B-Tree, Hash, Full-text, Spatial).
Apr 02, 2025 pm 07:05 PM
MySQL supports four index types: B-Tree, Hash, Full-text, and Spatial. 1.B-Tree index is suitable for equal value search, range query and sorting. 2. Hash index is suitable for equal value searches, but does not support range query and sorting. 3. Full-text index is used for full-text search and is suitable for processing large amounts of text data. 4. Spatial index is used for geospatial data query and is suitable for GIS applications.
Can mysql and mariadb coexist
Apr 08, 2025 pm 02:27 PM
MySQL and MariaDB can coexist, but need to be configured with caution. The key is to allocate different port numbers and data directories to each database, and adjust parameters such as memory allocation and cache size. Connection pooling, application configuration, and version differences also need to be considered and need to be carefully tested and planned to avoid pitfalls. Running two databases simultaneously can cause performance problems in situations where resources are limited.
