ORACLE精品脚本笔记

精品脚本手记（压箱的笔记拿出来）如果觉得好，强烈要好评。内容缩影：<br><br>11. 监控内存和硬盘的排序比率，最好使它小于 .10，增加 sort_area_size <br><br>SELECT name, value FROM v$sysstat WHERE name IN ('sorts (memory)', 'sorts (disk)'); <br><br><br>12. 监控当前数据库谁在运行什么SQL语句 <br><br>SELECT osuser, username, sql_text from v$session a, v$sqltext b <br>where a.sql_address =b.address order by address, piece; <br><br>13. 监控字典缓冲区 <br><br>SELECT (SUM(PINS - RELOADS)) / SUM(PINS) "LIB CACHE" FROM V$LIBRARYCACHE; <br>SELECT (SUM(GETS - GETMISSES - USAGE - FIXED)) / SUM(GETS) "ROW CACHE" FROM V$ROWCACHE; <br>SELECT SUM(PINS) "EXECUTIONS", SUM(RELOADS) "CACHE MISSES WHILE EXECUTING" FROM V$LIBRARYCACHE; <br><br>后者除以前者,此比率小于1%,接近0%为好。 <br><br>SELECT SUM(GETS) "DICTIONARY GETS",SUM(GETMISSES) "DICTIONARY CACHE GET MISSES" <br>FROM V$ROWCACHE <br><br>14. 找ORACLE字符集 <br><br>select * from sys.props$ where name='NLS_CHARACTERSET'; <br><br>15. 监控 MTS <br><br>select busy/(busy+idle) "shared servers busy" from v$dispatcher; <br><br>此值大于0.5时，参数需加大 <br><br>select sum(wait)/sum(totalq) "dispatcher waits" from v$queue where type='dispatcher'; <br>select count(*) from v$dispatcher; <br>select servers_highwater from v$mts; <br><br>servers_highwater接近mts_max_servers时，参数需加大 <br><br>16. 碎片程度 <br><br>select tablespace_name,count(tablespace_name) from dba_free_space group by tablespace_name <br>having count(tablespace_name)>10; <br><br>alter tablespace name coalesce; <br>alter table name deallocate unused; <br><br>create or replace view ts_blocks_v as <br>select tablespace_name,block_id,bytes,blocks,'free space' segment_name from dba_free_space <br>union all <br>select tablespace_name,block_id,bytes,blocks,segment_name from dba_extents; <br><br>select * from ts_blocks_v; <br><br>select tablespace_name,sum(bytes),max(bytes),count(block_id) from dba_free_space <br>group by tablespace_name; <br><br>查看碎片程度高的表 <br><br>SELECT segment_name table_name , COUNT(*) extents <br>FROM dba_segments WHERE owner NOT IN ('SYS', 'SYSTEM') GROUP BY segment_name <br>HAVING COUNT(*) = (SELECT MAX( COUNT(*) ) FROM dba_segments GROUP BY segment_name); <br><br>17. 表、索引的存储情况检查 <br><br>select segment_name,sum(bytes),count(*) ext_quan from dba_extents where <br>tablespace_name='&tablespace_name' and segment_type='TABLE' group by tablespace_name,segment_name; <br><br>select segment_name,count(*) from dba_extents where segment_type='INDEX' and owner='&owner' <br>group by segment_name; <br><br>