how to choose optimum parameter size

i want to change the default size of the database parameter like db_buffer_cache and shared_pool_size ,library_chace,hit_ratio to values that increase system performance by decreasing query response time
what is the opimum value of this parameter
elham011800Asked:
Who is Participating?
 
androknegoConnect With a Mentor Commented:
Unfortunately there is no best universal solution, each one depends on lots of factors such as available RAM, number of concurrent connections, application type (OLTP/DSS). However, Oracle 9i and 10g come with a few advistors which work quite well, telling you approximately how much more performance improvement you can get by tweaking certain parameters.

For 10g, have a look at:
http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10752/memory.htm#53186

You may also want to have a look at other chapters under Database Performance Tuning Guide, mainly IO and application tuning.

HTH

Andrew
0
 
peterside7Connect With a Mentor Commented:
Here's some sql statement to tune your parameters:

REM Tuning Shared Pool
REM Tuning The Data Dictionary Cache

SET NEWPAGE 0
ttitle center 'DATABASE REPORT AND INSTANCE TUNING' right 'Page :' sql.pno skip -
       center '3) TUNING THE DATA DICTIONARY CACHE' skip skip -
       center 'Date : ' TODAY ' ' TTIME skip skip

select sum(gets) "Data Dictionary Gets",
       sum(getmisses) "Data Dic Cache getMisses",
       round((sum(getmisses)/sum(gets))*100,2) || ' %' Ratio_data_dictionary_cache        
from v$rowcache;

ttitle off
SET NEWPAGE 2
SELECT ' < then 15 %' THRESHOLD2 FROM dual;
SELECT 'Increase SHARED_POOL_SIZE' ACTION FROM dual;

========================================
SET NEWPAGE 0
ttitle center 'DATABASE REPORT AND INSTANCE TUNING' right 'Page :' sql.pno skip -
       center '4) EXAMINING BUFFER CACHE ACTIVITY (Hit Ratio and Hit Ratio by user)' skip skip -
       center 'Date : ' TODAY ' ' TTIME skip skip

select rtrim(name) TITRE,
       value "Valeur"
from v$sysstat
where name in ('db block gets','consistent gets','physical reads');

ttitle off
SET NEWPAGE 2
REM select round(activity_buffer_cache,2) || ' %' " Ratio Activity Buffer cache" from dual;
select ROUND(((SUM(DECODE(Name, 'consistent gets', Value, 0))+
               SUM(DECODE(Name, 'db block gets', Value, 0)) -
               SUM(DECODE(Name, 'physical reads', Value, 0)) )/
              (SUM(DECODE(Name, 'consistent gets',Value,0))+
               SUM(DECODE(Name, 'db block gets', Value, 0)))) *100,2) || ' %' " Ratio Activity Buffer cache"
from V$SYSSTAT;

SELECT ' 98% for oltp or more than 89% ' THRESHOLD3 FROM dual;
SELECT 'Increase DB_BLOCK_BUFFERS' ACTION FROM dual;
================================================
SET NEWPAGE 0
ttitle center 'DATABASE REPORT AND INSTANCE TUNING' right 'Page :' sql.pno skip -
       center '2) TUNING DU LIBRARY CACHE (Shared SQL Area Hit Ratio)' skip skip -
       center 'Date : ' TODAY ' ' TTIME skip skip


SELECT sum(PINS) "Executions",
       sum(RELOADS) "Caches misses while executing",
       DECODE(SUM(Pins),0,0,(SUM(Reloads)/SUM(Pins))*100) Miss_Ratio,
       DECODE(SUM(Pins),0,0,((SUM(Pins)-SUM(Reloads))/SUM(Pins))*100) Hit_Ratio
FROM v$librarycache;
ttitle off
SET NEWPAGE 2
SELECT ' Hit Ratio > then 99 %' THRESHOLD1 FROM dual;
SELECT 'Increase SHARED_POOL_SIZE' ACTION FROM dual;

Hope it helps
0
 
Pierrick LOUBIERConnect With a Mentor IS Operational Excellence ManagerCommented:
Have a look at Note:1019592.6 on Metalink. You'll find a "SCRIPT TO PROVIDE DATABASE PERFORMANCE HEALTHCHECK and RECOMMENDATIONS".
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
peterside7Commented:
To be more specific.

db_buffer_cache : if it's a dedicated database server, use everything you can.

shared_pool_size : 150 megs should be enough, which is the default on 9i

library_cache : part of the shared pool, look for v$librarycache.reloads (those are not good), if too much increase  
                      shared_pool

hit_ratio : more than 90% for oltp (for datawarehouse, it really depends on the db_buffer_cache, the more you have the higher the hit ratio will be, but 20% is not anormal, full table scan puts new blocks in memory and flushes old ones)
 

To do more tuning :
TOP SQL STATEMENTS IN ENTEPRISE MANAGER SQL ANALYZE
SQL Analyze is a GUI tool available with the Oracle Enterprise Manager Tuning Pack that can be used to identify and can help with tuning resource intensive SQL statements.

The queries for the heaviest SQL statements are done from the Top SQL menu and a variety of search criteria are available:

Buffer Cache Hit Ratio
Buffer Gets
Buffer Gets per Execution
Buffer Gets per Row
Disk Reads
Disk Reads per Execution
Executions
Parse Calls
Parse Calls per Execution
Rows Processed
Sharable Memory
Sorts
0
 
peterside7Commented:
Elham, do you need more detail on this question ?
0
 
BobMcCommented:
In general, allocate memory to where is is best used.
Increase your parameters incrementally until you dont see any further improvements in your tuning goal.

If its query response time you are trying to minimize, and you have a reasaonably tuned database, you would probably be better off starting with your queries themselves to see where improvements can be made.
0
 
Pierrick LOUBIERIS Operational Excellence ManagerCommented:
The Metalink note I mentioned is quite a good start for tuning.
0
 
Helena Markováprogrammer-analystCommented:
OK,
I will leave a recommendation in the Cleanup topic area that this question is:

Split between androknego {http:#12701395} and peterside7 {http:#12708853} and Ploubier {http:#12714101}.

Please leave any comments here within the next four days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

Henka
EE Cleanup Volunteer
0
All Courses

From novice to tech pro — start learning today.