TUNING Q & A Page - 3

PERFORMANCE  TUNING INTERVIEW QUESTIONS AND ANSWERS

                                                                   

                                                    Page – 3

 21..What is the  types of hits? 

Buffer hit and library hit

22..Creating index  in  online is possible?

YES. You can create and rebuild indexes online. This enables you to update base tables at the same time you are building or rebuilding indexes on that table. You can perform DML operations while the index building is taking place, but DDL operations are not allowed. Parallel execution is not supported when creating or rebuilding an index online.
CREATE INDEX emp_name ON emp (mgr, emp1, emp2, emp3) ONLINE;

23.. Explain row chaining, how does it happen? How can you reduce it? How do you correct it?

Row chaining occurs when a VARCHAR2 value is updated and the length of the new value is longer than the old value and won't fit in the remaining block space. This results in the row chaining to another block. It can be reduced by setting the storage parameters on the table to appropriate values. It can be corrected by export and import of the effected table
To get an idea of the total number of cache misses

24..What are  the values of optimizer_mode init parameters and their meaning?

Optimizer_mode = choose

25..What are  the difference between  lock and latch?

Locks protect data during  transaction another process, latching, controls access to physical pages. Latches are very lightweight, short-term synchronization objects protecting actions that do not need to be locked for the life of a transaction

SQL> SELECT SUM(reloads) “Cache Misses”,

2 SUM(pins) “Executions”,

3 100 * ( SUM(reloads) / SUM(pins) ) “Cache Miss Percent”

4 FROM v$librarycache;

26..What are  the difference between  lock and latch?

Locks protect data during  transaction another process, latching, controls access to physical pages. Latches are very lightweight, short-term synchronization objects protecting actions that do not need to be locked for the life of a transaction

27.. What is the use of library cache?

The library cache contains the shared SQL ans PL/SQL areas. Performance can be improved by both  the cache-hit rate in the library  cache and by speeding access to the library. Cache by holding infrequently used SQL statements in catch  longer

28..What is the difference between  DB file Sequential  read and DB file scattered read?

A..DB file sequential read, reads block into contiguous memory and DB file scattered read has to do with full table scan

B..The DB file sequential read, reads block into  contiguous memory  and DB file scattered read gets from  multiple block and scattered them into  buffer cache.

29..What are the init parameters related to performance / optimizer?

optimizer_mode = choose
optimizer_index_caching = 90
optimizer_index_cost_adj = 25
optimizer_max_permutations = 100
optimizer_use_sql_plan_baselines=true
optimizer_capture_sql_plan_baselines=true
optimizer_use_pending_statistics = true;
optimizer_use_invisible_indexes=true
optimizer_connect_by_cost_based=false
optimizer_compute_index_stats= true;

30..How to identify badly formed SQL statements?

Badly tuned SQL statements are

A  Indexes are not used. If a query is not properly formed, you may bypass an index 
that could be used to reduce I/O and CPU processing

B Hashing is bypassed. If a hashed cluster is improperly accessed, performance could be severely degraded.

C Unnecessary table scans are performed. If the SQL statement is improperly formed,

you may be doing unnecessary table scans.

D  Unnecessary amounts of data are returned. This is an undue burden not only
on the network but on the application as well.