TUNING Q & A Page - 8

PERFORMANCE TUNING INTERVIEW QUESTIONS AND ANSWERS

                                                              

                                                             Page – 8

71..When an index should be  created?

An index segment is automatically allocated. This index segment

contains information that speeds access to data by determining the location of indexed data with as few I/Os as possible. Oracle indexes data by using an index structure known as a B*-Tree index.

72..What are some indications that you need to increase the SHARED_POOL_SIZE parameter?

Poor data dictionary or library cache hit ratios, getting error ORA-04031. Another indication is steadily decreasing performance with all other tuning parameters the same.

73..What is the trace file?

Oracle background process ( such as dbwr, lgwr, pmon, smon ) encounter an exception, they  will write a trace file.

A..These trace files are also recorded in the  alert.log
B..Trace file also created for diagnostic dump events.
C..An ORA-00600 error also  produces a trace file

74..How to remove the system bottleneck?

The goal is to remove the bottleneck as a limiting factor in the performance of your system. If disk I/O is causing a bottleneck, tuning the I/O subsystem by either redistributing your database or by adding additional capacity, should remove I/O as a limiting factor. Once this is done, I/O is no longer an issue in tuning your system

75..What is the use of  disk  array?

A disk array is collection of disk drives that are configured and act as one larger disk drive. Both hardware and software disk arrays are available today. Hardware disk arrays consist of a disk array controller and a set of disk drives (typically SCSI). Software disk arrays are made up of a software layer that lies between the file system and the device driver layers

76..What is the  best optimizer?

CBOThrough the use of SEPS you can store password credentials for connecting to database by using a client side oracle wallet, this wallet stores signing credentials. This feature introduced since oracle 10g. Thus the application code, scheduled job, scripts no longer needed embedded username and passwords. This reduces risk because the passwords are no longer exposed and password management policies are more easily enforced without changing application code whenever username and password change.

77..What is the secure external password(SEPS)?

Through  the use of SEPS you can stored password credentials for  connecting to the db by using a client side oracle wallet, this wallet stores singing  credential. This feature introduced embedded user name and passwords. This reduces risk because the passwords are no longer exposed and password management  policies are more easily enforced without changing application code whenever user name and password change.


78..What is the use and meaning of  tuning checkpoints?

Checkpoints occur in the Oracle database to ensure that all “dirty blocks” in the SGA are eventually written to disk. Because the DBWR works on a least recently used (LRU) algorithm, data that has been untouched in the SGA for the longest time is written out first. This has the side effect that often-used data blocks may never be written out to disk.

A checkpoint causes all the dirty blocks in the SGA to be written to disk. The following situations can cause a checkpoint.

 Every log switch causes a checkpoint. If a checkpoint is in progress, the log switch

overrides it and the checkpoint will restart.

The initialization parameter LOG_CHECKPOINT_INTERVAL can be set to force a checkpoint when a certain number of redo log blocks have been written relative to the last

checkpoint. This is useful when you are running with large log files and want several

checkpoints between log switches.

 The initialization parameter LOG_CHECKPOINT_TIMEOUT can be set to cause a checkpoint to occur a specific number of seconds since the beginning of the last checkpoint. This parameter can be used for large redo log files that have a desired checkpoint frequency.

 A checkpoint is forced at the beginning of a tablespace backup only on the affected

data files. This checkpoint also overrides any other checkpoint that is in progress.

 A checkpoint is forced on the data files of a tablespace that is brought offline.

 A checkpoint is forced on an instance shutdown. If the administrator shuts down the

instance with SHUTDOWN NORMAL or SHUTDOWN IMMEDIATE, a checkpoint occurs.

 A checkpoint can be caused manually by the administrator. This checkpoint overrides

any in-progress checkpoints

79..How do you get the  index of a table and on which column the index is?

dba_indexes and dba_ind_columns

80..What is the use of  Batch processing system? 

Batch processing systems are different from OLTP systems in that batch jobs are not run interactively. Batch jobs are submitted at some later time (perhaps much later) and run until they are done. The batch processing system typically contains many of the attributes of both the OLTP system and the decision support system, with some additional unique attributes. It is difficult to characterize a batch processing system accurately because the types of jobs run in batch tend to vary quite a lot