Tech Mahindra Oracle DBA Interview Questions - 2

Question: 6

What do the db_file_sequential_read and db_file_scattered_read events indicate?

The db_file_sequential_read event generally indicates index usage and shows an access by rowid while the db_file_scattered_read event indicates full table scan.

A single block is read at one time in the db_file_sequential_read event while multiple blocks are read parallel in the db_file_scattered_read event.

Question: 7

Which trace file is used for performance tuning and why?

User process trace file is used for performance tuning because it contains information about execution plan and resource consumption. This information can be used for performance tuning.

These files are located in the directory specified in the BACKGROUND_DUMP_DIRECTORY parameter.

Question: 8

How can you monitor performance of the database pro-actively?

Several tools, such as Oracle Enterprise Manager, and utilities from third party are available for monitoring database performance.

However, all these tools or utilities depend on statistics gathered by Oracle, which are available through dynamic performance views.

Question: 9

Describe the Oracle Wait Interface?

The Oracle Wait Interface is the set of data dictionary tables that store information about wait events.

Oracle offers multiple views to give information about wait events, such as v$system_event and v$session_event.

You can get the information about the wait events for the database or a specific session from these views and find out the event, which seems too high.

Question: 10

Name a few places you will look to get more details on a performance issue?

Oracle records the information about different kind of errors and the processes in the files, such as ALERT log, user process trace files and background process trace files.