Find us on Google+ Google+

March 25, 2019

Oracle DBA Interview Questions along with Answers

Oracle Database Admin Interview Questions - Part 5

1. What is direct path read?
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 dndex related things you will check in explain plan ?
Index range scan
Index fast full scan
Index unique scan

3. What is cache buffer chains wait event ?
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 common cause of cache buffers chains latch contention.

4. What are Scattered read and sequential read ?
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 mostly 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?
hash join uses a hash-table based lookup mechanism while nested loop doesn't or that the hash join can use cursor work-area memory (allocated in UGA) for buffering rows, while nested loops join can not.

6. What will happens when we open database in resetlogs ?
Use RESETLOGS after incomplete recovery (when the entire redo stream wasn't applied). RESETLOGS will initialize the logs, reset your log sequence number, and start a new "incarnation" of the database.

7. What is cursor sharing ?
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, database engine replaces every literals 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 same as force state and an already existing cursor will be used. If a histogram exist, a new child cursor is created.

8. What is parent and child cursor in oracle?
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.  

9. What is bind peeking?
One of the first drawbacks of bind variables are 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.

10. What is adaptive cursor sharing ?
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.

11. What is cardinality? What is high & low cardinality?

12. What is cardinality feedback init parameter ?

13. What is dynamic_sampling?

14. What are histograms?
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.

15. Can we offline system table space?
No, we can’t off-line system and undo table spaces.

16. What are the hints? give some examples?

17. What is the use of set dbid?
While connecting to recovery manager, where db_name is not unique, better to set dbid.
We must set dbid value, while recovering backup of spfile.

18. How to restore spfile without backup?
Take spfile or pfile backup from standby and restore it.
Use alert log to get non-default init parameters.
Use RMAN auto or manual spfile backups.

19. What is reverse key index ?

20. What will happen when a node crashes?
* 1. One or more RAC instances fail.
* 3. Node failure is detected by any one of the remaining instances.
* 4. Global Resource Directory(GRD) is reconfigured and distributed among surviving nodes.
* 5. The instance which first detected the failed instance, reads the failed instances redo logs to determine the logs which are needed to be recovered. The above task is done by the SMON process of the instance that detected failure.
* 6. Until this time database activity is frozen, The SMON issues recovery requests for all the blocks that are needed for recovery. Once all the blocks are available, the other blocks which are not needed for recovery are available for normal processing.
* 7. Oracle performs roll forward operation against the blocks that were modified by the failed instance but were not written to disk using redo log recorded transactions.
* 8. Once redo logs are applied, uncomitted transactions are rolled back using undo tablespace.
* 9. Database on the RAC in now fully available.

21. What is enq: hw contention ?
The enq: HW - contention occurs when competing processing are inserting into the same table and are trying to increase the high water mark 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.

22. What is Ora-4031?
ORA-04031: unable to allocate string bytes of shared memory
ORA-04031 is error message related to lack of available SGA memory component While ORA-04030 is related to lack of available memory in 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 leave enough room for new work.

23. how to reduce index contention (en: TX - index contention) in oracle?
Most probable reasons are:
  o  Indexes on the tables which are being accessed heavily from the application.
  o 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.
  o  Large data purge has been performed, followed by high concurrent insert
o       Rebuild or shrink associated index after huge amount of data purge
o        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
o       Increase PCT_FREE for the index
o        Consider increasing the CACHE size of the sequences

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

No comments:

Post a Comment