Re: Large SGA - performance problems

> Hi net!
 

> I'm pondering about some rather irritating performance problems we
> have, and wondering whether you have any suggestions for me to
> consider.
 

> Here's the thing:
 

> We run 2 HP9000 K420 machines in a HP cluster. Both equipped with 1GB
> of memory, 2 processors and fibre channel for internal communication.
> Disk ? Enough! One of them is our database server, running Oracle
> 7.2.3, and SQL*Net v1 (Yes, I know!). The database serves the other
> HP over the fibre channel into a Powerhouse application.
 

> Database characteristics:
 

> Sizes:
 

> TABLESPACE_NAME MB COUNT(*)
> ------------------------------ ---------- ----------
> GEN_FREE_TEXT 800 1
> INDEX01 2400 6
> RBS 800 2
> SYSTEM 100 1
> TEMP 400 1
> TOOLS 50 1
> USERS 50 1
> VMS 2000 5
 
> SGA:
 
> SQLDBA> show sga
> Total System Global Area 287680840 bytes
> Fixed Size 47936 bytes
> Variable Size 164589064 bytes
> Database Buffers 122880000 bytes
> Redo Buffers 163840 bytes
 

> optimizer_mode CHOOSE
 

> We have optimizer statistics.
 

> Tables, views and indices :
 

> Tables: 351
> Views: 40
> Indices: 607
 

> Concurrent users (approx) : 80

> OK, and now the problem:
 

> Every now and then a user generate a table scan (one way or another).
> This table scan tends to "hang" the database for a while. The database
> process uses ~100% CPU, and nothing else is allowed to happen on the
> database during the table scan. Not even a sqlplus session login. The
> hangs may last from 5 seconds up to over 1 minute. As of now we can
> cope, but as we increase number of concurrent users, I'am afraid the
> problem will be a nightmare.
 

> I ask myself :
 

> 1. Is the hangs due to the very large SGA ?

probably not, but you can't be sure until you figure out what that particular process is doing.

> Is such a large SGA generally a good idea ?

does your block buffer cache hit ratio decrease with a smaller SGA? if the answer is no, and your cache hit ratio is 95% or more, then you probably have a larger than necessary SGA. Is that harmful? Well, it depends on how your HP is handling this large chunk of memory being utilized by Oracle. If the system is paging excessively or swapping, you are better of shrinking your SGA. If you have to shrink your SGA to the degree that your cache hit ratio is consistently below 95% so you can avoid paging, then you have a strong argument to your boss to fork up some money to buy more memory.

There is another problem with having a large block buffer cache...there is a latch on the LRU structures that can become very busy in situations where there are a lot of users trying to load blocks into the SGA. This problem is relieved by having multiple LRU latches, which is a 7.3 improvement.

> 2. Should I use the cost-based optimizer in Oracle 7.2.3 ?

i personally don't like CBO because it's rather unpredicable sometimes. however, in some cases where you have an application which will allow users to perform ad-hoc SQL, it may be the better way than teaching end users how to write good SQL.

> And now I ask you:
 

> Am I asking myself the right questions ?
> Any other experience on this matter you would like to share with me ?

I think you'll need to get a handle on what exactly this backend process that's hogging up 100% of your CPU is doing. You can do this by looking at the v$session_wait view. The event the session is waiting on will give you an idea where all that CPU cycles are being spent (ie, latches, waiting on i/o, etc.) Also look at the entries for the sessions that seem to be hung up because of the session doing the FTS.

Another source of info is a estat/bstat run during a period when several of these FTS are runned. Compare the i/o stats to a period when none of the FTS are runned and see if the FTS are hurting you on the i/o side very badly.

good luck,

Johnny Chan
Independent Oracle Specialist Received on Fri Jan 03 1997 - 00:00:00 CST