4 When looking at the estat events report you see that you are getting busy buffer waits. Is this bad? How can you find what is causing it?
Ans: Buffer busy waits may indicate contention in redo, rollback, or data blocks. You need to check the v$waitstat view to see what areas are causing the problem.
The value of the “count” column tells where the problem is, the “class” column tells you with what. UNDO is rollback segments, DATA is database buffers.
1 How do you get the index of a table and on which column the index is?
Ans: dba_indexes and dba_ind_columns
2 How do you flush the shared pool?
Ans: alter system flush shared_pool
Who is responsible to update the indexes?
Ans: Oracle database software automatically maintains and uses the data indexes. When you make any changes to the table then oracle database software automatically reflects the changes to relevant indexes.
1 What is an execution plan?
Ans: Its a road map of how SQL is being executed by oracle DBA?
3 When should you increase copy latches? What parameters control copy latches?
Ans: When you get excessive contention for the copy latches as shown by the “redo copy” latch hit ratio.
You can increase copy latches via the initialization parameter LOG_SIMULTANEOUS_COPIES to twice the number of CPUs on your system.
2 How do you get the information about a specific session?
Ans: v$mystat
1 How do you delete statistics of an object?
Ans: table emp delete statistics
3 What is the general guideline for sizing db_block_size and db_multi_block_read for an application that does many full table scans?
Ans: Oracle almost always reads in 64k chunks. The two should have a product equal to 64 or a multiple of 64.
4 If a tablespace shows excessive fragmentation what are some methods to defragment the tablespace? (7.1,7.2 and 7.3 only)
Ans: In Oracle 7.0 to 7.2 The use of the ‘alter session set events ‘immediate trace name coalesce level ts#’;’ command is the easiest way to defragment contiguous free space fragmentation.
The ts# parameter corresponds to the ts# value found in the ts$ SYS table. In version 7.3 the ‘alter tablespace coalesce;’ is best.
If free space isn’t contiguous then exporting, drop and import of the tablespace contents may be the only way to reclaim non-contiguous free space.
3 Explain the use of TKPROF? What initialization parameter should be turned on to get full TKPROF output?
Ans: A tkprof tool is a tuning tool used to determine CPU and execution times for SQL statements.
You use it by first setting timed_statistics to true in the initialization file and then turning on tracing for either the entire database via the sql_trace parameter or for the session using the ALTER SESSION command.
Once the trace file is generated you run the tkprof tool against the trace file and then look at the output from the tkprof tool. This can also be used to generate explain plan output.
3 What is the fastest query method for a table?
Ans: Fetch by rowid
4 What can cause a high value for recursive calls? How can this be fixed?
Ans: A high value for recursive calls is caused by improper cursor usage, excessive dynamic space management actions, and or excessive statement re-parses.
You need to determine the cause and correct it By either relinking applications to hold cursors, using proper space management techniques (proper storage and sizing) or ensure repeat queries are placed in packages for proper reuse.