Oracle查询内存分配和使用率

SQL> 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); 2 3 4 5 6 7 NAM TOTAL USED FREE PCTUSED 

--- ---------- ---------- ---------- ----------<br>PGA        377     327.12      49.88      86.77<br>SGA 1135.99883    1067.11      68.89      93.94<br><br>查看内存大小

SQL> 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)/totaSQL> l*100 pctused from (
s  2  elect 'Java Pool  3  ' name,(select sum(bytes/1024/1024) total from v$sgastat where pool='java pool' group by pool)total,
( select  4   bytes/1024/1024 free from v$sgastat where pool='java pool' and nam  5  e='free memory')free from dual)
union
select name,Round(total,2),round(total-free,2)   6  used,r  7  ound(free,2) free, round((total-free)/total*100,2) pctused from (
select 'Large Pool' name,(select  8   sum(bytes/1024/1024) total from v$sgastat where pool='large pool' group by pool)total,
( select bytes/1024/1024 f  9  ree from v$sgastat where pool='large pool' and name='free memory')free from dual)
order by pc 10  tused de 11  sc); 12   13   14   15   16   17   18   19   20   21   22   23   24   25   26   27   28   29   30   31   32   33   34   35   36   37   38   39   40   41   42   43   44   45   46   47   48   49   50   51  
 
NAME                          TOTAL       USED       FREE    PCTUSED
------------------------ ---------- ---------- ---------- ----------
SGA                      1135.99883    1068.71      67.29      94.08
Shared pool                     272     252.24      19.76      92.74
PGA                             377     327.56      49.44      86.89
Large Pool                       32        .47      31.53       1.46
Default pool                 351.83     101.76     250.07        .29
DEFAULT 16K buffer cache          0          0          0          0
DEFAULT 32K buffer cache          0          0          0          0
Java Pool                        16          0         16          0
KEEP pool                         0          0          0          0
RECYCLE pool                      0          0          0          0
 
10 rows selected.<br><br>内存使用率

SQL> select pool,name,round(bytes/1024/1024,2) M from v$sgastat where name='free memory'
  2  ;
 
POOL           NAME                                M
-------------- -------------------------- ----------
shared pool    free memory                     19.67
large pool     free memory                     31.53
java pool      free memory                        16查询share pool的空闲内存

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) > 1000000
  2    3    4    5    6    7    8  ;
 
Stmt
--------------------------------------------------------------------------------
  COUNT(*)        Mem       Open       Exec
---------- ---------- ---------- ----------
select * from ( select name,total,round(total-free,2) used, round(free,2) free,r
ound((total-free)/to
         1    1022017          1          3通过下面的sql查询占用share pool内存大于1M的sql

SQL> SELECT address,
       sql_id,
       hash_value,
       version_count,
       users_opening,
       users_executing,
       sql_text
  FROM v$sqlarea WHERE version_count > 4  2    3    4    5    6    7    8  
  9  ;
 
ADDRESS          SQL_ID        HASH_VALUE VERSION_COUNT USERS_OPENING
---------------- ------------- ---------- ------------- -------------
USERS_EXECUTING
---------------
SQL_TEXT
--------------------------------------------------------------------------------
0000000080BC8998 5rurx5xtjwcu2 1931359042             5             0
              0
SELECT /*+ OPT_PARAM('_parallel_syspls_obey_force' 'false') */ KSPPCV.KSPPSTVL F
ROM X$KSPPCV KSPPCV, X$KSPPI KSPPI WHERE KSPPI.INDX = KSPPCV.INDX AND KSPPI.KSPP
INM = :B1
 
0000000080BDB368 b9nbhsbx8tqz5 4203535333             5             0
 
ADDRESS          SQL_ID        HASH_VALUE VERSION_COUNT USERS_OPENING
---------------- ------------- ---------- ------------- -------------
USERS_EXECUTING
---------------
SQL_TEXT
--------------------------------------------------------------------------------
              0
SELECT /*+ OPT_PARAM('_parallel_syspls_obey_force' 'false') */ SPARE4 FROM SYS.O
PTSTAT_HIST_CONTROL$ WHERE SNAME = :B1<br><br>