Swap, memory, kernal config? - ORA-12500 error

[Brian Crabtree]

Have your DBA tune the query. Most likely, it is doing something wrong, or needs to have some work done on it if it is eating up that much of the system.

Brian

[Yogeeraj_1]

hi,

If you have good broad knowledge of the database, you can start to use STATSPACK to identify possible 'bottlenecks'.

Note that you will also have wait events:
a. foreground (client) waits for latches (probably not using binds) and log syncs.
b. background (oracle background processes) waits.

Which you will have to analyse at the application level.

regards
Yogeeraj

[T G Manikandan]

YOur buffer cache is pulling out 50% of memory on the machine.

check the dbc_pct_max is 50 which is half the RAM.Only half the RAM is taken for the remaining tasks.

Bring down the buffer cache value to around 300Mb from 1GB i.e. that would be the value of 15.
Bring down your dbc_pct_max kernel parameter to 15

That should give some improvements.

[T G Manikandan]

Hey!
Are you joking!!
your kernel value for shmmax shows 67MB.Is that what you have defined.

Come on man you do not have the shmmax properly set and how will the Oracle SGA allocate space in memory.

First bump your shmmax to 1.5GB that would be
really solve hell lot of problems.

Just assume that your SGA does not fit in the memory which is causing hell lot of problems.

I am expecting your answers to the comments.

Revert back!

[Hein van den Heuvel]

Witht the increased physical memory, did you retune Oracle to exploit some of it.
You are running 64 bit now? What Oracle version? Did you clean some version 7 crud from the init.ora files?

The init.ora you published back in September showed a relatively small number of db_block_buffers. It is not uncommon to dedicated 1/4 of a machine to this (now 2Gb) and you have given it 100MB. I'd recommend at least 1GB (250,000 buffers of 4KB for an 8GB box). The log_buffer of 160KB might as well be bumped to 1MB just in case you have significant activity there.
And you may want to review PGA_AGGREGATE_TARGET = 24000000
LARGE_POOL_SIZE = 8000000
for appropriate values with the new RAM.

One set up values was relatively large and possibly contributed to your original problem: sort_area_retained_size=3072000 / sort_area_size=6144000
Times 300 users, that is 1GB !
Are you sure you need that? Can retained be reduced? Or can the pga_target be used to control this some?


Of course this all would be an 'ready, fire, aim' approach. You really should get statspack going to get an appreciation of what your main wait events really are (as remarked by others).

> everytime we fix one thing, another issue pops up

Ah yes, that's Oracle tuning for you. Think of it as Job Security. Supposedly the overall result gets better and better and eventually the new issue will be small enough (and hard enough!) to just let it be.

For better help yo may want to re-publish your current init.ora and at least teh first page of a relevant statspack output.

hth,
Hein.

[TWBSupport]

Thanks for all the suggestions. I am not the dba, just the sys admin, so I will pass along the information to the dba to get his comments on what you all are saying.

TJ - the shmmax you are seeing is what was config'd prior to the memory upgrade. The shmmax after the memory upgrade is 53687091.

Thanks again & I'll post what the DBA states

[TWBSupport]

Sorry about that, I mistyped the shmmax it is
536870912.

[TWBSupport]

latest init.ora file....

[Hein van den Heuvel]

> Thanks for all the suggestions. I am not the dba, just the sys admin, so I will pass along the information to the dba to get his comments on what you all are saying.

You have to involve the DBA into this discussin and work as a team. You can not tackle this seperatly from a unix and from an Oracle angle. The performance of your system will be defined for 90% by Oracle setup, and 10% by system setup (assuming the system setup at least is good enough to make it functional). So any performacne concerns must be addressed through Oracle at first.

>>> db_block_buffers = 25000

Major problem. You only dedicated 100MB for block buffers. That is not going to be enough and disproportionate to the memory available and in use for other Oracle usage.
I'm suprised a modestly aggresive DBA would accept this setting, there maybe more going on here then meets the eye.

As I said before, you can shoot from the hip and try 250000

Then with the help of STATSPACK (level 10) you can determine the a more optimal setting, either by evaluating the IO rates yourself or by using the oracle "Buffer Pool Advisory" from the report.


>>> PGA_AGGREGATE_TARGET = 24000000
This may cause exessive on disk sorting now that you have many more users.


Hein.

[T G Manikandan]

Still you can push up your shmmax value.
DId you bring down the buffer cache?

[TWBSupport]

Hi all -
I forwarded on all of your suggestions to the dba & he said he would look at it.

The only thing I see that he changed is the following in the init.ora:
db_file_multiblock_read_count = 8 from 32
db_block_buffers = 61035 (which still does not seem to be enough to me)from 25000
pga_aggregate_target = 500000000 from 24000000
db_writer_processes = 2 (wasn't originally there)

Nothing has changed with the large_pool_size = 8000000

Queries still affecting performance...

I have learned a little more on the file system swap that I created when this problem first arrived. Filesystem is not the way to go, the next time we have down time I will delete that swap & re-create it as a device, with hopes that might help some.

I'm sorry I can't hand out points as of yet, since the suggestions have not been done yet. He states he is using the stat tool you state here....

History recap...
3G Oracle 9i database on 11i, 8G RAM, 4G FSSwap, 4G Device Swap.

I will keep you updated.

[TWBSupport]

TG -
Sorry I forgot to answer your question, no the buffer did not end up being reduced. We had some other db problems happening over the holidays & this got missed. It is still on my list to get done with the next downtime we have.