posted by Eric S. Emrick at 3/28/2006
6 comments
sys@10g:SQL> select status from dba_tablespaces
where tablespace_name = 'USERS';
STATUS
---------
ONLINE
sys@10g:SQL> select file_id from dba_data_files
where tablespace_name = 'USERS';
FILE_ID
----------
4
sys@10g:SQL> select sequence# from v$log where status = 'CURRENT';
SEQUENCE#
----------
100
sys@10g:SQL> select distinct group# from v$log;
GROUP#
----------
1
2
3
sys@10g:SQL> alter database datafile 4 offline;
Database altered.
sys@10g:SQL> select sequence#, archived from v$log;
SEQUENCE# ARC
---------- ---
100 NO
98 YES
99 YES
sys@10g:SQL> alter system switch logfile;
System altered.
sys@10g:SQL> /
System altered.
sys@10g:SQL> /
System altered.
sys@10g:SQL> /
System altered.
sys@10g:SQL> /
System altered.
sys@10g:SQL> select sequence#, status, archived from v$log;
SEQUENCE# STATUS ARC
---------- ---------------- ---
103 INACTIVE YES
104 INACTIVE YES
105 CURRENT NO
sys@10g:SQL> alter database datafile 4 online;
alter database datafile 4 online
*
ERROR at line 1:
ORA-01113: file 4 needs media recovery
ORA-01110: data file 4:
'/ora/data001/10g/10G/datafile/o1_mf_users_1pdkdtz5_.dbf'
sys@10g:SQL> recover datafile 4;
ORA-00279: change 12438697 generated at 03/28/2006 13:48:42
needed for thread 1
ORA-00289: suggestion : /ora/arch/10g/1_98_584918376.dbf
ORA-00280: change 12438697 for thread 1 is in sequence #98
Specify log: {=suggested filename AUTO CANCEL}
ORA-00279: change 12438704 generated at 03/28/2006 13:48:44
needed for thread 1
ORA-00289: suggestion : /ora/arch/10g/1_99_584918376.dbf
ORA-00280: change 12438704 for thread 1 is in sequence #99
ORA-00278: log file '/ora/arch/10g/1_98_584918376.dbf'
no longer needed for
this recovery
Specify log: {=suggested filename AUTO CANCEL}
ORA-00279: change 12438707 generated at 03/28/2006 13:48:50
needed for thread 1
ORA-00289: suggestion : /ora/arch/10g/1_100_584918376.dbf
ORA-00280: change 12438707 for thread 1 is in sequence #100
ORA-00278: log file '/ora/arch/10g/1_99_584918376.dbf'
no longer needed for this recovery
Specify log: {=suggested filename AUTO CANCEL}
Log applied.
Media recovery complete.
posted by Eric S. Emrick at 3/28/2006
16 comments
posted by Eric S. Emrick at 3/27/2006
4 comments
posted by Eric S. Emrick at 3/24/2006
1 comments
posted by Eric S. Emrick at 3/21/2006
2 comments
posted by Eric S. Emrick at 3/15/2006
4 comments
This is a short follow-up post to my Hotsos 2006: Day 3 post, thus the extremely creative name for this post :) In Tom Kyte’s presentation “Performance Impact of the LOG ERRORS Clause in 10gR2”, Tom’s testing revealed that a single statement multi-row update using the LOG ERRORS clause generates nearly 2x the redo when compared to other methods of updating the same data: row-by-row and array processing via anonymous PL/SQL blocks. As I stated in my previous post, I wanted to take a look at this to understand why 2x redo is generated to accomplish the same task.
I was able to reproduce Tom’s results using the examples taken directly from the presentation. The additional redo owed to the multi-row update statement with the LOG ERRORS clause results from the row locking “approach” taken by Oracle during the execution of the statement. In the row-by-row processing the rows were locked ("to lock" effectively means to update the lock byte in the row header) and changed in the same redo record. However, in the aforementioned multi-row update using the LOG ERRORS clause each row affected is given two redo records to accommodate the change. The first redo record is for locking the row and the second redo record is used to affect the column data and column length changes. A dump of the log file showed the following pattern for the multi-row update using the LOG ERRORS clause:
1. Change the lock byte of row header for row 0 to the index value for the ITL entry associated with the current transaction.
2. Change the column data and column length attributes to reflect the updated column data and width for row 0.
3. Change the lock byte of row header for row 1 to the index value for the ITL entry associated with the current transaction.
4. Change the column data and column length attributes to reflect the updated column data and width for row 1.
.
.
5. Change the lock byte of row header for row N to the index value for the ITL entry associated with the current transaction.
N. Change the column data and column length attributes to reflect the updated column data and width for row N.
A dump of the log file for the row-by-row update yielded the following redo pattern, "lock the row AND change the row":
1. Change the lock byte information, column data and column length attributes in a single redo record for row 0.
2. Change the lock byte information, column data and column length attributes in a single redo record for row 1.
.
.
N. Change the lock byte information, column data and column length attributes in a single redo record for row N.
What is rather interesting is that this "lock THEN change" method of “acquiring” locks is not necessarily specific to multi-row updates with the LOG ERRORS clause. I tested multi-row updates without the LOG ERRORS clause and the same redo pattern emerged; lock the row then change the row.
Moreover, suffixing the update statement in the row-by-row PL/SQL block with the LOG ERRORS clause also produced the, “lock the row THEN change the row” redo record sequence. It appears that Oracle’s code path is adjusted necessarily by the LOG ERRORS clause to affect this pattern for update statements, irrespective of the number of rows the update statement modifies. However, this code path is apparently available to other multi-row updates not using the LOG ERRORS clause.
So, how does this account for 2x redo? It has been my experience that on average redo records are around 250-300 bytes for small changes. Much of this space is owed to the administrative data such as SCNs, operation codes, flags etc. Even a small single byte change can generate a redo record of 200+ bytes. In each and every test case I ran the redo record size for the single lock byte update was 208 bytes. If each row updated via an update with the LOG ERRORS clause created two redo records, one for the lock byte update and another for the small data change, this explains why nearly 2x redo is generated.
One point of clarification, the notion that Oracle locks a row before it changes a row is very fundamental. Indeed, our applications rely on it! However, it does appear that Oracle catalogues the changes owed to acquiring the row locks differently based on the task at hand.
posted by Eric S. Emrick at 3/14/2006
3 comments
posted by Eric S. Emrick at 3/09/2006
0 comments
He also mentioned a couple of open source products that can be used for micro-benchmarking: filebench and iozone.
One last comment - which requires further investigation. He said that anyone owning the book Oracle Wait Interface: A Practical Guide to Performance Diagnostics and Tuning should rip out page 134. I will wait until I get back to the office before I read my colleague Michael Eubanks' copy and rip said page out :-)
Then it was on to Doug Burns' "Tuning and Tracing Parallel Execution" presentation. First, off this guy can flat out present (and with terrific humor). I thought the presentation was very good. Some of the notable points were the following.
Why do so few sites implement the Parallel Query option? Well, I didn't know that so few sites were using it, but I will certainly take his word. He mentioned a few reasons:
He advocated, with a slight nudge from Tom Kyte, the use of the parallel_adaptive_multi_user parameter to adapt the PQ slave use to the current workload on the system. In this manner, we can theoretically take full advantage of Parallel Query while reducing the risk of CPU starvation. If you know me, you know that I love a diagram that can say in one slide what might take 20 bullet items to "spell out". Doug had one particular slide that showed the interaction between parallel query slave processes and the parallel query coordinator processes during a sort operation. It revealed how parallel query slaves can assume the role of both a consumer and a producer at different points within the execution of a single statement. That added a new wrinkle to my gray matter.
He also described some of the challenges of tracing PQ processes in pre-10g Oracle installations, i.e. one trace file per slave and coordinator. However, in 10g this is remedied via the use of trcsess as it generates a consolidated trace file for the query coordinator and the slave processes. I also thought his review of the parallel query wait events and related dynamic performance views topped off a very good presentation. Great job Doug! I look forward to your presentation on Day 3.
I thought Tanel Poder did a really good job in presenting his "End-to-end Performance Diagnosis in Oracle" presentation with relevance and clarity. To summarize his presentation, the highest probability in diagnosing performance issues is usually a correlative of the instrumentation at each layer. The less instrumentation that exists at each "layer" of the application the more time it will take to complete the diagnostic tasks at hand. The number of cycles of "why" -> "where" -> "why" -> "where" -> .... -> "root cause" will typically be greater as the instrumentation at each layer diminishes. The converse is likewise true. Of course, the cost of instrumentation to performance must be weighed against any apparent overhead said instrumentation incurs.
I rounded off the day with back-to-back presentations from Jonathan Lewis and Mogens Norgaard. Jonathan spoke on the "V$ and X$" performance views. With erudition that ostensibly only Jonathan can provide, the V$ and X$ views were exposed for what they truly are, views which can be immensely useful and recreated in a much for database-friendly manner. Unfortunately, this talk only lasted an hour. However, I am privileged to be able to attend the training day with Jonathan on Thursday. So, I am sure any gaps not fully appreciated in this one-hour presentation will be realized in this training session. Jonathan's understanding of Oracle internals is truly amazing. It is obvious he has invested much energy to decode some of the more mysterious aspects of Oracle, such as the v$ and x$ views; in an attempt to deliver to the average DBA quick access paths to otherwise potentially prohibiting queries against these prized structures. Just when you think you know a thing or two about Oracle internals, a Jonathan Lewis presentation quickly reminds you of how little you really know. As Socrates said, "True knowledge exists in knowing that you know nothing." When Jonathan Lewis or Tom Kyte present I feel my seat getting larger and larger beneath me.
Mogens delivered a fantastically witty and delightful presentation to round off the day. I set down my pen and closed my notebook to simply enjoy the offering. I have found Mogens to be one of, if not, the best person to deliver a high level technical discussion seasoned with relevant and primarily non-gratuitous humor. If the majority of the world understood databases he could easily give a stand-up routine each and every night at any venue in the world. He is that good.
posted by Eric S. Emrick at 3/07/2006
2 comments
In a nutshell, Mr. Lane contends that the innovations to come, and indeed already present in many open source products, will dictate the way corporate America invests in its enterprise software. He stated, "All the things we desire in our enterprise software are already understood by the individual user community." Essentially, if software is to survive in the future it needs to meet the seven conditions listed above. For example, many people really value their wireless phones as they can serve as a planner, camera, web browser, text messenger, etc. And to quote Mr. Lane, "When was the last time you bragged on your new enterprise software?"
James Morle delivered a very good presentation titled "Sane San." I think a couple of really good ideas to take from his presentation are:
I do have a couple of thoughts on Julian Dyke's presentation. He delivers technical topics with great humor. I really appreciate that in a technical presentation. Moreover, his animations for buffer cache management in RAC and non-RAC environment were awesome! It is really a talent when you can present technical ideas in such a basic manner, using animations, that it renders the notion nearly trivial. Animations are much easier to remember than an endless array of bulleted items. While RAC buffer cache management is not nearly as profound as, say, astrophysics I can't help but think about Richard Feyman's famous quote given to one of the few freshman classes he taught. He was asked by a professor attending one of his lectures to comment on a particular phenomenon. Feyman obliged and stated that he would present a freshman lecture on it for the next class. When the day arrived to give the lecture Feyman opened with, "I couldn't do it. I couldn't reduce it to a freshman level. That means we really don't understand it." I love that quote.
Lastly, I attended Rijay Shamsudeen's Redo Internals and Tuning by Redo Reduction presentation. Of course, this topic is near and dear to me so I was very excited about this presentation. He delivered a very clear and concise expose into redo internals that was both elucidating and informative. I learned several new redo internal tidbits and look forward to reading the accompanying white paper.
All in all, it was a very good first day at Hotsos 2006.
posted by Eric S. Emrick at 3/06/2006
1 comments
posted by Eric S. Emrick at 3/06/2006
0 comments
This is a bit of a follow-on post to my The Pleasure of Finding Oracle Things Out post in which I spoke of some Oracle internal related mechanics relating to user-managed hot backups. In this post I want to focus on RMAN and how, subsequent to a restore of a data file from an RMAN formatted backup set or an image copy to disk, Oracle knows what needs to be recovered. Remember, with user-managed backups the checkpoint SCN is frozen after the checkpoint issued by the BEGIN BACKUP completes. This tells Oracle where to start recovery when the file is restored. We also know that the END BACKUP command creates a redo record that corresponds to this backup – tied together via the checkpoint SCN. During recover Oracle is privileged to the operations that define the backup-necessary redo. That is, the BEGIN/END BACKUP commands act as recovery bookends to preserve consistency.
However, RMAN is a totally different beast. We know that RMAN permits us to take hot backups and restore either image copies or backup set formatted files for recovery. This is well documented and I am sure you have reaped the benefits of this RMAN feature in your DBA adventures. But how does it work? (I love that question)
Yes, we read of RMAN handling the fractured block dilemma inherent in the user-managed backups by re-reading changed blocks to ensure block consistency. And, you have probably read that it reads the file header block first and only backs up blocks that have been previously modified (let’s stick to level 0 backups here). Oracle also issues a checkpoint on the applicable file(s) before backing up said file(s) for either a backup set or image copy. Okay, but what can we say of the bookends? When we restore a file from a backup set or an image copy what defines the bookends for the backup-necessary redo? Backup-necessary redo can be defined as the redo that is required to make a fuzzy/inconsistent backup file a non-fuzzy/consistent file. Of course, we often continue past the backup-necessary redo to get to a further point in Oracle time.
If you stop for a moment and really think about it, defining the bookends for RMAN is not a very easy proposition. We know it works. We use it. We like it. How often do we question it?
Let’s create a very rudimentary example to demonstrate the nature of the RMAN bookends. In this example we will backup a file (level 0) comprised of six blocks (all blocks previously used therefore subject to our level 0 backup) to a backup set – to disk or tape it does not matter. The figure below depicts the states of the blocks in our sample data file at different points in time. Block 0 is the header block and blocks 1-5 are for data. Listed under the block number is the checkpoint SCN for the header block and the block SCN for the data blocks. For example, at time=0 the state of the data file is a checkpoint completed at SCN 120 and blocks 2, 4 and 5 have been modified since the last checkpoint. Blocks 1 and block 3 were modified prior to the checkpoint at SCN 120. What would happen if we started an RMAN backup at time=0? Oracle would issue a checkpoint on the file and start reading the blocks in order from 0-5. For simplicity assume we have only a single backup process reading the data file during the backup. Also assume the backup finishes reading the data file at time=4. What would the backup piece “contain” such that a subsequent restore would be primed for recovery?
First we need to know which blocks are backed up, and when, before we can determine what will be written to the backup set. Let’s use the following diagram to depict the state of the backup set if the backup is started at time=0.
At time=0 RMAN issues a checkpoint against the data file and reads the header block into memory. Assume the header block is buffered in the Large Pool (Oracle could just as easily buffer the block in the memory of the shadow process backing up the data file). I found a presentation on the Web authored by a member of the Oak Table that asserts the header block is read first and written last. Indeed, we will see this needs to be the case to satisfy both bookends of the backup-necessary redo.
As we can see at time=1 we have only backed up block 1. At time=2 RMAN backs up block=2. At time=3 it backs up blocks 3-4. Lastly, at time=5 it backs up block=5. Remember, before RMAN started it issued a checkpoint against the file and preserved the SCN from the completion of the checkpoint in the buffered image of the header block – the header block checkpoint SCN in this case was 125 as the checkpoint completed at time=1. Notice the state of the blocks written to the backup set. They vary in Oracle time as they contain different SCN values. The fact that block=4 has an SCN that is greater than the checkpoint SCN recorded in the buffered image of the header makes the backup of this file inconsistent or fuzzy (file header status 0x40). How does Oracle resolve this? Well, Oracle reads the SCNs of each block during the course of the backup. If a single block backed up has an SCN greater than the checkpoint SCN all intermediate SCNs need to be accounted for during recovery. For instance, in our example block=1 was changed to SCN=126 while the backup was being performed. Moreover, it is less than the highest block SCN encountered (block=4 has SCN=127). However, the backup set has the state of this block at SCN=123. If Oracle knew the highest SCN encountered during the course of the backup it can account for any recover needed between the checkpoint SCN and this highest SCN.
As it turns out, Oracle reserves a section of the file header block of each file for just such an occurrence. This is called the Absolute Fuzzy SCN and represents the SCN required for recovery to make this a consistent file. Our bookends are then defined as the checkpoint SCN and the Absolute Fuzzy SCN. At a minimum, Oracle must recover from the checkpoint SCN through the Absolute Fuzzy SCN for consistency. If Oracle did not detect any SCNs higher than the checkpoint SCN during the backup then the backup would be considered consistent (file header status 0x0) and the Absolute Fuzzy SCN would remain at 0x0 - obviating the need for any backup-necessary redo to be applied. As you can see, this is the reason Oracle waits until all data blocks in the file have been read and written before it writes the header to the backup set. This permits the proper settings for the bookends. You can find the Absolute Fuzzy SCN at the bottom of a file header dump.
.
.
Absolute fuzzy scn: 0x0000.004da811
Recovery fuzzy scn: 0x0000.00000000 01/01/1988 00:00:00
Terminal Recovery Stamp scn: 0x0000.00000000 01/01/1988 00:00:00
What about image copies?
RMAN does not really “behave” any differently for an image copy. It stills performs a checkpoint and still notes the Absolute Fuzzy SCN if applicable. What is different however, is that an image is, well, just that, an image. Therefore, the position of the header block is in its natural position at the logical front of the backed up data – not the end as in the backup set case. This is yet another reason, if not the primary reason, why Oracle does not permit image copies to be backed up directly to tape – it could not set the Absolute Fuzzy SCN in the header block because the header block would have already been written to a sequential access medium.
posted by Eric S. Emrick at 3/03/2006
13 comments
The views expressed are my own and not necessarily those of my employing company and its affiliates. The views and opinions expressed by visitors to this blog are theirs and do not necessarily reflect mine. I have worked as an Oracle DBA and systems architect for 15 years.
