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 eWHERE 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$parameterwhere 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.8NLS参数设置

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.14DBID

select dbid from v$database;

1.15Flashback是否启动

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) fWHERE a.file_id=f.file_id(+)order by a.tablespace_name,a.File_id;

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

select tablespace_name,contents from dba_tablespaceswhere 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.statusfrom dba_data_files f,v$datafile dwhere d.status=\'OFFLINE\' and f.file_id=File#(+);

2.6处于恢复模式的文件

select f.tablespace_name,f.file_namefrom dba_data_files f, v$recover_file rwhere 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 holesfrom (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) fwhere 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_blockwritesFROM gv$tablespace t, gv$datafile f, gv$filestat sWHERE 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) fstotWHERE D.FILE# = FS.FILE# AND D.TS# = TS.TS#;

2.10Next 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_SEGMENTSWHERE ((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.11Max Extents(>1)已经有90%被使用了的Segments

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

2.12已经分配超过100 Extents的Segments

Select segment_type, owner, segment_name, extents, partition_namefrom dba_segmentswhere 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_mbfrom 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) cwhere 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.freeorder 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 fwhere p.file_id(+) = h.file_idand p.tablespace_name(+) = h.tablespace_nameand f.file_id = h.file_idand f.tablespace_name = h.tablespace_namegroup by h.tablespace_name, f.maxbytesORDER 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 fwhere 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);

4SGA/PGA

4.1内存分配概况

select name,to_char(value) \"value(Byte)\"from v$sgaunion allselect name,valuefrom v$parameterwhere name in(\'shared pool_size\',\'large_pool_size\', \'java_pool_size\',\'lock_sga\');

4.2Library Cache Reload Ratio(<1%)

Select round((Sum(Reloads) / Sum (Pins)) * 100, 4) \"LC_Reload_Ratio%\"From V$Librarycache;4.3Data 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\') pwhere 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_statisticsWHERE 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 bwhere 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.15SGA 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 nameunion allSELECT 2, \'Shared Pool\', pool, round(sum(bytes)/1024/1024,2) FROM v$sgastat WHERE pool = \'shared pool\' group by poolunion allSELECT 3, \'Large Pool\', pool, round(sum(bytes)/1024/1024,2) FROM v$sgastat WHERE pool = \'large pool\' group by poolunion allSELECT 4, \'Java Pool\', pool, round(sum(bytes)/1024/1024,2) FROM v$sgastat WHERE pool = \'java pool\' group by poolunion allSELECT 5, \'Redo Log Buffer\', name, round(sum(bytes)/1024/1024,2) FROM v$sgastat WHERE pool is null and name = \'log_buffer\' group by nameunion allSELECT 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.16SGA 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 nameunion allSELECT \'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;

经典老歌推荐