Low performance and time responses

[Steve Slade]

Manuel,

I have had a look at you init.ora parms, and there are few things of note.

1. Your block size. The Oracle db_block_size is basically the unit of currency for Oracle. It completes all of its work in units of this size. A larger Oracle block size will improved efficiency in I/O in some cases. (Consider how large your row data is - you do not want a row to span multiple Oracle blocks, as each additional block is an additional read/overhead. - stat - table fetch continued row)

We have D250, D270 machines with HPUX 10.20, with a block size of 8K, so I imagine you should be able to do the same. (Its listed in the Oracle Documentation for HPUX - I do not have it handy so I cannot confirm.) However, changing you block size is NOT easy.

To change the blocksize you need to re-create the database. This means that you have to basically export all of your user data, code, objects, etc. Totally trash your database and then re-create it, and in the create database command specify the blocksize to be the new size, (4K or 8K). Once created you then have to re-import all of you user stuff. Also, you have to beaware, that some of your init.ora parms (those such as db_block_buffers, db_file_multiblock_read_count, etc), which are specified in units of Oracle buffers. Like I said, not for the faint hearted. I would recommend that you try it on a development type instance just so that you can verify the process, changes required, implications, validate that it can be done, and check for any performance enhancements/degredations.

2. I also notice that of your total shared pool size - 9000000, you are reserving 450000). I do not know the size of your database code, packages, queries etc, but you need to be aware that the shared pool is segregated based upon 4k (shared_pool_reservered_min_alloc) - half each. Depending on the weighting of your code size you wish to change this 4k value. You can check your object sizes using v$db_object_cache - once they have been loaded into the SGA.

3. Your compatible parm is set to 8.0.0, which means you are not utilizing any options specific to 8.0.4.

4. Its a good idea to set the parm log_checkpoints_to_alert to true, as you can then use your alert log to confirm if your database is performing a large amount of checkpoints. Whether this is a good thing, depends on how you value performance/against recovery.

[Manuel G]

Hi again:

- We were reviewing sar statistics during last days. We used sarcheck to help us.

- What sarcheck shows is that we have a very high I/O bottleneck with our external discs (those on the disk array).

- Bottleneck may be attacked by:
1) Reviewing sql statements for reducing I/O requests. We are working on it but it?s hard to do because app. code is already finished and we can generate some problem if we try to change a lot.
2) I?ve read in some forums about arrays configuring, stripping and something else. Could a new disk, logical volume, ... help us to improve performance. Charles, you advanced something in your last messages ...

- I can show our configuration if needed.


Thanks a lot.