Notes about OCP June 7, 2008

Hey, I passed my OCP exam today. Now I just need to take care of the hands-on course requirement and you’ll be reading the blog of an Oracle Certified Professional. It is more interesting already, right?

I already explained my study method when I took the OCA. This time I followed a similar plan, with three main changes:

1. I did not hike up Mount Shasta the weekend before the exam.
2. When I studied for the OCA I already knew all the material before I began studying. Simply because I spent years installing and maintaining Oracle. For the OCP, most of the material was new. I was completely unfamiliar with RMAN, MMON alerts, flashback database, recyclebin, IOT, clusters and resource manager. This meant that I had to do a lot more practice in order to feel comfortable with all those tools. I spent an entire weekend backing up and restoring with RMAN, and it was time well spent.
3. I followed good advice from my favorite OCM and studied quite a bit from Oracle’s official documentation. Mostly the backup and recovery guides. I found them way more accurate and more readable than the exam guides, and as an added bonus – you can send to Oracle any problems you find in the doc. I enjoy complaining about mistakes so much that the study process was actually fun.

Which brings me to a very important point – when you take mock exams from any of the study guides, verify each and every one of their answers with the oracle docs or with a live system. I found about 15% mistake rate in their answers, and you really don’t want to memorize the wrong things.

Its unbelievable, but I can honestly say that I’m a much better DBA now that I’ve studied for the OCP. I’m much more comfortable with most backup and recovery scenarios (Few years ago I caused unnecessary data loss while recovering a corrupt data file, simply because I did not know not to restore redo logs!) and I know how to use new important features that I didn’t know about before (or at least didn’t look into seriously).

So, my two cents for value of certification for employers debate: DBA with four years and experience and an OCP is probably better than four years of experience and no certification. I know I am.

What’s next for me? My team lead wants me to go for OCM. I’m not sure I’m quite there yet. I’m thinking of 11g upgrade and RAC Expert certificate. But before all that, I promised myself a white-paper and a presentation this year.

***

Lots of good posts this week:

Tanel Poder wrote an incredible V$ sampling query. Its simply brilliant. He also showed how to use his Snapper tool to find who is generating excessive redo and saved me a long session with logminer last weekend.

Steven Feuerstein shows how to run OS commands from PL/SQL.

Happy birthday to Syed Jaffar Hussain!

Alejandro Vargas posted a good RMAN practice.

Rob Van-Wijk wrote nice string aggregator using model. (Because Oracle SQL programmers do it with models!)

How I prepared for Oracle’s OCA exam September 28, 2007

Hurray! Hurray! I just passed the OCA exam!

I’ll follow Don Seiler’s idea and try to help other DBAs by describing how I prepared for the exam.

1. I was a production DBA for over three years prior to taking the exam. This experience was critical for passing the exam, because it is rather difficult to memorize 500 pages. Experience puts things in context, making them easier to remember. Also, you don’t have to memorize the things you do several times a week.
2. I used only Oracle 10g OCP Certification All-in-One Exam Guide , I can’t say it is perfect, but it was good enough. One thing that really confused me is that the book gave a lot of attention to how to do tasks in the db console, it had tons of screenshots and explanations. I never used dbconsole in my life, and I assumed that being familiar with how to perform tasks in the dbconsole will be important part of the exam, so I spent tons of time studying these screenshots. Turned out it was completely useless.
3. I studied for the exam for about three weeks. Reading about a chapter every day. I’m not sure it is a recommended method, by the time I finished reading the book and started the sample exams I discovered that I forgot much of the first chapters. Next time I may try taking a week off before the test and really immerse myself in the material.
4. The weekend before the exam, I hiked up Mount Shasta. Probably not a good study method, but it was lots of fun and therefore highly recommended.
5. The day before the exam a co-worker lent me his study book. I don’t remember which one it was, but I do remember that it was rather different than what I used. I was especially confused by the fact that his book claimed that Dictionary Cache is used to cache parsed SQL statement and is an optional structure and that you can’t install Oracle with OUI if you don’t export ORACLE_HOME and ORACLE_SID first. Now, I know Oracle’s memory structures inside out, but a day before the exam these kind of things can really shake your confidence. I recommend learning from more than one source, but I don’t recommend swapping the source one day before the exam.
6. Read every question several times and then go back and review. During the review I found out that a question I first understood as “which tablespaces can’t be taken offline” was actually “which tablespaces can be taken offline”, there is no reason to lose points to something silly like this.

Good luck to all of you studying for the exam. I know there is a lot of controversy regarding the certifications – whether they are worth anything or mean anything. I’m pretty sure you can be a bad DBA, study for the exam and pass, but I also think that studying for the exam made me a slightly better DBA, because reviewing the basics is always important, because there were some tasks I always did in a certain way and while studying I learned there are other ways to do it, and because I never knew how to use profiles, do external authentication or connect with ezconnect before studying for this exam.

Things you learn while studying for OCP September 11, 2007

So, these days I’m studying for my Oracle certification. I’m studying with a friend, and we use a book from Oracle Press to prepare. We are both experienced DBAs, so we make a game of finding mistakes in the book. Its a fun game, and keeps us alert while going over rather boring material.

Yesterday, I’ve read that Oracle doesn’t allocate space for a column, even if it is fixed size, until there is data in it.
While this is certainly space efficient, it seemed like a very difficult way to manage space – you have to keep moving columns around when people update them. So, we suspected that the book is making a mistake, and that Oracle allocates the space for fixed size column when the row is inserted, even if the column is empty.

Time for verification:
create table t1
(X integer, Y integer)

I created an empty table, with two fixed size columns, and checked the size:

SQL> select owner, segment_name, bytes,blocks, extents from DBA_SEGMENTS
where owner='CHEN';

OWNER      SEGMENT_NAME         BYTES         BLOCKS     EXTENTS
----------  ----------------    ----------   ---------   -----------
CHEN         T1                 65536         8             1

Nothing surprising. So lets insert some data, but only to the first column. Keep the second empty:

begin
for i in 1..30000 loop
    insert into t1 (X) values (i);
end loop;
end;

And check size again:

SQL> select owner, segment_name, bytes,blocks, extents from DBA_SEGMENTS
where owner='CHEN';

OWNER      SEGMENT_NAME         BYTES         BLOCKS     EXTENTS
----------  ----------------    ----------   ---------   -----------
CHEN         T1                 458752        56             7

Look, the table just got bigger.
Just for the heck of it, lets see what happens when I insert one row with both columns. Will this be enough to allocate trigger allocation in all rows?

insert into t1 values (1,1);
SQL> select owner, segment_name, bytes,blocks, extents from DBA_SEGMENTS
where owner='CHEN';

OWNER      SEGMENT_NAME         BYTES         BLOCKS     EXTENTS
----------  ----------------    ----------   ---------   -----------
CHEN         T1                 458752        56             7

Nope. Nothing much changed.
So lets update all rows with value in the second column and see what happens:

update t1 set Y=1;
SQL> select owner, segment_name, bytes,blocks, extents from DBA_SEGMENTS
where owner='CHEN';

OWNER      SEGMENT_NAME         BYTES         BLOCKS     EXTENTS
----------  ----------------    ----------   ---------   -----------
CHEN         T1                 851968        104            13

So, the table doubled. The book was correct – the second table was not allocated when I did the first insert, only after the update.
Since I assume that Oracle will now put one column of a row in one extent and the second in another extent, the update probably involved moving a bunch of rows around to the new extents. Something to keep in mind when trying to figure out why update is using to much IO.