The Eric S. Emrick Blog

Low Cardinality != Bitmap Index

2009-03-06T22:33:00.002-05:00

Sorry but this post is a bit of a rant. I was called into a performance issue yesterday. The users were complaining of slow performance. I issued extended SQL tracing on the session and found the SQL statement was a simple SINGLE ROW insert statement using bind variables. No triggers on the table.

What I found were hundreds of thousands of db file sequential read wait events to insert a single row. I checked out the data dictionary for any supporting indexes and found 10 indexes on the table, 4 of which were bitmap indexes. Fortunately, this was a 10g database, so the object number associated with the sequential reads were easily plucked using a simple AWK script.

wait #22: nam='db file sequential read' ela= 377 file#=17 block#=20988904 blocks=1 obj#=725386 tim=2691112678912

I found that nearly 99.99% of these wait events were owed to this object, a bitmap index.This application is not your standard OLTP as the underlying table gets loaded with thousands of rows each day with SINGLE ROW inserts. The dreaded concurrency and deadlocking did not come into play, well, because the load process is single threaded. However, all queries against this table need to perform very quickly. So, in that sense it has an OLTP face. Here is the rub. First, I asked if these indexes (in particular the bitmap indexes) could be dropped prior to their "load" and recreated after. The answer I received was essentially, "no, that is the way the application works." I then asked them to tell me why this index was a bitmap index. The developer stated the rationale was the fact that the data was uniformly distributed over 6 distinct values. I suppose that seems reasonable. I then asked the developer if this column was used in join conditions for other queries. The answer was a resounding NO.

Not to my surprise the index built as a standard b*tree index was just as efficient and lacked the horrific index maintenance overhead associated with SINGLE ROW inserts. The only reason the index was defined as a bitmap index was its cardinality and nothing more. I had them drop the index. The load that was taking 20+ hours to complete finished in under a minute. The lesson here is: Know your data, know your code and then evaluate the use of bitmap indexes to support your table access. The simple fact of low cardinality does not alone justify the use of a bitmap index. As a matter of fact, this bitmap index was so chubby that after it was re-created post load, it had been reduced in size by 99%. I suppose that is another point: Bitmap indexes aren't necessarily space savers either if used in an improper context.

BTW, the hundreds of thousands of blocks reads were not what you might have thought: locks against rows with the same bitmap as the inserted value for the bitmap column. Oracle was ranging over the index nonsensically looking for the proper place to dump the row. As the hundreds of thousands of sequential reads rolled by not a single TM lock was obtained and ZERO db block changes had accumulated. It was only when the row finally inserted that a few blocks changes showed up. This is just another example of a peculiarity with bitmap indexes that can crop up if used unlawfully.

Database Continuity

2009-03-04T16:03:00.005-05:00

Ever just have a burning desire to do something that never seems to go away? For me, that desire has been to write a book; more specifically an Oracle technology-based book. (Okay, maybe a novel later on in life...) I thoroughly enjoy researching Oracle technology and finding solutions to puzzling questions. I am, however, pragmatic and seek to understand that which I can put to good use in the future.

I was recently discussing this desire with a colleague. I told him that I felt there was a need for a really good backup and recovery book. Actually, I expounded a bit and said that there is a need for a good database continuity book. It just feels that backup and recovery is an overused phrase for a dramatically underutilized and uncultivated set of skills. After all, how frequently are we involved in backup and recovery exercises? I would guess that backup and recovery activities comprise less than 5% of the time spent by a DBA during the course of any given year. That would be less than 100 hours in a full work year. I suspect it could be much less for some.

Isn't spending little or no time on backup and recovery a good thing? That does imply our systems are resilient and few faults surface that require us to exercise our recovery plan. And, in the age of RMAN we simply don't have to worry about the nuances of recovery, right? RMAN knows exactly what is needed for restoration, and all the DBA needs to do is execute a few commands to restore and recover the database. What technology has afforded us with regard to ease of backup configurations and redundant infrastructure, it has equally reduced our ability to confidently take control when up against a critical database recovery scenario. In short, we become complacent and our knowledge of backup and recovery diminishes over time. How confident are we that our backup strategy meets the recovery point (RPO) and recovery time (RTO) objective of our business?

I digress. Let’s get back to the conversation with my colleague and this notion of database continuity. I defined for him database continuity as follows: Database continuity is a superset of knowledge, processes and tools that fulfill the data protection requirements of an organization. By consequence, backup and recovery become processes in the continuity methodology. Database continuity is a broadened perspective of Oracle database recovery and is intended to include: disaster recovery, standby databases, archive log management, user-managed backups, RMAN, RPO and RTO, etc. Each of these aspects of database continuity requires the DBA to have a firm understanding of Oracle database recovery. If we truly understand recovery these different continuity dimensions converge rapidly. You can plug in your knowledge of recovery to assist with any dimension. So, while the notion of database continuity has greater breadth at face value, it can be reduced to recovery mechanics, constructs and objectives.

That being said, I have many ideas about a book on Oracle database continuity. However, I want to hear from you. What do you find lacking in the backup and recovery books on the market? Maybe one text speaks to an aspect for which you wish the author had given more detail. Or, maybe there is an overindulgence of certain topics that you wish had been left out. What material would help you retain and reuse your recovery knowledge? I am not out to write a book on RMAN or Data Guard; thousands of pages have already been devoted to the treatment of these technologies. I view guides on such topics as utilities to affect my recovery objectives and mobilize my recovery knowledge.

RMAN, RAC, ASM, FRA and Archive Logs

2008-02-17T18:26:00.006-05:00

The topic, as the title suggests, concerns RMAN, RAC, ASM and archive logs. This post is rather different than my prior posts, in that, I want to open up a dialogue concerning the subject matter. So, I’ll start the thread by posing a question: Are any of you that run RAC in your production environments backing up your archive logs to an FRA that resides in an ASM disk group (and of course backing up the archive logs to tape from the FRA)? Managing your free space within your FRA is paramount as are judicious backups of the FRA (actually these really go hand in hand). However, I am very interested in your experience. Have you come across and “gotchas”, bad experiences, positive experiences, more robust alternatives, extended solutions, etc.? Being somewhat of a backup and recovery junky, I am extremely interested in your thoughts. Let the dialogue commence!

Update: 03/26/2008

A colleague of mine has been doing some testing using RMAN, RAC, ASM, FRA for archive log management. Also, he has tested the integration of DataGuard into this configuration. To be more precise, he has tested using an FRA residing in an ASM disk group as the only local archive log destination. In addition to the local destination, each archive log is sent to the standby destination. Based on his testing this approach is rather robust. The archive logs are backed up via the "BACKUP RECOVERY AREA" command with a regular periodicity. This enables the FRA's internal algorithm to remove archive logs that have been backed up, once the space reaches 80% full. No manual intervention is required to remove the archive logs. Moreover, the archive logs in this configuration will only be automatically deleted from the FRA if both of the following are true: 1) the archive log has been backed up satisfying the retention policy and 2) the archive log has been sent to the standby. When there is a gap issue with the standby database, the archive logs are read from the FRA and sent to the standby. It works real nice!

Last Blog Entry (sysdate-364)

2008-01-04T21:44:00.001-05:00

Well, it has been nearly one year, to the day, since my last post (sorry for the confessional like preamble). I was at a luncheon today with some former colleagues and some were asking me when I was going to start blogging again. I hope to start back up here pretty soon. So, if anyone is still dropping by, I hope to resume with some new material. However, I might try and keep it a bit less technical (fewer bits and more bytes); more light hearted, yet hopefully still informative and fun. Redo log dumps and SCN dribble probably sends most into a coma. Heck, I read some of my prior posts and nearly fell asleep. I will continue the "Oracle Riddles" posts as they seem to generate interesting and fun dialogue. The key is to have FUN with it. If blogging becomes a chore then you are doing it for the wrong reason. I actually visited Tom Kyte's blog this evening and started reviewing some of his more recent entries - to get the juices flowing. BTW, who is the chap with the Johnathan Lewis-ian beard pictured on his blog? :-).

Physical Standby Turbo Boost

2007-02-10T18:28:00.000-05:00

Is your physical standby database lagging way behind your production database? Maybe an outage to your standby environment has produced a lag that will not meet certain business requirements: reporting needs, disaster recovery time objective, testing, etc. When you don't have the luxury of performing a full production restore into your standby environment and your archive log files are not being consumed at an acceptable pace, you still have options that don't involve immediate architectural changes.

In some cases you can dramatically speed up your recovery time by copying a small subset of your production database to your standby environment and resume recovery. For example, if a large percentage of your database's write activity is absorbed by a small subset of your database you are primed for a standby recovery turbo boost. Notice I did not say small percentage of your data files. After all, you could have 90% of your writes going to 10% of your data files, but those data files might comprise 90% of your database footprint. In most cases a small percentage of your database files equates to a small subset of your database, but not always.

If a vast majority of writes go against a small subset of your database, how would copying these files to your standby give your recovery a boost? During recovery if Oracle does not need to recover a file it won't. All of those redo entries dedicated to recovering those files will just get passed over. Knowing this simple fact can help you get your physical standby database back on track to meet the needs of your business quickly.

The first order of business is to determine if the write skew condition exists in your database: those files, if copied to your standby, benefiting your recovery time the most. Fortunately, this information can be easily gathered using the v$filestat and v$datafile dynamic performance views in your production database. The following query will get you the top N most written to files in your database.

select * from
(select a.name, b.phyblkwrt from v$datafile a, v$filestat b
where a.file# = b.file# order by 2 desc)
where rownum < N;

If you know the data files that are getting written to the most in production then you also know the most frequently written to files on your standby during recovery. If Oracle can skip over redo entries during recovery then you avoid all of that physical and logical I/O against your standby data files. To recover a database block you have to perform a read and a write of that block. If your writes are somewhat evenly distributed amongst the files in your database then it will be more difficult to get that turbo boost. But, if 60+% of your database writes are absorbed by <= 10% of the database footprint you could gain a significant boost in the recovery time by shipping those files to your standby.

I know this is a rather short post, but this little tidbit just might help you get out of a physical standby database recovery dilemma.

Logical Reads and Orange Trees

2007-01-18T23:36:00.000-05:00

My previous post was a riddle aimed to challenge us to really think about logical I/O (session logical reads). Usually we think of I/O in terms of OS block(s), memory pages, Oracle blocks, Oracle buffer cache buffers, etc. In Oracle, a logical I/O is neither a measure of the number of buffers visited, nor the number of distinct buffers visited. We could of course craft scenarios yielding these results, but these would be contrived special cases - like an episode of Law and Order only better. Instead, logical I/O is the number of buffer visits required to satisfy your SQL statement. There is clearly a distinction between the number of buffers visited and the number of buffer visits. The distinction lies in the target of the operation being measured: the visits not the buffers. As evidenced in the previous post we can issue a full table scan and perform far more logical I/O operations than there are blocks in the table that precede the high water mark. In this case I was visiting each buffer more than one time gathering up ARRAYSIZE rows per visit.

If I had to gather up 313 oranges from an orchard using a basket that could only hold 25 oranges, then it would take me at least 13 visits to one or more trees to complete the task. Don't count the trees. Count the visits.

Oracle Riddles: What's Missing From This Code?

2007-01-15T19:03:00.000-05:00

The SQL script below has one line intentionally omitted. The missing statement had a material impact on the performance of the targeted query. I have put diagnostic bookends around the targeted query to show that no DML or DDL has been issued to alter the result. In short, the script inserts 32K rows into a test table. I issue a query requiring a full table scan, run a single statement and rerun the same query - also a full table scan. While the second query returns the same number of rows, it performs far fewer logical I/O operations to achieve the same result set. Review the output from the script. Can you fill in the missing statement? Fictitious bonus points will be awarded for the Oracle scholar that can deduce the precise statement :)

/* Script blog.sql

spool blog.out
set feed on echo on;
select * from v$version;
drop table mytable;
create table mytable (col1 number) tablespace users;
insert into mytable values (3);
commit;
begin
for i in 1..15 loop
insert into mytable select * from mytable;
commit;
end loop;
end;
/
analyze table mytable compute statistics;
select count(*) from mytable;
select blocks from dba_tables where table_name = 'MYTABLE';
select blocks from dba_segments where segment_name = 'MYTABLE';
select index_name from user_indexes where table_name = 'MYTABLE';
set autot traceonly;
select * from mytable;
set autot off;
REM Bookends to show no DML or DDL statement has been executed.
select statistic#, value from v$mystat where statistic# in (4,134);
... missing statement
REM Bookends to show no DML or DDL statement has been executed.
select statistic#, value from v$mystat where statistic# in (4,134);
set autot traceonly;
select * from mytable;
set autot off;
select blocks from dba_tables where table_name = 'MYTABLE';
select blocks from dba_segments where segment_name = 'MYTABLE';
select index_name from user_indexes where table_name = 'MYTABLE';
select count(*) from mytable;
spool off;

End Script blog.sql */

/* Output

oracle@eemrick:SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Solaris: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
5 rows selected.
oracle@eemrick:SQL> drop table mytable;
Table dropped.
oracle@eemrick:SQL> create table mytable (col1 number) tablespace users;
Table created.
oracle@eemrick:SQL> insert into mytable values (3);
1 row created.
oracle@eemrick:SQL> commit;
Commit complete.
oracle@eemrick:SQL> begin
2 for i in 1..15 loop
3 insert into mytable select * from mytable;
4 commit;
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
oracle@eemrick:SQL> analyze table mytable compute statistics;
Table analyzed.
oracle@eemrick:SQL> select count(*) from mytable;
COUNT(*)
----------
32768
1 row selected.
oracle@eemrick:SQL> select blocks from dba_tables where table_name =
'MYTABLE';
BLOCKS
----------
61
1 row selected.
oracle@eemrick:SQL> select blocks from dba_segments where segment_name =
'MYTABLE';
BLOCKS
----------
64
1 row selected.
oracle@eemrick:SQL> select index_name from user_indexes where table_name =
'MYTABLE';
no rows selected
oracle@eemrick:SQL> set autot traceonly;
oracle@eemrick:SQL> select * from mytable;
32768 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1229213413
-----------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time

-----------------------------------------------------------------------------
0 SELECT STATEMENT 32768 65536 26 (4) 00:00:01

1 TABLE ACCESS FULL MYTABLE 32768 65536 26 (4) 00:00:01

-----------------------------------------------------------------------------

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2248 consistent gets
0 physical reads
0 redo size
668925 bytes sent via SQL*Net to client
24492 bytes received via SQL*Net from client
2186 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
32768 rows processed
oracle@eemrick:SQL> set autot off;
oracle@eemrick:SQL> REM Bookends to show no DML or DDL statement has been
executed.
oracle@eemrick:SQL> select statistic#, value from v$mystat where statistic#
in (4,134);
STATISTIC# VALUE
---------- ----------
4 18 <-- Statistic #4 is user commits
134 461920 <-- Statistic #134 is redo size
2 rows selected.
oracle@eemrick:SQL> ... missing echo of statement
oracle@eemrick:SQL> REM Bookends to show no DML or DDL statement has been
executed.
oracle@eemrick:SQL> select statistic#, value from v$mystat where statistic#
in (4,134);
STATISTIC# VALUE
---------- ----------
4 18
134 461920
2 rows selected.
oracle@eemrick:SQL> set autot traceonly;
oracle@eemrick:SQL> select * from mytable;
32768 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1229213413
-----------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time

-----------------------------------------------------------------------------
0 SELECT STATEMENT 32768 65536 26 (4) 00:00:01

1 TABLE ACCESS FULL MYTABLE 32768 65536 26 (4) 00:00:01

-----------------------------------------------------------------------------

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
173 consistent gets
0 physical reads
0 redo size
282975 bytes sent via SQL*Net to client
1667 bytes received via SQL*Net from client
111 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
32768 rows processed
oracle@eemrick:SQL> set autot off;
oracle@eemrick:SQL> select blocks from dba_tables where table_name =
'MYTABLE';
BLOCKS
----------
61
1 row selected.
oracle@eemrick:SQL> select blocks from dba_segments where segment_name =
'MYTABLE';
BLOCKS
----------
64
1 row selected.
oracle@eemrick:SQL> select index_name from user_indexes where table_name =
'MYTABLE';
no rows selected
oracle@eemrick:SQL> select count(*) from mytable;
COUNT(*)
----------
32768
1 row selected.
oracle@eemrick:SQL> spool off;

End Output */

Clue: The missing statement is not "alter system set do_less_work = true;"

Increasing the Longevity of Your CPU

2006-12-06T12:49:00.000-05:00

One of my current assignments is to evaluate the potential to increase CPU headroom on a server running a large OLTP Oracle database. Of course, any project such as this is typically motivated by the desire to save money by foregoing a seemingly imminent hardware upgrade. Realizing more CPU headroom for your Oracle database server can be achieved, but not limited to, the following approaches:

1. Add more same-speed CPUs to your existing server.
2. Replace your existing CPUs with faster CPUs.
3. Replace your existing CPUs with a greater number of faster CPUs.
4. Commission a new server platform with more same-speed and/or faster CPUs.
5. Commission a new server platform with a greater number of slower CPUs.
6. Chronologically distribute the load on the system to avoid spikes in CPU.
7. Reduce the work required of the system to satisfy the business.

More times than not I suspect approaches 1-5 are chosen. I am of the opinion that 1) and 3) are more predictable when trying to evaluate the expected CPU headroom yield. Propositions 2), 4) and 5) can be a little less predictable. For example, if I double the speed of my current CPUs will I yield the upgraded CPU cycles as headroom? That is, if I am running 10x500MHz and upgrade to 10x1GHz will I now have the additional 5GHz as headroom? It has been my experience that upgrades such as these do not produce such predictable results, especially if your current box approximates 100% utilization. Certainly, moving to a new server with a greater number of same-speed and/or faster CPUs is a tricky proposition. New servers need to be tested using Production volume with great rigor. While at face value 500MHz would appear to be universally “portable” to any server, there are many other factors that can influence your CPU horsepower: memory architecture, amount of processor cache, processor crosstalk, etc. Options 1-5 can all be very costly and in some cases yield undesirable and unpredictable results.

If you have the luxury of distributing the load on your system to avoid spikes in CPU then that is a great first option. It could buy you more time to evaluate a longer-term solution. For example, shifting any batch jobs to off-peak OLTP hours might give you immediate relief.

What if we can simply “do less” to satisfy the needs of the business? This concept is not new to most Database Administrators and rings a rather cliché tone. After all, aren’t we brow-beaten by the dozens of books and countless articles that speak to database and SQL optimization? The “do less” principle is very sound, but it can be intractable. Reducing the work required of an application often requires management support and can run into political obstacles at every turn. Getting Application Developers and Database Administrators to work in lockstep can require a significant effort. If Management, Developers and Database Administrators buy into a synergistic endeavor the benefits can be amazing – and can save the company a large sum of money.

If you are lucky enough to be working on a project where the common goal of all parties is to reduce the CPU load on your system then I have learned a few things that I hope can help you.

Identify the Targets for Optimization

Identify those SQL statements that contribute the greatest to the CPU load on your database server. These statements usually relate to those that produce the most logical I/O on your database. Caution needs to be taken when trying to identify these statements. You shouldn’t focus solely on those statements that have the highest logical I/O (LIO) to execution ratio. Often you will find statements that are well optimized but are executed with extremely high frequency. Look for the aggregate LIO footprint of a SQL statement. Without Statspack or AWR this analysis might be very difficult. However, if you collect this diagnostic data you can use the LEAD analytical function to craft a nice SQL statement to identify the top CPU consuming statements on your system (join stats$sql_summary and stats$snaphot).

Don’t limit your SQL statement identification to just those statements flagged by your analysis as a top CPU consumer. Go another step and identify the most frequently executed statements. Some of the most frequently executed statements are the most optimized on your system. These statements if executed by many programs concurrently can influence concurrency and thusly CPU load. One approach I took recently identified the top 20 CPU consuming statements during a 12 hour window of each week day. I then ran the same analysis against the most frequently executed statements on the system. The results yielded only 31 distinct statements as 9 were on both lists. The amazing thing is that, on average, these 31 statements contributed to 58% of all logical reads on the system and 59% of all executions. Keep in mind that there were over 20 thousand distinct statements cached in the Shared Pool. It is rather amazing that such a small subset of the application footprint contributed so greatly to the aggregate load.

Ask The Right Questions

The identification phase is crucial as you want to optimize that which will yield the greatest benefit. Subsequent to the identification phase the Database Administrators and Developers can sit and discuss approaches to reduce the load incurred by these SQL statements. Here are some of the key points I have taken away during such collaborative efforts.

1. Is there a better execution plan for the statement? Optimization is often achieved by rewriting the query to get at a better execution plan. While I don’t like hinting code, they can relieve pressure in a pinch.

2. Does the statement need to be executed? If you see SQL statements that seldom/never return rows (rows/exec approaches 0) there is a possibility it can be eliminated from your application.

3. Does the statement need to be executed so frequently? You might be surprised that Developers often have other application-side caching techniques that can dramatically reduce the frequency of a statement’s execution against the database. Or, the application might simply call the statement needlessly. It doesn’t hurt to ask!

4. Are the requirements of the business immutable? Sometimes you can work an optimization by simply redefining what is required. This is not the tail wagging the dog here. It is possible that the business would be completely happy with a proposed optimization. For example, can the query return just the first 100 rows found instead of all rows.

5. Do the rows returned to the application need to be sorted? Highly efficient SQL statements can easily have their CPU profile doubled by sorting the output.

6. Are all columns being projected by a query needed? If your application retrieves the entire row and it only needed a very small subset of the attributes it is possible you could satisfy the query using index access alone.

7. Is the most suitable SQL statement being executed to meet the retrieval requirements of the application? Suitability is rather vague but could apply to: the number of rows fetched, any misplaced aggregation, insufficient WHERE clause conditions etc.

8. Are tables being joined needlessly? I have encountered statements that Developers have determined are joining a table, projecting some of its attributes, without using its data upon retrieval. The inclusion of another table in such a manner can dramatically increase the logical I/O required. This is extremely difficult for a DBA to discern without intimate application code knowledge.

9. How well are your indexes clustered with your table(s)? Sometimes data reorganization techniques can greatly reduce the logical I/O required of a SQL statement. Sometimes IOTs prove to be very feasible solutions to poor performing queries.

10. Can I add a better index or compress/rebuild an existing index to reduce logical I/O? Better indexing and/or index compression could take a query that required 10 logical I/O operations down to 5 or 6. This might feel like a trivial optimization. But, if this statement is executed 300 times each second that could save your system 1,500 logical I/Os per second. Never discount the benefit of a 50% reduction of an already seemingly optimized statement.

11. Can I reorganize a table to reduce logical I/O?

I suspect most of us have read that 80% of optimization is application centric (I tend to feel that the percentage is higher). Usually the implication is that the SQL being generated and sent to the database is 80% of the target for optimization. More specifically, optimization requires the tuning of SQL 80% of the time. However, don’t limit your efforts to optimize your application to “tuning the SQL.” Sometimes a portion of your optimization will include “tuning the algorithms” used by the application. Needless execution and improper execution of SQL statements can be equally destructive. Hardware resources, in particular CPUs, can be very expensive to purchase and license for production Oracle databases. It is well worth the effort to at least investigate the possibilities of increasing CPU headroom by decreasing CPU utilization.

Update: An astute reader suggested I mention Cary Millsap's Optimizing Oracle Performance with regard to this topic. I highly recommend reading this book as it weighs in heavy on Oracle optimization and Method-R. Trust me if you have optimization on the brain don't miss this read.

Oracle Riddles: What's The Point?

2006-09-10T09:53:00.000-04:00

I am frequently asked for directions. Sometimes I am not the best to ask and will just be a waste of your time and energy. Other times I am sought exclusively. I try to lead a balanced life. But, hey, I am not perfect. What exactly am I?

Don't Get Caught With Your GUI Down

2006-09-08T22:21:00.000-04:00

In a recent wave of interviews I was amazed how little prospective DBA candidates knew about user-managed hot backups. Most could give the BEGIN and END backup stuff and convey that it causes more redo to be generated during this time. But, when asked to give a little more of their insight into the mechanics or performance implications, 9 in 10 had plenty to say - just nothing that was correct. 90% could not explain the significance of putting a tablespace in hot backup mode. That is, why do it? Why not just copy the file while the database is open and cooking? Of course, most understood that Oracle needs us to do this so that the backup is "good", but few knew how Oracle went about doing it. Moreover, few knew why the extra redo was generated. And most amazing, nearly all thought the data files were locked and changes were written to the redo logs and reapplied when the END BACKUP command was given. Where are DBA-types reading this? Irrespective, the DBA population is evolving.

I am not basing my opinion on one simple question concerning user-managed backups, but a host of other questions given as mental exercises. What are some of the Oracle wait events? What do they represent? How would you go about troubleshooting systemic response time degradation in your production database? What is extended SQL tracing and why use it? Time after time candidates struggled to give lucid, well thought out responses. A vast majority of responses could be summarized as, "I would go into OEM and check for A or B." I don't have a problem with using OEM, but usually the A’s and B’s had little relevance to the question.

The herd of available DBAs that are able to navigate the database using native SQL to get at critical performance diagnostic information has thinned dramatically. Sometimes I wonder what would happen to some of these shops being supported by some I interview if OEM, Database Control or Grid Control took the night off. When relegated to digging into the database and troubleshooting armed only with a SQL prompt, many appear to be lost. I certainly appreciate what the Oracle GUI database management tools bring to the table. I even like them. My point is, don't throw away your shovel just because you have a snow blower. The day will come when your GUI will fail you and it will be just you and your SQL prompt.

P.S.> Oracle does not lock the content of its data files during the course of a user-managed hot backup. Actually, Oracle only locks one thing, the master checkpoint SCN inside the file header. Some other constructs in the file header stay mutable. Blocks in data files being backed up can be modified as per normal database operation. The changes to blocks are indeed recorded in the redo, but they are not replayed when the END BACKUP is issued. More redo is possible because Oracle must accommodate the potential presence of fractured blocks.

SQL Gone Wild!

2006-08-31T13:52:00.000-04:00

Ever see something so inefficient it evokes images of grape stomping to produce wine? I have, in Oracle 10g no less. A colleague of mine brought me a situation the other day that made me do a double-take, no triple-take, on a 10046 trace. The scenario involved a single row delete from a table using the index associated with the primary key on said table to delete the row, simple right? Well, the delete hung. The 10046 showed "db file sequential reads" spewing at a very rapid clip. The process was reading a child table that contained a column that referenced the primary key of the table being deleted. Okay, this is to be expected. We don't want to break our self-imposed business rules by orphaning child records. So what is my beef with this situation?

The child table had millions of rows that would have been orphaned had the delete succeeded. Keep in mind the constraint was NOT defined with ON DELETE CASCADE. Also, a single column index on the child table was associated with the child key. The stage was set for a swift and proper decline by Oracle to perform our delete. But this did not happen. Oracle was visiting ALL of the child rows then returning ORA-00292 "... - child record found." Yes, each and every child index entry was being visited. My colleague opened as SR with a very elegant little test case that reproduces the problem. Here it is. Try it for yourself and watch the trace with wonder and amazement. We have performed the test in 8i, 9i and 10g with the same results.

DROP TABLE CHILD;
DROP TABLE PARENT;
CREATE TABLE PARENT (COL1 NUMBER);
ALTER TABLE PARENT ADD CONSTRAINT PARENT_PK PRIMARY KEY (COL1);
CREATE TABLE CHILD (COL1 NUMBER);
CREATE INDEX CHILD_IX_01 ON CHILD (COL1);
ALTER TABLE CHILD ADD CONSTRAINT CHILD_FK_01 FOREIGN KEY (COL1) REFERENCES PARENT;
INSERT INTO PARENT VALUES (999999999999);
INSERT INTO CHILD VALUES (999999999999);
COMMIT;

-- Insert approximately 1 million records into CHILD
begin
for i in 1..20 loop
insert into child select * from child;
commit;
end loop;
end;
/

alter session set events '10046 trace name context forever, level 12';

DELETE FROM PARENT WHERE COL1 = 999999999999;

Why doesn't Oracle stop once it encounters the first index entry indicating a foreign key violation has just occurred? Isn't a single found entry sufficient to fail my statement? It seems a bit indulgent to check each and every child row irrespective of my barbaric attempt to break my own business rules. Is it a classic case of stupid is as stupid does? Nope. It is a good old fashioned Oracle bug.

By the way, the Oracle support analyst recommended putting the index associated with a child key in a read only tablespace as a workaround. Think about that for a second...

Instructive Presentation on Logical I/O Mechanics

2006-08-01T18:33:00.000-04:00

If a picture says a thousand words than a good animation can say ten thousand. Check out this offering by Julian Dyke. His presentations relating to Oracle mechanics still reign supreme in my book. Once you see a mechanical concept "in motion" you simply don't forget it. What a great didactic device. Anyway, I just wanted to pass this along. Enjoy.

Training Class (Final Day)

2006-07-27T20:07:00.000-04:00

To round off the material covered in this class the following topics were covered today:

Tuning Block Space Usage.
Tuning I/O.
Tuning PGA and Temporary Space.
Performance Tuning: Summary.

I found the Tuning I/O lecture somewhat interesting. The first portion of the lecture focused on the advantages and disadvantages of the various forms of RAID protection. While informative, I could've spent 5 minutes on Google had I not already been armed with the knowledge of this technology. The remainder of this lecture focused on ASM (Automatic Storage Management). This rather non-trivial feature in 10g sounds very cool; define some Data disk group(s) , the relevant protection and striping granularity and let Oracle do the all of the I/O tuning. Of course, this is a severe over simplification of what it really does (or doesn't, as your mileage may vary). But, the point is, this feature is supposed to free the DBA from the often times laborious chore of tuning the I/O subsystem. Truthfully, I think the degree to which Oracle touts the hands-off nature of this feature is overstated; especially for busy production systems. I, nor anyone in the class, had worked with the product. Consequently, I feel there are probably very few shops out there migrating their production databases to ASM. Is it more of a political battle? After all, if DBAs will be able to someday create and manage the logical volumes/file systems this might make the System Administrators feel a little encroached upon. It is just a hunch, but widespread conversions to ASM will probably not happen anytime soon. Anyone reading this blog have any good/bad experience with ASM in a production environment? I am very interested in your feedback.

The most engaging lecture of the day was the Tuning Block Space Usage. I am really keen to the Automatic Segment Space Management (ASSM) feature. This feature warrants serious consideration given the upside: free list elimination and a considerably more robust approach to reusing blocks for inserts. As much as I liked the discussion on ASSM, the subsequent topic grabbed my utmost attention: segment shrinking. What a great (and might I add way overdue) feature. If one of my production environments was on 10g today I could see using this tool to reclaim vast amounts of space in some of my very large heap tables, index-organized tables and indexes. Oracle claims that the majority of the work can be done online. Moreover, the indexes associated with your heap tables are still usable even after the row movement inherent to the SHRINK has completed. I like the idea of having the freedom to perform these "online" activities, but I still prefer to perform these kinds of operations during quite periods. The course material gives a fantastic, albeit brief, description of the mechanics. Very nice Oracle! Once again, are there any readers of this blog that have experience with this feature and want to share your experiences?

The final two lectures, Tuning PGA and Temporary Space and Performance Tuning Summary, were good, but not great. The material seemed to belabor a few points.

In summary, if you are considering taking this course I think you are best served if you do not have much 10g experience in production environments. If your experience with 10g and some of the "tuning" features is even moderate, I recommend you not take the course. Your time would be better spent reading up on this material in the Oracle documentation set.

Eric's rating of the course: B+.

Training Class (Day 3)

2006-07-26T20:37:00.000-04:00

Another day of training is in the books. What was on today's menu?

Tuning the Shared Pool.
Tuning the Buffer Cache.
Automatic Shared Memory Management.
Checkpoint and Redo Tuning.

Apparently, Oracle is migrating some of its serialization protection from latches to mutexes. For example, the structures previously protected by the Library Cache Pin latch are now protected by a mutex and evidenced by the cursor:pin S wait event. Actually there are several new mutexes and mutex related wait events new to 10g. For example:

- cursor:mutex indicates mutex waits on parent cursor operations and statistic block operations.

- cursor:pin events are waits for cursor pin operations (library cache pin now protected by mutex).

There are a couple interesting facts about Oracle and mutexes. A mutex get is about 30-35 instructions, compared to 150-200 instructions for a latch get. Also, a mutex is around 16 bytes in size, compared to 112 bytes for a latch in Release 10.2 (in prior releases, it was 200 bytes).

One of the appeals of the mutex, per the documentation, is the reduced potential for false contention. That is, a mutex can protect a single structure; often times stored with the structure it protects. However, latches often protect many structures (see cache buffers chain latch) and can yield what the documentation calls false contention. It is called false contention because "the contention is for the protection mechanism rather than the target object you are attempting to access." This all sounds really great, right? Well, maybe. If Oracle goes to more widespread use of mutexes instead of latches to protect target objects that would be a boatload more mutexes. I am sure the porters at Oracle are not intending to use mutexes exclusively in the future. But, I can see where contention in Oracle could be dramatically reduced at the cost of CPU cycles and memory. What would happen if Oracle protected each buffer with a mutex? While each mutex is less expensive with regard to memory and CPU than an individual latch, you will need considerably more mutexes for each replaced latch. 50 mutexes used to replace a single latch could run the CPU up considerably for the "same" application workload.

I have one final note on mutexes. As of version 10.2.0.2 a SELECT against V$SQLSTAT and searches of child cursor lists are mutex protected.

I found the Tuning the Buffer Cache discussion somewhat interesting. Unless you have been hiding under a rock the past 4-5 years, I am sure you have heard the Oracle experts preaching the notion that ratios are not very helpful in diagnosing the health of a database. In particular, the buffer cache hit ratio is frequently tagged as meaningless. A smile came to my face when I read the following excerpt from the course material:

"A badly tuned database can still have a hit ratio of 99% or better...hit ratio is only one part in determining tuning performance...hit ratio does not determine whether a database is optimally tuned..."

Oracle is finally teaching what the experts have been saying for years!

I have been to several Hotsos events/training classes. They often talk about the need to include the buffer is pinned count statistic in the tally for logical reads. These operations are simply latch-reduced logical reads. Why doesn't Oracle integrate this information into their course material or documentation set? They still only claim that db block gets and consistent gets constitute logical reads. I monitored a process recently in one of my production environments and noticed the process did 2 buffer is pinned count logical reads for every 1 (db block gets + consistent gets). That is a substantial percentage of work owed to operations not officially categorized as a measure of work by Oracle.

Lastly, the on-topic impromptu discussions were fruitful. That always makes the training session more interesting :)

Training Class (Day 2)

2006-07-26T08:57:00.000-04:00

The second day of training was much better than the first. I suspected it would get better based on the material to be covered. The topic set de jour was:

Metrics, Alerts and Baselines.
Using Statspack.
Using Automatic Workload Repository.
Reactive Tuning.

Having limited exposure to 10g in any true production environment, I found 75% of these topics interesting (Statspack chapter was not valuable to me). I really like what Oracle has accomplished with 10g with regard to the gathering and reporting of statistics and metrics (the rates of changes for given statistics). About 5 years ago I wrote a utility for 9i that allowed me to compare Oracle-captured statistics and wait event durations to similar reference points. This utility, I dubbed AppSnap (written in PL/SQL), captured the statistics and wait event durations each hour and calculated and stored the deltas in a separate tablespace. This permitted me to compare what is considered "typical" load to current load and evaluate the deviations rather quickly. I wrote a Unix shell script reporting tool called Instance Health that reports each hour the deltas as they relate to what I call peer hours. For example, each hour a report is generated as a text file and stored in a log directory. The most previous delta is compared to the same hour of day for the past 30 days, the same hour of day and day of week for the past 12 weeks and against all hours for the past 30 days. This has proved to be very valuable for detecting systemic anomalies after application upgrades, etc.

Okay. Now Oracle has come along with 10g and provides the same functionality (albeit not free). I appreciate the graphical conveyance of this type of analysis provided by Enterprise Manager. Shoot, Oracle even calculates the variance within the sampled timeframe for each metric. This is really cool because you can easily write a query that can ascertain if some metric is statistically anomalous (i.e. +-3 standard deviations). At first glance, some of the AWR reports are not very intuitive. But, the more you stare at them the more sense they appear to make. The Active Session History reporting is also a very nice feature (once again, not free).

If you already have considerable work experience with AWR/ASH/ADDM then this class probably won't provide you much value. The course does go into the mechanics of the data capturing and touches rather superficially on the reporting capabilities. So there is a good chance you probably have more knowledge about these products than this class affords. However, if you are like me and have yet to dig in your heels on a 10g production environment this class could serve as a very nice primer.

Well, I am off to day 3 of this training class.

Training Class (Day 1)

2006-07-24T18:02:00.000-04:00

As I mentioned in my last post, I am attending an Oracle training class this week. The class is Oracle Database 10g: Performance Tuning. Having been to the Oracle 10g New Features for Database Administrators class last year, I was hoping for a substantially more in-depth look at 10g instance tuning in this class.

The first day was just okay for me. I learned a few things but I felt the class dragged a bit: 30 minutes getting to know each other and too many unrelated tangents or related, yet gratuitous, topic embellishments. I don't mind an occasional anecdotal deviation as it relates to the topic at hand, but those that are completely off topic really slow down the course. You know when you are reading a story and everything seems to flow nicely (proper balance of dialogue and narrative), then you run into a couple pages of narrative? It really puts the brakes on the interest.

I tend to evaluate courses on:

The conveyed knowledge of the instructor.
The presentation of the subject matter.
The quality of the course material.
The value of the impromptu discourse along the way (icing on the cake stuff).

Based on the first day, I feel the instructor has a good command of the subject matter and adds value to the class based on relevant experience. I have been to several Oracle classes where the instructor did nothing more than read the slides verbatim and/or appeared to have little relevant experience. Aside from the introductions, we spent the remainder of the day on two topics: a performance tuning overview and 10g statistics/wait events. The study material that accompanies the course is rather good; I have taken the liberty to skip ahead and get into the meat of the course material. I am looking forward to the class tomorrow as I feel we will be digging our heels in a bit more (or at least I hope).

Given the sparse amount of substantive material covered on the first day, I don't have any really interesting takeaways. I'll give the first day of class a B.

iLoveit

2006-07-17T16:17:00.000-04:00

Well, this weekend I replaced my "archaic" Generation 4 IPod with a snazzy new Generation 5 Video IPod. I chose the black facing this time for a change of pace.

I really like the IPod product - versatile, convenient and just loads of entertainment/educational potential for my ~2 hours of public transit commute each day.

With more and more news and entertainment mediums adding video Podcasts this little device has become a cool way to pass the commuting time. I enjoy reading on my commute, but watching an interesting show or funny movie can pass the time at record clips. I won't be surprised if I ask the engineer to make another loop so I can finish watching my movie before heading into work :)

I wonder when Mr. Kyte will be Podcasting some material...

I'll be in Oracle training next week attending the Oracle Database 10g: Performance Tuning (Database) class. It should be a really good course. I'll be blogging some of the interesting takeaways from the class, so stay "tuned"!

Cool Illusion

2006-06-28T18:51:00.000-04:00

Nothing Oracle related this time. Just some fun. I received this nice little illusion today via email. Enjoy.

10,000 Visits and Counting

2006-06-22T21:56:00.000-04:00

Well, today my blog registered its 10,000th visit. While that is probably an average couple of days for Tom Kyte and a few others, I am kind of proud of my meager slice of the Oracle blogging pie. Of course, I get oodles of traffic from Google. But, that's not too bad is it?

Seriously, it has been a fun 4+ months of blogging. I haven't been able to blog as much as I would have liked over the past 2 months. The fact that I still desire to blog is a good sign though. Time and other constraints in life always crop up. Anyway, it has been really fun and I hope some of you have enjoyed your visits. I have received some very interesting emails and questions from Oracle enthusiasts all over the world.

I want to send one special thanks out to Tom Kyte for recommending I start a blog (and placing me on his metablog even though I stole the naming style of his blog) and another to Doug Burns who featured my blog on his site several months back, giving my traffic a jump start.

Actually, Tom, my first choice for a blog name was The Emusing Blog :)

Oracle Blooper

2006-06-21T19:38:00.000-04:00

Well, instead of assimilating the suitable, albeit plentiful, Oracle manuals to cover the material required for the Oracle 7.3 -> Oracle 9i upgrade certification exam I opted to search for a single resource. I came across a third-party study tool that was recommended by Oracle and seemed to foot the bill. Very much to my surprise the material was disjointed, loaded with typographical errors and often flat out wrong.

I was willing to forgive the copious typographical mistakes per/page (which approached the Golden Ratio mind you) as I could deduce the intentions. I could stomach the disjointed word salads and sparse information. But I refused to read another page after encountering a heinously blatant, careless and nonsensical bit of misinformation. How could I possibly continue to use this material as a study reference if I could not trust the content? To the misinformation at hand, the material states the following verbatim:

PGA_USED_MEM - The process is using PGA memory.
PGA_ALLOC_MEM- The process has been allocated PGA memory.
PGA_MAX_MEM - The process has been allocated maximum memory.
PGA_GIBBERISH - The process has found gibberish in the PGA and wishes to purge. (OK, this was my invention)

I scratched my head. Re-read, scratched head some more. Finished beer and reached for another. Nothing seemed to alleviate my consternation. I was well aware of these attributes of v$process and was not so much concerned with the incorrectness, as I knew their meaning. It was the gross negligence that left my jaw drooping for a minute.

The values for these attributes are NOT Boolean as you well know. You don't query v$process and find a Y or N associated with the values for these attributes. The Oracle documentation defines these attributes in a very straightforward manner. Is there any other way?

PGA_USED_MEM number PGA memory currently used by the process
PGA_ALLOC_MEM number PGA memory currently allocated by the process (including free PGA memory not yet released to the operating system by the server process)
PGA_MAX_MEM number Maximum PGA memory ever allocated by the process

Simply stated, I was shocked that the author(s) and editor(s) put such little thought into the material and subsequent proof reading. Actually, I think the author's brain was tied behind his back while writing this material. If one aspires to put together training material and includes attribute definitions that are pre-defined for you in the Oracle documentation set, might I recommend taking a cursory glance at said documentation? You can't just feed me a heaping helping of documentation rubbish without expecting me to pitch the kindling into the nearest can - I know, I've seen me do it! Did I mention the material is several fold more expensive than any of Tom Kyte's or Jonathan Lewis' books? Lesson learned.

Grapes of Math Puzzle

2006-06-04T17:30:00.000-04:00

First, let me say that I was really excited about the title I gave to this post. It hit me while mowing my lawn and made me stop and laugh - thankfully the neighbors were not watching (as far as I know). Anyway, I did a quick Google search on the title Grapes of Math to see how original it was. While I had not heard of the title, it has many hits on the Web. Oh well, so much for absolute originality.

Tom Kyte has a really good puzzle on his blog. I enjoy a good puzzle and submitted my response, which I believe is the solution to the puzzle. But, for my personal satisfaction and to address Mr. Ed's concerns, I wanted to prove that my response was the only possible correct answer, given some conditions I have derived from Tom's post, the problem (picture) itself and intuition. If you are interested in the puzzle, please visit Tom's blog and try to solve it for yourself and skip the remainder of this post.

You mathematicians will please forgive any seemingly barbaric notations or proof layout :) Cary, Jonathan, Tom or any other mathematician lurking about, please feel free to critique the proof if it is incorrect.

Grapes of Math equation generated from picture:
(10(banana)+apple)/pear = 10(grapes)+peach+(strawberry/pear)

Prove the only solution set (banana, apple, pear, grapes, peach, lemon, strawberry) for the Grapes of Math is (9,3,2,4,6,8,1) given the following conditions:

Conditions

0) each fruit represents a distinct integer that must be in [0-9]. Negative integers don't really make much sense in this case - how do you ascribe a negative integer to any fruit but the pear?
1) the numerator concat(banana,apple) is in [00-99].
2) pear cannot be 1 because grapes * pear would equal grapes and it does not. grapes * pear = lemon.
3) pear cannot be 0 because x/0 is undefined for all integers x.
4) from 2) and 3) 9 >= pear > 1.
5) grapes cannot be 1 because grapes * pear would equal pear and it does not. grapes * pear = lemon.
6) grapes cannot be greater than 4 because that would yield a concat(banana,apple) that is > two digits, which cannot be (condition 1). For example, the integer portion of the quotient concat(grapes,peach) must be less than 50, based on 4).
7) from 5) and 6), 4 >= grapes > 1.
8) from 7) banana is in [4-9]. If the integer portion of the quotient (grapes) is 2, 3 or 4, then given 4) the numerator, concat(banana,apple), must be in [40-99] . The least the numerator could be is 40 given 4) and 7). The highest would be 99 by definition.
9) all fruits taste really yummy (This is for Mr. Ed)

Proof by Exhaustion (brute force method): Grapes of Math Puzzle

Case 1: grapes = 4

If grapes = 4 then banana can only be 8 or 9 because of 4).

Case 1.1: banana = 8

If grapes = 4 and banana = 8 then pear = 2 and lemon = 8. Lemon cannot equal banana by condition 0) and, thusly, banana != 8. Therefore, concat(banana,apple) is not in [80-89].

Case 1.2: banana = 9

If grapes = 4 and banana = 9 then pear = 2 and lemon = 8. Then by subtraction (banana - lemon ) = (9-8) = 1 = strawberry.

Case 1.2.1: apple = 0

If apple = 0 then peach = 5 and concat(grapes,peach) = 45 with no remainder. We know that there must be a remainder because strawberry is 1 in this case. Therefore, apple != 0 and concat(banana,apple) is not 90.

Case 1.2.2: apple = 1

If apple = 1 then apple = strawberry = 1. Therefore, apple != 1 and concat(banana,apple) is not 91.

Case 1.2.3: apple = 2

If apple = 2 then apple = pear = 2. Therefore apple != 2 and concat(banana,apple) is not 92.

Case 1.2.4: apple = 3

If apple = 3 then peach = 6 and concat(strawberry,apple) - concat(strawberry,pear) = strawberry = 1. Therefore, grapes = 4, banana = 9, pear = 2, lemon = 8, strawberry = 1 and apple = 3.

Therefore, concat(banana,apple) = 93 is a numerator solution.

Case 1.2.5: apple = 4

If apple = 4 then apple = grape = 4. Therefore, apple != 4 and concat(banana,apple) != 94.

Case 1.2.6: apple = 5

If apple = 5 then peach = 7 and pear = 4. But, pear is assumed to be 2 and cannot 2 != 4. Therefore, apple != 5 and concat(banana,apple) != 95.

Case 1.2.7: apple = 6

If apple = 6 then peach = lemon = 8. Therefore, apple != 6 and concat(banana,apple) != 96.

Case 1.2.8: apple = 7

If apple = 7 then peach = lemon = 8. Therefore, apple != 7 and concat(banana,apple) != 97.

Case 1.2.9: apple = 8

If apple = 8 then apple = lemon = 8. Therefore, apple != 8 and concat(banana,apple) != 98.

Case 1.2.10: apple = 9

If apple = 9 then apple = banana = 9. Therefore, apple != 9 and concat(banana,apple) != 99.

Therefore, for grapes = 4, the only solution for numerator concat(banana,apple) in [80-99] is 93.

Case 2: grapes = 3

If grapes = 3 then banana can only be 6 or 7 because pear > 1 from condition 4).

Case 2.1: banana = 6

If grapes = 3 and banana = 6 then pear = 2 and lemon = 6, and lemon = banana = 6. Therefore, banana != 6 and concat(banana,apple) is not in [60-69].

Case 2.2: banana = 7

If grapes = 3 and banana = 7 then pear = 2 and lemon = 6. This means apple can only be in [4-5] (cannot be 6 because lemon = apple = 6 violates condition 0).

Case 2.2.1: apple = 4

If apple = 4 then peach = 3, and peach = grapes = 3. Therefore, apple !=4.

Case 2.2.2: apple = 5

If apple = 5 then peach = 5. Therefore, apple != 5.

Therefore, concat(banana,apple) is not in [70-79].

Case 3: grapes = 2

If grapes = 2 then banana must be in [4-5] because of condition 4).

Case 3.1: banana = 4

If banana = 4 then lemon = banana. Therefore, banana != 4 and concat(banana,apple) is not in [40-49].

Case 3.2: banana = 5

If grapes = 2 and banana = 5 then pear = grapes = 2. Therefore, banana != 5 and concat(banana,apple) is not in [50-59].

Therefore, concat(apple,banana) is not in [40 - 59].

From grapes in [2-4], we have proved that only one solution (93) exists for the numerator concat(banana,apple) between 40 and 99. By condition 7), concat(banana,apple) is not in [00-39].

Conclusion

Therefore, 93 is the only solution for concat(banana,apple) in [00-99]. After exhausting all possible two digit values for concat(banana,apple) only one solution set (banana, apple, pear, grapes, peach, lemon, strawberry) was found:

(9,3,2,4,6,8,1)

and for Mr. Ed...

(9,3,-2,4,6,8,1) iff concat(grapes,peach) = -46

Solution set applied to equation of Grapes of Math:

(10(9)+3)/2=93/2=46 ½=10(4)+6+(1/2) quod erat demonstrandum

Note: lemon is absorbed in the equation, given the correctness of strawberry = 1.

The Oracle Certification Process

2006-04-27T22:52:00.000-04:00

In late 1997, on what was a very shiny day (for all of DBA-kind I am sure), I proudly exited my local facility that proctored Oracle certification exams. On this glorious day I had passed the last of four exams required to obtain the coveted Oracle Certified Professional (OCP) title. I was certified on Oracle 7.3 and could not have been more proud. After waiting a few weeks to receive my certificate I brandished it in my home study. Make no mistake about it. I felt this had legitimized my 3 long years of Oracle work to date - I had reached an Oracle summit. At that time the OCP title was not nearly as pervasive as it is today. In hindsight, I suppose my enthusiasm was not entirely unjustified.

Let’s roll time forward nine years to 2006. I have not renewed my certification. For all practical purposes I am not an OCP. I certainly wouldn’t claim such on my resume having only achieved version 7.3 certification. Why haven’t I renewed my certification? After all, Oracle has bent over backwards to assist this erstwhile OCP by offering an upgrade exam. I can take a single exam and immediately upgrade my certification status to an Oracle 9i OCP. If I labored a bit more, I could take another upgrade exam and attain the highest OCP level available. Does this mean that I could, nearly overnight, claim expertise in all of the concepts and elegant nuances Oracle has built into its database since version 7.3? Professionally, on my resume, I suppose the answer is yes. Realistically, the answer is, no way!

I feel the only real way to stay current with our Oracle knowledge and exhibit the technical acumen associated with a proficient Oracle practitioner is to read (and reread) documentation and test features. There is absolutely no substitute for good old-fashioned studying in conjunction with trial and error exercises. I have interviewed dozens of Oracle Certified Professionals over the years, many of which struggled with the basics. I do believe that today, more than ever, the ubiquitous OCP title provides little insight into the qualifications of an Oracle DBA. However, I do believe that the certification process can lay an excellent framework for a strong understanding of the Oracle database. Just, not by necessity. It varies from person to person. One person with the same temporal experience with Oracle and an OCP title might appear lacking when compared to another with equal “qualifications” and accomplishments. Why? We all have different approaches to storing information for retrieval. I remember cramming for exams in college for the courses I loathed. I always seemed to make out okay. But, did I really learn the material or just buffer it long enough so that my mind could hurl it back out in the nick of time? I know, for those “undesirable” classes it was the latter. For me to learn I must:

1. Want to learn.
2. Be passionate about the topic.
3. and study, study, study.

Of course, there are exceptions to the rules, those supremely intelligent humans that roam the earth with a glut of gray matter that have little need for 3), leaving it for the rest of us to toil.

Am I a better DBA than I was nine years ago? I certainly hope so. Could I augment the breadth and depth of my Oracle knowledge by revisiting the certification process? Absolutely. But, couldn’t I really do the same by studying the material covered by the exams? After all, I am passionate about the topic and want to learn. I know. I know. It sounds like a really cheap excuse. Read the material, but, uh hum, skip the exams right? How convenient.

For those of you with your OCP please don’t think I am minimizing your achievements. I am certainly not doing so. I believe that the Oracle certification process can yield a very productive learning experience, insofar as we really take the time to authentically learn the material we are studying. It has been my experience, that if I have ostensibly forgotten what I have learned, as long as I truly understood the material while in the learning process, re-learning can be a very quick enterprise.

By the way, I think I will take the upgrade exams this year. But, this time I refuse to cram. I will revisit the exam topics with a cheerful willingness, as the science of Oracle database administration is a very exciting and challenging branch of knowledge.

Solaris and High Wait I/O CPU

2006-04-20T23:53:00.000-04:00

A few days back a familiar little situation surfaced. Someone monitoring the OS was making claims that a particular machine was running at 100% cpu utilization during a period when a portion of the application was running slower than normal. The assertion being made was our system had a cpu shortage.

Given the fact that the application was running on a Solaris platform I looked at the vmstat history logs kept for just such an investigation. Per vmstat, for the time in question, there was plenty of idle cpu. Immediately, I thought this person must have been looking at the sar data on the machine in question. Sure enough, the sar data indicated a very low percentage of idle cpu. As you might have guessed, the percentage of time the system was waiting for I/O was rather large according to sar and, consequently, low idle time was being reported. I explained that it was typical for this system to run a high wait I/O percentage as reported by sar; after all, it is a database server with many processors. I also explained that low idle time as reported by sar does not necessarily mean a cpu bottleneck exists.

I remembered reading in Adrian Cockroft’s book, Sun Performance Tuning, that vmstat lumps wait I/O into idle time. So, naturally I was confident in my counter-assertion that our cpu utilization was just fine. I assuredly reached for my copy of the Sun Performance Tuning book to show where I had read this information years ago. I searched the index of the book and gave the book a cursory once-over to no avail. I started doubting whether I had reached for the wrong text! A bit frustrated I decide to perform a full book scan. Low and behold, I only got past two pages before my memory was vindicated. On page 3 it reads. “Whenever there are any blocked processes, all cpu idle time is treated as wait for I/O time! The vmstat command correctly includes wait for I/O in its idle value…” Viola!

The clock interrupt handler in the Solaris operating system runs every 10ms (or at least used to) to get cpu utilization information. It will search the state structure for each cpu and find that each cpu is in one of five states: user, system, idle, waiting for I/O or quiesced. Based on my understanding, the quiesced state is not really indicated by a value stored in a structure or variable associated with a cpu. It is simply the state when a cpu is not running user, system or idle threads and not waiting for I/O.

The point is, a high value for wait I/O generated from sar on a Solaris platform does not indicate a cpu bottleneck. Moreover, high wait I/O values do not necessarily indicate an I/O bottleneck. However, an I/O bottleneck could very easy manifest in high wait I/O percentages. You really need to look at your I/O service times to determine if the I/O subsystem is performing poorly.

For those wanting to know more on the algorithm used by Solaris to calculate idle and wait I/O cpu percentages read here. It is a bit dated, but describes how wait I/O is tallied in the Solaris operating system (at least in earlier versions). Interestingly enough this article cites Sun Performance Tuning, my trusty reference.

Getting a Handle on Logical I/O

2006-04-18T18:41:00.003-04:00

The other day a colleague brought to my attention an interesting situation related to one of the databases he supports. The database was, rather consistently, experiencing heavy cache buffers chains (CBC) latch wait events while processing against a set of “related” tables. The solution devised to mitigate the CBC latch contention involved range partitioning said tables. I believe proper partitioning can be a very reasonable approach to minimize the probability of CBC latch collisions. Of course, you must know the manner in which your data is accessed and partition accordingly, as you don’t want to sacrifice existing solid execution plans among other considerations.

As it turned out, the partitioning approach did indeed reduce the CBC collisions; albeit another form of contention surfaced as a corollary, cache buffer handles latch collisions. I must admit I had a very limited knowledge of buffer handles prior to being made aware of this situation. My colleague pointed me to a very interesting article on Jonathan Lewis' site. This article gives a pithy description of buffer handles. I highly recommend you carve out a few minutes to read it. Not only might you learn something about buffer handles, you might be surprised that the more traditional notions of logical I/O do not really suffice. I was first suitably introduced to the buffer is pinned count statistic during a Hotsos training course. Essentially, this statistic indicates the presence of latch-reduced logical I/O.

While, generally speaking, Oracle recommends that hidden parameters not be changed, sometimes they need to be modified to accommodate very specific issues your database is encountering. In this particular case, increasing the value of the _db_handles_cached parameter got rid of the newly surfaced collisions on the cache buffer handles latch. I love learning from others’ experiences. It is amazing how many interesting little tales such as this exist. Also, this type of unforeseen contention shifting reinforces the need to properly test production changes - or maybe better said, the ability to properly test production changes.

Oracle Riddles: Now that is interesting.

2006-03-28T15:27:00.000-05:00

A process can prevent me from staking the claim for another. Users sometimes need me to know if they can secure a spot where another process may have been before. Needless to say, I am a pretty big deal, and users are very interested in me. However, when some stop by for a visit they often feel I haven't cleaned up very well. Do you know what I am?

Recovery of Offline Data Files

2006-03-28T14:38:00.000-05:00

You have probably noticed at one time or another that taking a data file offline in a READ/WRITE tablespace necessitates a subsequent recovery operation on that data file, to bring it online again. The same cannot be said for tablespaces under most circumstances. That is, we can offline normal a tablespace (to make a backup, for instance) and recovery is not required to bring the data files comprising the tablespace online. The reason Oracle requires that you recover your data file is Oracle does not checkpoint a data file when we take it offline. Whereas, for a tablespace taken offline "normally" each data file in that tablespace is checkpointed. This checkpoint operation synchronizes the STOP SCN in the control file and START SCN in the file header. For data files taken offline, Oracle only updates the STOP SCN for the control file entry related to that data file. The result is a control file that has a STOP SCN that is in the future of the Checkpoint SCN in the file’s header, and thusly recovery is required.

I don’t think this is terribly enlightening information for most DBAs. However, I have read on more than one occasion that we need to beware of taking data files offline because of the "potential" of having to roll through a vast amount of archive logs to bring it back online. Most items I have read on this topic, in essence, state that you need all of the redo between the offline operation and the current log to fully recover the data file. Assuming the control file mounted by your database is the current control file, this notion is not accurate. The only redo that is required to recover your data file subsequent to an offline operation is that which is generated between the Checkpoint SCN in the file header and the STOP SCN recorded in the control file.

Let’s create a simple mental exercise to understand why this is the case. Then we can go through a quick example using an Oracle database.

If a database is mounted using the current control file then the information that Oracle maintains for the file headers is assumed 100% reliable. When we take a data file offline we are telling Oracle to immediately render the data file inaccessible to the application. Oracle cannot do this without telling the control file WHEN this offline activity was performed (we are not dealing with OFFLINE DROP). By updating the control file STOP SCN for the data file this requirement is satisfied. Remember, the STOP SCN defines the WHEN in Oracle time. If Oracle decided to checkpoint the data file before taking it offline then recovery would not be an issue. Taking a tablespace offline "normally" proves this.

Okay, let’s assume in our single instance database we have 3 redo log groups with the current log at sequence number 100. Furthermore, let’s take an online data file offline. Assume that this offline activity occurs while log sequence 100 is still the current log sequence number. If after we take the data file offline we initiate 5 logs switches, then our current thread will have the current log sequence number at 105. Since we have 3 log groups we know that log sequence numbers (LSN) 100-102 should be archived (and probably even 103 and 104). So, to the question, why would Oracle require us to roll up through log sequence 105 when the data file was taken offline in log sequence number 100? The control file is current and has recorded the STOP SCN. Given the current status of the control file it is not possible that any change vectors with SCNs greater than the STOP SCN are relevant to the data file we took offline in log sequence number 100. Therefore, it seems abundantly reasonable that the only log sequences required for recovery are those between the Checkpoint SCN (LSN<=100) and the STOP SCN (LSN=100). After log sequence number 100 is applied, recovery should be complete. Now, let’s take this mental exercise and apply it to a real database scenario.

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.

Here we see that Oracle requested archive logs 98 through 100 for recovery. If we had dumped the file header for data file 4 we would have seen that the last time the file was checkpointed the log sequence number was 98. This is evident by looking at the redo byte address associated with the Checkpoint SCN in the header dump. Nonetheless, we have seen using the example above that Oracle does not require all redo generated between the offline operation and the current log for complete data file recovery. Oracle only required the range of log sequence numbers that corresponded to the Checkpoint SCN (LSN=98) and the STOP SCN (LSN=100). Notice that we cannot assume Oracle has applied all redo between the offline and the current log simply because it stopped asking for my archive logs. If Oracle had really applied all redo between log sequence number 98 and the current redo log, then it would have necessitated the application of log sequence numbers 101 and 102 as they are archived and not present in the online thread. But Oracle did not require these logs. We can rest assured that Oracle has only done what it needed to do based on the information in the current control file and the data file header.

Oracle Riddles: Where oh where?

2006-03-27T09:55:00.000-05:00

I create products that are most often undesirable. Maybe a clause in my master plan is where the answer lies. I always cross many obstacles to complete my work. For my namesake, Rene, I behave as I am instructed. What am I?

Media Recovery Locks

2006-03-24T15:05:00.000-05:00

What is the deal with all of those strange media recovery locks (v$lock.type=MR) held by the Database Writer process in our Oracle databases? It seems slightly strange that Oracle would have media recovery locks on a database that is open and fully functional. Or does it?

These media recovery locks do serve a meaningful purpose. When a data file is online and read/write the control file and data file states, alone, would not prevent recovery from being run against that data file. Consider the database that has been shutdown with the ABORT option. The state of the file headers and the corresponding control file information for the database files do not change as a result of an ABORT. No checkpoint is taken to update the file headers and no control file updates are made. The instance is simply terminated; the file header and its corresponding control file information appear just as it did when the database was still open. The replay of committed transactions and the rolling back of uncommitted data is later handled by crash recovery and post-open transaction recovery.

However, it is a perfectly legitimate proposition to take an aborted instance, mount it and issue the RECOVER DATABASE command to make the database consistent. In fact, after you issue the RECOVER DATABASE command you can take a backup of the database. So, if I can issue a RECOVER DATABASE command against data files that are in “aborted” status, what would prevent me from invoking the recovery engine while the database was open? You guessed it, the media recovery (MR) locks held by the Database Writer. Some very strange things, indeed, might ensue if we could start recovery against an online file.

It has been my experience, for an open database, that the Database Writer does not relinquish the MR lock for a data file until the file is taken offline with or without the tablespace. With the data file offline, file media recovery is now a legal operation. When a recovery session is created to recover a data file, that process acquires the MR lock for the applicable data file. This makes complete sense, as that is the session responsible for the recovery. Even if the database is only mounted the recovery session holds the MR lock until that recovery session is cancelled or complete. This prevents multiple recovery sessions from doing the same recovery. If RECOVERY_PARALLELISM is set to a non-zero value the MR lock is still held by the SQL*Plus or RMAN session that started the recovery.

Let’s look at a quick example. This really requires two sessions to show the MR lock activity. The sessions are differentiated by the color of the output.

sys@10g:SQL> @test.sql
sys@10g:SQL> select a.sid,
2 substr(a.program,1,25) program,
3 b.file#,
4 c.type
5 from v$session a,
6 v$datafile b,
7 v$lock c
8 where a.sid = c.sid and
9 b.file# = c.id1 and
10 c.type = 'MR';

SID PROGRAM FILE# TY
---------- ------------------------- ---------- --
167 oracle@machine (DBW0) 1 MR
167 oracle@machine (DBW0) 2 MR
167 oracle@machine (DBW0) 3 MR
167 oracle@machine (DBW0) 4 MR
167 oracle@machine (DBW0) 5 MR
167 oracle@machine (DBW0) 6 MR
167 oracle@machine (DBW0) 7 MR
167 oracle@machine (DBW0) 8 MR

8 rows selected.

sys@10g:SQL>
sys@10g:SQL> select file_name
2 from dba_data_files
3 where tablespace_name = 'USERS';

FILE_NAME
--------------------------------------------------------------------------------
/ora/data001/10g/datafile/o1_mf_users_1pdkdtz5_.dbf

sys@10g:SQL>
sys@10g:SQL> alter tablespace users begin backup;

Tablespace altered.

sys@10g:SQL>
sys@10g:SQL> !cp /ora/data001/10g/datafile/o1_mf_users_1pdkdtz5_.dbf
/ora/data001/10g/datafile/o1_mf_users_1pdkdtz5_.dbf.bkp

sys@10g:SQL>
sys@10g:SQL> alter tablespace users end backup;

Tablespace altered.

sys@10g:SQL>
sys@10g:SQL> alter system switch logfile;

System altered.

sys@10g:SQL> alter system switch logfile;

System altered.

sys@10g:SQL> alter system switch logfile;

System altered.

sys@10g:SQL> alter tablespace users offline;

Tablespace altered.

sys@10g:SQL> !cp /ora/data001/10g/datafile/o1_mf_users_1pdkdtz5_.dbf.bkp
/ora/data001/10g/datafile/o1_mf_users_1pdkdtz5_.dbf

sys@10g:SQL> recover tablespace users;
ORA-00279: change 12173563 generated at 03/24/2006 14:49:59 needed for thread 1
ORA-00289: suggestion : /ora/arch/10g/1_87_584918376.dbf
ORA-00280: change 12173563 for thread 1 is in sequence #87

Specify log: {=suggested filename AUTO CANCEL}

sys@10g:SQL> select a.sid,
2 substr(a.program,1,25) program,
3 b.file#,
4 c.type
5 from v$session a,
6 v$datafile b,
7 v$lock c
8 where a.sid = c.sid and
9 b.file# = c.id1 and
10 c.type = 'MR';

SID PROGRAM FILE# TY
---------- ------------------------- ---------- --
167 oracle@machine (DBW0) 8 MR
167 oracle@machine (DBW0) 2 MR
167 oracle@machine (DBW0) 3 MR
167 oracle@machine (DBW0) 1 MR
167 oracle@machine (DBW0) 5 MR
167 oracle@machine (DBW0) 6 MR
167 oracle@machine (DBW0) 7 MR
153 sqlplus@machine (TNS V1- 4 MR

8 rows selected.

Log applied.
Media recovery complete.

sys@10g:SQL> select a.sid,
2 substr(a.program,1,25) program,
3 b.file#,
4 c.type
5 from v$session a,
6 v$datafile b,
7 v$lock c
8 where a.sid = c.sid and
9 b.file# = c.id1 and
10 c.type = 'MR';

SID PROGRAM FILE# TY
---------- ------------------------- ---------- --
167 oracle@machine (DBW0) 1 MR
167 oracle@machine (DBW0) 2 MR
167 oracle@machine (DBW0) 3 MR
167 oracle@machine (DBW0) 5 MR
167 oracle@machine (DBW0) 6 MR
167 oracle@machine (DBW0) 7 MR
167 oracle@machine (DBW0) 8 MR

7 rows selected.

sys@10g:SQL> alter tablespace users online;

Tablespace altered.

sys@10g:SQL> select a.sid,
2 substr(a.program,1,25) program,
3 b.file#,
4 c.type
5 from v$session a,
6 v$datafile b,
7 v$lock c
8 where a.sid = c.sid and
9 b.file# = c.id1 and
10 c.type = 'MR';

SID PROGRAM FILE# TY
---------- ------------------------- ---------- --
167 oracle@machine (DBW0) 1 MR
167 oracle@machine (DBW0) 2 MR
167 oracle@machine (DBW0) 3 MR
167 oracle@machine (DBW0) 4 MR
167 oracle@machine (DBW0) 5 MR
167 oracle@machine (DBW0) 6 MR
167 oracle@machine (DBW0) 7 MR
167 oracle@machine (DBW0) 8 MR

8 rows selected.

As you can see, the MR lock was acquired by the recovery session and was not relinquished until the tablespace was brought online. At that point the Database Writer assumed ownership of the MR lock for the file in question (file_id=4). In this manner Oracle can ensure that file media recovery is only being performed by a single recovery session; and that recovery cannot be invoked against an online file. These MR locks are indeed annoying as they pollute our otherwise clean v$lock dynamic performance view. But, they serve a very vital function in preserving the integrity of our data.

Unused Indexes and Scalability

2006-03-21T11:29:00.000-05:00

I was asked an interesting question yesterday. The gist of the question is: Can eliminating unused indexes reduce latching in the database? The answer is likely “yes” in most production environments. Even though an index may not be used in an access path for any SQL in the database, the index is still maintained (in most cases) when the indexed columns of a table are modified. Even if we reduce latching owed to unused indexes, how does this improve the scalability of an application?

Let’s assume you have an unused B-tree index on a table. If an insert, delete or update affects the indexed columns for this index, then more times than not this index will need to be modified to accommodate the change. If this index is modified Oracle must find the relevant index block(s) to change. To do this it must read the root node of the index segment, probably a branch block and lastly the leaf block(s). All of this activity requires Oracle to hash the DBA (data block address) for each visited index block to determine if the block is already in the buffer cache.

Which latches are required for this investigation? Well, the DBA is hashed to determine which cache buffers chain on which the block “should” reside if it is in the buffer cache. Once the applicable cache buffers chain latch is acquired Oracle searches this linked list (cache buffers chain) , protected by that latch, hoping to find the current version of the leaf block (let’s not rule out a branch block change as well). If it is not found on the relevant cache buffers chain, then the block is not in the buffer cache. Therefore, the block must be read from “disk” and placed into the buffer cache. Oracle needs a free buffer to write the block into. This is accomplished by searching the applicable cache buffers LRU chain for a free buffer. The cache buffers LRU chain structure is protected by a cache buffers LRU chain latch. Once this latch is acquired and the block is written to the buffer cache, the block can be modified.

Of course, modifying the block requires Oracle to first allocate space in the log buffer to accommodate our change. Oracle will need to acquire the redo allocation latch to allocate this space in the log buffer. Then the redo change vectors, constructed in the PGA, are written onto the log buffer using a redo copy latch.

To maintain this unused index Oracle has performed unnecessary logical I/O (root, branch and leaf index blocks as well as undo segment header and undo segment blocks) and unnecessary latching. Moreover, unnecessary redo has been generated along the way.

Latching plays a major role in the ability of an application to scale, as concurrency is impacted via latch contention. Latches serialize operations on structures to ensure the integrity of that structure. So they are our friends, but they can easily become areas of frequent contention. The less latching Oracle does the greater the probability a process will succeed in getting the latch it needs on its first attempt to acquire it. Concurrency and, thusly, scalability are benefited. Latch contention can result in a process spinning (CPU intensive) on latches and sleeping (response time inhibitor) until the latch is available. Processes are not managed by a queue for latch access. That is, a process is not guaranteed a position in a queue based on the point in time it attempted to acquire the latch. It either acquires it immediately or it does not.

So, before we go adding indexes we think might be useful, we might want to consider the inherent maintenance overhead against the scope of any performance gain. If scalability is deemed paramount, each database or SQL change should be made with a high-level understanding of the resultant latching fresh in our minds.

Terrific Presentations on Oracle Redo

2006-03-15T19:19:00.000-05:00

Recently, I came across a couple of really terrific presentations on redo internals and redo reduction by Julian Dyke. If you have any interest in some of the more subtle details of Oracle redo, I highly recommend you take some time to go through these presentations. In typical Julian Dyke form, they are lucid, concise and very informative.

They are available on the web free of charge. Check them out and let me know what you think!

Hotsos 2006: Day 3 – Revisited

2006-03-14T14:30:00.000-05:00

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.

Hotsos 2006: Day 3

2006-03-09T02:11:00.000-05:00

I started off the day with a pastry, a glass of orange juice and an hour of Tom Kyte. Not a bad way to start a day, now is it? Tom gave a presentation on the performance impact of the LOG ERRORS clause in 10GR2. This feature of Oracle is really nice. As Tom said, "It will change the way we extract, transfer and load data in the future." I totally agree. The gist of this new feature is the ability to log error messages (and other relevant row data) for the failed rows in a multi-row DML statement. Much like the .bad file in the SQL*Loader tool gives us the ability to record failed rows outside the database, the LOG ERRORS clause permits us to store essentially the same data in an "error" table. Circa, 10GR2 no longer do our multi-row DML operations need to be an "all or nothing" proposition. It is perfectly permissible for a pre-configured number of rows to fail. Moreover, the logging of errors is an autonomous transaction. That is, if you rollback the DML the error data can persist in the "error" table.

As for the performance impact owed to the LOG ERRORS clause, it really varies and can be a function of the number of errors encountered and/or the path (conventional or direct) taken by the statement. In general, DML error logging should use direct path processing. Obviously, it is abundantly safe if a high percentage of your data is clean. If you are running 10GR2 this is definitely an option to seriously consider for your data loads.

For me there was one unresolved issue with using this option. Based on Tom's testing, updates that use the LOG ERRORS clause generate nearly 2 times the amount of redo and nearly 2 times the undo when compared to a variety of other more clunky "coding" options that render the same result. Naturally, I will need to determine why this is the case :) When I get the answer I will create a follow-on post. Like I always say, Oracle cannot hide the nature of the redo!

In the afternoon Doug Burns gave another very good presentation titled "How Many Slaves? (Parallel Execution and the Magic of Two)." While I haven't consumed the entire paper that accompanies the presentation it is very obvious Doug has done his homework. What I truly appreciate with this topic, in particular Doug's paper and presentation material, is the rigor he has imposed on his investigations. While Doug will be the first to tell you that there are many more testing scenarios that can be envisioned (countless) and our results will be platform-specific, the key is to know your system and understand that the degree of parallelism is not an exact science. I encourage you to read his paper to get the details as they are extremely interesting. And remember, just because your system affords you the ability to run potentially higher degrees of parallelization to achieve greater response time this doesn't give us a license to abuse our systems. Everything has a cost. We should carefully evaluate our "need" for fast throughput for jobs deemed worthy of parallelizing as we could easily starve other well deserving processes of CPU.

Tomorrow, Jonathan Lewis is giving a training day on "Understanding and Assisting the Cost Based Optimizer." I am sure this will be a terrific ending to a fantastic week of learning from some of the world's best Oracle minds.

Hotsos 2006: Day 2

2006-03-07T14:23:00.000-05:00

Okay. Day 2 of the 2006 Hotsos Symposium is in the books. It proved to be yet another really good day of learning and sharing of technology. One of my favorite aspects about the symposium format is the ability to get so many varied opinions on such a wide variety of technical topics by simply strolling the floor. You might have a fleeting conversation with someone you have never met and learn something new. That is cool!

Anyway, as for the proceedings I started off in Bob Sneed's "I/O Micro-benchmarking with Oracle in Mind" presentation. He made a few really good points pertaining to common micro-benchmarking errors:

We often run irrelevant tests.
Failure to pay attention to initial pre-test state influences can sabotage your benchmark (Beware of the warm cache).
Failure to repeat tests to ensure repeatability of results.
Don't leap to conclusions.

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:

Lack of understanding of the nuisances of PQ.
Deterred by horrible early experiences (i.e. cpu starvation, unpredictable results).
Community's resistance to change.
The very fact that it is not useful in all environments.

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.

Hotsos 2006: Day 1

2006-03-06T19:00:00.000-05:00

Well, the education portion for Day 1 at Hotsos 2006 has ended (at least formally). There was some very good information given today. However, I think the keynote speech given by Ray Lane, former President and COO of Oracle Corporation, was potentially the most interesting. It was not a techy, nuts and bolts delivery but he shared some very fresh ideas borne from his vast experience in the industry.

Mr. Lane spoke of the software paradigm shift in which we are currently in the midst. He quoted his seven laws for successful software in the future:

Serves Individual Need
Viral/Organic Adoption
Contextual Personalized Information
No Data Entry/No Training Required
Utilitizes Community, Social Relationships
Delivers Instantaneous Value
Minimum IT Footprint

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:

Disks are getting bigger and faster. But, the bigness is growing faster than the fastness. That is definitely something to keep in mind when capacity planning is on the table.
We tend to price our DASD by the gigabyte/megabyte. Why aren't we costing I/O? That is, what does an I/O cost in our production environments?

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.

Hotsos 2006: Day 0

2006-03-06T00:27:00.000-05:00

Well, after a few unintentional "detours" my colleagues and I arrived at the Omni Mandalay too late for registration. Tomorrow morning will have to suffice. However, we did get an opportunity to meet for a while with a former DBA colleague Dan Furnier. It is always nice to catch up with former co-workers.

There is a full day of learning on tap for tomorrow. I am really looking forward to tomorrow's presentations - so much to learn and so little time.

Time to turn in. I need to maximize my fresh brain cells for tomorrow. Hotsos 2006 is officially here!

RMAN is Absolutely Fuzzy

2006-03-03T16:59:00.000-05:00

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.

LogMiner and Dynamic Performance Views in Harmony

2006-02-28T14:56:00.000-05:00

I want to revisit a topic covered in my paper The Oracle Redo Generation. More specifically, the notion of determining a transaction’s redo size from LogMiner output. A few folks have emailed me with questions so I think a clarification post is warranted, as I anticipate others might have similar questions.

Like I state in the paper, you can use v$mystat/v$sesstat dynamic performance views or LogMiner to discern the volume of redo a transaction generates. These two measures should always agree based on my testing. Let’s examine a very simple transaction that attempts to perform an insert into a test table t but fails on a unique constraint. I chose a statement that fails a constraint to exhibit the undo replay in the redo stream. We will list the redo size data from v$mystat before and after the transaction.

eric@10g:SQL> desc t
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COL1                                      NOT NULL NUMBER
 COL2                                               NUMBER

eric@10g:SQL> select * from t;

      COL1       COL2
---------- ----------
1 1

eric@10g:SQL> select constraint_name, constraint_type, status, deferrable,
rely from user_constraints where table_name = 'T';

CONSTRAINT_NAME                C STATUS   DEFERRABLE     RELY
------------------------------ - -------- -------------- ----
SYS_C0010643                   P ENABLED  NOT DEFERRABLE


eric@10g:SQL> select 'REDO_START',
(select value from v$mystat where a.statistic#=statistic#) value
from v$statname a  where name = ('redo size');

'REDO_STAR      VALUE
---------- ----------
REDO_START          0

eric@10g:SQL> select 'ENTRY_START',
(select value from v$mystat where a.statistic#=statistic#) value
from v$statname a  where name = ('redo entries');

'ENTRY_STAR      VALUE
----------- ----------
ENTRY_START          0

eric@10g:SQL> insert into t values (1,1);
insert into t values (1,1)
*
ERROR at line 1:
ORA-00001: unique constraint (ERIC.SYS_C0010643) violated

eric@10g:SQL> select 'REDO_STOP',
(select value from v$mystat where a.statistic#=statistic#) value
from v$statname a  where name = ('redo size');

'REDO_STO      VALUE
--------- ----------
REDO_STOP        808

eric@10g:SQL> select 'ENTRY_STOP',
(select value from v$mystat where a.statistic#=statistic#) value
from v$statname a  where name = ('redo entries');

'ENTRY_STO      VALUE
---------- ----------
ENTRY_STOP          3

eric@10g:SQL> select sequence#, status from v$log;

 SEQUENCE# STATUS
---------- ----------------
       272 ACTIVE
       273 CURRENT
       271 INACTIVE

eric@10g:SQL> alter system switch logfile;

System altered.

eric@10g:SQL> select sequence#, archived from v$log where sequence#=273;

 SEQUENCE# ARC
---------- ---
       273 YES

eric@10g:SQL> connect / as sysdba
Connected.

sys@10g:SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LogFileName =>
'/tmp/eric_273.arc', Options => dbms_logmnr.ADDFILE);

PL/SQL procedure successfully completed.

sys@10g:SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);

PL/SQL procedure successfully completed.

sys@10g:SQL> select scn,data_obj#,substr(operation,1,20) OP,
rbablk,rbabyte from v$logmnr_contents;

            SCN  DATA_OBJ# OP                       RBABLK    RBABYTE
--------------- ---------- -------------------- ---------- ----------
       10237995          0 START                         2         16
       10237995      64105 INSERT                        2         16
       10237995      64105 DELETE                        3         80
       10237996          0 ROLLBACK                      3        248

sys@10g:SQL> alter system dump logfile '/tmp/eric_273.arc';

System altered.

sys@10g:SQL> exit

Okay, let’s review what we see. The v$mystat dynamic performance view claims that 808 bytes of redo has been generated from this failed transaction. Moreover, v$mystat claims our user session has created 3 redo entries. Let’s reconcile this information with the output from LogMiner. The only change vectors in the mined file are those associated with my transaction as I was the only user on the database. I switched into the mined log before starting my transaction and switched out immediately after.

The test platform OS block size is 512 bytes. Therefore, each redo block (RBABLK) is comprised of 512 bytes. The RBABYTE (OFFSET) value represents the offset into the RBABLK where a particular redo record begins. The first 16 bytes of redo in each RBABLK are reserved for header information. This limits the actual space for transaction redo entries to 496 bytes per RBABLK.

If we walk through each entry we should be able to calculate the 808 bytes of redo v$mystat claims my session created. The START and INSERT redo records are the very same redo record. LogMiner simply gives us the START line for our benefit. It comes in handy when reviewing a log file with many transactions. This means we should not double count (indeed even hard to do given the method of calculation) the redo, but only consider the INSERT redo record as our “redo start” position. LogMiner tells us that the INSERT statement consumes redo from RBABLK=2 OFFSET=16 and spans through RBABLK=3 OFFSET=79. To calculate the redo owed to the INSERT redo record we add the redo owed to the INSERT in RBABLK=2 to that in RBABLK=3. That is, 496 bytes for RBABLK=2 and (80-16=64) bytes for RBABLK=3. Therefore, the total redo attributed to the INSERT redo record is 496 + 64 = 560 bytes.

Why do we see a DELETE operation? Well, remember the INSERT failed a uniqueness constraint and needed to be rolled back. The DELETE operation is the undo portion of this transaction. It is easy to see that the DELETE operation accounts for only (248-80=168) bytes. This means our INSERT and DELETE operations comprise 560 + 168 = 728 bytes. We can then deduce that the ROLLBACK entry comprises the remaining 80 bytes. However, let’s take a slightly different approach so that we can be 100% certain our ROLLBACK marker is indeed 80 bytes.

I used a Solaris egrep command to scrape the log file dump and get at the redo record sizes.

/opt/oracle/admin/10g/udump # egrep 'REDO RECORDCHANGE' 10g_ora_10436.trc
REDO RECORD - Thread:1 RBA: 0x000111.00000002.0010 LEN: 0x0230 VLD: 0x05
CHANGE #1 TYP:0 CLS:17 AFN:2 DBA:0x00800009 OBJ:4294967295 SCN:0x0000.009c3824 SEQ:  1 OP:5.2
CHANGE #2 TYP:0 CLS:18 AFN:2 DBA:0x00800a7f OBJ:4294967295 SCN:0x0000.009c3823 SEQ:  1 OP:5.1
CHANGE #3 TYP:0 CLS: 1 AFN:4 DBA:0x01000056 OBJ:64105 SCN:0x0000.009c3805 SEQ:  2 OP:11.2
CHANGE #4 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ:  0 OP:5.19
REDO RECORD - Thread:1 RBA: 0x000111.00000003.0050 LEN: 0x00a8 VLD: 0x01
CHANGE #1 TYP:0 CLS: 1 AFN:4 DBA:0x01000056 OBJ:64105 SCN:0x0000.009c382b SEQ:  1 OP:11.3
CHANGE #2 TYP:0 CLS:17 AFN:2 DBA:0x00800009 OBJ:4294967295 SCN:0x0000.009c382b SEQ:  1 OP:5.11
REDO RECORD - Thread:1 RBA: 0x000111.00000003.00f8 LEN: 0x0050 VLD: 0x01
CHANGE #1 TYP:0 CLS:17 AFN:2 DBA:0x00800009 OBJ:4294967295 SCN:0x0000.009c382b SEQ:  2 OP:5.4

Each redo record has a LEN attribute denoting the length of the redo record in hexadecimal format. The redo byte address or RBA is also given. This can be used to cross reference the RBABLK and RBABYTE information from the LogMiner output to ensure we are getting at the correct data. The redo byte address contains the log sequence#, redo block number and the redo byte offset. Note the first redo record is positioned at redo block 0x2=2 and redo byte 0x0010=16. This corresponds with the first redo record from the LogMiner output RBABLK=2, RBABYTE=16. The LEN attribute for this record is 0x0230=560. This also matches the 560 bytes calculated via the LogMiner output for the INSERT redo record. The next redo record is DELETE operation with 0x00a8=168 bytes. The last redo record at redo block 0x3=3 and redo byte 0x00f8=248 is the ROLLBACK entry. The LEN attribute for this redo record is 0x0050=80. The ROLLBACK redo record is now shown to be 80 bytes that we deduced from LogMiner.

One final comment on this topic, notice there are 3 redo records that constitute this transaction’s redo footprint. I displayed the redo entries statistic before and after the transaction to show that a redo record is synonymous with a redo entry catalogued in the v$mystat/v$sesstat/v$sysstat dynamic performance views. We hear a lot about redo records and change vectors when Oracle redo is mentioned in documentation. Redo records are comprised of one or more change vectors. An SCN is associated with a redo record and not the individual change vectors directly. Additionally, many redo records can have the same SCN.

I apologize for this being such a cryptic read. I promise I perpetually strive to eschew obfuscation ;-)

The Pleasure of Finding Oracle Things Out

2006-02-24T11:09:00.000-05:00

Yesterday I was speaking with a colleague about various technical topics (as we frequently do) and he brought up a good question. He remembered reading that upon the issuance of the ALTER TABLESPACE END BACKUP command Oracle creates a redo record with the begin backup SCN and he wanted to know why Oracle needed to put this SCN in the redo record? More specifically, why doesn’t Oracle create the end backup marker in the redo stream with just the file number? Then during recovery, Oracle could simply read this redo record and consider all “hot-backup-necessary” redo for that file applied?

That seemed like a fair question. I told him that I had seen this begin backup SCN in a log file dump associated with an END BACKUP statement. So, I knew the information he read is correct. However, I could not immediately give him an answer for this mechanic. Of course, the fact that I could not initially get my mind around the mechanic and formulate a response other than, “I don’t really know” bothered me. I assume it is fairly common knowledge that all redo between the BEGIN BACKUP and END BACKUP commands is required for recovery; every good backup and recovery guide tells us this. But, I really didn’t know why the begin backup SCN was required in the redo record created from the END BACKUP statement.

I took pen to paper and reviewed what I knew of the END BACKUP statement.

When issued it tells Oracle to unfreeze the frozen section of the header. Notice I said section. The entire header of each affected file is not immutable during the course of a user-managed hot backup. For instance, the hot backup SCN and checkpoint counter in the header is moving in accord with the database changes to the file(s).
Oracle updates the master checkpoint SCN in the file header and control file with the hot backup SCN (the current SCN for the file). Remember a file in backup mode is still subject to normal modifications, checkpoints, etc.
The status in the file header goes from 0x1 (hot backup mode) to 0x4 (normal online mode).
A redo record for each file in the tablespace is created with the file number and begin backup SCN.
During recovery after the end backup redo record is encountered the file in question has its status updated from 0x1 (hot backup mode) to 0x0 (consistent mode).

Here is an example of a redo record created for a file affected by the END BACKUP statement. I have bolded the begin backup SCN.

REDO RECORD - Thread:1 RBA: 0x000030.00000002.0010 LEN: 0x0038 VLD: 0x01
SCN: 0x0000.004b0b57 SUBSCN: 1 02/23/2006 15:59:59
CHANGE #1 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ: 0 OP:17.1
End backup marker - file:3 scn: 0x0000.004b0b4c

Okay, let me return to my colleague’s question. Why does Oracle place the begin backup SCN in the redo record and not just the file number? Consider the following:

At time y we take a complete hot backup of our production database. Of course we cannot instantaneously take a backup at a single point in time as it takes some time to get all tablespaces in hot backup mode. But, for the sake of this example let y be the “time” we associate with our most recent backup of the database. We also have complete hot backups of our database at times y-n, and y-2n where n is a day, week or some other arbitrary time interval.

What happens if we restore all but one of our files from the backup taken at y. Maybe, we had a corrupt version restored for one of the files and we had to restore a copy from the y-2n backup. That is, we have one file restored from y-2n and the rest from y. Our recovery would start using log files from y-2n as that is the oldest file in our restored database. In our redo stream we have redo records for the END BACKUP commands for the files backed up during the y-2n backup. Remember, each of these redo records contains this mysterious begin backup SCN. While rolling forward Oracle would hit these redo records from the y-2n complete hot backup. What would happen if Oracle simply put the file number in the redo record for the END BACKUP command? It would immediately update the file headers to 0x0 (file is consistent and needs no further “hot-backup-necessary” redo) for the files backed up at y. Well, this would cause major issues. The recovery would stop after all redo records containing the end backup marker from the y-2n backup had been encountered. However, Oracle provided another level of protection from this scenario, the begin backup SCN.

From the previous scenario, with the begin backup SCN embedded in the redo record for each file, Oracle would continue to recover through the necessary redo. It would consider a file consistent only when it found the redo record for the end backup statement that contained the begin backup SCN that corresponds with the begin backup SCN stored in the file header. The begin backup SCN is a portion of the frozen section of the file header when the BEGIN BACKUP command was issued.

All is well, now that it feels I have gotten my mind around this one.

Oracle, It Leads By Example (a small rant)

2006-02-19T23:51:00.000-05:00

Reliable

1 : suitable or fit to be relied on : dependable
2 : giving the same result on successive trials

“To err is human...”

What is really meant by this? Is it a conscious awareness and blatent admission by humanity that we are less than perfect? Is it the standard appeal to the listener’s pathos? From a reliability perspective maybe we are saying humans are only N nines reliable. Wouldn’t that be an interesting metric to use when interviewing a candidate for a DBA position? I think so.

Interviewer: “Great. Great. You have a nice understanding of latching and appear to have a good grasp on diagnosing systemic and localized database performance issues. Very impressive.”

Interviewee: “Thank you very much. I read a lot of technical material. It keeps me sharp and fresh.

Interviewer: “However, I must admit there is one issue.”

Interviewee: “Oh. Okay. What is it?”

Interviewer: “Yes, it says here on your reliability report that you are only 2 nines reliable.”

Interviewee: “Dangit! You get those reports?”

Well, as it turns out, today I had an experience in my non-occupational life where a person was less than reliable. Admittedly, reliability is a pet peeve of mine. I couldn’t help but draw a quick analogy for my wife when expressing my dissatisfaction. I told her that sometimes I wish people were as dependable as the Oracle databases I support. She laughed of course. But, I meant it! Hmmm, maybe that is why she laughed.

The Oracle relational database management system is indeed a very reliable and dependable thing. Sure Oracle has imperfections we like to call bugs and it is certainly not perfect, but if left to its own devices it is an amazingly reliable and resilient system. I have, on several occasions in my career, seen the Oracle RDBMS “outlast” the OS on which it operates. For example, I can recall a simple reboot that eliminated systemic latch contention. However, this measure was only taken AFTER the database had been bounced several times; each time the database would escalate to the same condition. This is probably a good topic for a separate post.

Consider for a moment the complexity of the Oracle RDBMS. In Oracle10g it can manage your files and storage automatically (ASM), essentially a file system and a volume manager. Wait, it gets better. It can also automatically distribute the files in an attempt to balance I/O for optimal performance. Moreover, while tending to our storage needs it simultaneously performs the vastly more complicated tasks of memory management, inter-process communication, queue management and resource concurrency through an extremely intricate binary dance of code and control structures. All this is required for me to simply ask the database a question. Just crack open the Oracle Concepts Guide and you’ll soon be amazed at the high-level management performed by the system. There is no need swimming through the thousands of pages comprising the complete documentation set to get the gist. This mountain of complexity notwithstanding, the Oracle database is incredibly reliable and can weather the punishment of the most poorly written applications for weeks, months or even years.

In all fairness to humanity we are systems that are orders of magnitude more complex than the Oracle RDBMS. The ability to predict the behavior of a system, I suspect, is increasingly inhibited by the complexity therein, e.g. quantum mechanics. I am sure this sentiment is not lost on your local weather authority. So maybe I shouldn’t hold people to a reliability standard analogous to that of the Oracle RDBMS. But, in which release will Oracle fix my furnace?

By the way, “…to forgive is divine.”

Reliable Hot Backups Without Oracle Intervention?

2006-02-16T12:53:00.000-05:00

In most cases when we need to take a hot backup of an Oracle database we employ either the user-managed hot backup approach using the ALTER TABLESPACE BEGIN BACKUP facility or we simply take an RMAN backup. Of course, there are measures that can be taken to hot backup your database using hardware mirrors by enlisting a combination of the ALTER SYSTEM SUSPEND, ALTER TABLESPACE BEGIN BACKUP commands and vendor specific mirror control interfaces. However, each of these approaches has a downside.

ALTER TABLESPACE BEGIN BACKUP

1. Redo generation overhead and potential redo infrastructure contention.
2. Backup processing consumes production server resources.
3. To minimize performance impacts to production iterating through your tablespaces can take quite some time.

RMAN

1. Backup processing consumes production server resources.
2. Careful planning to ensure the RMAN metadata is maintained, i.e. frequent control file and/or RMAN catalog backups.
3. Additional layer of backup and recovery abstraction.

ALTER SYSTEM SUSPEND and Hardware Mirrors

1. Redo overhead associated with the recommended ALTER TABLESPACE BEGIN BACKUP.
2. All tablespaces must be in backup mode prior to splitting; once again redo overhead.
3. Suspension of all I/O causing immediate outages to application processing until your mirror is split. While this suspend/split might only take minutes, some 24x7x365.25 applications cannot afford zero application activity on a nightly or semi-nightly basis.
4. The cost of a third mirror. Ideally we don’t want to split our primary mirror as we immediately become susceptible to media failure during the time the split mirror is out of sync with your production database.

It would be nice to avoid as many of these side effects as possible in your backup strategy. That is, could we take a "hot backup" that 1) Does not require our tablespaces to be placed in hot backup mode 2) Does not required ALL application data to be suspended during a portion of the backup phase and 3) does not require another layer of backup and recovery abstraction via additional Oracle metadata maintenance?

Yes. EMC has a really good suite of products in their TimeFinder [tm] solution set that can accommodate. Within the TimeFinder solution set is the notion of a Composite Group [tm]. The Composite Group can be defined for the set of primary devices comprising your production database paired with Business Continuance Volumes (BCV), or simply, software controlled mirrors. When you synchronize your primary devices with their respective paired BCV devices and initiate a consistent split operation the EMC subsystem suspends WRITE operations to your primary devices only for the duration of the split operation. However, the split is for a consistent point in time and typically takes less than a few seconds. In recent versions of the TimeFinder product read activity is permitted to flow. This differs from the ALTER SYSTEM SUSPEND approach in that Oracle cannot guarantee the immediate termination of I/O and the read activity is necessarily suspended. The end result of this consistent split is an “aborted” database on the mirror.

How do we convert this “aborted” image of the database on the mirror to a database that is meaningful for backup and recovery? Startup the database in mount mode and issue the RECOVER DATABASE command. Remember, the online redo logs are preserved in the synchronization process to the same point in time as the data files and control files. The RECOVER DATABASE command makes the database consistent with respect to a single point in time. As a matter of fact, the file headers have consistent stop SCNs and a file status flag of(0x0). Consequently, your mirror taken while the database was up and functional (1-3 seconds of write suspension) has been converted to a consistent backup. Please note, if you intend to backup your database(s) from the mirror devices, you cannot open the database on said devices as that would rollback transactions that, in production, might have been committed. Naturally, there are license fees associated with TimeFinder and the third mirror needs to be purchased, among other considerations. But these additional expenditures can be evaluated against the benefits of the solution to determine if the product is worth the investment.

This stuff really works and is very reliable!

ORA-00001.5: unique constraint NOT violated

2006-02-09T15:32:00.000-05:00

Recently, I came across a very interesting “feature” in Oracle – prescience. Yes indeed, this newly discovered and bizarre behavior of Oracle borders on precognition. Just when I thought Oracle only knew how to operate in the now, and how to deal with the past, it can tell the future! Okay already, enough with the melodrama and on to the experience.

I was testing a particular multi-row update scenario in an attempt to discern Oracle’s approach to managing the redo. I used LogMiner and a log file dump to help understand this mechanic. The test involved a heap table with two columns and a primary key enforced using a unique index. The table was loaded with ten rows.


eric@erice:SQL> desc parent_table_1
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
COL1                                      NOT NULL NUMBER
COL2                                               NUMBER

eric@erice:SQL> select * from parent_table_1;

      COL1       COL2
---------- ----------
         0          1
         1          1
         2          1
         3          1
         4          1
         5          1
         6          1
         7          1
         8          1
         9          1

10 rows selected.

eric@erice:SQL> select index_name from user_indexes where table_name = 'PARENT_TABLE_1';

INDEX_NAME
------------------------------
PK_PARENT_TABLE_1

eric@erice:SQL> select constraint_name, constraint_type, deferrable, validated, rely, index_name
from user_constraints where table_name = 'PARENT_TABLE_1';


CONSTRAINT_NAME                C DEFERRABLE     VALIDATED     RELY INDEX_NAME
------------------------------ - -------------- ------------- ---- ----------------------
PK_PARENT_TABLE_1              P NOT DEFERRABLE VALIDATED          PK_PARENT_TABLE_1

I wanted to understand the redo mechanics as it related to constraint validation for a multi-row update against PARENT_TABLE_1 using the following simple update statement:


eric@erice:SQL> update parent_table_1 set col1=col1+1;

10 rows updated.

eric@erice:SQL> commit;

Commit complete.

eric@erice:SQL> select * from parent_table_1;

      COL1       COL2
---------- ----------
         1          1
         2          1
         3          1
         4          1
         5          1
         6          1
         7          1
         8          1
         9          1
        10          1

10 rows selected.

eric@erice:SQL> select * from table(dbms_xplan.display());

------------------------------------------------------------------------
 Id   Operation              Name            Rows   Bytes  Cost
------------------------------------------------------------------------
   0  UPDATE STATEMENT
   1   UPDATE               PARENT_TABLE_1
   2    TABLE ACCESS FULL   PARENT_TABLE_1
------------------------------------------------------------------------

I must admit, when I saw all rows updated sans unique constraint violation I was taken aback. This statement should have failed after the first row was processed, right? I ran the test again with the same results. I then interrogated the data dictionary looking for some database condition that might rationally explain this behavior. I was left staring at the screen having yielded my intuition to what must be a quirk, some kind of bug or my ignorance. How did Oracle know that my statement would eventually yield a set of unique keys? Not all was lost. Oracle couldn’t hide the redo!

Log file dumps are not pretty and take a couple “reads” before you get a handle on what is happening. To display all the applicable dump here would consume more space than desired. Thankfully, LogMiner can format much of the information for us.


eric@erice:SQL> select scn,rel_file#,data_blk#,data_obj#,substr(operation,1,20) OP from v$logmnr_contents;

            SCN  REL_FILE#  DATA_BLK#  DATA_OBJ# OP
--------------- ---------- ---------- ---------- --------------------
        4571216          0          0          0 START
        4571216          3        554       6480 UPDATE
        4571216          0          0       7149 INTERNAL
        4571216          3        554       6480 UPDATE
        4571216          0          0       7149 INTERNAL
        4571216          3        554       6480 UPDATE
        4571216          0          0       7149 INTERNAL
        4571216          3        554       6480 UPDATE
        4571216          0          0       7149 INTERNAL
        4571216          3        554       6480 UPDATE
        4571216          0          0       7149 INTERNAL
        4571216          3        554       6480 UPDATE
        4571216          0          0       7149 INTERNAL
        4571216          3        554       6480 UPDATE
        4571216          0          0       7149 INTERNAL
        4571216          3        554       6480 UPDATE
        4571216          0          0       7149 INTERNAL
        4571216          3        554       6480 UPDATE
        4571216          0          0       7149 INTERNAL
        4571216          3        554       6480 UPDATE
        4571216          0          0       7149 INTERNAL
        4571216          0          0       7149 INTERNAL

eric@erice:SQL> select object_name, object_id from user_objects where object_id in ('6480','7149');

OBJECT_NAME                     OBJECT_ID
------------------------------ ----------
PK_PARENT_TABLE_1                    7149
PARENT_TABLE_1                       6480

From the LogMiner data we can see that Oracle performs alternating modifications on PARENT_TABLE_1 and PK_PARENT_TABLE_1 with two consecutive index modifications at the end. I took the liberty to “scrub” the log file dump using a Solaris egrep command. Annotations were added for the first few entries and last few entries to depict the pattern.


> egrep '^colindex redokeydatakey :' log_file.dmp
col  0: [ 1]  80    (table undo) 80 == 0 in the log file dump.
col  0: [ 2]  c1 02    (table redo) change 0 -> 1
key :(2):  01 80        (index undo) Index key value of 0.
keydata/bitmap: (6):  00 c0 02 2a 00 00   (index undo) old rowid data for key 0.
index redo (kdxlde):  delete leaf row    (index redo)
col  0: [ 2]  c1 02    (table undo) old value is 1
col  0: [ 2]  c1 03    (table redo) new value is 2
update keydata in row    (index undo)
key :(3):  02 c1 02    (index undo) Index key value of 1.
keydata/bitmap: (6):  00 c0 02 2a 00 01  (index undo) rowed data for key 1.
index redo (kdxlpu): update keydata, count=3 (index redo) update keydata operation
keydata : (6):  00 c0 02 2a 00 00  (index redo) new rowid data for key 1.
col  0: [ 2]  c1 03    (table undo) old value is 2.
col  0: [ 2]  c1 04    (table redo) new value is 3.
update keydata in row    (index undo)
key :(3):  02 c1 03    (index undo) Index key value of 2.
keydata/bitmap: (6):  00 c0 02 2a 00 02  (index undo) rowid data for key 2.
index redo (kdxlpu): update keydata, count=3 (index redo) update keydata operation
keydata : (6):  00 c0 02 2a 00 01  (index redo) new rowid data for key 2.
col  0: [ 2]  c1 04    (table undo) old value is 3.
col  0: [ 2]  c1 05    (table redo) new value is 4.
update keydata in row
key :(3):  02 c1 04
keydata/bitmap: (6):  00 c0 02 2a 00 03
index redo (kdxlpu): update keydata, count=3
keydata : (6):  00 c0 02 2a 00 02
col  0: [ 2]  c1 05
col  0: [ 2]  c1 06
update keydata in row
key :(3):  02 c1 05
keydata/bitmap: (6):  00 c0 02 2a 00 04
index redo (kdxlpu): update keydata, count=3
keydata : (6):  00 c0 02 2a 00 03
col  0: [ 2]  c1 06
col  0: [ 2]  c1 07
update keydata in row
key :(3):  02 c1 06
keydata/bitmap: (6):  00 c0 02 2a 00 05
index redo (kdxlpu): update keydata, count=3
keydata : (6):  00 c0 02 2a 00 04
col  0: [ 2]  c1 07
col  0: [ 2]  c1 08
update keydata in row
key :(3):  02 c1 07
keydata/bitmap: (6):  00 c0 02 2a 00 06
index redo (kdxlpu): update keydata, count=3
keydata : (6):  00 c0 02 2a 00 05
col  0: [ 2]  c1 08
col  0: [ 2]  c1 09
update keydata in row
key :(3):  02 c1 08
keydata/bitmap: (6):  00 c0 02 2a 00 07
index redo (kdxlpu): update keydata, count=3
keydata : (6):  00 c0 02 2a 00 06
col  0: [ 2]  c1 09
col  0: [ 2]  c1 0a
update keydata in row
key :(3):  02 c1 09
keydata/bitmap: (6):  00 c0 02 2a 00 08
index redo (kdxlpu): update keydata, count=3
keydata : (6):  00 c0 02 2a 00 07
col  0: [ 2]  c1 0a    (table undo) old value is 9.
col  0: [ 2]  c1 0b    (table redo) new value is 10.
update keydata in row    (index undo)
key :(3):  02 c1 0a    (index undo) Index key value of 9.
keydata/bitmap: (6):  00 c0 02 2a 00 13  (index undo) rowid data for key 9.
index redo (kdxlpu): update keydata, count=3 (index redo) update keydata operation.
keydata : (6):  00 c0 02 2a 00 08  (index redo) new rowid data for key 9.
key :(3):  02 c1 0b    (index undo) Index key value of 10.
index redo (kdxlin):  insert leaf row  (index redo)
keydata: (6):  00 c0 02 2a 00 13  (index redo) new rowid data for key 10.

As you can see, Oracle has taken the following approach in managing the index while performing this update.

1. Update table data (col0) for row 1 from 0 to 1.
2. Delete leaf row for index entry with key value of 0.
3. Update table data (col0) for row 2 from 1 to 2.
4. Update the key data for the index entry that has a key value of 1 with the rowid of the row updated by step 1.
5. Update table data (col0) for row 3 from 2 to 3.
6. Update the key data for the index entry that has a key value of 2 with the rowid of the row updated by step 3.
....
Continue associating rowid values for the index entries that correspond to the new table values until the final row is to be updated.
....n. After the last row has been updated to a key value that is not already represented by a current index entry, insert a leaf row with the address of the table row updated by step n-1.

Viola! Oracle prevented index key value collisions by taking special measures. This explains what Oracle has done and how Oracle has accomplished it. But, how did Oracle know to do this? Certainly the execution plan for this statement does not give us any insight into Oracle’s index maintenance stategy. It is doing a full table scan on the test table as expected and index maintenance is under the covers. However, this does not mean that the code does not behave differently when parsed under different “conditions.” For example, what if there was not a unique index on COL1, but instead a non-unique index and no primary key constraint. Would that change Oracle’s approach to index maintenance? It turns out that it does indeed. If the conditions are arranged as just mentioned, Oracle performs its "typical" index update maintenance (delete leaf row followed by an insert leaf row) taking no special measures to account for potential index key collisions. Why should it? There isn’t any data condition (i.e. unique index) to tell Oracle to take any precautions.

Wait, have we answered the question “how did Oracle know to do this”? It certainly appears so. Oracle was aware that I was attempting to update the key value associated with a unique index and took an optimistic approach to the index maintenance; giving me the benefit of the doubt that the resultant index entries would still be unique. Oracle did not evaluate my SQL statement and devise an index maintenance plan that was “friendly” based on the excellent prospect that COL1=COL1+1 would ultimately generate a set of unique entries. To verify this I tested the same behavior with the update condition COL1=mod(COL1,2) and Oracle took the same "friendly" approach. That is, Oracle updated 5 table rows before failing. It wanted to accommodate but apparently a tolerance threshold was met. The index and table maintenance in this test was the following:

1. No action was required on index entries for the first two table row modifications. Oracle was okay with modifying 0->0 and 1-> 1 without affecting the index. That is pretty cool!
2. On the third row COL1 was updated in the table to a value of 0. Well, that was a collision so Oracle only deleted the leaf row associated with the key value of 2 (third table row).
3. On the fourth row COL1 was updated in the table to a value of 1. Once again this was a collision so Oracle only deleted the leaf row associated with the key value of 3 (fourth row).
4. On the fifth row COL1 was updated in the table to a value of 0. On this collision Oracle has seemingly "had enough" and rolled back the transaction after failing with an ORA-00001.

In summary, Oracle has simply taken the code path associated with maintaining a unique index given the current execution plan. No fortune telling or precognition, just good schema awareness.

A Brief History of Oracle Time

2006-02-07T11:49:00.000-05:00

Recently, while in my vehicle on the way to work I was lulled into a deeper-than-usual thought stream thanks to a clog of traffic. It was then that I entered into an Oracle cosmological musing. I was pondering the notion of time inside Oracle. Outside of the Oracle database the idea of concurrency, and thusly simultaneity, within the database appear abundantly unambiguous. That is, from the outside different transactions within the same database can affect change on the database simultaneously. In the world outside of Oracle the idea of simultaneity outside of Oracle is relative. My chronicle on the time an event occurs, such as typing this article, might differ substantially from another’s due to my position, motion, etc. We can thank Albert Einstein for this discovery. However, Oracle’s notion of its time and simultaneity is a bit different.

Consider the fact that Oracle only chronicles changes to the database. Yes, even changes created by queries, e.g. delayed block cleanout. Therefore, we are only concerned with Oracle time as it relates to modifications. It we were to rewind Oracle time, via a database restoration, and then roll Oracle time forward we would only see the affects of change being replayed.

How is change affected in the database? We know that all transactions contend for the lone redo allocation latch, per redo thread, to affect their respective changes. Until the redo allocation latch has been acquired a transaction cannot produce change and therefore cannot make any contribution to Oracle time. Once the redo allocation latch has been acquired a transaction can then contribute to Oracle time, its position in Oracle time henceforth solidified. After getting some external time on a redo copy latch and copying its change into the redo buffer the transaction has made an indelible contribution (barring instance failure) to time in Oracle. Although the change is forever imprinted in Oracle time it does not mean the change will be perpetually visible, e.g. an uncommitted change during an instance crash.

How is Oracle time measured? Every change made to the database is chronicled using a system change number (SCN). The SCN is Oracle’s time quantum. We cannot recover to a position in Oracle time to a more granular point than a specific SCN. Is an SCN unique to a particular change? Not necessarily. Many changes from distinct transactions can comprise a single SCN. So, simultaneity in Oracle can be defined as the set of all redo records that are recorded for a single SCN. Therefore, simultaneity in Oracle time is not relative as your change either occurred at an absolute point in Oracle time T or it did not.

How does Oracle map external time to its internal time? What are we instructing Oracle to do when we tell it to recover to a particular point in external time? You can view external time as an index into Oracle time. Each redo record has an external time associated with it. If I want Oracle to recover to an external point in time E, it resolves E to an SCN such that this SCN has the highest external timestamp that precedes E.

Notice, Oracle time is not a continuum. It cannot be arbitrarily divided into equal units. Relativistic time can be arbitrarily divided into seconds, minutes, hours, days, etc. Oracle time is discontinuous because it is necessarily divided along the bounds of each indivisible SCN. Oracle time is analogous to a movie being played at your local theatre. Each frame on the tape can be the analog to an Oracle SCN. For two successive frames many changes on the movie screen are possible. Some frames can contain more change (altered "pixels") than the previous frame. For example, a man pauses to think versus children riding bikes and people crossing streets. So the next time you sit down with a tub of popcorn at the movies whisper to your friend that what he/she is observing is analogous to Oracle time. Be prepared for a strange look in return :)

Backup Control Files - Are they special?

2006-02-02T14:35:00.000-05:00

I read an article today that talked about backup control files. Typically, DBAs don’t deal with backup control files on a daily basis so I thought I would take a look. This article asserts that Oracle requires a RESETLOGS operation after performing recovery using a backup control file because –

Oracle needs to update certain structures in the backup control file before opening the database

I certainly agree that Oracle will update the control file when opening the database and resetting the logs. But, let's try to understand what is updated and why. Before we talk about the criticality of the RESETLOGS post-recovery, using a backup control file, let’s try to define what a backup control file really is. Better yet, let’s first list what a backup control file is NOT:

· An OS image of the control file taken while the database is closed.
· An OS image of the control file taken while the database is open (I just shuddered).
· An OS image of the control file taken while the database is mounted.
· A trace backup of the database control file (not unless a can of tomato sauce, some dough and grated cheese is a pizza).

A backup control file is an image of the database control file. But this image possesses very distinctive qualities.

· It contains a file type flag (value=4) that tells Oracle it is a backup control file.
· It is consistent with respect to a single point in time – thus the file type flag for Oracle’s benefit.
· The Stop SCN markers for each data file record are set to 0xffff.ffffffff. To you and I this is hexadecimal for “not available.”

These qualities assure Oracle that the integrity of the backup control file has been preserved. If a database is open and an OS image is taken of the control file it will most likely be corrupt. In contrast, when a backup control file is created using the ALTER DATABASE BACKUP CONTROLFILE TO ‘’ command (or via RMAN) the acquisition of the control file enqueue ensures the file is static during the operation. The file type flag in the backup control file tells Oracle that it cannot (and should not) rely on its redo thread, checkpoint progress and log file records. By instructing Oracle to recover using a backup control file you are telling Oracle to avoid the redo thread records and log file records in the control file – and for good reason.

Well, what about those control file backups to trace – can you create a backup control file using the SQL dumped to trace? Yes. Simply instruct Oracle to RESETLOGS in the CREATE CONTROLFILE statement. The resultant control file will have the same file type flag setting as the backup control file created using RMAN or ALTER DATABASE BACKUP CONTROLFILE TO ‘’ command. You can get at this file type flag data using the oradebug command.

SQL> oradebug setmypid;
SQL> oradebug dump controlf 10;

Be careful during your recovery because you can very easily convert your unsuspecting current control file into a backup control file – destroying the transactional ancestry of the database via the requisite RESETLOGS operation. Consider the following:

sys@erice:SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
sys@erice:SQL> startup mount;
ORACLE instance started.

Total System Global Area 110070328 bytes
Fixed Size 731704 bytes
Variable Size 88080384 bytes
Database Buffers 20971520 bytes
Redo Buffers 286720 bytes
Database mounted.
sys@erice:SQL> recover database using backup controlfile;
ORA-00279: change 4379115 generated at 02/01/2006 22:41:36 needed for thread 1
ORA-00289: suggestion : /opt/oracle/dba/erice/erice_arch_1.dbf
ORA-00280: change 4379115 for thread 1 is in sequence #1


Specify log: {=suggested filename AUTO CANCEL}
cancel
Media recovery cancelled.
sys@erice:SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


sys@erice:SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done

By issuing recovery using a backup control file I have actually converted the current control file to a backup control file. This does not mean that I could not completely recover this database. All I had to do was issue RECOVER DATABASE USING BACKUP CONTROLFILE and supply the appropriate (preserved) online redo log(s).

Back to the original question – why does Oracle require a RESETLOGS operation post-recovery using a backup control file? Within each control file there are checkpoint progress records that provide Oracle with the on-disk RBA (redo byte address). This tells Oracle the offset into the current redo log file(s) that the LGWR has flushed the redo thread.

Let’s suppose Oracle permitted you to open the database without a RESETLOGS after recovery using a backup control file. Then Oracle must use what it knows to be true about its online redo thread: the log file records, redo stream records and checkpoint progress records. If the redo log files remained intact during your restore and recovery then they probably have previously generated redo. If you have 3 online redo log groups and your backup control file states that the on-disk RBA is half way into group 2 and the existing (non-reset) redo log for group 2 is full of previously generated redo, Oracle would start writing redo to its last know on-disk RBA. Hopefully you see where this is going. Potentially, the last half of redo written to group 2 would correspond to changes post-recovery, i.e. you would have a redo log that is corrupt. There could be a major gap in SCNs between the last entry pre-recovery and the first entry post-recovery within the same redo log file. For this reason, Oracle needs to update the redo thread, log file and checkpoint progress records in accord with the new incarnation of the database.

I am sure we could come up with dozens of scenarios where not resetting the logs would cause corruption. But, I have already given too many words to a post that was intended to be short.

The Oracle Redo Generation V2

2006-01-31T17:13:00.000-05:00

Based on some very valuable feedback I will have the next version of this paper available on this site soon. I did not make wholesale changes but did enhance the paper in suitable areas.

Updated 03 Feb 2006: The Oracle Redo Generation V2 is now available.

SQL*Loader and Redo...

2006-01-30T19:29:00.000-05:00

Conventional path SQL*Loader is a very nice and reliable tool. But, we must be a bit careful if the data we load has a high incidence of failed rows resulting from unique constraint violations. In my recent white paper The Oracle Redo Generation I assert that SQL*Loader can incur serious redo overhead if unique constraint violations abound. More specifically, I make the claim that Oracle will actually reprocess an entire batch of rows (ROWS=n) if only a single row in the batch fails an integrity constraint.

Let's first establish the facts before we try to understand Oracle's rationale. With the aid of two very nice devices, LogMiner and log file dumps, we can easily demonstrate this reprocessing behavior. Certainly, a test case is warranted.

I created a simple test table with a primary key constraint enforced by a non-unique index. The non-unique index is used to show the index-optimistic behavior of Oracle. I staged my 10 rows to be loaded such that the 8th row would fail the primary key constraint. The batch size for the load was ROWS=5. I switched into a new redo log prior to loading the data and switched out immediately after the load. This made my life easier when interpreting the LogMiner data (exponentially easier when swimming through the raw log file dump).

Here is what LogMiner revealed:

SQL> select scn, rel_file#, data_blk#, data_obj#, substr(operation,1,15) OPER, rbablk, rbabyte from v$logmnr_contents;

            SCN  REL_FILE#  DATA_BLK#  DATA_OBJ# OPER                 RBABLK   RBABYTE
--------------- ---------- ---------- ---------- ---------------  ---------- ----------
        3016007          0          0          0 START                     2         16
        3016007          3        394       6459 INSERT                    2         16
        3016007          3        394       6459 INSERT                    2         16
        3016007          3        394       6459 INSERT                    2         16
        3016007          3        394       6459 INSERT                    2         16
        3016007          3        394       6459 INSERT                    2         16
        3016007          0          0       6460 INTERNAL                  3         92
        3016007          0          0       6460 INTERNAL                  3        332
        3016007          0          0       6460 INTERNAL                  4         52
        3016007          0          0       6460 INTERNAL                  4        268
        3016007          0          0       6460 INTERNAL                  4        484
        3016008          0          0          0 COMMIT                    5        204
        3016009          0          0          0 START                     6         16
        3016009          3        394       6459 INSERT                    6         16
        3016009          3        394       6459 INSERT                    6         16
        3016009          3        394       6459 INSERT                    6         16
        3016009          3        394       6459 INSERT                    6         16
        3016009          3        394       6459 INSERT                    6         16
        3016009          0          0       6460 INTERNAL                  7         60
        3016009          0          0       6460 INTERNAL                  7        300
        3016009          0          0       6460 INTERNAL                  8         20
        3016009          0          0       6460 INTERNAL                  8        220
        3016009          0          0       6460 INTERNAL                  8        412
        3016009          0          0       6460 INTERNAL                  9        116
        3016009          0          0       6460 INTERNAL                  9        332
        3016009          0          0       6460 INTERNAL                  9        464
        3016009          0          0       6460 INTERNAL                 10        100
        3016009          0          0       6460 INTERNAL                 10        232
        3016009          0          0       6460 INTERNAL                 10        364
        3016009          0          0       6460 INTERNAL                 10        496
        3016009          3        394       6459 DELETE                   11        148
        3016009          3        394       6459 DELETE                   11        148
        3016009          3        394       6459 DELETE                   11        148
        3016009          3        394       6459 DELETE                   11        148
        3016009          3        394       6459 DELETE                   11        148
        3016009          3        394       6459 INSERT                   11        320
        3016009          0          0       6460 INTERNAL                 12        148
        3016009          3        394       6459 INSERT                   12        388
        3016009          0          0       6460 INTERNAL                 13        132
        3016009          3        394       6459 INSERT                   13        348
        3016009          3        394       6459 INSERT                   13        348
        3016009          0          0       6460 INTERNAL                 14         92
        3016009          0          0       6460 INTERNAL                 14        308
        3016010          0          0          0 COMMIT                   15         28

What is all of this LogMiner stuff telling us? With the help of a log file dump it was apparent that the following occurred. Oracle attempted to bulk-insert the first batch of 5 rows and succeeded. Index maintenance for those five rows followed, as denoted by the INTERNAL operations. Oracle attempted to bulk-insert the second batch. Oracle was optimistic regarding the table data changes and bulk-inserted the 5 rows in the second batch. The index leaf rows were written for the first 3 entries and upon failing the constraint were subsequently purged. This accounts for the twelve INTERNAL operations. This was followed by the bulk-delete against the table. Oracle re-inserted the first two rows of the second batch one-by-one, maintaining the index for each table change vector. It discarded the 3rd row in the second batch and bulk-inserted the remaining 2 rows. Index maintenance for those two rows followed.

If you are keeping score the redo associated with this small 10 row load constituted the following: 12 rows inserted in separate bulk operations and the index maintenance therein, 2 rows inserted in separate change vectors and their index maintenance, 5 rows deleted in a single bulk operation and, yes, the index maintenance associated with these deletes.

To load 10 rows SQL*Loader actually performed 14 inserts, 5 deletes and plenty of index maintenance. If the batch size were much larger then a single unique constraint violation could have caused many more rows to be reprocessed one-by-one.

Based on the LogMiner data it appears Oracle, when reprocessing a failed batch, will employ singleton inserts until the known failed row is encountered. After skipping the failed row Oracle then attempts to bulk-insert the remaining rows in the batch.

Ok, so it is nice to know that Oracle behaves this way. But, why does it? Why the reprocessing of rows? From a redo perspective bulk-inserts are cheaper than singleton inserts for the same number of rows. When Oracle constructs a redo record for a bulk activity there is less overhead per unit of work. More precisely there are fewer change vectors. Look at the RBABLK and RBABYTE columns above in the LogMiner query output. Notice the first 5 row bulk insert is associated with a single redo record, i.e. they have the same RBABLK and RBABYTE values. Actually the first five-row bulk-insert that succeeded and the relevant index maintenance generated 1452 bytes of redo. The two singleton inserts (rows 9 and 10) and its index maintenance generated 1020 bytes of redo. If you extrapolate the redo owed to five singleton inserts for this test case (510*5=2550) the singleton inserts are (2550/1452=1.76) 76% more expensive.

Given the relative redo savings of bulk-inserts (and the resultant performance savings) and the all-or-nothing quality of the single redo record for the bulk activity, Oracle is hedging that your data will not violate uniqueness constraints. In the case where violations are encountered Oracle reprocesses the rows one-by-one until it knows that another bulk-insert can be attempted against the batch. The upside to bulk-inserts can quickly diminish if batches frequently contain rows that will violate a uniqueness constraint.

If your data predisposes conventional path SQL*Loader to violate constraints your redo can be dramatically increased. For more on the redo associated with constraint violations you can get all of the lurid details in The Oracle Redo Generation :) It just so happens that the Oracle Import utility also uses a reprocessing technique.

Hello World

2006-01-28T19:25:00.000-05:00

Welcome to my blog! This blog has been setup to share thoughts, ideas and experiences on Oracle database technology. If you have any feedback on my articles or white papers please share your thoughts. Around every corner lurks another bit (or byte) of knowledge.

However, I reserve the right to whimsically opine on other interest areas such as world events, sports, cosmology, et cetera. I might simply have the urge to share a witticism along the way.

Thanks for stopping by. Let the musing commence!