How Do I Do That? - Installing a pks, pkb file

"S" before "B" when using the "P".

Just when I was feeling a little more secure in tidbits of knowledge, I got a request from a client today.

From: Client
To: Kim

Please run a simple SQL statement in Production.

"Okay", I thought, "Here goes!".  It was easy! I ran it! No fear!  Then I got another request two minutes later.

From: Client
To: Kim

 Please run these two attachments.

[attachments: ThingOne.pkb; ThingOne.pks]

OH MY GAWD!

What were THEY?  What was I supposed to DO with them?  Was I supposed to RUN them? Was I supposed to import them into a schema?  I have never seen things with these extensions before!  Yah, again, people -  wonder at my ignorance.  It's pretty vast.

Okay, Google is my friend.  You know what?  pks and pkb files are all over the place in the Google, but they don't tell you what to do with them. They just tell you bugs and issues. Thank GOD I have Sr. DBA R.

And Instant Messenger

I open up Instant Messenger and start typing...

Me:  Um, have you ever seen a pks or pkb file?

Sr. DBA R: Yes.

Me:  Um, do you... run them?

(pause of Instant Messenger silence)

Sr. DBA R.: Yes.
(kim comment - he messenges like this. Very brief. To the point. He probably knows I'm trying to compose a message that sounds somewhat knowledgeable and this entertains him.)

Me: Um... how?

Sr. DBA R.: Open them in editor in Toad and then run them.

This is where I am busily scrounging in my mind to figure out how to do this, I mean, yes, technically, I understand what he means, but I am still sort of new to the little green frog.  I've been using this tool called Embarcadero and it seems easier, but none of the cool DBAs use, and I want to be cool.  Desperately, I want to be cool.  I want to use the Toad.

Me:  So... how do I do that in Toad? (oh, please come over please please please)

(Two moments of Instant Messenger silence)

Sr. DBA R shows up at my cubicle.  He was probably thinking oh my god, she's in Production maybe it's worth the 1 minute I spend with her in her cube to head off the 3 hours of RMAN recovery in case she wipes out the database.  Or maybe not.  I project a lot of my insecurities.

He very kindly shows me how to open editor, open my files and VOILA! There it is!  He cautions me to run pkS, then pkB.  And I run it! And it's against the wrong schema.  Whereupon he kindly shows me select name from v$database, which I have now consigned to making sure I always run before I do anything else ever in my life, ever.

And then I run them again.  They run great!  And ask, "Do I do commit?" and he wanders off saying, "Nope, it's ddl".  (which I KNEW didn't require commit!! Dammit!! But I forgot.)

And now I know what to do with pkS and pkB files.

OMG What Do I Do? - "The database is slow!" - Part II

Okay, when we last left our heroine, she was bravely dodging the arrows of slow databasity.  Well, here's the thing.  When someone says their database is slow, what does this mean?  I don't mean what's causing it, I mean what is the client really trying to say?  I have complained of what I have called incorrectly a "slow database".  My last job, and in some ways my part-time current job, is Senior Administrator of our enterprise scheduling system.  This system is Applications Manager by UC4, (formerly AppWorx) and I have gone through a couple different application upgrades and two database upgrades.  And I have complained about a slow "database".  My personal experience when *I* was a client went as follows:

Me:  "Oh my god! My database!  It's just... creeping!" (note: complete tone of desperation in my voice. also note: this scheduling system, of which I am/was the Senior Administrator, runs ALL of our billing batch jobs, runs ALL of our payroll, including the top management, runs ALL of our financials, and well, pretty much runs everything in our company.  Normally, I block this all out of my day to day thoughts which helps me cope with all the awesome and scary responsibility.)

Sr. DBA DE:  "Hmm.  When did this start? (As he types on something completely unrelated in the background.)

Me: "Um, when? Um, it just FLIPPED, it was running okay and now everything is going really really slow, like 10 times as slow as it normally does!!"

Sr. DBA DE: "Is the database slow or is your application slow?"

(kim comment - See, this is what DBAs do.  Blame the application.  Typical comment:  It's not the database.  It's the application.  The database is perfectly healthy! All the data is there, the listener is up and running, the archive logs are shuffling off as planned, there is no heavy I/O or cpu, and nothing is crashed. So therefore the database is FINE, except of course the frickin' phone is ringing off the hook because everything that actually touches the database, YES, application wise, is going 100x slower than normal.)

Me:  Trying not to have the panic show in my voice, I say: "Okay, you're right, it's the application, the database is fine.  Can't you put some indexes in or something? Or run statistics or something?"

Although this sounded perfectly reasonable to me, my Sr. DBA snorts.

Me: "Okay, here's the situation, I can make it run at normal speed.  I can stop the application stack, truncate one table, delete all the data out of another table, start it again and then it runs at normal speed.  UNTIL the truncated table starts filling up the table again.  So it's recreatable!"

Sr. DBA DE:  Swivels around his chair, ears all perked up and suddenly shows interest.  "Really?  Well, then we can probably do something that could fix this... have you ever heard of OEM?"

Me: "No. What does it do?"

Sr. DBA DE: "Magical stuff.  Basically, if we can find that sql statement that is causing us trouble, we can lock it into the good twin behavior when it is playing nice and keep it running fast.  Maybe.  No promises.  But maybe, if you can recreate it.  But really, no promises."

(kim comment - there is an art to giving a client hope but not actually promising anything. A skill I need to work on.)

Me: "Okay, let's do it!  I can totally recreate it!"

I use words like totally a lot because I grew up on the East Coast on the beach.  It's sort of mid 80's Valley Talk.  Some people find it endearing.

So then I found out about OEM and magical things like SQL Advisor, which really is magical.  Of course, it took some time to get access hooked up and in the meantime I did a lot of truncating and bouncing.  But I watched my now Hero Sr DBA DE trap the offending sql statement, tune it by locking it in as its good twin so NO MATTER WHAT, it ALWAYS runs this way!  And just like that my application loved my database again, and they danced together as fast as they were supposed to.

Now wouldn't it be great if I could do that with this database that my clients were meeping about?  Well, a couple of things.  We don't have everything in OEM yet, and of course this one particular database is not in OEM.  So that tool is ruled right out.  It will all be manual and eyeballing.  But here are some magical questions that I followed up with.  I think, that in any troubleshooting scenarios there are a couple of basic fundamentals that will work when trying to figure out what's going on.

• Journalistic Questions.
• Sherlock Holmes Most Famous Quotation.

Remember journalism in middle school?  There were basic questions that were taught in that class that have helped me troubleshoot practically everything.

Who?
Who is this affecting? Is it affecting everyone? Are you sure? Have you tried it with different permissions?

What?
What is it affecting? Is it doing the same thing on every query? What is it doing exactly? I need times, dates, milliseconds. What is your definition of "it's taking forever"? I mean, forever is a long time.

Where?
Is it doing this everywhere or only on one desktop or one floor?

When?
When did it start happening exactly? Does it happen all the time? Is it recreatable?

Why?
You know, the client is the closest thing to their application other than the database.  Ask them what their opinion is as to why it's happening.  They have feelings towards their app.  They may have seen it before. Clients have actually pretty good intuition.  And besides which, it makes them feel valued.

How?
How did you discover it? How does it make you feel?  Okay the last question is kinda touchy feely, but I think empathy is something all database administrators should cultivate. I mean, you can go to the best doctor in the world, but there is something lacking if, when you are really sick, he doesn't hold your hand and murmurs, "There, there...we will try and make it better..."

And then Sherlock Holmes Most Famous Quotation.

When you have eliminated the impossible, whatever remains, however improbable, must be the truth.

So using these basic tools, I found out the following:

The whole application is fine, except for two update statements that occur every day at about 1:30 p.m.  These are the only things that are slow. I also learned that this didn't start happening last week, this started happening a couple of weeks ago. Interesting!  You know why?  We have something called Veritas Clustering for high availability databases (databases that have to be up all the time).  To analogize, it's something like having a duplex where there is apartment A on the left side and apartment B on the right side. Usually, each database has their own duplex, own fridge, own bathroom and their individual space and privacy.  Which is important to databases. They like being by themselves as much as possible.  But sometimes, something happens to one side of the duplex that forces that database to temporarily move over to the same side that the other database is living in.  Not ideal, but good in a pinch.  But just like any crunched living situation, it can get a little crowded.

Guess what happened a couple of weeks right when the database started getting slow?  Yup, one database moved in with another and never moved back.  Could this be the problem?  Maybe. No-one likes sharing the bathroom.  Even if it doesn't fix the slowness issue, we can't really start analyzing the situation in great depth until we get the databases back to their respective homes.  So that's happening on Monday.  So everybody, cross your fingers for me!!

In the meantime give good thoughts to all the DBAs in Egypt, Tunisia, and Libya.  They exist and are going through a lot right now, good and bad.  My thoughts and well wishes to them all.

OMG What Do I Do? - "The database is slow!"

Okay, so it's finally happened.  I was sitting at my desk, peacefully catching up on all my work, finding out what's happening in the world, thinking to myself, "Hey, this isn't so bad. Sr. DBA D just left for New Orleans, Sr. DBA H is in New Zealand on vacation, it's actually not bad.  Just basic work, writing meeting minutes, reading about database philosophy, checking out reports..."  So there I was, peaceful and happy, noodling around trying to figure out why the good Pink Floyd wasn't available on vinyl, when suddenly it happened.  A client just popped up at my desk with a panicked look on her face.  "The database, it's slow! It's twice as slow as it was last week! Getting worse!"

Oh crap.  This is where I get the horrible sinking feeling in my stomach.  Database tuning. Like, if I didn't have a really bad weakness of not knowing anything, I'd have to say that database tuning is probably my absolute worst weakness.  I mean, you have to actually know something about databases in order to tune them.  Now I know why some DBAs keep vodka in their desk.

I immediately look over at her, and try and compose myself:

Me:  "Hi! I am sorry to hear that. Slowness, huh? Has this happened before? Well, what did Sr. DBA  H do in the past?"

Client: "I dunno!  Sr. DBA H just did something. Maybe he analyzed tables? Yah, I think he did that. Can you do that? Can you analyze tables?"

Me: "Of course! I can analyze tables.  I can run dbms_stats."  (this is like actually the one thing I can do)

So then I swivel around and log into the machine I need to look at the database on.  My little frog icon comes up, ribbits and then asks me for a license. WHAT?  Doesn't this pc have Toad as a universal user? Apparently not.  So then I go hit up Embarcadero. Another license prompt.  I turn around and look at my client who is twisting her fingers and shooting daggers at me.  OMG.  I can't even open a tool to look at the databases.

I weakly smile: "Hang on.."

Client: "Can we go talk to other DBAs?"

Me: "Um, I think that would be a good idea."

So then my client and I go tromp over to the nearest DBA desk, Sr DBA DH, not to be confused with Sr. DBA D or Sr. DBA H.

Me:  "I can't get into Toad, Embarcadero or anything else, can you pleeeeeze look at ABCD database? It's slow."  (as soon as I say this, I see Sr. DBA DH go into DBA mode.

Sr. DBA DH:  "Well, slowness can be caused by a lot of different things.  It may not necessarily be fixed by running statistics.  In fact, it could be... (whereupon he whips out his fingers and starts counting out all the things that it could be...)"

Client: momentarily transfixed then immediately asks about stats.  She is really convinced that stats is the fix. Maybe it is.  I have no idea.  This is when I get the understanding there is no quick fix for database slowness.  Sr. DBA DH starts running stats after expounding for a long time that this probably isn't going to fix anything. I am learning that we should probably do what the client wants us to do, if it doesn't hurt the database, because maybe it shows we are listening to them.  Maybe it's PR. I don't know.  All I know is, I have a client that has a slow database. And she wants me to fix it.  And I have no idea what the hell to do....

(to be continued....)

How Do I Do That? - Changing Tables from Read Only or vice versa

Every day is a fun learning experience.  What did I learn today?  How to change tables from read only to read writable.  In my current incarnation (haha) I support a database that has had its life divided up into easily understandable periods of time called YEARS.  So basically, my crosstraining partner has gone through and partitioned off tablespaces into these portions called YEARS and most of these tablespaces are in a read only mode.  I think to make the clients unable to write into them.  And possibly easy to maintain.  Now, I had no idea about this until a client sent an enigmatic email:

From: Client
To: Any DBA that can read this note

Plz request that we can write records.  We can't now.
Love,


Client

See, sometimes you get these notes.  Forget about the fact that there are like 300 instances in different iterations of development, stage and production.  To a client only one (maybe two!) databases exist.  They are like teenagers.  There is only one true love.  And we are supposed to read their minds.  Okay.  And actually, sometimes, after years and years at this job, some DBAs can. Which is very Jedi like.  I am light years away from being at that mental state.

So lucky for me at this particular time, even though I am the caretaker of these particular beasts, I wasn't at my desk.  And we have a lot of very helpful DBAs lounging about, so very helpful Sr. DBA E took this email and asked a few more questions.

From: Sr. DBA E
To: Client 


What database? What tables? Who are you? What is that you need, exactly? 


Sincerely,


Sr. DBA E

Funnily enough, I am still learning to ask these questions without floundering around and looking questionable. Anywhoo, after all the information was gathered and apparently the work done, I came back to my desk and saw this:

From: Sr. DBA E
To: Client 

Done!

Sincerely,

Sr. DBA E

What?  What was done?  I asked Sr DBA E and he said, while waving his hand, "C'mon over to my desk and I'll show you.."  Whereupon I learned how to make tablespaces writable.  It's very easy.  Open Embarcadero or Toad and navigate over to tablespaces area.  Right click on the tablespace that says READONLY and change it in the drop down menu to READ WRITE. That's it. It's that easy.  Or you could be all commandline in SQLplus and do a real ALTER TABLESPACE <tablespace_name> READ WRITE;


This is a really simple explanation and nifty trick.

Sometimes things really are that easy.  Tomorrow I'll try and describe my feelings of awe and admiration over Sr DBA D who amazed me with his bravery and ability to kill  a process without thinking twice about it, and apparently magically fixed an application.  By the way, he'd never even seen this process before.  That's how professionals do.