Performance Question and Answers

Documents Created by             : Bharat Gali

Created date                            : 01-Apr-2007

Database Version                     : 10g

Last Revised                             :30-Jan-2008



Question 1

1. Currently, commercial database engines incorporate many features that assume large disk secondary storage areas. These are two basic strategies for improving the I/O performance:


1) Avoid disk I/O whenever possible, or 2) make the physical reads and writes more efficient.When they do occur. Briefly outline a few mechanisms or techniques that are useful for implementing these two strategies.


Bharat Gali reply:

Potential reasons for more disk I/O is

1) Bad SQL and PL/SQL.

2) Missing indexes, full table scans, row chaining, data fragmentation, etc


Mechanisms to avoid disk I/O and make the physical reads and writes more efficient


1) Find the largest amount of physical reads and logical reads by query.


Simple way to find Queries having more physical read


select    disk_reads, sql_text

from     v$sqlarea

where  disk_reads > 10000

order    by disk_reads desc;



Simple way to find Queries having more logical reads.


select    buffer_gets, sql_text

from     v$sqlarea

where  buffer_gets > 200000

order by           buffer_gets desc;



Multiples approaches must be made to cover all types of queries


Below listed few approaches.

1) Check the tables in the queries for proper indexes creations.

2) Check for tables, indexes and other objects for recent statistics (This can be done using

dbms_stats package)

3) Pin (Cache) most used PL/SQL statements into memory

syntax: Dbms_shared_pool.keep(object_name).

4) Avoid using database function in select statements as much as possible.

Ex select sales_no,get_vendor_name(sales_no)

     from sales_transactions

     where  sales_date = sysdate

In the above query get_vendor_name is a function.

5) Avoid full table scans for data retrievals as much as possible.

Some times if data in the table is huge and select criteria for  retrieval is more. Then even

thought the table column having indexes this will not help. In such cases create table

partitions.( Some times there will be other dis-advantages for table partitions check for

business cases before creating table partitions).

6) Use Merge statements (Available in 9i and 10g only) for huge updates and deletes


Merge statements uses very less I/O.


Example Merge statements


MERGE INTO test1 a

  USING all_objects b

    ON (a.object_id = b.object_id)


    UPDATE SET a.status = b.status


    INSERT (object_id, status)

    VALUES (b.object_id, b.status);



Your Question

2. Most commercial database systems use variable-sized rows to avoid wasted space. However as rows change size through update activity they may no longer fit in their original block, migrating to other blocks, or even requiring several blocks to store large rows. How does this problem affect performance? Which features of the Oracle DBMS allow the designer to lessen such performance impacts.


Bharat Gali Answer.


The above problem technically defined as row chaining in Oracle. Excessive row chaining can cause a dramatic increase in disk I/O because several I/Os are required to fetch the block instead of one single I/O.


Row chaining can be avoid using more value for PCTFREE parameter at the time of table


creation in oracle.


Sample syntax




  SUBSCRIBER_ID  NUMBER                    


  CONID          NUMBER,

  SCORE          NUMBER








            INITIAL          16K

            MINEXTENTS       1

            MAXEXTENTS       2147483645

            PCTINCREASE      0

            FREELISTS        1

            FREELIST GROUPS  1

            BUFFER_POOL      DEFAULT









The Oracle DBMS relies on shared memory for interprocess communications within the database engine. In addition, the shared memory is divided into separate areas, such as the database buffer cache, redo log buffer, shared pool (library cache and data dictionary cache), java pool and large pool. Why is shared memory a good choice of for communications? Are there any potential drawbacks?


I have no Clue with this question.


Partial answer I saw in the internet.


Why is shared memory a good choice of for communications?


Shared memory allows processes to access common structures and data by placing them in shared memory segments. It's the fastest form of Interprocess Communication (IPC) available since no kernel involvement occurs when data is passed between the processes. In fact, data does not need to be copied between the processes.


Oracle uses shared memory segments for the Shared Global Area (SGA) which is an area of memory that is shared by Oracle processes. The size of the SGA has a significant impact to Oracle's performance since it holds database buffer cache and much more.