Oracle Server Enterprise Edition Technical Forum Displayed below are the messages of the selected thread. Thread Status: Active From: Amir Hameed 09-Jun-00 16:06 Subject: delayed_logging_block_cleanouts parameter RDBMS Version: 8.1.x Operating System and Version: Sun Solaris 2.6 Error Number (if applicable): Product (i.e. SQL*Loader, Import, etc.): Product Version: 8.1.6 delayed_logging_block_cleanouts parameter Hi, I understand that this parameter has been obsolete as of Oracle 8.1.x. But does the "fast commit" mechanism is still the default for cleaning out data blocks ? If not, then how oracle does it now. thanks Amir -------------------------------------------------------------------------------- From: Oracle, Reem Munakash 12-Jun-00 20:09 Subject: Re : delayed_logging_block_cleanouts parameter It is always enabled (as if it were set to TRUE). Reem Munakash Enterprise Server Analyst -------------------------------------------------------------------------------- From: Amir Hameed 14-Jun-00 16:05 Subject: Re : delayed_logging_block_cleanouts parameter Hi, Thanks for the reply. I am trying to clear my concepts about this feature (delayed_logging_block_cleanouts) in version 8i as compared to prior-to-7.3. I would really appreciate if you took some time to help me understand the following. I will use "Note 39019.1" for reference. ** Prior to 7.3,the transaction that updated a data block would not do the clean up job itself.It was the next transaction, either a DML or a SELECT, visiting that block would do the cleanup when accessing that updated block. Now, I have following questions: - What are the contents of ITL ? - If the term "block cleanup" means that the "next visiting transaction" will update the commit timestamp of the "previously commited transaction", then would the "next visiting transaction" stamp it with its own visiting time or the "priviously visited transaction" time ? - does "cleanup" occurs when accessing the data block or only those rows that were modified in that data block by the previous transaction? - If the "cleanup" was done during a DML statement, the redo generated would piggyback on the redo generated by the transaction itself, is this true ? - If the "cleanup" occurs by the next visiting SELECT transaction, the only redo generated would be from the block cleanout, which would make the block dirty again, is this true ? ** As of 7.3, when a transaction commits, Oracle does a "fast commit" and cleans out the data blocks. This "fast commit" version does not generated any redo information, and only cleans up blocks that are in the data buffer cache or the long running . I have the following questions: - If Oracle only cleans up blocks in the buffer cache, then what happens to the blocks that were flushed to disks ? Would they be cleaned by the next visiting DML transaction ? - the "next visiting" SELECT transaction will not clean up any blocks, it is only the "next visiting" DML that will do the job, is this true ? - according to note 39019.1 "In long-running transactions, block ceanouts will not be performed during the transaction. If the transaction is not long running, block cleanout will be performed and the block cleanout is logged at the change of block." How does Oracle determine that a transaction is a long-running, and for these types of transactions, what mechanism is used to cleanout blocks ? thanks for your help regards Amir -------------------------------------------------------------------------------- From: Oracle, Reem Munakash 15-Jun-00 18:05 Subject: Re : Re : delayed_logging_block_cleanouts parameter Prior to 7.3: - What are the contents of ITL ? An ITL is a entry in the block header which lists information about the process making changes to the block including the rollback segment it is using. - If the term "block cleanup" means that the "next visiting transaction" will update the commit timestamp of the "previously commited transaction", then would the "next visiting transaction" stamp it with its own visiting time or the "previously visited transaction" time? The "next visiting transaction" will cleanup the block with the "previously committed transaction's" SCN. This assumes the SCN information is still in the rollback segment or can be reconstructed, if it can't it makes an assumption based on an internal algorithm. - does "cleanup" occurs when accessing the data block or only those rows that were modified in that data block by the previous transaction? When accessing the block. - If the "cleanup" was done during a DML statement, the redo generated would piggyback on the redo generated by the transaction itself, is this true? Prior to 7.3, whoever cleans out the block would also generate the redo. If the statement is a DML, it would piggyback the redo. - If the "cleanup" occurs by the next visiting SELECT transaction, the only redo generated would be from the block cleanout, which would make the block dirty again, is this true? Under the old method, the select would cleanout the block and thus it makes a change to the block in the buffer. This makes the block 'dirty' and needs to be written by DBWR. After 7.3: - If Oracle only cleans up blocks in the buffer cache, then what happens to the blocks that were flushed to disks ? Would they be cleaned by the next visiting DML transaction ? Yes, if they are no longer in the cache, they would go to the old cleanout method. I.e., next access will clean it out. - the "next visiting" SELECT transaction will not clean up any blocks, it is only the "next visiting" DML that will do the job, is this true ? This is where we get confused. 'Fast commit' is something which changes the block but doesn't generate redo. This is good because future access will not need to go to the rollback segments to know if a transaction has committed. Then we have redo which will record the change to the block. Once both are done the block is completely 'cleaned'. The select will do the 'fast commit'. A DML statement will do both. - according to note 39019.1 "In long-running transactions, block cleanouts will not be performed during the transaction. If the transaction is not long running, block cleanout will be performed and the block cleanout is logged at the change of block." How does Oracle determine that a transaction is a long-running, and for these types of transactions, what mechanism is used to cleanout blocks?? If the transaction accesses 10% of the buffer cache it will be considered a long running transaction. A long transaction will leave the blocks 'dirty' for future access to cleanup. Reem Munakash Enterprise Server Analyst