Home / Oracle DBA Interview Questions / Oracle Performance Tuning Interview Questions

Oracle Performance Tuning Interview Questions


  1. Till yesterday a query is giving output in 1 min. today it is taking 5 min. how you will troubleshoot this problem?
    1. We will start looking at the network issues
    2. Check if the query is changed from yesterday to today
    3. Check table statistics are upto date
    4. Generate explain plan and see where is the problem
  2. A job which will generally execute in 1 hour, now running since 5 hours, what can be done to find out the problem?
    1. Check if any code change in the job
    2. Check table statistics
    3. Generate explain plan for all the queries and DML/DDL statements in the job
    4. Better to disable indexes for time being in order to make it faster
  1. Users are complaining that database is running slow everyday between 11 AM t0 2 PM. How to find out what’s going wrong during that time?
    1. We can generate statspack or AWR report and will observe different sections
  1. What is the purpose of collecting statistics?
    1. It will help optimizer to generate best execution plan
  1. If you need to schedule a new analyze job for a single large schema, how you will do that?
    1. We will schedule daily analyze job for frequently accessed tables
    2. Schedule weekly job for semi dynamic tables
  1. What things you will analyze in explain plan?
    1. We will check if query is using proper indexes are not. If not we will either create new indexes or can use hints to specify which indexes to use
  1. Have you ever done partitioning? What is its use?
    1. Yes. It will help in searching data easily as it is stored in multiple partitions
  1. Why to go for composite partitioning?
    1. In case we have very large table and want faster access
  1. What is the difference between local and global index?
    1. Local index is the one which we can create for a single partition. Global index can be used for entire table at a time
  1. How you will re-organize the database?
    1. If 9i we can use exp/imp or move table. From 10g, we can use shrink compact command
  1. Why indexes will become unusable during a table move?
    1. Because the row id’s will change when we move a table and index is based on that row id
  1. Can we move a table to the same tablespace?
    1. Yes, but we need to have enough free space
  1. What should the frequency of performing re-organization in a database?
    1. If we are using ASSM, there is no need to re-org. Else, it is preferred to do every 6 months
  1. How can avoid fragmentation in a table?
    1. By using automatic segment space management
  1. What is the difference between row chaining and row migration?
    1. Row chaining means splitting same row across multiple blocks. Row migration means moving row to another block. In both the cases, I/O will be increased
  1. What is mean by row chaining and when it will occur? Solutions to avoid it?
    1. Splitting a single row into various blocks is called row chaining. It will occur when the data size is more than blocksize. We can use non-default block size tablespaces to avoid this
  1. What is the benefit of using index?
    1. It will speed up the process of searching for data
  1. How index works in the database?
    1. Index will be stored as tree format and when a comparison is required, it will start with root node and will spread to branch or leaf nodes if required
  1. You want to drop and re-create a parent table. How you will do that without affecting child tables?
    1. By disabling constraints first
  1. How you would go about increasing buffer cache hit ratio? Which view will help in this case?
    1. V$buffer_cache_advice
  1. What is the difference between locks and latches?
    1. Locks are to maintain read consistency and will be applied on tables. If the same kind is on instance memory structures, it is called latches
  1. How you will manage locks?
    1. We doesn’t need to do anything about locks, oracle will take care of them
  1. What are the 3 advisory statistics that you can collect?
    1. Memory, segment and hit ratio’s advisory
  1. What you will analyze out of TKPROF report?
    1. It will help us in knowing how much time and cost a query is using in the database during its execution
  2. What options we can use with tkprof report?
    1. So many, listed in google (generally we will not use them)
  1. What is the use of keep cache and recycle cache?
    1. Keep cache can be used to store a table which is being frequently used and if we want to not flush from the instance. Recycle is used for full scan tables to flush the data immediately
  1. How you will determine for which resources sessions are waiting for?
    1. V$session_waits or v$system_waits
  1. What is the difference between a statspack and AWR report?
    1. AWR is advanced version of statspack report in which statstics collection will be done automatically every one hour
  1. How you will analyze AWR report?
    1. Refer to the documents provided along with class notes
  1. What are top 5 timed events in AWR report?
    1. Those will represent for what database is waiting mostly for
  1. What is ADDM in 10g?
    1. It is a utility helps in providing some recommendations based on the statistics collected every hour
  2. What is the difference between ANALYZE and DBMS_STATS?
    1. DBMS_STATS will collect more information than ANALYZE command
  1. When to use more than one DBWR process?
    1. If we have datafiles spread across multiple disks and mostly it is used in OLTP environment
  1. When a database object becomes invalid?
    1. If the underlying base objects gets modified
  1. How to find the no of blocks a table is occupied?
    1. dba_tables
  1. In which situations you will increase SHARED_POOL_SIZE parameter?
    1. When we observe that a frequently executed statement is undergoing hard parsing every time
  1. Which parameter helps in giving complete output for TKPROF report?
    1. SQL_TRACE
  1. When looking at v$sysstat, you observed sort(disk) is high, is this good or bad? If bad, how you correct it?
    1. Its bad. We should not have more physical I/O
  1. What is the difference between instantaneous and cumulative buffer hit ratio?
    1. Reading data from instance is better than cumulative in which data should be read from disk which is costly
  1. If a tablespace is having zero pctincrease value, what could be the problem with SMON?
    1. SMON will not coalesce the tablespace
  1. How can you tell if a tablespace has excessive fragmentation?
    1. If a select aganist dba_free_space shows the count of a tablespace extents is greater than the count of its datafiles, then it is fragmented
  1. What can cause high value for recursive calls? How to fix it?
    1. Improper cursor usage which should be fixed by code writers
  2. How you will identify that a deadlock occurred and what immediate action you will take?
    1. Oracle itself will detect that and will resolve that by rolling back a transaction
  1. What is ORA-600 error and what causes it to occur? How to resolve it?
    1. It is an internal error which can be occurred due to various reasons. We need to contact oracle support or using ora-600 lookup tool in metalink
  1. How oracle will maintain read consistency always?
    1. By using locks on the tables
  1. How many types of optimizers are there? What is the difference between them?
    1. Cost based and rule based optimization. Cost will generated better execution plan than rule based
  1. How you will specify a query to use full table scan even though it is having index?
    1. Using hint
  1. How do you identify which sessions are locking the objects? What is the way to clear them?
    1. By combining V$locked_object and v$session
  1. If we observe latch hit ratio is high, is it a good or bad sign?
    1. It is a good sign as every statement need not to wait for the free buffer
  1. In your experience, what are the different wait events noticed and tell how you resolved them?
    1. Read the PT docs provided and explain any 2 or 3 of them with solutions


View More:


Subscribe To Newsletter
Be the first to get latest updates and exclusive content straight to your email inbox.
Stay Updated
Give it a try, you can unsubscribe anytime.

Check Also


ORACLE EXP/IMP & DATAPUMP INTERVIEW QUESTIONS How can i make export faster? By using DIRECT=Y …

Leave a Reply

Your email address will not be published. Required fields are marked *

Subscribe To Newsletter
Be the first to get latest updates and exclusive content straight to your email inbox.
Stay Updated
Give it a try, you can unsubscribe anytime.

Your information will never be shared
Sign-up for exclusive content. Be the first to hear about DBA Centre Blogs.