Very slow database (with a large SGA 10GB) startup

[Richard McLellan_1]

Hello.

Do we need to setup any parameters
to speed up the database startup (9i RAC on HP-UX 11i)?. We have 16GB RAM , two swap disks (4GB and 12GB).

Thanks for all your help on this..

[Brian Crabtree]

You might want to consider lowering the size of your SGA. This alone would require a large amount of time to load and allocate the space. Most likely, your application does not need to have 10g for the SGA.

Otherwise, you will most likely have to wait for the system to continue normal startup. Also, if this is a problem, you will want to make sure you don't shutdown abort unless you have to, as the startup recovery portion can take a bit if there is alot of undo to perform.

Brian

[ASO CENTRAL]

I've seen 8 to 12 Gb SGA sizes very effective for instant access to the database, but startup does take a very long time due to the way Oracle obtains the data to place into SGA. It is definitely not an HP-UX problem--your DBA needs to look at the way the data is gathered...specifying some items in SGA will require Oracle to form the information from several sources, creating a very long series of steps.

Now the good news is that the gathering of that data occurs once at bootup and subsequent use and updating of the tables will be at memory speeds. So you afre trading a long startup for instant response while it is running.

[Prakash_10]

Hello All,

Thanks for your reply. Actually we are just trying to start the system and we have not started the database access yet. We are in the process of configuring the database and our estimate with regards to SGA is that we will need to have a 10GB SGA.


So we are looking for information on recommendations for appropriate values for various kernel parameters.

Thanks and regards

Prakash

[A. Clay Stephenson]

Without knowing more about the other applications on your system, it's difficult to know. The very first thing that I would do is use Glance or vmstat to see if you are paging out. If you are swapping then your should really reduce your SGA or buy more memory. Next is to check to see if you have left dbc_max_pct at the default 50%. You should have no more than about 1GB (and that is generous) used as buffer cache. Because you are probably using raw/io, you could really reduce buffer cache on your system. In any event, you should not exceed about 6% as your dbc_max_pct or set it to a fixed value using bufpages. Also examine timeslice to see that it is not set to 1; set it to 10.

In any event, it's going to take some time to start your database.

[Prakash_10]

Hello.

I am checking the timeslice parameter. Here is some additional info.

We have 8GB RAM/16GB swap on the each of the nodes.

When the SGA is 4GB the db starts up in couple of minutes. With SGA =6GB db doesn't come up even after an hour. So I guess that is too long.

There is nothing else going on the server. We are just trying to bring up the database with the 6GB or more SGA.

Thanks once again for all your input.

Regards

Prakash

[mghosalk]

check if oracle database parameter pre_page_sga is set.

if it is set to TRUE, then u may experience slow startups.

make sure it is FALSE.

Mandar

[Wodisch]

Hi Richard,

have you restricted your buffer cache to a maximum of about 400MB? That would safe you a lot of RAM... (and your timeout experience sounds alot like a RAM bottleneck)

And do you use the "OnLineJFS" and the additional mount-options "convosync=direct,mincache=direct" to instruct the kernel to bypass the whole buffer cache for the I/O to/from the filesystems mounted that way? Of course those filesystem must only have "datafiles" stored on them, no "logfiles", or "controlfiles", or "codefiles", or anything else...

For the two special init*ora parameters "pre_page_sga" and "lock_sga", I would actually USE them (=true), to ensure that the SGA is allocated in RAM at the very start of your instance, and *stays* in RAM. The difference in time will not be one hour due to these parameters.

HTH,
Wodisch