PT Basics

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

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