Response Time
in Minutes 
Figure 1: Response Time for a Memory Intensive Report with given SGA (Buffer) settings
Inside slow queries
Documents Created by : Bharat Gali
Email : bharat@bharatgali.com
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 oracle.com 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
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)
WHEN MATCHED THEN
UPDATE SET a.status = b.status
WHEN NOT MATCHED THEN
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
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
98.415926
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$sgastat.name = 'free memory'
and v$ parameter .name = ‘shared_pool_size;
Shared Pool Size Free Bytes Percent Free
100,000,000 82,278,960 82.27896