Find us on Google+ Google+

October 20, 2012

Oracle Performance Related Interview Questions/FAQs

Oracle Performance Related Interview Questions/FAQs

1. What you’ll check whenever user complains that his session/database is slow?

2. What is the use of statistics?

3. How to generate explain plan?

4. How to check explain plan of already ran SQLs?

5. How to find out whether the query has ran with RBO or CBO?

6. What are top 5 wait events (in AWR report) and how you will resolve them?

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 that comes from the physical side of the database. It related to memory starvation and non selective index use. Sequential read is an index read followed by table read because it is doing index lookups which tells 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 may be 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 than 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

7. What are the init parameters related to performance/optimizer?
optimizer_mode = choose
optimizer_index_caching = 90
optimizer_index_cost_adj = 25
optimizer_max_permutations = 100
optimizer_use_pending_statistics = true;
_optimizer_compute_index_stats= true;

8. What are the values of optimizer_mode init parameters and their meaning?
optimizer_mode = choose

9. What is the use of AWR, ADDM, ASH?

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

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

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

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

14. How to generate TKPROF report and what are the things you will check in the report?
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.


  1. Hello Buddy,

    What a brilliant post I have come across and believe me I have been searching out for this similar kind of post for past a week and hardly came across this.

    I am trying to generate a flat file using SSIS 2014 and want to use ý ascii(0253) instead of comma in the delimited csv file, please can you advise.

    Thank you very much and will look for more postings from you.

    Irene Hynes

  2. Hi There,

    Your blog is such a complete read. I like your approach with Oracle Performance Related Interview Questions/FAQs . Clearly, you wrote it to make learning a cake walk for me.

    I have a output table with values as below

    P_K Col1 Col2 Col3

    1 A null null

    1 null B null

    1 null null C

    2 A2 null null

    2 null B2 null

    2 null null C2

    I need to get output as

    P_K Col1 Col2 Col3

    1 A B C

    2 A2 B2 C2

    I tried to do self join with not null like below

    select a.p_k, a.col1, b.col2, c.col3
    from t7 a, t7 b, t7 c
    where a.p_k= b.p_k
    and b.p_k= c.p_k
    and a.col1 is not null
    and b.col2 is not null
    and c.col3 is not null

    This works but is there any built in functionality or any other optimal way to get this output.

    Excellent tutorials - very easy to understand with all the details. I hope you will continue to provide more such tutorials.

    Thanks a heaps,

  3. Hi There,

    Great piece on Satya's DBA Blog, I’m a fan of the ‘flowery’ style Looking forward to more long form articles

    Is true the number of really declining people with Unix skills is?
    A lot of Microservices, serverless computing bandwagon is seen on the market, does it means Unix/Linux and servers will be non-existent in future?

    Awesome! Thanks for putting this all in one place. Very useful!