Oracle Core Essential Internals for DBAs and Developers – Jonathan Lewis
很可能是未来十年,关于Oracle内部机制最好的书籍. 书评见这里
Read more »
2011关于Oracle的书单
[Oracle Core Essential Internals for DBAs and Developers] 未来十年，最好的书
去年大概8月份的时候, 我看完了Cost-Based Oracle Fundamentals，在SQL Tuning方面，我是藉由这书入门的，一本经典好书可以帮助提升在某一方面的层次。之后我想找Jonathan Lewis的一本更旧十年前的书看 – Practical Oracle8i™: Building Efficient Databases，当时还在豆瓣上找看过这本书的朋友，看看能不能把书借给我看或者卖给我。
Read more »
Transaction Tables Consistent Reads
There is an interesting case in this week. A query run over hours, it take millions of buffer gets. The query is to verify if there is any data in the past 3 month for a customer, which usually completes in a minute. The case can’t be reproduced in production clone DB. The same execution path return around 10 seconds, doing 3829 buffer gets. The execution plan is as expected as below, the index skip scan will return hundreds of rows, zero or several rows return after nested loop join. Read more »
Archive Listener Log
Time and time again, you may need to archive the listener log in the ORACLE_HOME mount point. If you simply rename or remove the log file, you may see the log file disappears in the directory, and the space is released. Although the file descriptor help by listener is mark as “deleted”, the listener will not generate a new file, the log is still flushed to the removed file. Read more »
Enqueue: Owners, Converters and Waiters
This is a case about enqueue from the book Oracle Core. The case demonstrates two feature of oracle enqueue:
1. Lock conversion
Lock conversion appears in the common foreign key problem, If supporting index is missing on a foreign key, and a session is try to update or delete on the parent table, it will lock the child table with mode 4. If the session already hold a lock on child table with mode 3, when it update or delete on the parent table, it’ll convert the lock to mode 5.
2. The priority of the converters and waiters
There are 3 queues per enqueue, owners/converters/waiters. Once the owner release the enqueue, which session will hold the lock first, the one in converters queue or the one in waiters queue? Read more »
False User Commits And User Rollbacks
If a session is doing change, such insert/update/delete, and find there are critical change between consistent read and current read, the session will rollback the statement and restart again; or fail if the transaction isolation is set to SERIALIZABLE. Critical change means the value of the columns either in the predicates or referenced in the before for each row trigger has changed. Tom Kyte demonstrate the restart feature in his book “Export Oracle Database Architecture”. Read more »
