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

TUNING Q & A Page - 8

PERFORMANCE TUNING INTERVIEW QUESTIONS AND ANSWERS

                                                              

                                                             Page – 8

71..When an index should be  created?

An index segment is automatically allocated. This index segment

contains information that speeds access to data by determining the location of indexed data with as few I/Os as possible. Oracle indexes data by using an index structure known as a B*-Tree index.

72..What are some indications that you need to increase the SHARED_POOL_SIZE parameter?

Poor data dictionary or library cache hit ratios, getting error ORA-04031. Another indication is steadily decreasing performance with all other tuning parameters the same.

73..What is the trace file?

Oracle background process ( such as dbwr, lgwr, pmon, smon ) encounter an exception, they  will write a trace file.

A..These trace files are also recorded in the  alert.log
B..Trace file also created for diagnostic dump events.
C..An ORA-00600 error also  produces a trace file

74..How to remove the system bottleneck?

The goal is to remove the bottleneck as a limiting factor in the performance of your system. If disk I/O is causing a bottleneck, tuning the I/O subsystem by either redistributing your database or by adding additional capacity, should remove I/O as a limiting factor. Once this is done, I/O is no longer an issue in tuning your system

75..What is the use of  disk  array?

A disk array is collection of disk drives that are configured and act as one larger disk drive. Both hardware and software disk arrays are available today. Hardware disk arrays consist of a disk array controller and a set of disk drives (typically SCSI). Software disk arrays are made up of a software layer that lies between the file system and the device driver layers

76..What is the  best optimizer?

CBOThrough the use of SEPS you can store password credentials for connecting to database by using a client side oracle wallet, this wallet stores signing credentials. This feature introduced since oracle 10g. Thus the application code, scheduled job, scripts no longer needed embedded username and passwords. This reduces risk because the passwords are no longer exposed and password management policies are more easily enforced without changing application code whenever username and password change.

77..What is the secure external password(SEPS)?

Through  the use of SEPS you can stored password credentials for  connecting to the db by using a client side oracle wallet, this wallet stores singing  credential. This feature introduced embedded user name and passwords. This reduces risk because the passwords are no longer exposed and password management  policies are more easily enforced without changing application code whenever user name and password change.


78..What is the use and meaning of  tuning checkpoints?

Checkpoints occur in the Oracle database to ensure that all “dirty blocks” in the SGA are eventually written to disk. Because the DBWR works on a least recently used (LRU) algorithm, data that has been untouched in the SGA for the longest time is written out first. This has the side effect that often-used data blocks may never be written out to disk.

A checkpoint causes all the dirty blocks in the SGA to be written to disk. The following situations can cause a checkpoint.

 Every log switch causes a checkpoint. If a checkpoint is in progress, the log switch

overrides it and the checkpoint will restart.

The initialization parameter LOG_CHECKPOINT_INTERVAL can be set to force a checkpoint when a certain number of redo log blocks have been written relative to the last

checkpoint. This is useful when you are running with large log files and want several

checkpoints between log switches.

 The initialization parameter LOG_CHECKPOINT_TIMEOUT can be set to cause a checkpoint to occur a specific number of seconds since the beginning of the last checkpoint. This parameter can be used for large redo log files that have a desired checkpoint frequency.

 A checkpoint is forced at the beginning of a tablespace backup only on the affected

data files. This checkpoint also overrides any other checkpoint that is in progress.

 A checkpoint is forced on the data files of a tablespace that is brought offline.

 A checkpoint is forced on an instance shutdown. If the administrator shuts down the

instance with SHUTDOWN NORMAL or SHUTDOWN IMMEDIATE, a checkpoint occurs.

 A checkpoint can be caused manually by the administrator. This checkpoint overrides

any in-progress checkpoints

79..How do you get the  index of a table and on which column the index is?

dba_indexes and dba_ind_columns

80..What is the use of  Batch processing system? 

Batch processing systems are different from OLTP systems in that batch jobs are not run interactively. Batch jobs are submitted at some later time (perhaps much later) and run until they are done. The batch processing system typically contains many of the attributes of both the OLTP system and the decision support system, with some additional unique attributes. It is difficult to characterize a batch processing system accurately because the types of jobs run in batch tend to vary quite a lot

TUNING Q & A Page - 7

PERFORMANCE TUNING INTERVIEW QUESTIONS AND ANSWERS

                                                              

                                                             Page – 7

61..Data dictionary follows which  optimizer mode?

RBO

62..When should one rebuild an index?

You can run the ‘ANALYZE INDEX VALIDATE STRUCTURE’ command on the affected indexes – each invocation of this command creates a single row in the INDEX_STATS view. This row is overwritten by the next ANALYZE INDEX command, so copy the contents of the view into a local table after each ANALYZE. The ‘badness’ of the index can then be judged by the ratio of ‘DEL_LF_ROWS’ to ‘LF_ROWS’.

63..How do you collect statistics of a user / schema?

exec dbms_stats.gather_schema_statas(scott)

64..Which Tables Should Be Indexed?

                                                                       

Use the following guidelines to decide which tables to index:

A. Index tables when queries select only a small number of rows. Queries that select a

large number of rows defeat the purpose of the index. Index the table when queries

access less than 5 percent of the rows in the table

B. Don’t index tables that are frequently updated. Update, insert, and deletes on indexed

tables incur extra overhead. Base your decision about whether or not to index a table

on the number of updates, inserts, and deletes relative to the total number of queries

to the table.

C.  Index tables that don’t have duplicate values on the columns usually selected in WHERE clauses. Tables for which the selection is based on TRUE/FALSE values are not good candidates for an index.

D. Index tables that are queried with relatively simple WHERE clauses. Complex WHERE

clauses may not be able to take advantage of indexes. You can solve this shortcoming

by creating a complex index, by simplifying the SQL statement, or by using a hint.

65..Why  we need the  CASCADE option with DROP USER command whenever dropping  user and why DROP USER commands fails when we don’t  use it?

If a user having any object then ‘YES’ in that case you are not able to drop that user without using CASCADE option. The DROP USER with CASCADE option command drops user along with its all associated objects. Remember it is a DDL command after the execution of this command roll-back cannot be performed.

66..What and  how to  test in the O/S?

The OS is tested with the RDBMS. If you have to change some specific OS

parameter to increase a limitation, you usually don’t have to retest the performance. Any

limit  change is usually associated with an RDBMS change, and the two can be tested together

67..How can one optimize %XYZ% queries?     

It is possible to improve %XYZ% queries by forcing the optimizer to scan all the entries from the index instead of the table. This can be done by specifying hints. If the index is physically smaller than the table (which is usually the case) it will take less time to scan the entire index than to scan the entire table.

68..What is the difference between  compute and  estimate?

If you  use compute, the FTS(FULL TABLE SCAN) will happen, if  you  use estimate  just 10% of the  table  will be read.

69..How to analyse the explain plan?

The Oracle EXPLAIN PLAN command displays the execution plan chosen by the Oracle optimizer for SELECT, UPDATE, INSERT, and DELETE statements. By examining the execution plan, you can see exactly how Oracle executes your SQL statement. The execution plan can help you determine whether you have written the an efficient SQL statement or whether changes can be made to optimize the statement.

To execute an EXPLAIN PLAN statement, you must first create a table with the name plan_table and with the specified plan_table format. The table format and the SQL statement required to create the table are included in Oracle in the SQL script UTXPLAN.SQL. Once this table is created, you can execute the EXPLAIN PLAN statement by issuing these SQL statements followed

by your SQL statements:

EXPLAIN PLAN

SET STATEMENT_ID = ‘NAME’

FOR

In this syntax, NAME specifies a label for the statement in the plan_table table.

70..Which  factors are to be  considered  for creating  index on table? how to select column for index?

Creation of index on table depends on size of table, volume of data. If size of table is large and we need only few data for selecting or in report then we need to create index. There are some basic reason of selecting column for indexing like cardinality and frequent usage in where condition of select query. Business rule is also forcing to create index like primary key, because configuring primary key or unique key automatically create unique index. 

It is important to note that creation of so many indexes would affect the performance of DML on table because in single transaction should need to perform on various index segments and table simultaneously. 

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