Re: DBA Newbie question

Comments embedded. If you need any further guidance, let's continue this discussion outside office hours (I'm currently working at home, though I may need to depart very soon to troubleshoot) in Dutch.

Most likely any close examination of this system will pull of the cap of a garbage-bin

Hth,

Sybrand Bakker, Oracle DBA

"Maurice Sogelée" <msogelee_at_kembit.nl> wrote in message news:8r1ej2$a7gno$1_at_reader4.wxs.nl...
> Hi,
>
> i have been asked to examine a database where users experience problems
> while committing data to a certain table. I have run a trace several
 times,
> but found no severe 'values'.
>
> What i would, using TOAD, is that there are some problem areas, which are
:
>
> - Buffer cashe hit rate= 22,9292 , comment : may need to increase
> db_block_buffer

One of the strategies of Oracle to minimise disk I/O is to cache selected records (which are likely to be needed by other clients). Typically the buffer cache hit ratio should exceed 80 percent, in OLTP systems it should exceed 90 percent and reach 95 percent.
So your hit ratio is telling you the db_block_buffers parameter is WAY too LOW, most likely still the default 200. Increase it by at least a factor 20 if you have 4k blocksize, by 40 if you have 2k blocksize and by 10 if you have 8k blocksize.

>
> - Chained fetch ratio = 0,0318, comment: pctfree TOO LOW for a table
>

In at least one table there are records located in more than one Oracle block. Usually this is also a disaster.
You should run dbms_utility.analyze_schema(<schemaname>,'COMPUTE') on suspected schemas and query owner, table_name, num_rows and chained_rows from dba_tables
If you find chaining you can either increase pctfree for that table or -if *1* record is *bigger* than 1 block, increase the block size. You'll need to recreate the database for this.

> - parse/execute ratio = 66,9118, comment : high parse to execute ratio.

This is also a true disaster. Your application most likely uses dynamic sql and/or hardcoded literals everywhere, forcing Oracle to parse everything over and over again. The ratio tells you how many parses have been processed compared to executes. In your case Oracle is parsing every statement multiple times. In a well-designed application there is no need for that

Usually this has a severe impact on overall-performance.
>
> Can anyone tell me what these statistics tell me ?
>
> regards,
>
> Maurice Sogelée
>
>
Received on Fri Sep 29 2000 - 06:02:40 CDT