TUNING Q & A Page - 10

PERFORMANCE TUNING INTERVIEW QUESTIONS AND ANSWERS

                                                              

                                                             Page – 10

  

91..What is the use of  PIN and RELOADS?

PINS: The number of times the item in the library cache was executed.

RELOADS: The number of times the library cache misses and the library object must

be reloaded

.

To look at the cache hits based on the types of statements, you can use the following

statement:

SQL> SELECT namespace,

2 reloads “Cache Misses”,

3 pins “Executions”

4 FROM v$librarycache;

92..The  Query was fine last week and now it is slow? why?

The likely cause of this is because the execution plan has changed. Generate a current explain plan of the offending query and compare it to a previous one that was taken when the query was performing well. Usually the previous plan is not available.
Some factors that can cause a plan to change are:

Which tables are currently analyzed? 

Were they previously analyzed? (ie. Was the query using RBO and now CBO?)
Has OPTIMIZER_MODE been changed in INIT.ORA?
Has the DEGREE of parallelism been defined/changed on any table?
Have the tables been re-analyzed? Were the tables analyzed using estimate or compute? If estimate, what percentage was used?
Have the statistics changed?
Has the INIT.ORA parameter DB_FILE_MULTIBLOCK_READ_COUNT been changed?
Has the INIT.ORA parameter SORT_AREA_SIZE been changed?
 Have any other INIT.ORA parameters been changed?
What do you think the plan should be? Run the query with hints to see if this produces the required performance.

93..What are unused indexes? How to reuse unused indexes?

Most of the indexes are not  used by the queries which are written  based on the  

indexes. First query the data dictionary  to find  all indexes on that table.

select  index_name from  user_indexes where table_name ='emp';

Query of the V$object_usage view. The used column of this view contain YES if the  index was used, otherwise  it will contain NO. This will tell   you if the index  has every  been used since the MONITORING  USAGE command was last given.

94..What is the parallel query option? 

It is almost impossible to determine exactly where the problem is. If the bottleneck is internal to the operating system or to Oracle itself, there is really nothing you can do about it. The developers of Oracle—as well as developers of most operating systems
are constantly testing new releases to eliminate all the bottlenecks

95..What is the parallel query  option?

The Oracle Parallel Query option makes it possible for some Oracle functions to be processed by multiple server processes. These functions are queries, index creation, data loading, and recovery. In each of these functions, the general principle is the same: keep the processing going while Oracle is waiting for I/O.

96..What affects Oracle Server Performance?

Many factors contribute to the performance of Oracle on the server, the tuning of the

Oracle instance, the OS, the hardware itself, and the load generated by the users.

In tuning the system, you have some control over almost all these factors.

97..How Does a disk array work?

 A disk array is a set of disk drives that make up a larger logical disk, sometimes called a logical volume. The logical volume is made up of identical-sized pieces of the individual drives, called stripes. The data is said to be striped across the logical volume because the logical drive has pieces of all the individual drives striped within it

If you look at the logical volume, the physical drives seem to make stripes

98..Which columns should be Indexed?

 A. Choose columns frequently specified in WHERE clauses. Frequently accessed columns can most benefit from indexes.

 B. Don’t index columns that do not have many unique values. Columns in which a good

percentage of rows are duplicates cannot take advantage of indexing.

C. Columns that have unique values are excellent candidates for indexing. Oracle

automatically indexes columns that are unique or that are primary keys defined with

constraints. These columns are most effectively optimized by indexes.

D. Index columns that are foreign keys of referential integrity constraints in cases
 where large numbers of concurrent INSERT, UPDATE, and DELETE statements 
access both the parent and child tables. Such an index allows the child table to be updated without having to lock the parent table.

E. Columns that are commonly used to join tables are good candidates for indexing.

F. Frequently modified columns probably should not be index columns because of the

overhead involved in updating the index.

99..Where can one find I/O statistics per table?

The UTLESTAT report shows I/O per tablespace but one cannot see what tables in the tablespace has the most I/O. 

The $ORACLE_HOME/rdbms/admin/catio.sql script creates a sample_io procedure and table to gather the required information. After executing the procedure, one can do a simple SELECT * FROM io_per_object; to extract the required information.
For more details, look at the header comments in the $ORACLE_HOME/rdbms/admin/catio.sql script.

100..What is the benefits of the Oracle Parallel Server Option?

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

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

B. Fault tolerance. Many OLTP installations demand no downtime. By using the

Oracle Parallel Server option, you can keep the system running, even if one 
computer fails.

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

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 )