Home » RDBMS Server » Performance Tuning » how find SGA size is sufficient or not? (oracle 10.2.0.3 solaris 10)
how find SGA size is sufficient or not? [message #335585] Tue, 22 July 2008 17:46 Go to next message
skumar.dba
Messages: 82
Registered: October 2007
Member
Experts,

how to find weather SGA size is sufficient or not. i am observing from EM console that DB_Buffer_cache is undersized.

is there any best way to find DB_BUFFER_CACHE,LIBRIRARY_CAHCE, SHARED_POOL SIZE is sufficient or not.
Re: how find SGA size is sufficient or not? [message #335586 is a reply to message #335585] Tue, 22 July 2008 17:50 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above

here are some free clues.

  1  select view_name from dba_views where view_name like 'DBA%ADVI%'
  2* order by 1
SQL> /

VIEW_NAME
--------------------------------------------------------------------------------
DBA_ADVISOR_ACTIONS
DBA_ADVISOR_COMMANDS
DBA_ADVISOR_DEFINITIONS
DBA_ADVISOR_DEF_PARAMETERS
DBA_ADVISOR_DIRECTIVES
DBA_ADVISOR_FINDINGS
DBA_ADVISOR_JOURNAL
DBA_ADVISOR_LOG
DBA_ADVISOR_OBJECTS
DBA_ADVISOR_OBJECT_TYPES
DBA_ADVISOR_PARAMETERS
DBA_ADVISOR_PARAMETERS_PROJ
DBA_ADVISOR_RATIONALE
DBA_ADVISOR_RECOMMENDATIONS
DBA_ADVISOR_SQLA_REC_SUM
DBA_ADVISOR_SQLA_WK_MAP
DBA_ADVISOR_SQLA_WK_STMTS
DBA_ADVISOR_SQLW_COLVOL
DBA_ADVISOR_SQLW_JOURNAL
DBA_ADVISOR_SQLW_PARAMETERS
DBA_ADVISOR_SQLW_STMTS
DBA_ADVISOR_SQLW_SUM
DBA_ADVISOR_SQLW_TABLES
DBA_ADVISOR_SQLW_TABVOL
DBA_ADVISOR_SQLW_TEMPLATES
DBA_ADVISOR_TASKS
DBA_ADVISOR_TEMPLATES
DBA_ADVISOR_USAGE
DBA_HIST_DB_CACHE_ADVICE
DBA_HIST_JAVA_POOL_ADVICE
DBA_HIST_MTTR_TARGET_ADVICE
DBA_HIST_PGA_TARGET_ADVICE
DBA_HIST_SGA_TARGET_ADVICE
DBA_HIST_SHARED_POOL_ADVICE
DBA_HIST_STREAMS_POOL_ADVICE

35 rows selected.
Re: how find SGA size is sufficient or not? [message #335609 is a reply to message #335585] Tue, 22 July 2008 23:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
is there any best way to find DB_BUFFER_CACHE,LIBRIRARY_CAHCE, SHARED_POOL SIZE is sufficient or not.

Without any expertise in Oracle, no.
Follow EM and the advisors.

The other way is to read Database Concepts and Database Performance Tuning Guide.

Regards
Michel
Re: how find SGA size is sufficient or not? [message #335739 is a reply to message #335609] Wed, 23 July 2008 08:07 Go to previous messageGo to next message
sunil_v_mishra
Messages: 506
Registered: March 2005
Senior Member
Sizing SGA Wink

Define the SGA
Oracle Instance is made up of Back ground process and memory, oracle uses shared memory for its working this shared memory is composed of SGA and PGA.

Simply stated, the system global area (SGA) is just shared memory structures that are created at instance startup, hold information about the instance and control its behavior.

• How you would configure SGA for a mid-sized OLTP environment?

Oracle Database 10g automates the management of shared memory used by an instance and liberates administrators from having to manually configure the sizes of shared memory components.

Automatic Shared Memory Tuning significantly simplifies Oracle database administration by introducing a more dynamic, flexible and adaptive memory management scheme. It makes more effective use of available memory therefore reducing the cost incurred on acquiring additional hardware.

In automatic memory management feature we can set value of SGA_TARGET in parameter file. Oracle it self manage space in SGA as per the requirement.

Allowing Oracle to take Control

There is really nothing to switching into automatic shared memory tuning. You only need to set the SGA_TARGET parameter.

1. Take a look and see if you are already in automated sizing of SGA

SQL> show parameter sga_target
NAME TYPE VALUE
------------------------------------ ----------- --------
sga_target big integer 0

2. Alter system to begin automated sizing of SGA

SQL> alter system set sga_target=216m;
System altered.

3. Done
There is no thumb rule to configure SGA
SGA is made up of 5 parts
Suppose if we consider SGA of 1 GB than and we allocate
1. Shared pool as 48% of SGA
2. Database buffer as 48% of SGA.
3. Log Buffer as 4 MB of SGA.
4. Java pool 20 MB
5. Large pool 20 MB.

But if SGA is more than 1 GB suppose 10 GB than above mention % of all part will not suit, like for 1GB 4 MB was log buffer but for 10GB 40MB for log buffer may be too much.

Suppose only Oracle is running on the server and MTS is chosen for OLTP.

Reserve 10% of RAM for UNIX/Linux or 20% of RAM for Windows, the rest of RAM is allocated to SGA.
Log_buffer=3-4M

Large_pool_size: For dedicated Oracle server, 20-30M is enough. For MTS, the UGA will be here. Estimate parallel connection and MTS server processes.

Java_pool_size=10M

Shared_pool_size: If all the SQL statements that sent to ORACLE are using bind variable adequately, then 300M is enough in most cases and it should greater than 100M depending on total RAM.

Data buffer: All the rest RAM should be allocated to Data buffer.

Below is some referenced materials related to this issue:

If you only have Oracle on the server, start by reserving 10% of RAM for UNIX/Linux or 20% of RAM for Windows. With whatever RAM is left-over for SGA Sizing and PGA Sizing

For dedicated Oracle servers, the maximum total RAM SGA size can be computed as follows:

OS Reserved RAM — This is RAM required to run the OS kernel and system functions, 10% of total RAM for UNIX/Linux, and 20% of total RAM for Windows.

Oracle Database Connections RAM — Each Oracle connection requires OS RAM regions for sorting and hash joins. (This does not apply when using the Oracle multithreaded server or pga_aggregate_target.) The maximum amount of RAM required for a session is as follows:

2 megabytes RAM session overhead + sor_area_size + hash_area_size

So Automatic Memory management is a best option for configuring SGA..

• What is involved in tuning the SGA?

1. Check the statspack report.
2. Check hit ratio of Data buffer. If it is less than 90%, then we need to increase the Data buffer.
3. Check hit ratio of Shared pool. If it is less than 95%, then we need to increase the Shared pool.
4. Check log buffer. If redo buffer allocation retries/redo entries is greater than 1%, then we need to increase log_buffer.
5. Determine how to use keep pool and recycle pool efficiently.
Re: how find SGA size is sufficient or not? [message #335742 is a reply to message #335739] Wed, 23 July 2008 08:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I just read the last paragraph and I have to say:

2. Check hit ratio of Data buffer. If it is less than 90%, then we need to increase the Data buffer.
Wrong. Hit ratio is meaningless as it just misleading.

3. Check hit ratio of Shared pool. If it is less than 95%, then we need to increase the Shared pool.
Wrong. Hit ratio is meaningless as it just misleading.

4. Check log buffer. If redo buffer allocation retries/redo entries is greater than 1%, then we need to increase log_buffer.
Wrong. Hit ratio is meaningless as it just misleading.

5. Determine how to use keep pool and recycle pool efficiently.
Wrong. Never use these pools unless you are an expert.

Regards
Michel

[Updated on: Wed, 23 July 2008 08:14]

Report message to a moderator

Re: how find SGA size is sufficient or not? [message #335779 is a reply to message #335742] Wed, 23 July 2008 13:17 Go to previous messageGo to next message
sunil_v_mishra
Messages: 506
Registered: March 2005
Senior Member
Micheal in my previous post the point that i mention was for what is involved in tunning SGA?,Answer i gave was in general... nothing specific... Any ways things which are involved is as below...


Sad TUNNING SHARED POOL

Shared pool is divided in to two parts
 Library cache
 Dictionary cache

• Shared pool should not be free.
• In ideal case it should be nearly 4-5 % free, free below it will apply LRU.
• It is good if it is filled.

TUNNING LIBRARY CACHE (USE IN REUSABILITY)
There is no parameter to define Library cache Size.

Goal
• Reusability of SQL.
• Increase hit Ratio.

If hit ratio is less Solution is increase size of shared pool.

Reasons For library cache miss,
1. Oracle is applying LRU
2. INVALIDATIONS

What is invalidation?
If already stored SQL in library cache is marked as invalid.

Why this happen?
Whenever objects involved in a SQL are identified after SQL is loaded into library cache.

Why pinning important PL/SQL and simple SQL into library cache?
So that oracle does not apply LRU to important SQL.

PL is stored in Database and SQL is stored in Application.

Keeping important PL/SQL code in memory.
==>/rdbms/admin/dbmspool.sql

TUNNING DICTIONARY CACHE

Dictionary cache stores details of Data dictionary.

Solution :-if dictionary cache hit ratio is less than 95% increase size of shared pool.

Dictionary cache hit ratio is there for performance tunning

Hit ratio should be greater than 95%.

TUNNING DATABASE BUFFER

TUNNING LOG BUFFER


Oracle must flush log buffer immediately. Tunning is related to the size of log buffer.

Size has 2 criteria.
1. No space is available in log buffer.
2. Not enough space is available in log buffer.

Solution increase the size of log buffer.

JAVA POOL and Large Pool are also in SGA... so if required we have to tune...Razz

[Updated on: Thu, 24 July 2008 01:04]

Report message to a moderator

Re: how find SGA size is sufficient or not? [message #335780 is a reply to message #335779] Wed, 23 July 2008 13:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Your advices smell good old V7 version.

You never tune with hit ratio.
You first tune application and SQL then on wait events (more precisely on where instance spend its time). Hit ratios are just there to warn that something changes if they change, nothing more.

I can build a very bad application that gives you the 95% or 98% hit ratio you want and you will think everything is good when I kill the server.

Regards
Michel
Re: how find SGA size is sufficient or not? [message #335874 is a reply to message #335780] Thu, 24 July 2008 01:11 Go to previous messageGo to next message
sunil_v_mishra
Messages: 506
Registered: March 2005
Senior Member
You are correct micheal

First tune application and SQL then on wait events (more precisely on where instance spend its time). Hit ratios are just there to warn that something changes if they change, nothing more.

But If application and SQL seems to be good .. still performance is a problem...?.. then what we have to tune....?

Can we go for O/S tunning also Smile?
Re: how find SGA size is sufficient or not? [message #335888 is a reply to message #335874] Thu, 24 July 2008 01:44 Go to previous message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
OS tuning is the last step after instance one.

See the good old "Oracle8i Designing and Tuning for Performance", Chapter 2 "Performance Tuning Methods", Section "Prioritized Tuning Steps"

Regards
Michel

Previous Topic: Is possible to turn the following into a view
Next Topic: Testing of generated test data from Sequence
Goto Forum:
  


Current Time: Wed Dec 07 10:39:39 CST 2016

Total time taken to generate the page: 0.11167 seconds