The Advantages of Patching Part II

This is a second part to "The Advantages of Patching". See the previous entry for more details.

Ok, now that I've gotten the patching issue over with, what's the next step? I'm going to do some testing against the application, of course.


EVENT/ERROR:
Let's see. Running a standard process... yes.. yes... doing good.. should be 20 minutes.. seems a bit longer than usual, but still acceptable... going on to the next process.. oh, that's fine too.. doesn't seem like there's any changes... going on to the next... should be 2 minutes... 5 minutes... waiting... 30 minutes... waiting... waiting... waiting... (after 6 hours) ... still waiting... Oh my. This is NOT GOOD.


[STEPS TO A] SOLUTION (1):
Turning on the feature to get the tracefiles*, and gives the the following contents:

WAIT #4: nam='db file sequential read' ela= 0 p1=7 p2=64377 p3=1
WAIT #4: nam='db file sequential read' ela= 0 p1=7 p2=64384 p3=1
WAIT #4: nam='db file sequential read' ela= 0 p1=7 p2=64414 p3=1
WAIT #4: nam='db file sequential read' ela= 0 p1=7 p2=64428 p3=1
WAIT #4: nam='db file sequential read' ela= 0 p1=7 p2=64436 p3=1
WAIT #4: nam='db file sequential read' ela= 0 p1=7 p2=64441 p3=1
WAIT #4: nam='db file sequential read' ela= 0 p1=7 p2=66425 p3=1
WAIT #4: nam='db file sequential read' ela= 0 p1=7 p2=67596 p3=1
WAIT #4: nam='db file sequential read' ela= 0 p1=7 p2=67751 p3=1
WAIT #4: nam='db file sequential read' ela= 0 p1=7 p2=68079 p3=1
WAIT #4: nam='db file sequential read' ela= 0 p1=7 p2=70166 p3=1
WAIT #4: nam='db file sequential read' ela= 0 p1=7 p2=70380 p3=1
<...>

I know this development server's I/O bandwidth is slower compared to others, but this is ridiculous! Finally curious what the tracefile is saying (tkprof doesnt usually help me on cases where one process is running perpetually forever) and its about time I learned to decipher it out myself. Metalink does provide Note 39817.1 for this. And this little snippet from the raw tracefile:

WAIT #4: nam='db file sequential read' ela= 0 p1=7 p2=64377 p3=1

Is to be taken as :

Waiting under cursor#4
for "db file sequential read"
We waited for 0.00 seconds
For a read of File 7 starting at block 64377 for 1 block

So on and so forth. Unfortunately, it seems that the process just keeps on reading and reading and reading indexes (this is actually 1 b-tree index from a table) and its just keeps on reading single blocks, all the way through since the whole process (at least when I started the trace) started!


[STEPS TO A] SOLUTION (2):
A bit put off (actually an understatement. By this time I was fuming because I was again in a helpless state of incredulity), I finally decided to see how fast this same database would run on another faster development server. I decided to hit two birds with one stone: (a) run the same query on the same server and see if the faster server will help with the slow processing time and (b) see if an unpatched Oracle will do any better.

After much file moving and restoring, I proceeded to run the same SQL query. And the answer comes back to me in less than a second.

By now I was suspicious. Despite the fact the other development server is faster, it is not THAT fast. And, I was already curious at this point. Could it be that the patch somehow skewed something? Although there are other "compulsory" fixes included in the patch, and it does have a fix for "2363247 - UNNECESSARY BITMAP PATH GENERATION AND BITMAP MEM ALLOCATION ", it is a fix, and not an existing bug. I've read and re-read the patch release notes but there seems to be no answer for my question. BUT, it could also not be my "db preparation" that I did for the past 5 days, since I've done this sort of "db preparation" before and it never affected the execution time this drastically.


[STEPS TO A] SOLUTION (3):
First thing done? I extracted the SQL in chunks (it was rather a longish SQL statement, but nothing that would've normally aroused any suspicion or ring any bells) to check and see which exactly in the SQL statement affected this whole process slowly. The statement was something like this:

SELECT A,B,C
FROM
(
SELECT T1.A,T1.B,T1.C
FROM
(SELECT A,B,C,D FROM TABLE_FLAG WHERE FLAG_COL1 = 'E') T1,
(SELECT A,B,C,D FROM TABLE_MAIN WHERE MAIN_COL1 = 'F'
AND MAIN_COL2 > 0 AND MAIN_COL3 = 2 AND MAIN_COL4 = 3) T2
WHERE T1.A = T2.A AND T1.B = T2.B
AND T1.C = T2.C AND TRUNC(T2.D + NVL(T1.D,0) ) - TRUNC(SYSDATE)  = -1

UNION
SELECT T1.A,T1.B,T1.C
FROM
(SELECT A,B,C,D FROM TABLE_FLAG WHERE FLAG_COL1 = 'G') T1,
(SELECT A,B,C,D FROM TABLE_MAIN WHERE MAIN_COL1 = 'F' AND MAIN_COL2 > 0
AND MAIN_COL3 = 2 AND MAIN_COL4 = 3 ) T2
WHERE T1.A = T2.A AND T1.B =T2.B
AND T1.C = T2.C AND TRUNC(T2.D + NVL(T1.D,0) ) - TRUNC(SYSDATE)  = -1
)

From there I did the following:

1. Executed the inner SELECT statement for TABLE_FLAG (that was ok)
2. Executed the inner SELECT statement for TABLE_MAIN (that seemed ok too)
3. Executed both inner SELECT statements with the join (hmmmmm... now this is taking a long time)
4. Finally, with a SELECT statement on hand, I did an explain plan on the same SQL. This is what I got for the slow processing db:

ID OPERATION           OPTIONS        OBJECT_NODE    OBJECT_NAME
-- ------------------- -------------- -------------- ----------------
0  SELECT STATEMENT
1  VIEW
2  SORT                UNIQUE
3  UNION-ALL
4  NESTED LOOPS
5  TABLE ACCESS        FULL                          TABLE_FLAG
6  TABLE ACCESS        BY INDEX ROWID                TABLE_MAIN
7  BITMAP CONVERSION   TO ROWIDS
8  BITMAP AND
9  BITMAP CONVERSION   FROM ROWIDS
10 INDEX               RANGE SCAN                    TABLE_MAIN_IDX1
11 BITMAP CONVERSION   FROM ROWIDS
12 INDEX               RANGE SCAN                    TABLE_MAIN_IDX2
13 NESTED LOOPS
14 TABLE ACCESS        FULL                          TABLE_FLAG
15 TABLE ACCESS        BY INDEX ROWID                TABLE_MAIN
16 BITMAP CONVERSION   TO ROWIDS
17 BITMAP AND
18 BITMAP CONVERSION   FROM ROWIDS
19 INDEX               RANGE SCAN                    TABLE_MAIN_IDX1
20 BITMAP CONVERSION   FROM ROWIDS
21 INDEX               RANGE SCAN                    TABLE_MAIN_IDX2

And this is what I got from the fast database:

ID OPERATION           OPTIONS        OBJECT_NODE    OBJECT_NAME
-- ------------------- -------------- -------------- ----------------
0   SELECT STATEMENT
1   VIEW                               :Q60911003
2   SORT                UNIQUE         :Q60911003
3   UNION-ALL                          :Q60911002
4   HASH JOIN                          :Q60911002
5   TABLE ACCESS        FULL           :Q60911000     TABLE_FLAG
6   TABLE ACCESS        FULL           :Q60911002     TABLE_MAIN
7   HASH JOIN                          :Q60911002
8   TABLE ACCESS        FULL           :Q60911001     TABLE_FLAG
9   TABLE ACCESS        FULL           :Q60911002     TABLE_MAIN

Excuse me??? BITMAP CONVERSIONS?? WHAT THE HECK IS THAT?? I don't even HAVE any bitmap indexes in ANY of my tables!

I googled for bitmap conversions but it didn't help very much though. From what I've read online, these usually occur when the bitmap indexes are existing in the queried table are used for the access path. Unfortunately, both tables I am querying DO NOT HAVE ANY bitmap indexes at all. It seems the engine is doing this out of sheer whim. BUT. Its still interesting to know that b-tree to bitmap indexes conversion do happen. Will need to take note of that and watch it out in the future.


[STEPS TO A] SOLUTION (4):
I finally got sick of all this guessing and database juggling. I've resolved to find out if forcing the same SQL statement (in the slower executing db with default bitmap conversion) to do a full table scan instead of using the default access path (with the bitmap conversions) would result in the same, fast execution time. Not being a db developer and with the familiar like-the-back-of-my-hand on using hints, I googled for it and eventually composed my SQL carefully:

SELECT /*+ FULL (T1, T2)*/ T1.A,T1.B,T1.C
FROM
(SELECT A,B,C,D FROM TABLE_FLAG WHERE FLAG_COL1 = 'E') T1,
(SELECT A,B,C,D FROM TABLE_MAIN WHERE MAIN_COL1 = 'F'
AND MAIN_COL2 > 0 AND MAIN_COL3 = 2 AND MAIN_COL4 = 3) T2
WHERE T1.A = T2.A AND T1.B = T2.B
AND T1.C = T2.C AND TRUNC(T2.D + NVL(T1.D,0) ) - TRUNC(SYSDATE)  = -1

Then I executed it, and the results got back to me in less than a minute. How interesting. And the db engine insists on using bitmap conversions (6 hours vs less than 1 minute)? It must've gone cuckoo along the way.


RESULTS:
As for now, it was down to whether my "5-day preparation for the db" was the one affecting the explain plan, or the patch itself. My best bet was the patch, as there was a bugfix within the patch that DID touch on bitmap indexes (the patch said that it was a patch that would fix the bug, but I would never have thought it'd be the other way around). I uninstalled the Oct2005 Critical Patch Update on the original development server I was testing.

I crossed my fingers.

I ran the explain plan.

And the heavens opened up. The plan was what it should be... a FULL TABLE SCAN! :)

After dancing around the office (well, ok, in my head) I tried older patches first to see if it affected the explain plan for this particular SQL statement in anyway. I ended up with April2005 (ok fine, I didnt try the patch after that).

How interesting. Learned DBMS_REPCAT_RGT, OWA/MODPLSQL, bitmap indexes, bitmap conversions, raw tracefiles, patches, hints? all by just patching? That seems a productive experience.

Moral Lesson? There's a couple:

(1) Always think of the first rule to adding any changes: Do it in a testing/development environment, and test the full functionality of your application, REGARDLESS of what Oracle says.
(2) Patching is good. Aside from making the database more secure and stable (if it works anyway) any dirt, rock or boulder that you'll encounter on your way will also be a good stepping point to learning something new.


*Acknowledgements to http://www.petefinnigan.com for "how to set trace..."

The Advantages of Patching Part I

This is just what this Oracle blog was talking about.

No, I'm not going to quip or drone about how patching will make your Oracle database more secure. Or generate access paths better. Or faster execution times. Or less buffer overflows.

I'm going to talk about how patching is expected and prone to many different errors on deployment, and how its going to help you know more interesting things about Oracle. After all, we're all on a rugged path to glory here. ;)


EVENT/ERROR:
As mentioned, I was patching one of our development databases - with the most recent Oracle Critical Patch Update (October 2005) - and had head on problems with the patching process. Patching the oracle software was smooth, but I encountered some problems when running catcpu.sql (it is a script that needs to be run for each existing database instance under the patched Oracle Home directory for CPUOct2005 Patch) with the following problems:

alter package DBMS_REPCAT_RGT compile
*
ERROR at line 1:
ORA-04043: object DBMS_REPCAT_RGT does not exist

OWA_MESSAGE
--------------------------------------------------------------------------------
No older OWA packages detected or OWA packages too old
Backing up previous OWA packages to: MODPLSQL/owa_restore_testdb.sql


OWA_MESSAGE
--------------------------------------------------------------------------------
No older OWA packages detected or OWA packages too old
OWALOAD File: MODPLSQL/owa_all/30/owaload_patch.sql

Logfile: MODPLSQL/owa_patch_testdb.log


OWA_DBG_MSG
--------------------------------------------------------------------------------
No older OWA packages detected or OWA packages too old;
Shipped OWA version is : 3.0.9.8.5;
OWA packages v3.0.9.8.5 will be installed into your database v81740
Will install wpiutl.sql and owacomm.sql

Uh-oh.

COMMENT:
Its amazing what will prompt you to learn when you receive patching errors. You'd have to have the right disposition of course. In my case, I've undergone through several disposition changes upon receiving the above error:

1. Shock ("Oh my godness, I've wrecked my db! Did I just do THAT?")
2. Fear ("I had to run 5 day's worth of preparation for that DB! now it might possibly be a goner?!??!?! My deadline is tomorrow! nooOOooo!")
3. Frustration (a bit of hair pulling involved here, methinks)
4. Helplessness ("What am I going to do???? I can't find anything about this error!")
5. Reality Check ("Hold on, I've got to stop whining about this. If there's a will, there's always going to be a way. Besides, I still have some sort of backup...")
6. Composture ("Ok. This is a patch. It's systematically deployed. There should be answer to this, regardless...")
7. Engagement ("Let me see what I can do about this..." *type type type search enter*)
8. Determination ("Ok, found some stuff on the missing packages...")

Needless to say, disposition 1,6,7 and 8 is the most acceptable and the most productive. Try not to waste time going through 2,3,4 and 5, unless of course (a) you didn't have backup at all, (b) you immediately tried this on your production database without any testing, or (c) you have a very short tempered and anally retentive supervisor who's itching to fire you at your slight sneeze. Which, by then, you would have to start packing soon and start updating your resume.

Sorry. Can't resist. Didn't mean to scare you like that. :)

The main (side) point is, try to keep a cool and even head. Mistakes and catastrophes happen, whether or not it was caused by you. Just keep thinking forward. It will always help with this kind of job.


[STEPS TO A] SOLUTION:
I went off to Metalink to double check the error messages in Note:333956.1 (Oracle Critical Patch Update October 2005 Pre-Installation Note for Oracle Database) more closely. I scanned and found no similar errors logged for my case.

So that lead me to research on two things:
(1) What's the purpose of DBMS_REPCAT_RGT, and
(2) what is OWA and MODPLSQL?

Incidentally, I also posted a message on a forum to check with other people who may have encountered the same problem. (Can't tell who else is lurking out there with the same problem. Most of us do it, I'm sure.)


RESULTS:
(1) DBMS_REPCAT_RGT = controls the maintenance and definition of refresh group templates for Oracle's replication management API (How interesting, you can actually use this to replicate schemas?) and here's a site about it.
(2) OWA/MODPLSQL= OWA is Oracle Web Agent, and MODPLSQL is a "webserver extension module allowing to create dynamic web pages from PL/SQL packages." For starters, you can find more about it at www.orafaq.com. Here's another related site. Seems like they're both the same thing, and lucky me, we don't use this feature. :)

Work-wise, I can safely breathe.. at least for now.

But further research is in order to figure out what these utilities exactly are.. and they seem like interesting stuff, too :) Will put that on my list.

Additionally, someone DID reply to my post in one of the forums. It feels slightly better to know that its "not just me". Wouldn't you know it, life isn't getting personal with just me :P


Oh but there's more. In the next entry.

As a Newbie DBA...

I was browsing through Tom Kyte's blog when I hit on this commenter, and led me to her blog. Her blog is about newbie DBAs (hence the name, Newbie DBA) and came across one of her entries asking people about how to go about learning Oracle the first time. This prompted me to share my experience when I first encountered Oracle, taken the responsibilities of being a newbie Oracle DBA, the very first time in my working life.

I've already had several drafts in my Oracle blog, but was hesitant to publish them because the entries doesn't seem "right" to begin this blog. But after commenting on Lisa's blog, I thought this was a good way to start my Oracle blog, so let me expand on my comment on her site and tell you my experience in detail...

Hi Lisa! I just found your site via Tom Kyte's, and I'm so relieved to learn I'm not the only one who entered the Oracle DBA realm alone 4 years ago. I was sort of pushed into learning Oracle when my company decided it was more "practical" to have a neophyte, SQLServer familiar DB developer to handle the company database instead of retaining the senior Oracle DBA. The previous senior DBA gave me a couple of notes and commands to clone the db and make sure the standby database was running ok (back then I didn't even had any idea what a standby database, or RMAN, is! I was just taught "commands" e.g. run this shell script to create a backup, run that script to make sure standby is in sync). I've been given the chance to ask the senior DBA questions before he left, but at that point in time I didn't even know natch about Oracle to ask any specific questions. Its been hell and back for me, including a couple of instances when the production DB was just locked and frozen.

Best way to learn? Unfortunately, from my disaster circumstances. Once you're faced with disaster situations several times over, you get tired of "unexpected circumstances" and stop treating the DB like a black box. I agree with what you've said, sticking with one concept at a time helps a lot, because learning them all together in one go will actually leave you beffudled and leave you right at the start.

First thing to learn when placed in a neophyte position and left with a working Oracle db? Build the DB server from ground up without any GUI tools. It'll help you familiarize with the barebone basics (create db, tablespace, rollback segment scripts) and have a development/testing site to play around with.

Second thing to learn? Backup and recovery options. This helps when hell freezes over. Once you know how to recover, usually you can rest easy since you're (sort of) still in control when the db crashes or hangs up. You can recover the data which is most likely the lifeblood of the company. At least it won't get you fired ("errr.. Sorry, I seemed to have lost your precious data" company: "We're also sorry, cause we're going to fire you").

Third thing to learn? Find out why the d*mnned DB is so slow! Starting out with the statspack/utl scripts helps a lot. After that, its research, research and more research! (Jonathan Lewis' Practical Oracle8i really helped a lot on this one)

I'm still learning a lot up until now, but things are picking up.

The best way to learn? Learn from Lisa, be curious, inquisitive, and open minded. Test things out little by little from what you know. Reading the documentation will help, but for the neophyte DBA, it might seem a lot to take in and discourage you from moving on. Read snippets on the topic, but don't ever go too far lest you're one of the people who can hold up to this type of reading.

Hopefully, this blog, as well as Lisa's, and countless number of other newbie blogs out there will help you move on to bigger, better things about Oracle.

Don't let the end get out of your sight! ;)

Why This Site Came to Be

Taken excerpt from my main personal blog, On Sea Dragons, Ents and Nobel Peace Prize Winners:

I've been considering this for quite some time, but I figured now is the right time as any to do it. This thought was prompted by the same considerations being weighted by my friend, Kristoffer, and I thought I might as well do it also.

So what's under consideration? It shouldn't affect the handful of readers here actually: I'm going to set up another blog specifically containing Oracle entries and other database related matters. It'll still be me writing down the articles, so the occasional quirks, side comments and rants will probably be still there. But the articles will be more technical oriented in nature (if my mood and discipline can help it, anyway), but (and I hope) will also be readable for beginners who are interested in databases and Oracle [new] features. Aside from sparing my current handful of readers to the point of boredom with technical articles, this will be a stepping stone for me to reiterate things I've learned at work as well as expand my horizons and list down my discoveries in the Oracle/Database subject, of course, in writing. Besides, I find that writing down things help me focus on specific topics at hand instead of laying it all out in my head (which in due time, when not written down, leads to a mush of left over spaghetti, lasagna, beef stroganoff, peanut butter and Coke).

Besides, the best way to learn something is to publish articles and set it about for peer reviews. Of course, there's this problem with "peers", but I'd probably be grateful if experienced DBAs like Tom Kyte would accidentally stumble upon the site and get into a discussion on the entries. For now, it will probably serve as a repository for what I know now, and what I will gather on in the future. Peer reviews and helping other fellow DBAs will just be an added bonus. Additionally, I have tons of document at the office.. and I just can't keep track of ALL of them. This puts everything in one place, available for me anytime, anywhere, as long as the blog host is up and I have a net connection to connect to :)

This also means that my time will be divided between both blogs. So post frequency will probably abate a bit (Not that this one has a high post frequency anyway) it will mostly depend on what I am working on and what "tickles" me.

Until I find a better way of reorganizing both blogs so that both still be in chronological order as I publish them (I would not like to needlessly cross check entries between 2 blogs), I'll probably still use this blog as the main site and link new entries from the Oracle blog. So if any of you see entries with "exciting" entries containing text like "New Entry entered in Oracle blog: The effects of using Undocumented Parameters for Super Sizing the PGA", bear with me for a while. :)

For those who want to be challenged and stretched thin with boredom, you can head to the new site to get a piece of the "action".

Thanks and good day to y'all!

Disclaimer

This blog does not pretend to know everything related to databases, or Oracle. All articles here are from my own research and experience, and the suggestions and opinions highlighted in all the entries may or may not be applicable to other scenarios. I'd suggest you always take the safe, sure approach and test out suggestions written in this blog with care before deploying anything in your production databases. I am not liable to any catastrophic event that may occur to it, so don't come running to me for answers to your problems if it happens.

On a sunnier note, If you have a question, I will try my best to answer any questions related to Oracle databases that you might have to the best of my knowledge. If I can't answer them (which will most probably be most of the time since I am just a neophyte), I'll probably politely point you to the a more experienced person, and then crawl into my hole for several days to depress on the fact that I am not omnicient. Please do not bother me with the same question again until then, otherwise I will be tempted to jump out of a 60 storey building and haunt you for the rest of my life.

If you find anything wrong or have an opinion to any of the entries, please feel free to leave a comment, and I will gladly welcome them. No man is an island, and humanity does not improve very much when nobody complains. Complainers are great when they make sense, logical, rational and sane, but otherwise, do not bother me or I will be tempted to stalk you and eventually poke your eye with a knitting needle.

And finally, all thoughts and opinions expressed in this blogsite are my own, and in no way reflects the opinions of my employers, past, current or future, Oracle, my mom or my pet tiger.

Thank you very much for reading this. Carry on.