@SGAParameters.sql
Database Started Last
----------------------------------------
03-Feb-2022 12:50:57
1 row selected.
Setting MBytes
-------------------------------------------------------------------- --------
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: 7282688 7
Recycle Cache: 0 0
SGA Max: 2097152000 2,000
SGA Target: 1056964608 1,008
Shared Pool Reserved Area: 26004684 25
Shared Pool Size: 0 0
Streams Pool Size: 0 0
16 rows selected.
Setting
-----------------------------------------------------------------
DB Files: 200
Open Cursors: 300
Processes: 300
Session Cached Cursors: 50
Sessions: 472
5 rows selected.
Setting
------------------------------
Cache Advice: ON
Compatible: 19.0.0
Cursor Sharing: EXACT
Query Rewrite: TRUE
Statistics Level: TYPICAL
5 rows selected.
Resource Current HWM Setting
------------------------- ---------------- ---------------- ---------------
processes 85 121 300
sessions 105 144 472
enqueue_locks 40 57 5621
enqueue_resources 34 55 2296
ges_procs 74 109 1088
ges_ress 1,330 1,935 6246
ges_locks 268 337 11300
ges_cache_ress 1,359 1,963 0
ges_reg_msgs 2 2 2630
ges_big_msgs 0 0 478
ges_rsv_msgs 0 0 478
gcs_resources 11,250 11,250 52519
dml_locks 0 19 2076
max_shared_servers 1 2 UNLIMITED
14 rows selected.
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 411041792 411041792
__java_pool_size 0 0
__large_pool_size 8388608 8388608
__shared_pool_size 520093696 520093696
__streams_pool_size 0 0
_io_shared_pool_size 4194304 4194304
_kghdsidx_count 1 1
_kgl_heap_size 4096 4096
_kill_java_threads_on_eoc FALSE FALSE
_large_pool_min_alloc 65536 65536
_library_cache_advice TRUE TRUE
_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
event@SGAComponents.sql
Component Explicit Setting Current Size Min Size Max Size Granule Size
------------------------- ---------------- ---------------- ---------------- ---------------- ----------------
shared pool 0 520,093,696 520,093,696 520,093,696 4,194,304
large pool 0 8,388,608 8,388,608 8,388,608 4,194,304
java pool 0 0 0 0 4,194,304
streams pool 0 0 0 0 4,194,304
unified pga pool 0 0 0 0 4,194,304
memoptimize buffer cache 0 0 0 0 4,194,304
DEFAULT buffer cache 0 411,041,792 411,041,792 411,041,792 4,194,304
KEEP buffer cache 0 0 0 0 4,194,304
RECYCLE buffer cache 0 0 0 0 4,194,304
DEFAULT 2K buffer cache 0 0 0 0 4,194,304
DEFAULT 4K buffer cache 0 0 0 0 4,194,304
DEFAULT 8K buffer cache 0 0 0 0 4,194,304
DEFAULT 16K buffer cache 0 0 0 0 4,194,304
DEFAULT 32K buffer cache 0 0 0 0 4,194,304
Shared IO Pool 100,663,296 100,663,296 100,663,296 100,663,296 4,194,304
Data Transfer Cache 0 0 0 0 4,194,304
In-Memory Area 0 0 0 0 4,194,304
In Memory RW Extension Ar 0 0 0 0 4,194,304
ea
In Memory RO Extension Ar 0 0 0 0 4,194,304
ea
ASM Buffer Cache 0 0 0 0 4,194,304
----------------
sum 1,040,187,392
20 rows selected.
Operation Operation
Component Type Mode Timestamp
------------------------- --------------- --------------- -------------------------
shared pool STATIC
large pool STATIC
java pool STATIC
streams pool STATIC
unified pga pool STATIC
memoptimize buffer cache STATIC
DEFAULT buffer cache INITIALIZING
KEEP buffer cache STATIC
RECYCLE buffer cache STATIC
DEFAULT 2K buffer cache STATIC
DEFAULT 4K buffer cache STATIC
DEFAULT 8K buffer cache STATIC
DEFAULT 16K buffer cache STATIC
DEFAULT 32K buffer cache STATIC
Shared IO Pool STATIC
Data Transfer Cache STATIC
In-Memory Area STATIC
In Memory RW Extension Ar STATIC
ea
In Memory RO Extension Ar STATIC
ea
ASM Buffer Cache STATIC
20 rows selected.
Component Parameter Initial Final Status Changed At
------------------------- ------------------------- ---------------- ---------------- ---------- -------------------
shared pool shared_pool_size 0 520,093,696 COMPLETE 02/03/2022 12:51:11
DEFAULT buffer cache db_cache_size 411,041,792 411,041,792 COMPLETE 02/03/2022 12:51:11
java pool java_pool_size 0 0 COMPLETE 02/03/2022 12:51:11
streams pool streams_pool_size 0 0 COMPLETE 02/03/2022 12:51:11
unified pga pool _unified_pga_pool_size 0 0 COMPLETE 02/03/2022 12:51:11
memoptimize buffer cache memoptimize_pool_size 0 0 COMPLETE 02/03/2022 12:51:11
DEFAULT buffer cache db_cache_size 0 411,041,792 COMPLETE 02/03/2022 12:51:11
KEEP buffer cache db_keep_cache_size 0 0 COMPLETE 02/03/2022 12:51:11
RECYCLE buffer cache db_recycle_cache_size 0 0 COMPLETE 02/03/2022 12:51:11
DEFAULT 2K buffer cache db_2k_cache_size 0 0 COMPLETE 02/03/2022 12:51:11
DEFAULT 4K buffer cache db_4k_cache_size 0 0 COMPLETE 02/03/2022 12:51:11
DEFAULT 8K buffer cache db_8k_cache_size 0 0 COMPLETE 02/03/2022 12:51:11
DEFAULT 16K buffer cache db_16k_cache_size 0 0 COMPLETE 02/03/2022 12:51:11
DEFAULT 32K buffer cache db_32k_cache_size 0 0 COMPLETE 02/03/2022 12:51:11
Shared IO Pool _shared_io_pool_size 0 100,663,296 COMPLETE 02/03/2022 12:51:11
Data Transfer Cache data_transfer_cache_size 0 0 COMPLETE 02/03/2022 12:51:11
In-Memory Area inmemory_size 0 0 COMPLETE 02/03/2022 12:51:11
In Memory RW Extension Ar _inmemory_ext_rwarea 0 0 COMPLETE 02/03/2022 12:51:11
ea
In Memory RO Extension Ar _inmemory_ext_roarea 0 0 COMPLETE 02/03/2022 12:51:11
ea
ASM Buffer Cache db_cache_size 0 0 COMPLETE 02/03/2022 12:51:11
large pool large_pool_size 0 8,388,608 COMPLETE 02/03/2022 12:51:11
21 rows selected.
Component Lowest MBytes Highest MBytes
------------------------- ---------------- -------- ---------------- --------
shared pool 520,093,696 496 520,093,696 496
java pool 0 0 0 0
streams pool 0 0 0 0
DEFAULT 8K buffer cache 0 0 0 0
In Memory RW Extension Ar 0 0 0 0
ea
DEFAULT 16K buffer cache 0 0 0 0
unified pga pool 0 0 0 0
Shared IO Pool 100,663,296 96 100,663,296 96
DEFAULT buffer cache 411,041,792 392 411,041,792 392
DEFAULT 2K buffer cache 0 0 0 0
Data Transfer Cache 0 0 0 0
KEEP buffer cache 0 0 0 0
RECYCLE buffer cache 0 0 0 0
DEFAULT 4K buffer cache 0 0 0 0
In-Memory Area 0 0 0 0
large pool 8,388,608 8 8,388,608 8
DEFAULT 32K buffer cache 0 0 0 0
In Memory RO Extension Ar 0 0 0 0
ea
memoptimize buffer cache 0 0 0 0
ASM Buffer Cache 0 0 0 0
20 rows selected.
Name BYTES Auto CON_ID
---------------------------------------- ---------- ---- ----------
Fixed SGA Size 9154768 No 0
Redo Buffers 7618560 No 0
Buffer Cache Size 511705088 Yes 0
In-Memory Area Size 0 No 0
Shared Pool Size 520093696 Yes 0
Large Pool Size 8388608 Yes 0
Java Pool Size 0 Yes 0
Streams Pool Size 0 Yes 0
Shared IO Pool Size 100663296 Yes 0
Data Transfer Cache Size 0 Yes 0
Granule Size 4194304 No 0
Maximum SGA Size 2097148112 No 0
Startup overhead in Shared Pool 261792656 No 0
Free SGA Memory Available 1040187392 0
14 rows selected.@CursorEfficiency.sql
Max
Init Parameter Limit Usage
------------------------------ -------------------- -----
session_cached_cursors 50 100%
open_cursors 300 15% @SGAStat.sql
Script Run TimeStamp
-----------------------------
03-FEB-2022 14:06:54
1 row selected.
Startup Time
-----------------------------
03-FEB-2022 12:50:57
1 row selected.
Name MB
-------------------------- --------
SQLA 63
KGLH0 38
free memory 35
KGLS 22
gcs resources 13
ksunfy_meta 1 12
SO private sga 11
KQR X PO 10
reader lock mitigation en 10
KGLHD 10
10 rows selected.@PoolAdvice.sql
Shared Pool Size (MB) Size Factor Estimated Hits in Library Cache Estimate of LC Size Estimate of objects in LC
--------------------- ----------- ------------------------------- ------------------- -------------------------
392 .7903 44,212 44 3,008
444 .8952 45,198 96 4,986
456 .9194 45,500 107 5,476
460 .9274 45,612 111 5,645
464 .9355 45,722 115 5,838
468 .9435 45,832 115 5,872
472 .9516 45,941 115 5,872
476 .9597 46,051 115 5,872
480 .9677 46,160 115 5,872
484 .9758 46,269 115 5,872
488 .9839 46,377 115 5,872
492 .9919 47,175 115 5,872
496 1 47,976 115 5,872
500 1.0081 47,976 115 5,872
504 1.0161 47,976 115 5,872
508 1.0242 47,976 115 5,872
512 1.0323 47,976 115 5,872
516 1.0403 47,976 115 5,872
520 1.0484 47,976 115 5,872
524 1.0565 47,976 115 5,872
528 1.0645 47,976 115 5,872
532 1.0726 47,976 115 5,872
548 1.1048 47,976 115 5,872
600 1.2097 47,976 115 5,872
652 1.3145 47,976 115 5,872
704 1.4194 47,976 115 5,872
756 1.5242 47,976 115 5,872
808 1.629 47,976 115 5,872
860 1.7339 47,976 115 5,872
912 1.8387 47,976 115 5,872
964 1.9435 47,976 115 5,872
1,016 2.0484 47,976 115 5,872 @ReservedAnalysis.sql
Shared
Pool Failed Reserved Reserved Reserved
Miss Size Free Space Max Avg
------ ---------------- ---------------- ---------------- ----------------
0 0 26,964,088 2,096,952 219,220
1 row selected.
Reserved
Reserved Total Area Size of
Used Requests Misses Last Miss
---------------- ---------------- ---------------- ----------------
3,977,232 0 0 0
1 row selected.
ALLOC_CL AVERAGE MAXIMUM
-------- ----------- --------
perm 120.00 120
freeabl 122.21 138,248
recr 1,537.06 4,184
free 1,868.20 4,008PinnedCode.sql -- investigates which objects are pinned into the Library Cache? NOTE: These queries will work on 9i/9.2.x/10g. The data provides more detailed information on what objects are pinned and how much memory is needed for them.@LCObjectsStats.sql
KEPT TYPE MEMORY
----- -------------------- --------------------
NO CURSOR 86,949,952
NO PACKAGE BODY 8,311,032
NO PACKAGE 4,799,560
NO TABLE 4,178,680
NO VIEW 2,290,488
NO TYPE 2,096,864
NO MULTI-VERSIONED OBJE 978,656
CT
NO SYNONYM 858,896
NO INDEX 801,376
NO CURSOR STATS 667,808
NO FUNCTION 206,600
NO Optimizer Expression 169,344
Header
NO TYPE BODY 122,016
NO QUEUE 100,968
NO LIBRARY 60,664
NO SCHEDULER JOB 60,520
NO OPERATOR 48,672
NO SEQUENCE 44,552
NO TRIGGER 34,248
NO Optimizer Finding 28,504
NO SCHEDULER WINDOW 28,224
NO PUB SUB INTERNAL INF 25,152
ORMATION
NO RULESET 24,608
NO RESOURCE MANAGER CON 24,232
SUMER GROUP
NO Optimizer Directive 24,232
Owner
NO Optimizer Expresion 24,192
Object
NO DIRECTORY 16,168
NO SCHEDULER PROGRAM 16,128
NO CLUSTER 16,128
NO RESOURCE MANAGER PLA 12,216
N
NO SCHEDULER SCHEDULE 12,096
NO SCHEDULER GLOBAL ATT 12,096
RIBUTE
NO SUBSCRIPTION 9,888
NO RESOURCE MANAGER CDB 8,144
PLAN
NO SCHEDULER CLASS 8,104
NO TRANSFORMATION 8,088
NO AUDIT POLICY 8,064
NO EDITION 4,072
NO FED APP 4,032
NO PDB 0
NO PROCEDURE 0
NO NONE 0
NO RULE EVALUATION CONT 0
EXT
NO OBJECT ID 0
YES CURSOR STATS 8,046,272
YES RUNTIME STATISTICS 1,840,544
YES MULTI-VERSIONED OBJE 1,043,904
CT
YES TABLE 268,352
YES INDEX 56,448
YES CLUSTER 48,784 @HardParses.sql
Inst Hash SQL
---- ---------- ----------------------------------------
1 720789879 SELECT /*+ no_monitor */
to_date(substr(v2.info, 1, v2.pos1 - 2),
'YYYY:MM:DD:HH24:MI:SS') sql_exec_start,
to_number(substr(v2.info, v2.pos2,
v2.pos3 - v2.pos2 - 1)) inst_id,
to_number(substr(v2.info, v2.pos3,
v2.pos4 - v2.pos3 - 1))
session_id,
to_number(substr(v2.info, v2.pos4,
v2.pos5 - v2.pos4 - 1))
session_serial,
......@PinCandidates.sql
可以修改为count，做统计用。参考@SQLStats.sql
select sql_fulltext, sharable_mem, invalidations, loads, executions
from v$sql
where loads > invalidations
and executions > 1000
order by sharable_mem desc
/
select sql_fulltext, sharable_mem, invalidations, loads
from v$sql
where loads > invalidations
and sharable_mem > 190000
order by loads descOwner Name Type Memory Used
-------------------- ------------------------- ------------------------- ----------------
SYS DBMS_AQADM_SYS PACKAGE BODY 641,568
SYS DBMS_STATS_INTERNAL PACKAGE BODY 600,200
SYS DBMS_SQLTUNE_INTERNAL PACKAGE BODY 563,200
SYS DBMS_XPLAN PACKAGE BODY 429,936
SYS DBMS_ISCHED PACKAGE BODY 416,640
SYS PRVT_HDM PACKAGE BODY 231,392
SYS PRVT_ADVISOR PACKAGE BODY 222,928
SYS DBMS_SWRF_INTERNAL PACKAGE BODY 207,112
SYS DBMS_STATS_ADVISOR PACKAGE BODY 202,344
SYS DBMS_SQL PACKAGE BODY 145,048
DBSNMP BSLN_INTERNAL PACKAGE BODY 140,368
SYS DBMS_UTILITY PACKAGE BODY 94,624
SYS DBMS_SYSTEM PACKAGE BODY 57,176
SYS DBMS_ASSERT PACKAGE BODY 53,040
SYS DBMS_AQADM_SYSCALLS PACKAGE BODY 52,984
SYS DBMS_STANDARD PACKAGE 48,640
SYS STANDARD PACKAGE BODY 36,600
SYS DBMS_UMF_PROTECTED PACKAGE BODY 32,552
SYS DBMS_LOB PACKAGE BODY 32,520
SYS DBMS_AUTO_TASK PACKAGE BODY 24,400
SYS IS_VPD_ENABLED FUNCTION 24,304
SYS DICTIONARY_OBJ_OWNER FUNCTION 24,304
SYS DICTIONARY_OBJ_NAME FUNCTION 24,304
SYS DBMS_PRVT_TRACE PACKAGE BODY 20,296
SYS DBMS_SQLTUNE_UTIL0 PACKAGE BODY 20,272
SYS DBMS_SESSION PACKAGE BODY 20,232
SYS DBMS_AQADM_VAR PACKAGE BODY 16,232
SYS DBMS_PDB PACKAGE BODY 12,192
SYS UTL_RAW PACKAGE BODY 12,152
SYS DBMS_OUTPUT PACKAGE BODY 12,120
SYS PLITBLM PACKAGE 8,104
31 rows selected.
Code Loaded Memory Footprint Invalidations Loads Executions
---------------------------------------- ---------------- ------------- ------------ ----------------
select obj#,type#,ctime,mtime,stime, 59,749 1 2 1,678
status, dataobj#, flags, oid$, spare1,
spare2, spare3, signature, spare7,
spare8, spare9, nvl(dflcollid, 16382),
creappid, creverid, modappid, modverid,
crepatchid, modpatchid from obj$ where
owner#=:1 and name=:2 and namespace=:3
and remoteowner is null and linkname is
null and subname is null
......
Code Loaded Memory Footprint Invalidations Loads
---------------------------------------- ---------------- ------------- ------------
SELECT COUNT(*) FROM DBA_SCHEDULER_JOBS 554,895 0 1
WHERE REGEXP_LIKE
(JOB_NAME,'LOAD_OPATCH_INVENTORY_([0-9])
+$')
select partition_name from (select 413,116 0 1
......@TrendsLC.sql
"TABLE/PROCEDURE" 3101 3148 3158 3186 3203 3228 3260 3398
"CLUSTER" 7 7 7 7 7 7 7 7
"TRIGGER" 77 79 79 81 81 83 85 87
"SQL AREA" 724 726 726 726 732 739 740 740 @SQLStats.sql
HWM Information:
----- Max Invalidations: 2
----- Max Versions Loaded: 6
----- Versions HWM: 6
----- Largest Memory object: 4,479,177
====================================
PL/SQL procedure successfully completed.@SQLVersions11g.sql
SQL ID SQL VERSION_COUNT
--------------- ------------------------------ -------------
121ffmrc95v7g select 6
i.obj#,i.ts#,i.file#,i.block#,
i.intcols,i.type#,i.flags,i.pr
operty,i.pctfree$,i.initrans,i
.maxtrans,i.blevel,i.leafcnt,i
.distkey,i.lblkkey,i.dblkkey,i
.clufac,i.cols,i.analyzetime,i
.samplesize,i.dataobj#,nvl(i.d
egree,1),nvl(i.instances,1),i.
rowcnt,mod(i.pctthres$,256),i.
indmethod#,i.trunccnt,nvl(c.un
icols,0),nvl(c.deferrable#+c.v
alid#,0),nvl(i.spare1,i.intcol
s),i.spare4,i.spare2,i.spare6,
decode(i.pctthres$,null,null,m
od(trunc(i.pctthres$/256),256)
),nvl(i.evaledition#,1),nvl(i.
unusablebefore#,0),nvl(i.unusa
blebeginning#,0),
ist.cachedblk,ist.cachehit,ist
.logicalread from ind$ i,
ind_stats$ ist, (select
enabled, min(intcols)
unicols,min(to_number(bitand(d
efer,1)))
deferrable#,min(to_number(bita
nd(defer,4))) valid# from
cdef$ where obj#=:1 and
enabled > 1 group by enabled)
c where i.obj#=c.enabled(+)
and i.obj# = ist.obj#(+) and
i.bo#=:1 order by i.obj#
5u7g54s63p4ts select toid from type$ where 6
package_obj#=:1 and
typ_name=:2
2 rows selected.
***Watch for***
Variance > 0 means more versions tracked in V$SQLAREA
Variance < 0 means more versions tracked in V$SQL_SHARED_CURSOR
no rows selected