Weekend Learning: Block corruption in pre-12c DB versions

Out of a lot of error messages out there, I would think that the "ORA-01578: ORACLE data block corrupted" error is likely the most feared out there for DBAs (of course behind ORA-00600).  Do you want to find out more?  Read the long article "Handling Oracle Block Corruptions in Oracle7/8/8i/9i/10g/11g" on My Oracle Support via note ID 28814.1 to get all the details which also includes a lot of datafile related tables and errors that go into the alert log which are all potential candidates for proactive alerts going forward!

Blog roll update: Iggy Fernandez

Lately my blog roll updates have been pretty technical and many related to Exadata specifically, but today I bring you a different kind of blog!  If you need to brush up on your SQL (or NoSQL) skills and then take them to the next level, you need to check out Iggy Fernandez's blog!  My SQL/NoSQL skills are just fine, there is no reason I need to visit Iggy's page you say?  Wrong you would be!  Not on does his blog cover this subject, but he is an Oracle ACE, regular contributor with the NoCOUG, and author of the Beginning Oracle Database 11g Administration book.  Do you really want to miss out on the posts of an experienced DBA about the subject of SQL or NoSQL?  I would not if I were you!

Who is a developer, and who is a DBA?

This is the question that the oraclenerd, Chet Justice, has burning his in mind so he's asked Tom Kyte and Cary Millsap about where they think they fit in the traditional definitions and I think he was trying to get at the difference between the roles (at least in Cary's interview!).  Of course the comments add some meat to the subject as well, and he links to a few older articles where this has come up before!

Being in Support I think I have a high level outsiders view of the values and traits that make these roles (and the people) successful or not.  Along these lines, I think I'll have to make up my own definition of what makes a truly good Support individual and then share it to see what everybody else thinks!

What traits or attitudes do you think helps make a good developer, DBA, or Support person?

Purge Inactive Sessions and HWM expectations

Recently I ran the Purge Inactive Sessions concurrent program after it had not run for a while, and based on my Workflow purging I wanted to make sure we didn't purge out too much data or else it would be likely the system would hit High Water Mark contention when trying to query the ICX_SESSIONS table.  On the first day I purged out records to leave us above where I thought the HWM contention would occur, but I noticed even after a Gather Table Statistics concurrent program was run there was slight slow down on querying from ICX_SESSIONS but nothing in HWM enqueue yet.  Reporting the issue to the DBAs, they suggested that I continue purging out past what I felt was safe since this was in QA and they were prepared to do a shrink to restore the table(s) affected by this program.

On the next day I ran the Purge Inactive Sessions report again, and as expected, there is now a performance problem with ICX_SESSIONS which I thought was due to purging too many records since a simple SELECT was not returning even after 90 minutes.  Why is that bad performance?  Before purging, the same statement was returning in less than a minute so obviously my statement must have been affected by the HWM enqueue contention right?  Wrong!  Curiouser, and curiouser.  That wasn't what I expected at all.  Since the report was still running, I decided to let it continue deleting before doing anything else and then when it completed I ran my SELECT statement.  Guess what I found?  That statement no longer took 90 minutes, as it was completing in a minute or two, nor did it go into HWM contention even before another Gather Table Statistics was done.

Why did my query take so long to return on the second day?  My best guess is that the system was trying to calculate the number of results in the table, but was unable to do so while I was busy having the report deleting from the table so it was stuck until after the report was completed.  When I do this in another environment prior to going to PROD, I'll be able to look at this closer and hopefully I'll gain more insight as to what was going on!

R12: FND_LOG_MESSAGES

Along the lines of my presentation, and published article, I bring you new details of another vector you need to verify to not bloat your database.  Looking into the space consumption of our database post R12 upgrade, the DBAs found that the table FND_LOG_MESSAGES was taking up 13% of our space in PROD so they found My Oracle Support document 735138.1 which details how to purge this information.  Why is this related to R12 you ask?  Based on some of the data analysis scripts in my article, I substituted in FND_LOG_MESSAGES and found that prior to R12 our monthly total in this table was just under 3.5 million rows added back in 2010.  Yet, the largest volume of data added to this table was in October when we went to R12 of over 320 million rows.  Okay, sure the system churn and processing might be excessive when installed so let's look at the next highest month that's been completed; in November we more than 233 million rows added to FND_LOG_MESSAGES, which is an increase over 67 times between our previous high and the following month after R12 was installed!

I have a few more nuggets of information to share with you about this situation, so stay tuned the next few days if you're interested by this!

EDIT: I realized I forgot to add in the links to my other articles last night!