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 ?
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?
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 ?
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?
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
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.
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?
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?
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 ?
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 :
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.
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?
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: 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?
* 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:
19. What is the difference between locks, latches, enqueues and semaphores?
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.
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
Answer :
Related Oracle Interview Articles: Oracle Database Admin Interview Questions/FAQs – Part1 Oracle DBA Interview Questions/FAQs – Part4
