CNDBA社区
1.PGA&SG使用情况
select name,total,round(total-free,2) used, round(free,2) free,round((total-free)/total*100,2) pctused from
(select 'SGA' name,(select sum(value/1024/1024) from v$sga) total,
(select sum(bytes/1024/1024) from v$sgastat where name='free memory')free from dual)
union
select name,total,round(used,2)used,round(total-used,2)free,round(used/total*100,2)pctused from (
select 'PGA' name,(select value/1024/1024 total from v$pgastat where name='aggregate PGA target parameter')total,
(select value/1024/1024 used from v$pgastat where name='total PGA allocated')used from dual);
NAME TOTAL USED FREE PCTUSED
-------------------------------------------------------------------------------- ---------- ---------- ---------- ----------
PGA 782 529.3 252.7 67.69
SGA 3135.99731 2906.8 229.2 92.69
2.内存使用率
select * from (
select name,total,round(total-free,2) used, round(free,2) free,round((total-free)/total*100,2) pctused from
(select 'SGA' name,(select sum(value/1024/1024) from v$sga) total,
(select sum(bytes/1024/1024) from v$sgastat where name='free memory')free from dual)
union
select name,total,round(used,2)used,round(total-used,2)free,round(used/total*100,2)pctused from (
select 'PGA' name,(select value/1024/1024 total from v$pgastat where name='aggregate PGA target parameter')total,
(select value/1024/1024 used from v$pgastat where name='total PGA allocated')used from dual)
union
select name,round(total,2) total,round((total-free),2) used,round(free,2) free,round((total-free)/total*100,2) pctused from (
select 'Shared pool' name,(select sum(bytes/1024/1024) from v$sgastat where pool='shared pool')total,
(select bytes/1024/1024 from v$sgastat where name='free memory' and pool='shared pool') free from dual)
union
select name,round(total,2)total,round(total-free,2) used,round(free,2) free,round((total-free)/total,2) pctused from (
select 'Default pool' name,( select a.cnum_repl*(select value from v$parameter where name='db_block_size')/1024/1024 total from x$kcbwds a, v$buffer_pool p
where a.set_id=p.LO_SETID and p.name='DEFAULT' and p.block_size=(select value from v$parameter where name='db_block_size')) total,
(select a.anum_repl*(select value from v$parameter where name='db_block_size')/1024/1024 free from x$kcbwds a, v$buffer_pool p
where a.set_id=p.LO_SETID and p.name='DEFAULT' and p.block_size=(select value from v$parameter where name='db_block_size')) free from dual)
union
select name,nvl(round(total,2),0)total,nvl(round(total-free,2),0) used,nvl(round(free,2),0) free,nvl(round((total-free)/total,2),0) pctused from (
select 'KEEP pool' name,(select a.cnum_repl*(select value from v$parameter where name='db_block_size')/1024/1024 total from x$kcbwds a, v$buffer_pool p
where a.set_id=p.LO_SETID and p.name='KEEP' and p.block_size=(select value from v$parameter where name='db_block_size')) total,
(select a.anum_repl*(select value from v$parameter where name='db_block_size')/1024/1024 free from x$kcbwds a, v$buffer_pool p
where a.set_id=p.LO_SETID and p.name='KEEP' and p.block_size=(select value from v$parameter where name='db_block_size')) free from dual)
union
select name,nvl(round(total,2),0)total,nvl(round(total-free,2),0) used,nvl(round(free,2),0) free,nvl(round((total-free)/total,2),0) pctused from (
select 'RECYCLE pool' name,( select a.cnum_repl*(select value from v$parameter where name='db_block_size')/1024/1024 total from x$kcbwds a, v$buffer_pool p
where a.set_id=p.LO_SETID and p.name='RECYCLE' and p.block_size=(select value from v$parameter where name='db_block_size')) total,
(select a.anum_repl*(select value from v$parameter where name='db_block_size')/1024/1024 free from x$kcbwds a, v$buffer_pool p
where a.set_id=p.LO_SETID and p.name='RECYCLE' and p.block_size=(select value from v$parameter where name='db_block_size')) free from dual)
union
select name,nvl(round(total,2),0)total,nvl(round(total-free,2),0) used,nvl(round(free,2),0) free,nvl(round((total-free)/total,2),0) pctused from(
select 'DEFAULT 16K buffer cache' name,(select a.cnum_repl*16/1024 total from x$kcbwds a, v$buffer_pool p
where a.set_id=p.LO_SETID and p.name='DEFAULT' and p.block_size=16384) total,
(select a.anum_repl*16/1024 free from x$kcbwds a, v$buffer_pool p
where a.set_id=p.LO_SETID and p.name='DEFAULT' and p.block_size=16384) free from dual)
union
select name,nvl(round(total,2),0)total,nvl(round(total-free,2),0) used,nvl(round(free,2),0) free,nvl(round((total-free)/total,2),0) pctused from(
select 'DEFAULT 32K buffer cache' name,(select a.cnum_repl*32/1024 total from x$kcbwds a, v$buffer_pool p
where a.set_id=p.LO_SETID and p.name='DEFAULT' and p.block_size=32768) total,
(select a.anum_repl*32/1024 free from x$kcbwds a, v$buffer_pool p
where a.set_id=p.LO_SETID and p.name='DEFAULT' and p.block_size=32768) free from dual)
union
select name,total,total-free used,free, (total-free)/total*100 pctused from (
select 'Java Pool' name,(select sum(bytes/1024/1024) total from v$sgastat where pool='java pool' group by pool)total,
( select bytes/1024/1024 free from v$sgastat where pool='java pool' and name='free memory')free from dual)
union
select name,Round(total,2),round(total-free,2) used,round(free,2) free, round((total-free)/total*100,2) pctused from (
select 'Large Pool' name,(select sum(bytes/1024/1024) total from v$sgastat where pool='large pool' group by pool)total,
( select bytes/1024/1024 free from v$sgastat where pool='large pool' and name='free memory')free from dual)
order by pctused desc);
NAME TOTAL USED FREE PCTUSED
-------------------------------------------------------------------------------- ---------- ---------- ---------- ----------
Java Pool
SGA 3135.99731 2909.04 226.96 92.76
Shared pool 1856 1678.04 177.96 90.41
PGA 782 549.17 232.83 70.23
Large Pool 32 15 17 46.88
Default pool 128.09 103.24 24.84 .81
DEFAULT 16K buffer cache 0 0 0 0
DEFAULT 32K buffer cache 0 0 0 0
KEEP pool 0 0 0 0
RECYCLE pool 0 0 0 0
10 rows selected.
3.
sys@TESTDB 09:25:53> select * from v$sga_dynamic_components;
COMPONENT CURRENT_SIZE MIN_SIZE MAX_SIZE USER_SPECIFIED_SIZE OPER_COUNT LAST_OPER_TYP LAST_OPER LAST_OPE GRANULE_SIZE CON_ID
---------------------------------------------------------------- ------------ ---------- ---------- ------------------- ---------- ------------- --------- -------- ------------ ----------
shared pool 1946157056 1694498816 1946157056 0 3 GROW DEFERRED 22:55:33 16777216 0
large pool 33554432 33554432 33554432 0 0 STATIC 16777216 0
java pool 0 0 0 0 0 STATIC 16777216 0
streams pool 33554432 33554432 33554432 0 0 STATIC 16777216 0
unified pga pool 0 0 0 0 0 STATIC 16777216 0
memoptimize buffer cache 0 0 0 0 0 STATIC 16777216 0
DEFAULT buffer cache 1124073472 1124073472 1375731712 0 3 SHRINK DEFERRED 22:55:33 16777216 0
KEEP buffer cache 0 0 0 0 0 STATIC 16777216 0
RECYCLE buffer cache 0 0 0 0 0 STATIC 16777216 0
DEFAULT 2K buffer cache 0 0 0 0 0 STATIC 16777216 0
DEFAULT 4K buffer cache 0 0 0 0 0 STATIC 16777216 0
DEFAULT 8K buffer cache 0 0 0 0 0 STATIC 16777216 0
DEFAULT 16K buffer cache 0 0 0 0 0 STATIC 16777216 0
DEFAULT 32K buffer cache 0 0 0 0 0 STATIC 16777216 0
Shared IO Pool 134217728 134217728 134217728 134217728 0 STATIC 16777216 0
Data Transfer Cache 0 0 0 0 0 STATIC 16777216 0
In-Memory Area 0 0 0 0 0 STATIC 16777216 0
In Memory RW Extension Area 0 0 0 0 0 STATIC 16777216 0
In Memory RO Extension Area 0 0 0 0 0 STATIC 16777216 0
ASM Buffer Cache 0 0 0 0 0 STATIC 16777216 0
20 rows selected.
4.
sys@TESTDB 09:26:18> select * from v$pgastat;
NAME VALUE UNIT CON_ID
-------------------------------------------------------------------------------- ---------- ------------ ----------
aggregate PGA target parameter 819986432 bytes 0
aggregate PGA auto target 361654272 bytes 0
global memory bound 104857600 bytes 0
total PGA inuse 418288640 bytes 0
total PGA allocated 560513024 bytes 0
maximum PGA allocated 975066112 bytes 0
total freeable PGA memory 10092544 bytes 0
MGA allocated (under PGA) 0 bytes 0
maximum MGA allocated 0 bytes 0
process count 86 0
max processes count 102 0
PGA memory freed back to OS 2.1497E+10 bytes 0
total PGA used for auto workareas 0 bytes 0
maximum PGA used for auto workareas 110273536 bytes 0
total PGA used for manual workareas 0 bytes 0
maximum PGA used for manual workareas 1067008 bytes 0
over allocation count 0 0
bytes processed 5.1593E+11 bytes 0
extra bytes read/written 5.5514E+10 bytes 0
cache hit percentage 90.28 percent 0
recompute count (total) 576292 0
21 rows selected.
sys@TESTDB 09:26:46> select a.*,round(a.bytes/1024/1024,2) M from v$sgastat a where a.NAME = 'free memory';
POOL NAME BYTES CON_ID M
-------------- -------------------------------------------------------------------------------- ---------- ---------- ----------
shared pool free memory 186411544 0 177.78
large pool free memory 17825792 0 17
streams pool free memory 33554432 0 32
Elapsed: 00:00:00.01
通过下面的sql查询占用share pool内存大于10M的sql
SELECT substr(sql_text,1,100) "Stmt", count(*),
sum(sharable_mem) "Mem",
sum(users_opening) "Open",
sum(executions) "Exec"
FROM v$sql
GROUP BY substr(sql_text,1,100)
HAVING sum(sharable_mem) > 10000000;
COUNT(*) Mem Open Exec
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------- ---------- ---------- ----------
select 1 from DBA_SCHEDULER_JOBS where JOB_NAME like 'KWQICPOSTMSGDEL_1_%' and JOB_ACTION = 'DBMS_ 29 13307931 1 1095
SELECT * FROM (SELECT sql_id, force_matching_signature, sql_text, parsing_schema_name, modul 394 43913889 0 0
/* SQL Analyze(1) */ select /*+ full(t) no_parallel(t) no_parallel_index(t) dbms_stats cursor_sh 433 41552295 0 772
DECLARE stmt VARCHAR2(31700); currSeq NUMBER; currUserId NUMBER; errorMessage VARCHAR2(32767); objst 72 24937728 1 3047
select /*+ no_parallel */ spare4 from sys.optstat_hist_control$ where sname=:1 92 24652996 0 38917
SELECT D.STATUS, D.GENERATED, D.TEMPORARY, D.SECONDARY, D.OBJECT_TYPE, DECODE(BITAND(T.PROPERTY,1),1 53 24575811 0 2292
SELECT /* DS_SVC */ /*+ dynamic_sampling(0) no_sql_tune no_monitoring optimizer_features_enable(defa 1568 148733136 0 2857
select substrb(dump(val,16,0,64),1,240) ep, cnt from (select /*+ no_expand_table(t) index_rs(t) no 700 24721477 0 1199
select substrb(dump(val,16,0,64),1,240) ep, freq, cdn, ndv, (sum(pop) over()) popcnt, (sum(pop*fre 194 11027881 0 334
SELECT new.sql_seq, old.plan_hash_value, sqlset_row(new.sql_id,new.force_matching_signature, new.s 38 12312628 0 1689
SELECT T.CLIENT_ID, T.OPERATION_ID, T.TARGET_TYPE, T.TARGET_NAME, T. 28 29814404 0 79
SELECT /*+ first_rows(1) */ sql_id, force_matching_signature, sql_text, cast(NULL as SQL_OBJECTS) o 740 152713583 0 2507
MERGE /*+ dynamic_sampling(ST 4) dynamic_sampling_est_cdn(ST) OPT_PARAM('_parallel_sy 7 23880379 0 13
WITH MONITOR_DATA AS (SELECT * FROM TABLE(GV$(CURSOR( SELECT USERENV('instance') AS INST_ID, KEY, NV 6 24336090 0 18
select /*+ no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_exact use_weak_name_resl d 644 32618701 0 908
SELECT /*+ ordered push_pred(v) OPT_PARAM('_parallel_syspls_obey_force' 'false') 37 25316672 0 3350
SELECT (SELECT COUNT(*) FROM SYS.TAB$ T, SYS.SEG$ S WHERE T.OBJ# = :B1 AND T.FILE# = S.FILE# AND T.B 24 13432688 0 963
select /* KSXM:LOAD_DML_INF *//*+ leading(o) index(m) use_nl(m) */ nvl(m.inserts, 0) ins, 60 13487024 1 3448
18 rows selected.
查询一下version count过高的语句
SELECT address,
sql_id,
hash_value,
version_count,
users_opening,
users_executing,
sql_text
FROM v$sqlarea WHERE version_count > 10;
版权声明：本文为博主原创文章，未经博主允许不得转载。
oracle
- 上一篇：Oracle RAC RMAN备份脚本
- 下一篇：Oracle查看缓冲池命中率
