Monday, July 17, 2006

Oracle Certified What?

Just recieved the following email via one of the Oracle DBA lists I'm on:

I am XXX from Singapore.I am also an Oracle Certified Professional
(OCP).Could I know size of any database and what's the size of SGA for
that Database and how long it take to complete Logical Backup and
Physical Backup.I will be awiting for ur reply.

Thanks in Advance
Names have been removed to protect the guilty.

Now, I have no particular problems with answering beginner questions (I've asked a fair few myself), even though the list this email appeared on is suposedly an experienced DBA list, not a newbie list. I'm quite happy to ignore the poor standard of English, the questioner probably doesn't speak English as their first language (although in Eric S Raymond's guide to Newbies, on how to ask questions, he does state that they should be asked in clear, grammatical, correctly-spelled language). The thing is the questioner is an OCP, or at least claims to be, and those questions are definite newbie type questions. They're also questions that a little thought and looking at the manual should answer if basic common sense doesn't.

Taking the last one first: "how long it take to complete Logical Backup and
Physical Backup"

It depends. Size is just one factor. Machine disk speed, competing jobs, speed of target media &c have a much bigger affect. Try it and see. As you get more experienced you'll be able to estimate how long it will take by taking all the factors into consideration. There's probably a formula somewhere that will produce a precise, supportable, validatable and utterly wrong answer.

Moving back up the mail we get to: "what's the size of SGA for
that Database"

Querying v$sga would be a start. If it's an older version of the database then they'll have to dig into v$parameter and add up the various pools.

And finally: "Could I know size of any database"

I am kinda assuming throughout that the questioner wants to be able to find the size and other factors of a database they manage, rather than for people to pick a database at random and pass on the information on that.

This one is quite hard to answer, not that the information is hard to get to but because there are so many possibe measures of the size of a database. Files on disk? Data volume? Transactions per second? Number of concurrent users?

One of the first two is most likely (given the later questions about backups). For files on disk just query dba_data_files and dba_temp_files and add up the BYTES column then query v$log and sum the BYTES column. If you want you can look at the controlfiles, pfile, spfile and archived logs, it depends how you're measuring (controlfile, pfile and spfile will constitute a tiny proportion of the size of any non-trivial database and space taken by archived logs is likely to vary according to transaction load and your management procedures).

Data volume can be obtained from querying dba_extents and summing the bytes column for all table extents. It won't give a spot on answer due to partially filled extents but should be close enough. No doubt there are scripts out there to measure down to the individual block, frankly I've never needed to be that precise.

Does make on wonder why the OCP is thought of as a desirable qualification if mails like that are flying around.

No comments: