TUNING Q & A Page - 9

PERFORMANCE TUNING INTERVIEW QUESTIONS AND ANSWERS

                                                              

                                                             Page – 9

81..How do you flush  the shared pool?

alter system flush shared_pool

82..What is the meaning for system Bottleneck? 119

The term bottleneck comes from the shrinking in size of the neck of a bottle. This constriction causes a reduction in flow, limiting the amount of liquid coming out of the bottle. In a similar fashion, this term is used to describe something that is constricting system performance. Over the years, this term has grown to represent any sort of major limiting factor in a computer system.

A bottleneck can significantly reduce the performance of a system while leaving some resources such as the CPU—completely underutilized. It is the job of the performance engineer to reduce or eliminate bottlenecks

83..What is STATSPACK? and how does one can  use it?

Statspack is a set of performance monitoring and reporting utilities provided by Oracle from Oracle8i and above. Statspack provides improved BSTAT/ESTAT functionality, though the old BSTAT/ESTAT scripts are still available. For more information about STATSPACK, read the documentation in file $ORACLE_HOME/rdbms/admin/spdoc.txt.

Install Statspack:
cd $ORACLE_HOME/rdbms/admin
sqlplus “/ as sysdba” @spdrop.sql — Install Statspack –
sqlplus “/ as sysdba” @spcreate.sql– Enter tablespace names when prompted

Use Statspack:
sqlplus perfstat/perfstat
exec statspack.snap; — Take a performance snapshots
exec statspack.snap;

• Get a list of snapshots
select SNAP_ID, SNAP_TIME from STATS$SNAPSHOT;
@spreport.sql — Enter two snapshot id’s for difference report
Other Statspack Scripts:

• sppurge.sql – Purge a range of Snapshot Id’s between the specified begin and end Snap Id’s

• spauto.sql – Schedule a dbms_job to automate the collection of STATPACK statistics

• spcreate.sql – Installs the STATSPACK user, tables and package on a database (Run as SYS).

• spdrop.sql – Deinstall STATSPACK from database (Run as SYS)

• sppurge.sql – Delete a range of Snapshot Id’s from the database

• spreport.sql – Report on differences between values recorded in two snapshots

• sptrunc.sql – Truncates all data in Statspack tables

84..How we  can tuned the Private SQL and PL/SQL Areas?

A private SQL area is an area in memory that contains binding information and runtime buffers. Every session that issues SQL statements has a private SQL area; reducing these resources can be very effective when large numbers of users are involved. A private SQL area is further segmented into a persistent area and a runtime area

85..What are all the situation can cause performance loss?

An overloaded I/O system. This can cause the entire system to slow down while

waiting for disk requests to return.

Not enough memory. This can cause additional I/O usage by reducing the cache-hit

rate and by causing the operating system to swap or page.

Lack of Oracle resources. A shared pool that is too small can cause performance

problems, as described in Chapter 9, “Oracle Instance Tuning.”

A slow network. Network performance problems can reduce throughput and cause

user response times to rise.

Hardware. Problems may be caused by defective hardware or by an insufficient

amount of some resource such as memory or disk space.

Operating system. Problems may be caused by tuning issues as well as OS resources

used by Oracle.

Oracle. It is essential to properly tune Oracle for your configuration so that you can

realize optimal performance. A poorly tuned Oracle instance can drastically affect

performance

86..What is database replay?

Database replay functionality of oracle allows  you to capture workloads on a production system and replay from them exactly as they happened on a test system. This provides an accurate method to test the impact of a variety  of system changes including

A..Database upgrades

B..O/S upgrade and migration
C..Configuration changes, such as changes to initialization parameters or conversion from a single mode to a RAC environment.


87..How Do Discrete Transactions Work?     

With discrete transactions, all changes made to data are deferred until the transaction has been committed. Even though redo information is saved, it is not written to the redo log until the transaction has been committed. Until the commit, the redo information is stored in another area of memory

88..What will happen if you see the  optimizer_mode=choose?

If the statistics  of an object is available  then CBO used. If  RBO will be used

89..If  you see the multiple fragmentations in the  SYSTEM tablespace, what should
 you check?

Ensure  that users don't  have the SYSTEM table space as their TEMPORARY or DEFAULT tablespace  assignment by checking the DBA_USERS view

90..If a tablespace has a default pctincrease of zero what will this cause (in relationship to the smon process)?

The SMON process won't automatically coalesce its free space fragments