oracle 11g SGA report

 




SGA Report 
SGA Info

SGA Report
SGA Info

-------- ====
set lines 132
set pages 999
set termout off
set trimout on
set trimspool on

spool sga.txt

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 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 component, current_size, min_size, max_size, granule_size from v$sga_dynamic_components
/



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 component, last_oper_type, last_oper_mode,to_char(last_oper_time, 'dd/mm/yyyy hh24:mi:ss') lasttime from v$sga_dynamic_components
/


select component, parameter, initial_size, final_size, status, to_char(end_time ,'dd/mm/yyyy hh24:mi:ss') changed from v$sga_resize_ops
/

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 component, min(final_size) low,(min(final_size/1024/1024)) lowMB,max(final_size) high, (max(final_size/1024/1024)) highMB
from v$sga_resize_ops
group by component
/




Component                 Parameter                          Initial            Final Status     Changed At
------------------------- ------------------------- ---------------- ---------------- ---------- -------------------
In-Memory Area            inmemory_size                            0                0 COMPLETE   02/06/2023 11:54:22
DEFAULT buffer cache      db_cache_size                6,375,342,080    6,375,342,080 COMPLETE   02/06/2023 11:54:22
KEEP buffer cache         db_keep_cache_size                       0                0 COMPLETE   02/06/2023 11:54:22
DEFAULT 32K buffer cache  db_32k_cache_size                        0                0 COMPLETE   02/06/2023 11:54:22
DEFAULT 16K buffer cache  db_16k_cache_size                        0                0 COMPLETE   02/06/2023 11:54:22
large pool                large_pool_size                          0   11,140,071,424 COMPLETE   02/06/2023 11:54:22
shared pool               shared_pool_size                         0    2,751,463,424 COMPLETE   02/06/2023 11:54:22
java pool                 java_pool_size                           0      469,762,048 COMPLETE   02/06/2023 11:54:22
DEFAULT 8K buffer cache   db_8k_cache_size                         0                0 COMPLETE   02/06/2023 11:54:22
DEFAULT 4K buffer cache   db_4k_cache_size                         0                0 COMPLETE   02/06/2023 11:54:22
streams pool              streams_pool_size                        0      134,217,728 COMPLETE   02/06/2023 11:54:22
DEFAULT 2K buffer cache   db_2k_cache_size                         0                0 COMPLETE   02/06/2023 11:54:22
DEFAULT buffer cache      db_cache_size                            0    6,375,342,080 COMPLETE   02/06/2023 11:54:22
ASM Buffer Cache          db_cache_size                            0                0 COMPLETE   02/06/2023 11:54:22
RECYCLE buffer cache      db_recycle_cache_size                    0                0 COMPLETE   02/06/2023 11:54:22
large pool                large_pool_size             11,140,071,424    9,462,349,824 COMPLETE   02/06/2023 11:55:26
DEFAULT buffer cache      db_cache_size                6,375,342,080    8,053,063,680 COMPLETE   02/06/2023 11:55:26
DEFAULT buffer cache      db_cache_size                8,120,172,544    8,187,281,408 COMPLETE   02/07/2023 22:01:13
streams pool              streams_pool_size              134,217,728       67,108,864 COMPLETE   02/07/2023 22:01:13
java pool                 java_pool_size                 469,762,048      402,653,184 COMPLETE   02/07/2023 22:01:13
DEFAULT buffer cache      db_cache_size                8,053,063,680    8,120,172,544 COMPLETE   02/07/2023 22:01:13
DEFAULT buffer cache      db_cache_size                8,187,281,408    8,254,390,272 COMPLETE   02/08/2023 22:01:51
java pool                 java_pool_size                 402,653,184      335,544,320 COMPLETE   02/08/2023 22:01:51
shared pool               shared_pool_size             2,751,463,424    2,885,681,152 COMPLETE   02/08/2023 23:00:15
DEFAULT buffer cache      db_cache_size                8,254,390,272    8,120,172,544 COMPLETE   02/08/2023 23:00:15
DEFAULT buffer cache      db_cache_size                8,120,172,544    7,985,954,816 COMPLETE   02/09/2023 03:00:22
shared pool               shared_pool_size             2,885,681,152    3,019,898,880 COMPLETE   02/09/2023 03:00:22
DEFAULT buffer cache      db_cache_size                7,985,954,816    7,851,737,088 COMPLETE   02/09/2023 23:10:05
shared pool               shared_pool_size             3,019,898,880    3,154,116,608 COMPLETE   02/09/2023 23:10:05
shared pool               shared_pool_size             3,154,116,608    3,288,334,336 COMPLETE   02/09/2023 23:14:10
DEFAULT buffer cache      db_cache_size                7,851,737,088    7,717,519,360 COMPLETE   02/09/2023 23:14:10

31 rows selected.



Component                           Lowest   MBytes          Highest   MBytes
------------------------- ---------------- -------- ---------------- --------
java pool                      335,544,320      320      469,762,048      448
DEFAULT buffer cache         6,375,342,080    6,080    8,254,390,272    7,872
DEFAULT 2K buffer cache                  0        0                0        0
DEFAULT 16K buffer cache                 0        0                0        0
DEFAULT 32K buffer cache                 0        0                0        0
KEEP buffer cache                        0        0                0        0
streams pool                    67,108,864       64      134,217,728      128
shared pool                  2,751,463,424    2,624    3,288,334,336    3,136
large pool                   9,462,349,824    9,024   11,140,071,424   10,624
In-Memory Area                           0        0                0        0
ASM Buffer Cache                         0        0                0        0
DEFAULT 8K buffer cache                  0        0                0        0
RECYCLE buffer cache                     0        0                0        0
DEFAULT 4K buffer cache                  0        0                0        0

14 rows selected.




clear breaks
col name format a40 head "Name"
col resizeable format a4 head "Auto?"
select * from v$sgainfo
/



Name                                          BYTES Auto     CON_ID
---------------------------------------- ---------- ---- ----------
Fixed SGA Size                              7654304 No            0
Redo Buffers                               59453440 No            0
Buffer Cache Size                        8254390272 Yes           0
In-Memory Area Size                               0 No            0
Shared Pool Size                         3288334336 Yes           0
Large Pool Size                          9462349824 Yes           0
Java Pool Size                            335544320 Yes           0
Streams Pool Size                          67108864 Yes           0
Shared IO Pool Size                       536870912 Yes           0
Data Transfer Cache Size                          0 Yes           0
Granule Size                               67108864 No            0
Maximum SGA Size                         2.1475E+10 No            0
Startup overhead in Shared Pool           905769256 No            0
Free SGA Memory Available                         0               0

14 rows selected.



spool off
set termout on
set trimout off
set trimspool off
clear col




================


set lines 120
set pages 999
clear col
set termout off
set trimout on
set trimspool on

col "Setting" format 999,999,999,999
col "MBytes" format 999,999
col lifetime format a40 heading "Database Started Last"
spool parameters.out

select to_char(startup_time, 'dd-Mon-yyyy hh24:mi:ss') Lifetime from v$instance;


set lines 120
set pages 999
clear col
set termout off
set trimout on
set trimspool on

col "Setting" format 999,999,999,999
col "Mb" format 999,999
set pagesize 100
spool parameters.out

select 'Shared Pool Size'||':'||decode(value,null,-1,value) "Setting" ,(value/1024/1024) "Mb" from v$parameter where name='shared_pool_size'
union
select 'Shared Pool Reserved Area'||':'||decode(value,null,-1,value) "Setting" ,(value/1024/1024) "Mb" from v$parameter where

name='shared_pool_reserved_size'
union
select 'Results Cache'||':'||decode(value,null,-1,value) "Setting" ,(value/1024/1024) "Mb" from v$parameter where name='result_cache_size'
union
select 'Log Buffer'||':'||decode(value,null,-1,value) "Setting" ,(value/1024/1024) "Mb" from v$parameter where name='log_buffer'
union
select 'PGA Aggregate Target'||':'||decode(value,null,-1,value) "Setting" ,(value/1024/1024) "Mb" from v$parameter where name='pga_aggregate_target'
union
select 'Streams Pool Size'||':'||decode(value,null,-1,value) "Setting" ,(value/1024/1024) "Mb" from v$parameter where name='streams_pool_size'
union
select 'Buffer Cache'||':'||decode(value,null,-1,value) "Setting" ,(value/1024/1024) "Mb" from v$parameter where name='db_cache_size'
union
select 'Recycle Cache'||':'|| decode(value, null,-1,value) "Setting" ,(value/1024/1024) "Mb" from v$parameter where name='db_recycle_cache_size'
union
select 'Keep Cache'||':'|| decode(value, null,-1,value) "Setting" ,(value/1024/1024) "Mb" from v$parameter where name='db_keep_cache_size'
union
select '2K Cache'||':'|| decode(value, null,-1,value) "Setting" ,(value/1024/1024) "Mb" from v$parameter where name='db_2k_cache_size'
union
select '4K Cache'||':'|| decode(value, null,-1,value) "Setting" ,(value/1024/1024) "Mb" from v$parameter where name='db_4k_cache_size'
union
select '8K Cache'||':'|| decode(value, null,-1,value) "Setting" ,(value/1024/1024) "Mb" from v$parameter where name='db_8k_cache_size'
union
select '16K Cache'||':'|| decode(value, null,-1,value) "Setting" ,(value/1024/1024) "Mb" from v$parameter where name='db_16k_cache_size'
union
select '32K Cache'||':'|| decode(value, null,-1,value) "Setting",(value/1024/1024) "Mb" from v$parameter where name='db_32k_cache_size'
union
select 'Memory Target'||':'|| decode(value, null,-1,value) "Setting",(value/1024/1024) "Mb" from v$parameter where name='memory_target'
union
select 'Memory Max Target'||':'|| decode(value, null,-1,value) "Setting",(value/1024/1024) "Mb" from v$parameter where name='memory_max_target'
union
select 'Large Pool Size'||':'|| decode(value,null,-1,value) "Setting" ,(value/1024/1024) "Mb" from v$parameter where name='large_pool_size'
union
select 'Java Pool Size'||':'|| decode(value,null,-1,value) "Setting" ,(value/1024/1024) "Mb" from v$parameter where name='java_pool_size'
union
select 'SGA Max'||':'|| decode(value, null,-1,value) "Setting" ,(value/1024/1024) "Mb" from v$parameter where name='sga_max_size'
union
select 'Memory Max'||':'||decode(value,null,-1,value) "Setting",(value/1024/1024) "Mb" from v$parameter where name='memory_max_target'
union
select 'Memory Target'||':'||decode(value,null,-1,value) "Setting" ,(value/1024/1024) "Mb" from v$parameter where name='memory_target'
union
select 'SGA Target'||':'||decode(value, null,-1,value) "Setting",(value/1024/1024) "Mb" from v$parameter where name='sga_target'
order by 1
/



Setting                                                             Mb
------------------------------------------------------------- --------
16K Cache:0                                                          0
2K Cache:0                                                           0
32K Cache:0                                                          0
4K Cache:0                                                           0
8K Cache:0                                                           0
Buffer Cache:0                                                       0
Java Pool Size:0                                                     0
Keep Cache:0                                                         0
Large Pool Size:0                                                    0
Log Buffer:12558336                                                 12
Memory Max Target:0                                                  0
Memory Max:0                                                         0
Memory Target:0                                                      0
PGA Aggregate Target:4294967296                                  4,096
Recycle Cache:0                                                      0
SGA Max:21474836480                                             20,480
SGA Target:21474836480                                          20,480
Streams Pool Size:0                                                  0

18 rows selected.




col Setting format 999,999,99

select 'Session Cached Cursors'||':'|| decode(value, null,-1,value) "Setting" from v$parameter where name='session_cached_cursors'
union
select 'Open Cursors'||':'||decode(value,null,-1,value) "Setting" from v$parameter where name='open_cursors'
union
select 'Processes'||':'||decode(value,null,-1,value) "Setting" from v$parameter where name='processes'
union
select 'Sessions'||':'||decode(value,null,-1,value) "Setting" from v$parameter where name='sessions'
union
select 'DB Files'||':'||decode(value,null,-1,value) "Setting" from v$parameter where name='db_files'
union
select 'Shared Server (MTS)'||': '||decode(value,null,-1,value) "Setting" from v$parameter where name='shared_server'
order by 1
/



Setting
---------------------------------------------------------------
DB Files:200
Open Cursors:300
Processes:1000
Session Cached Cursors:50
Sessions:1568




col Setting format a30

select 'Cursor Sharing'||':'|| value "Setting" from v$parameter where name='cursor_sharing'
union
select 'Query Rewrite'||':'||value "Setting" from v$parameter where name='query_rewrite_enabled'
union
select 'Result Cache Mode'||':'||value "Setting" from v$parameter where name='result_cache_mode'
union
select 'Statistics Level'||':'||value "Setting" from v$parameter where name='statistics_level'
union
select 'Cache Advice'||':'||value "Setting" from v$parameter where name='db_cache_advice'
union
select 'Compatible'||':'||value "Setting" from v$parameter where name='compatible'
order by 1
/

Setting
------------------------------
Cache Advice:ON
Compatible:12.1.0.2.0
Cursor Sharing:EXACT
Query Rewrite:TRUE
Result Cache Mode:MANUAL
Statistics Level:TYPICAL

6 rows selected.




SQL> 

select resource_name, current_utilization, max_utilization, initial_allocation intl
 from v$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')
 /

RESOURCE_NAME                  CURRENT_UTILIZATION MAX_UTILIZATION INTL
------------------------------ ------------------- --------------- ----------------------------------------
processes                                      522             552       1000
sessions                                        87             147       1568
enqueue_locks                                   68             113      19508
enqueue_resources                               38              66       7116
ges_procs                                        0               0          0
ges_ress                                         0               0          0
ges_locks                                        0               0          0
ges_cache_ress                                   0               0          0
ges_reg_msgs                                     0               0          0
ges_big_msgs                                     0               0          0
ges_rsv_msgs                                     0               0          0
gcs_resources                                    0               0          0
dml_locks                                        0              21       6896
max_shared_servers                               1               1  UNLIMITED

14 rows selected.






col Parameter format a35 wrap
col "Session Value" format a25 wrapped
col "Instance Value" format a25 wrapped
set linesize 200
select 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.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','_io_shared_pool_size',
'_4031_max_dumps','4031_sga_dump_interval','4031_sga_max_dumps',
'_kill_java_threads_on_eoc','_NUMA_pool_size',
'_optim_peek_user_binds','_px_bind_peek_sharing','event','_kgl_heap_size',
'_library_cache_advice','_kglsim_maxmem_percent','_shrunk_aggs_enabled',
'_memory_management_tracing')
order by 1;



Parameter                           Session Value             Instance Value
----------------------------------- ------------------------- -------------------------
_4031_dump_bitvec                   67194879                  67194879
_4031_max_dumps                     100                       100
_NUMA_pool_size                     Not specified             Not specified
_PX_use_large_pool                  FALSE                     FALSE
__db_cache_size                     7717519360                7717519360
__java_pool_size                    335544320                 335544320
__large_pool_size                   9462349824                9462349824
__shared_pool_size                  3288334336                3288334336
__streams_pool_size                 67108864                  67108864
_io_shared_pool_size                4194304                   4194304
_kghdsidx_count                     7                         7
_kgl_heap_size                      4096                      4096
_kglsim_maxmem_percent              5                         5
_kill_java_threads_on_eoc           FALSE                     FALSE
_large_pool_min_alloc               65536                     65536
_library_cache_advice               TRUE                      TRUE
_memory_management_tracing          0                         0
_optim_peek_user_binds              TRUE                      TRUE
_px_bind_peek_sharing               TRUE                      TRUE
_shared_pool_reserved_min_alloc     4400                      4400
_shared_pool_reserved_pct           5                         5
_shrunk_aggs_enabled                TRUE                      TRUE
event


The database buffer cache has three pools:

Default sized by DEFAULT_CACHE_SIZE and is required,
Keep pool sized by DB_KEEP_CACHE_SIZE and used to keep buffers in memory mainly the most used buffers.
Recycle pool sized by DB_RECYCLE_CACHE_SIZE used when you know that buffers will not be used again so best to get them flushed and get the space back.



===========

select
'0 (<140 1000="" 10="" 140="" 20="" 267="" 268="" 4107="" 500="" 50="" 523="" 524="" all="" and="" avg="" between="" bucket="" by="" count="" from="" group="" iggest="" ksmchcls="" ksmchidx="" ksmchsiz="" ksmsp="" max="" otal="" ount="" rom="" select="" sum="" trunc="" union="" vgsize="" where="" x="">= 4108
and
KSMCHCLS='free'
group by
KSMCHCLS, KSMCHIDX, 1000*trunc(KSMCHSIZ/1000);


BUCKET         KSMC   KSMCHIDX       From      Count    Biggest    AvgSize      Total
-------------- ---- ---------- ---------- ---------- ---------- ---------- ----------
6+ (4108+)     free          7     753000          1     753272     753272     753272
6+ (4108+)     free          7     789000          1     789456     789456     789456
6+ (4108+)     free          7     328000          1     328440     328440     328440
6+ (4108+)     free          7     166000          1     166008     166008     166008
6+ (4108+)     free          7     471000          1     471808     471808     471808
6+ (4108+)     free          7     376000          1     376952     376952     376952



SQL> 

select * from v$sgainfo where name = 'Granule Size';

NAME                                  BYTES RES     CON_ID
-------------------------------- ---------- --- ----------
Granule Size                       67108864 No           0




select name, size_for_estimate, size_factor, estd_physical_reads from v$db_cache_advice;


NAME                 SIZE_FOR_ESTIMATE SIZE_FACTOR ESTD_PHYSICAL_READS
-------------------- ----------------- ----------- -------------------
DEFAULT                            704       .0957            66734762
DEFAULT                           1408       .1913            48019247
DEFAULT                           2112        .287            38353636
DEFAULT                           2816       .3826            32951663
DEFAULT                           3520       .4783            29279900
DEFAULT                           4224       .5739            25005807
DEFAULT                           4928       .6696            21400707
DEFAULT                           5632       .7652            18148484
DEFAULT                           6336       .8609            14581830
DEFAULT                           7040       .9565            13448105
DEFAULT                           7360           1            13063864
DEFAULT                           7744      1.0522            12590364
DEFAULT                           8448      1.1478            11561299
DEFAULT                           9152      1.2435            11153562
DEFAULT                           9856      1.3391            10954022
DEFAULT                          10560      1.4348            10876005
DEFAULT                          11264      1.5304            10841156
DEFAULT                          11968      1.6261            10822045
DEFAULT                          12672      1.7217            10810353
DEFAULT                          13376      1.8174            10808105
DEFAULT                          14080       1.913            10807993

21 rows selected.






set linesize 300 pagesize 300 
select
component,
oper_type,
oper_mode,
initial_size/1024/1024 "Initial",
TARGET_SIZE/1024/1024 "Target",
FINAL_SIZE/1024/1024 "Final",
status
from
v$sga_resize_ops order by component;

Component                OPER_TYPE     OPER_MODE    Initial     Target      Final Status
------------------------ ------------- --------- ---------- ---------- ---------- ----------
ASM Buffer Cache         STATIC                           0          0          0 COMPLETE
DEFAULT 16K buffer cache STATIC                           0          0          0 COMPLETE
DEFAULT 2K buffer cache  STATIC                           0          0          0 COMPLETE
DEFAULT 32K buffer cache STATIC                           0          0          0 COMPLETE
DEFAULT 4K buffer cache  STATIC                           0          0          0 COMPLETE
DEFAULT 8K buffer cache  STATIC                           0          0          0 COMPLETE
DEFAULT buffer cache     SHRINK        DEFERRED        7616       7488       7488 COMPLETE
DEFAULT buffer cache     GROW          DEFERRED        7680       7744       7744 COMPLETE
DEFAULT buffer cache     SHRINK        DEFERRED        7488       7360       7360 COMPLETE
DEFAULT buffer cache     GROW          DEFERRED        7744       7808       7808 COMPLETE
DEFAULT buffer cache     S




SELECT component, current_size/1024/1024 as size_mb, min_size/1024/1024 as min_size_mb FROM v$sga_dynamic_components
WHERE  1=1 
--current_size > 0
ORDER BY component;


Component                   SIZE_MB MIN_SIZE_MB
------------------------ ---------- -----------
ASM Buffer Cache                  0           0
DEFAULT 16K buffer cache          0           0
DEFAULT 2K buffer cache           0           0
DEFAULT 32K buffer cache          0           0
DEFAULT 4K buffer cache           0           0
DEFAULT 8K buffer cache           0           0
DEFAULT buffer cache           7360        6080
Data Transfer Cache               0           0
In-Memory Area                    0           0
KEEP buffer cache                 0           0
RECYCLE buffer cache              0           0
Shared IO Pool                  512         512
java pool                       320         320
large pool                     9024        9024
shared pool                    3136        2624
streams pool                     64          64

16 rows selected.



Display the 20 hottest blocks


set linesize 300 pagesize 300 
 col what for a50

select tch, file#, dbablk,
case when obj = 4294967295
then 'rbs/compat segment'
else (select max( '('||object_type||') ' ||owner || '.' || object_name ) ||decode( count(*), 1, '', ' maybe!' )
from dba_objects
where data_object_id = X.OBJ )
end what
from (
select tch, file#, dbablk, obj
from x$bh
where state <> 0
order by tch desc
) x
where rownum <= 20 ;




       TCH      FILE#     DBABLK WHAT
---------- ---------- ---------- --------------------------------------------------
       255          1     246159 (INDEX) SYS.I_OBJ5
       255          1       4410 (TABLE) SYS.KOTTD$
       255          1      81849 (TABLE) SYS.UET$ maybe!
       255          1      28724 (INDEX) SYS.I_OBJ5
       255          4     257384 rbs/compat segment
       255          1     237112 (TABLE) SYS.UET$ maybe!
     



Set a table to use the recycle pool
alter table emp(storage buffer_pool recycle); (need to check)

Set a index to use the keep pool

alter index emp_name_idx(storage buffer_pool keep);

(need to check)

Buffer cache size factoring




select name, size_for_estimate, size_factor, estd_physical_reads
from v$db_cache_advice;

ame                                     SIZE_FOR_ESTIMATE SIZE_FACTOR ESTD_PHYSICAL_READS
---------------------------------------- ----------------- ----------- -------------------
DEFAULT                                                704       .0957            66782249
DEFAULT                                               1408       .1913            48053417
DEFAULT                                               2112        .287            38380928
DEFAULT                                               2816       .3826            32975111
DEFAULT                                               3520       .4783            29300735
DEFAULT                                               4224       .5739            25023600
DEFAULT                                               4928       .6696            21415935
DEFAULT                                               5632       .7652            18161398
DEFAULT                                               6336       .8609            14592207
DEFAULT                                               7040       .9565            13457675
DEFAULT                                               7360           1            13073160
DEFAULT                                               7744      1.0522            12599323
DEFAULT                                               8448      1.1478            11569526
DEFAULT                                               9152      1.2435            11161499
DEFAULT                                               9856      1.3391            10961817
DEFAULT                                              10560      1.4348            10883744
DEFAULT                                              11264      1.5304            10848870
DEFAULT                                              11968      1.6261            10829745
DEFAULT                                              12672      1.7217            10818046
DEFAULT                                              13376      1.8174            10815796
DEFAULT                                              14080       1.913            10815683

21 rows selected.



===========================================================================================





set linesize 300 pagesize 500


set term on

COLUMN inst_id             HEADING "Inst#"          FORMAT 99999 
COLUMN name                HEADING "Name"           FORMAT a21
COLUMN current_value1      HEADING "Current"        FORMAT a10 
COLUMN spfile_value1       HEADING "SPFile"         FORMAT a10 
COLUMN current_value2      HEADING "Current"        FORMAT a10 
COLUMN spfile_value2       HEADING "SPFile"         FORMAT a10 
COLUMN current_value3      HEADING "Current"        FORMAT a10 
COLUMN spfile_value3       HEADING "SPFile"         FORMAT a10 
COLUMN current_value4      HEADING "Current"        FORMAT a10 
COLUMN spfile_value4       HEADING "SPFile"         FORMAT a10 
COLUMN current_value5      HEADING "Current"        FORMAT a10 
COLUMN spfile_value5       HEADING "SPFile"         FORMAT a10 
COLUMN current_value6      HEADING "Current"        FORMAT a10 
COLUMN spfile_value6       HEADING "SPFile"         FORMAT a10 
COLUMN current_value7      HEADING "Current"        FORMAT a10 
COLUMN spfile_value7       HEADING "SPFile"         FORMAT a10 
COLUMN current_value8      HEADING "Current"        FORMAT a10 
COLUMN spfile_value8       HEADING "SPFile"         FORMAT a10 


COLUMN component           HEADING "Component"             FORMAT a24
COLUMN user_specified_size HEADING "User|Specified|(MB)"   FORMAT 99,999,999
COLUMN current_size        HEADING "Current|(MB)"          FORMAT 99,999,999
COLUMN free_size           HEADING "Free|(MB)"             FORMAT 99,999,999
COLUMN min_size            HEADING "Min|(MB)"              FORMAT 99,999,999
COLUMN max_size            HEADING "Max|(MB)"              FORMAT 99,999,999
COLUMN GRANULE_SIZE        HEADING "Granule|(MB)"          FORMAT 9,999
COLUMN last_oper_type      HEADING "Last|Operation|Type"   FORMAT a12
COLUMN oper_count          HEADING "Operation|Count"       FORMAT 99,999,999
COLUMN last_oper_time      HEADING "Last|Operation|Time"   FORMAT a18 

BREAK ON REPORT
COMPUTE SUM LABEL 'Total' OF current_size FORMAT 99,999,999  ON REPORT 
COMPUTE SUM LABEL 'Total' OF free_size    FORMAT 99,999,999  ON REPORT 

PROMPT  
PROMPT  ####################################################################
PROMPT  #######                                                      #######
PROMPT  #######    Automatic Shared Memory Management Settings       #######
PROMPT  #######                                                      #######
PROMPT  ####################################################################
PROMPT  



select RPAD(pp.name,20) ||  '|' name
     , MAX(DECODE(pp.inst_id, 1, pp.display_value,NULL))                                                        current_value1
     , MAX(DECODE(pp.inst_id, 1, RPAD(NVL(NVL(sp.display_value,sp_generic.display_value),' '),9) || '|' ,NULL)) spfile_value1
     , MAX(DECODE(pp.inst_id, 2, pp.display_value,NULL))                                                        current_value2
     , MAX(DECODE(pp.inst_id, 2, RPAD(NVL(NVL(sp.display_value,sp_generic.display_value),' '),9) || '|' ,NULL)) spfile_value2
     , MAX(DECODE(pp.inst_id, 3, pp.display_value,NULL))                                                        current_value3
     , MAX(DECODE(pp.inst_id, 3, RPAD(NVL(NVL(sp.display_value,sp_generic.display_value),' '),9) || '|' ,NULL)) spfile_value3
     , MAX(DECODE(pp.inst_id, 4, pp.display_value,NULL))                                                        current_value4
     , MAX(DECODE(pp.inst_id, 4, RPAD(NVL(NVL(sp.display_value,sp_generic.display_value),' '),9) || '|' ,NULL)) spfile_value4
     , MAX(DECODE(pp.inst_id, 5, pp.display_value,NULL))                                                        current_value5
     , MAX(DECODE(pp.inst_id, 5, RPAD(NVL(NVL(sp.display_value,sp_generic.display_value),' '),9) || '|' ,NULL)) spfile_value5
     , MAX(DECODE(pp.inst_id, 6, pp.display_value,NULL))                                                        current_value6
     , MAX(DECODE(pp.inst_id, 6, RPAD(NVL(NVL(sp.display_value,sp_generic.display_value),' '),9) || '|' ,NULL)) spfile_value6
     , MAX(DECODE(pp.inst_id, 7, pp.display_value,NULL))                                                        current_value7
     , MAX(DECODE(pp.inst_id, 7, RPAD(NVL(NVL(sp.display_value,sp_generic.display_value),' '),9) || '|' ,NULL)) spfile_value7
     , MAX(DECODE(pp.inst_id, 8, pp.display_value,NULL))                                                        current_value8
     , MAX(DECODE(pp.inst_id, 8, RPAD(NVL(NVL(sp.display_value,sp_generic.display_value),' '),9) || '|' ,NULL)) spfile_value8
from gv$system_parameter pp
     JOIN gv$instance i ON pp.inst_id = i.inst_id
     LEFT OUTER JOIN gv$spparameter sp ON pp.inst_id = sp.inst_id AND   sp.sid =  i.instance_name and   pp.name = sp.name 
     LEFT OUTER JOIN gv$spparameter sp_generic ON sp_generic.inst_id = pp.inst_id AND sp_generic.sid =  '*' and sp_generic.name  = pp.name
WHERE (  pp.name in ('memory_target'
                   ,'memory_max_target'
                   ,'sga_target'
                   ,'sga_max_size'
                   ,'lock_sga'
                   ,'pre_page_sga'
                   ,'pga_aggregate_target'
                   ,'large_pool_size'
                   ,'use_large_pages'
                   )
       )
GROUP BY pp.name
UNION ALL
-- Get Host physical memory
select RPAD(os.stat_name,20) ||  '|' name
     , MAX(DECODE(os.inst_id, 1, ROUND(os.value/1024/1024/1024) || 'G',NULL))                          current_value1
     , MAX(DECODE(os.inst_id, 1, RPAD(NVL(ROUND(os.value/1024/1024/1024) || 'G',' '),9) || '|' ,NULL)) spfile_value1
     , MAX(DECODE(os.inst_id, 2, ROUND(os.value/1024/1024/1024) || 'G',NULL))                          current_value2
     , MAX(DECODE(os.inst_id, 2, RPAD(NVL(ROUND(os.value/1024/1024/1024) || 'G',' '),9) || '|' ,NULL)) spfile_value2
     , MAX(DECODE(os.inst_id, 3, ROUND(os.value/1024/1024/1024) || 'G',NULL))                          current_value3
     , MAX(DECODE(os.inst_id, 3, RPAD(NVL(ROUND(os.value/1024/1024/1024) || 'G',' '),9) || '|' ,NULL)) spfile_value3
     , MAX(DECODE(os.inst_id, 4, ROUND(os.value/1024/1024/1024) || 'G',NULL))                          current_value4
     , MAX(DECODE(os.inst_id, 4, RPAD(NVL(ROUND(os.value/1024/1024/1024) || 'G',' '),9) || '|' ,NULL)) spfile_value4
     , MAX(DECODE(os.inst_id, 5, ROUND(os.value/1024/1024/1024) || 'G',NULL))                          current_value5
     , MAX(DECODE(os.inst_id, 5, RPAD(NVL(ROUND(os.value/1024/1024/1024) || 'G',' '),9) || '|' ,NULL)) spfile_value5
     , MAX(DECODE(os.inst_id, 6, ROUND(os.value/1024/1024/1024) || 'G',NULL))                          current_value6
     , MAX(DECODE(os.inst_id, 6, RPAD(NVL(ROUND(os.value/1024/1024/1024) || 'G',' '),9) || '|' ,NULL)) spfile_value6
     , MAX(DECODE(os.inst_id, 7, ROUND(os.value/1024/1024/1024) || 'G',NULL))                          current_value7
     , MAX(DECODE(os.inst_id, 7, RPAD(NVL(ROUND(os.value/1024/1024/1024) || 'G',' '),9) || '|' ,NULL)) spfile_value7
     , MAX(DECODE(os.inst_id, 8, ROUND(os.value/1024/1024/1024) || 'G',NULL))                          current_value8
     , MAX(DECODE(os.inst_id, 8, RPAD(NVL(ROUND(os.value/1024/1024/1024) || 'G',' '),9) || '|' ,NULL)) spfile_value8
from gv$osstat os
where os.stat_name = 'PHYSICAL_MEMORY_BYTES'
GROUP BY os.stat_name
ORDER BY 1
/


BREAK ON inst_id SKIP 1

SELECT c.inst_id 
     , c.component
     , ROUND(c.user_specified_size / 1024 / 1024) user_specified_size
     , ROUND(c.current_size        / 1024 / 1024) current_size
     , ROUND(NVL(s.bytes,0)        / 1024 / 1024) free_size
     , ROUND(c.min_size            / 1024 / 1024) min_size
     , ROUND(c.max_size            / 1024 / 1024) max_size
     , ROUND(c.GRANULE_SIZE        / 1024 / 1024) GRANULE_SIZE
     , c.oper_count
     , c.last_oper_type 
     , to_char(c.last_oper_time,'DD-MON-YY hh24:mi:Ss') last_oper_time
FROM  gv$sga_dynamic_components c
    , GV$SGASTAT s
WHERE c.inst_id = s.inst_id (+)
  AND c.component = s.pool (+)
  AND s.name (+) = 'free memory'  
  AND c.current_size <> 0   
ORDER BY c.inst_id , c.component 
/




Name                  Current    SPFile     Current    SPFile     Current    SPFile     Current    SPFile     Current    SPFile     Current    SPFile          Current    SPFile     Current    SPFile
--------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
PHYSICAL_MEMORY_BYTE| 755G       755G     |
large_pool_size     | 0                   |
lock_sga            | FALSE               |
memory_max_target   | 0                   |
memory_target       | 0                   |
pga_aggregate_target| 4G                  |
pre_page_sga        | TRUE                |
sga_max_size        | 20G        20G      |
sga_target          | 20G        20G      |
use_large_pages     | TRUE                |

10 rows selected.




                                       User                                                                     Last         Last
                                  Specified     Current        Free         Min         Max Granule   Operation Operation    Operation
 Inst# Component                       (MB)        (MB)        (MB)        (MB)        (MB)    (MB)       Count Type         Time
------ ------------------------ ----------- ----------- ----------- ----------- ----------- ------- ----------- ------------ ------------------
     1 DEFAULT buffer cache               0       7,360           0       6,080       7,872      64           8 SHRINK       09-FEB-23 23:14:10
       Shared IO Pool                   512         512           0         512         512      64           0 STATIC
       java pool                          0         320         320         320         448      64           2 SHRINK       08-FEB-23 22:01:51
       large pool                         0       9,024         521       9,024      10,624      64           1 SHRINK       06-FEB-23 11:55:26
       shared pool                        0       3,136         583       2,624       3,136      64           4 GROW         09-FEB-23 23:14:10
       streams pool                       0          64          64          64         128      64           1 SHRINK       07-FEB-23 22:01:13


6 rows selected.



SELECT * FROM v$sga_target_advice ORDER BY sga_size ASC;

 SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR ESTD_PHYSICAL_READS ESTD_BUFFER_CACHE_SIZE ESTD_SHARED_POOL_SIZE     CON_ID
---------- --------------- ------------ ------------------- ------------------- ---------------------- --------------------- ----------
      2560            .125         7795               .2989            13082660                   7744                  3456          0
      5120             .25         7795               .2989            13082660                   7744                  3456          0
      7680            .375         7795               .2989            13082660                   7744                  3456          0
     10240              .5         7795               .2989            13082660                   7744                  3456          0
     12800            .625       164900              6.3231            69347256                    704                  2176          0
     15360             .75        51869              1.9889            34242554                   2816                  2944          0
     17920            .875        33918              1.3006            18858654                   5632                  2880          0
     20480               1        26079                   1            13082660                   7744                  3456          0
     23040           1.125        23967                .919            11300802                  10560                  3328          0
     25600            1.25        23839               .9141            11245855                  11968                  4416          0
     28160           1.375        23805               .9128            11230155                  13376                  5696          0
     30720             1.5        23805               .9128            11230155                  14080                  6016          0
     33280           1.625        23805               .9128            11230155                  16896                  6016          0
     35840            1.75        23805               .9128            11230155                  19008                  6016          0
     38400           1.875        23805               .9128            11230155                  21824                  6016          0
     40960               2        23805               .9128            11230155                  23936                  6016          0

16 rows selected.



select snap_id, sga_size from dba_hist_sga_target_advice
where sga_size_factor = 1
order by snap_id asc




WITH snaps as (
select snap_id, begin_interval_time from dba_hist_snapshot),
SGAAdviceCur as (select snap_id, sga_size, estd_db_time, estd_physical_reads from dba_hist_sga_target_advice where SGA_SIZE_FACTOR=1),
SGAAdviceNew as (select snap_id, sga_size as sga_size_new, 
                      estd_db_time as estd_db_time_new, 
                      estd_physical_reads as estd_physical_reads_new 
                 from dba_hist_sga_target_advice 
                where SGA_SIZE_FACTOR=2)
SELECT snaps.SNAP_ID,
       TO_CHAR(snaps.BEGIN_INTERVAL_TIME,'MM/DD/YYYY HH24:MI') as RecordDate, 
       SGAAdviceCur.SGA_SIZE, SGAAdviceNew.SGA_SIZE_NEW,
       SGAAdviceCur.ESTD_DB_TIME, SGAAdviceNew.ESTD_DB_TIME_NEW,
       ROUND(SGAAdviceNew.ESTD_DB_TIME_NEW/SGAAdviceCur.ESTD_DB_TIME*100,2) as NewTimePct,
       SGAAdviceCur.ESTD_PHYSICAL_READS, SGAAdviceNew.ESTD_PHYSICAL_READS_NEW,
       ROUND(SGAAdviceNew.ESTD_PHYSICAL_READS_NEW/SGAAdviceCur.ESTD_PHYSICAL_READS*100,2) as NewReadsPct
from snaps left outer join SGAAdviceCur on snaps.snap_id=SGAAdviceCur.snap_id
         left outer join SGAAdviceNew on snaps.snap_id = SGAAdviceNew.snap_id
 ORDER BY begin_interval_time;


 SNAP_ID RECORDDATE         SGA_SIZE SGA_SIZE_NEW ESTD_DB_TIME ESTD_DB_TIME_NEW NEWTIMEPCT ESTD_PHYSICAL_READS ESTD_PHYSICAL_READS_NEW NEWREADSPCT
---------- ---------------- ---------- ------------ ------------ ---------------- ---------- ------------------- ----------------------- -----------
      5172 02/02/2023 00:00      20480        40960        28092            28092        100             2850986                 2850986     100
      5173 02/02/2023 01:01      20480        40960        28207            28207        100             2983262                 2983262     100
      5174 02/02/2023 02:00      20480        40960        28335            28335        100             3115895                 3115895     100
      5175 02/02/2023 03:00      20480        40960        28630            28630        100             3240857                 3240857     100
      5176 02/02/2023 04:00      20480        40960        28809            28809        100             3372947                 3372947     100
      5177 02/02/2023 05:00      20480        40960        28938            28938        100             3504665                 3504665     100
      5178 02/02/2023 06:01      20480        40960        29054            29054        100             3636384                 3636384     100





VARIABLE BgnSnap NUMBER
VARIABLE EndSnap NUMBER
VARIABLE DID NUMBER
VARIABLE INST_NUMBER number
exec select max(snap_id) -2 into :BgnSnap from dba_hist_snapshot ;
exec select max(snap_id) into :EndSnap from dba_hist_snapshot ;
exec select DBID into :DID from v$database;
exec select INSTANCE_NUMBER into :INST_NUMBER from v$instance ;



SELECT snap_id,
    instance_number,
    MAX (DECODE (stat_name, 'SGA', stat_value, NULL)) "SGA",
    MAX (DECODE (stat_name, 'PGA', stat_value, NULL)) "PGA",
    MAX (DECODE (stat_name, 'SGA', stat_value, NULL)) + MAX (DECODE (stat_name, 'PGA', stat_value,
    NULL)) "TOTAL"
   FROM
    (SELECT snap_id,
        instance_number,
        ROUND (SUM (bytes) / 1024 / 1024 / 1024, 1) stat_value,
        MAX ('SGA') stat_name
       FROM dba_hist_sgastat
      WHERE dbid = :DID
        AND snap_id BETWEEN :BgnSnap AND :EndSnap
   GROUP BY snap_id,
        instance_number
  UNION ALL
     SELECT snap_id,
        instance_number,
        ROUND (value / 1024 / 1024 / 1024, 1) stat_value,
        'PGA' stat_name
       FROM dba_hist_pgastat
      WHERE dbid = :DID
        AND snap_id BETWEEN :BgnSnap AND :EndSnap
        AND NAME = 'total PGA allocated'
    )
GROUP BY snap_id,
    instance_number
ORDER BY snap_id,
    instance_number;




   SNAP_ID INSTANCE_NUMBER        SGA        PGA      TOTAL
---------- --------------- ---------- ---------- ----------
      5369               1       19.5         .8       20.3
      5370               1       19.5         .9       20.4
      5371               1       19.5         .9       20.4

SQL>


select snap_id,instance_number,sga_target_gb,size_factor,ESTD_PHYSICAL_READS,lead_read_diff
from(
with top_n_dbtime as(
select snap_id from(
select snap_id, sum(average) dbtime_p_s,
  dense_rank() over (order by sum(average) desc nulls last) rnk
 from dba_hist_sysmetric_summary
where dbid = :DID
 and snap_id between :BgnSnap AND :EndSnap
 and metric_name = 'Database Time Per Sec'
 group by snap_id)
 where rnk <= 10)
SELECT a.SNAP_ID,
  INSTANCE_NUMBER,
  ROUND(sga_size/1024,1) sga_target_gb,
  sga_size_FACTOR size_factor,
  ESTD_PHYSICAL_READS,
  round((ESTD_PHYSICAL_READS - lead(ESTD_PHYSICAL_READS,1,ESTD_PHYSICAL_READS) over (partition by a.snap_id,instance_number order by sga_size_FACTOR asc nulls last)),1) lead_read_diff,
  min(sga_size_FACTOR) over (partition by a.snap_id,instance_number) min_factor,
  max(sga_size_FACTOR) over (partition by a.snap_id,instance_number) max_factor
FROM DBA_HIST_SGA_TARGET_ADVICE a,top_n_dbtime tn
WHERE dbid          = :DID
AND a.snap_id         = tn.snap_id)
where (size_factor = 1
or size_factor = min_factor
or size_factor = max_factor
or lead_read_diff > 1)
order by snap_id asc,instance_number, size_factor asc nulls last;



   SNAP_ID INSTANCE_NUMBER SGA_TARGET_GB SIZE_FACTOR ESTD_PHYSICAL_READS LEAD_READ_DIFF
---------- --------------- ------------- ----------- ------------------- --------------
      5369               1           2.5        .125            12858814              0
      5369               1          12.5        .625            68141427       34487339
      5369               1            15         .75            33654088       15118108
      5369               1          17.5        .875            18535980        5677166
      5369               1            20           1            12858814        1751370
      5369               1          22.5       1.125            11107444          55293
      5369               1            25        1.25            11052151          14145
      5369      



SELECT SNAP_ID,
  INSTANCE_NUMBER,
  PGA_TARGET_GB,
  SIZE_FACTOR,
  ESTD_EXTRA_MB_RW,
  LEAD_SIZE_DIFF_MB,
  ESTD_PGA_CACHE_HIT_PERCENTAGE
FROM
  ( WITH top_n_dbtime AS
  (SELECT snap_id
  FROM
    (SELECT snap_id,
      SUM(average) dbtime_p_s,
      dense_rank() over (order by SUM(average) DESC nulls last) rnk
    FROM dba_hist_sysmetric_summary
      where dbid = :DID
      and snap_id between :BgnSnap AND :EndSnap
    AND metric_name = 'Database Time Per Sec'
    GROUP BY snap_id
    )
  WHERE rnk <= 10
  )
SELECT a.SNAP_ID,
  INSTANCE_NUMBER,
  ROUND(PGA_TARGET_FOR_ESTIMATE/1024/1024/1024,1) pga_target_gb,
  PGA_TARGET_FACTOR size_factor,
  ROUND(ESTD_EXTRA_BYTES_RW  /1024/1024,1) ESTD_EXTRA_MB_RW,
  ROUND((ESTD_EXTRA_BYTES_RW - lead(ESTD_EXTRA_BYTES_RW,1,ESTD_EXTRA_BYTES_RW) over (partition BY a.snap_id,instance_number order by PGA_TARGET_FACTOR ASC nulls last))/1024/1024,1) lead_size_diff_mb,
  ESTD_PGA_CACHE_HIT_PERCENTAGE,
  MIN(PGA_TARGET_FACTOR) over (partition BY a.snap_id,instance_number) min_factor,
  MAX(PGA_TARGET_FACTOR) over (partition BY a.snap_id,instance_number) max_factor
FROM DBA_HIST_PGA_TARGET_ADVICE a,
  top_n_dbtime tn
WHERE dbid = :DID
AND a.snap_id = tn.snap_id
  )
WHERE (size_factor   = 1
OR size_factor       = min_factor
OR size_factor       = max_factor
OR lead_size_diff_mb > 1)
ORDER BY snap_id ASC,
  instance_number,
  size_factor ASC nulls last;



   SNAP_ID INSTANCE_NUMBER PGA_TARGET_GB SIZE_FACTOR ESTD_EXTRA_MB_RW LEAD_SIZE_DIFF_MB ESTD_PGA_CACHE_HIT_PERCENTAGE
---------- --------------- ------------- ----------- ---------------- ----------------- -----------------------------
      5369               1            .5        .125          47128.5           46361.6                            97
      5369               1             1         .25            766.9             766.9                           100
      5369               1             4           1                0                 0                           100
      5369               1            32           8                0                 0                           100
      5370               1            .5        .125          47230.5           46463.6                            97
      5370               1             1         .25            766.9             766.9                           100
      5370               1             4           1                0                 0                           100
      5370               1            32           8                0                 0                           100
      5371               1            .5        .125            47324           46557.1                            97
      5371               1             1         .25            766.9             766.9                           100
      5371               1             4           1                0                 0                           100
      5371               1            32           8                0                 0                           100

12 rows selected.

<140 1000="" 10="" 140="" 20="" 267="" 268="" 4107="" 500="" 50="" 523="" 524="" all="" and="" avg="" between="" bucket="" by="" count="" from="" group="" iggest="" ksmchcls="" ksmchidx="" ksmchsiz="" ksmsp="" max="" otal="" ount="" rom="" select="" sum="" trunc="" union="" vgsize="" where="" x="">

<140 1000="" 10="" 140="" 20="" 267="" 268="" 4107="" 500="" 50="" 523="" 524="" all="" and="" avg="" between="" bucket="" by="" count="" from="" group="" iggest="" ksmchcls="" ksmchidx="" ksmchsiz="" ksmsp="" max="" otal="" ount="" rom="" select="" sum="" trunc="" union="" vgsize="" where="" x="">
select COMPONENT,OPER_TYPE,PARAMETER,INITIAL_SIZE,TARGET_SIZE,FINAL_SIZE,STATUS,START_TIME 
from v$sga_resize_ops order by START_TIME;

select round(used.bytes /1024/1024 ,2) used_mb
, round(free.bytes /1024/1024 ,2) free_mb
, round(tot.bytes /1024/1024 ,2) total_mb
from (select sum(bytes) bytes
from v$sgastat
where name != 'free memory') used
, (select sum(bytes) bytes
from v$sgastat
where name = 'free memory') free
, (select sum(bytes) bytes
from v$sgastat) tot ;


Select POOL, Round(bytes/1024/1024,0) Free_Memory_In_MB From V$sgastat Where Name Like '%free memory%';


select value/1024/1024 shared_pool_size
from v$parameter
where name = 'shared_pool_size';

<140 1000="" 10="" 140="" 20="" 267="" 268="" 4107="" 500="" 50="" 523="" 524="" all="" and="" avg="" between="" bucket="" by="" count="" from="" group="" iggest="" ksmchcls="" ksmchidx="" ksmchsiz="" ksmsp="" max="" otal="" ount="" rom="" select="" sum="" trunc="" union="" vgsize="" where="" x="">===set linesize 156
set trimspool on
set pagesize  60
set tab off
 
column indx             format 999
column component        format a20
column cursize          format 9,999
column gransize         format 999,999,999,999
column grantype         format 999
column granstate        format a10
 
column ct               format 9,999
column total_memory     format 999,999,999,999
 
break on report
compute sum of total_memory on report
 
select
        sct.indx, sct.component, sct.cursize, 
        ge.gransize, ge.grantype, ge.granstate, ct,
        ge.gransize * sct.cursize total_memory
from
        x$kmgsct        sct,
        (
        select
                ge.grantype, ge.granstate, ge.gransize,
                count(*) ct
        from
                x$ksmge         ge
        group by
                ge.grantype, ge.granstate, ge.gransize
        )       ge
where
        ge.grantype(+) = sct.grantype
and     sct.cursize != 0
order by
        sct.indx, sct.component, ge.granstate
;



egrep -i "SHARED GLOBAL AREA" /u01/app/oracle/diag/rdbms/xxx/xxx/trace/alert_xxx.log

grep 'Dump of system resources acquired for SHARED GLOBAL AREA' -B1 -A22  /u01/app/oracle/diag/rdbms/xxx/xxx/trace/alert_xxx.log

 PAGESIZE  AVAILABLE_PAGES  EXPECTED_PAGES  ALLOCATED_PAGES  ERROR(s)
2023-02-08T09:41:38.527298+00:00
        4K       Configured               7        14279521        NONE
2023-02-08T09:41:38.527493+00:00
     2048K            64368           92161           64271        NONE
2023-02-08T09:41:38.527544+00:00

====

 ps -ef|grep -i memleak

 ps -ef|grep -i memleak.sh
oracle    5310 32113  0 16:44 pts/0    00:00:00 grep -i memleak.sh
oracle   19647 32113  0 16:39 pts/0    00:00:00 /bin/sh ./memleak.sh 45




./memleak.sh 45 > memleak.out &   ---- 45 second 

memleak.sh

#!/bin/sh
until test 0 -eq 1
do
sqlplus /nolog <<END
connect / as sysdba
@pga.sql
END
sleep $1
done


sql !!!

-- pga.sql


set linesize 300 pagesize 300


select to_char(sysdate, 'dd-MON-yyyy hh24:mi:ss') "Script Run Time" from dual;

select INSTANCE_NUMBER,INSTANCE_NAME,HOST_NAME from v$instance;

PROMPT
PROMPT V$SESSSTAT MEMORY INFO
PROMPT -------------------------

--Monitor the pga usage for all processes related to an instance from v$sesstat.
--Look at trends of individual processes growing in size or high number of processes
REM v$sesstat  pga/uga memory size

select p.spid, s.sid, substr(n.name,1,25) memory, s.value as Bytes from v$sesstat s, v$statname n, v$process p, v$session vs
where s.statistic# = n.statistic#
/* this query currently looks at both uga and pga, if only one of these is desired modify the like clause to pga or uga */
and n.name like '%ga memory%'
and s.sid=vs.sid
and vs.paddr=p.addr
/* --remove comment delimiters to view only certain sizes, i.e. over 10Mbytes */
/* and s.value > 10000000 */
order by s.value asc;

PROMPT
PROMPT LARGEST PGA_ALLOC_MEM PROCESS NOT LIKE LGWR
PROMPT -------------------------

REM List Largest process.

/* Do Not eliminate all background process because certain background processes do need to be monitored at times */
select pid,spid,substr(username,1,20) "USER" ,program,PGA_USED_MEM,PGA_ALLOC_MEM,PGA_FREEABLE_MEM,PGA_MAX_MEM
from v$process
where pga_alloc_mem=(select max(pga_alloc_mem) from v$process
where program not like '%LGWR%');

PROMPT
PROMPT SELECT SUM(PGA_ALLOC_MEM) sum(PGA_USED_MEM) FROM V$PROCESS
PROMPT -------------------------

REM Summation of pga based on v$process
REM allocated includes free PGA memory not yet released to the operating system by the server process
select sum(pga_alloc_mem)/1024/1024 as "Mbytes Alloc", sum(PGA_USED_MEM)/1024/1024 as "Mbytes used" from v$process;

PROMPT
PROMPT SELECT SUM PGA MEMORY FROM V$SESSTAT
PROMPT -------------------------

REM Summation of pga memory based on v$sesstat
select sum(value)/1024/1024 as "Mbytes" from v$sesstat s, v$statname n
        where
        n.STATISTIC# = s.STATISTIC# and
        n.name = 'session pga memory';

PROMPT
PROMPT SELECT * FROM V$PGASTAT
PROMPT -------------------------

REM PGA stats from v$pgastat
select substr(name,1,30), value, unit from v$pgastat;


PROMPT
PROMPT SHOW INFO on ALL PROCESSES
PROMPT -------------------------

--List all processes including pga size from v$process
--Outer join will show if any defunct processes exist without associated session.
set linesize 300 pagesize 300
column spid heading 'OSpid' format a8
column pid heading 'Orapid' format 999999
column sid heading 'Sess id' format 99999
column serial# heading 'Serial#' format 999999
column status heading 'Status' format a8
column pga_alloc_mem heading 'PGA alloc' format 99,999,999,999
column pga_used_mem heading 'PGA used' format 99,999,999,999
column username heading 'oracleuser' format a12
column osuser heading 'OS user' format a12
column program heading 'Program' format a20

SELECT
p.spid,
p.pid,
s.sid,
s.serial#,
s.status,
s.sql_id,
p.pga_alloc_mem,
p.PGA_USED_MEM,
s.username,
s.osuser,
s.program,
s.event
FROM
v$process p,
v$session s
WHERE s.paddr ( + ) = p.addr
and p.BACKGROUND is null /* Comment out this line if need to monitor background processes */
Order by p.pga_alloc_mem desc;


PROMPT
PROMPT SUM of PGA and SGA FROM V$SESSTAT,V$SGA
PROMPT -------------------------

--Summation of pga and sga gives a value of total memory usage by oracle instance
--look at total memory used by instance SGA and PGA

select sum(bytes)/1024/1024 as "Total PGA+SGA Mbytes" from
        (select value as bytes from v$sga
        union all
        select value as bytes from
        v$sesstat s,
        v$statname n
        where
        n.STATISTIC# = s.STATISTIC# and
        n.name = 'session pga memory'
        );



clear breaks

Oracle Drop all the object from user

drop all the table
drop all the object from schema
drop all the table from own schema

SQL> connect scott/tiger
Connected.



begin

for i in ( select * from user_objects ) loop

EXECUTE immediate 'drop '|| i.object_type||' "'||i.object_name || '" ' ;

-- dbms_output.put_line ('drop '|| i.object_type||' "'||i.object_name || '" ;' );
-- dbms_output.put_line(i.object_type ||'.'||i.object_name ) ;

end loop;
end;

/

PL/SQL procedure successfully completed.




from sys

delete all the object from sys for other schema


begin

for i in ( select * from dba_objects where owner='BOAPTUS' and object_type in ('TABLE' ) ) loop

EXECUTE immediate 'drop '|| i.object_type||' '|| i.owner||'.'||'"'||i.object_name || '" ' ;

-- dbms_output.put_line ('drop '|| i.object_type||' "'||i.object_name || '" ;' );
-- dbms_output.put_line(i.object_type ||'.'||i.object_name ) ;

end loop;
end;

/





sql> select OBJECT_TYPE,count(*) from dba_objects
where OWNER='BOAPTUS'
group by OBJECT_TYPE ;

OBJECT_TYPE COUNT(*)
------------------- --------------------
LOB 4

http://anuj-singh.blogspot.com/2011/07/oracle-lob-delete.html


sql>purge dba_recyclebin;

DBA Recyclebin purged.

sql>

select OBJECT_TYPE,count(*) from dba_objects
where OWNER='BOAPTUS'
group by OBJECT_TYPE ;
no rows selected



SQL> select * from dba_objects where owner='BOAPTUS' ;

no rows selected



select 'alter table ' || table_name || ' disable constraint ' || constraint_name || ';' from user_constraints;


ALTER TABLE table_name DISABLE CONSTRAINT constraint_name;


select owner, constraint_name,table_name,index_owner,index_name
from dba_constraints
where (index_owner,index_name) in (select owner,index_name from dba_indexes
where tablespace_name='TABLESPACE_NAME');

=========================================================

drop all the object for particular user / schema






declare

v_object varchar2(30);
v_owner varchar2(30):='SCOTT' ;

begin


for x in (select object_type from dba_objects where owner= v_owner) loop

if x.object_type='TABLE' then

for i in ( select * from dba_objects where owner= v_owner and object_type= 'TABLE' ) loop

EXECUTE immediate 'drop '|| i.object_type||' '|| i.owner||'.'||'"'||i.object_name || '" cascade constraints ' ;

-- dbms_output.put_line ('drop '|| i.object_type||' "'||i.object_name || '" ;' );
-- dbms_output.put_line(i.object_type ||'.'||i.object_name ) ;

end loop ;

elsif x.object_type='MATERIALIZED VIEW' then

for i in ( select * from dba_objects where owner= v_owner and object_type= 'MATERIALIZED VIEW' ) loop

EXECUTE immediate 'drop '|| i.object_type||' '|| i.owner||'.'||'"'||i.object_name || '" ' ;

-- dbms_output.put_line ('drop '|| i.object_type||' "'||i.object_name || '" ;' );
-- dbms_output.put_line(i.object_type ||'.'||i.object_name ) ;

end loop ;

elsif x.object_type in ( 'DIMENSION','CLUSTER','SEQUENCE','VIEW','FUNCTION','PROCEDURE','PACKAGE','SYNONYM',
'DATABASE LINK','INDEXTYPE','PACKAGE BODY','TRIGGER') then

for i in ( select * from dba_objects where owner= v_owner
and object_type in ( 'DIMENSION','CLUSTER','SEQUENCE','VIEW','FUNCTION','PROCEDURE','PACKAGE','SYNONYM',
'DATABASE LINK','INDEXTYPE','PACKAGE BODY','TRIGGER')) loop

EXECUTE immediate 'drop '|| i.object_type||' '|| i.owner||'.'||'"'||i.object_name || '" ' ;

end loop ;

elsif x.object_type in ('TYPE','OPERATOR') then

for i in ( select * from dba_objects where owner= v_owner and object_type in ( 'TYPE','OPERATOR') ) loop

EXECUTE immediate 'drop '|| i.object_type||' '|| i.owner||'.'||'"'||i.object_name || '" force ' ;

end loop ;

elsif x.object_type in ('JAVA RESOURCE') then

for i in ( select * from dba_objects where owner= v_owner and object_type in ( 'JAVA RESOURCE') ) loop
EXECUTE immediate 'drop '|| i.object_type||' '|| i.owner||'.'||'"'||i.object_name || '" ' ;

end loop ;


elsif x.object_type in ('JAVA CLASS') then

for i in ( select * from dba_objects where owner= v_owner and object_type in ( 'JAVA CLASS') ) loop
EXECUTE immediate 'drop '|| i.object_type||' '|| i.owner||'.'||'"'||i.object_name || '" ' ;

end loop ;



else

for q in ( select queue_table from dba_queue_tables where owner= v_owner) loop
dbms_aqadm.DROP_QUEUE_TABLE(q.queue_table,true);
end loop;


for j in ( select job_name from dba_scheduler_jobs where owner= v_owner ) loop
dbms_scheduler.DROP_JOB(j.job_name,true);
end loop;

-- null ;

end if;

end loop;

EXECUTE immediate 'purge dba_recyclebin' ;
execute immediate 'purge recyclebin';


end;

/

Oracle LOB delete

LOB object drop error
oracle drop lob objects


after delete all the object schema .... LOB still exists .


select count(*) from dba_objects where owner='TIME' ;

COUNT(*)
--------------------
4



apt-rdbms-01.aptus.co.uk:APTDB\sys> select object_type,owner,object_name from dba_objects where owner='TIME' ;

OBJECT_TYPE OWNER
------------------- ------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------------------------------------------------------
LOB TIME
SYS_LOB0000075558C00003$$

LOB TIME
SYS_LOB0000075618C00006$$

LOB TIME
SYS_LOB0000075652C00006$$

LOB TIME
SYS_LOB0000075693C00003$$






apt-rdbms-01.aptus.co.uk:APTDB\sys> purge dba_recyclebin;

DBA Recyclebin purged.

apt-rdbms-01.aptus.co.uk:APTDB\sys> select object_type,owner,object_name from dba_objects where owner='TIME' ;

no rows selected




purge recyclebin;

Unix delete all the mail for the user

unix delete mail
solaris delete mail


on linux


$mail

then

d *

this will delete all the mail for the user

===============
On solaris
now it is Oracle solaris

ln-ora-01# uname
SunOS
ln-ora-01# uname -a
SunOS ln-ora-01 5.10 Generic_137111-07 sun4u sparc SUNW,Sun-Fire-880



ln-ora-01# mailx -N give any number like 1-10 mails
? d 1-10
? q
Held 282 messages in /var/mail//root
ln-ora-01# mailx -N
? d 1-282
? q
ln-ora-01# mail
No mail.