Proactive DBAs and TEMP space

On the same day that we ran out of FLASH, we later saw our database running out of TEMP space and thought this might be related to the same archive job but in reality it was a user report which was run with invalid parameters so it was just sitting there consuming temp space trying to complete.  In addition to resolving the issue by terminating the session, the DBAs created an alert that day to check if any sessions are taking up more than 6 GB of TEMP space and if anything is found it e-mails the DBA and Support teams.  This is a critically important and proactive alert that has been put into place, which has helped us several times to identify potential issues before they could halt the database until the TEMP space issue was resolved.

Four words that strike terror in the hearts of men (and women)

After having a major system uplift, there are four words I’ve heard over and over again which are stalking my dreams and turning them into nightmares lately.  “We didn’t test that.”  Well, that doesn’t seem too dire really, but the repercussions of this are huge and cross every team that worked on the uplift in the first place.

Who is impacted?

Developers - In some cases, these are the unsung heroes of your uplift and what is their reward after it is completed?  To keep their noses to the grindstone and work a few more weeks off on the fires that keep cropping up.  Doesn’t sound like a real promising job huh?

Projects - Not just the project people, which I'll get back to, but you're now putting off being able to work on your next projects.  It doesn't matter if it is old production fixes or currently pipelined new projects, delays will drain your political capital because you are now missing target deployment dates and the business thinks you are pretty much worthless for not catching all the things you are now responsible for fixing.  Project personnel start feeling burned out just like your developers because they've been on the project forever, and the bigger the project the longer you'll want to keep them around to play clean up for all the new debris coming up.

Support - How many fires do you get on a daily basis?  Multiply that by a factor of 2 or 3 after doing an uplift like this for about a month, and you’ll see what kind of volume you’ll be slowly crushed under when you have people in the new system doing their daily work which they never really replicated in the test environment.

DBAs - During the project and throughout the implementation we've been wearing them thin, and they are now getting no rest by making them look at applying patches and identifying system issues which should’ve been caught months ago so they aren't able to instead be able to focus on things like building new monitoring tools.  Fundamental things like missing critical DB bugs can easily happen because everything in the system is being thrown against the wall without a whole lot of investigation possible for each item.

How can we do better?

We need to be more proactive about showing the business and project teams what has been run in the last 30, 60, 180 days in your system and by whom.  Having these snapshots would be able to show the majority of the daily, monthly and quarterly reporting goals that the project needs to meet.

We can do a better job about documenting out our system flow, so we know what all our parts and pieces are which are required to provide full service to the business.  This way you won't get surprised by a report pushing output to a third party in-house group, to a server you didn't even know was connected to your system.

I'll probably add more as I encounter them, but what would you add to this list?

Users do what users do

If I had to write a book, the first chapter would be titled after this post.  Maybe more accurately, users do what users can do.  Last week I was going to write something up about this type of thing, but it makes SO much more sense after this week and introduces what I'll call Main Tenants of Support.

These Main Tenants are things we support individuals hold to be self evident, even if developers and project personnel don't understand a damn thing about them.

Our case:  We were given scripts from Oracle to drop and recreate a queue table that we were told by our developers wasn't being used since it had built up over 10 years of non-use.

rem stop queue: exec dbms_aqadm.stop_queue(‘AR.AR_REV_REC_Q’);

rem drop queue: exec dbms_aqadm.drop_queue(‘AR.AR_REV_REC_Q’);

rem drop queue table: exec dbms_aqadm.drop_queue_table(‘AR.AR_REV_REC_QT’);

rem create new queue table: begin DBMS_AQADM.CREATE_QUEUE_TABLE( Queue_table => ‘AR.AR_REV_REC_QT’, Queue_payload_type => ‘SYSTEM.AR_REV_REC_TYP’, storage_clause => ‘TABLESPACE AR’, Sort_list => ‘ENQ_TIME’, Multiple_consumers => TRUE, Compatible => ‘10.2’, comment => ‘Revenue Recognition Table’); end; /

rem create queue: exec dbms_aqadm.create_queue(‘AR.AR_REV_REC_Q’,’AR.AR_REV_REC_QT’);

rem start queue: exec dbms_aqadm.start_queue(‘AR.AR_REV_REC_Q’);

So instead of just purging it once and then letting another 10 years go by before we purge it again, we decided to wrap it into a concurrent program and let it run under 2 parameters:

• drop and recreate queue
• delete records from the queue

In DEV and TEST the report was run in 60-70 hours which seemed like an acceptable time frame for deleting 45-50 million records and rebuilding the queue, since this is something we've never had to do at this type of scale.

What we didn't know:  The queue table is used in base Oracle functionality for invoice processing.  So what you say?  The API for the drop queue was designed to delete all the rows AFTER it locked the object.

Error: Unable to create invoice due to an error: ORA-04021: timeout occurred while waiting to lock object AR.AR_REV_REC_Q
ORA-06512: at "APPS.RA_CUSTOMER_TRX

Why was this a problem:  That meant when we kicked off the concurrent program 10 PM Friday night, it prevented receipts from getting created in Financials from our web payment front end until we saw an alert on Monday shortly before noon that was telling us we had a backlog in receipt creation.  Killing the concurrent request didn't do anything because the process was still alive, so we had to mark the session for kill but the session was still alive.  It did stop what it was doing after getting past the 9 millionth record, but then it popped out to our shell of the concurrent program which was a problem because it found itself in a loop that it had no way out of.  I researched a bit and found a good article from Tanel Poder breaking down a situation a lot like ours, and I had some tricks in my bag from a previous issue involving the Revenue Recognition, to realize that the OS process needed to be killed manually.

What this shows us about our Main Tenant:  We were able to make the mistake of locking the queue.  Oracle told us to do this, so why would we believe it would have an issue?  We did exactly what the application allowed us to do, so we did it.  The same can be said of forms, fields, and just about anything else in the application so we need to be aware of how developers and project analysts (and Oracle) allow users to interact with the application.  Did we build a form that allows blind queries?  Does a report spend 3 days figuring out a Hash Join before running the actual meat and potatoes of the report in 15 minutes time?  It is the aftermath of many of these unasked, or unanswered, questions prior to implementation that I have to deal with on a daily basis.

Edit: 8/16/12 - Needed to change the link to Tanel's article since it was somehow linking the search engine URL instead.