Re: Oracle and Memory

Rich Headrick wrote:
> I have a Sun Spark 20 dual cpu, with 128MB of Ram. Are my init.ora parameters anywhere near
> correct for a system of this size? I'll be running Aurum's SalesTrak app, and have about 40
> user concurent connections. Some tables will have as much as 1Million rows.

With 1Million row tables you should consider using the largest available Oracle block size when you create you database. There are significant speed ups in index searches and reduction in block overheads.

> db_block_buffers = 14648 # LARGE

You need to run your database and check how effective your cache is. Anything above 80% is fine. Lots of fine tuning may get you up to 90+%.

> shared_pool_size = 6000000 # MEDIUM

The shared pool size holds PL/SQL and SQL so if you are using a lot of PL/SQL then double or treble this figure.

> log_checkpoint_interval = 10000

Should be large enough to ensure checkpoints only occur at REDO Log changeover.

> # timed_statistics = true # if you want timed statistics

Turn this on all the time.
I've not noticed any significant penalty and the timing information is very useful when tuning.

> mts_dispatchers="ipc,1"

If your users are all clients (accessing from another machine/PC/whatever) then don't use MTS for IPC connections. Always better to connect directly with dedicated process for your DBA work.

> mts_dispatchers="tcp,1"

NEVER use just one. Two should be a minimum and scale from there until the n'th dispatcher is almost idle. See note below.

> mts_servers=1

Again, NEVER use a setting of one. Use a minimum of two. See note below.

> mts_listener_address="(ADDRESS=(PROTOCOL=ipc)(KEY=tst1))"

Not needed if you remove the ipc dispatcher above.

NOTE: If you have dispatchers D1 D2 ... Dn and Servers S1 S2 ... Sp then when a client passes a request across Oracle will find the first available dispatcher starting at D1 and going to Dn. The dispatcher will find the first available server starting at S1 and going to Sn. The result of this is that the low numbered dispatcher and server processes will get the bulk of the work and the high numbered ones will show much less cpu usage. You can tune the number of dispatchers and servers so that the last one is a zero cpu usage or at least very very low. Having just one dispatcher or server means you can't see this cpu usage pattern. Also, the point of having MTS is to reduce server startup time and memory usage. If you only have one server at startup then you may miss those occasions when 6 or 7 are active and you're incurring that server startup overhead.

Hope that's of some use.

Bruce... Received on Fri Aug 23 1996 - 00:00:00 CEST