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?
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?
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?
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?
27. How do you get the information of specific session?
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
Autoconfig post 12C upgrade from 11g failed with "ORA-01804: failure to initialize timezone information"
Autoconfig post 12C upgrade from 11g failed with "ORA-01804: failure to initialize timezone information" Error DB Autoconfig ...
How to drop and recreate TEMP Tablespace in Oracle 9i/10g/11g 1. Create Temporary Tablespace Temp CREATE TEMPORARY TABLESPACE TEMP2 TEMP...
Error: " An error occurred while attempting to establish an Applications File Server connection. There may be a network configuration...
How to drop and recreate UNDO Tablespace in Oracle 9i/10g/11g ? Solution : 1. Determine the size of your undo tablespace SQL> selec...