Very good case described. Igor Neyman, OCP DBA [EMAIL PROTECTED]
----- Original Message ----- To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Wednesday, January 22, 2003 2:16 PM > Take the case of an insert (we'll call tx1), where space allocation is > required. As you insert records, the table allocated additional extents > (updating fet$ (free extent table) and uet$ (used extent table) in the data > dictionary). These updates to the data dictionary are implicitly committed, > even if you issue an explicit rollback for the insert statement. Imagine if > the dd changes are not immediately committed. Let's say another tx (we'll > call tx2) needs to allocate an extent in the same datafile. If fet$ > contains only a single row for the file requested, then tx1 will have an > exclusive lock on the row. tx2 needs to also lock the row exclusively in > order to update it. Thus, tx2 would wait until tx1 has completed and > released the lock. In the meantime, any transaction that needs to allocate a > new extent in that file will have to wait...and wait...and wait. > > A different strategy is to commit the changes to fet$ and uet$ immediately. > Then the next tx can access the row and grab space. While this could result > in an overallocation of space if the tx is rolled back, it does not block > other txs. If space was allocated to an object, and the tx failed, there is > a strong probability that this space will be used at some point in the > future. > > It seems that the tradeoff here is that the access to the data dictionary is > kept to a minimum duration at the expense of periodic space wastage > (initially). > > As for the other data dictionary tables, it may be a case of read > consistency conflicts. If a long running transaction needs to access a table > definition, but a previous transaction has updated the table definition, but > not committed, which table definition do you use? There are some issues with > definitions not being the same at the start of a transaction and at a later > point. As I recall, Oracle terminates the transaction if object definitions > change while the transaction is running. > > All in all, it makes sense (at least to me) that changes to the data > dictionary are immediately committed. Otherwise, the performance and > integrity of the system would be comprimised. > > Dan Fink > > -----Original Message----- > Sent: Wednesday, January 22, 2003 10:01 AM > To: Multiple recipients of list ORACLE-L > > > > That raises another doubt. For an simple insert statement, could also > update the UET$ or FET$ tables? So, if the purpose was to preserve all > changes to the data dictionary, What's different between OBJ$, COL$ and > these space management tables? > > Thanks > Raj > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Fink, Dan > INET: [EMAIL PROTECTED] > > Fat City Network Services -- 858-538-5051 http://www.fatcity.com > San Diego, California -- Mailing list and web hosting services > --------------------------------------------------------------------- > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Igor Neyman INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
