TUNING Q & A Page - 6

PERFORMANCE TUNING INTERVIEW QUESTIONS AND ANSWERS

                                                              

                                                             Page – 6

51..What if redo log space wait time is high? How can you fix this?

Since wait time is zero, no. If wait time was high it might indicate a need for more or larger redo logs

52..How can perform the O/S resources?

The operating system is typically tuned to allow Oracle to allocate needed resources. Many operating systems have limitations on how much memory or CPU time a 

single process or user can consume. Because Oracle is not a regular user it is a

 server process these resources must be increased to allow Oracle to consume vast system resources.

53..What is the  difference between Redo, Rollback and Undo?

Redo

Every oracle  database has a set of ( two or more ) redo log files. The redo log records all changes made to the data, including  both  committed, and uncommitted changes. In addition to the online redo logs oracle also stores archive redo logs. All redo logs are used in recovery.

Rollback

Rollback segments  store the  data as it was before changes were made. This is contrast to the redo log which is a record of  insert / update / delete.

Undo

Rollback segments are really one in the same undo data is  stored in the undo tablespace. Undo is  helpful in building a read consistent view or data.

54..What is the  materialized views?

            

A materialized view is a stored summary, containing  pre computed results. Materialized  views allow for significantly faster data  warehouse query processing. The create materialized view statement  is used to create a  materialized view. This statement includes  a sub query, typically  a join or a data aggregation ( GROUP BY ), 
the  result of which comprise the materialized view

55..Temp tablespace is 100% FULL  and there is no  space available to add the datafile to increase  temp tablespace. What can you do in that case to free up TEMP  tablespace?

Try to close  some of the idle sessions connected to the database will help you to free some TEMP space. Otherwise you can also  use ‘Alter tablespace PTCINCREASE 1’ followed by ‘Alter tablespace PCTINCREASE 0’

56..When looking at the stat events report you see  that you are  getting busy buffer waits, is this bad? How can you find  what is causing it?

Buffer busy waits  may indicates contention in redo, rollback or data blocks. You  need to check the v$waitstat view  to see  what areas are causing the problem. The value of the “count” column tells where the problem is, the “class” column or not. UNDO is rollback  segments, DATA is data base buffers.

57..What is the  purpose of  TPC-E?

The TPC-E benchmark is designed to quantify the ability of a system to support the computing environment appropriate to large business “enterprises.” These environments typically support workload demands that exceed the demands imposed by other TPC benchmarks

58..What is the function  of  Tuning the Server Operating System

The server operating system functions as a host for the RDBMS. The OS performs 

such tasks as providing access to data stored on disk, either through the file system or directly through the raw interfaces (available on some OSes). The OS also provides the network interface that SQL*Net uses to communicate to other machines. Other functions provided by the operating system include system performance monitoring and backup and recovery functions

59..What is the  use of memory tuning?

In oracle instance, data is stored in two places, in memory and  on disk. Memory has by far the  best performance but also  has the highest cost. Disk on the other hand, can  store vast amounts of data very cost effective but has very slow performance relative to memory.

The memory is the better performer, it is desirable to use memory to access data whenever possible. But because of the vast amounts of data usually accessed and the number of users who need this data, there is a lot  of contention on this resource. To make most effective use of memory, you must achieve a balance between the memory used for oracle caching and the memory needed by the users.

Tuning memory in the oracle instance involves tuning several major areas

A..The OS

B..Private SQL and PL/SQL

C..The shared pool

D..The redo log buffer

E.The buffer cache

60..How do you tune a query using explain plan?

A..The explain plan gives a detailed output on query costs for each sub query.

B..The query cost is directly proportional to the  query execution time.

C..The explain plan shows the problem query / sub-query, the way data is fetched in

     this query