Oracle DBA Interview Questions

Posted on May 8, 2008

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 backed-up?
RC_BACKUP_REDOLOG or
1) backup set RMAN> list archivelog all | from … until….. e.g RMAN> list archivelog from time ‘sysdate-1′ 2)backup RMAN> LIST BACKUP OF ARCHIVELOG FROM SEQUENCE 1437 UNTIL SEQUENCE 1437;
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 pool.
? 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.
Log_buffer=3M
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.
Java_pool_size=10M
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 as follows:
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 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.
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 V$DB_CACHE_ADVICE.
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 DB_BLOCK_BUFFERS. For Oracle 9 onwards: increate the initialisation parameter DB_CACHE_SIZE.
For the library cache hit ratio, it calculates how often the parsed representation of the statement can be reused. It also known as soft parse.
Select namespace, pins, pinhits, reloads, invalidations from v$librarycache order by namespace;
Library Cache Hit Ratio = sum(pinhits) / sum(pins)
For the Dictionary cache hit ratio, typically, if the shared pool is adequately sized for the library cache, it will also be adequate for the dictionary cache data.
6. Other than making use of the statspack utility， what would you check when you are monitoring or running a health check on an Oracle 8i or 9i database?
Daily Procedures:
A.
Verify all instances are up
B.
Look for any new alert log entries
C.
Verify DBSNMP is running
D.
Verify success of database backup
E.
Verify success of database archiving to tape
F.
Verify enough resources for acceptable performance
G.
Copy Archived logs to standby Database and Roll forward
H.
Read DBA manuals for one hour
Nightly Procedures:
A.
Collect volumetric data – Gather statistics data
7. How do you tell what your machine name is and what is its IP address?
Uname –n
Hostname
8. How would you go about verifying the network name that the local_listener is currently using?
Lsnrctl status
check listener.ora file.
Show parameter LISTENER in SQLPLUS
9. You have 4 instances running on the same UNIX box. How can you determine which shared memory and semaphores are associated with which instance?
Ipcs
SQL> oradebug setmypid
SQL> oradebug ipc
SQL>oradebug tracfile_name
10. What view(s) do you use to associate a user’s SQLPLUS session with his o/s process?
v$process and v$session
Select a.spid from v$process a, v$session b where a.addr = b.addr and b.audsid=userenv(‘sessionid’);

Popularity: 6% [?]

Related Posts:

» Filed Under Other

Comments

Leave a Reply