Re: Help for the Newbies out there

bobcat_at_usa1.com wrote:
>
> I have found that one of the most common areas of concern for Oracle is performance.
> I am frequently run into sites that are new to Oracle, complaining about sluggish
> performance and have taken their flat file systems and jammed them into Oracle.
>
> For the benefits of the "newbies" out there, I would like to put together a list
> of common areas to look at when systems are performing slowly.
>
> For Example:
> . Check for locking contentions (how?)
> . Check to see if Database is over-extended (how?)
> . Check to see if rollback segments should be increased
> . Can the database be split across multiple drives to easy disk useage
> . Should a more efficient index be defined?
>
> If you can add anything to this thread please feel free to you. I think this type
> of listing would be beneficial to all of us.
>
> Thank you.

Well, time and time again, the biggest performance problem is that the SGA is not big enough to support the transactions being asked of it. If the SGA is too small, the server process will generate recursive SQL calls that really slow things down. Usually, just increasing the memory parameters in the init.ora takes care of the problem.

But, sometimes this isn't the problem. It would take me a week to list all possibilities, but in order of commonness:

1. Oracle has a facility that allows it to quickly read contiguous Oracle blocks from an extent. This is called 'multiblock read.' It is used automatically by the server process if the Oracle blocks are contiguous. To make a database object's blocks contiguous, you can export and then import the object in question. You can protect against fragmentation by sizing the object properly in the first place with a good STORAGE clause.
2. Oracle can slow down dramatically if it has to chain blocks. Chaining occurs when the DBA didn't make a large enough PCTFREE for the blocks that an object uses, and therefore the header information from one block has to be stored in _another_ block. This is very inefficient. It can be avoided quite easily by using an appropriate PCTFREE when the object is created.

God, there's tons more stuff like this I could put, but this is already getting too long. Just email me if you need more explanation.

-- 
==============================================================================
Jason Wells                 Tapestry Computing, Inc.       Voice: 314.344.0066
Senior Software Developer   Email:jason.wells_at_tapestry.com   Fax: 314.344.0990
==============================================================================