Re: performance problem

From: Rniemic <rniemic_at_aol.com>
Date: 7 Jun 1999 17:29:21 GMT
Message-ID: <19990607132921.24858.00000008@ng34.aol.com>

Fragmented free space (from your query) may slow down heavy inserts looking for large enough blocks to expand into...but below are some more common problems. If the free space is an issue ... altering tables to grab larger pieces will mean that they will do it less often. You may want to check dba_segments to see which objects are fragemented as well. There are a large amount of potential areas that could be the issue... I suggest some of the following queries to help find the potential problem. The Performance Tuning Tips and Techniques book I wrote is a much larger (900 pages worth) volume of information to pinpoint problems ... it is out on www.amazon.com with the ISBN: 0078824346 or search for Niemiec.

You might want to check some of these:

Determine if the data block buffers is set high enough (this is memory for data):

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

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

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.

Find the problem queries ... ones slowing you down:

Example (Finding the largest amount of physical reads > 10000 by query):

select 	disk_reads, sql_text
from   	v$sqlarea
where  	disk_reads > 10000
order 	by disk_reads desc;

Example (Finding the largest amount of logical reads > 200000 (memory) by query):

select 	buffer_gets, sql_text
from   	v$sqlarea
where  	buffer_gets > 200000
order by 	buffer_gets desc;

Other init.ora's:

2. DB_BLOCK_BUFFERS - Memory allocated for the data
3. SHARED_POOL_SIZE - Memory allocated for data dictionary and SQL & PL/SQL
4. OPTIMIZER_MODE - Choose, Rule, First_Rows or All_Rows
5. SORT_AREA_SIZE - Memory used for sorting and merging tables
6. SORT_AREA_RETAINED_SIZE - Memory held after the sort for a session
7. LOG_CHECKPOINT_INTERVAL - How often that committed transactions are archived
8. OPEN_CURSORS - Holds user statements to be processed (private area)
9. SHARED_POOL_RESERVED_SIZE - Memory held for future big PL/SQL needing

contiguous memory (x$ksmsp shows contig.) 10. DB_BLOCK_SIZE - Size of the blocks (db_block_size x db_block_buffers=bytes for data)...need a rebuild to change this....

This might help...

Rich Niemiec
Oracle Performance Tips and Techniques
Oracle Press
ISBN: 0078824346 Received on Mon Jun 07 1999 - 12:29:21 CDT