TUNING Q & A Page - 2

PERFORMANCE TUNING INTERVIEW QUESTIONS AND ANSWERS

                                                                   

                                                             Page – 2

11..Types  of wait  events in tuning?

 CPU time and direct path read

12..What Is Locking?

In multi user systems or servers, many users may update the same information at the same time.Locking allows this to occur and ensures that data you are currently modifying cannot be modified at the same time by a different user.

13..Why the drop table is not going to   recycle bin?

If you are  using SYS use to drop any table  then user's object will not go to the recycle bin as  there is  no recycle bin for SYSTEM tablespace, even we have already  SET recycle bin parameter TRUE

select  * from v$parameter where name = ' recyclebin';

show parameter recyclebin;

14..How do you collect statistics of a table?

Analyse table emp compute statistics  or analyse  table emp estimate statistics.

15..What  is the fastest query method for a table?

Fetch by rowid

16..If oracle  database is running  slow, how to solve the problem?

select username, default_tablespace from dba_users;

usually the default  tablespace of he user  should not be in SYSTEM tablespace. This might  cause  performance issue. Create  a separate  and alter / assign this user. Also must move all the objects from SYSTEM table space to this new tablespace which you created.

17..Why and when should one tune?

One of the biggest responsibilities of a DBA is to ensure that the Oracle database is tuned properly. The Oracle RDBMS is highly tunable and allows the database to be monitored and adjusted to increase its performance.

One should do performance tuning for the following reasons:
The speed of computing might be wasting valuable human time (users waiting for response);

Enable your system to keep-up with the speed business is conducted; and  Optimize hardware usage to save money (companies are spending millions on hardware).

 18..What is the use of Benchmarks?

Standardized benchmarks are a good way to judge how well a system is performing and to compare different hardware platforms. If they are available to you, standardized benchmarks are also a good way to analyse the performance of your particular platform

19..What tuning  indicators one can use?

The following high-level tuning indicators can be used to establish if a database is performing optimally or not:

A.Buffer Cache Hit Ratio
Formula: Hit Ratio = (Logical Reads – Physical Reads) / Logical Reads
Action: Increase DB_CACHE_SIZE (DB_BLOCK_BUFFERS prior to 9i) to increase hit ratio

B.. Library Cache Hit Ratio
Action: Increase the SHARED_POOL_SIZE to increase hit ratio

20..How to run the ANALYSE command

The ANALYSE command is determined by the type of statistics or analysis you want

to perform. The ANALYSE command can be used in several different modes. The mode you choose depends on the data you want to gather as well as on the configuration of your system.