Oracle DBA Interview Questions and Answers

Post date: Feb 12, 2019 6:22:46 AM

• How many memory layers are in the shared pool?

Ans:  The shared pool portion of the SGA contains three major areas: 

library cache(contains parsed sql statements,cursor information,execution plans), 

dictionary cache (contains cache -user account information,priveleges information,datafile,segment and extent information), 

buffers for parallel execution messages, and control structure.

Define the SGA and:

i)  How you would configure SGA for a mid-sized OLTP environment?

ii) What is involved in tuning the SGA? 

Ans: SGA: The System Global Area (SGA) is a group of shared memory areas that are dedicated to an Oracle “instance” (an instance is your database programs and RAM). All Oracle processes use the SGA to hold information. The SGA is used to store incoming data (the data buffers as defined by the db_cache_size parameter), and internal control information that is needed by the database. You control the amount of memory to be allocated to the SGA by setting some of the Oracle “initialization parameters”.  These might include db_cache_size, shared_pool_size and log_buffer.

• 1. i) 40% of RAM can be used for sizing SGA rest is reserved for OS and others in 64 bit machine and in 32 bit machine max SGA configured can be 1.5GB only.

  2. ii) Check the statspack report. Check hit ratio of Data buffer. If it is less than 90%, then we need to increase the Data buffer. Check hit ratio of Shared pool. If it is less than 95%, then we need to increase the Shared pool. Check log buffer. If redo buffer allocation retries/redo entries is greater than 1%, then we need to increase log_buffer.

  How do you find out from the RMAN catalog if a particular archive log has been backed-up? 

Ans:  list archivelog all;

  How can you tell how much space is left on a given file system and how much space each of the file system’s sub directories take-up? 

Ans: df -kh and du-sh

What is the cache hit ratio, what impact does it have on performance of an Oracle database and what is involved in tuning it? 

Ans: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 V$DB_CACHE_ADVICE.

sql> 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:  increase the initialisation parameter DB_CACHE_SIZE.

Library cache hit ratio: It calculates how often the parsed representation of the statement can be reused. It also known as soft parse.

sql>  select namespace, pins, pinhits, reloads, invalidations from v$librarycache order by namespace;

Library Cache Hit Ratio = sum(pinhits) / sum(pins)

Dictionary cache hit ratio:It is a measure of the proportion of requests for information from the data dictionary, the collection of database tables and views containing reference information about the database, its structures, and its users. On instance startup, the data dictionary cache contains no data, so any SQL statement issued is likely to result in cache misses. As more data is read into the cache, the likelihood of cache misses should decrease. Eventually the database should reach a “steady state” in which the most frequently used dictionary data is in the cache.

How would you go about verifying the network name that the local_listener is currently using?

Ans: lsnrctl stat or ps-eaf|grep tns

You have 4 instances running on the same UNIX box. How can you determine which shared memory and semaphores are associated with which instance? 

Ans:

SQL> oradebug setmypid

SQL> oradebug ipc

SQL>oradebug tracfile_name

Also you can check the spfile. The parameters will start with instance_name. parameter_name naming.