The second part of the memory structure of the Oracle architecture which is known as PGA (Program Global Area)
PGA: This area is always controlled by main process for which it's spun. To elaborate further, it's the memory allocated to every server process or the background process running in the memory. The configuration of the PGA changes with the dedicated server and shared server architecture. In shared server architecture the sort area is in SGA while in dedicated server the area is in PGA. Furthermore, PGA helps to localize the memory location to the dedicated User; the SQL statements executed by the user use this memory space. PGA has one to one relations with processes.
The PGA memory structure can be classified as below:
1) SQL Area:
The area is allocated to SQL variables bind information and runtime memory structure. Every user creates its own session on Oracle server which has its own SQL area.
In-Execution Area:
This area is used to keep user session information whenever the user does DML operations like SELECT, INSERT, DELETE or UPDATE. Garbage collection of In- Execution area is done as soon the output of the above queries is received. Also, the SQL statements using JOINS or GROUP BY or ORDER BY clauses use this area for sorting purpose.
OPEN_CURSOR parameter initializes the number of cursors in the session to handle SQL areas in memory. By now we know that all sorting operations are performed in the PGA cache memory, and this mode of operations is called as optimal operation mode, as it prevents disk I/O too. Further, if the memory area is not appropriately configured the SQL end up using disk in limited fashion, such operations are single pass operations. Thus, for an efficient execution of the query the memory size should be appropriated to the size of SQL queries like in OLTP the SQL statements are not data intensive, while in OLAP the queries are executed on large data to generate reports including sorting on huge dataset.
Managing PGA size:
The PGA size can be automatically managed by setting WORKAREA_SIZE_POLICY initialization parameter to auto. We are also required to manually configure SORT_AREA_SIZE, HASH_AREA_SIZE and BITMAP_AREA_SIZE parameters to get adequate sort performance. Further, if we want to automate the memory allocation to PGA process that is, let oracle decide what is best for the users then we need only to initialize PGA_AGGREGATE_TARGET parameter.
Now let us discuss the second major part of oracle architecture i.e "LOGICAL STRUCTURE".
The Logical structure of the Oracle database is hierarchal in nature.
1) Data Block: This is the most basic part of the logical composition of the database. It contains specified number of bytes on the disk. DB_BLOCK_SIZE parameter initializes the size of the data block, which can range from 2KB to 32 KB. For a good performance we should size data block as multiple of OS level data block size. The main role of oracle data block comes in block recovery via RMAN, this happens in the scenario when there is block corruption due to some hardware, i/o, oracle or OS level reasons, then we create block dump to see the data present in the data block which done by the process of binary dumping. ALTER SYSTEM commands can be used to create data block dump files
2) Extents: It is a combination of two or more data blocks. Please remember these data blocks are in consecutive manner. This is also a unit of space allocation. We can understand it better with example: If we create a table we can specify the initial storage space to be allocated in extents and also gives its next extent range to a max range.
CREATE TABLE emp
(emp_no NUMBER(2),
emp_name VARCHAR2(14),
location VARCHAR2(13) )
STORAGE ( INITIAL 100K NEXT 50K
MAXEXTENTS 50 );
3) Segments: It is a collection of extents which is allocated for use in structures like index or table or so. Thus, the space allocated to above created EMP table will be called as the EMP table segments. Similarly, the other data objects like sort segments, undo segments etc.; will have the segments name same as the object name.
4) Table space: This is formed by allocation of similar segments. It contains one or more data files and all objects are inside the table space. These data files are oracle formatted OS files. It is advised that related tables be kept in the same table space. Space management can be done by adding new data files if the space is full, else it is managed automatically by the oracle. Below are the default table space create while installing the Oracle DB
a) SYSAUX table space
b) System table space
c) Temporary table space
d) Undo table space
e) Default table space
Advantage of using table space:
I. It helps in easy recovery and backups as it acts as a unit.
II. It`s easy to allocate space.
III. We can offline a particular table space without bringing down the entire table space.
IV. Table space helps increase performance as we can use data files which resets on different disks, further controlling the I/O on the disks.
V. Also we can use import/export utility at table space level.
And finally the third part which is "PHYSICAL DATABASE STRUCTURE":
In this I am going to share about how the oracle database files are kept in the OS level. The physical data structure mainly consists of three types of files:
1) Data files:
The data files as discussed above are used to store logical entity like Table space. The data files can be part of one or more data base or entirely belong to one database. These data files store all the tables, indexes on the physical files. If there is a read or write then there is I/O operation for the disks where the data files are saved. Oracle managed Files (OMF) which was introduced in Oracle8i has automated the management of the Operating System file. We need not mention data file names while performing operations on data objects. In oracle we can map a table space to a data files but we cannot map table or data inside a table space to a data files directly.
2) Control files:
This is the most vital file of Oracle Database, without this file database cannot function or even be brought up. It contains the recent state information of the database, and because of this it is maintained in multiplexed form, generally three copies. The control file consists of the information about the names and locations of the data files, backup set details, redo log files, current log sequence numbers and the all- significant system change number (SCN), which indicates the most recent version of committed changes in the database information that is restricted form the users even for reading purposes. The changes in the control file are made by oracle processes only, which again is replicated in all the copies of the Control file. When we bring up the Oracle database in mount state it reads the control file to make itself aware of the database status and where about of its configuration and data files. V$CONTROLFILE is the dynamic view which provides us information about the control files. The control files end with .ctl extension.
3) Redo log Files:
The redo log files actually records everything (DML operations) happening on the database. These are used for recovery and backups also. Whenever there are changes made to the data and committed its first recorded in the redo log files even before its being updated on the data files, thus we can recover the latest changes made from the redo log files as part of recovery process. The set of redo log files that are currently being used are called online redo log file and these files are flushed to be archived and kept for recovery, these are offline files called archived redo log files. Redo log buffer area keeps the online redo log files. Further it is recommended to multiplex the redo log files because of its importance in database recovery.
4) Other important files which includes below files:
I. SPFILE: It is called server parameter files. It is the first file that is read while bringing up the database. This file contains information about the initialization parameters like memory limit, setting to determine the database configuration, archived log location and most important the CONTROL FILE location. This file is stored on the Oracle server, though the directories may differ in different Operating System. We can use V$SPPARAMETER dynamic view to get the information of the SP file.
II. Password File: This file contains the information about the SYSDBA and SYSOPER administrative privilege user, as it helps them to perform startup, shutdown, backup and recovery of database.
III. Alert log file: This is a mandatory file of oracle database its nomenclature is alterdb_name.log where db_name is the name of the database. It captures all the major database incidents like errors related to oracle, warnings or other messages, sequence of startup, parameters of initialization, track table space regarding the addition or removal of data files. The alert log location cab be viewed by BACKGROUND_DUMP_DEST parameter. Alert log is the first step towards diagnosing an error.
IV. Trace Files: If any background process like DB Writer, Log Writer, others fail or any user process fails, these trace files help to diagnose the error. DIAGNOSE_DEST is the initialization parameter to know the destination of the trace files. These files have extension of .trc.
Backup Files: Backup files help us to recover and restore database to its present form. These backup files could be self-initiated or created via using RMAN back-up tool.
