>
> Ari,
> Hey Ari, you know I am not a DBA, I am a programmer who
> knows how to write applications that use databases, therefore I
> usually end up designing the database backends. I like it but...
> I'm not a DBA. So here is my question. I am being asked by some
> DBAs about the performance of my database I designed and asking
> some questions I just don't know. So I found this script on the
> web and ran it and it gave me the output below. If you have a
> spare moment (if there is such a thing), could you look it over
> and tell me whether it is good or bad? And, what is OFA, and
> what does it mean to be OFA complient? If you don't have the
> time, I understand. You just seem to be the only one I ever get
> a response from.
>
> Thank you,
> John
OFA means "Oracle Flexible Architecture", and is basically how a directory
structure should be set up so that multiple databases may exist on one server.
For example, all databases should have a bdump, cdump, ufile, pfile directory.
Tablespaces should be spread out on different drives, etc.
I believe there is a white paper on this somewhere on "www.oracle.com".
I have commented below to your utlestat result, cutting to only the important
parts...
-Ari Kaplan
Independent Oracle DBA Consultant
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
<-> For 115+ Oracle tips, visit my Web Page: <->
<-> <->
<-> www.arikaplan.com <->
<-> <->
<-> email: akaplan@interaccess.com <->
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
> Hit Ratio = (logical reads - physical reads) / logical
> reads
> Hit Ratio =
> 90.53%
>
> If the hit ratio is less than 60%-70%, increase the initialization
> parameter DB_BLOCK_BUFFERS. ** NOTE: Increasing this parameter will
> increase the SGA size.
Hit ratio is pretty good. Anything less than 85% should be an indication to
increase the DB_BLOCK_BUFFER size.
>
> GET HIT PIN
> HIT
> NAME GETS GETHITS RATIO PINHITS
> RATIO
> -------------------- --------- --------- ------- --------
> -------
> SQL AREA 16957 16343 .96 36192
> .96
> TABLE/PROCEDURE 2128 1867 .88 3007
> .90
> BODY 1.00
> 1.00
> TRIGGER 26 18 .69 27
> .26
> INDEX 21 .00 21
> .00
> CLUSTER 27 12 .44 15
> .33
> OBJECT 1.00
> 1.00
> PIPE 1.00
> 1.00
The CLUSTER, TRIGGER, and INDEX hits could be improved above, but since they
were hit only 21-27 times, it is statistically meaningless. For example, the
SQL AREA was hit 16957 times (much more significant statistically than 27). So,
you are good in the above result...
> free memory
> 10,844
Memory looks low, increase the SHARED_POOL_SIZE parameter in your init.ora file
(by about 5 megs), shut down the database, and start again.
Aside from that, everything looks good!
>
> **********************************************************
> * John Holland (mailto:holland@blairlake.com) *
> * Technical Director *
> * BlairLake New Media (http://www.blairlake.com) *
> * 104 West 42nd Street Tel 816.756.2121 *
> * Kansas City, MO 64111-2301 Fax 816.756.2992 *
> **********************************************************
>
Back to Ari Kaplan's Home Page