Result Cache In Sga -Oracle Architecture

December 4, 2016

When a query is executed for the very first time, the user’s process searches for the data in the database buffer cache. If data is there, it uses it. otherwise, it performs an I/O operation to retrieve data from the datafile on disk into the buffer cache, and from this data, the final result set is displayed.

if another query requires the same data set, the process uses the data from the buffer cache to build the result set required by the user. only if it is present in buffer cache.

The Result Cache is an area in the shared pool and contains the end results of a query execution.

The Result Cache can be managed either on the client side or the server side.

Client side

Result Cache implementation would require the application to use the Oracle Call Interface (OCI) calls.

Server side

The query could be executed with a /*+ RESULT CACHE */ hint (or) the result_cache_mode parameter could be set to AUTO.

If result_cache_mode parameter is set AUTO, It moves all query results to the Result Cache section of the shared pool.

Cannot cache the result of:

When a query is executed for the very first time.

when the cache is flushed.

Pga Monitoring Views In Oracle

December 4, 2016

By postgresdba

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;

AMM (Automatic Memeory Managment) 11g Oracle

December 1, 2016

By postgresdba

The basic memory structures associated with Oracle Database include:

System Global Area (SGA)

The SGA is a group of shared memory structures, known as SGA components, that contain data and control information for one Oracle Database instance. The SGA is shared by all server and background processes.

Program Global Area (PGA)

A PGA is a memory region that contains data and control information for a server process. It is nonshared memory created by Oracle Database when a server process is started. Access to the PGA is exclusive to the server process. There is one PGA for each server process. Background processes also allocate their own PGAs. The total PGA memory allocated for all background and server processes attached to an Oracle Database instance is referred to as the total instance PGA memory, and the collection of all individual PGAs is referred to as the total instance PGA, or just instance PGA.

It contains global variables and data structures and control information for a server process. example of such information is the runtime area of a cursor. Each time a cursor is executed, a new runtime area is created for that cursor in the PGA memory region of the server process executing that cursor.

The performance of complex long running queries, typical in a DSS environment, depend to a large extent on the memory available in the Program Global Area (PGA) which is also called work area.

Below figure illustrates the relationships among these memory structures.

Oracle Memory Structure

Memory management has evolved with each database release

Oracle 9i

Beginning with Oracle9i, the dynamic SGA infrastructure allowed for the sizing of the Buffer Cache, Shared Pool and the Large Pool without having to shutdown the database. Key features being

Dynamic Memory resizing
DB_CACHE_SIZE instead of DB_BLOCK_BUFFERS
DB_nK_CACHE_SIZE for multiple block sizes
PGA_AGGREGATE_TARGET Introduction of Automatic PGA Memory management

Oracle Database 10g

Automatic Shared Memory Management (ASMM) was introduced in 10g. You enable the automatic shared memory management feature by setting the SGA_TARGET parameter to a non-zero value.

Oracle Database 11g

Automatic Memory Management is being introduced in 11g. This enables automatic tuning of PGA and SGA with use of two new parameters named MEMORY_MAX_TARGET and MEMORY_TARGET.

The most important SGA components are the following:

Database Buffer Cache:

The database buffer cache is the portion of the SGA that holds copies of data blocks read from datafiles. All users concurrently connected to the instance share access to the database buffer cache.

DB_CACHE_SIZE

DB_KEEP_CACHE_SIZE

DB_RECYCLE_CACHE_SIZE

DB_nK_CACHE_SIZE

Redo Log Buffer:

The redo log buffer is a circular buffer in the SGA that holds information about changes made to the database. This information is stored in redo entries. Redo entries contain the information necessary to reconstruct, or redo, changes made to the database by INSERT, UPDATE, DELETE, CREATE, ALTER, or DROP operations. Redo entries are used for database recovery, if necessary.

LOG_BUFFER

Shared Pool:

The shared pool portion of the SGA contains the library cache, the dictionary cache, the result cache, buffers for parallel execution messages, and control structures.

SHARED_POOL_SIZE

SHARED_POOL_RESERVED_SIZE

RESULT_CACHE_SIZE *

Large Pool:

Used for allocating session memory for shared server, Oracle XA, or parallel query buffers or for RMAN.

LARGE_POOL_SIZE

Java Pool :

Java pool memory is used in server memory for all session-specific Java code and data within the JVM.

JAVA_POOL_SIZE

Streams Pool :

The streams pool is used exclusively by Oracle Streams. The Streams pool stores buffered queue messages, and it provides memory for Oracle Streams capture processes and apply processes.

STREAMS_POOL_SIZE

RESULT_CACHE_MAX_SIZE is new component which has been introduced as part of 11g Memory architecture. The result cache is composed of the SQL query result cache and PL/SQL function result cache, which share the same infrastructure.Results of queries and query fragments can be cached in memory in the SQL query result cache. The database can then use cached results to answer future executions of these queries and query fragments. Similarly PL/SQL Function Result can also be cached.

You have to use RESULT_CACHE_MODE initialization parameter which determines the SQL query result cache behavior. The possible initialization parameter values are MANUAL and FORCE.

You can use various memory management types from following:

For Both the SGA and Instance PGA – Automatic Memory Management

For the SGA – Automatic Shared Memory Management

For the Instance PGA – Automatic PGA Memory Management

1.Automatic Memory Management (SGA & PGA)

Oracle Database can manage the SGA memory and instance PGA memory completely automatically by setting 2 parameters, MEMORY_MAX_TARGET and MEMORY_TARGET. You designate only the total memory size to be used by the instance, and Oracle Database dynamically exchanges memory between the SGA and the instance PGA as needed to meet processing demands. This capability is referred to as automatic memory management. With this memory management method, the database also dynamically tunes the sizes of the individual SGA components and the sizes of the individual PGAs. To do so (on most platforms), you set only a target memory size initialization parameter (MEMORY_TARGET) and optionally a maximum memory size initialization parameter (MEMORY_MAX_TARGET).

If you create your database with Database Configuration Assistant (DBCA) and choose the basic installation option, automatic memory management is enabled. If you choose advanced installation, Database Configuration Assistant (DBCA) enables you to select automatic memory management.

Switching to Automatic Memory Management

1)Check the current values configured for SGA_TARGET and PGA_AGGREGATE_TARGET.

SQL> show parameter TARGET

NAME TYPE VALUE

—————————— ————– ————–

archive_lag_target integer 0

db_flashback_retention_target integer 1440

fast_start_io_target integer 0

fast_start_mttr_target integer 0

memory_max_target big integer 0

memory_target big integer 0

parallel_servers_target integer 256

pga_aggregate_target big integer 1G

sga_target big integer 1G

SQL> show parameter cache_size

NAME TYPE VALUE

—————————— ————– ————–

client_result_cache_size big integer 0

db_16k_cache_size big integer 0

db_2k_cache_size big integer 0

db_32k_cache_size big integer 64M

db_4k_cache_size big integer 64M

db_8k_cache_size big integer 0

db_cache_size big integer 1520M

db_flash_cache_size big integer 0

db_keep_cache_size big integer 0

db_recycle_cache_size big integer 0

Add the values of pga_aggregate_target and sga_target.

2)Decide on a maximum amount of memory that you would want to allocate to the database which will determine the maximum value for the sum of the SGA and instance PGA sizes. In our case we decide to set to 808M

3)Change the parameter in initialization parameter file.

Using Spfile

SQL>ALTER SYSTEM SET MEMORY_MAX_TARGET = 8G SCOPE = SPFILE;

SQL>ALTER SYSTEM SET MEMORY_TARGET = 8G SCOPE = SPFILE;

SQL>ALTER SYSTEM SET SGA_TARGET =0 SCOPE = SPFILE;

SQL>ALTER SYSTEM SET PGA_AGGREGATE_TARGET = 0 SCOPE = SPFILE;

Using Pfile

If you have started the instance with Pfile, then edit the pfile and set the parameters manually

MEMORY_MAX_TARGET = 8G

MEMORY_TARGET = 8G

SGA_TARGET =0

PGA_AGGREGATE_TARGET = 0

In case you do not specify any value for MEMORY_MAX_TARGET and only use MEMORY_TARGET then database automatically sets MEMORY_MAX_TARGET to the value of MEMORY_TARGET. BUT If you only set MEMORY_MAX_TARGET the database will not be in AMM mode, till you set MEMORY_TARGET

If you omit the line for MEMORY_TARGET and include a value for MEMORY_MAX_TARGET, the MEMORY_TARGET parameter defaults to zero. After startup, you can then dynamically change MEMORY_TARGET to a non-zero value, provided that it does not exceed the value of MEMORY_MAX_TARGET.

MEMORY_MAX_TARGET is a static parameter i.e it cannot be changed Dynamically and Instance has to be bounced for modifying the value. So ensure that you have set it to appropriate value. I have changed values to the following for demonstration purpose.

SQL>ALTER SYSTEM SET MEMORY_MAX_TARGET = 8G SCOPE=SPFILE;

SQL>ALTER SYSTEM SET MEMORY_TARGET = 8G SCOPE=SPFILE;

SQL>ALTER SYSTEM SET SGA_MAX_SIZE = 7G SCOPE=SPFILE;

SQL>ALTER SYSTEM SET SGA_TARGET = 7G SCOPE=SPFILE;

SQL>ALTER SYSTEM SET PGA_AGGREGATE_TARGET = 0 SCOPE=SPFILE;

SQL>ALTER SYSTEM SET SHARED_POOL_SIZE = 0 SCOPE=SPFILE;

SQL>ALTER SYSTEM SET JAVA_POOL_SIZE = 0 SCOPE=SPFILE;

SQL>ALTER SYSTEM SET DB_CACHE_SIZE = 0 SCOPE=SPFILE;

SQL>ALTER SYSTEM SET DB_CACHE_SIZE = 0 SCOPE=SPFILE

ERROR at line 1:

ORA-32017: failure in updating SPFILE

ORA-00383: DEFAULT cache for blocksize 8192 cannot be reduced to zero

If you get above error then try:

SQL> ALTER SYSTEM RESET DB_CACHE_SIZE SCOPE=SPFILE;

System altered.

ALTER SYSTEM SET DB_4K_CACHE_SIZE = 0 SCOPE=SPFILE;

ALTER SYSTEM SET DB_32K_CACHE_SIZE = 0 SCOPE=SPFILE;

I have to adjust the memory manual if I need to set the lower limit even when I am using AMM

4)Shutdown and startup the database

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area 7482626048 bytes

Fixed Size 2214416 bytes

Variable Size 2281702896 bytes

Database Buffers 5167382528 bytes

Redo Buffers 31326208 bytes

SQL> show parameter target

NAME TYPE VALUE

———————————— ———– ——————————

archive_lag_target integer 0

db_flashback_retention_target integer 1440

fast_start_io_target integer 0

fast_start_mttr_target integer 0

memory_max_target big integer 8G

memory_target big integer 8G

pga_aggregate_target big integer 1G

sga_target big integer 7G

The preceding steps instruct you to set SGA_TARGET and PGA_AGGREGATE_TARGET to zero so that the sizes of the SGA and instance PGA are tuned up and down as required, without restrictions. You can omit the statements that set these parameter values to zero and leave either or both of the values as positive numbers. In this case, the values act as minimum values for the sizes of the SGA or instance PGA.

Monitoring and Tuning Automatic Memory Management

The dynamic performance view V$MEMORY_DYNAMIC_COMPONENTS shows the current sizes of all dynamically tuned memory components, including the total sizes of the SGA and instance PGA.

The view V$MEMORY_TARGET_ADVICE provides tuning advice for the MEMORY_TARGET initialization parameter.

SQL> select * from v$memory_target_advice order by memory_size;

You can also use V$MEMORY_RESIZE_OPS which has a circular history buffer of the last 800 SGA resize requests.

2. Automatic Shared Memory Management – For the SGA

If you want to exercise more direct control over the size of the SGA, you can disable automatic memory management and enable automatic shared memory management.This feature was introduced in 10g with a parameter known as SGA_TARGET. When automatic SGA memory management is enabled, the sizes of the different SGA components are flexible and can adapt to the needs of current workload without requiring any additional configuration.

In case you have enabled Automatic Memory Management , then to switch to Automatic Shared Memory Management , please follow below procedure

SQL>Alter system set MEMORY_TARGET=0 scope=both;

SQL>Alter system set SGA_TARGET=500M scope=both;

3. Automatic PGA Memory Management – For the Instance PGA

While using Automatic memory management , PGA memory is allocated based upon value of MEMORY_TARGET. In case you enable automatic shared memory management or manual shared memory management, you also implicitly enable automatic PGA memory management.

Automatic/Manual PGA memory management is decided by initialization parameter WORKAREA_SIZE_POLICY which is a session- and system-level parameter that can take only two values: MANUAL or AUTO. The default is AUTO.

With automatic PGA memory management, you set a target size for the instance PGA by defining value for parameter named PGA_AGGREGATE_TARGET and sizing of SQL work areas is automatic and all *_AREA_SIZE initialization parameters are ignored for these sessions. This feature is available from 9i.

At any given time, the total amount of PGA memory available to active work areas on the instance is automatically derived from the parameter PGA_AGGREGATE_TARGET. This amount is set to the value of PGA_AGGREGATE_TARGET minus the PGA memory allocated for other purposes (for example, session memory). The resulting PGA memory is then allotted to individual active work areas based on their specific memory requirements.

In case you wish to manually specify the maximum work area size for each type of SQL operator (such as sort or hash-join) then you can enable Manual PGA Memory management.

Set WORKAREA_SIZE_POLICY value to MANUAL and also specify values for *_area_size such as SORT_AREA_SIZE, HASH_AREA_SIZE, BITMAP_MERGE_AREA_SIZE, and CREATE_BITMAP_AREA_SIZE, etc.

Although the Oracle Database 11g supports this manual PGA memory management method, Oracle strongly recommends that you leave automatic PGA memory management enabled.

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

December 1, 2016

By postgresdba

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.

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

SESSION MEMORY
PRIVATE SQL AREA
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.

Shared Server environment – the Private SQL Area is located in the System Global Area.

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

Architecture Of Oracle -2

December 1, 2016

By postgresdba

Larry Ellison and two friends and former co-workers, Bob Miner and Ed Oates, started a consultancy called Software Development Laboratories (SDL) in 1977. SDL developed the original version of the Oracle software.

Versions:-

Oracle Database 10g Release 2: 10.2.0.1–10.2.0.5 (Patchset as of April 2010 )

Oracle Database 11g Release 1: 11.1.0.6–11.1.0.7 (Patchset as of September 2008 )

Oracle Database 11g Release 2: 11.2.0.1–11.2.0.4 (Patchset as of August 2013 )

Oracle Database 12c Release 1: 12.1.0.1 (Patchset as of June 2013 )

What is An Oracle Database?

Basically, there are two main components of Oracle database –– instance and database itself. An instance consists of some memory structures and the background processes, whereas a database refers to the disk resources.

Figure 1 will show you the relationship.

1.Instance

Database files themselves are useless without the memory structures and processes to interact with the database. Oracle defines the term instance as the memory structure and the background processes used to access data from a database. The memory structuresand background processes contitute an instance. The memory structure itself consists of System Global Area (SGA), Program Global Area (PGA),and an optional area –– Software Area Code. In the other hand, the mandatory background processes are Database Writer (DBWn), Log Writer (LGWR), Checkpoint (CKPT), System Monitor (SMON), and Process Monito (PMON). And another optional background processes are Archiver (ARCn), Recoverer (RECO), etc.

Figure 2 will illustrate the relationship for those components on an instance.

A.System Global Area

SGA is the primary memory structures. When Oracle DBAs talk about memory, they usually mean the SGA. This area is broken into a few of part memory –– Buffer Cache, Shared Pool, Redo Log Buffer, Large Pool, and Java Pool.

Buffer Cache

Buffer cache is used to stores the copies of data block that retrieved from datafiles. That is, when user retrieves data from database, the data will be stored in buffer cache. Its size can be manipulated via DB_CACHE_SIZE parameter in init.ora initialization parameter file.

Shared Pool

Shared pool is broken into two small part memories –– Library Cache and Dictionary Cache. The library cache is used to stores information about the commonly used SQL and PL/SQL statements; and is managed by a Least Recently Used (LRU) algorithm. It is also enables the sharing those statements among users. In the other hand, dictionary cache is used to stores information about object definitions in the database, such as columns, tables, indexes, users, privileges, etc.

The shared pool size can be set via SHARED_POOL_SIZE parameter in init.ora initialization

parameter file.

Redo Log Buffer

Each DML statement (select, insert, update, and delete) executed by users will generates the redo entry. What is a redo entry? It is an information about all data changes made by users. That redo entry is stored in redo log buffer before it is written into the redo log files. To manipulate the size of redo log buffer, you can use the LOG_BUFFER parameter in init.ora initialization parameter file.

Large Pool

Large pool is an optional area of memory in the SGA. It is used to relieves the burden place on the shared pool. It is also used for I/O processes. The large pool size can be set by LARGE_POOL_SIZE parameter in init.ora initialization parameter file.

Java Pool

As its name, Java pool is used to services parsing of the Java commands. Its size can be set by JAVA_POOL_SIZE parameter in init.ora initialization parameter file.

B.Program Global Area

Although the result of SQL statemen parsing is stored in library cache, but the value of binding variable will be stored in PGA. Why? Because it must be private or not be shared among users. The PGA is also used for sort area.

C.Software Area Code

Software area code is a location in memory where the Oracle application software resides.

D.Oracle processes

There are two categories of processes that run with an Oracle database. They are mentioned

below:

User processes
System processes

The following figure illustrates the relationship between user processes, server processes, PGA, and session:

The first interaction with the Oracle-based application comes from the user computer that creates a user process. The user process then communicates with the server process on the host computer. Here, PGA is used to store session specific information.

E.Oracle Background Processe

Oracle background processes is the processes behind the scene that work together with the memories.

DBWn

Database writer (DBWn) process is used to write data from buffer cache into the datafiles. Historically, the database writer is named DBWR. But since some of Oracle version allows us to have more than one database writer, the name is changed to DBWn, where n value is a number 0 to 9.

LGWR

Log writer (LGWR) process is similar to DBWn. It writes the redo entries from redo log buffer

into the redo log files.

CKPT

Checkpoint (CKPT) is a process to give a signal to DBWn to writes data in the buffer cache into datafiles. It will also updates datafiles and control files header when log file switch occurs.

SMON

System Monitor (SMON) process is used to recover the system crash or instance failure by applying the entries in the redo log files to the datafiles.

PMON

Process Monitor (PMON) process is used to clean up work after failed processes by rolling back the transactions and releasing other resources.

ARCH

The ARCH background process is invoked when your database is running in ARCHIVELOG mode. If you are archiving your redo logs, the redo logs are touched by several background processes. First, the LGWR process copies the log_buffer contents to the online redo log files, and then the ARCH process copies the online redo log files to the archived redo log filesystem on UNIX. The ARCH process commonly offloads the most recent online redo log file whenever a log switch operation occurs in Oracle

The figure 4: shows various components of SGA, Oracle background processes, and their interactions

with control files, data files, Redo Log files, and archived redo logs.

2.Database

The database refers to disk resources, and is broken into two main structures –– Logical structures and Physical structures.

A.Logical Structures:~

Oracle database is divided into smaller logical units to manage, store, and retrieve data effeciently. The logical units are tablespace, segment, extent, and data block.

Figure 5 will illustrate the relationships between those units.

Tablespace

A Tablespace is a grouping logical database objects. A database must have one or more tablespaces.

In the Figure 5, we have three tablespaces –– SYSTEM tablespace, Tablespace 1,and Tablespace 2 Tablespace

is composed by one or more datafiles.

There are three types of tablespaces in Oracle:

Permanent tablespaces
Undo tablespaces
temporary tablespaces

Segment

A Tablespace is further broken into segments. A segment is used to stores same type of objects. That is, every table in the database will store into a specific segment (named Data Segment) and every index in the database will also store in its own segment (named Index Segment). The other segment types are TemporarySegment and Rollback Segment.A segment is a container for objects (such as tables, views, packages . . . indexes). A segment consists of Extends.

There are 11 types of Segments in oracle 10g.

Table
Table Partition
Index
Index Partition
Cluster
Rollback
Deferred Rollback
Temporary
Cache
Lobsegment
Lobindex

Extent

A segment is further broken into extents. An extent consists of one or more data block. When the database object is enlarged, an extent will be allocated. Unlike a tablespace or a segment, an extent cannot be named. Space for a data on a hard disk is allocated in extends.

Data Block

A data block is the smallest unit of storage in the Oracle database. The data block size is a specific number of bytes within tablespace and it has the same number of bytes.

B.Physical Structures:~

The physical structures are structures of an Oracle database (in this case the disk files) that are not directly manipulated by users. The physical structure consists of datafiles, redo log files, and control files.

Datafiles

A datafile is a file that correspondens with a tablespace. One datafile can be used by one tablespace,

but one tablespace can has more than one datafiles. An Oracle databae include of a number of physical files called datafile.

Redo Log Files

A Redo Log is a file that is part of an Oracle Database. When a transaction is committed the transaction’s details in the redo log buffer is written in a redo log file. These files contain information that helps in recovery in the event of system failure.

The figure 6: shows three Redo Log groups. Each group consists of two members. The first member

of each Redo Log group is stored in directory D1 and the second member is stored in directory D2.

Control Files

Control files are used to store information about physical structure of database. The control file is absolutely crucial to database operations.

It contains the following types of information:

Database Information

Archive log history

Tablespace and datafile records

Redo threads

Database’s creation data

Database name

Current Archive information

Log records

Database Id which is unique to each Database

Oracle Architecture And Working Process Of Query Flow

December 1, 2016

By postgresdba

Oracle Architecture consists of two things,

Oracle Instance – consists of Memory(SGA & PGA) and Background process
Oracle database files – consists of OS level database files like Datafile, logfile and controlfiles.

user process (User Process Interface) : client sending a request from one end. It starts as we use putty to connect to server machine.

server process (Oracle Process Interface) : connecting to db using command sqlplus / as sysdba. Two types of server process in oracle,

1. shared server(dispatcher): multiple user using single resources for sending request and receiving reply from the database as using single server process.(till 8i)

2. Dedicated server(listener) : each and every user uses dedicate server process as independent of other user for sharing.(from 9i) (from 10g default ).

Oracle Instance:

Oracle instance consists of memory and background process. Oracle memory consist of

Program Global Area (PGA)
System Global Area (SGA)
and oracle background process are huge in numbers. In 9i there are 60+ background process, 10g 150+ background process and in 11g 250+ background process. Some of the important background process on requirement when database starts up is,

System Monitor(SMON)
Process Monitor(PMON)
Database writer(DBWR)
Log writer(LGWR)
Checkpoint(Chkpt)
Archiver(ARCH)

password file(orapwd) : this will maintain the user password and login details and

parameter(init.ora) : this will state the parameter required for database creation.

Datafile is the collection of segments, used to store data in the format of binary as tables,index,etc. System is the default tablespace in database level.

Control file is used to store data about the database which will control entire database. We can have minimum of one controlfile and maximum of 8.

Logfile is used in recovery process as it takes out backup of each and every transaction logs. It consist of group (minimum -2 maximum – 32) and members (minimum – 1 maximum – 8).

Archive Logfile:

It is Backup or process of storing the transaction logs from redo online log file to Archive log destination for recovery purpose.

Query Flow :

When a Query is issued from user end, user process will be initiated and it will travel to server side. Then the server process will be initiated where, it will decides whether it needs to go for Shared server or Dedicated server. Also Program Global Area(PGA) is allocated for user query processing like sorting operation. Then the query moves to system global area(SGA) where complete query execution happens here.

Shared Pool, Here the query enters in to Library cache where inturns it has 2 component inside, SQL Area & PLSQL Area. If the given input is of Sql Query, it will go to Sql Area and if it’s a PLSQL Block it will go PLSQL Area. In Share pool, Query Validation and execution plan generation will takes place as below sequence of steps.

Syntax & Semantic check – Query keyword & identifiers will be checked.
Hash Value Generation – A unique value will be assigned for the query.
Parsing – Hard parsing (if query is new) Soft parsing (if its an already used query)
Execution – By Default will create 2000 plans and picks up a best plan among it for query.

Redo log Buffer cache(rlbc), is used to take backup of all transaction logs except select query for database recovery purpose. Transaction logs in rlbc will inturn moved to online logfiles via lgwr background process.

Database Buffer Cache, (dbbc) is where the query execution will takes place using the best execution plan it received from shared pool. It contains 3 parts namely,

Default – query default execution region.
Keep – can able to pin small tables and frequently used ones.
Recycle – Can able to pin huge tables and rarely used ones.

In Dbbc, we represent storage objects are buffer where the data will be written in buffer and there status are,

Free – empty buffer with no data written.
Pinned – currently writing buffer.
Dirty – filled or completely written buffer.

Up on execution of a user query, if the table block image already exists in the DBBC memory means, then it is called Cache hit, where it process the execution without disturbing the datafiles. Else if the block image not available in the DBBC buffer means, then it is Cache miss, where it will call the Oracle server process to fetch the image block of the respective table and put it in the DBBC and does the execution. Except select, all other sql commands which affect the database will be written it executed data permanently to the datafile using dbwr background process upon its dirty buffer status.

SGA Of Explanation in oracle

December 1, 2016

By postgresdba

SGA

System Global Area or Shared Global Area

A system global area is a group of shared memory areas that dedicated to an Oracle database instance.

It is a large part of memory that all the oracle background process access.

It is allocated from shared memory(SHMMAX) from Linux OS, which is specified in /etc/sysctl.conf.

SGA Consists of below components.

DBBC (Database Buffer Cache)
RLBC (Redo Log Buffer Cache)
Shared Pool
Large Pool
Java Pool
Streams Pool
Fixed SGA

Shared Pool :

Shared pool consists of two components.

Library Cache
Dictionary Cache or Row Cache
Server Result Cache
Reserverd Pool

Library cache and Dictionary Cache

When a SQL statement is executed it first enters library cache of Shared pool. It does syntax checking and semantec checking , then will create a shared SQL area.

In Shared SQL area it will have the parsed info and the execution plan for that SQL. So it can be reused.

syntax checking : Checks the SQL is syntactically correct.

Eg. select * from emp might have syntax error like selct * from emp;

semantec checking : Checks that the user have access to all the objects in the SQL. It uses Dictionary Cache/Row Cache to achive this. Dictionary will read the dictionary information from SYSTEM Tablespace.

So as already mentioned , any SQL statement that is executed will have a Shared SQL area. When another user executes a query , first it checks if there is any Shared SQL Area present , if it finds then it is called soft parsing , if not it will again create Shared SQL Area which is called hard parsing. We can also call as HIT and MISS.

Result Cache :

This memory component stores result of SQL result Cache and PLSQL function result Cache

Ex: Assume the query

SQL> select count(*) from emp;

count(*)

——————-

1000000000

takes 1 hr to execute , if result cache is enabled then the result 1000000000 will be stored in result cache. so next time if you execute the same query it will get from the result cache and will take some seconds.

If there is any dml occurs for emp table , then the result cache will invalidate and it will not get used. Again it will take 1 hr to execute the query for first time.

Database buffer cache

Holds a copy of data blocks read from data files

The LRU list holds free buffers, pinned buffers, and dirty buffers that have not yet been moved to the write list. Free buffers do not contain any useful data and are available for use.

Pinned buffers are currently being accessed.

It contains Multiple buffer pools are Default,keep,recycle,

Redo Log Buffer

The redo log buffer is a circular buffer in the SGA that holds information about changes made to the database. This information is stored in redo entries.

Includes information about transactions that have not yet been written to online redo log files.

Java pool

P arsing of Java code and scripts

Installation tasks related to Java applications with Oracle 11g

Java stored procedure code parsing

Streams pool

Oracle Streams can be used for:

Replication

Message Queuing

Loading data into a Data Warehouse

Event Notification

Data Protection

Large pool

Parallel Query performance management.

Recovery Manager (RMAN) backup and recovery operations.

Usage of a Large Pool Area allows the shared pool to primarily cache SQL and avoid the overhead caused by shrinking the SQL Cache Area

PARAMETERS

SGA_TARGET

SGA_MAX_SIZE

SHARED_POOL_SIZE

DB_CACHE_SIZE

DB_KEEP_CACHE_SIZE

DB_RECYCLE_CACHE_SIZE

DB_nk_CACHE_SIZE

LARGE_POOL_SIZE

JAVA_POOL_SIZE

LOG_BUFFERS

Views

V$SGA

V$SGAINFO

V$SGASTAT