Oracle System Architecture Interview Questions & Answers

Download PDF

Please share your answers.

Shared SQL areas are shared memory areas; any Oracle process can use a shared SQL area. The use of shared SQL areas reduces memory usage on the database server, thereby increasing system throughput. Shared SQL areas are aged out of the shared pool by way of a least recently used algorithm (similar to database buffers). To improve performance and prevent reparsing, you may want to prevent large SQL areas from aging out of the shared pool

Redo log buffers consists of change vectors where each vector contains information about which block is changed and where is the change done in the memory. This information will be useful when you are performing recovery and while writing to disk while checkpointing. There is a 1-to-1 correspondence with redo log buffers and data buffer cache buffers.

A cursor can be viewed as a "Pointer" into the result table of an SQL SELECT statement. Cursors are needed whenever the result contains more than one row. 3rd generation programs like COBOL and C cannot handle more than one row at a time. SQL produces a set-at-a-time. The cursor allows the set to be processed one row-at-a-time, just like a sequential file. COBOL and C are happy, SQL is happy. I'm happy.

LGWr writes when:

1)at commit
2)when 1/3rd full
3)when there is 1mb of redo
4)every 3 seconds
5)before DBWn writes.

Actually Shared pool is composed of two parts one is Library Cache and another Data Dictionary cache The Library cache contains the shared sql areas, private sql areas, PL/SQL procedures and packages, and control structures such as locks and Library cache handles, The shared sql area contains the parse tree and execution plan where as the private sql area contains values for bind variables and runtime buffers.the data dictionary cache holds most recently used database dictionary informations.

System Global Area (SGA) is component of instance which consist shared memory & control information of the instance It consist of shared pool, database buffer cached, redo log buffer, large pool, Java pool.

DBWn writes when:

1)checkpoint occurs
2)dirty buffers reach threshold
3)there are no free buffers
4)timeout occurs
5)RAC ping request is made
6)when any tablespace is taken offline,read only,drop or truncated & when begin backup

The checkpoint (CKPT) process frees up limited memory space and ensures instance recovery. It is a background process that monitors the number of dirty buffers that have not been written to disk. Depending on the LOG_CHECKPOINT_INTERVAL parameter, it also gives the DBWR a wakeup call to write dirty buffer to disk. At a specified time, it updates all the data and control files with the new log file control number. This process is optionally enabled if parameter CHECKPOINT_PROCESS contains a TRUE value

LGWR is oracle background process.. all changes which have been made inthe database gets recorded in the redolog buffer.. so when the users fires an commit statement , LGWR writes these changes it into thr redolog files. and also writes it into files when the redolog buffer is one thrid full... and also when buffer is nealy 1mb full lgwr writes it into redologfiles.

It's made up of Memory Structure and Blackground ProcessesMemory Struture(In oracle SGA(system global area))SGA consists of several memory areamandatory memory area1.shared pools=libary+data dictionary cache2.database buffer cache3.redo log bufferOptional1.large pool2.jave poolBACKGROUND Process(mandatory)1.smon(system monitor)2.pmon(process monitro)3.ckpt(check point)4.dbwr(database writer)5.lgwn(log writer)pls correct me,if i miss any point

System Monitor (SMON) performs instance recovery at instance start-up. In a multiple instance system (one that uses the Parallel Server), SMON of one instance can also perform instance recovery for other instance that have failed SMON also cleans up temporary segments that are no longer in use and recovers dead transactions skipped during crash and instance recovery because of file-read or off-line errors. These transactions are eventually recovered by SMON when the tablespace or file is brought back on-line SMON also coalesces free extents within the database to make free space contiguous and easier to allocate.

Please share your answers.

The deployment of a single database across a cluster of servers—providing unbeatable fault tolerance, performance and scalability

Components of SGA are :
1> Shared pool : consists of shared sql area and shared pl/sql area
2> Data Base buffer cache(DB cache) : contains most recently used blocks & dirty blocks(modified but not yet written to datafile)
3> Redolog Buffer : contains changed/redo data
4> Large pool : used only in parellel sever environment
5> Java pool : used when you are using java strored procedures etc.

Oracle introduced the Unbreakable Enterprise Kernel for Oracle Linux during Sep 2010 which runs directly on Oracle LINUX 5 with fast, modern, reliable kernel that is optimized for Oracle software and hardware.

sga stands fro system global area.. which is the part of the oracle instance(includes memory struture and background processes). for faster executon and better performance everthing comes in the sga.so sga is located in oracle instance,,, which itself consist of two parts.
1>database buffercache: which stores the recently executed database blocks,, when user fires a query oracle searches that block in dictionary and stores it into buffer cache.. so if the next time when the user fires the same query ... users gets output from the buffer cache itself.. no need for parsing again.. only fetch and execute will work.
2> redolog buffer: it stores all the changes made in the database,, and also all DMLs,, when user fires an commit statement,, lgwr back process writes it into the redolog files.. which can be used for recovery of database during instance crash .when u install an oracle in ur machine ,,, system will become slow because of the unstructured sga configuration and also ur system configuration , once the oracle instance instants starts .. oracle itself will allocate the space from system .for example : if u have system confi 512 mb and if will install oracle with specified sga congi. obviously system performance will go low.so in order to increase the system performance,,, use the system configu should be well enough to run the oracle instance.... and also allocates enough space for sga....

Database buffer contains the data which server process reads recently from disk. Database buffer may have three parts: 1. free buffer 2. dirty buffer 3. pinned buffer Free buffer area is free to be used to store data. Dirty buffer area contains the data which is updated since read from disk, but not written to the disk till now. Pinned buffer is a area where processing on specific data for current transaction is going on. Database buffer may be of three types: 1. keep 2. recycle 3. default Database buffer may be divided into five different block size buffers: 2k,4k,8k,16k,32k and 64k also but supported by most of the servers (HP, IBM AIX, SOLARIS 10)

Program Global Area. The PGA is a memory region containing data and control information for a single process (server or background). One PGA is allocated for each server process; the PGA is exclusive to that server process and is read and written only by Oracle code acting on behalf of that process. A PGA is allocated by Oracle when a user connects to an Oracle database and a session is created

That is Acl . Those levels are

Manager,
designer,
editor,
author,
reader,
depositor,
no access

Atleast one Dispatcher process is created for every communication protocol in use.

Lock (LCKn) are used for inter-instance locking when the ORACLE Parallel Server option is used.

RECOver (RECO) is used to resolve distributed transactions that are pending due to a network or system failure in a distributed database. At timed intervals,the local RECO attempts to connect to remote databases and automatically complete the commit or rollback of the local portion of any pending distributed transactions.

Dispatcher (Dnnn) process is responsible for routing requests from connected user processes to available shared server processes and returning the responses back to the appropriate user processes.

Process Monitor (PMON) performs process recovery when a user process fails PMON is responsible for cleaning up the cache and Freeing resources that the process was using PMON also checks on dispatcher and server processes and restarts them if they have failed.