This skill “Oracle Performance Tuning ” is required for Oracle DBAs/Oracle Developers/ETL Developer and Report Developers (who work with oracle SQLs) or any one who work with Oracle databases.

Now a days Companies are realizing importance of Proactive Tuning over Reactive Tuning.
So, “Oracle Performance Tuning Interview Questions” are asked from 2+ years of experience Onwards.
Most of the time  “Oracle Performance Tuning Interview Questions” are “approach based” rather than “single word answers” .

 1. Tell me about various Oracle  Performance Tuning issues you have worked upon and how did you fix those?

2. If one Oracle SQL Query is executing in Toad/SQL Developer for more than an hour . How to know whether that Query  is hanging OR running ?

3. You are given a Oracle procedure (600 Lines of Code) to tune , How do you tune ? what is your approach ?

4.  In which phase of SDLC (Software Development Life Cycle ) does “Oracle SQL Tuning” activity will come?

5.  Who is responsible for  Oracle Performance Tuning in the project DBA/Developer /Data Architect?

6.  “Full Table Scan ” always indicates poor performance of the query . Is this statement TRUE /FALSE?

7.  Given an explain plan for Oracle Sql Query for tuning , What is your approach for improving its performance ?

8.  If two explain plans of one oracle query are given to you ,On what basis , you finalize the better explain plan?

9.  Which Oracle Tuning Tools  have you used?

10. Oracle sql Performance tuning Interview Questions :
Always Creating an index to the where condition columns in the query ,  it  improves Oracle  performance . do you agree with this statement? Give me the analysis for your answer.

11. Oracle sql performance tuning interview questions :

What are different ways of “analyzing table statistics”?

12. Oracle sql performance tuning interview questions #12:

What is difference between “Full Table Scan” and ” Index Scan”?

13. Oracle sql performance tuning interview questions :

What is the  difference between “Nested Loop Join”  and “Hash Join”?

14. What is the  difference between ” Hash Join”  and “Sort Merge Join”?

15. Explain Plan for Query is not giving potential problems for poor performance of the SQL , then which factors do you analyze?

16. What are the ways for oracle performance tuning of “INSERT” statement?

17. What are the ways for oracle performance tuning of “DELETE” statement?

18. pl sql performance tuning interview questions :

What are the steps for Performance Tuning of “Oracle SQL Script”?

19. pl sql performance tuning interview questions :

 How do you tune sql script /Procedure using TKprof?

20. pl sql performance tuning interview questions :

Tell us 5 hints used while tuning SQLs

 Oracle Performance Tuning Interview Questions

 (For Oracle DBAs)

The below  “Oracle Performance Tuning Interview Questions”  are  useful for

• • • Oracle DBAs

1. Database users complain database is slow , what is your approach to fix this performance issue?

2. Developer comes for your help saying one particular report is running slow , How do you address this issue?

3. One particular batch program yesterday it ran for longer time than usual. Today  batch run time is normal.
How do you find what might have caused performance issue on yesterday?

4. How do you find which sqls are consuming more resources ?

5. SQL Tuning Advisor gives some suggestions to create more indexes on a table , will you follow the suggestions, justify?

6. Tell us few wait events which you have come across wrt tuning issues?

7. How to you analyze/interpret AWR Report for Solving Tuning issues?

8. What is the difference between ASH and AWR Report?

9. How do you use Oracle Enterprise manager for solving Tuning Purpose?

10.What are Oracle “Hit Ratios” , in what way they are useful in Oracle  Performance Tuning database?

11.  What do you analyze in ADDM Report?

12. How do  you tune  Oracle Data Load  using SQL Loader?

13. How  do you tune  Oracle Data Pump Loads?

15. performance tuning interview questions for oracle dba

How do you tune  sorting  related  Queries?

16. performance tuning interview questions for oracle dba

How do you ensure sql reusability  w.r.t  oracle memory ?

17. performance tuning interview questions for oracle dba

How do you ensure memory allocated  is enough for application usage ?

18. performance tuning interview questions for oracle dba

When a oracle performance problem is reported , what are the steps you follow?

19. What are five init.ora parameters used for Oracle Performance Tuning?

20. How do you find locks held by  sessions which cause performance Tuning?