TUNING Q & A Page - 5

PERFORMANCE TUNING INTERVIEW QUESTIONS AND ANSWERS

                                                                    

                                                             Page – 5

41.. Which init parameter is used to make use of  optimizer?

optimizer_mode=rule---RBO cost---CBO choose---first CBO otherwise RBO

42..How do you set up tablespaces during an Oracle installation?

You should always attempt to use the Oracle Flexible Architecture standard or another partitioning scheme to ensure proper separation of SYSTEM, ROLLBACK, REDO LOG, DATA, TEMPORARY and INDEX segments.

43..How determine the EXPLAIN PLAN?        

            

The  best way to determine whether your SQL statements are properly optimized is by using the Oracle SQL Trace facility and the EXPLAIN PLAN command. You can use the SQL Trace facility and the Oracle program TKPROF, which is used to translate trace files, to trace production SQL statements, and gather statistics about those statements.

Here is an example of a completed command:

SQL> EXPLAIN PLAN

2 SET STATEMENT_ID = ‘Testing EXPLAIN PLAN’

3 INTO plan_table

4 FOR

5 SELECT

6 SUBSTR(dogname,1,20) “Dog Name”,

7 SUBSTR(breed_name,1,20) “Breed”,

8 SUBSTR(owner,1,20) “Owner”

9 FROM

10 dogs, breeds

11 WHERE

12 dogs.breed = breeds.breed

13 ORDER BY

14 dogs.breed;

Explained.

44..How do you know which session  is running log jobs?

v$session_longops

45..What database aspects should be monitored?   

One should implement a monitoring system to constantly monitor the following aspects of a database. Writing custom scripts, implementing Oracle’s Enterprise Manager, or buying a third-party monitoring product can achieve this. If an alarm is triggered, the system should automatically notify the DBA (e-mail, page, etc.) to take appropriate action.

Infrastructure availability:

• Is the database up and responding to requests
• Are the listeners up and responding to requests
• Are the Oracle Names and LDAP Servers up and responding to requests
• Are the Web Listeners up and responding to requests

Things that can cause service outages:

• Is the archive log destination filling up?
• Objects getting close to their max extents
• Tablespaces running low on free space/ Objects what would not be able to extend
• User and process limits reached

46..How do you delete  statistics of an object?

Analyse  table emp delete  statistics

47..How do you see the statistics  of a table?

select num_rows, blocks, empty_blocs from dba_tables where tab_name='emp';

48..Explain Oracle Parallel Server Option?

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

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

B. Fault tolerance. By using the Oracle Parallel Server option, you can keep the
 system running even if a computer fails. DSS systems do not typically have the 
same uptime  requirements as OLTP systems.

49..What Is a Client Machine?

To define what a client is, it may help to look at the evolution of computing over

 the last few years. By looking at where we came from and where we are today,

it may be easier to see where we will be going in the future.

50..What all are the types of  optimizer?

A..RBO ( Rule Based Optimizer )
B..CBO ( Cost Based Optimizer )