TUNING Q & A Page - 1

  PERFORMANCE TUNING INTERVIEW QUESTIONS AND ANSWERS 

PAGE - 1

1..What is performance tuning?

Performance tuning is the improvement of system performance. Performance improvement by its nature, is iterative.  The oracle  server is a sophisticated  and highly  tunable  performance. By tuning  your system , you can tailor its performance  to best meet your needs.

STEP A: Tune the business rules

STEP B: Tune the data design

STEP C: Tune the  application design

STEP D: Tune  the logical   structure of the database

STEP E: Tune  the database operation

STEP F: Tune the  access path

STEP G: Tune the  memory allocation

STEP H: Tune the I/O and physical structure

STEP I :  Tune the resource contention

STEP J:  Tune the  underlying platform(s)

2..Define well tuned sql statements?

Well-tuned SQL statements are

Makes efficient use of RDBMS features. The well-tuned SQL statement uses

indexes or hashing as available. If possible, the application should also take advantage of features such as array processing and discrete transactions.

                                      

A. Uses PL/SQL to improve performance. PL/SQL allows blocks of statements to be sent to the Oracle server at one time. If you don’t use PL/SQL, you must send each statement individually.

B  Uses stored procedures. By using stored procedures, you reduce the amount of data that must be sent across the network and increase the chance that the statement may already be parsed in the shared SQL area.

C. Uses packages. Packages increase performance because the entire package is loaded when the package is called for the first time.

D. Uses cached sequences to generate primary key values. This improves the performance of key generation and makes it unnecessary to generate the key in the application.

E. Makes efficient use of space. The SQL statement uses the VARCHAR2 data type instead of CHAR, when possible, to avoid unnecessary blank padding.

F Uses hints where necessary. A well-tuned SQL statement uses hints where appropriate to allow the programmer’s understanding of the SQL statement and the database design to override Oracle’s choice of optimization method.

3..Explain the system tuning?

The system is tuning  is also a major factor in the overall performance. For the most part, tuning the system involves resource allocation. By adding or removing a resource in the RDBMS or the OS or even the hardware performance can be drastically affected

4..Explain the types of tunings?

A..CPU, B. Memory tuning, C. I/O tuning, D. Application tuning, E. Database tuning

5..What tools/utilities does Oracle provide to assist with performance tuning?

Oracle provide the following tools/ utilities to assist with performance monitoring and tuning:
A..TKProf
B..UTLBSTAT.SQL and UTLESTAT.SQL – Begin and end stats monitoring
C..Statspack
D..Oracle Enterprise Manager – Tuning Pack

6..SQL tuning  belongs to how many  categories?

A..Tuning an existing application. This approach involves less flexibility in terms of

changing the structure of the application and the database, but may provide performance

improvements anyway.

B.. Designing a new application. With a new application, you have the flexibility to

design the application and perhaps even the database itself. With this approach, you

can take advantage of indexes, clustering, and hashing.

7.. How do you increase  the db cache?

alter  table emp table

8.. Can we drop hash partition  in oracle table? If yes then how?If no than  why?

NO

9.. What mainly  database  tuning contains?

A.. Hit ration, B.. Wait events

10..What are all the steps involve in the operating system  tuning?

The first step in tuning memory for the Oracle instance is to make sure that there are sufficient resources available in the operating system. You cannot allocate memory to Oracle that doesn’t exist. Giving Oracle additional memory at the expense of causing paging or swapping is ineffective and hurts performance

The operating system must provide for the following

Enough memory for the SGA to fit into main memory. In most operating systems,

this involves allocating a special type of memory structure called shared memory.

Shared memory is provided by the operating system to allow multiple processes to

access the same memory through special system calls. 

In many operating systems, the shared memory is locked into place and cannot be swapped or paged.

 Enough memory for the user processes to fit into main memory. Remember that

each shadow process, or dispatcher, also consumes memory. For user processes, the

amount of memory consumed depends on the number of users connected.

Avoid paging and swapping. Although it is not uncommon for some paging or

swapping to occur, if it occurs frequently, you should take steps to reduce it.

 Enough memory for operating system activities. Remember that other OS activities

may become active at various times and therefore allocate memory.

 Enough memory to accommodate Oracle operations such as archiving, loading,

online backup, and so on