PROCESSES MEMORY USAGE:
-- Check for the entry memory processes usage.
select count(1) as qt_proc, trunc(min(decode(pga_alloc_mem,0,999999999,pga_alloc_mem))/1024/1024,3) as min_pga_proc_mb, trunc(max(pga_alloc_mem)/1024/1024,3) as max_pga_proc_mb, trunc(avg(pga_alloc_mem)/1024/1024,3) as avg_pga_proc_mb, trunc(sum(pga_alloc_mem)/1024/1024,3) as alloc_pga_mb, trunc(sum(pga_max_mem)/1024/1024,3) as total_high_pga_mb
from v$process;
 
-- Check for the 20 top processes of memory usage.
column user_info format a55
select * from (select s.sid, s.username || ' / ' || osuser || ' / ' || p.PROGRAM || decode(USERENV('SESSIONID'),audsid,' (**current**)', '') as user_info, trunc(PGA_ALLOC_MEM/1024/1024,3) as PGA_ALLOC_MEM, trunc(PGA_USED_MEM/1024/1024,3) as PGA_USED_MEM, 
trunc(PGA_MAX_MEM/1024/1024,3) as PGA_MAX_MEM
from v$session s, v$process p
where s.paddr = p.addr
order by pga_alloc_mem desc
) where rownum <= 20;
 
-- Check for the number of objects and the memory usage for java.
Select * from v$java_library_cache_memory;
-- Check the value for the initialization parameter PGA_AGGREGATE_TARGET
If WORKAREA_SIZE_POLICY is TRUE;  The PGA_AGGREGATE_TARGET should be defined with a value to keep ESTD_PGA_CACHE_HIT_PERCENTAGE grather than 95%.
Select value from v$parameter where name = 'pga_aggregate_target';
 
Select value from v$parameter where name = 'workarea_size_policy';
Select 'ACTUAL' as state, trunc( min( pga_target_for_estimate )/1024/1024,2) as SYZE_MB
from v$pga_target_advice
where pga_target_factor = 1
UNION ALL
select 'MINIMUN' as state, trunc( min( pga_target_for_estimate )/1024/1024,2) as SYZE_MB
from v$pga_target_advice
where estd_pga_cache_hit_percentage >= 95
UNION ALL
select 'DESIRED' as state, trunc( min( pga_target_for_estimate )/1024/1024,2) as SYZE_MB
from v$pga_target_advice
where estd_pga_cache_hit_percentage > 95;
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SGA Stats:
Statistic Name - Name of the statistic
Bytes - Size
Select NAME, BYTES from v$sgastat
order by NAME;
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort Stats:
Sort Parameter - Name of the sort parameter
Value - Number of sorts
sorts (memory) - The number of sorts small enough to be performed entirely in sort areas without using temporary segments.
sorts (disk) - The number of sorts that were large enough to require the use of temporary segments for sorting.
sorts (rows) - Number of sorted rows
The memory area available for sorting is set via the SORT_AREA_SIZE and SORT_AREA_RETAINED_SIZE init.ora parameters.
Select NAME, VALUE from v$sysstat
where   NAME like 'sort%';
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
All Events:
Event Name - Name of the event
Total Waits - Total number of waits for the event
Total Timeouts - Total number of timeouts for the event
Time Waited - The total amount of time waited for this event, in hundredths of a second
Average Wait - The average amount of time waited for this event, in hundredths of a second
Select EVENT, TOTAL_WAITS, TOTAL_TIMEOUTS, TIME_WAITED, round(AVERAGE_WAIT,2) "Average Wait" from v$system_event
order by TOTAL_WAITS;
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
All Statistics:
Stat# - Number of the statistic
Name - Name of the statistic
Class - Statistic class: 1 (User), 2 (Redo), 4 (Enqueue), 8 (Cache), 16 (OS), 32 (Parallel Server), 64 (SQL), 128 (Debug)
Value - Value of the statistic
Select STATISTIC#, NAME, CLASS, VALUE
from v$sysstat
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Wait Stats:
Class - Class of block subject to contention
Count - Number of waits by this OPERATION for this CLASS of block
Time -Sum of all wait times for all the waits by this OPERATION for this CLASS of block
Data Blocks - Usually occurs when there are too many modified blocks in the buffer cache; reduce contention by adding DBWR processes.
Free List - May occur if multiple data loading programs run simultaneously.
Segment Header - May occur when may full table scans execute simultaneously with data loading processes; aggravated by the parallel options. Reschedule data loading jobs to reduce contention;
Sort Block - Rarely seen except when the Parallel Query option is used; reduce contention by reducing the degree of parallelism or decreasing the SORT_AREA_SIZE init.ora parameter setting.
Undo Block - Very rarely occurs; may be caused by multiple users updating records in the same data block at a very fast rate; contention can usually be resolved by increasing the PCTFREE of the tables being modified.
Undo Header - May occur if there are not enough rollback segments to support the number of concurrent transactions.
Select CLASS, COUNT, TIME from  v$waitstat
order by CLASS;
* if the contention is on 'data block' check for:
SQL statements using unselective indexes.
Consider using automatic segment-space management or increase free-lists.
* if the contention is on 'undo header' consider using automatic segment-space management or add more rollback segments.
* if the contention is on 'undo block' consider using automatic segment-space management or make rollback segment sizes larger.
Select class, count, time
from  v$waitstat
where class in ('data block', 'undo header', 'undo block', 'segment header');
 
* if the contention is on 'segment header' look for the segment and consider increase free-lists.
Select s.segment_name, s.segment_type, s.freelists, w.wait_time, w.seconds_in_wait, w.state
from dba_segments s, v$session_wait w
where w.event = 'buffer busy waits'
and w.p1 = s.header_file
and w.p2 = s.header_block;
 
* Check for waits to find a free buffer in the buffer cache:
  Check if the I/O system is slow.
  Consider increase the size of the buffer cache if it is so small.
  Consider increase the number of DBWR process if the buffer cache is properly sized.
Select event, total_waits, time_waited
from v$system_event
where event in ('free buffer waits');
 
Contention for LATCHES:
* Generally, the DBA should not attempt to tune latches.
  The following steps are useful:
  - Investigate further, depending on the latch thait is contention
  - Consider tuning the application if the contentio is maily for shared pool and library cache.
  - if further investigation suggests it, size shared pool and buffer cache appropriately.
* For 'cache buffers chains' the misses_ratio and immed_misses_ratio shoud be less than 0.03 (< 3%). If not, consider increase buffer cache.
For 'cache buffers lru chain' the misses_ratio and immed_misses_ratio shoud be less than 0.03 (< 3%). If not, consider increase DBWRn Processes.
For 'library cache' a high value in misses_ratio and immed_misses_ratio indicate that is possible to tuning Shared Pool or create a Large Pool for Oracle Shared Server.
The wait_time column express the number of milliseconds waited after willing-to-wait request.
column name format a40
Select latch#, name, misses, gets, trunc(misses/gets,4) as misses_ratio,immediate_misses, immediate_gets, trunc(immediate_misses/immediate_gets,4) as immed_misses_ratio, 
wait_time as wait_time_ms, sleeps, spin_gets
from v$latch
where gets > 0
and immediate_gets > 0
and lower(name) like '%shared%'
or lower(name) like '%library%'
or lower(name) like '%chain%'
or lower(name) like '%redo%' )
order by name;
 
* If high value of misses are found to Library Cache, try to identify similar SQL statements that could be shared if literals were replaced with bind variables:
column sqlarea format a30
Select 'ENTRIES WITH 1 EXECUTION' as SqlArea, count(1) as Qtd,
'<-- A goal for this value should be less than 10% of Total Entries.' as Tip
from v$sqlarea
where executions = 1
UNION ALL
Select 'TOTAL OF ENTRIES IN SQLAREA' as SqlArea, count(1) as qtd, null
from v$sqlarea;
column sqlarea clear
set lines 1500
set pages 9999
spool D:\shared-pool-frag-yyyymmdd.txt
column  texto_hid noprint
Select qtde, texto_hid, sql_text
from    (select substr(sql_text,1,50) as texto_hid, count(1) as qtde
from v$sqlarea
where executions = 1
group by substr(sql_text,1,50)
having count(1) > 5
order  by 2 ) res,
v$sqlarea sa
where  sa.sql_text like res.texto_hid || '%'
order  by 1 desc, 2, 3;
column  texto_hid print
spool off

set lines 999
select v.HASH_VALUE, vt.sql_text as text_command
from v$sqltext vt, v$sqlarea v
where v.executions = 1
and vt.hash_value = v.hash_value
order  by upper(v.sql_text), hash_value, vt.piece;