An Analogy on the Architectures of Oracle and Animated Living Being

– By Srinivas Maddali 

Prologue:

 To take Oracle more closely than it is to its DBA to Developer, it is necessary that we speak in the terms of audience than in terms of Oracle.

I am a fascinated by the complexities of Human Mind which, I understand, consists of consciousness, Knowledge Base Collected by sensory organs and perceptions historically and also a source to feel at the least 57 identified feelings by me. The reasoning and decision making are also part of that complex gray matter.

By profession I am an Oracle DBA. I know how complex and complicated Oracle internals are and I have been trying to quench my thirst on those internals ever since I started working close with Oracle. I know much less than the unknown vast.

In this presentation an attempt is made to take Oracle close to non-Oracle Life Sciences community.

Dedication

I dedicate this article to my dad and son and to my wife who are my best friends.

Introduction to Oracle by Oracle

Oracle processes—which include server processes and background processes—and user processes. On almost all systems, the Oracle processes and the user processes are on separate computers.

Background Processes – Oracle Database creates a set of background processes for each instance. The background processes consolidate functions that would otherwise be handled by multiple Oracle Database programs running for each user process. They asynchronously perform I/O and monitor other Oracle Database processes to provide increased parallelism for better performance and reliability.

User processes—sometimes called client processes—are created and maintained to run the software code of an application program (such as an OCI or OCCI program) or an Oracle tool (such as Oracle Enterprise Manager). Most environments have separate machines (laptops, desktops, and so forth) for the client processes. User processes also manage communication with the server process through the program interface, which is described in a later section.

Server Processes – Oracle Database creates server processes to handle requests from connected user processes. A server process communicates with the user process and interacts with Oracle Database to carry out requests from the associated user process. For example, if a user queries some data not already in the database buffers of the SGA, then the associated server process reads the proper data blocks from the datafiles into the SGA. Oracle Database can be configured to vary the number of user processes for each server process. In a dedicated server configuration, a server process handles requests for a single user process. A shared server configuration lets many user processes share a small number of server processes, minimizing the number of server processes and maximizing the use of available system resources.

Instance Memory Structures

Oracle Database creates and uses memory structures for various purposes. For example, memory stores program code being run, data shared among users, and private data areas for each connected user. Two basic memory structures are associated with an Oracle Database:

System Global Area (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 Areas (PGA) are memory regions that contain data and control information for a server or background process. A PGA is non shared memory created by Oracle Database when a server or background process is started. Access to the PGA is exclusive to the process. Each server process and background process has its own PGA.

The  genesis and gene bundle

Living being has multiple stages in the life, exploded or passed thru with a gotten bundle of genes from its parents in the process of fertilization, Oracle too has multiple stages. Oracle depends on parameter file called

init<tobebornSID>.ora

for its gene bundle and has a virtual entity in the memory structures of a host server in nomount – only instance is conceived

mount – the conception is ready for physical association with data and redo log files

open restricted – ready for adulthood but under protected mode

migrate – to upgrade its capabilities takes classes, let me say, and undergoes metamorphosis

open – ready to show the world its universal form

Oracle becomes a perceivable entity ready to be associated with physical structures which it can read and write to when in MOUNT stage like a baby that is there, at the same time a helpless reading ‘controlfiles’ and trying to understand thru that bundle of genes  it future shape and size and ability to grow. YOU need to do everything for it, be it repairs or maintenance or throw it Open to Free World.

When you open that ‘Baby’ RESTRICTED to a family of DBA nobody else is allowed to communicate with that Baby, as it is in a kind of monitored or may be quarantined or might have needed an incubated environment for a specific purpose.

Throw her OPEN – and behold the Universal Form of that Oracle Instances associated with the Database serving multitude to translate their dreams of Business. Never tired and restless.

Attachment in Detachment Architectures

Oracle never allows any user to use HER by himself or herself. Let me know and you are severed. It is so complex and complicated a relation. Believe it or not, she knows what to serve you and what not to. She has her own rules and then you need tell Her too your own rules as the owner or buyer of the space in Public Storage.

Like any other bundle of Software Oracle is FRAGILE and hence would not allow any body to directly be in touch with her. ‘Don’t touch me’ – Lady Oracle warns you – when you are fascinated and feel like having a warm hug and cool kiss.

The User Request Process submits a request for information service then

 

  1. Oracle provides a DEDICATED server process for each User that wants to her services if one-on-one relation with Her is requested.
  2. Oracle provides a SHARED server process when you ask for a Royal Welcome for your community to the rendezvous with Lady Oracle each user’s request is connected to a dispatcher process and that process submits that User Request to any of the server processes that is in the free queue to serve a User. This is a SHARED environmental architecture and Oracle provides a pool of server processes to extend Royal Welcome to Her presence for service.

Look at that, every user is attached to a Oracle Server Process (DEDICATED or SHARED) but is DETACHED with Oracle. Everybody enjoys her service but never her. She is so complicated and complex Gorgeous Lady.

She reminds me of John Keats’ melodramatic situation when he was penning his most famous ode on Grecian Urn.

Musculoskeletal System and Supporting System in Animated Forms compared to Oracle

Like the skeleton and muscles wrapped up into a body covered by the skin to give a shape to the form which is visible to the naked eye, Oracle has its skeletal structures described as

  1. Redo log files
  2. data files

Archived log files are optional structures, depending upon the desire of the creator (YOU) of the instance/database to re-generate the database when it is lost or you want to roll that back into the past.

While a rollback is not possible for a body mass with a form and life with all its vital characters, Oracle permits to roll backwards or forwards basing on a given/saved image at various stages in the linear time scale.

These skeletal structures are wrapped around by the background processes and server processes that provide soft and hard locks to keep them together in a skin sack provided by these background and foreground processes that convert the potential energy of Oracle into a kinetic form of that unique database, an entity you give a name and you interact with.

Time is a cyclical concept in Indian thought and philosophy not linear as has been thought of in western philosophy – time started with ‘big bang’.  Thus ‘reborn’ philosophy too.

Oracle also believes with some modifications but not leaving the basic time cycle philosophy and hence we see restartable images, recoverable images and renamed restartable images and increasing portability.

My Belief on LIFE and its association with a Body compared to Oracle Instance, Data files and Control file

LIFE expresses itself thru a FORM. Otherwise LIFE remain a potential force only. This is my belief. I am not able to prove this. A FORM is declared DEAD, in 2 cases. If the life feels that the form can not hold or is not fit enough and if the FORM is not able to hold within itself the potential force LIFE.

What determines the expression of LIFE thru which FORM is not known to me?

Oracle Instance when associated with data files and redo log files it gains the form of an Oracle Database. But which data files are to be associated with that potential force Oracle is determined by the control files.

Motor Nervous System compared to Control Files

When Life starts using the form the activated nervous system identifies all the limbs and other parts of the body. I remember V.S.Ramachandran discussing in his ‘A Brief Tour of human Consciousness’ says that the mind can carry phantom images of the body parts and to experience the reality of loss of any limbs the brain is to be trained. As long as the old image is not erased from the memory of brain, even the amputated parts of the body are still seen and try to use them and feel the pain of it too.

We need to train that part of the brain that it has no more that amputated part of the body.

Oracle Control files is a kind of that part of the motor nervous system of parietal lobe.

Oracle Control files determines which Instance is to be associated with which set of data files and redo log files. They also determine whether the body is in synch or not by tracking certain vital information like System Change Numbers (SCN) and other required information like

  1. Name of the database
  2. database creation and reset log information
  3. archive log information if the option is enabled
  4. redo threads
  5. log records and each SCN for each log
  6. database block corruption details
  7. database id
  8. tablespace names and names of the associated data files
  9. RMAN catalog

Unless proper mechanism to alter contents of the control file is implemented, Oracle goes on complaining and would not allow you to OPEN the database. Oracle, on its own may take off line one or more data files that are associated with the tablespace or a tablespace. In such circumstances those data files or tablespace are to be recovered or to be dropped from within the database. Check it out, it is like training the brain to accept the amputated part of the body.

Supporting Systems for Involuntary Activities in Life Forms compared to Oracle

Circulatory, respiratory, digestive and excretory systems Life Forms have, Oracle has Server and Background Processes.

Gathering Food is an essential part of the Life. This process of gathering food has been sophisticated by the process of evolution that modified multiple body parts including the process of reasoning and decision making that can help gather food.

The eyes, ears, mouth, fore and hind limbs are all to fulfil that process of gathering food. Communication serves not only to fulfil the process of gathering food but also to feel a sense of security with a sense of belonging ness and expression/s.

Oracle has SERVER processes that gather data from the source files. All these Server Processes are foreground processes and there are background processes to match circulatory,  respiratory,  digestive and excretory systems. There are multiple background processes that process data and ultimately provide that data to those server processes that communicate with the user process to provide the data as has been asked or modify data as has been suggested.

They are

  1. LGWR – Log Writer – This background process writes sequentially batches of modified entries in the memory structures redo buffers for one or more transactions to the online redo log files. Multiple LGWR processes are not allowed as of now by Oracle.
  2. ARCn – Archiver – Optional background process – that generates the archived log files by saving online redo log files to arched log file destination. This process is active only when the database is in ARCHIVELOG mode. Multiple ARC processes are allowed by Oracle with a maximum of 30 set for the instance using LOG_ARCHIVE_MAX_PROCESSES.
  3. DBWn – DB Writer – This background process is to write back to the source (data files) all the changes impacted (that live in the memory buffer cache) to the data as requested by the User/Owner of the data. The initialization parameter DB_WRITER_PROCESSES specifies the number of DBWn processes. The maximum number of DBWn processes is 20. If it is not specified by the user during startup, Oracle determines how to set DB_WRITER_PROCESSES based on the number of CPUs and processor groups. Leave that to default and YOU are fine.
  4. SMON – System Monitor– background process takes care of the instance recovery when a crash recovery is to be made by Oracle.
  5. PMON –Process Monitor – background process takes care of the of the process recovery in case of a process crash. The process monitor (PMON) performs process recovery when a user process fails. PMON is responsible for cleaning up the database buffer cache and freeing resources that the user process was using. For example, it resets the status of the active transaction table, releases locks, and removes the process ID from the list of active processes. PMON periodically checks the status of dispatcher and server processes, and restarts any that have stopped running (but not any that Oracle has terminated intentionally). PMON also registers information about the instance and dispatcher processes with the network listener. SMON and PMON processes check regularly to see whether it is needed and can be called if another process detects the need for it.
  6. RECO – Recoverer Process – this background process is used with the distributed database configuration that automatically resolves failures involving distributed transactions. The RECO process of a node automatically connects to other databases involved in an in-doubt distributed transaction. When the RECO process reestablishes a connection between involved database servers, it automatically resolves all in-doubt transactions, removing from each database’s pending transaction table any rows that correspond to the resolved in-doubt transactions. If the RECO process fails to connect with a remote server, RECO automatically tries to connect again after a timed interval. However, RECO waits an increasing amount of time (growing exponentially) before it attempts another connection. The RECO process is present only if the instance permits distributed transactions. The number of concurrent distributed transactions is not limited.
  7. J000 and CJQ0 processes – Job Queue Processes – Job queue processes are used for batch processing. They run user jobs. They can be viewed as a scheduler service that can be used to schedule jobs as PL/SQL statements or procedures on an Oracle instance. Given a start date and an interval, the job queue processes try to run the job at the next occurrence of the interval. Job queue processes are managed dynamically. This allows job queue clients to use more job queue processes when required. The resources used by the new processes are released when they are idle. Dynamic job queue processes can run a large number of jobs concurrently at a given interval. The job queue processes run user jobs as they are assigned by the CJQ process. Here’s what happens: (a) the coordinator process, named CJQ0, periodically selects jobs that need to be run from the system JOB$ table. New jobs selected are ordered by time. (b) The CJQ0 process dynamically spawns job queue slave processes (J000…J999) to run the jobs. (c) The job queue process runs one of the jobs that was selected by the CJQ process for execution. The processes run one job at a time. (d) After the process finishes execution of a single job, it polls for more jobs. If no jobs are scheduled for execution, then it enters a sleep state, from which it wakes up at periodic intervals and polls for more jobs. If the process does not find any new jobs, then it aborts after a preset interval. The initialization parameter JOB_QUEUE_PROCESSES represents the maximum number of job queue processes that can concurrently run on an instance. However, clients should not assume that all job queue processes are available for job execution.
  8. QMNn – Queue Monitor Processes – The queue monitor process is an optional background process for Oracle Streams Advanced Queuing, which monitors the message queues. You can configure up to 10 queue monitor processes. These processes, like the job queue processes, are different from other Oracle background processes in that process failure does not cause the instance to fail.
  9. MMON – Memory Monitor – performs various manageability-related background tasks, such as issuing alerts whenever a given metrics violates its threshold value. Taking snapshots by spawning additional process (MMON slaves). Capturing statistics value for SQL objects which have been recently modified. MMON background slave (m000) processes are identified separately. The memory monitor (MMON) process was introduced in 10g and is associated with the Automatic Workload Repository new features used for automatic problem detection and self-tuning. MMON writes out the required statistics for AWR on a scheduled basis.
  10. MMNLMemory Monitor Light – performs frequent and light-weight manageability-related tasks, such as session history capture and metrics computation. The Memory Monitor Light (MMNL) process is a new process in 10g which works with the Automatic Workload Repository new features (AWR) to write out full statistics buffers to disk as needed.
  11. MMAN – Memory Manager – is used for internal database tasks. The Automatic Shared Memory Management feature uses a new background process named Memory Manager (MMAN). MMAN serves as the SGA Memory Broker and coordinates the sizing of the memory components. The SGA Memory Broker keeps track of the sizes of the components and pending resize operations.
  12. RBAL coordinates rebalance activity for disk groups in an Automatic Storage Management instance. It performs a global open on Automatic Storage Management disks. ORBn performs the actual rebalance data extent movements in an Automatic Storage Management instance. There can be many of these at a time, called ORB0, ORB1, and so forth.
  13. OSMB is present in a database instance using an Automatic Storage Management disk group. It communicates with the Automatic Storage Management instance.
  14. PSPn – process spawner – spawns Oracle processes.
  15. RVWR – Recovery Writer ( RVWR) introduced which is responsible for writing flashback logs which stores pre-image(s) of data blocks (1) One can use Flashback Database to back out changes that (a). Have resulted in logical data corruptions. (b) Are a result of user error. (c) This feature is not applicable for recovering the database in case of media failure. (d) The time required for flashbacking a database to a specific time in past is DIRECTLY PROPORTIONAL to the number of changes made and not on the size of the database.
  16. CTWR – This is a new process Change Tracking Writer (CTWR) which works with the new block changed tracking features in 10g for fast RMAN incremental backups.
  17. ARBx – These processes are managed by the RBAL process and are used to do the actual rebalancing of ASM controlled disk resources. The number of ARBx processes invoked is directly influenced by the asm_power_limit parameter.
  18. ASMB – The ASMB process is used to provide information to and from the Cluster Synchronization Services used by ASM to manage the disk resources. It is also used to update statistics and provide a heartbeat mechanism.

Nervous System – Reasoning and Decision Making compared to System Global Area  

The bundle of genes provided by the initialization parameter file that spawns system global area. It is true that Brain which is the center of the activity for all decision making and reasoning and repository of the ciphered knowledge and thus consciousness.

The scientific community is still trying to understand the brain its functions. There us no hard disk like to store data which is limited by some capacity in the brain. The ability of the brain is unlimited. The capacity of the brain to store data is unlimited.

Let this be clear that I am not a neurological sciences student but a reader.

The attempt, which I am making in this part of the article, is not to map at one to one with Oracle memory structures. It is just a kind of analogy. I beg every one not to construe nor misunderstand, but enjoy or do not like this let me know or ignore this.

CPU and Memory are the structures that OS allows Oracle to share with on the board.

Every system can survive only when there is a host. Every server is a host, on which Oracle is installed. Oracle uses the resources that are made available by the host.

Some time back I read on some pages of IBM

“In a multitasking environment there is often a requirement to synchronize the execution of various tasks or ensure one process has been completed before another begins. This requirement is facilitated by the use of a software switch known as a Semaphore or a Flag. The function of this is to work in much the same way a railway signal would; only allowing one train on the track at a time. A semaphore timeout is where the railway signal has been set in one state too long, maybe because the train has broken down.” These are some of the Basic UNIX Operating System Kernel Shared Memory Structure resources Oracle uses to create its Brain (Its Memory Structures and Background Processes + Program Global Area that make fully functional Oracle Instance). On Windows Operating System the resources are provided by the threads. Much more discussion on the architectural differences may not be relevant here at all.

The brain mapping broadly consists of as has been taught by my son:

  1. CEREBRAL CORTEX
  1. Frontal Lobe: Most anterior, right under the forehead and known to control:
  1. How we know what we are doing within our environment (Consciousness)?
  2. How we initiate activity in response to our environment. Judgments we make about what occurs in our daily activities. Controls our emotional response?
  3. How Controls our expressive language. Assigns meaning to the words we choose. Involves word associations?
  4. Memory for habits and motor activities.
  1. Parietal Lobe: near the back and top of the head and known to control:
  1. Location for visual attention. Location for touch perception. Goal directed voluntary movements. Manipulation of objects
  2. Integration of different senses that allows for understanding a single concept.
  1. Occipital Lobes: Most posterior, at the back of the head and known to control:
  1. Vision
  1. Temporal Lobes: Side of head above ears and known to control:
  1. Hearing ability Memory acquisition some visual perceptions categorization of  objects.
  1. BRAIN STEM and known to control:
  1. Breathing Heart Rate Swallowing Reflexes to seeing and hearing (Startle Response).
  2. Controls sweating, blood pressure, digestion, temperature (Autonomic Nervous  System).
  3. Affects level of alertness.
  4. Ability to sleep.
  5. Sense of balance (Vestibular Function)
  1. CEREBELLUM and known to control:
  1. Coordination of voluntary movement Balance and equilibrium
  2. Some memory for reflex motor acts.

Oracle System Global Area consists of

O1. FIXED AREA  

This fixed area can not be influenced by any changes in initialization parameters. This size is driven by the release of the Oracle and platform on which Oracle is hosted.

  1. VARAIBLE PORTION

Database buffer cache – this part of the memory structures contain

  1. WRITE LIST that holds the dirty buffers that are to be written to disk
  2. LRU list that holds free buffers, pinned buffers and dirty buffers that have not been moved to WRITE LIST
  3. FREE BUFFERS that do not contain useful date
  4. PINNED BUFFERS that are currently accessed

When an Oracle Database process accesses a buffer, the process moves the buffer to the most  recently used (MRU) end of the LRU list. As more buffers are continually moved to the MRU     end of the LRU list, dirty buffers age toward the LRU end of the LRU list.

The first time an Oracle Database user process requires a particular piece of data, it searches   for the data in the database buffer cache. If the process finds the data already in the cache (a             cache hit), it can read the data directly from memory. If the process cannot find the data in the  cache (a cache miss), it must copy the data block from a datafile on disk into a buffer in the cache before accessing the data. Accessing data through a cache hit is faster than data access  through a cache miss. Before reading a data block into the cache, the process must first find a   free buffer. The process searches the LRU list, starting at the least recently used end of the list.   The process searches either until it finds a free buffer or until it has searched the threshold  limit of buffers.

If the user process finds a dirty buffer as it searches the LRU list, it moves that buffer to the   write list and continues to search. When the process finds a free buffer, it reads the data block                 from disk into the buffer and moves the buffer to the MRU end of the LRU list.

If an Oracle Database user process searches the threshold limit of buffers without finding a free   buffer, the process stops searching the LRU list and signals the DBW0 background process to write some of the dirty buffers to disk.

Redo Log Buffers

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. Redo entries are copied by Oracle Database processes from the user’s memory space to the redo log buffer in the SGA. The redo entries take up continuous, sequential space in the buffer. The background process LGWR writes the redo log buffer to  the active  redo log file (or group of files) on disk. ARCn process helps saves copies of these  redo log files before they are over written to a predefined destination when ARCHIVELOG  mode is enabled for the instance. Thus enables the media recovery as also multiple task   possible with a given backup + archived logs.

Shared Pool

  1. Library cache – this area contains
  1. Shared SQL Areas
  2. Private SQL Areas
  3. PLSQL procedures and packages
  4. Control structures like Locks and Library Cache Handles
  1. dictionary cache

The data dictionary is a collection of database tables and views containing reference information about the database, its structures, and its users. Oracle Database accesses the data dictionary frequently during SQL statement parsing. This access is essential to the continuing operation of Oracle Database.

The data dictionary is accessed so often by Oracle Database that two special locations in memory are designated to hold dictionary data. One area is called the data dictionary cache, also known as the row cache because it holds data as rows instead of buffers (which hold entire blocks of data). The other area in memory to hold dictionary data is the library cache. All Oracle Database user processes share these two caches for access to data dictionary information.

  1. result cache

The result cache is composed of the SQL query result cache and PL/SQL function result cache, which share the same infrastructure.

The DBMS_RESULT_CACHE package provides administration subprograms, which, for example, flush all cached results and turn result-caching on or off system-wide. The dynamic performance views V$RESULT_CACHE_* allow the developer and DBA to determine, for example, the cache-hit success for a certain SQL query or PL/SQL function.

Similar to the result cache, the client result cache also caches results, except that the caching is done on the client side.

  1. PL/SQL Function Result Cache

A PL/SQL function is sometimes used to return the result of a computation whose inputs are one or several parameterized queries issued by the function. In some cases, these queries access data (for example, the catalog of wares in a shopping application) that changes very infrequently compared to the frequency of calling the function. You can include syntax in the source text of a PL/SQL function to request that its results be cached and, to ensure correctness, that the cache be purged when any of a list of tables experiences DML. The look-up key for the cache is the combination of actual arguments with which the function is invoked. When a particular invocation of the result-cached function is a cache hit, then the function body is not executed; instead, the cached value is returned immediately.

  1. ASH buffers

Active session history buffers that are required for the repository management by Oracle. Readers and Writers of these buffers travel in opposite directions. This data is to help fine tune the application and/or database.

Large Pool

The database administrator can configure an optional memory area called the large pool to provide large memory allocations for:

  1. Session memory for the shared server and the Oracle XA interface (used where transactions interact with more than one database)
  2. I/O server processes
  3. Oracle Database backup and restore operations

Java Pool

Java pool memory is used in server memory for all session-specific Java code and data within the JVM. Java pool memory is used in different ways, depending on the mode in which Oracle Database is running.

The Java Pool Advisor statistics provide information about library cache memory used for Java and predict how changes in the size of the Java pool can affect the parse rate. The Java Pool Advisor is internally turned on when statistics_level is set to TYPICAL or higher. These statistics reset when the advisor is turned off.

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 and APPLY processes.

Unless you specifically configure it, the size of the Streams pool starts at zero. The pool size grows dynamically as needed when Oracle Streams is used.

Program Global Area

Oracle Database allocates a program global area (PGA) for each server process. The PGA is used to process SQL statements and to hold logon and other session information. For the purposes of memory management, the collection of all PGA is known as the instance PGA. Using an initialization parameter, you set the size of the instance PGA, and the database distributes memory to individual PGA as needed.

PGA consists of 01. Session Memory and 02. Private SQL Area

Session Memory

Session memory is the memory allocated to hold a session’s variables (logon information) and other information related to the session. For a shared server, the session memory is shared and not private.

Private SQL Area

The private SQL area contains data such as bind variable values, query execution state information, and query execution work areas. Each session that issues a SQL statement has a private SQL area. Each user that submits the same SQL statement has his or her own private SQL area that uses a single shared SQL area. Thus, many private SQL areas can be associated with the same shared SQL area.

The location of a private SQL area depends on the type of connection established for a session. If a session is connected through a dedicated server, private SQL areas are located in the server process’s PGA. However, if a session is connected through a shared server, part of the private SQL area is kept in the SGA.

Private SQL Area consists:

  1. Cursors and SQL Areas
  2. Private SQL Area Components
  3. SQL Work Areas

 Cursor and SQL Areas

 The application developer of an Oracle Database precompiler program or OCI program can explicitly open cursors, or handles to specific private SQL areas, and use  them as a named resource throughout the execution of the program. Recursive  cursors that Oracle Database issues implicitly for some SQL statements also use  shared SQL areas.

The management of private SQL areas is the responsibility of the user process. The allocation and deallocation of private SQL areas depends largely on which application  tool you are using, although the number of private SQL areas that a user process can allocate is always limited by the initialization parameter OPEN_CURSORS. The default value of this parameter is 50.

A private SQL area continues to exist until the corresponding cursor is closed or the statement handle is freed. Although Oracle Database frees the runtime area after the statement completes, the persistent area remains waiting. Application developers close all open cursors that will not be used again to free the persistent area and to minimize the amount of memory required for users of the application.

Private SQL Area Components

The private SQL area of a cursor is itself divided into two areas whose lifetimes are different:

The persistent area—this area contains bind variable values. It is freed only when  the cursor is closed.

The runtime area—Oracle Database creates this area as the first step of an execute request. It contains the following structures:

– Query execution state information

– SQL work areas

These areas are allocated as needed for memory-intensive operations like sorting or  hash-joins. More detail is provided later in this section.

SQL Work Areas

SQL work areas are allocated to support memory-intensive operators such as the following:

Sort-based operators (order by, group-by, rollup, window function)

  1. Hash-join
  2. Bitmap merge
  3. Bitmap create

Regenerative Capability

 Regenerative capability in the human beings for the amputated organs or lost organs is very limited while the same is highly available in some salamanders and other amphibians. The lost organs and limbs are re-grown to make the life form holistic.

Oracle has the regenerative capability much greater than human beings.

When a datafile is lost and empty file can be created and re-associated with the database and then recover that suing then archived log files.  The data file can also be restored from the latest backup available and recovered.

If a redo log file is corrupt or lost the same can be dropped from the database and re-created.

A lost a controlfile can be re-created by executing CREATE CONTROLFILE command and listing all the data files and redo log files.

Conclusion

Oracle is created by a set of dedicated technologists who have translated almost all the 12 rules of RDBMS as laid down by Dr. E.F. Codd in 1985. Many RDBMS have been claiming to have been

following this set RDBMS Bible. I am not getting into any discussion or argument that leads us to different end.

To know the truth, Oracle is complex and complicated like the human mind. I do not think that, the experts sitting there in Oracle may also not have understood Oracle in a holistic way. They have a high level understanding that enables them to integrate their component into Oracle.