Category Archives: pga

Pga Monitoring Views In Oracle

By
The PGA(program or process global area)is a memory area(ram) that stores data and control information for a single process.
It typically contais a sort_area,hash_area,session_cursor cache.
pga areas can be sized manually by setting parameters like hash_area_size,sort_area_size
Hash_Area_Size
Oracle hash_area_size is simple. The hash_area_size parameter value defaults to 1.5 times sort_area_size
and is used for performing hash joins of Oracle tables. The higher the value for hash_area_size, the higher
the propensity for the CBO to use a hash join.
The value for hash_area_size is quickly the oracle show parameters command
SQL > show parameter hash_area_size;
The hash_area_size is obsolete if you are using pga_aggregate_target,but in oracle 9i with
pga_aggregate_target,a hash area size cannot exceed 5% of the pga area many increase the
hash_area_size with “alter session get hash_area_size” or with a use_hash hint.
Sort_Area_Size
The sort_area_size parameters control the RAM size for dedicated tasks to sort SQL result sets and reduce
expensive disk sorts.
The usage for sort_area_size changed when pga_aggregate_target was introduced.
The sort_area_size is ignored when pga_aggregate_target is set and when workarea_size_policy =auto,
unless you are using a old feature such as the MTS (shared servers).
If dedicated server connections are used, the sort_area_size parameter is ignored unless you set
workarea_size_policy=manual.  You can still use sort_area_size at the session level, but there are special
tricks for sorting very large result sets in RAM because of the default governor on sort_area_size which
imposes a limit on the amount of sort area any session may acquire:
SQL > alter session set workarea_size_policy=manual;
SQL > alter session set sort_area_size=nnnn;
Bitmap_Merge_Area_Size
BITMAP_MERGE_AREA_SIZE is relevant only for systems containing bitmap indexes.
It specifies the amount of memory Oracle uses to merge bitmaps retrieved from a range scan of the index.
The default value is 1 MB.
A larger value usually improves performance, because the bitmap segments must be sorted before being
merged into a single bitmap.
SQL > show parameter bitmap_merge_area_size
Create_Bitmap_Area_Size
CREATE_BITMAP_AREA_SIZE is relevant only for systems containing bitmap indexes.
It specifies the amount of memory (in bytes) allocated for bitmap creation.
The default value is 8 MB.
A larger value may speed up index creation.
the number of unique values in a column in relation to the number of rows in the table.
If cardinality is very small, you can set a small value.
SQL > show parameter create_bitmap_area_size
Pga_Aggregate_Target
  • PGA_AGGREGATE_TARGET specifies the target aggregate PGA memory available to all server processes attached to the instance.
  • The default value for PGA_AGGREGATE_TARGET is non zero. Oracle sets it’s value to 20% of the
  • SGA or 10 MB, whichever is greater.
  • Setting PGA_AGGREGATE_TARGET to a nonzero value has the effect of automatically setting the
  • WORKAREA_SIZE_POLICY parameter to AUTO.
  • This means that SQL working areas used by memory-intensive SQL operators such as sort, group-by,
  • hash-join, bitmap merge, and bitmap create will be automatically sized.
  • In that case we don’t have to bother about settings of sort_area_size , hash_area_size etc.
  • If you set PGA_AGGREGATE_TARGET to 0 then oracle automatically sets the WORKAREA_SIZE_POLICY parameter to MANUAL.
  • This means that SQL workareas are sized using the *_AREA_SIZE parameters.
  • The memory allocated for PGA_AGGREGATE_TARGET has no relation with SGA_TARGET.
  • The similarity is both is taken from total memory of the system.
  • The minimum value of this parameter is 10 MB and maximum is 4096 GB – 1.
SQL >show parameter pga_aggregate_target
Workarea_Size_Policy
WORKAREA_SIZE_POLICY specifies the policy for sizing work areas. This parameter controls the
mode in which working areas are tuned.
AUTO
Work areas used by memory-intensive operators are sized automatically, based on the PGA memory used
by the system, the target PGA memory set in PGA_AGGREGATE_TARGET, and the requirement of
each individual operators
MANUAL
The sizing of work areas is manual and based on the values of the *_AREA_SIZE parameter
corresponding to the operation (for example, a sort uses SORT_AREA_SIZE). Specifying MANUAL
may result in sub-optimal performance and poor PGA memory utilization
SQL > show parameter workarea_size_policy
PGA usage statistics:
SQL > select * from v$pgastat;
Determine a good setting for pga_aggregate_target:
SQL > select * from v$pga_target_advice order by pga_target_for_estimate;
Show the maximum PGA usage per process:
SQL > select max(pga_used_mem), max(pga_alloc_mem), max(pga_max_mem) from v$process;

learing abbout postgresql database and oracle database and cricket Entertainment

Program Global Area (Or) Process Global Area-Architecture Of Oracle(Pga)

By

1.Its mainly used for sorting purpose

2.A PGA is nonshared memory created by Oracle Database when a server or background process is started.
3.Using v$session we can check whether dedicated server or shared server
4.One pga for each server process
5.One PGA exists for each Server Process and each Background Process.
6.It stores data and control information for a single Server Process or a single Background Process.
7.The Program Global Areas (PGA) are memory regions that contain data and control information for a server or background process.  
     

            Image  
            Monitor

            PGA usage statistics:
            select * from v$pgastat;

            Determine a good setting for pga_aggregate_target:
             select * from v$pga_target_advice order by pga_target_for_estimate;

            Show the maximum PGA usage per process:
            select max(pga_used_mem), max(pga_alloc_mem), max(pga_max_mem) from v$process;

            Pga memory divede into three area
            1. SESSION MEMORY
            2. PRIVATE SQL AREA
            3. SQL WORK AREA
            1.Session Memory:/session information:
            Session memory is the memory allocated to hold a session’s variables (logon information) and other information related to the session.
            for eg:
            deine_editor=vi
            set serveroutput on;
            set autotrace on
            set pagesize;
            set linesize;
            monitor session information:-
            we can check total PGA memory used by processes using this below query
            SELECT ROUND(SUM(pga_used_mem)/(1024*1024),2) PGA_USED_MB FROM v$process;
            we can find PGA usage for a specific session using this below query
            SELECT SID, b.NAME, ROUND(a.VALUE/(1024*1024),2) MB FROM
            v$sesstat a,  v$statname b
            WHERE (NAME LIKE ‘%session uga memory%’ OR NAME LIKE ‘%session pga memory%’)
            AND a.statistic# = b.statistic#
            AND SID = 80;
            To calculate the amount of memory that you gone need for PGA, estimate the number of maximum connected sessions and run:
            SELECT :MAX_CONNECTED_SESSIONS*(2048576+P1.VALUE+P2.VALUE)/(1024*1024) YOU_NEED_PGA_MB
            FROM V$PARAMETER P1, V$PARAMETER P2
            WHERE P1.NAME = ‘sort_area_size’
            AND P2.NAME = ‘hash_area_size’;

            2.PRIVATE SQL AREA:-
            Stores information for a parsed SQL statement – stores bind variable values and runtime memory allocations.
            Cursor information also maintained in this area
            Dedicated Server environment  – the Private SQL Area is located in the Program Global Area.
               Image

              Shared Server environment      – the Private SQL Area is located in the System Global Area.
            Image
            However, if a session is connected through a shared server, part of the private SQL area is kept in the SGA
            Private sql area have two part
            i)PERSISTENT  AREA:-
            The private SQL area contains data such as bind variable values, query execution state information, and query execution work areas,The persistent area contains the bind variable values and this area is released only when the cursor is closed.

            ii)Runtime memory:
            The runtime area contains information about an executed query, including the progress of a full table scan and the current state of the SQL work areas. These PGA areas are allocated to run RAM intensive operations such as sorting and hash-joins. For DML operations, a runtime area is released when the command completes.
            Tempory information,parameters.
            Query execution statment info…etc
            3.SQL WORK AREA:-
            • These areas are allocated as needed for memory-intensive operations like sorting or hash-joins.
            • Memory allocated for sort, hash-join, bitmap merge, and bitmap create types of operations.
            • For DML, the run-time area is freed when the statement finishes running.For queries, it is freed after all rows are fetched or the query is canceled.
            • Oracle 9i and later versions enable automatic sizing of the SQL Work Areas by setting the WORKAREA_SIZE_POLICY = AUTO parameter (this is the default!)
            PGA parameters:-
            SORT_AREA_SIZE=size                      —order by,group by,roll up,window   functions                                                     
            HASH_AREA_SIZE=size                      —where   clause
                                                                                                                      
            BITMAP_MERGE_AREA_SIZE=size   —index merge area                                                                                                                
            CREATE_BITMAP_AREA_SIZE =size —Index creation area
            1.the above parameters from 9i,
            PGA_AGGREGATE_TARGET = size are individually and seperately allocated manually
            2. the above parameters from 10g,
            PGA_AGGREGATE_TARGET = size
            WORKAREA_SIZE_POLICY = auto/manual
            if you set above workarea_size_policy=manual then set size above all parameter manually
            otherwise f you set workarea_size_policy=auto then no need to set size above all parameter it automatically set memory all the  parameter

            3.the above parameters from 11g(AMM),

            MEMORY_TARGET=SGA+PGA

            MEMORY_TARGET: The amount of shared memory available for Oracle to use when dynamically controlling the SGA and PGA. This parameter is dynamic, so the total amount of memory available to Oracle can be increased or decreased, provided it does not exceed the MEMORY_MAX_TARGET limit. The default value is “0”.

            MEMORY_MAX_TARGET: This defines the maximum size the MEMORY_TARGET can be increased to without an instance restart. If the MEMORY_MAX_TARGET is not specified, it defaults to MEMORY_TARGET setting.
            # df -k /dev/shm
            Filesystem           1K-blocks      Used Available Use% Mounted on
            tmpfs                  1029884    350916    678968  35% /dev/shm
            #
            The shared memory file system should be big enough to accommodate the MEMORY_TARGET and MEMORY_MAX_TARGET values, or Oracle will throw the following error.
            ORA-00845: MEMORY_TARGET not supported on this system
            To adjust the shared memory file system size issue the following commands, specifying the required size of shared memory.
            # umount tmpfs
            # mount -t tmpfs shmfs -o size=1200m /dev/shm
            Make the setting permanent by amending the “tmpfs” setting of the “/etc/fstab” file to look like this.
             tmpfs                   /dev/shm                tmpfs   size=1200m   0  0

            Configuration Of AMM
            The Database Configuration Assistant (DBCA) allows you to configure automatic memory management during database creation.

            When creating the database manually, simply set the appropriate MEMORY_TARGET and MEMORY_MAX_TARGET initialization parameters before creating the database.
            Enabling automatic memory management on a system that didn’t previously use it is a simple task. Assuming you want to use a similar amount of memory to your current settings you will need to use the following calculation.
            MEMORY_TARGET = SGA_TARGET + GREATEST(PGA_AGGREGATE_TARGET, “maximum PGA allocated”)
            The following queries show you how to display the relevant information and how to combine it in a single statement to calculate the required value.
            Individual values.
            COLUMN name FORMAT A30
            COLUMN value FORMAT A10
            SELECT name, value
            FROM   v$parameter
            WHERE  name IN (‘pga_aggregate_target’, ‘sga_target’)
            UNION
            SELECT ‘maximum PGA allocated’ AS name, TO_CHAR(value) AS value
            FROM   v$pgastat
            WHERE  name = ‘maximum PGA allocated’;
            Calculate MEMORY_TARGET
            SELECT sga.value + GREATEST(pga.value, max_pga.value) AS memory_target
            FROM (SELECT TO_NUMBER(value) AS value FROM v$parameter WHERE name = ‘sga_target’) sga,
                (SELECT TO_NUMBER(value) AS value FROM v$parameter WHERE name = ‘pga_aggregate_target’) pga,
                (SELECT value FROM v$pgastat WHERE name = ‘maximum PGA allocated’) max_pga;

            Assuming our required setting was 5G, we might issue the following statements.
            CONN / AS SYSDBA
            Set the static parameter. Leave some room for possible future growth without restart.
            ALTER SYSTEM SET MEMORY_MAX_TARGET=6G SCOPE=SPFILE;

            Set the dynamic parameters. Assuming Oracle has full control.
            ALTER SYSTEM SET MEMORY_TARGET=5G SCOPE=SPFILE;
            ALTER SYSTEM SET PGA_AGGREGATE_TARGET=0 SCOPE=SPFILE;
            ALTER SYSTEM SET SGA_TARGET=0 SCOPE=SPFILE;
            Restart instance.
            SHUTDOWN IMMEDIATE;
            STARTUP;
            Once the database is restarted the MEMORY_TARGET parameter can be amended as required without an instance restart.
            ALTER SYSTEM SET MEMORY_TARGET=4G SCOPE=SPFILE;
            AMM MONITOR
            SELECT  component, current_size, min_size, max_size
            FROM    v$memory_dynamic_components
            WHERE   current_size != 0;
            COMPONENT                      CURRENT_SIZE   MIN_SIZE   MAX_SIZE
            —————————— ———— ———- ———-
            shared pool                       197132288  192937984  197132288
            large pool                          4194304    4194304    4194304
            java pool                          41943040   41943040   41943040
            SGA Target                        318767104  285212672  318767104
            DEFAULT buffer cache               71303168   41943040   75497472
            PGA Target                        104857600  104857600  138412032
            6 rows selected.
            The V$MEMORY_CURRENT_RESIZE_OPS and V$MEMORY_RESIZE_OPS views provide information on current and previous component resize operations.

            The V$MEMORY_TARGET_ADVICE view provides information to help tune the MEMORY_TARGET parameter. It displays a range of possible MEMORY_TARGET settings, as factors of the current setting, and estimates the potential DB Time to complete the current workload based on these memory sizes.
            SELECT * FROM v$memory_target_advice ORDER BY memory_size;
            MEMORY_SIZE MEMORY_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR    VERSION
            ———– —————— ———— ——————- ———-
                   303                .75         3068              1.0038          2
                   404                  1         3056                   1          2
                   505               1.25         3056                   1          2
                   606                1.5         3056                   1          2
                   707               1.75         3056                   1          2
                   808                  2         3056                   1          2
            6 rows selected.

            PGA VIEWs:
            Statistics on allocation and use of work area memory can be viewed in the following dynamic performance views:
            V$SYSSTAT
            V$SESSTAT
            V$PGASTAT
            V$SQL_WORKAREA
            V$SQL_WORKAREA_ACTIVE
            The following three columns in the V$PROCESS view report the PGA memory allocated and used by an Oracle process:
            PGA_USED_MEM
            PGA_ALLOCATED_MEM
            PGA_MAX_MEM
            MEMORY MANAGEMENT IN ORACLE:
            1.Automatic memory management:
            DBA specifies the target size for instance memory.
            The database instance automatically tunes to the target memory size.
            Database redistributes memory as needed between the SGA and the instance PGA.

            2. Automatic shared memory management:
            This management mode is partially automated.
            DBA specifies the target size for the SGA.
            DBA can optionally set an aggregate target size for the PGA or managing PGA work areas individually.
            3.Manual memory management:
            Instead of setting the total memory size, the DBA sets many initialization parameters to manage components of the SGA and instance PGA individually
            NOTE: if pga is fill the all queries will go to temporay tablespace that is disk level sorting

            learing abbout postgresql database and oracle database and cricket Entertainment