TOP oracle database administrator performance tuning interview questions

Senior  oracle DBA performance tuning interview questions

1.       How to take explain plan for table? Methods?

2.       How to check when the SQL executed in DB?

3.       How to check SQL is done hard or soft parse?

4.       How to compare the DB performance between two different timings?

5.       How to run the SQL Tunning advisor?

6.       How to Oracle base line for SQL?

7.       How to create SQL profile for sql addmprt?

8.       How to run addm and use?

9.       How to run awr and use?

10.   When to run ash and use for ashrpt.sql?

11.   Steps to run SQL Tunning advisor using OEM?

12.   Steps to check different hash value for SQL?

13.   How to check whether SQL is in cursor cache or not?

14.   Steps to run the segment advisor using OEM?

15.   Steps to run SQL Tunning advisor using OEM?

16.   Steps to create Oracle SQL profile using OEM?

17.   Steps to create ash report for particular OEM sql-id?

18.   Steps to compare two different timing awr using OEM?

19.   Steps to get historical SQL using OEM?

20.   How to force oracle to pick a index?

21.   Steps to check oracle execution plan using OEM?

22.   Steps to check how many times SQL executed and execution time using OEM?

23.   What is access advisor?

24.   What is adaptive cursor sharing?

Manual SQL Tunning:

1.       What are the list of areas to look into AWR for tunning?

2.       What are the areas we will look into ash report?

3.       When to collect stats for a table?

4.       When to rebuild index for a table?

5.       How do you say DB health is good?

6.       What are the areas you need to look when application says performance and what are the areas you will look into it?

7.       How to check top SQL from the database?

8.       How to check what are the objects accessed by a Query?

9.       Steps to run SQL tunning advisor manually?

10.   Steps to run SQL tunning using OEM?

11.   How to get bind values of SQL Query?

12.   What is SQL advisor?

13.   What is wait Events? How do you restore the issue?

14.   What is physical reads / logical reads? How do you restore the issue?

15.   What is high I/O? How do you restore the issue?

16.   When will you create baseline for SQL?

17.   Steps to force oracle use index?

18.   Cursor-sharing parameter effect on DB?

19.   What is the difference between explain plan & execution plan?

20.   How to change the SQL query to bad execution plan?

21.   Steps to restore optimizer statistics for a table?

22.   How to see stats retention periods and how to alter it?

23.   How to flush a particular object from shared pool?

24.   Steps to do export & import scheme stats?

25.   What is the stale stats? How to unlock the stats?

26.   What is the difference between lock and latch?

27.   How to check explain plan for already ran SQL?

28.   How to find out whether query run CBO & RBO?

29.   Parallel execution of SQL Query?

30.   Performance Tunning parameter?

31.   What is the areas to look into AWR?

32.   What are the areas to look into ASH?

33.   How many blocks used by an object?

34.   When to rebuild index? Run segment advisor, if we have defragmentation run it?

35.   When to collect stats for a table using SQL tunning advisor recommendation & ADDM?

36.   When the stats become state stats? 10% data change?

37.   How to find out current wait Events in DB?

38.  V$A-S-H, V$A-S-Wait, V$Session-wait

39.   How to fix sort-area-site? V$sysstat?

40.   Performance stated init parameters?

41.   How to check explain plan for already ran SQL?

42.   How to find whether query used CBO & RBO?

43.   Steps to check awr retention period? Snapshot time & how to change it?

44.   ASH retention period? Can we change?

45.   Compare AWR report by awrddrpt.sql?

46.   Are you aware of Awrgrpt.sql & AWR report?

47.   AWR vs ASH generation methods & types?

48.   Scripts to check wait Events? 

49.   What are the different methods of tracing session?