What use is OCP ?

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Fri, 27 Sep 2002 15:18:14 +0100
Message-ID: <an1pc3$rpq$1$8300dec7@news.demon.co.uk>

Have any of you received the Sept/Oct issue of Oracle Magazine ? Have you read the article by the Certification Director for OCP ? Would you like to see a few questions, answers, and comments about DBA fundamentals.

Question 1:

What is the minimum number of granules allocated at SGA start-up ?

Answer 1:

The claim is three. The fact that my system always allocates at least 16MB to the shared pool alone, let alone the granules needed for the buffer and redo log when I start-up is a pity, but the answer might be right. However we then read:

"On my database, I can check this by querying v$buffer_pool for LO_SETID which is 3"
Why does the working data set of the default buffer pool prove anything about the number of granules ? If I have a KEEP and RECYCLE pool, the LO_SETID of the db_keep_cache_size is 1, the LO_SETID of the db_recycle_cache_size is 2.

We also hear: "if the SGA_MAX_SIZE is less than 128M then the granule size is 4M, otherwise it is 16M" - perhaps he hasn't found that note on Metalink about granules of 8M on Windows 2K.

Anyway - what's the point of asking someone about the notional minimum SGA of 3 granules (12MB) ? The executable is about 60M, and I'm not trying to run the thing on my cell-phone !

Question 2:

What four parameters most affect the SGA size ?

a) sga_max_size
b) shared_pool_size
c) db_cache_size
d) large_pool_size
e) log_buffer

Well, since the SGA_MAX_SIZE is the thing that is supposed to be the absolute limit on the number of granules you can allocate to the others it clearly has to be the other four. After all, you're not really 'affecting' the size of something if all you doing is stopping it from getting any bigger !

Answer 2

Yup, sga_max_size is the one wrong answer.

We also read "The sga_max_size is determined by the size of the other memory structures". Well, yes, if you let it default - but the sga_max_size is the thing you are supposed to set (if you are being a good DBA) so that you can vary the others at need.

Question 3:

Which command would you execute to decrease the size of the shared pool from 50MB to 20MB ?

a)....
b)...
c) alter system set shared_pool_size 20m;
d) alter system set shared_pool_size = 20m;

Answer 3:

(d) - perfectly correct, perfectly pointless question.

alter system set shared_pool_size 20m
ORA-00927: missing equal sign *

I don't care if the DBA doesn't remember that there is supposed to be an equal sign - if he can't spot the problem when Oracle gives him the error message he probably never got past filling in the application form for the interview.

Question 4:

Oracle Managed Files are established by setting what two of the following parameters.

a) db_file_create_dest
b) db_file_name_convert
c) db_files
d) db_create_online_log_dest_n

Answer 4:

(a) and (d)

wait a moment - there's no such parameter as

db_create_online_log_dest_n.  There is
    db_create_online_log_dest_1,
    db_create_online_log_dest_2,
    db_create_online_log_dest_3,
    db_create_online_log_dest_4,
    db_create_online_log_dest_5,

and the manual suggests that you set at least two of them. And of course the manual also tells you that you don't need to set any of them, as db_file_create_dest will do on its own.

Is this hair-splitting - well if 3C is definitely wrong because of an '=' is it safe to assume that 4D is right when it has a letter instead of a number ?

Of course, the article does continue to point out that in fact "you can set either or both". Of course the Database reference manual happens to say that there is no default value for db_create_file_dest, so I thought I'd have a go creating a database with just db_create_online_log_dest_1 - did it work ? Yes - but it built the data files in $ORACLE_HOME/dbs, which isn't really the ideal place for them.

So I have learnt something today.

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Next Seminar dates:


(see http://www.jlcomp.demon.co.uk/seminar.html )


____England______September 24/26, November 12/14

____USA__________November 7/9 (MI), 19/21 (TX)

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Received on Fri Sep 27 2002 - 09:18:14 CDT