Experience: is what you get soon after you need it.

Experience: is what you get soon after you need it.

Rasul Allah (sal Allahu alaihi wa sallam) said: "Restore the trusts of those who trust you, and deal not falsely with him who deals falsely with you." [Abu Dawud, Tirmidhi]

Search This Blog

Friday, September 4, 2015

Oracle Memory configuration

Memory Configuration:

Specifying the DDL Lock Timeout

A data definition language (DDL) statement is either nonblocking or blocking, and both types of DDL statements require exclusive locks on internal structures. If these locks are unavailable when a DDL statement runs, then nonblocking and blocking DDL statements behave differently:
  • Nonblocking DDL waits until every concurrent DML transaction that references the object affected by the DDL either commits or rolls back.
  • Blocking DDL fails, though it might have succeeded if it had been executed subseconds later when the locks become available.
To enable blocking DDL statements to wait for locks, specify a DDL lock timeout—the number of seconds a DDL command waits for its required locks before failing.

To specify a DDL lock timeout, use the DDL_LOCK_TIMEOUT parameter. The permissible range of values for DDL_LOCK_TIMEOUT is 0 to 1,000,000. The default is 0. You can set DDL_LOCK_TIMEOUT at the system level, or at the session level with an ALTER SESSION statement.


Specifying the Maximum Number of Processes


The PROCESSES initialization parameter determines the maximum number of operating system processes that can be connected to Oracle Database concurrently. The value of this parameter must be a minimum of one for each background process plus one for each user process.

Specifying a Fast Recovery Area

The Fast Recovery Area is a location in which Oracle Database can store and manage files related to backup and recovery. It is distinct from the database area, which is a location for the current database files (data files, control files, and online redo logs).
You specify the Fast Recovery Area with the following initialization parameters:
  • DB_RECOVERY_FILE_DEST: Location of the Fast Recovery Area. This can be a directory, file system, or Automatic Storage Management (Oracle ASM) disk group. It cannot be a raw file system.
  • In an Oracle Real Application Clusters (Oracle RAC) environment, this location must be on a cluster file system, Oracle ASM disk group, or a shared directory configured through NFS.
  • DB_RECOVERY_FILE_DEST_SIZE: Specifies the maximum total bytes to be used by the Fast Recovery Area. This initialization parameter must be specified before DB_RECOVERY_FILE_DEST is enabled.


Nonstandard Block Sizes

Tablespaces of nonstandard block sizes can be created using the CREATE TABLESPACE statement and specifying the BLOCKSIZE clause. These nonstandard block sizes can have any of the following power-of-two values: 2K, 4K, 8K, 16K or 32K. Platform-specific restrictions regarding the maximum block size apply, so some of these sizes may not be allowed on some platforms.
To use nonstandard block sizes, you must configure subcaches within the buffer cache area of the SGA memory for all of the nonstandard block sizes that you intend to use.

RENAME THE DATABASE:
You can rename the GLOBAL_NAME of your database using the ALTER DATABASE RENAME GLOBAL_NAME statement. However, you must also shut down and restart the database after first changing the DB_NAME and DB_DOMAIN initialization parameters and re-creating the control files. Re-creating the control files is easily accomplished with the command ALTER DATABASE BACKUP CONTROLFILE TO TRACE


SELECT PROPERTY_NAME,PROPERY_VALUE FROM DATABASE_PROPERTIES;


SHAIKDB>select * from v$resource_limit;

RESOURCE_NAME              CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_ALLOCATION               LIMIT_VALUE
------------------------------ ------------------- --------------- ---------------------------------------- ----------------------------------------
processes                   32       37      150                       150
sessions                   36       42      247                       247
enqueue_locks                   16       31     3030                     3030
enqueue_resources               14       37     1304                    UNLIMITED
ges_procs                   27       31      320                       320
ges_ress                    3        3       64                    UNLIMITED
ges_locks                    3        3      128                    UNLIMITED
ges_cache_ress                    0        0        0                    UNLIMITED
ges_reg_msgs                    0        0      100                    UNLIMITED
ges_big_msgs                    0        0      100                    UNLIMITED
ges_rsv_msgs                    0        0      100                       100
gcs_resources                    0        0        0                        0
gcs_shadows                    0        0        0                        0
dml_locks                    0        0     1084                    UNLIMITED
temporary_table_locks                0        3  UNLIMITED                    UNLIMITED
transactions                    0        0      271                    UNLIMITED
branches                    0        0      271                    UNLIMITED
cmtcallbk                    0        1      271                    UNLIMITED
max_rollback_segments               11       11      271                    65535
sort_segment_locks                0        2  UNLIMITED                    UNLIMITED
k2q_locks                    0        0      494                    UNLIMITED
max_shared_servers                1        1  UNLIMITED                    UNLIMITED
parallel_max_servers                0        0       20                     3600

23 rows selected.


Changing archive log mode:

SHAIKDB>archive log list;
Database log mode           No Archive Mode
Automatic archival           Disabled
Archive destination           USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     3
Current log sequence           5


SHAIKDB>shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SHAIKDB>startup mount
ORACLE instance started.

Total System Global Area 1135747072 bytes
Fixed Size           2212656 bytes
Variable Size         318770384 bytes
Database Buffers      805306368 bytes
Redo Buffers           9457664 bytes
Database mounted.


SHAIKDB>alter database archivelog;

Database altered.

SHAIKDB>archve log list;
SP2-0734: unknown command beginning "archve log..." - rest of line ignored.
SHAIKDB>archive log list;
Database log mode           Archive Mode
Automatic archival           Enabled
Archive destination           USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     3
Next log sequence to archive   5
Current log sequence           5
SHAIKDB>alter database open;

Database altered.

Memory Configuration:


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. Examples of data stored in the SGA include cached data blocks and shared SQL areas.

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.


Automatic Memory Management


The total memory that the instance uses remains relatively constant, based on the value of MEMORY_TARGET, and the instance automatically distributes memory between the system global area (SGA) and the instance program global area (instance PGA). As memory requirements change, the instance dynamically redistributes memory between the SGA and instance PGA.


MEMORY_TARGET initialization parameter is dynamic, you can change MEMORY_TARGET at any time without restarting the database.

MEMORY_MAX_TARGET, which is not dynamic, serves as an upper limit so that you cannot accidentally set MEMORY_TARGET too high, and so that enough memory is set aside for the database instance in case you do want to increase total instance memory in the future. Because certain SGA components either cannot easily shrink or must remain at a minimum size, the instance also prevents you from setting MEMORY_TARGET too low.


SHAIKDB>show parameter target

NAME                    TYPE     VALUE
------------------------------------ ----------- ------------------------------
memory_max_target            big integer 0
memory_target                big integer 0
pga_aggregate_target            big integer 360M
sga_target                big integer 1088M

SHAIKDB>


SHAIKDB>select value/1024/1024  from v$pgastat where name='maximum PGA allocated';

VALUE/1024/1024
---------------
    166.006836

SHAIKDB> select name,value from v$pgastat;

NAME                         VALUE
---------------------------------------- ----------
aggregate PGA target parameter         377487360
aggregate PGA auto target         213101568
global memory bound               75497472
total PGA inuse              140705792
total PGA allocated             162916352
maximum PGA allocated             174070784
total freeable PGA memory           8323072
process count                    36
max processes count                38
PGA memory freed back to OS           22347776
total PGA used for auto workareas         0
maximum PGA used for auto workareas         0
total PGA used for manual workareas         0
maximum PGA used for manual workareas         0
over allocation count                 0
bytes processed                47719424
extra bytes read/written             0
cache hit percentage               100
recompute count (total)            607

19 rows selected.


As a starting point:
To set the memory_target look at the value sga_target & either pga_aggregate_target or pga_max_allocated whichever is  higher.


SHAIKDB>alter system set memory_max_target=500m scope=spfile;

System altered.


SHAIKDB>alter system set memory_target=450m scope=spfile;

System altered.

SHAIKDB>shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SHAIKDB>startup

ORACLE instance started.

Total System Global Area  521936896 bytes
Fixed Size           2214936 bytes
Variable Size         473957352 bytes
Database Buffers       41943040 bytes
Redo Buffers           3821568 bytes
Database mounted.
Database opened.

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


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

MEMORY_SIZE MEMORY_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR    VERSION
----------- ------------------ ------------ ------------------- ----------
   226           .5        11             1      0
   339           .75        11             1      0
   452            1        11             1      0
   565         1.25        11             1      0
   678           1.5        11             1      0
   791         1.75        11             1      0
   904            2        11             1      0

7 rows selected.

V$SYSSTAT
V$SESSTAT
V$PGASTAT
V$SQL_WORKAREA
V$SQL_WORKAREA_ACTIVE


To Tune SGA:

check v$sgainfo or v$sga_targte_advice

SHAIKDB>select * from v$sga_target_advice order by sga_size;

 SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR ESTD_PHYSICAL_READS
---------- --------------- ------------ ------------------- -------------------
      148        1         11          1           9344
      185          1.25         11          1           8799
      222           1.5         11          1           8799
      259          1.75         11          1           8799
      296        2         11          1           8799



Use the fixed view V$BUFFER_POOL to track the sizes of the different cache components and any pending resize operations.


SHAIKDB>select * from V$BUFFER_POOL ;

   ID NAME        BLOCK_SIZE RESIZE_STA CURRENT_SIZE    BUFFERS TARGET_SIZE TARGET_BUFFERS  PREV_SIZE PREV_BUFFERS    LO_BNUM    HI_BNUM     LO_SETID   HI_SETID  SET_COUNT
---------- -------------------- ---------- ---------- ------------ ---------- ----------- -------------- ---------- ------------ ---------- ---------- ---------- ---------- ----------
    3 DEFAULT             8192 STATIC       36     4419           36        4419     40        4910      0         0       3       3          1



Memory Management Data Dictionary Views



V$SGA
Displays summary information about the system global area (SGA).
V$SGAINFO
Displays size information about the SGA, including the sizes of different SGA components, the granule size, and free memory.
V$SGASTAT
Displays detailed information about how memory is allocated within the shared pool, large pool, Java pool, and Streams pool.
V$PGASTAT
Displays PGA memory usage statistics as well as statistics about the automatic PGA memory manager when it is enabled (that is, when PGA_AGGREGATE_TARGET is set). Cumulative values in V$PGASTAT are accumulated since instance startup.
V$MEMORY_DYNAMIC_COMPONENTS
Displays information on the current size of all automatically tuned and static memory components, with the last operation (for example, grow or shrink) that occurred on each.
V$SGA_DYNAMIC_COMPONENTS
Displays the current sizes of all SGA components, and the last operation for each component.
V$SGA_DYNAMIC_FREE_MEMORY
Displays information about the amount of SGA memory available for future dynamic SGA resize operations.
V$MEMORY_CURRENT_RESIZE_OPS
Displays information about resize operations that are currently in progress. A resize operation is an enlargement or reduction of the SGA, the instance PGA, or a dynamic SGA component.
V$SGA_CURRENT_RESIZE_OPS
Displays information about dynamic SGA component resize operations that are currently in progress.
V$MEMORY_RESIZE_OPS
Displays information about the last 800 completed memory component resize operations, including automatic grow and shrink operations for SGA_TARGET and PGA_AGGREGATE_TARGET.
V$SGA_RESIZE_OPS
Displays information about the last 800 completed SGA component resize operations.
V$MEMORY_TARGET_ADVICE
Displays information that helps you tune MEMORY_TARGET if you enabled automatic memory management.
V$SGA_TARGET_ADVICE
Displays information that helps you tune SGA_TARGET.
V$PGA_TARGET_ADVICE
Displays information that helps you tune PGA_AGGREGATE_TARGET.

No comments: