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 )

TUNING Q & A Page - 4

PERFORMANCE TUNING INTERVIEW QUESTIONS AND ANSWERS

                                                                    

                                                             Page – 4

  

31..How one can to the hardware tuning?

Hardware cannot be tuned in the same manner as the RDBMS and the OS. After analysing the system, you may discover that it is necessary to add hardware resources such as more disks, disk controllers, RAM, cache, CPUs, and so on. Adding hardware resources is all part of the tuning  and requires the same analysis and testing as RDBMS and OS tuning.

32..How do  you get the info about FTS?

using v$sysstat

33..Why library cache pins are needed?

Library cache pins deals with current  of dependent objects. Example, an underlying objects  should  not be modified when a session is executing or accessing a dependent objects. So  before parse locks on a library  cache  object can be broken, library cache pins  must  be acquired  in an  exclusive mode  and then only library cache objects can be   dropped. If  session in executing a stored object, then the  library cache pins will not be  available and there will be  waits for  library cache pins. Typically this happens for a long running statement executing a stored objects

34..Where do you get the info about library cache?

v$librarycache


35..Where should the tuning effort be directed?

Consider the following areas for tuning. The order in which steps are listed needs to be maintained to prevent tuning side effects. For example, it is no good increasing the buffer cache if you can reduce I/O by rewriting a SQL statement.

Database Design (if it’s not too late):

Poor system performance usually results from a poor database design. One should generally normalize to the 3NF. Selective denormalization can provide valuable performance improvements. When designing, always keep the “data access path” in mind. Also look at proper data partitioning, data replication, aggregation tables for decision support systems, etc.

Application Tuning:
Experience showed that approximately 80% of all Oracle system performance problems are resolved by coding optimal SQL. Also consider proper scheduling of batch tasks after peak working hours.

Memory Tuning:
Properly size your database buffers (shared pool, buffer cache, log buffer, etc) by looking at your buffer hit ratios. Pin large objects into memory to prevent frequent reloads.

Disk I/O Tuning:
Database files needs to be properly sized and placed to provide maximum disk subsystem throughput. Also look for frequent disk sorts, full table scans, missing indexes, row chaining, data fragmentation, etc.

Eliminate Database Contention:
Study database locks, latches and wait events carefully and eliminate where possible.

Tune the Operating System:
Monitor and tune operating system CPU, I/O and memory utilization. For more information, read the related Oracle FAQ dealing with your specific operating system.

36..How do you create a execute and display the   plan ?

A..@?/rdbms/admin/utlxplan.sql  (To create a plan table)

B..explain set statemen_id='1' for select * from emp;

C..@?/rdbns/admin/utlxpls.sql   (To  display the plan)

37..What is the difference between DBFile sequential and scattered reads?

Both “db file sequential read” and “db file scattered read” events signify time waited for I/O read requests to complete. Time is reported in 100’s of a second for Oracle 8i releases and below, and 1000’s of a second for Oracle 9i and above. Most people confuse these events with each other as they think of how data is read from disk. Instead they should think of how data is read into the SGA buffer cache.


db file sequential read: 
A sequential read operation reads data into contiguous memory (usually a single-block read with p3=1, but can be multiple blocks). Single block I/Os are usually the result of using indexes. This event is also used for rebuilding the controlfile and reading datafile headers (P2=1). In general, this event is indicative of disk contention on index reads.


db file scattered read: 
Similar to db file sequential reads, except that the session is reading multiple data blocks and scatters them into different discontinuous buffers in the SGA. This statistic is NORMALLY indicating disk contention on full table scans. Rarely, data from full table scans could be fitted into a contiguous buffer area, these waits would then show up as sequential reads instead of scattered reads.


The following query shows average wait time for sequential versus scattered reads: 


prompt “AVERAGE WAIT TIME FOR READ REQUESTS”
select a.average_wait “SEQ READ”, b.average_wait “SCAT READ”
from sys.v_$system_event a, sys.v_$system_event b
where a.event = ‘db file sequential read’
and b.event = ‘db file scattered read’;

38..Which init parameter  you have to set to by pass parsing?

cursor_sharing=force

39..How to check and  generate the  TKPROF report?

The TKPROF tool is a tuning tool used to determine CPU and execution times for SQL statements. Use it by first setting  timed_statistics to true in the initialization  file and then tuning  to trace for either the entire database  via the sql_trace parameter or for the session against the trace file and  then look at the output from the TKPROF tool. This can also be used to  generate explain plan output  

40..When is cost based optimization triggered?

It’s important to have statistics on all tables for the CBO (Cost Based Optimizer) to work correctly. If one table involved in a statement does not have statistics, Oracle has to revert to rule-based optimization for that statement. So you really want for all tables to have statistics right away; it won’t help much to just have the larger tables analyzed.
Generally, the CBO can change the execution plan when you: 

A  Change statistics of objects by doing an ANALYZE;
B. Change some initialization parameters (for example: hash_join_enabled, sort_area_size, db_file_multiblock_read_count).

TUNING Q & A Page - 3

PERFORMANCE  TUNING INTERVIEW QUESTIONS AND ANSWERS

                                                                   

                                                    Page – 3

 21..What is the  types of hits? 

Buffer hit and library hit

22..Creating index  in  online is possible?

YES. You can create and rebuild indexes online. This enables you to update base tables at the same time you are building or rebuilding indexes on that table. You can perform DML operations while the index building is taking place, but DDL operations are not allowed. Parallel execution is not supported when creating or rebuilding an index online.
CREATE INDEX emp_name ON emp (mgr, emp1, emp2, emp3) ONLINE;

23.. Explain row chaining, how does it happen? How can you reduce it? How do you correct it?

Row chaining occurs when a VARCHAR2 value is updated and the length of the new value is longer than the old value and won't fit in the remaining block space. This results in the row chaining to another block. It can be reduced by setting the storage parameters on the table to appropriate values. It can be corrected by export and import of the effected table
To get an idea of the total number of cache misses

24..What are  the values of optimizer_mode init parameters and their meaning?

Optimizer_mode = choose

25..What are  the difference between  lock and latch?

Locks protect data during  transaction another process, latching, controls access to physical pages. Latches are very lightweight, short-term synchronization objects protecting actions that do not need to be locked for the life of a transaction

SQL> SELECT SUM(reloads) “Cache Misses”,

2 SUM(pins) “Executions”,

3 100 * ( SUM(reloads) / SUM(pins) ) “Cache Miss Percent”

4 FROM v$librarycache;

26..What are  the difference between  lock and latch?

Locks protect data during  transaction another process, latching, controls access to physical pages. Latches are very lightweight, short-term synchronization objects protecting actions that do not need to be locked for the life of a transaction

27.. What is the use of library cache?

The library cache contains the shared SQL ans PL/SQL areas. Performance can be improved by both  the cache-hit rate in the library  cache and by speeding access to the library. Cache by holding infrequently used SQL statements in catch  longer

28..What is the difference between  DB file Sequential  read and DB file scattered read?

A..DB file sequential read, reads block into contiguous memory and DB file scattered read has to do with full table scan

B..The DB file sequential read, reads block into  contiguous memory  and DB file scattered read gets from  multiple block and scattered them into  buffer cache.

29..What are the init parameters related to performance / optimizer?

optimizer_mode = choose
optimizer_index_caching = 90
optimizer_index_cost_adj = 25
optimizer_max_permutations = 100
optimizer_use_sql_plan_baselines=true
optimizer_capture_sql_plan_baselines=true
optimizer_use_pending_statistics = true;
optimizer_use_invisible_indexes=true
optimizer_connect_by_cost_based=false
optimizer_compute_index_stats= true;

30..How to identify badly formed SQL statements?

Badly tuned SQL statements are

A  Indexes are not used. If a query is not properly formed, you may bypass an index 
that could be used to reduce I/O and CPU processing

B Hashing is bypassed. If a hashed cluster is improperly accessed, performance could be severely degraded.

C Unnecessary table scans are performed. If the SQL statement is improperly formed,

you may be doing unnecessary table scans.

D  Unnecessary amounts of data are returned. This is an undue burden not only
on the network but on the application as well.

TUNING Q & A Page - 2

PERFORMANCE TUNING INTERVIEW QUESTIONS AND ANSWERS

                                                                   

                                                             Page – 2

11..Types  of wait  events in tuning?

 CPU time and direct path read

12..What Is Locking?

In multi user systems or servers, many users may update the same information at the same time.Locking allows this to occur and ensures that data you are currently modifying cannot be modified at the same time by a different user.

13..Why the drop table is not going to   recycle bin?

If you are  using SYS use to drop any table  then user's object will not go to the recycle bin as  there is  no recycle bin for SYSTEM tablespace, even we have already  SET recycle bin parameter TRUE

select  * from v$parameter where name = ' recyclebin';

show parameter recyclebin;

14..How do you collect statistics of a table?

Analyse table emp compute statistics  or analyse  table emp estimate statistics.

15..What  is the fastest query method for a table?

Fetch by rowid

16..If oracle  database is running  slow, how to solve the problem?

select username, default_tablespace from dba_users;

usually the default  tablespace of he user  should not be in SYSTEM tablespace. This might  cause  performance issue. Create  a separate  and alter / assign this user. Also must move all the objects from SYSTEM table space to this new tablespace which you created.

17..Why and when should one tune?

One of the biggest responsibilities of a DBA is to ensure that the Oracle database is tuned properly. The Oracle RDBMS is highly tunable and allows the database to be monitored and adjusted to increase its performance.

One should do performance tuning for the following reasons:
The speed of computing might be wasting valuable human time (users waiting for response);

Enable your system to keep-up with the speed business is conducted; and  Optimize hardware usage to save money (companies are spending millions on hardware).

 18..What is the use of Benchmarks?

Standardized benchmarks are a good way to judge how well a system is performing and to compare different hardware platforms. If they are available to you, standardized benchmarks are also a good way to analyse the performance of your particular platform

19..What tuning  indicators one can use?

The following high-level tuning indicators can be used to establish if a database is performing optimally or not:

A.Buffer Cache Hit Ratio
Formula: Hit Ratio = (Logical Reads – Physical Reads) / Logical Reads
Action: Increase DB_CACHE_SIZE (DB_BLOCK_BUFFERS prior to 9i) to increase hit ratio

B.. Library Cache Hit Ratio
Action: Increase the SHARED_POOL_SIZE to increase hit ratio

20..How to run the ANALYSE command

The ANALYSE command is determined by the type of statistics or analysis you want

to perform. The ANALYSE command can be used in several different modes. The mode you choose depends on the data you want to gather as well as on the configuration of your system.

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