Inside slow queries

Documents Created by             : Bharat Gali

Email                                      :

Created date                            : 01-Feb-2007

Database Version                     : 10g

Last Revised                             :22-Apr-2010


If my query is running slow. The immediate answer is go and creates some indexes. Lot of times this may not help. If the query is running slow first collect some basic information before doing any thing.


Information needs to collect when query is running slow


1)      Is this query running very slow since it developed or it happened today? I mean yesterday it is running very fine but today it is very slow.

The first thing you have to do is check the network traffic/Network errors for some thing when wrong in the network side. The simple way to check for network problems is open some site like or access some share drive which is located some where in network or use tnsping to the database.

If you noticed any slow access then contact network support. Some times this has been a problem. Which is out of scope of oracle database but creates problem to the database.



2)      Network is good no problems noticed then next steps needs to check for any massive data loads done during last past night.


If data loads done last past night. Check whether database statistics ran after database load

Simple way to run statistics is

     DBMS_STATS.GATHER_TABLE_STATS ('scott', 'emp', 5, stattab => 'savestats');


3)      Stats are fine. Still the query is slow. Next step needs to check is whether any data base upgrades done from lower version to higher version.


Same Queries with same data will run very slow in newer versions when upgraded from lower version.

 Then you might running with database CBO bug.

Please refer Oracle support for more details for this kind of bugs.


4)      Some times queries will be slow due to more I/O from database to client

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);





Queries will be slow due to in sufficient memory.


Simple query for determine if the data buffers,block size and sort area size are properly allocated


select      name, value

from         v$parameter

where     name in (‘db_block_buffers’, ‘db_block_size’, ‘shared_pool_size’, ‘sort_area_size’);


NAME                                                      VALUE                                           

--------------------------------------------------             --------------------                            

db_block_buffers                                    4000                                             

db_block_size                                         4096                                            

shared_pool_size                                                   7000000                                          

sort_area_size                                        262144        






From here I copied from google


Determine if the data block buffers is set high enough:

select 1-(sum(decode(name, 'physical reads', value,0))/            

                 (sum(decode(name, 'db block gets', value,0)) +        

                 (sum(decode(name, 'consistent gets', value,0))))) * 100

                 "Read Hit Ratio"

from   v$sysstat;


Read Hit Ratio


Although hit ratios below 90-95% are usually a sign of poor indexing; Distortion of the hit ration numbers is possible.  See the next section for more information.

Response Time in Minutes

    Figure 1: Response Time for a Memory Intensive Report with given SGA (Buffer) settings


Hit Ratio Distortion:

 Even though the equations for finding a problems seems easy, sometimes the results are not accurate.  Many third party products also receive this misinformation, yet some go to other areas to get the correct information.  Below, I show one such case where misinformation is returned.

There are also false hit ratio distortions.  SQL*Forms can cause a false high hit ratio, rollback segments can cause a false high hit ratio impact and indexes can have hit ratios as high as 86% when none of the blocks were cached prior to the query executing. 

C. It is important to look at the SHARED_POOL_SIZE for proper sizing

With a greater amount of procedures, packages and triggers being utilized with Oracle, the SHARED_POOL_SIZE makes up a much greater portion of the Oracle SGA.  This is the memory allocated for the library and data dictionary cache.  If the SHARED_POOL_SIZE is set too low then you will not get the full advantage of your DB_BLOCK_BUFFERS.

Determine dictionary cache miss ratio:

select       sum(gets) “Gets”, sum(getmisses) “Misses”,

                (1 - (sum(getmisses) / (sum(gets) +          

                sum(getmisses))))*100 “HitRate”

from        v$rowcache;


Gets         Misses      HitRate

10233             508   95.270459

This would be a good Ratio and would probably not require action in this area.

Determine library cache hit ratio:

select       sum(pins) Executions, sum(pinhits) “Execution Hits”,

                ((sum(pinhits) / sum(pins)) * 100) phitrat,

                sum(reloads) Misses,

                ((sum(pins) / (sum(pins) + sum(reloads))) * 100)  hitrat

from        v$librarycache;


Executions               Execution Hits   PHITRAT       Misses  HITRAT

        3,582                                3,454           96.43                6       99.83

Tip: If the hit ratio or reloads is high, increase the shared_pool_size INIT.ora parameter.  Reloads indicate that statements that were once in memory now had to be reloaded because they were pushed out, whereas misses include statements that are loaded for the first time.


How much memory is left for SHARED_POOL_SIZE:

col value for 999,999,999,999 heading “Shared Pool Size”

col bytes for 999,999,999,999 heading “Free Bytes”

select       to_number(v$parameter.value) value, v$sgastat.bytes,

(v$sgastat.bytes/v$parameter.value)*100 “Percent Free”

from        v$sgastat, v$parameter

where       v$ = 'free memory'

and           v$ parameter .name = ‘shared_pool_size;


Shared Pool Size       Free Bytes                                Percent Free

     100,000,000       82,278,960              82.27896