SGA Info as per Oracle script 430473.1

 

prompt**=====================================================================================================**

prompt**  **  SGA  Info  as per Oracle script 430473.1  **

prompt**=====================================================================================================**

set lines 100

set pages 300

col inst_id heading "Instance"

col instance_number heading "Instance"

col name heading "Allocations"

col mb format 999,999 heading "MBytes"

select inst_id, name, round((bytes/1024/1024),0) MB 

from gv$sgastat where pool='shared pool' 

and bytes > 10000000

order by inst_id asc, bytes desc

/

prompt**  parameers  **

set lines 120

set pages 300

clear col

set termout off

set trimout on

set trimspool on

col "Setting" format 999,999,999,999

col "MBytes" format 999,999

col inst_id format 999 head "Instance #"

 

break on inst_id skip 2

select inst_id, 'Shared Pool Size'||':  '||decode(value,null,-1,value) "Setting"

   ,(value/1024/1024) "MBytes"

from gv$parameter where name='shared_pool_size'

union

select inst_id, 'Shared Pool Reserved Area'||':  '||decode(value,null,-1,value) "Setting"

   ,(value/1024/1024) "MBytes"

from gv$parameter where name='shared_pool_reserved_size'

union

select inst_id, 'Log Buffer'||':  '||decode(value,null,-1,value) "Setting"

   ,(value/1024/1024) "MBytes"

from gv$parameter where name='log_buffer'

union

select inst_id, 'Streams Pool Size'||':  '||decode(value,null,-1,value) "Setting"

   ,(value/1024/1024) "MBytes"

from gv$parameter where name='streams_pool_size'

union

select inst_id, 'Buffer Cache'||':  '||decode(value,null,-1,value) "Setting" 

   ,(value/1024/1024) "MBytes"

from gv$parameter where name='db_cache_size'

union

select inst_id, 'Recycle Cache'||':  '|| decode(value, null,-1,value) "Setting" 

   ,(value/1024/1024) "MBytes"

from gv$parameter where name='db_recycle_cache_size'

union

select inst_id, 'Keep Cache'||':  '|| decode(value, null,-1,value) "Setting" 

   ,(value/1024/1024) "MBytes"

from gv$parameter where name='db_keep_cache_size'

union

select inst_id, '2K Cache'||':  '|| decode(value, null,-1,value) "Setting" 

   ,(value/1024/1024) "MBytes"

from gv$parameter where name='db_2k_cache_size'

union

select inst_id, '4K Cache'||':  '|| decode(value, null,-1,value) "Setting" 

   ,(value/1024/1024) "MBytes"

from gv$parameter where name='db_4k_cache_size'

union

select inst_id, '8K Cache'||':  '|| decode(value, null,-1,value) "Setting" 

   ,(value/1024/1024) "MBytes"

from gv$parameter where name='db_8k_cache_size'

union

select inst_id, '16K Cache'||':  '|| decode(value, null,-1,value) "Setting" 

   ,(value/1024/1024) "MBytes"

from gv$parameter where name='db_16k_cache_size'

union

select inst_id, '32K Cache'||':  '|| decode(value, null,-1,value) "Setting" 

   ,(value/1024/1024) "MBytes"

from gv$parameter where name='db_32k_cache_size'

union

select inst_id, 'Large Pool Size'||':  '||decode(value,null,-1,value) "Setting" 

   ,(value/1024/1024) "MBytes"

from gv$parameter where name='large_pool_size'

union

select inst_id, 'Java Pool Size'||':  '||decode(value,null,-1,value) "Setting" 

   ,(value/1024/1024) "MBytes"

from gv$parameter where name='java_pool_size'

union

select inst_id, 'SGA Max'||':  '|| decode(value, null,-1,value) "Setting" 

   ,(value/1024/1024) "MBytes"

from gv$parameter where name='sga_max_size'

union

select inst_id, 'SGA Target'||':  '|| decode(value, null,-1,value) "Setting" 

   ,(value/1024/1024) "MBytes"

from gv$parameter where name='sga_target'

order by 1, 2

/

col Setting format 999,999,99

select inst_id, 'Session Cached Cursors'||':  '|| decode(value, null,-1,value) "Setting" 

from gv$parameter where name='session_cached_cursors'

union

select inst_id, 'Open Cursors'||':  '||decode(value,null,-1,value) "Setting" 

from gv$parameter where name='open_cursors'

union

select inst_id, 'Processes'||':  '||decode(value,null,-1,value) "Setting" 

from gv$parameter where name='processes'

union

select inst_id, 'Sessions'||':  '||decode(value,null,-1,value) "Setting" 

from gv$parameter where name='sessions'

union

select inst_id, 'DB Files'||':  '||decode(value,null,-1,value) "Setting" 

from gv$parameter where name='db_files'

union

select inst_id, 'Shared Server (MTS)'||':  '||decode(value,null,-1,value) "Setting" 

from gv$parameter where name='shared_server'

order by 1, 2

/

col Setting format a30

select inst_id, 'Cursor Sharing'||':  '|| value "Setting" 

from gv$parameter where name='cursor_sharing'

union

select inst_id, 'Query Rewrite'||':  '||value "Setting" 

from gv$parameter where name='query_rewrite_enabled'

union

select inst_id, 'Statistics Level'||':  '||value "Setting" 

from gv$parameter where name='statistics_level'

union

select inst_id, 'Cache Advice'||':  '||value "Setting" 

from gv$parameter where name='db_cache_advice'

union

select inst_id, 'Compatible'||':  '||value "Setting" 

from gv$parameter where name='compatible'

order by 1, 2

/

col resource_name format a25 head "Resource"

col current_utilization format 999,999,999,999 head "Current"

col max_utilization format 999,999,999,999 head "HWM"

col intl format a15 head "Setting"

select inst_id, resource_name, current_utilization, max_utilization, initial_allocation intl

from gv$resource_limit

where resource_name in ('processes', 'sessions','enqueue_locks','enqueue_resources',

   'ges_procs','ges_ress','ges_locks','ges_cache_ress','ges_reg_msgs',

   'ges_big_msgs','ges_rsv_msgs','gcs_resources','dml_locks','max_shared_servers')

order by resource_name, inst_id

/

col Parameter format a35 wrap

col "Session Value" format a25 wrapped

col "Instance Value" format a25 wrapped

select  a.inst_id, a.ksppinm  "Parameter",

             b.ksppstvl "Session Value",

             c.ksppstvl "Instance Value"

      from x$ksppi a, x$ksppcv b, x$ksppsv c

     where a.indx = b.indx and a.indx = c.indx

       and a.inst_id=b.inst_id and b.inst_id=c.inst_id

       and a.ksppinm in ('_kghdsidx_count','__shared_pool_size','__streams_pool_size',

 '__db_cache_size','__java_pool_size','__large_pool_size',

 '_PX_use_large_pool', '_large_pool_min_alloc', 

 '_shared_pool_reserved_min', '_shared_pool_reserved_min_alloc',

 '_shared_pool_reserved_pct','_4031_dump_bitvec','4031_dump_interval',

 '_4031_max_dumps','4031_sga_dump_interval','4031_sga_max_dumps',

 '_optim_peek_user_binds','_px_bind_peek_sharing','event',

 '_kgl_heap_size','_library_cache_advice')

order by 2;

 

clear col

set termout on

set trimout off

set trimspool off

clear breaks

prompt** resize operation  **

REM  Investigate SGA components

REM     These queries do not impact performance on the database and you can run them 

REM     as often as you like

set lines 132

set pages 300

set termout off

set trimout on

set trimspool on

col component for a25 head "Component"

col status format a10 head "Status"

col initial_size for 999,999,999,999 head "Initial"

col parameter for a25 heading "Parameter"

col final_size for 999,999,999,999 head "Final"

col changed head "Changed At"

col current_size for 999,999,999,999 head "Current Size"

col user_specified_size for 999,999,999,999 head "Explicit Setting"

col min_size for 999,999,999,999 head "Min Size"

col max_size for 999,999,999,999 head "Max Size"

col granule_size for 999,999,999,999 head "Granule Size"

break on report

compute sum of current_size on report

select inst_id, component, user_specified_size, current_size, granule_size

from gv$memory_dynamic_components

order by component, inst_id

/

col last_oper_type for a15   head "Operation|Type"

col last_oper_mode for a15  head "Operation|Mode"

col lasttime for a25 head "Timestamp"

select inst_id, component, last_oper_type, last_oper_mode, 

  to_char(last_oper_time, 'mm/dd/yyyy hh24:mi:ss') lasttime

from gv$memory_dynamic_components

order by 2, 1

/

select inst_id, component, parameter, initial_size, final_size, status, 

to_char(end_time ,'mm/dd/yyyy hh24:mi:ss') changed

from gv$memory_resize_ops

order by 1, 7

/

REM These values tend to help find explicit (minimum settings)

REM for the components to help auto-tuning

REM steer clear of over-aggressive moving of memory

REM withing the SGA

col low format 999,999,999,999 head "Lowest"

col high format 999,999,999,999 head "Highest"

col lowMB format 999,999 head "MBytes"

col highMB format 999,999 head "MBytes"

select inst_id, component, min(final_size) low, (min(final_size/1024/1024)) lowMB,

max(final_size) high, (max(final_size/1024/1024)) highMB

from gv$memory_resize_ops

group by component, inst_id

/

clear breaks

col name format a40 head "Name"

col resizeable format a4 head "Auto?"

select * from gv$sgainfo order by inst_id

/

 

set termout on

set trimout off

set trimspool off

clear col

prompt** non shared sql   **

REM  AnalyzeNonShared.sql

REM  Single use SQL (nonshared)

REM  

REM  The goal in the Library Cache is to reuse SQL

REM  The code listed here may need further investigation

REM

REM   Run as "/ as sysdba"  

REM  

REM Pointer:   The total memory for non-shared SQL statements should not exceed

REM                20% of the Shared Pool.   If this percentage is much larger than that

REM                then efforts should be made to decrease the non-shared code in the

REM                application.

set serveroutput on

 

declare

   SHPSIZE number;

   SV number;

   IV number;

   NUM_SQL number;

   NUM_EX1 number;

   TTLBYTES number;

   EX1_MEMORY number;

   cursor c1 is select bytes/1024/1024 from gv$sgainfo where name='Shared Pool Size';

   cursor c2 is select to_number(b.ksppstvl)/1024/1024, to_number(c.ksppstvl)/1024/1024

      from x$ksppi a, x$ksppcv b, x$ksppsv c

      where a.indx = b.indx and a.indx = c.indx and a.ksppinm in ('__shared_pool_size')

      order by 1;

   cursor c3 is select count(1), sum(decode(executions,1,1,0)),

      round(sum(sharable_mem)/1024/1024,0), round(sum(decode(executions, 1, sharable_mem/1024/1024)),0)

      from gv$sqlarea where sharable_mem > 0;

begin 

   open c1;

     fetch c1 into SHPSIZE;

     dbms_output.put_line('Explicit/Minimum Setting:  '||SHPSIZE);

   close c1;

   open c2;

     fetch c2 into SV, IV;

     dbms_output.put_line('Auto-tuned Setting Currently:  '||SV);

   close c2;

   open c3;

     fetch c3 into NUM_SQL, NUM_EX1, TTLBYTES, EX1_MEMORY;

     dbms_output.put_line('   ---   Analysis: (Memory Shown in MBytes)');

     dbms_output.put_line('   =======================================');

     dbms_output.put_line('  ............... Stored Objects:  '||NUM_SQL);

     dbms_output.put_line('  ....................... Memory for All Objects:  '||TTLBYTES);

     dbms_output.put_line('  ............... Run Only Once:   '||NUM_EX1);

     dbms_output.put_line('  ....................... Memory for Non-Shared Code: '||EX1_MEMORY);

   close c3;

     dbms_output.put_line(' ');

     dbms_output.put_line('   ...Ideal < 20% .... Actual: '||round(100*(EX1_MEMORY / TTLBYTES), 0));

end;

/

prompt** sql versions   **

REM  V$SQLSTATS is new to 10g.

REM  You can increase/decrease the number of versions to look for problems

REM

set pages 300

set lines 100

col sql_id for a15 head "SQL ID"

col sql_text for a30 word_wrapped head "SQL"

col version_count for 999,999

select sql_id,  version_count

from v$sqlstats

where version_count > 5

order by version_count desc

/

REM  

REM   This is a "checksum" type script.  Focusing on versions

REM   or copies of objects in the Library Cache--

REM   What kind of variances do you see between 

REM   V$SQLAREA and V$SQL_SHARED_CURSOR?  

REM     > 0 More versions tracked in V$SQLAREA

REM     < 0 More versions tracked in V$SQL_SHARED_CURSOR

REM

prompt ***Watch for***

PROMPT Variance > 0 means more versions tracked in V$SQLAREA

prompt Variance < 0 means more versions tracked in V$SQL_SHARED_CURSOR

col s_text format a55 word_wrapped head "Object in Memory"

col s_id head "SQL ID"

col VERSIONS_VARIANCE format 999,999,999 head "Variance"

select sql_id s_id,  (SQLAREA_CNT-SHARED_CNT) VERSIONS_VARIANCE 

from (select a.sql_id, a.version_count SQLAREA_CNT, count(*) SHARED_CNT 

from v$sqlarea a, v$sql_shared_cursor c

where a.sql_id=c.sql_id

and a.version_count > 8

group by a.sql_id, a.version_count ) vers

/