TUNING Q & A Page - 10

PERFORMANCE TUNING INTERVIEW QUESTIONS AND ANSWERS

                                                              

                                                             Page – 10

  

91..What is the use of  PIN and RELOADS?

PINS: The number of times the item in the library cache was executed.

RELOADS: The number of times the library cache misses and the library object must

be reloaded

.

To look at the cache hits based on the types of statements, you can use the following

statement:

SQL> SELECT namespace,

2 reloads “Cache Misses”,

3 pins “Executions”

4 FROM v$librarycache;

92..The  Query was fine last week and now it is slow? why?

The likely cause of this is because the execution plan has changed. Generate a current explain plan of the offending query and compare it to a previous one that was taken when the query was performing well. Usually the previous plan is not available.
Some factors that can cause a plan to change are:

Which tables are currently analyzed? 

Were they previously analyzed? (ie. Was the query using RBO and now CBO?)
Has OPTIMIZER_MODE been changed in INIT.ORA?
Has the DEGREE of parallelism been defined/changed on any table?
Have the tables been re-analyzed? Were the tables analyzed using estimate or compute? If estimate, what percentage was used?
Have the statistics changed?
Has the INIT.ORA parameter DB_FILE_MULTIBLOCK_READ_COUNT been changed?
Has the INIT.ORA parameter SORT_AREA_SIZE been changed?
 Have any other INIT.ORA parameters been changed?
What do you think the plan should be? Run the query with hints to see if this produces the required performance.

93..What are unused indexes? How to reuse unused indexes?

Most of the indexes are not  used by the queries which are written  based on the  

indexes. First query the data dictionary  to find  all indexes on that table.

select  index_name from  user_indexes where table_name ='emp';

Query of the V$object_usage view. The used column of this view contain YES if the  index was used, otherwise  it will contain NO. This will tell   you if the index  has every  been used since the MONITORING  USAGE command was last given.

94..What is the parallel query option? 

It is almost impossible to determine exactly where the problem is. If the bottleneck is internal to the operating system or to Oracle itself, there is really nothing you can do about it. The developers of Oracle—as well as developers of most operating systems
are constantly testing new releases to eliminate all the bottlenecks

95..What is the parallel query  option?

The Oracle Parallel Query option makes it possible for some Oracle functions to be processed by multiple server processes. These functions are queries, index creation, data loading, and recovery. In each of these functions, the general principle is the same: keep the processing going while Oracle is waiting for I/O.

96..What affects Oracle Server Performance?

Many factors contribute to the performance of Oracle on the server, the tuning of the

Oracle instance, the OS, the hardware itself, and the load generated by the users.

In tuning the system, you have some control over almost all these factors.

97..How Does a disk array work?

 A disk array is a set of disk drives that make up a larger logical disk, sometimes called a logical volume. The logical volume is made up of identical-sized pieces of the individual drives, called stripes. The data is said to be striped across the logical volume because the logical drive has pieces of all the individual drives striped within it

If you look at the logical volume, the physical drives seem to make stripes

98..Which columns should be Indexed?

 A. Choose columns frequently specified in WHERE clauses. Frequently accessed columns can most benefit from indexes.

 B. Don’t index columns that do not have many unique values. Columns in which a good

percentage of rows are duplicates cannot take advantage of indexing.

C. Columns that have unique values are excellent candidates for indexing. Oracle

automatically indexes columns that are unique or that are primary keys defined with

constraints. These columns are most effectively optimized by indexes.

D. Index columns that are foreign keys of referential integrity constraints in cases
 where large numbers of concurrent INSERT, UPDATE, and DELETE statements 
access both the parent and child tables. Such an index allows the child table to be updated without having to lock the parent table.

E. Columns that are commonly used to join tables are good candidates for indexing.

F. Frequently modified columns probably should not be index columns because of the

overhead involved in updating the index.

99..Where can one find I/O statistics per table?

The UTLESTAT report shows I/O per tablespace but one cannot see what tables in the tablespace has the most I/O. 

The $ORACLE_HOME/rdbms/admin/catio.sql script creates a sample_io procedure and table to gather the required information. After executing the procedure, one can do a simple SELECT * FROM io_per_object; to extract the required information.
For more details, look at the header comments in the $ORACLE_HOME/rdbms/admin/catio.sql script.

100..What is the benefits of the Oracle Parallel Server Option?

The Oracle Parallel Server option may be very beneficial to your operation in two ways:

A. Performance. If your system is a candidate for the Parallel Server option, you
 should see significant performance improvements.

B. Fault tolerance. Many OLTP installations demand no downtime. By using the

Oracle Parallel Server option, you can keep the system running, even if one 
computer fails.