See How to tune a query, How many opened cursors?(to tune session_cached_cursors)
11g Basic memory parameters schema
sga_target pga_aggregate_target memory_target memory_max_target result
0 0 900M 999M AMM enabled, defaults 60% to SGA and 40% to PGA
50M 100M 900M 999M AAM enabled, SGA minimum 50M, PGA minimum 100M
50M 0 900M 999M AAM enabled, SGA minimum 50M, PGA minimum is memory_target-50M
0 100M 900M 999M AAM enabled, SGA minimum is memory_target-100M, PGA minimum 100M
900M 0 or null AAM enabled, max_memory_target is memory_target. (SGA is not autotuned, PGA is)
50M Automatically tuned only SGA subcomponents. PGA is autotuned. max_memory_target=0
0 or null 999M AAM disabled. memory_target=0 default value
In 11g you may simply put:
*.memory_target=300M
#I recommend to set this since there are some bugs in 11.1
*.memory_max_target=300M
|
Curent settings select * from V$MEMORY_DYNAMIC_COMPONENTS #bear in mind that dinamically growing components is extremely time consuming during growth, #I really suggest to avoid growth. You should not see LAST_OPER_TYPE=GROWTH #this shows any growing component select * from V$MEMORY_CURRENT_RESIZE_OPS |
Tuning the sga size
At the moment
select sga_size, (1-estd_db_time_factor)*100 from v$sga_target_advice order by sga_size;
#remember to look at actual size:
select * from V$MEMORY_DYNAMIC_COMPONENTS
sga_size, (1-estd_db_time_factor)*100
171 , -2,41
228 , 0
285 , 1,2
342 , 2,41 #OPTIMAL value, since increasing over will not add benefit
399 , 2,41
456 , 2,41
On the past
--select dbid, instance_number, max(sga_size) from (
select a.*, s.begin_interval_time, s.end_interval_time
from DBA_HIST_SGA_TARGET_ADVICE a, sys.wrm$_snapshot s
where s.snap_id=a.snap_id and s.dbid=a.dbid and s.instance_number=a.instance_number
/* Comment out this condition for a complete view */
and sga_size = (select min(sga_size) from DBA_HIST_SGA_TARGET_ADVICE
where snap_id=a.snap_id and dbid=a.dbid and instance_number=a.instance_number
and estd_db_time =
(select min(estd_db_time) from DBA_HIST_SGA_TARGET_ADVICE
where snap_id=a.snap_id and dbid=a.dbid and instance_number=a.instance_number
)
)
order by a.snap_id desc, a.dbid, a.instance_number, sga_size
--) group by dbid, instance_number
|
Tune java_pool_size #Using the same logic look at: select * from V$JAVA_POOL_ADVICE #also use SELECT pool, name, bytes/1024/1024 MB FROM V$SGASTAT WHERE NAME = 'free memory' AND POOL = 'java pool'; |
Size the shared pool and large pool Size the shared pool using shared_pool_size and large_pool_size param Determine actual free space in the pool SELECT pool, name, bytes/1024/1024 MB FROM V$SGASTAT WHERE NAME = 'free memory' Available memory denotes pool is ok You may see the graphical advisor in Enterprise Manager under Instance/Configuration/Memory Look for reload in library cache: select * from V$LIBRARYCACHE |
| Tune the buffer cache Turn on DB_CACHE_ADVICE and let the db work. See the current buffer cache size select value/1024/1024 from v$parameter where upper(name) = 'DB_CACHE_SIZE' SELECT size_for_estimate, buffers_for_estimate, estd_physical_read_factor, estd_physical_reads FROM V$DB_CACHE_ADVICE WHERE name = 'DEFAULT' AND block_size = (SELECT value FROM V$PARAMETER WHERE name = 'db_block_size') AND advice_status = 'ON'; Lookup on SIZE_FOR_ESTIMATE to the current value and select a right one. Look at the buffer cache hit ratio using select 1 - ((pr.value - prd.value - prdl.value) / (db_block_gets.value + consistent_gets.value - physical_reads_direct.value - physical_reads_direct_lob.value)) hit_ratio from V$SYSSTAT pr, V$SYSSTAT prd, V$SYSSTAT prdl, V$SYSSTAT db_block_gets, V$SYSSTAT consistent_gets, V$SYSSTAT physical_reads_direct, V$SYSSTAT physical_reads_direct_lob where pr.name = 'physical reads' and prd.name = 'physical reads direct' and prdl.name = 'physical reads direct (lob)' and db_block_gets.name = 'db block gets' and consistent_gets.name = 'consistent gets' and physical_reads_direct.name = 'physical reads direct' and physical_reads_direct_lob.name = 'physical reads direct (lob)' It should be near 0,98 or 0,99. Create and size a "keep" buffer pool Determine most used object SELECT o.owner, o.object_name, object_type, COUNT(1) number_of_blocks FROM DBA_OBJECTS o, V$BH bh WHERE o.object_id = bh.objd AND o.owner != 'SYS' AND o.owner != 'SYSTEM' GROUP BY o.owner, o.object_name, object_type ORDER BY count(1) desc; Create a buffer pool keep by assigning a value to db_keep_cache_size, it is not a subset of the default buffer pool, the size should be the sum of the used blocks of the candidate objects Assign the keep buffer pool to most used object, You may use DBMS_SHARED_POOL.KEEP Tune keep buffer pool using SELECT name, SIZE_FOR_ESTIMATE, BUFFERS_FOR_ESTIMATE, ESTD_PHYSICAL_READ_FACTOR, ESTD_PHYSICAL_READS FROM V$DB_CACHE_ADVICE WHERE NAME = 'KEEP'AND BLOCK_SIZE = (SELECT VALUE FROM V$PARAMETER WHERE NAME = 'db_block_size') AND ADVICE_STATUS = 'ON' |
| Determine index use since instance startup (please see Note:144070.1 about ALTER INDEX MONITORING USAGE) (please see Note:50607.1) about hints, select /*+ index(TABLE_NAME INDEX_NAME) */ col1 --Determine last instance startup, stats below refers to last instance startup
select trunc(sysdate-startup_time) elapsed_days, to_char(startup_time, 'dd/mm/yyyy hh24:mi') instance_startup
--**************
--* TXT format *
--**************
select rpad(a.owner, (select max(length(owner)) from dba_users)) owner,
rpad(a.index_name, (select max(length(index_name)) from dba_indexes)) index_name,
a.logical_read,
physical_reads,
physical_reads_direct,
rpad(a.bytes_mb, 4) MB,
a.db_block_changes,
a.uniqueness from (
--*****************
--* QUERY 1 BEGIN *
--*****************
select i.owner, index_name
, nvl(logical_read, 0) logical_read
, nvl(physical_reads, 0) physical_reads
, nvl(physical_reads_direct, 0) physical_reads_direct
--, nvl(bytes, 0) bytes
, nvl(bytes/1024/1024, 0) bytes_MB
, nvl(db_block_changes, 0) db_block_changes
--, nvl(db_block_changes/1024/1024, 0) db_block_changes_MB
, uniqueness
from dba_indexes i
, (select owner, object_name, sum(value) logical_read from V$SEGMENT_STATISTICS st where statistic# = 0 group by owner, object_name) st_logical_reads
, (select owner, object_name, sum(value) db_block_changes from V$SEGMENT_STATISTICS st where statistic# = 2 group by owner, object_name) st_db_block_changes
, (select owner, object_name, sum(value) physical_reads from V$SEGMENT_STATISTICS st where statistic# = 3 group by owner, object_name) st_physical_reads
, (select owner, object_name, sum(value) physical_reads_direct from V$SEGMENT_STATISTICS st where statistic# = 5 group by owner, object_name) st_physical_reads_direct
, (select owner, segment_name, sum(bytes) bytes from dba_segments st group by owner, segment_name) segments
where st_logical_reads.owner (+)= i.owner and st_logical_reads.object_name (+)= i.index_name
and st_db_block_changes.owner (+)= i.owner and st_db_block_changes.object_name (+)= i.index_name
and st_physical_reads.owner (+)= i.owner and st_physical_reads.object_name (+)= i.index_name
and st_physical_reads_direct.owner (+)= i.owner and st_physical_reads_direct.object_name (+)= i.index_name
and segments.owner (+)= i.owner and segments.segment_name (+)= i.index_name
and i.owner not in ('SYS', 'SYSTEM', 'PERFSTAT', 'OUTLN', 'DBSNMP', 'XDB', 'SYSMAN',
'CTXSYS', 'OLAPSYS')
--We are not interested in Primary Key indexes
and not i.uniqueness in ('UNIQUE')
--and table_owner='VPO' and table_name='INDIVIDUALE_S'
order by 3 desc
--*****************
--* QUERY 1 END *
--*****************
) a |