Ora-12542: Tns:Address Already In Use

December 3, 2016

By postgresdba

solution

D:\oracle\product\10.2.0\db_1\network\admin\listener.ora

INBOUND_CONNECT_TIMEOUT_LISTENER = 0

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME = PLSExtProc)

(ORACLE_HOME = D:\oracle\product\10.2.0\db_1)

(PROGRAM = extproc)

)

LOG_DIRECTORY_LISTENER = G:\Listener_Trace

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = newpass)(PORT = 1521))

(ADDRESS = (PROTOCOL = TCP)(HOST = newpass)(PORT = 1523))

)

And on client side, map net service names to recently added listener protocol address. i.e. 1523, as below:

NEWPASS_29 =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.6)(PORT = 1521))

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.6)(PORT = 1523))

)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = NEWPASS)

)

after made above changes to listener.ora and tnsnames.ora, TNSPING was successful and able to create DB-link.

You may face mentioned error in following cases:

New installation
listener.ora file has been edited manually since the last listener restart
TCP port number is duplicated across the list of ADDRESS configurations in the listener.ora file.

Fragmentation Defragementation In Table Level

December 2, 2016

By postgresdba

The rows are split into more than one block is called fragmentation. it decrease the block accesses and it slow the database performance. fragmentation are two type one is table fragmentation and second is index fragmentation.

TABLE FRAGMENTATION:

Each table have the blocks and it stored in the data files. The data will store in the block level. When the data split into more than one blocks is called table fragmentation. The table get more update and delete statement means table will get fragmentation.

When table getting full table scan in the that time oracle will read the data upto HWM (HIGH WATER MARK LEVEL). HWM is using to find used block and free block in table.

ROW CHAINING:

When inserting the row, in that time the block does not have any space means oracle allocate the next block for that inserting its called the row chaining.

ROW MIGRATION:

When updating the row in block the update not fit into the existing block it go to another block is called row migration. it based on pctfree. Because pctfree it low means the block get row migration.

FINDING THE FRAGMENTATION IN TABLE:

Create one table and add 2 column in that table.
Insert max 1000000 records in that table and commit it.
Now do some delete operation in that table and analyze the table using estimate or compute statistics or dbms_space.space_usage package.
Now check the info in dba_tables using query

(SELECT chain_cnt FROM user_tables WHERE table_name = ‘ROW_MIG_CHAIN_DEMO’;)

Its show the num_rows and chain_cnt, if u get any values in chain_cnt means fragmentation is accured.
Using dbms_space.space_usage

Steps to Check and Remove Table Fragmentation:-

1. Gather table stats:

To check exact difference in table actual size (dba_segments) and stats size (dba_tables). The difference between these value will report actual fragmentation to DBA. So, We have to have updated stats on the table stored in dba_tables. Check LAST_ANALYZED value for table in dba_tables. If this value is recent you can skip this step. Other wise i would suggest to gather table stats to get updated stats.

exec dbms_stats.gather_table_stats(‘&schema_name’,’&table_name’);

2. Check Table size:

select table_name,bytes/(1024*1024*1024) from dba_table where table_name=’&table_name’;

3. Check for Fragmentation in table:

SELECT chain_cnt FROM user_tables WHERE table_name = ‘ROW_MIG_CHAIN_DEMO’;

(or)

set pages 50000 lines 32767

select owner,table_name,round((blocks*8),2)||’kb’ “Fragmented size”, round((num_rows*avg_row_len/1024),2)||’kb’ “Actual size”, round((blocks*8),2)-round((num_rows*avg_row_len/1024),2)||’kb’,

((round((blocks*8),2)-round((num_rows*avg_row_len/1024),2))/round((blocks*8),2))*100 -10 “reclaimable space % ” from dba_tables where table_name =’&table_Name’ AND OWNER LIKE ‘&schema_name’

/Note: This query fetch data from dba_tables, so the accuracy of result depends on dba_table stats.

If you find reclaimable space % value more than 20% then we can expect fragmentation in the table. Suppose, DBA find 50% reclaimable space by above query, So he can proceed for removing fragmentation.

4. DEFRAGMENTATIONS (or) remove fragemenation

1. move table to another tablespace

2. export and import the table(exp/imp)

3. shrink command (fron oracle 10g)

(shrink command is only applicable for tables which are tablespace with auto segment space management)

4. CTAS method

5.online redefinition

option: 1 move table to another tablespace

alter table

enable row movement;

alter table

move tablespace ;

Now, get back table to old tablespaces using below command

alter table table_name move tablespace old_tablespace_name;

REBUILD ALL INDEXES:

We need to rebuild all the indexes on the table because of move command all the index goes into unusable state.

Here, value in status field may be valid or unusable.

SQL> select status,index_name from dba_indexes where table_name = ‘&table_name’;

STATUS INDEX_NAME

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

UNUSABLE INDEX_NAME

Here UNUSABLE status going to valid

SQL> alter index rebuild online;

Index altered.

Here, value in status field must be valid.

SQL> select status,index_name from dba_indexes where table_name = ‘&table_name’;

STATUS INDEX_NAME

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

VALID INDEX_NAME

Gather table stats:

SQL> exec dbms_stats.gather_table_stats(‘&owner_name’,’&table_name’);

PL/SQL procedure successfully completed.

Check Table size:

table size will find reduced size of the table.

select table_name,bytes/(1024*1024*1024) from dba_table where table_name=’&table_name’;

Check for Fragmentation in table:

set pages 50000 lines 32767

Option: 2Export and Import the table(exp/imp)

SQL> select table_name, round((blocks*8),2)||’kb’ “size”

2 from user_tables

3 where table_name = ‘BENZ’;

TABLE_NAME size

—————————— ——————————————

BENZ 85536kb

SQL> select table_name, round((num_rows*avg_row_len/1024),2)||’kb’ “size”

2 from user_tables

3 where table_name = ‘BENZ’;

TABLE_NAME size

—————————— ——————————————

BENZ 42535.54kb

SQL> select status from user_indexes where table_name = ‘BENZ’;

STATUS

——–

VALID

SQL> exit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 – Pr

oduction

With the Partitioning, OLAP and Data Mining options

C:\>exp scott/tiger@Orcl file=c:\benz.dmp tables=benz

Export: Release 10.1.0.5.0 – Production on Sat Jul 28 16:30:44 2007

Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 – Produc

tion

With the Partitioning, OLAP and Data Mining options

Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path …

. . exporting table BENZ 468904 rows exported

Export terminated successfully without warnings.

C:\>sqlplus scott/tiger@orcl

SQL*Plus: Release 10.1.0.5.0 – Production on Sat Jul 28 16:31:12 2007

Connected to:

Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 – Production

With the Partitioning, OLAP and Data Mining options

SQL> truncate table benz;

Table truncated.

SQL> exit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 – Pr

oduction

With the Partitioning, OLAP and Data Mining options

C:\>imp scott/tiger@Orcl file=c:\benz.dmp ignore=y

Import: Release 10.1.0.5.0 – Production on Sat Jul 28 16:31:54 2007

Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 – Produc

tion

With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.01.00 via conventional path

import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

. importing SCOTT’s objects into SCOTT

. . importing table “BENZ” 468904 rows imported

Import terminated successfully without warnings.

C:\>sqlplus scott/tiger@orcl

SQL*Plus: Release 10.1.0.5.0 – Production on Sat Jul 28 16:32:21 2007

Connected to:

Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 – Production

With the Partitioning, OLAP and Data Mining options

SQL> select table_name, round((blocks*8),2)||’kb’ “size”

2 from user_tables

3 where table_name = ‘BENZ’;

TABLE_NAME size

—————————— ——————————————

BENZ 85536kb

SQL> select table_name, round((num_rows*avg_row_len/1024),2)||’kb’ “size”

2 from user_tables

3 where table_name = ‘BENZ’;

TABLE_NAME size

—————————— ——————————————

BENZ 42535.54kb

SQL> exec dbms_stats.gather_table_stats(‘SCOTT’,’BENZ’);

PL/SQL procedure successfully completed.

SQL> select table_name, round((blocks*8),2)||’kb’ “size”

2 from user_tables

3 where table_name = ‘BENZ’;

TABLE_NAME size

—————————— ——————————————

BENZ 51840kb

SQL> select table_name, round((num_rows*avg_row_len/1024),2)||’kb’ “size”

2 from user_tables

3 where table_name = ‘BENZ’;

TABLE_NAME size

—————————— ——————————————

BENZ 42542.27kb

SQL> select status from user_indexes where table_name = ‘BENZ’;

STATUS

——–

VALID

SQL> exec dbms_redefinition.can_redef_table(‘SCOTT’,’BENZ’,-

> dbms_redefinition.cons_use_pk);

PL/SQL procedure successfully completed.

Option: 3 Shrink command (fron Oracle 10g):-

Shrink command:

Its a new 10g feature to shrink (reorg) the tables (almost) online which can be used with automatic segment space management.

This command is only applicable for tables which are tablespace with auto segment space management.

step to shrink

SQL> alter table

enable row movement;

Table altered.

There are 2 ways of using this command.

1. Rearrange rows and reset the HWM:

Part 1: Rearrange (All DML’s can happen during this time)

SQL> alter table

shrink space compact;

Table altered.

Part 2: Reset HWM (No DML can happen. but this is fairly quick, infact goes unnoticed.)

SQL> alter table

shrink space;

Table altered.

2. Directly reset the HWM:

SQL> alter table

shrink space; (Both rearrange and restting HWM happens in one statement)

Table altered.

Advantages are:

1. Unlike “alter table move ..”,indexes are not in UNUSABLE state.After shrink command,indexes are updated also.

2. Its an online operation, So you dont need downtime to do this reorg.

3. It doesnot require any extra space for the process to complete.

Option: 4 CTAS method

Examples:-

SQL> create table benz as select * from nijam;

Table created.

SQL> drop table nijam purge;

Table dropped.

SQL> rename benz to nijam;

Table renamed.

SQL> exec dbms_stats.gather_table_stats(‘SCOTT’,’NIJAM’);

PL/SQL procedure successfully completed.

SQL> select table_name,round((blocks*8),2)||’kb’ “size”

2 from user_tables

3 where table_name = ‘NIJAM’;

TABLE_NAME size

—————————— ——————————————

NIJAM 85536kb

SQL> select table_name,round((num_rows*avg_row_len/1024),2)||’kb’ “size”

2 from user_tables

3 where table_name = ‘NIJAM’;

TABLE_NAME size

—————————— ——————————————

NIJAM 68986.97kb

SQL> select status from user_indexes

2 where table_name = ‘NIJAM’;

no rows selected

Note:- we need to create all indexes.

Option:5 online redefinition examples

SQL> create table NIJAM (

2 no number,

3 name varchar2(20) default ‘NONE’,

4 ddate date default SYSDATE);

Table created.

SQL> alter table nijam add constraint pk_no primary key(no);

Table altered.

SQL> begin

2 for x in 1..100000 loop

3 insert into nijam ( no , name, ddate)

4 values ( x , default, default);

5 end loop;

6 end;

7 /

PL/SQL procedure successfully completed.

SQL> create or replace trigger tri_nijam

2 after insert on nijam

3 begin

4 null;

5 end;

6 /

Trigger created.

SQL> select count(*) from nijam;

COUNT(*)

———-

100000

SQL> delete nijam where rownum <= 50000;

50000 rows deleted.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats(‘SCOTT’,’NIJAM’);

PL/SQL procedure successfully completed.

SQL> select table_name, round((blocks*8),2)||’kb’ “size”

2 from user_tables

3 where table_name = ‘NIJAM’;

TABLE_NAME size

—————————— ——————————————

NIJAM 2960kb

SQL> select table_name, round((num_rows*avg_row_len/1024),2)||’kb’ “size”

2 from user_tables

3 where table_name = ‘NIJAM’;

TABLE_NAME size

—————————— ——————————————

NIJAM 822.69kb

Minimum Privs required “DBA” role or “SELECT” on dbms_redefinition pkg

First check table is condidate for redefinition.

SQL> exec sys.dbms_redefinition.can_redef_table(‘SCOTT’,-

> ‘NIJAM’,-

> sys.dbms_redefinition.cons_use_pk);

PL/SQL procedure successfully completed.

After verifying that the table can be redefined online, you manually crea

te an empty interim table (in the same schema as the table to be redefined)

SQL> create table BENZ as select * from nijam WHERE 1 = 2;

Table created.

SQL> exec sys.dbms_redefinition.start_redef_table ( ‘SCOTT’,-

> ‘NIJAM’,-

> ‘BENZ’);

PL/SQL procedure successfully completed.

This procedure keeps the interim table synchronized with the original tab

SQL> exec sys.dbms_redefinition.sync_interim_table (‘SCOTT’,-

> ‘NIJAM’,-

> ‘BENZ’);

PL/SQL procedure successfully completed.

SQL> –Create PRIMARY KEY on interim table(BENZ)

SQL> alter table BENZ

2 add constraint pk_no1 primary key (no);

Table altered.

SQL> create trigger tri_benz

2 after insert on benz

3 begin

4 null;

5 end;

6 /

Trigger created.

Disable foreign key on original table if exists before finish this proces

SQL> exec sys.dbms_redefinition.finish_redef_table ( ‘SCOTT’,-

> ‘NIJAM’,-

> ‘BENZ’);

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats(‘SCOTT’,’NIJAM’);

PL/SQL procedure successfully completed.

SQL> select table_name, round((blocks*8),2)||’kb’ “size”

2 from user_tables

3 where table_name = ‘NIJAM’;

TABLE_NAME size

—————————— ——————————————

NIJAM 1376kb

SQL> select table_name, round((num_rows*avg_row_len/1024),2)||’kb’ “size”

2 from user_tables

3 where table_name = ‘NIJAM’;

TABLE_NAME size

—————————— ——————————————

NIJAM 841.4kb

SQL> select status,constraint_name

2 from user_constraints

3 where table_name = ‘NIJAM’;

STATUS CONSTRAINT_NAME

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

ENABLED PK_NO1

SQL> select status ,trigger_name

2 from user_triggers

3 where table_name = ‘NIJAM’;

STATUS TRIGGER_NAME

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

ENABLED TRI_BENZ

SQL> drop table BENZ PURGE;

Table dropped.

Tablespace Management In Oracle Version

December 1, 2016

By postgresdba

There are two type of tablespace management LMT and DMT.

LMT:(local managed tablespace)
It came from 9i. the block information(free or used) will maintain in datafile header as 0’s & 1’s.

DMT:(dictionary managed tablespace)
The extent information will maintain in data dictionary as FET$ and UET$.

DIFFERENCE BETWEEN LMT AND DMT:

No	DMT	LMT
1 2 3 4	free list maintain in data dictionary as FET$,UET$ high I/O process will happen manual coalesce more undo generated	free list maintain in data file header as 0’s & 1’s less I/O process will happen auto coalesce Less undo generated

EXTENT MANAGEMENT:
collection of blocks is called as extent.
using local managed tablespace the extent size can be managed like UNIFORM or

AUTOALLOCATE.
UNIFORM:
The extent size will allocated as same size for every new extent.

AUTOALLOCATED:
It take the default values base on storage parameters.

(STORAGE PARAMETERS IN EXTENT MANAGEMENT):

Intial   – first extent values
Next – next values in segments
Min Extents – minimum amount of extent get created at a time of table/segment.
Max Extents   – unlimited
Pct Increase – 50% of increase for next extent value.
Logging -it allow all sql statement to write in online redo logs except (select).
No-logging   – it opposite to logging and when giving the rebuild index,cts,insert into table from select statement this ill not write in online redo logs.
Blocksize – this parameter is using to specifies the block size .
Online –   it show the tablespcae in online .
Offline – after creation tablespace it show in offline.
Permanent   – it hold the permanent database objects.
Temporary   – it hold the temporary databasse objects.
(next extent/growth=prev.extent + (pct increase x prev.extent)—- this is formula for 3rd extent

In DMT Tablespace:

create tablespace sainora datafile ‘/u01/app/oracle/oradata/data/sainora.dbf’ size 200m extent management dictionary default storage (intial 1m next 2m minextents 1 maxextents 10 pct increase 50%);

In LMT Tablespace:
create tablespace sainora datafile ‘/u01/app/oracle/oradata/data/sainora.dbf’ size 200m extent management local uniform size 1m;

create tablespace sainora datafile ‘/u01/app/oracle/oradata/data/sainora.dbf’ size 200m extent management local autoallocate;

SEGMENT MANAGEMENT:
Collection of extents is called as segment.
It using to managed the free and used space with in a segments. it can mention as

MANUAL or AUTO.

MANUAL:
mention the data block size for each segments using the space parameter.

AUTO:
It take automatic values based on data blocks.
It managed the free space automatically.

(SPACE PARAMETER IN SEGMENT MANAGEMENT):
Pct free – 10% for updates.
Pct used – to find the block where free or used.
Init trans – intial transaction for each block
Max trans – max transaction for each block

In DMT Tablespace (manual):
create table san_tab(id number(9),name varchar2(9) pctfree 20 pctused 50 inittrans 3 maxtrans 10;

In LMT Tablespace(auto/manual):
create table san_tab(id number(9),name varchar2(9));

VIEWS:

DBA_EXTENTS
USER_EXTENTS
DBA_DATA_FILES
V$DATAFILE
V$TABLESPACE
DBA_FREE_SPACE
DBA_SEGMENTS
USER_SEGMENTS
DBA_TABLESPACES
USER_TABLESPACES

ASSIGNMENT QUESTIONS:
1.increase the max extent.
2.find the 3rd extent size.
3.create LMT tablespace using storage parameter(nologging).
4.how will you change the extent size,it is possible or not.

INTERVIEW QUESTIONS:
1.find the num of extent in a table.
2.convert the dmt tablespace into lmt tablespace.
3.fine the tablespace where lmt or dmt.
4.diff between lmt and dmt.
5.how will u change the extent size.
6.when show error like (unable to create extent)what u do?.
7.find block size and extent size in particular table or segments.

Tablespaces Monitoring,Auditing,Add,Delete,Rename,Drop,Views Relocate -Oracle

December 1, 2016

By postgresdba

A database is divided into One or more logical storage units called tablespace.
collection of datafiles is called as tablespace.

TYPES OF TABLESPACE:

Permanent Tablespaces
Undo Tablespace
Temporary Tablespaces

1.PERMANENT TABLESPACE:

(Users data files and normally contains the system (data dictionary) and users data)

System

Is a permanent tablespace and contains the vital data dictionary (metadata about the database).
It created when database is created.
It always in online.
It stores tables that support the core functionality of the database such as the data dictionary tables.
Cannot rename or drop the system tablespace

Sysaux

Its introduce in oracle 10g.
Is an auxiliary tablespace to the system tablespace and contains performance statistics collected by the database.(ex:logminar,oracle streams)
It cannot be dropped or renamed.
Sysaux tablespace may be taken offline for performing tablespace recovery.

USER DEFINED TABLESPACES:

Is used to store organizational data in table accessed by application programs.
Flexibility in database administration.
Separate data by backup reruitrmrnts.
Separate dynamic and static data to enable database tuning.
Control space allocation for both applications and system users.
Reduce contention for input/output path access.

Create tablespace:

Syntax:

Create tablespace data_tbs datafile ‘/home/oracle/data/data_tbs.dbf’ size 200m;

Adding datafile :

Syntax:

Alter tablespace data_tbs add datafile ‘/home/oracle/data/data02_tbs.dbf’ size 200m;

Renaming tablespace:

Syntax:

Alter tablespace data_tbs rename to data_old_tbs;

Dropping tablespace:

Syntax:

Drop tablespace data_tbs;

Resize the datafile :

Syntax:

Alter database datafile ‘/home/oracle/data/data_tbs.dbf’ resize size 300m;

Relocating tablespace/datafile:

Syntax:

Step 1 shut the database

Step 2 use copy command to move the phy file to new location

Step 3 mount the database

Step 4 excute this command

Alter tablespcae data_tbs rename datafile ‘/home/oracle/data/data_tbs.dbf’ to ‘/home/oracle/king/data_tbs.dbf’;

Quotas on tablespces:

Syntax:

Alter user schema01 quota 50m on data_tbs;

Read only tablespace:

Syntax:

Alter tablespace data_tbs read only;

Offline/online tablespace:

Syntax:

Alter tablespace data_tbs offline;

Alter tablespace data_tbs online;

Assing tablespace to user:

Syntax:

Alter user schema01 default tablespace data_tbs;

Bigfile tablespace:

It is new feature of oracle 10g and it contain the single datafile and allocate large size up to 4g.

It reduce the disk I/O.

Syntax:

Create bigfile tablespace userdata datafile ‘/home/oracle/data/userdata.dbf’ size 10g;

Resize the bigfile tablespace:

Syntax:

Alter tablespace userdata resize 20g;

Tablespace creation in ASM:

syntax:

create tablespace sainora datafile ‘+dg1’ size 200m;

Adding datafile in ASM:

syntax:

alter tablespace sainora add datafile ‘+dg1’ size 200m;

Droping tablespace in ASM:

syntax:

drop tablespace sainora;

Drop datafile in ASM:

syntax:

alter tablespace sainora drop datafile ‘+dg1/sainora.dbf ‘;

VIEWS:

DBA_DATA_FILES

V$DATAFILE

DBA_SEGMENTS

DBA_USERS

DBA_FREE_SPACE

DBA_TABLESPACES

V$TABLESPACE

ASSIGNMENT QUESTIONS:

1.create tablespace size with 2m and insert the values above 10,00,000.

2.add the datafile in tablespace.

3.create tablespace without giving the datafile location.

4.rename the tablespace name give syntax.

5.find the default location of tablespace.

INTERVIEW QUESTIONS:

1.create the tablespace size with 1gb.

2.How to assign quota to more then one user for same tablespace.

3.How will u drop the system tablespace.

4.resize the sysaux tablespace give syntax.

5.find the tablespace size and status.

6.find the datafile location and size.

7.find how many datafile is there in a single tablespace.

8.how to check the used space of tablespace.

9.find the free space of tablespace.

10.use of bigfile tablespace.

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.

Modes Of Locking in table –oracle

December 1, 2016

By postgresdba

The LOCK TABLE statement is used to lock tables, table partitions, or table subpartitions.

Database locks are used to provide concurrency control in order to ensure data consistency and integrity.

A locked table remains locked until you either commit your transaction or roll it back and savepoint.

Ensure that only one user can modify a record at a time.

Ensure that a table cannot be dropped while another user is querying it.

Ensure that one user cannot delete a record while another is updating it.

TYPES

DML locks (data locks)
DDL locks (dictionary locks)
Internal Locks/Latches
Distributed Locks
Parallel Cache Management Locks
Deadlocks

DML locks

Row Level Locks [TX]

All DML locks Oracle acquires automatically are row-level locks.

Row locking provides the lowest level of locking possible provides the best possible transaction concurrency.

Row-level locks serve a primary function to prevent multiple transactions from modifying the same row.

Table Level Lock [TM]

A transaction acquires a table lock for DML statements such as INSERT/UPDATE/DELETE, SELECT with the FOR UPDATE, and LOCKTABLE

A table lock can be held in several modes:

Row share (RS)

Row exclusive (RX)

Share (S)

Share row exclusive (SRX)

Exclusive (X)

DDL locks

A DDL lock protects the definition of a schema object while the object is referenced in a DDL operation.

· Exclusive DDL Locks

· Shared DDL Locks

· Breakable Parse Locks

Oracle automatically acquires a DDL lock to prevent other DDL operations from referencing or altering the same object.

Internal locks

Data Dictionary Locks

Held on entries in dictionary caches while the entries are being modified or used. They guarantee that statements being parsed do not see inconsistent object definitions.

File and Log Management Locks

Protect various files like control files, redo log files so that only one process at a time can change it. Datafiles are locked to ensure that multiple instances mount a database in shared mode or that one instance mounts it in exclusive mode.

Tablespace and Rollback Segment Locks

Protect tablespaces and rollback segments. Example, all instances accessing a database must agree on if s tablespace is online or offline. Rollback segments are locked so that only one instance can write to a segment.

Latches

Latches are low-level serialization mechanisms to protect shared data structures in the system global area (SGA). Latches protect the oracle lists like list of users currently accessing the database and protect the data structures describing the blocks in the buffer cache.

Distributed Locks

A distributed lock manager (DLM) is a software component provided by your platform vendor.

Distributed locks are held by a database instance, not by individual transactions.

The Oracle Parallel Server uses a distributed lock manager to coordinate concurrent access to resources, such as data blocks and rollback segments, across multiple instances.

Parallel Cache Management Locks

The instance locks which manage the locking of blocks in datafiles.

PCM locks ensure cache coherency by forcing instances to acquire a lock before modifying or reading any database block.

Deadlocks

A deadlock is the situation where you have two, or more, Oracle “sessions” (well, transactional “states”) competing for mutually locked resources. Oracle deals with deadlocks pretty much immediately by raising an exception (ORA-00060) in one of the sessions.

Manual Table Lock Modes

LOCK TABLE table_name IN lock_mode MODE [ WAIT [, integer] | NOWAIT ];

For example:

LOCK TABLE EMP IN SHARE MODE NOWAIT;

Lock_mode

ROW SHARE(RS) – Allows concurrent access to the table, but users are prevented from locking the entire table for exclusive access.

ROW EXCLUSIVE(RX) – Allows concurrent access to the table, but users are prevented from locking the entire table with exclusive access and locking the table in share mode.

SHARE(S) – Allows concurrent queries but users are prevented from updating the locked table.

SHARE ROW EXCLUSIVE(SRX) – Users can view records in table, but are prevented from

updating the table or from locking the table in SHARE mode.

EXCLUSIVE(E) – Allows queries on the locked table, but no other activities.

Views to identify Locking Issues

V$LOCK

V$LOCKED_OBJECT

DBA_BLOCKERS

DBA_WAITERS

DBA_LOCK

DBA_DDL_LOCKS

DBA_DML_LOCKS

DBA_LOCK_INTERNAL

DBA_KGLLOCK

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