Oracle DBA Interview Questions By B G
1. How many memory layers are in the shared pool?
The shared pool consists of the library cache and the dictionary cache.
2. How do you find out from the RMAN catalog if a particular archive log has been
1) backup set
RMAN> list archivelog all | from ... until.....
RMAN> list archivelog from time 'sysdate-1'
RMAN> LIST BACKUP OF ARCHIVELOG FROM SEQUENCE 1437 UNTIL
3. How can you tell how much space is left on a given file system and how much space
each of the file system's subdirectories take-up?
df – report file system disk space usage.
du – summarize disk usage of each file, recursively for directories.
4. Define the SGA and：
System Global Area.
It consists of Shared pool, Large pool, Java pool, Buffer cache, Log buffer,
Nonstandard block size buffer caches, Keep and recycle buffer caches, and Streams
? How you would configure SGA for a mid-sized OLTP environment?
Suppose only Oracle is running on the server and MTS is chosen for OLTP.
Reserve 10% of RAM for UNIX/Linux or 20% of RAM for Windows. The rest of
RAM is allocated to SGA.
Large_pool_size: For dedicated Oracle server, 20-30M is enough. For MTS, the
UGA will be here. Estimate parallel connection and MTS server processes.
Shared_pool_size: If all the SQL statements that sent to ORACLE are using bind
variable adequately, then 300M is enough in most cases and it should greater than
100M depending on total RAM.
Data buffer: All the rest RAM should be allocated to Data buffer.
Below is some referenced materials related to this issue:
If you only have Oracle on the server, start by reserving 10% of RAM for
UNIX/Linux or 20% of RAM for Windows. With whatever RAM is left-over:
SGA Sizing and PGA Sizing
For dedicated Oracle servers, the maximum total RAM SGA size can be computed
OS Reserved RAM -- This is RAM required to run the OS kernek and system
functions, 10% of total RAM for UNIX/Linux, and 20% of total RAM for Windows.
Oracle Database Connections RAM -- Each Oracle connection requires OS RAM
regions for sorting and hash joins. (This does not apply when using the Oracle
multithreaded server or pga_aggregate_target.) The maximum amount of RAM
required for a session is as follows:
2 megabytes RAM session overhead + sor_area_size + hash_area_size
? What is involved in tuning the SGA?
Check the statspack report.
Check hit ratio of Data buffer. If it is less than 90%, then we need to increase the
Check hit ratio of Shared pool. If it is less than 95%, then we need to increase the
Check log buffer. If redo buffer allocation retries/redo entries is greater than 1%,
then we need to increase log_buffer.
Determine how to use keep pool and recycle pool efficiently.
5. What is the cache hit ratio, what impact does it have on performance of an Oracle
database and what is involved in tuning it?
For the buffer cache hit ratio, it calculates how often a requested block has been
found in the buffer cache without requiring disk access. This ratio is computed using
data selected from the dynamic performance view V$SYSSTAT. The buffer cache
hit ratio can be used to verify the physical I/O as predicted by
Select name, value From v$sysstat
Where name in ('db block gets', 'consistent gets', 'physical reads');
The cache-hit ratio can be calculated as follows:
Hit ratio = 1 - (physical reads / (db block gets + consistent gets))
If the cache-hit ratio goes below 90% then:
For Oracle 8 and earlier: increase the initialisation parameter