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> 【ORACLE精品脚本笔记】提供了许多用于监控和优化Oracle数据库性能的关键脚本。以下是这些脚本涉及的主要知识点： 1. **监控内存和硬盘的排序比率**：通过查询`v$sysstat`视图，我们可以关注`sorts (memory)`和`sorts (disk)`这两项指标。理想情况下，内存排序应占主导，硬盘排序比率应小于0.10。如果比例过高，可以考虑增大`sort_area_size`参数以改善性能。 2. **监控当前运行的SQL语句**：使用`v$session`和`v$sqltext`视图可以追踪当前数据库中正在执行的SQL语句，包括操作的用户名和SQL文本，这对于找出资源消耗大的查询非常有用。 3. **监控字典缓冲区**：通过查询`v$librarycache`和`v$rowcache`视图，我们可以获取字典缓冲区的使用情况。良好的状态是`LIB CACHE`和`ROW CACHE`比率接近0%，这意味着大部分请求都直接命中缓存。 4. **查询Oracle字符集**：使用`props$`视图可以查看数据库使用的字符集，这在处理多语言或字符编码问题时非常有用。 5. **监控MTS（多线程服务器）**：通过查询`v$dispatcher`视图，可以了解共享服务器的繁忙程度以及调度器等待情况。当`busy/(busy+idle)`比例大于0.5或`dispatcher waits`较高时，可能需要调整相关参数以提高MTS性能。 6. **碎片管理**：监测表空间的碎片程度有助于优化空间利用率。例如，通过`dba_free_space`视图可以查找碎片，并使用`ALTER TABLESPACE COALESCE`和`ALTER TABLE DEALLOCATE UNUSED`命令进行整理。 7. **查看高碎片程度的表**：使用`dba_segments`视图，可以找出具有过多段的表，这可能是碎片问题的标志。可以通过重建索引或重新组织表来解决这个问题。 8. **检查表和索引的存储情况**：查询`dba_extents`视图可以获取表和索引的空间使用情况，帮助我们识别潜在的空间浪费或优化需求。 此外，脚本还包含了一些用于统计用户对象、表、索引及其约束的实用工具，例如`USER_TAB_COLUMNS`、`USER_INDEXES`、`USER_IND_COLUMNS`和`USER_TABLES`等视图，这些可以帮助数据库管理员了解用户数据的具体存储位置和索引结构。 这些脚本和查询对于日常数据库维护、性能优化和问题排查非常有价值，它们能够提供深入的洞察力，帮助我们更好地管理和调整Oracle数据库。