DBA常用数据库查询语句

1 数据库信息

1.1 数据库概要

select a.name "DB Name",
       e.global_name "Global Name",
       c.host_name "Host Name",
       c.instance_name "Instance Name" ,
       DECODE(c.logins,'RESTRICTED','YES','NO') "Restricted Mode",
       a.log_mode  "Archive Log Mode"
FROM v$database a, v$version b, v$instance c,global_name e
WHERE b.banner LIKE '%Oracle%';

1.2 参数文件(是spfile还是pfile)

select nvl(value,'pfile') "Parameter_File"
from v$parameter where Name='spfile';

1.3 非默认的参数

select name, rtrim(value) "pvalue"
from v$parameter
where isdefault = 'FALSE'
order by name;

1.4 控制文件及其状态

select Name,Status from v$controlfile;

1.5 数据库版本信息

select * from v$version;

1.6 数据库组件（true：已安装，false：未安装）

SELECT PARAMETER, VALUE FROM V$OPTION;

1.7 实例信息

select instance_name,host_name,version,status,database_status from v$instance;

1.8 NLS参数设置

SELECT * FROM NLS_Database_Parameters;

1.9 已装载的产品选项

select COMP_ID, COMP_NAME, VERSION,STATUS from dba_registry;

1.10 数据库的并发数

select count(*) as "并发数" from v$session where status='ACTIVE';

1.11 数据库Session连接数

select count(*) as "连接数" from v$session;

1.12 数据库总大小（GB）

select round(sum(space)) "总容量/Gb"
  from (select sum(bytes) / 1024 / 1024 / 1024 space
          from dba_data_files
        union all
        select nvl(sum(bytes) / 1024 / 1024 / 1024, 0) space
          from dba_temp_files
        union all
        select sum(bytes) / 1024 / 1024 / 1024 space from v$log);

1.13 数据库服务器运行的操作系统

select PLATFORM_NAME from v$database;

1.14 DBID

select dbid from v$database;

1.15 Flashback是否启动

select decode(flashback_on,'NO','未启用','启用') as "闪回模式" from v$database;

2 存储结构、表空间、数据文件

2.1 表空间及数据文件

select tablespace_name,file_name,
bytes/1024/1024 "Total Size(MB)",autoExtensible "Auto" 
from dba_data_files 
order by tablespace_name,file_id;

2.2 表空间状态及其大小使用情况

SELECT  d.tablespace_name "Name", d.status "Status", d.contents "Type",
        ROUND(NVL(a.bytes / 1024 / 1024, 0), 2) "Size (MB)",
        ROUND(NVL(a.bytes - NVL(f.bytes, 0), 0) / 1024 /1024, 2) "Used (MB)",
        ROUND(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0), 2) "Used%",
        ROUND(NVL(a.maxbytes / 1024 / 1024, 0), 2) "Max Size (MB)",
        DECODE(NVL(a.maxbytes,0), 0, 0, ROUND(NVL(a.maxbytes - a.bytes, 0) / 1024 / 1024, 2)) "Unused (MB)",
        DECODE(NVL(a.maxbytes,0), 0, 0, ROUND((1 - NVL(a.bytes / a.maxbytes, 0))*100, 2)) "Unused%"
  FROM sys.dba_tablespaces d,
      (SELECT tablespace_name, SUM(bytes) bytes, SUM(maxbytes) maxbytes
         FROM dba_data_files GROUP BY tablespace_name
       UNION ALL
       SELECT tablespace_name, SUM(bytes) bytes, SUM(maxbytes) maxbytes
         FROM dba_temp_files GROUP BY tablespace_name) a,
      (SELECT tablespace_name, SUM(bytes) bytes
         FROM dba_free_space GROUP BY tablespace_name
       UNION ALL
       SELECT tablespace_name, SUM(bytes_free) bytes
         FROM gv$temp_space_header GROUP BY tablespace_name) f
 WHERE d.tablespace_name = a.tablespace_name(+)
      AND d.tablespace_name = f.tablespace_name(+);

2.3 数据文件状态及其大小使用情况

SELECT  a.tablespace_name "TableSpace Name", a.File_Name "File Name",
        a.status "Status", a.AutoExtensible "Auto",
        round(NVL(a.bytes / 1024 / 1024, 0),1) "Size (MB)",
        round(NVL(a.bytes - NVL(f.bytes, 0),0)/1024/1024, 1) "Used (MB)",
        round(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0), 2) "Used %"
FROM dba_data_files a,
  (select file_id, sum(bytes) bytes
   from dba_free_space group by File_id) f
WHERE a.file_id=f.file_id(+)
order by a.tablespace_name,a.File_id;

2.4 不使用临时文件的临时表空间

select tablespace_name,contents from dba_tablespaces
where contents='TEMPORARY' and tablespace_name not in
  (select tablespace_name from dba_temp_files);

2.5 无效的数据文件(offline)

select f.tablespace_name,f.file_name,d.status
from dba_data_files f,v$datafile d
where d.status='OFFLINE' and f.file_id=File#(+);

2.6 处于恢复模式的文件

select f.tablespace_name,f.file_name
from dba_data_files f, v$recover_file r
where f.file_id=r.file#;

2.7 含有50个以上的Extent且30%以上碎片的表空间

select s.tablespace_name,
       round(100 * f.hole_count / (f.hole_count + s.seg_count)) pct_fragmented,
       s.seg_count segments, f.hole_count holes
from  (Select tablespace_name, count(*) seg_count
       from   dba_segments group by tablespace_name) s,
      (Select   tablespace_name, count(*) hole_count
       from     dba_free_space  group by tablespace_name) f
where s.tablespace_name = f.tablespace_name
      and s.tablespace_name in (Select tablespace_name
           from dba_tablespaces where contents = 'PERMANENT')
             And s.tablespace_name not in ('SYSTEM')
      and 100 * f.hole_count / (f.hole_count + s.seg_count) > 30
      and s.seg_count > 50;

2.8 表空间上的I/O分布

SELECT  t.name   ts_name,
        f.name  file_name,
        s.phyrds phy_reads,
        s.phyblkrd phy_blockreads,
        s.phywrts phy_writes,
        s.phyblkwrt phy_blockwrites
FROM    gv$tablespace t,
        gv$datafile f,
        gv$filestat s
WHERE   t.ts# = f.ts#
        and
        f.file# = s.file#
ORDER BY s.phyrds desc, s.phywrts desc;

2.9 数据文件上的I/O分布

Select   ts.NAME "Table Space", D.NAME "File Name",
         FS.PHYRDS "Phys Rds",
         decode(fstot.sum_ph_rds,  0, 0,
                 round(100 * FS.PHYRDS    / fstot.sum_ph_rds,  2)) "% Phys Rds",
         FS.PHYWRTS "Phys Wrts",
         decode(fstot.sum_ph_wrts, 0, 0,
                 round(100 * FS.PHYWRTS   / fstot.sum_ph_wrts, 2)) "% Phys Wrts"
FROM   V$FILESTAT FS, V$DATAFILE d, V$tablespace ts,
      (select sum(phyrds)   sum_ph_rds, sum(phywrts)   sum_ph_wrts,
              sum(phyblkrd) sum_bl_rds, sum(phyblkwrt) sum_bl_wrts
       from   V$filestat) fstot
WHERE  D.FILE# = FS.FILE# AND D.TS#   = TS.TS#;

2.10 Next Extent 相对于段当前已分配字节过大(>=2倍)或过小(<10%)的Segments

Select InitCap(SEGMENT_TYPE) "Type", OWNER, SEGMENT_NAME, BYTES, NEXT_EXTENT,
       ROUND(100 * NEXT_EXTENT / BYTES) "Percent(Next/Bytes)"
FROM   DBA_SEGMENTS
WHERE  ((ROUND(100 * NEXT_EXTENT / BYTES) < 10) OR
     (ROUND(100 * NEXT_EXTENT / BYTES) >= 200))
  AND    SEGMENT_TYPE NOT IN ('ROLLBACK', 'TEMPORARY', 'CACHE', 'TYPE2 UNDO')
order by 2,3,1;

2.11 Max Extents(>1)已经有90%被使用了的Segments

Select segment_type, owner,  Segment_name, Tablespace_name,
       partition_name, round(bytes /1024/1024) "Size(MB)", extents, max_extents
From   dba_segments
where  segment_type not in ('ROLLBACK', 'TEMPORARY', 'CACHE', 'TYPE2 UNDO')
and    extents >= (1 - ( 10 / 100)) * max_extents  and  max_extents > 1
order by bytes / max_extents desc;

2.12 已经分配超过100 Extents的Segments

Select segment_type, owner, segment_name, extents, partition_name
from dba_segments
where  segment_type not in ('ROLLBACK', 'TEMPORARY', 'CACHE', 'TYPE2 UNDO')
   and   owner not in ('SYS', 'SYSTEM', 'OUTLN', 'DBSNMP', 'ORDSYS', 'ORDPLUGINS',
           'MDSYS', 'CTXSYS', 'AURORA$ORB$UNAUTHENTICATED', 'XDB')
   and    extents > 100;

2.13 因表空间空间不够将导致不能扩展的Objects

Select a.tablespace_name, a.owner,
       decode(a.partition_name, null, a.segment_name,
       a.segment_name || '.' || a.partition_name) "Segment Name",
       a.extents, round(next_extent/1024) next_extent_kb,
       round(b.free / 1024) ts_free_kb,
       round(c.morebytes / 1024 / 1024) ts_growth_mb
from   dba_segments a,
      (Select df.tablespace_name, nvl(max(fs.bytes), 0) free
       from     dba_data_files df,
                dba_free_space fs
       where    df.file_id = fs.file_id (+)
       group by df.tablespace_name) b,
     (Select tablespace_name, max(maxbytes - bytes) morebytes,
             sum(decode(AUTOEXTENSIBLE, 'YES', 1, 0)) autoextensible
      from     dba_data_files
      group by tablespace_name) c
where a.tablespace_name = b.tablespace_name
      and   a.tablespace_name = c.tablespace_name
      and   ((c.autoextensible = 0) or ((c.autoextensible > 0)
      and (a.next_extent > c.morebytes)))
      and   a.next_extent > b.free
order by 1;

2.14 表空间碎片化程度分析（FSFI<30,破碎化程度高）

select tablespace_name,
       round(sqrt(max(blocks) / sum(blocks)) *
             (100 / sqrt(sqrt(count(blocks)))),
             2) FSFI,
       (case
         when sqrt(max(blocks) / sum(blocks)) *
              (100 / sqrt(sqrt(count(blocks)))) > = 30 then
          '正常'
         when sqrt(max(blocks) / sum(blocks)) *
              (100 / sqrt(sqrt(count(blocks)))) < 30 then
          '表空间破碎化程度高，请整理'
       end) Prompt
  from dba_free_space
 group by tablespace_name
 order by 2;

2.15 可传输表空间支持的操作系统和字节顺序

select * from v$transportable_platform;

2.16 数据库临时文件状态

SELECT FILE_ID ID,
       FILE_NAME,
       TABLESPACE_NAME,
       round(BYTES / 1024 / 1024, 2) "Size/Mb",
       autoextensible
  FROM dba_temp_files;

2.17 临时表空间使用率

select h.tablespace_name,
       round(sum(h.bytes_free+h.bytes_used)/1048576, 2) "MB_Alloc",
       round(sum((h.bytes_free+h.bytes_used)-nvl(p.bytes_used, 0))/1048576, 2) "MB_free",
       round(sum(nvl(p.bytes_used, 0))/ 1048576, 2) "MB_Used",
       round((sum((h.bytes_free + h.bytes_used)-nvl(p.bytes_used, 0))/sum(h.bytes_used + h.bytes_free)) * 100,2) "Pct_Free%",
       100 - round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) / sum(h.bytes_used + h.bytes_free)) * 100,2)
"pct_used%"
from   sys.v_$TEMP_SPACE_HEADER h, sys.v_$Temp_extent_pool p, dba_temp_files f
where  p.file_id(+) = h.file_id
and    p.tablespace_name(+) = h.tablespace_name
and    f.file_id = h.file_id
and    f.tablespace_name = h.tablespace_name
group by h.tablespace_name, f.maxbytes
ORDER BY 4;

2.18 使用最多临时表空间的SQL

SELECT SE.USERNAME,
       SE.SID,
       SU.EXTENTS,
       (SU.BLOCKS * TO_NUMBER(RTRIM(P.VALUE)))/(1024*1024) AS "SPACE",
       TABLESPACE,
       SEGTYPE,
       SQL_TEXT
  FROM V$SORT_USAGE SU, V$PARAMETER P, V$SESSION SE, V$SQL S
 WHERE P.NAME = 'DB_BLOCK_SIZE'
   AND SU.SESSION_ADDR = SE.SADDR
   AND S.HASH_VALUE = SU.SQLHASH
   AND S.ADDRESS = SU.SQLADDR
 ORDER BY SU.BLOCKS * TO_NUMBER(RTRIM(P.VALUE)) DESC, SE.SID;

3 重做日志

3.1 重做日志文件信息

select  f.group#, f.member "Redo File", f.Type,
        l.Status,l.bytes/1024/1024 "Size(MB)"
from v$log l,v$logfile f
where l.group#=f.group#;

3.2 最近7天归档日志的生成频率

select a.recid,
       to_char(a.first_time, 'yyyy-mm-dd hh24:mi:ss') begin_time,
       b.recid,
       to_char(b.first_time, 'yyyy-mm-dd hh24:mi:ss') end_time,
       round((b.first_time - a.first_time) * 24 * 60, 2) minutes
  from v$log_history a, v$log_history b
 where b.recid = a.recid + 1
   and a.first_time > sysdate - 7;

3.3 监控当前重做日志文件使用情况（as sysdba）

select le.leseq "Current log sequence No",
       100 * cp.cpodr_bno / le.lesiz "Percent Full",
       (cpodr_bno - 1) * 512 "bytes used exclude header",
       le.lesiz * 512 - cpodr_bno * 512 "Left space",
       le.lesiz * 512 "logfile size"
  from x$kcccp cp, x$kccle le
 where LE.leseq = CP.cpodr_seq
   and bitand(le.leflg, 24) = 8;

3.4 最近7日联机日志切换频度

SELECT SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'), 1, 5) DAY,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'), 10, 2), '00', 1, 0)) H00,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'), 10, 2), '01', 1, 0)) H01,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'), 10, 2), '02', 1, 0)) H02,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'), 10, 2), '03', 1, 0)) H03,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'), 10, 2), '04', 1, 0)) H04,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'), 10, 2), '05', 1, 0)) H05,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'), 10, 2), '06', 1, 0)) H06,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'), 10, 2), '07', 1, 0)) H07,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'), 10, 2), '08', 1, 0)) H08,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'), 10, 2), '09', 1, 0)) H09,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'), 10, 2), '10', 1, 0)) H10,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'), 10, 2), '11', 1, 0)) H11,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'), 10, 2), '12', 1, 0)) H12,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'), 10, 2), '13', 1, 0)) H13,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'), 10, 2), '14', 1, 0)) H14,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'), 10, 2), '15', 1, 0)) H15,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'), 10, 2), '16', 1, 0)) H16,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'), 10, 2), '17', 1, 0)) H17,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'), 10, 2), '18', 1, 0)) H18,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'), 10, 2), '19', 1, 0)) H19,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'), 10, 2), '20', 1, 0)) H20,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'), 10, 2), '21', 1, 0)) H21,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'), 10, 2), '22', 1, 0)) H22,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'), 10, 2), '23', 1, 0)) H23,
       COUNT(*) TOTAL
  FROM v$log_history a
 WHERE (TO_DATE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'), 1, 8),
                'MM/DD/RR') >= sysdate - 7)
   AND (TO_DATE(substr(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'), 1, 8),
                'MM/DD/RR') <= sysdate)
 GROUP BY SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'), 1, 5)
 ORDER BY SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'), 1, 5);

4 SGA/PGA

4.1 内存分配概况

select name,to_char(value) "value(Byte)"
from v$sga
union all
select name,value
from v$parameter
where name in
('shared pool_size','large_pool_size',
   'java_pool_size','lock_sga');

4.2 Library Cache Reload Ratio(<1%)

Select round((Sum(Reloads) / Sum (Pins)) * 100, 4) "LC_Reload_Ratio%"
From V$Librarycache;
4.3	Data Dictionary Miss Ratio(<15%)
Select Round((((sum(GetMisses)) / sum(Gets)) * 100),4) "DC_Miss_Ratio%"
From V$rowcache;
4.4	共享池使用概况
Select round(sum(a.bytes)/(1024*1024), 2) "Used(MB)",
       round(max(p.value)/(1024*1024), 2) "Size(MB)",
       round((max(p.value)/(1024*1024))-(sum(a.bytes)/(1024*1024)), 2) "Avail(MB)",
       round((sum(a.bytes)/max(p.value))*100, 2) "Used(%)"
from V$sgastat a,
    (select decode(sign(instr(upper(value), 'K') + instr(upper(value),'M')),
                       0, value,
                       1, decode(sign(instr(upper(value), 'K')),
                         1, to_number(1024 * rtrim(substr(value, 1, instr(upper(value), 'K') - 1))),
                           to_number(1024 * 1024 * rtrim(substr(value, 1,instr(upper(value), 'M') - 1))))) value
         from v$parameter
         where name like 'shared_pool_size') p
where a.name in
      ( 'reserved stopper', 'table definiti', 'dictionary cache',
        'library cache', 'sql area', 'PL/SQL DIANA', 'SEQ S.O.');

4.5 共享池建议

select shared_pool_size_for_estimate "Shared Pool Size(estimate)",
       SHARED_POOL_SIZE_FACTOR "Factor",
       estd_lc_size "Libarary Cache Size",
       estd_lc_time_saved "time Saved"
from v$shared_pool_advice;

4.6 DB Buffer Cache(Default) Hit Ratio(>90%)

Select round(100 * (1-(physical_reads/(db_block_gets+consistent_gets))), 4) "BC_Hit _Ratio"
FROM    v$buffer_pool_statistics
WHERE   name = 'DEFAULT';

4.7 DB Buffer Cache Advice

select Name "Pool Name",Block_size,SIZE_FOR_ESTIMATE "Buffer Size",
       SIZE_FACTOR "Factor",ESTD_PHYSICAL_READ_FACTOR "Phy_Read_Factor",
       ESTD_PHYSICAL_READS "ESTD_PHY_READS"
from v$db_cache_advice where ADVICE_STATUS='ON';

4.8 磁盘排序(<5%)

select a.value "Sort(Disk)", b.value "Sort(Memory)",
       round(100*(a.value/decode((a.value+b.value), 0,1,
               (a.value+b.value))),2) "Disk_Sort_Ratio%"
from v$sysstat a, v$sysstat b
where a.name = 'sorts (disk)' and b.name = 'sorts (memory)';

4.9 Log Buffer latch Contention(<1%)

SELECT name "Redo Name", gets, misses, immediate_gets, immediate_misses,
       Decode(gets,0,0,round(misses/gets*100,3)) "Miss_Ratio%",
       Decode(immediate_gets+immediate_misses,0,0,
       round( immediate_misses/(immediate_gets+immediate_misses)*100,3)) "Immediate Misses Ratio%"
FROM v$latch WHERE name IN ('redo allocation', 'redo copy');

4.10 数据缓冲区高速缓存

SELECT physical_reads, db_block_gets, consistent_gets, NAME,
    100 * ( 1 - ( physical_reads / (consistent_gets + db_block_gets - physical_reads))) "Data Buffer Hit Ratio"
FROM v$buffer_pool_statistics;

4.11 重做日志缓冲区

SELECT a.VALUE redo_entries,
       b.VALUE redo_buffer_allocation_retries,
       ROUND((1 - b.VALUE / a.VALUE) * 100, 4) log_buffer_ratio
  FROM v$sysstat a, v$sysstat b
 WHERE a.NAME = 'redo entries'
   AND b.NAME = 'redo buffer allocation retries';

4.12 数据字典高速缓存

SELECT a.VALUE redo_entries,
       b.VALUE redo_buffer_allocation_retries,
       ROUND((1 - b.VALUE / a.VALUE) * 100, 4) log_buffer_ratio
  FROM v$sysstat a, v$sysstat b
 WHERE a.NAME = 'redo entries'
   AND b.NAME = 'redo buffer allocation retries';

4.13 高速缓存

SELECT ROUND((1 - SUM(getmisses) / SUM(gets)) * 100, 1) "Dictionary Cache Hit Ratio"
  FROM v$rowcache;

4.14 排序（磁盘/内存）

SELECT b.VALUE memory_sort,
       a.VALUE disk_sort,
       ROUND((1 - a.VALUE / (a.VALUE + b.VALUE)) * 100, 4) sort_ratio
  FROM v$sysstat a, v$sysstat b
 WHERE a.NAME = 'sorts (disk)'
   AND b.NAME = 'sorts (memory)';

4.15 SGA Memory Map (overall)

SELECT 1 dummy, 'DB Buffer Cache' area, name, round(sum(bytes)/1024/1024,2) "Size/Mb"
  FROM v$sgastat
 WHERE pool is null
   and name = 'db_block_buffers'
 group by name
union all
SELECT 2, 'Shared Pool', pool, round(sum(bytes)/1024/1024,2)
  FROM v$sgastat
 WHERE pool = 'shared pool'
 group by pool
union all
SELECT 3, 'Large Pool', pool, round(sum(bytes)/1024/1024,2)
  FROM v$sgastat
 WHERE pool = 'large pool'
 group by pool
union all
SELECT 4, 'Java Pool', pool, round(sum(bytes)/1024/1024,2)
  FROM v$sgastat
 WHERE pool = 'java pool'
 group by pool
union all
SELECT 5, 'Redo Log Buffer', name, round(sum(bytes)/1024/1024,2)
  FROM v$sgastat
 WHERE pool is null
   and name = 'log_buffer'
 group by name
union all
SELECT 6, 'Fixed SGA', name, round(sum(bytes)/1024/1024,2)
  FROM v$sgastat
 WHERE pool is null
   and name = 'fixed_sga'
 group by name
 ORDER BY 4 desc;

4.16 SGA Memory Map (shared pool)

SELECT 'Shared Pool' area,
       name,
       round(sum(bytes) / 1024 / 1024, 2) "Size/Mb"
  FROM v$sgastat
 WHERE pool = 'shared pool'
   and name in
       ('library cache', 'dictionary cache', 'free memory', 'sql area')
 group by name
union all
SELECT 'Shared Pool' area,
       'miscellaneous',
       round(sum(bytes) / 1024 / 1024, 2) "Size/Mb"
  FROM v$sgastat
 WHERE pool = 'shared pool'
   and name not in
       ('library cache', 'dictionary cache', 'free memory', 'sql area')
 group by pool
 order by 3 desc;

4.17 查看SGA的使用

select COMPONENT,CURRENT_SIZE from V$SGA_DYNAMIC_COMPONENTS;