Queries Running Very Slow

Performance Tuning

This is the one which we often get, and the immediate answer would be, just go and take plan of the query and create some indexes if necessary. Lot of times this will not help you. There are so many possible reasons that we need to look.
If the query is running very slow collect some basic information before doing anything, this will be the first step to resolve this issue.

Information needs to collect when query is running slow

  1. First execute the query and check how long it took? Is this query running very slow since it developed or it happened today? I mean yesterday or sometime before it was running fine but today it is very slow. The first thing you have to do is check the network traffic/Network errors for something when wrong in the network side, the best way to check is access some share drive which is located somewhere in network or use tnsping, traceroute, try pinging or ask network admin to check the packet size. If you notice any slow access then contacts network support. Sometimes this has been a problem. Which is out of scope of oracle database but created problem to the database, recently I faced the same kind of issue.
  2. Network Issue:
    Network is good no problems noticed then next step needs to check for any massive data loads done during last past night, are there any code changes, when was the last time the tables got analyzed, check the tables in the queries for proper indexes creation, check for tables, indexes and other objects for recent statistics, avoid using database functions, avoid using full table scan as much as possible, create partitions if necessary for faster access , use merge statements for huge updates and deletes, and so on. Check whether database statistics ran after database load. Simple way to run statistics is using DBMS_STAT.GATHER_TABLE_STATS
  3. Statistics are fine. Still the query is slow. Next step needs to check is whether any database upgrades are done recently or any patches applied to higher version. Same queries with same data will run very slow in newer versions when upgrade from lower version. Then you might run with database CBO Bug. Contact Oracle support for more details regarding this bug.
  4. I/O Issue:
    Sometimes queries will be very slow due to more I/O from database to client.
    Mechanism to avoid disk I/O and make the physical reads and writes more efficient
    First get the largest amount of physical reads and logical reads and the simple way to get them are:

For Physical Reads:
Select disk_reads, sql_text from v$sqlarea where disk_reads > 10000 order by disk_reads desc;
For Logical Reads:
Select Buffer_gets, sql_text from v$sqlarea where buffer_gets > 200000 order by
Buffer_gets desc;
Multiple approaches must be made to cover all types of queries

  1. Memory Issue:
    Pin (Cache) most used PL/SQL statement into memory
    Syntax: dbms_shared_Pool.keep(Object_name)
    Few queries which would help you to resolve the memory related issues are:
    Select name, value from gv$parameter where name in (ëdb_block_buffersí, ëdb_block_sizeí, ëshared_pool_sizeí, ësort_area_sizeí);
    Check the following:
    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 gv$sysstat;

Read Hit Ratio
98.99
Hit ratio below 90-95% is signed as poor indexing;
Check the Shared Pool Size:
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.

How to find the data dictionary cache miss ratio?
Select sum(gets) ìGetsî, sum(getmisses) ìMissesî, (1-(sum(getmisses)/(sum(gets)+ sum(getmisses)))) * 100 ìHitRateî
From gv$rowcache;

Gets Misses HitRate
11000 558 96.5

This is a good ratio does not require any further action.
How to find 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 gv$librarycache;

EXECUTIONS Execution Hits PHITRAT MISSES HITRAT


1126456014 1121589121 99.5679465 102177 99.9909302

How much memory is left for shared_pool_size?
select to_number(gv$parameter.value) value, gv$sgastat.bytes, (gv$sgastat.bytes/gv$parameter.value)*100 ìPercent Free
from gv$sgastat, gv$parameter where gv$sgastat.name = ‘free memory and gv$ parameter .name = ëshared_pool_size;

Shared Pool Size Free Bytes Percent Free

100,000,000 82,278,960 82.278