Popular White Paper On This Topic

Did you copy paste those settings?
Check the dump destinations since there is a space in cdump and udump that should not be there.
Is your application an off the shelf purchased one?
Why do you preset memory management parameters? Is this following solution provider recommendations?
How many concurrent users are online? is the PGA (8 GB) sized acordingly vs SGA (6 GB)?
What are your tools for investigating DB performance?
Can you run an AWR report for a typical day's length and check if you have blocking sessions and if so why?

Please do not run AWR unless you have the correct license from Oracle.

Why have you set SGA_TARGET=0?
your PGA look far to large, have you actually monitoried PGA statistics?

This looks like a set of parameters that have come from an application configured originally in 8i or 9i and the application vendor has not taken into consideration the memory management capabilities of 10g.

I have copied and pasted those settings
the pga is 0.8 gb and sga is 6 gb
we basically use toad to monitor the database
actually these settings were defined in the beginning of the application deployment
there will be around 100 concurrent sessions at any given point of time.

i need to apply the best settings for optimal database performance.

Pls. give your expert advice on what has to be changed and what will be the implication, if i can understand the impact then I can tweak the settings and test and reach to a optimal configuration.

Anyway, I don't believe there is an "optimal" setting for your database, unless you can guarantee that the user and data load, and any overnight batch loads, will be the same and consistent all the time, and match the resources available. That rarely happens.

It is still not clear WHAT resources were available to you on the old system (memory and CPU), and STILL not clear if your new system is different to this, AND you need to see if there is excessive swapping going on (that will have a very serious affect on any servers performance, running any application, including Oracle). If your users queries are taking, say 10 times longer than before to complete, then the chances of getting deadlocks (and locks in general, that will often lead to deadlocks over time) vastly increase, and you then get a downward spiral of performance.

You will need to ensure that the new server has adequate resource to operate NORMALLY, THEN think about the "fine tuning" as you call it (but most of the time, this really means tuning the application queries to be more efficient).

You could increase the PGA_AGGREGATE_TARGET, up to 4 times its current value, but if the bottleneck is elsewhere, it might make hardly any difference. You needs to use the diagnostic facilities you have in the Oracle Database Server software itself, and if you are licensed, run AWR reports, and compare between your current and old system, so at least you know here to target the resource issue. Then fine tune AFTER fixing your major issue.

Copyright 1998-2015 Ziff Davis, LLC (Toolbox.com). All rights reserved. All product names are trademarks of their respective companies. Toolbox.com is not
affiliated with or endorsed by any company listed at this site.