3.6 系统调优知识
3.6.1.1 生成状态报表( statspack 的使用)
使用(存放位置 @?\rdbms\admin\ )的文件生成报表用户
@?\rdbms\admin\Spcreate.sql 建表
将 timed_statistics 设定 true
使用生成的 perfstat 用户登录,执行以下语句手动收集信息
Exex statspack.snap
Exec statspack.snap(I_SNAP_LEVEL=>0,I_MODEFY_PRAMETER=>TRUE) 0 级,最少 10 最大
使用下面的语句生成状态报表
@?\rdbms\admin\Spreport.sql
其他相关文件
delete stats$snapshot ; 清原来记录数据
@?\rdbms\admin\Saputo.sql
select job from user_jobs 取用户作业号
exec dbms_remove( 作业号 )
timed_statistics=true 要求
@?\rdbms\admin\spdrop.sql ;
3.6.1.2 sql 追踪
设定全部用户跟踪
alter system set sql_trace=true;
用户级别跟踪
alter session set sql_trace=true;
用户的跟踪文件生成在 admin\{pid}\udump\{pid} _ora_{ SPID}.trc 中 ,spid 从下面语句得到
SELECT b.name bkpr, s.username, p.spid,s.sid,s.serial# FROM v$bgprocess b, v$session s, v$process p WHERE p.addr = b.paddr(+) AND p.addr = s.paddr and s.username=user;
DBA 对特定用户跟踪
exec dbms_system_set_Sql_trace_in_session(sid,serial#,true)
信息从下面得到
SELECT b.name bkpr, s.username, p.spid,s.sid,s.serial#,osuser,s.program
FROM v$bgprocess b, v$session s, v$process p
WHERE p.addr = b.paddr(+)
AND p.addr = s.paddr;
/*p.spid 用于 sql_trace 时日志编号, dbms_system.set_sql_trace_in_session(sid,erial#,true)*/
用户的跟踪文件生成在 admin\{pid}\udump 中
系统的跟踪文件生成在 admin\{pid}\bdump\alert_{pid}.log
tkprof.exe 将 log 文件生成格式化文本
在 av Rd(ms) 20 以上说明表空间使用过用频繁,考虑将表分开其他表空间上
系统变量 fast_start_mttr_target 的值要大到不产生 log 等待,当然也可通过加 log 组使其不等待
reao log 大小应为每 30 分钟切换一次
建议表空间的利用率不超 80%
buffer hit 要达 80% 以上为好
3.6.1.3 内存调整
一般的内存分配原则
SGA 50% (其中 80% DATA BUFFER , 15% SHARE POOL , 5 其他)
PGA30%
OS 20%
例如: 2G 的 WINDOWS 的平台, OS 300M , SAG 1.2G,PGA 500M
内存分配的基本单位
SGA《=128M 4M
SGA》128M 64位系统16M,32M系统8M
动态分配时总值不可大于 sga_max_size
通过 V$SGA_DYNAMIC_FREE_MEMORY 取空闲内存空间
在缩小时如果内存空间实际在应用中,CPU利用率将达100%,最后将语句出错。
V$SGASTAT 可看实际的使用情况
Redo log buffer 一般在5M内,可通过 v$sessuon_wait 看是否等, v$sysstat
可也通过报警文件看是否等切换,方法可加组。可通过 nologging (数据库也要设定支持 nologging )方法减少日志文件产生量。
java_pool 没有设定时,使用 shared_pool_size
3.6.1.3.1 shared_pool
本缓冲区用于 sql 语句, pl sql 等的对象保存
Cursor_sharing{Exact|Similar|force} 游标共享设定
Force 方式适用 OLTP 数据库, Exact 方式适合数据仓库, similar 为智能方式
hard parses 硬 SQL 语句分析,每秒要底于 100 次 , 小要加大 shared_pool
soft parse 软 SQL 语句分析, OLTP 要达 90% 以上,小要加大 shared_pool
不建议用无命名 PL SQL 段
如果有大 PL SQL( 存储过程 ) 对象可强制保存于内存,也可加大 SHARED_POOL_RESERVED_SIZE ,大小不可过 SHARED_POOL_SIZE 的 50% ,不然实例不能起动
3.6.1.3.2 db_cache
本缓冲区用于数据库数据对象保存
db_cache_advice 为 on ,可以提出通过企业管理器看到系统建议
通过 select * from v$system_event 进行系统查看。
发现存在 free buffer waits ,说明不能将 data buffer 及时写入 data file;
可通过增加加 CPU 后,加 db_writer_processes=CPU 数改善。
也可设 disk_asynch_io 为 true, 使用异步 IO (前提同要操作系统支持) db_writer_processes=1 时(只有一个 CPU 的情况下),也可通加大 dbwr_io_slaves 来改善。 db_writer_processes>1, 不可用本功能
调整效果排序:异步 IO>CPU>dbwr_io_slaves
Buffer Busy Waits 大说明出现 IO 冲突
Buffer Busy Waits 大 和 dbbock 大说明全表扫描多,说明数据不能读入,可加大
db_cache_size 来改善 .
Undo block 大要加大回滚段(手动管理方式,9I默认是自动管理)
undo header 大要加大回滚段(手动管理方式,9I默认是自动管理)
db_cache 命中率 99% ,不是唯一因素,关系是不要出现等待。建议达 90% 以上。
内存使用建议:
系统可以设三个缓冲区,建表时可设定用那个缓冲区(默认在 db_cache_size )
db_cache_size (默认区)
db_keep_cache_size (常访问,小于 db_keep_cache_size 的 10% 的表可放于本区)
db_recycle_cache_size (一个事物完成后常时间不再使用,或两倍大小于缓冲区)
3.6.2 排序的优化
9I 为专用服务器时系统变量 workarea_size_policy 设定为 auto, statistics_level 设定为 TYPICAL 可获取 v$pga_target_advice 中的优化建议。参数 pga_aggregate_target 值为所有连接用户可用排序内存。
9I 为共享服务器时 workarea_size_policy 设定为 menaul, sort_area_size 值为每用户排序内存。
如果内存不足将使用 TEMP 表空间进行排序 , 排序使用比率 disk/meme 应小于 5%
尽量少用排序 , 如果使用排序功能,尽量在字段上加索引进行优化。
SQL 分析模式 :RBO( 基于规则 ) 方案小表 ( 驱动表 ) 放在最后 , 优先使用索引 , 对 SQL 语句要求严格 (8I 以前的模式 );CBO (基于开销)根据统计值进行选择开销最少 , 性能最优的最佳方式进行 , 但本方式 DBA( 使用 analyze table 语句 ) 要定期进行分析统计 . 系统设定通过 optimizer_mode 系统参数
说明 : 指定优化程序的行为。如果设置为 RULE, 就会使用基于规则的优化程序 , 除非查询含有提示。如果设置为 CHOOSE, 就会使用基于成本的优化程序 , 除非语句中的表不包含统计信息。 ALL_ROWS 或 FIRST_ROWS
始终使用基于成本的优化程序。
值范围 : RULE | CHOOSE | FIRST_ROWS | ALL_ROWS
默认值 : CHOOSE
{ rule ( RBO ) |choose (自动选择) |fist_rows| fist_rows_n|all_row }
3.6.3 统计信息
进行某表的统计分析
EXECUTE dbms_stats.gather_table_stats ('HR','EMPLOYEES');
查看结果
4 设计工具
统一使用 sybase power designer 设计工具,在该工具上完成物理模型的设计。所有的数据库对象尽可能在物理模型上进行设计,而且每个物理模型都要有相应的文字描述。
所有的数据库对象变更以数据库物理模型为基准。为了避免字符敏感问题,产生的脚本以大写字母为标准。