Oracle简易优化技巧

1. 在数据仓库上使用位图索引
2. 在联机事务处理系统上面设定CURSOR_SHARING = SIMILAR, 数据仓库上面为EXACT.如:
   ALTER SYSTEM SET CURSOR_SHARING = SIMILAR;
3. 联机系统倾向于小的数据块(DB_BLOCK_SIZE),推荐8K. 数据仓库倾向于大的数据块
4. 加大一次同时输入输出的数据块数量.
   ALTER SYSTEM SET DB_FILE_MULTIBLOCK_READ_COUNT = n;
5. 命令行获取数据库系统设计. 登录该数据库之后:
   a.) SELECT * FROM CAT;
   b.) DESC 表名;
   c.) SELECT owner, constraint_name, constraint_type, table_name,r_constraint_name FROM user_constraints;
   d.) SELECT owner, constraint_name, table_name, column_name FROM user_cons_columns;
   e.) SELECT index_name, index_type, table_name, uniqueness, tablespace_name FROM user_indexes;
   f.) SELECT index_name, table_name, column_name, column_position FROM user_ind_columns;
6. 命令行导出存储过程/函数的代码
   a.) SELECT object_name, object_type, created, status, last_ddl_time FROM user_objects WHERE object_type IN ('PROCEDURE', 'FUNCTION');
   b.) DESC 名称
   c.) SELECT line, text FROM user_source WHERE name = '名称';
7. 减少排序和全表扫描
   a.) 避免使用DISTINCT关键字,使用变通方法
   b.) 为GROUP BY和ORDER BY使用的列创建索引
   e.) 为已排序的SQL创建索引时,可以使用NOSORT参数
   f.) 使用UNION ALL操作代替UNION操作.(等同于省去类似DISTINCT部分)
   g.) 刷新静态数据字典统计系统信息时,使用ESTIMATE代替COMPUTE.会有少许偏差(大约4%),但是一般在可接受范围内.
         ANALYZE TABLE scott.emp ESTIMATE STATISTICS;
   h.) 对连接要用到的两方面的列,都创建索引.避免全表扫描.
8. 获取SQL执行计划.可能先要运行$ORACLE_HOME/RDBMS/ADMIN/utlxplan.sql才能支持此功能.
   a.) EXPLAIN PLAN FOR sql语句;
   b.) SELECT id, operation, options, object_name, position FROM plan_table;
         或
         SELECT operation, object_owner, object_name, cost FROM v$sql_plan WHERE object_owner = 'xxx'; (本语句只对确实执行的语句有结果,无需EXPLAIN语句)
9. 对编号持续增长且经常删除的表使用反转索引.不过会影响范围查询(即是使用BETWEEN关键字的情况).
   a.) 可在创建索引时加入REVERSE关键字.
   b.) 变现有索引为反转索引 ALTER INDEX xxx REBUILD REVERSE;
   c.) 变反转索引为普通索引 ALTER INDEX xxx REBUILD NOREVERSE;
   d.) 直接转换只对普通类型索引有效.位图和其他类型索引必须删除重建.
10. 基于函数的缩影
    a.) 如: CREATE INDEX emp_salgt_idx ON scott.emp(sal-2000);
    b.) 能加快类似查询的速度: SELECT ename FROM emp WHERE (sal – 2000) < 1000;
    c.) QUERY_REWRITE_ENABLE必须为TRUE才能使用此类功能.
11. 设定SGA参数.spfile范围修改需要重启数据库.
    a.) SGA_MAX_SIZE
         ALTER SYSTEM SET SGA_MAX_SIZE = 1024M SCOPE=SPFILE;
    b.)LOCK_SGA, 尽量保持SGA在内存而不是到交换区.
        ALTER SYSTEM SET LOCK_SGA = TRUE SCOPE=SPFILE;
    c.) PRE_PAGE_SGA. 启动时完全装载SGA.
        ALTER SYSTEM SET PRE_PAGE_SGA = TRUE SCOPE=SPFILE;
    d.) 加大日志缓冲区
         ALTER SYSTEM SET LOG_BUFFER = 字节数 SCOPE+SPFILE;
    e.)自动管理大部分缓存区
        aa.) 设定SGA_TARGET. 最大可等同于MAX_SGA_SIZE
               ALTER SYSTEM SET SGA_TARGET = 1024M;
        bb.) STATISTICS_LEVEL参数必须是ALL或TYPICAL
        cc.) 可自动管理如下缓冲区
               DB_CACHE_SIZE                 SHARED_POOL_SIZE
               LARGE_POOL_SIZE             JAVA_POOL_SIZE
               STREAMS_POOL_SIZE
         dd.)自动管理时,以上各缓冲区的参数值自动变为零. 之后仍可自行定义,定义值即是设定下限.
12. 获取SGA信息
    a.) show sga;
    b.) show parameter sga;
    c.) SELECT * FROM v$sga;
13. 定义PGA
    a.)当WORKAREA_SIZE_POLICY为MANUAL时,需手动设置SORT_AREA_SIZE参数.每个服务器进程独享设定大小的排序区.连接较多时容易大量占用内存.
    b.)设定PGA_AGGREGATE_TARGET参数,之后再将WORKAREA_SIZE_POLICY设为TRUE.系统即自动调度排序区,总大小不超过此处设定的值.
14. 查询排序区大小与系统性能个情况. cache hit应为100%. 有时再怎么提升排序区大小对hit提升也有限时,不应强求.
    a.) SELECT * FROM v$pgastat;
    b.) 获取参考大小         SELECT round(pga_target_for_estimate/1024/1024) AS target_mb, estd_pga_cache_hit_percentage AS cache_hit_percentage, estd_overalloc_count FROM v$pga_target_advice ORDER BY target_mb;
15. 将程序常驻内存
    a.) 获取程序当前共享池使用信息(查询驻留是否成功用)
         SELECT name,namespace,sharable_mem, kept FROM v$db_object_cache WHERE user = 'xxx';
    b.) 驻留内存
         EXECUTE dbms_shared_pool.keep('模式名.程序名');
    c.) 执行以上语句如果出错,可能需要运行dbmspool.sql脚本. 此脚本位于$ORACLE_HOME/RDBMS/ADMIN/目录下.
    d.) 清空共享池,对KEEP的对象无效.
         ALTER SYSTEM FLUSH SHARED_POOL;
    e.) 去除驻留
         EXECUTE dbms_shared_pool.unkeep('模式名.程序名');
16. 缓存数据到内存(还是有可能被淘汰),不应驻留大表以免占用内存.
    a.) 查询当前状况
         SELECT table_name, tablespace_name, cache FROM user_table;
    b.) 驻留
         ALTER TABLE 表名 CACHE;
    c.) 去除
         ALTER TABLE 表名 NOCACHE;
17. 常驻数据到内存
    a.) 查询
         SELECT owner, segment_name, segment_type, blocks FROM dba_segments WHERE owner = 'xxx' AND segment_name = 'yyy';
         和
         SELECT table_name, tablespace_name, buffer_pool, blocks, empty_blocks, avg_space FROM dba_tables WHERE owner = 'xxx' AND table_name = 'yyy';
    
         查询前最好重新统计(ANALYZE)一下信息.
         或执行:          EXECUTE dbms_stats.gather_table_stats('xxx', 'yyy');
    b.) 查询和设定常驻缓冲区大小
         SHOW PARAMETER DB_KEEP_CACHE_SIZE;
         ALTER SYSTEm SET DB_KEEP_CACHE_SIZE = 128M;
         SELECT id, name, block_size, buffers FROM v$buffer_pool;
    c.) 驻留
         ALTER TABLE xxx.yyy STORAGE (BUFFER_POOL KEEP); 
    d.) 去除
         ALTER TABLE xxx.yyy STORAGE (BUFFER_POOL DEFAULT); 
18. 驻留索引与其他对象到内存.大体与上面类似
    a.) 查询
19.      SELECT index_name, table_name, tablespace_name, buffer_pool FROM dba_indexes WHERE owner = 'xxx' AND table_name = 'yyy';
    b.) 驻留
         ALTER INDEX xxx.zzz STORAGE(BUFFER_POOL KEEP);