oracle 优化4

一. 设置合适的 SGA
    常常有人抱怨服务器硬件很好, 但是 Oracle 就是很慢. 很可能是内存分配不合理造成的.
1) 假设内存有 512M, 这通常是小型应用. 建议 Oracle 的 SGA 大约 240M,
其中: 共享池(SHARED_POOL_SIZE)可以设置 60M 到 80M, 根据实际的用户数、查询等来定.
数据块缓冲区可以大致分配 120M-150M:
8i 下需要设置 DB_BLOCK_BUFFERS, DB_BLOCK_BUFFER * DB_BLOCK_SIZE 等于数据块缓冲区大小.
9i 下的数据缓冲区可以用 db_cache_size 来直接分配.

2) 假设内存有 1G, Oracle 的 SGA 可以考虑分配 500M: 共享池分配 100M 到 150M, 数据缓冲区分配 300M 到 400M.

3) 内存 2G, SGA 可以考虑分配 1.2G, 共享池 300M 到 500M, 剩下的给数据块缓冲区.

4) 内存 2G 以上: 共享池 300M 到 500M 就足够, 再多也没有太大帮助;
(Biti_rainy 有专述)数据缓冲区是尽可能的大, 但是一定要注意两个问题:
一是要给操作系统和其他应用留够内存,
二是对于 32 位的操作系统, Oracle 的 SGA 有 1.75G 的限制. 有的 32 位操作系统上可以突破这个限制

二. 分析表和索引, 更改优化模式
    Oracle 默认优化模式是 CHOOSE, 在这种情况下, 如果表没有经过分析, 经常导致查询使用全表扫描, 而不使用索引. 这通常导致磁盘 I/O 太多, 而导致查询很慢. 如果没有使用执行计划稳定性, 则应该把表和索引都分析一下, 这样可能直接会使查询速度大幅提升. 分析表命令可以用 ANALYZE TABLE 分析索引可以用 ANALYZE INDEX 命令. 对于少于 100 万的表, 可以考虑分析整个表, 对于很大的表, 可以按百分比来分析, 但是百分比不能过低, 否则生成的统计信息可能不准确. 可以通过 DBA_TABLES 的 LAST_ANALYZED 列来查看表是否经过分析或分析时间, 索引可以通过 DBA_INDEXES 的 LAST_ANALYZED 列. 
    下面通过例子来说明分析前后的速度对比.（表 USER 大约有 35 万数据, 有主键）首先在 SQLPLUS 中打开自动查询执行计划功能.
(第一次要执行 \RDBMS\ADMIN\utlxplan.sql 来创建 PLAN_TABLE 这个表)
　　SQL> SET AUTOTRACE ON
　　SQL> SET TIMING ON
通过 SET AUTOTRACE ON 来查看语句的执行计划, 通过 SET TIMING ON 来查看语句运行时间.
　　SQL> select count(*) from USER;
　　COUNT(*)
　　----------
　　346639
　　
　　已用时间: 00: 00: 21.38
　　
　　Execution Plan
　　　　0 SELECT STATEMENT Optimizer=CHOOSE
　　1 0 SORT (AGGREGATE)
　　2 1 TABLE ACCESS (FULL) OF 'USER'
　　……………………
    请注意上面分析中的 TABLE ACCESS(FULL), 这说明该语句执行了全表扫描. 而且查询使用了 21.38 秒. 这时表还没有经过分析.
    下面我们来对该表进行分析:
   
　　SQL> analyze table USER compute statistics;
    表已分析. 已用时间: 00: 05: 357.63.
   
    然后再来查询:
   
　　SQL> select count(*) from USER;
　　COUNT(*)
　　----------
　　346639
　　
　　已用时间: 00: 00: 00.71
　　
　　Execution Plan
　 
　　0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=351 Card=1)
　　1 0 SORT (AGGREGATE)
　　2 1 INDEX (FAST FULL SCAN) OF 'PK_USERID' (UNIQUE) (Cost=351 Card=346351)
　　…………………………
    请注意, 这次时间仅仅用了 0.71 秒! 这要归功于 INDEX(FAST FULL SCAN). 通过分析表, 查询使用了 PK_USERID 索引, 磁盘 I/O 大幅减少, 速度也大幅提升! 下面的实用语句可以用来生成分析某个用户的所有表和索引, 假设用户是 ORA9I:
　　SQL> set pagesize 0
　　SQL> spool d:\analyze_tables.sql;
　　SQL> select 'analyze table '||owner||'.'||table_name||' compute statistics;' from dba_tables where owner='ORA9I';
　　SQL> spool off
　　SQL> spool spool d:\analyze_indexes.sql;
　　SQL> select 'analyze index '||owner||'.'||index_name||' compute statistics;' from dba_indexes where owner='ORA9I';
　　SQL> spool off
　　SQL> @d:\analyze_tables.sql
　　SQL> @d:\analyze_indexes.sql
    解释: 上面的语句生成了两个 sql 文件, 分别分析全部的 ORA9I 的表和索引. 如果需要按照百分比来分析表, 可以修改一下脚本.
通过上面的步骤, 我们就完成了对表和索引的分析, 可以测试一下速度的改进. 建议定期运行上面的语句, 尤其是数据经过大量更新.
当然, 也可以通过 dbms_stats 来分析表和索引, 更方便一些. 但是我仍然习惯上面的方法, 因为成功与否会直接提示出来.

三. 将常用的小表、索引钉在数据缓存 KEEP 池中
    内存上数据读取速度远远比硬盘中读取要快, 据称, 内存中数据读的速度是硬盘的 14000 倍! 如果资源比较丰富,
把常用的小的、而且经常进行全表扫描的表给钉内存中, 当然是再好不过了. 可以简单的通过 ALTER TABLE tablename CACHE 来实现,
在 ORACLE8i 之后可以使用 ALTER TABLE table STORAGE(BUFFER_POOL KEEP). 一般来说, 可以考虑把 200 数据块之内的表放在 keep 池中,
当然要根据内存大小等因素来定.
    关于如何查出哪些表或索引符合条件, 可以使用本文提供的 access.sql 和 access_report.sql. 这两个脚本是著名的 Oracle 专家 Burleson 写的, 你也可以在读懂了的情况下根据实际情况调整一下脚本. 对于索引, 可以通过 ALTER INDEX indexname STORAGE(BUFFER_POOL KEEP)来钉在 KEEP 池中.
    将表定在 KEEP 池中需要做一些准备工作. 对于 ORACLE9i 需要设置 DB_KEEP_CACHE_SIZE, 对于 8i, 需要设置 buffer_pool_keep.
在 8i 中, 还要修改 db_block_lru_latches, 该参数默认是 1, 无法使用 buffer_pool_keep. 该参数应该比 2*3*CPU 数量少,
但是要大于 1, 才能设置 DB_KEEP_CACHE_BUFFER. buffer_pool_keep 从 db_block_buffers 中分配, 因此也要小于 db_block_buffers.
设置好这些参数后, 就可以把常用对象永久钉在内存里.

四. 调整排序参数
1) SORT_AREA_SIZE: 默认的用来排序的 SORT_AREA_SIZE 大小是 32K, 通常显得有点小, 一般可以考虑设置成 1M(1048576). 这个参数不能设置过大, 因为每个连接都要分配同样的排序内存.
2) SORT_MULTIBLOCK_READ_COUNT: 增大这个参数可以提高临时表空间排序性能, 该参数默认是 2, 可以改成 32 来对比一下排序查询时间变化. 注意, 这个参数的最大值与平台有关系.