30 Top Oracle DBA Performance Interview Questions with Answers [2023]

Oracle Database Admin (Performance) Interview Questions for experienced - Part 1

1. What is direct path read?

Answer :
A direct path read is where the data is read directly from the data files into the PGA rather than into the buffer cache in the SGA. The direct path read is available only when the Oracle optimizer chooses a full table scan.

2. What are the index related things you will check in explain plan ?

Answer :
Index range scan
Index fast full scan
Index unique scan
BITMAP INDEX SINGLE VALUE

3. What is the cache buffer chains wait event in Oracle Database?

Answer :
A user process acquires the CACHE BUFFERS CHAINS latch to scan the System Global Area (SGA) for database cache buffers. Blocks in the buffer cache are placed on linked lists (cache buffer chains). Blocks are put on the hash chain according to their DBA (data block address) and CLASS of the block. Each hash chain is protected by a single child latch. The latch allows a process to scan a hash chain without having the linked list change while it scans.
Hot blocks are a common cause of cache buffers chains latch contention.

4. What are Scattered read and sequential read ?

Answer :
A db file sequential read is an event that shows a wait for a foreground process while doing a sequential read from the database. This is most commonly used for single block reads. Single block reads are most commonly seen for index block access or table block access by a rowid.
A db file scattered read will read multiple data blocks. Multi-block reads are typically used on full table scans. The "scattered read" refers to the fact that multiple blocks are read into DB block buffers that are 'scattered' throughout memory. 

5. What is the difference between Hash join and nested loop join?

Answer :
Hash join uses a hash-table based lookup mechanism while nested loop doesn't or 

the hash join can use cursor work-area memory (allocated in UGA) for buffering rows, while nested loops join can not.

6. What is cursor sharing ?

Answer :
A cursor is a name to a specific private SQL area. The cursor contains session-specific state information such as bind variable values and result sets. you can think of a cursor as a pointer on the client side and as a state on the server side.
Exact means exactly use my cursor and don’t do any changes. In this state, Oracle searches different execution plans for every query.

Force means force to cursor sharing. In this state, the database engine replaces every literal with bind variables. One child cursor is used for every SQL.

Similar means use cursor sharing but take advantage of histograms. If no histogram exists for one of the replaced literals, similar is the same as force state and an already existing cursor will be used. If a histogram exists, a new child cursor is created.

7. What is the parent and child cursor in oracle?

Answer :
PARENT CURSOR stores the SQL text of the cursor. When two statements are identical textually, they will share the same parent Cursor.
The Child Cursor holds other required information like the identity of the objects referenced by the SQL Cursor; the names, type and length of the bind variables used. Child cursors are also called versions.

8. What is bind peeking?

Answer :
One of the first drawbacks of bind variables is the explain plan. Oracle optimizer is able to peek the value of the bind variable and generate a plan like if the query where with a fixed value and so using histograms and statistics.
Bind variable peeking is used with SQL statements in the library cache to see if the value of a host variable is going to change the existing execution plan.

9. What is adaptive cursor sharing ?

Answer :
Oracle 11g uses Adaptive Cursor Sharing to solve bind peeking problem by allowing the server to compare the effectiveness of execution plans between executions with different bind variable values.

10. What is cardinality? What is high & low cardinality?
Answer :

11. What is the cardinality feedback init parameter ?
Answer :

12. What is dynamic_sampling?
Answer :

13. What are histograms in the database?

Answer :
Histograms are a feature of the cost-based optimizer (CBO) that allows the Oracle engine to determine how data is distributed within a column. They are most useful for a column that is included in the WHERE clause of SQL and the data distribution is skewed.
Histograms provide improved selectivity estimates in the presence of data skew, resulting in optimal execution plans with non-uniform data distributions.

14. What are the hints? give some examples?
Answer :

15. What is a reverse key index ?

Answer :

16. What is enq: hw contention ?

Answer :
The enq: HW - contention occurs when competing processing is inserting into the same table and are trying to increase the high watermark of a table simultaneously. HW enqueues are acquired in Exclusive mode before updating HWM (High Water Mark) and essentially HW enqueues operate as a serializing mechanism for HWM updates.

17. What is Ora-4031?

Answer :
ORA-04031: unable to allocate string bytes of shared memory

ORA-04031 is an error message related to the lack of available SGA memory components.

While ORA-04030 is related to the lack of available memory in the PGA area.
*  Heavy fragmentation of the shared pool - This can be fixed by increasing the shared_pool_size or doing a "alter system flush shared pool" or bouncing the instance.
* Too many pinned packages - If you have pinned lots of packages with dbms_shared_pool.keep, they have not left enough room for new work.

18. how to reduce index contention (en: TX - index contention) in oracle?

Answer :
The most probable reasons are:

• Indexes on the tables which are being accessed heavily from the application.
• Indexes on table columns which are monotonically growing. In other words, most of the index insertions occur only on the right edge of an index.
• Large data purge has been performed, followed by high concurrent insert
• Solutions:
• Rebuild or shrink associated index after huge amount of data purge
• Rebuild the index  as reverse key indexes or hash partition the indexes which are listed in the 'Segments by Row Lock Waits' of the AWR reports
• Increase PCT_FREE for the index
• Consider increasing the CACHE size of the sequences

19. What is the difference between locks, latches, enqueues and semaphores?

Answer :
Semaphores: Inside Oracle database there are many processes or threads are running and for successful running they need to communicate with each other. The process through which they communicate with each other is known as semaphores. It is just like a signal which tells the thread that when to run and stop. There is a semaphore for every Oracle server process.

Locks and Latches: There are several processes inside Oracle SGA (System Global Area) that need to be accessed concurrently by many different database processes. And it is very important that only one process is able to modify a particular data structure at a time. Oracle does this with the help of Locks and Latches.

Latches: Latches are the more restrictive mechanism, because they do not allow multiple processes to inspect the protected data structure at the same time. Latches do not support queuing. Oracle uses latch for the data is accessed in detail.

Locks: Locks allow for better concurrency, because they may be held in a shared mode when the data structure is simply being inspected. Queuing is supported by Latch. Locks are used by Oracle for performance tuning.

Enqueues: Many of the Locks in Oracle are known as Enqueues locks. To enqueue a lock request is to place that request on the queue for its resource.

20. How to find out whether the query has run with RBO or CBO?
Answer :

Related Oracle Interview Articles: Oracle Database Admin Interview Questions/FAQs – Part1   Oracle DBA Interview Questions/FAQs – Part4

Top 30 Oracle Performance Related Interview Questions for DBAs

Oracle DBA Performance Related Interview Questions with answers [ 2023 ] - Part 2

21. What you’ll check whenever a user complains that his session/database is slow?
Answer :

22. What is the use of statistics?
Answer :

23. How to generate explain plan?
Answer :

24. How to check explain plan of already ran SQLs?
Answer :

25. What are the top 5 wait events (in AWR report) and how you will resolve them?
Answer :
http://satya-dba.blogspot.in/2012/10/wait-events-in-oracle-wait-events.html

db file sequential read => tune indexing, tune SQL (to do less I/O), tune disks, increase buffer cache. This event is indicative of disk contention on index reads. Make sure all objects are analyzed. Redistribute I/O across disks. The wait comes from the physical side of the database. It is related to memory starvation and non-selective index use. Sequential read is an index read followed by the table read because it is doing index lookups which tell exactly which block to go to.
db file scattered read => disk contention on full table scans. Add indexes, tune SQL, tune disks, refresh statistics, and create materialized view. Caused due to full table scans maybe because of insufficient indexes or unavailability of updated statistics.
db file parallel read => tune SQL, tune indexing, tune disk I/O, increase buffer cache. If you are doing a lot of partition activity then expect to see that wait even. It could be a table or index partition.
db file parallel write => if you are doing a lot of partition activity then expect to see that wait even. It could be a table or index partition.
db file single write => if you see this event then probably you have a lot of data files in your database.

control file sequential read
control file parallel write

log file sync => committing too often, archive log generation is more. Tune applications to commit less, tune disks where redo logs exist, try using nologging/unrecoverable options, log buffer could be too large.
log file switch completion => May need more log files per group.
log file parallel write => Deals with flushing out the redo log buffer to disk. Disks may be too slow or have an I/O bottleneck. Look for log file contention.
log buffer space => Increase LOG_BUFFER parameter or move log files to faster disks. Tune application, use NOLOGGING, and look for poor behavior that updates an entire row when only a few columns change.
log file switch (checkpoint incomplete) => May indicate excessive db files or slow IO subsystem.
log file switch (archiving needed)  => Indicates archive files are written too slowly.
redo buffer allocation retries => shows the number of times a user process waited for space in the redo log buffer. 
redo log space wait time => shows cumulative time (in 10s of milliseconds) waited by all processes waiting for space in the log buffer.

buffer busy waits/ read by other session => Increase DB_CACHE_SIZE. Tune SQL, tune indexing, we often see this event along with full table scans, if the SQL is inserting data, consider increasing FREELISTS and/or INITRANS, if the waits are on segment header blocks, consider increasing extent sizes.
free buffer waits => insufficient buffers, process holding buffers too long or i/o subsystem is over loaded. Also check you db writes may be getting clogged up.
cache buffers lru chain => Freelist issues, hot blocks.
no free buffers => Insufficient buffers, dbwr contention.

latch free
latch: session allocation
latch: in memory undo latch => If excessive could be bug, check for your version, may have to turn off in memory undo.
latch: cache buffer chains => check hot objects.
latch: cache buffer handles => Freelist issues, hot blocks.
direct path write => You wont see them unless you are doing some appends or data loads.
direct Path reads => could happen if you are doing a lot of parallel query activity.
direct path read temp or direct path write temp => this wait event shows Temp file activity (sort,hashes,temp tables, bitmap) check pga parameter or sort area or hash area parameters. You might want to increase them.
library cache load lock
library cache pin => if many sessions are waiting, tune shared pool, if few sessions are waiting, lock is session specific.
library cache lock => need to find the session holding the lock, look for DML manipulating an object being accessed, if the session is trying to recompile PL/SQL, look for other sessions executing the code.
undo segment extension => If excessive, tune undo.
wait for a undo record => Usually only during recovery of large transactions, look at turning off parallel undo recovery.

enque wait events => Look at V$ENQUEUE_STAT

SQL*Net message from client
SQL*Net message from dblink
SQL*Net more data from client
SQL*Net message to client
SQL*Net break/reset to client

26. What are the init parameters related to performance/optimizer?
Answer :
optimizer_mode = choose
optimizer_index_caching = 90
optimizer_index_cost_adj = 25
optimizer_max_permutations = 100
optimizer_use_sql_plan_baselines=true
optimizer_capture_sql_plan_baselines=true
optimizer_use_pending_statistics = true;
optimizer_use_invisible_indexes=true
_optimizer_connect_by_cost_based=false
_optimizer_compute_index_stats= true;

27. What are the values of optimizer_mode init parameters and their meaning?
Answer :
optimizer_mode = choose

28. What is the use of AWR, ADDM, ASH?
Answer :

29. How to generate AWR report and what are the things you will check in the report?
Answer :

30. What is Elapsed time and DB Time in the AWR report?
Answer :
Elapsed Time - When looking into the AWR report, the Elapsed Time is the wall clock time of duration for which the AWR report has been generated. For example, if we generate AWR report for 1 hour then Elapsed Time in AWR report will be 60 mins.

DB CPU - DB CPU is the CPU consumption by all Oracle server processes/foreground processes during snapshot interval time.

DB Time - DB time is a statistic which represents CPU time consumed by all Oracle processes over a period of time plus non-idle wait time. DB Time is the time spent by the database server executing user calls. DB Time is the total time spent by the all user processes which are actively working or actively waiting in the database calls. It includes the CPU Time, IO Wait time and non-idle time. It tells us that how much activity is performed by the database for the duration.

31. How to generate ADDM report and what are the things you will check in the report?
Answer :

32. How to generate ASH report and what are the things you will check in the report?
Answer :

33. How to generate STATSPACK report and what are the things you will check in the report?
Answer :

34. How to generate TKPROF report and what are the things you will check in the report?
Answer :
The tkprof tool is a tuning tool used to determine cpu and execution times for SQL statements. Use it by first setting timed_statistics to true in the initialization file and then turning on tracing for either the entire database via the sql_trace parameter or for the session using the ALTER SESSION command. Once the trace file is generated you run the tkprof tool against the trace file and then look at the output from the tkprof tool. This can also be used to generate explain plan output.

Related Oracle Interview Articles: Oracle Data Guard Interview Questions  Oracle DBA Interview Questions – Part4   UNIX Interview Questions for Oracle DBAs