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

3. 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

4. What is the purpose of collecting statistics?
   1. It will help optimizer to generate best execution plan

5. 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

6. 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

7. 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

8. Why to go for composite partitioning?
   1. In case we have very large table and want faster access

9. 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

10. 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

11. 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

12. Can we move a table to the same tablespace?
    1. Yes, but we need to have enough free space

13. 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

14. How can avoid fragmentation in a table?
    1. By using automatic segment space management

15. 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

16. 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

17. What is the benefit of using index?
    1. It will speed up the process of searching for data

18. 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

19. You want to drop and re-create a parent table. How you will do that without  affecting child tables?
    1. By disabling constraints first

20. How you would go about increasing buffer cache hit ratio? Which view will help  in this case?
    1. V$buffer_cache_advice

21. 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

22. How you will manage locks?
    1. We doesn’t need to do anything about locks, oracle will take care of them

23. What are the 3 advisory statistics that you can collect?
    1. Memory, segment and hit ratio’s advisory

24. 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
25. What options we can use with tkprof report?
    1. So many, listed in google (generally we will not use them)

26. 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

27. How you will determine for which resources sessions are waiting for?
    1. V$session_waits or v$system_waits

28. 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

29. How you will analyze AWR report?
    1. Refer to the documents provided along with class notes

30. What are top 5 timed events in AWR report?
    1. Those will represent for what database is waiting mostly for

31. What is ADDM in 10g?
    1. It is a utility helps in providing some recommendations based on the statistics collected every hour
32. What is the difference between ANALYZE and DBMS_STATS?
    1. DBMS_STATS will collect more information than ANALYZE command

33. 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

34. When a database object becomes invalid?
    1. If the underlying base objects gets modified

35. How to find the no of blocks a table is occupied?
    1. dba_tables

36. 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

37. Which parameter helps in giving complete output for TKPROF report?
    1. SQL_TRACE

38. 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

39. 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

40. If a tablespace is having zero pctincrease value, what could be the problem with  SMON?
    1. SMON will not coalesce the tablespace

41. 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

42. What can cause high value for recursive calls? How to fix it?
    1. Improper cursor usage which should be fixed by code writers
43. 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

44. 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

45. How oracle will maintain read consistency always?
    1. By using locks on the tables

46. 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

47. How you will specify a query to use full table scan even though it is having index?
    1. Using hint

48. 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

49. 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

50. 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