【转】DBA日常维护SQL整理

database 概况信息检查

# 检查 database 基本信息

 查看数据文件信息 

# 检查表空间数据文件信息

# 检查表空间

# 检查数据文件状态  

# 检查表空间使用情况

# 查询临时 segment 使用情况

# 查看临时表空间大小 

 # 查看临时表空间的使用情况

# 查找消耗较多临时表空间的sql

# 查看当前临时表空间使用大小与正在占用临时表空间的sql语句

# 查看数据文件信息 , 若文件较多可以根据需要字段进行排序 输出 top 10

# 查看所有数据文件 i/ o 情况 , 若文件太多 , 可以改写 为 top 10 select *( order by xx desc) where

rownum<=10。其中phyrds为物理读的次数极为Reads，phywrts为物理写的次数极为Writes，phyblkrd为物理块读的次数即为br，phyblkwrt为物理写的次数即为bw。readtime为耗费在物理读上的总时间极为RTime，writetim为耗费在物理写上的总时间即为WTime。这两个值只有在参数timed_statistics参数为true时才有效。

# 获取 top 10 热 segment

 # 判断物理读最多的 object

# 查看热点数据文件 ( 从单块读取时间判断 )

查看redo 

# 检查日志切换频率

# 检查 lgwr i/o 性能 (time_waited/total_waits:表示平均lgwr写入完成时间若>1表示写入过慢 ) 

# 查询 redo block size

# 查看 user commit 次数 

# 查看系统运行时间 

# 计算出每秒用户提交次数 

 # 计算出每个事务平均处理多少个 redo block 

sga,pga, 命中率 

# sga,pga, 命中率

# 检查 sga

# 查看buffer cache设置建议

# 查看 cache 池 

# 查看 buffer cache 中 defalut pool 命中率 

# 检查 shared pool 

# 检查 shared pool 中 library cache

# 检查整体命中率 (library cache) 

# 检查 shared pool free space 

# 每个子shared pool 由单独的 shared pool latch保护，查看他们的命中率 shared pool latch，用于shared pool空间回收分配使用的latch 

# 使用 v$shared_pool_advice 计算不同 shared pool 大小情况下，响应时间， S 单位 

# 查看 shared pool 中 各种类型的 chunk 的大小数量 

# 查看是否有库缓冲有关的等待事件 

# 查询 sga 中各个 pool 情况 

 # 查看使用 shard_pool 保留池情况 

Oracle 专门从共享池内置出一块区域来来分配内存保持这些大块。这个保留共享池的默认大小是共享池的5%(_shared_pool_reserved_pct 5 控制 ) oracle 建设置为 10% 。大小通过参数 SHARED_POOL_RESERVED_SIZE 改。它是从共享池中分配，不是直接从 SGA 中分配的，它是共享池的保留部分，专门用于存储大块段#shared pool 中内存大于 _SHARED_POOL_RESERVED_MIN_ALLOC 将放入 shared pool 保留池 , 保留池维护一个单独的 freelist,lru ，并且不会在 lru 列表存recreatable 类型 chunks ，普通 shared pool 的释放与 shared pool 保留池无关。 

# 关于设置 SHARED_POOL_RESERVED_SIZE

#1.如果系统出现ora-04031, 发现请求内存都是大于 _SHARED_POOL_RESERVED_MIN_ALLOC (default 10GR2 4400) , 且v$shared_pool_reserved 中有大量 REQUEST_MISSES( 并且可以看下LAST_MISS_SIZE )表示 SHARED_POOL_RESERVED_SIZE 太小了需要大的内存的请求失败 , 那么需要加大SHARED_POOL_RESERVED_SIZE

#2. 如果 ora-04031 请求内存出现在 4100-4400 并造成 shared pool lru 合并 , 老化换出内存 , 可以调小 _SHARED_POOL_RESERVED_MIN_ALLOC 让此部分内存进入shared reserved pool, 相应的加大SHARED_POOL_RESERVED_SIZE

#3. 从 v$shared_pool_reserved 来判断 , 如果 REQUEST_FAILURES>0( 出现过 ora-04031) 且LAST_FAILURE_SIZE( 最后请求内存大小 )>_SHARED_POOL_RESERVED_MIN_ALLOC表示 shared reserved pool 缺少连续内存 , 可以加大 SHARED_POOL_RESERVED_SIZE, 减少 _SHARED_POOL_RESERVED_MIN_ALLOC 少放对象 , 并相对加大 shared_pool_size

# 要是反过来 REQUEST_FAILURES>0( 出现过 ora-04031) 且 LAST_FAILURE_SIZE( 最后请求内存大小)<_SHARED_POOL_RESERVED_MIN_ALLOC, 表示 在 shared pool 中缺少连续内存 , 可以加减少_SHARED_POOL_RESERVED_MIN_ALLOC 多放入一些对象 , 减少 sharedpool 压力 , 适当加大shared_pool_size,SHARED_POOL_RESERVED_SIZE

# 查询还保留在 library cache 中，解析次数和执行次数最多的 sql( 解析 * 执行 )

 # 查看 pga 

# 查看pga建议

# 查看数据库 cache 或 keep 了哪些 object

# 取消 cache 或 keep(keep pool) 

检查undo 

# 检查 undo rollback segment 使用情况 

 # 计算每秒钟产生的 undoblk 数量 

# 计算undo表空间大小

 # 查询 undo 具体信息 

# 在内存中排序比率 ( 最优排序 ) 

# 查看当前系统undo使用情况

# 查看当前系统和undo相关的会话

查看对象

# 检查数据库中无效对象

# 检查是否有禁用约束 

# 检查是否有禁用 trigger 

# 在某个用户下找所有的索引

# 检查与索引相关的字段

查看当前系统状态

# 检查系统中当前等待事件

# 查看经常被使用而没有pin在内存中的对象

# 形成生成pin住共享池中当前没有被pin住的对象的sql语句。在执行exec sys.DBMS_SHARED_POOL.keep('JXXXT.IN_GZ_LOGS','P');可能会报出未定义的错误，需要在sqlplus下执行脚本$ORACLE_HOME/rdbms/admin/dbmspool.sql

# 查看使用了超过10MB内存 而没有pin的对象

# 查看大的没有被pin住的对象.

# 查看大的没有被pin住的过程，包和函数

需要被pin入内存中的对象主要有：常用的较大的存储对象，如standard、diutil包；编译的常用的triggers；sequences。

最好在开机时就将其pin入内存中。这样，既是使用命令alter system flush shared_pool时，也不会讲这些object flush掉。具体pin对象到内存的方法使用DBMS_SHARED_POOL.keep存储过程。可以用unkeep方法解除其pin状态。

db_object_cache和碎片化

碎片化造成在共享池中虽然有许多小的碎片可以使用，但没有足够大的连续空间，这在共享池中是普遍的现象。消除共享池错误的关键就是即将加载对象的大小是否可能会产生问题。一旦知道了这个存在问题的PL/SQL，那么就可以在数据库启动时(这时共享池是完全连续的)就将这个代码固定。这将确保在调用大型包时，它已经在共享池里，而不是在共享池中搜索连续的碎片(在使用系统时，这些碎片可能就不复存在)。可以查询V$DB_OBJECT_CACHE视图来判断PL/SQL是否很大并且还没有被标识为"kept"的标记。今后需要加载这些对象时，可能会产生问题(因为它们的大小和需要占用大量连续的内存)。通过查询V$DB_OBJECT_CACHE表，可以发现那些没有固定，但由于所需空间太大而很有可能导致潜在问题的对象。

# 查询一下回滚段的使用情况，其中USED_UREC为undo记录的使用条目数，USED_UBLK为undo块的使用数目

# 查看锁住对象的会话信息，操作系统进程信息

 # 根据进程查看sql

# 查看被锁的表的被锁时间

# 查看被锁的对象和引起锁的sql

# 查看锁定的会话信息

 # 杀死相关会话

# 如果出现ora-00031错误，则

# 亦可先查询该会话相对应的操作系统进程，在操作系统上进行kill

TOP SQL

# 逻辑读 TOP 10

# 物理读 TOP 10

# 消耗CPU TOP 10

# 执行时间 TOP 10

查找需要使用绑定变量的sql

再 select sql_text from v$sqlarea where sql_text like 'insert into test %'; 找出具体的sql代码