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.
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
A db file scattered read will read multiple data blocks. Multi-block reads are typically used on full table scans.
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.
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.
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.
75. What is the difference between instance recovery and crash recovery ?
Crash recovery is done automatically by SMON, in case of abonormal shutdown such as shutdown abort or any such reason.
Instance recovery refers to the case where a surviving instance recovers a failed instance in an Oracle Real Application Clusters (RAC) database.
