Oracle DBA -Performance Tuning Questions

Oracle DBA - Performance Tuning Interview Questions




1. What is Performance Tuning?

Ans: Making optimal use of system using existing resources called performace tuning.

2. Types of Tunings?

Ans: 1. CPU Tuning 2. Memory Tuning 3. IO Tuning 4. Application Tuning 5. Databse Tuning

3. What Mailny Database Tuning contains?

Ans: 1. Hit Ratios 2. Wait Events

3. What is an optimizer?

Ans: Optimizer is a mechanizm which will make the execution plan of an sql statement

4. Types of Optimizers?

Ans: 1. RBO(Rule Based Optimizer) 2. CBO(Cost Based Optimzer)

5. Which init parameter is used to make use of Optimizer?

Ans: optimizer_mode= rule----RBO cost---CBO choose--------First CBO otherwiser RBO

6. Which optimizer is the best one?

Ans: CBO

7. What are the pre requsited to make use of Optimizer?

Ans: 1. Set the optimizer mode 2. Collect the statistics of an object

8. How do you collect statistics of a table?

Ans: analyze table emp compute statistics or analyze table emp estimate statistics

9. What is the diff between compute and estimate?

Ans: If you use compute, The FTS will happen, if you use estimate just 10% of the table will be read

10. What wll happen if you set the optimizer_mode=choose?Ans: If the statistics of an object is available then CBO used. if not RBO will be used

11. Data Dictionay follows which optimzer mode?

Ans: RBO

12. How do you delete statistics of an object?

Ans: analyze table emp delete statistics

13. How do you collect statistics of a user/schema?

Ans: exec dbms_stats.gather_schema_stats(scott)

14. How do you see the statistics of a table?

Ans: select num_rows,blocks,empty_blocks from dba_tables where tab_name='emp'

15. What are chained rows?

Ans: These are rows, it spans in multiple blocks

16. How do you collect statistics of a user in Oracle Apps?

Ans: fnd_stats package

17. How do you create a execution plan and how do you see?Ans: 1. @?/rdbms/admin/utlxplan.sql --------- it creates a plan_table 2. explain set statement_id='1' for select * from emp; 3. @?/rdbms/admin/utlxpls.sql -------------it display the plan

18. How do you know what sql is currently being used by the session?

Ans: by goind v$sql and v$sql_area

19. What is a execution plan?

Ans: Its a road map how sql is being executed by oracle db?

20. How do you get the index of a table and on which column the index is?

Ans: dba_indexes and dba_ind_columns

21. Which init paramter you have to set to by pass parsing?

Ans: cursor_sharing=force

22. How do you know which session is running long jobs?

Ans: by going v$session_longops

23. How do you flush the shared pool?

Ans: alter system flush shared_pool

24. How do you get the info about FTS?

Ans: using v$sysstat

25. How do you increase the db cache?

Ans: alter table emp cache

26. Where do you get the info of library cache?

Ans: v$librarycache

27. How do you get the information of specific session?

Ans: v$mystat

28. How do you see the trace files?

Ans: using tkprof --- usage: tkprof allllle.trc llkld.txt

29. Types of hits?

Ans: Buffer hit and library hit

30. Types of wait events?

Ans: cpu time and direct path read