一：前言
一：调整初始化参数
1： Oracle 初始化参数分类
2：主要系统调优参数介绍
二：系统全局区 sga 优化
1：调整内存分配
2：调整日志缓冲区
1：查询当前oracle 实例的日志缓冲区的大小
1 2 SYS@orcl> select distinct lebsz as log_cache_size from x$kccle; 3 4 LOG_CACHE_SIZE 5 -------------- 6 512 7 0 8 9 SYS@orcl>
3：调整共享池
1 2 SYS@orcl> select sum(pins) as sum_pins/*请求存取数*/,sum(reloads) as sum_reloads /*不命中数*/ from v$librarycache; 3 4 SUM_PINS SUM_RELOADS 5 ---------- ----------- 6 11151706 52931 7 8 SYS@orcl>
1 2 SYS@orcl> select sum(pins) as sum_pins/*请求存取数*/,sum(reloads) as sum_reloads /*不命中数*/ from v$librarycache; 3 4 SUM_PINS SUM_RELOADS 5 ---------- ----------- 6 11151706 52931 7 8 SYS@orcl>1 SYS@orcl> select sum(gets) as sum_pins /*请求存取数*/,sum(getmisses) as sum_reloads/*不命中数*/ from v$rowcache; 2 3 SUM_PINS SUM_RELOADS 4 ---------- ----------- 5 14853534 220272 6 7 SYS@orcl> 8
1 SYS@orcl> select sum(value)||'字节' as value_byte /*当前分配给所有会话的内存数*/ from v$sesstat a ,v$statname b where name='session uga memory' and a.statistic#=b.statistic#; 2 VALUE_BYTE 3 -------------------------------------------- 4 8376368字节 5 6 SYS@orcl>
1 SYS@orcl> select sum(value)||'字节' as value_type /*曾经分配给所有会话的最大内存数*/ from v$sesstat a,v$statname b where name='session uga memory max' and a.statistic#=b.statistic#; 2 3 VALUE_TYPE 4 -------------------------------------------- 5 25527396字节 6 7 SYS@orcl>
4：调整数据库缓冲区
示例：查询一段时间内 v$sysstat表中的统计信息
1 SYS@orcl> select name ,value from v$sysstat where name in ('db block gets','consistent gets','physical reads'); 2 3 NAME VALUE 4 ---------------------------------------------------------------- ---------- 5 db block gets 2445 6 consistent gets 145199 7 physical reads 8414 8 9 SYS@orcl>
5：SGA调优建议
三：排序区优化
1 SYS@orcl> show parameter sort_area_size; 2 3 NAME TYPE VALUE 4 ------------------------------------ ----------- ------------------------------ 5 sort_area_size integer 65536 6 7 SYS@orcl> show parameter sort_area_ 8 9 NAME TYPE VALUE 10 ------------------------------------ ----------- ------------------------------ 11 sort_area_retained_size integer 0 12 sort_area_size integer 65536 13 SYS@orcl>
1：排序区与其他内存区的关系
1 2 SYS@orcl> show parameter large_pool_size; 3 4 NAME TYPE VALUE 5 ------------------------------------ ----------- ------------------------------ 6 large_pool_size big integer 0 7 SYS@orcl> alter system set large_pool_size=32M; 8 9 System altered. 10 11 SYS@orcl> show parameter large_pool_size ; 12 13 NAME TYPE VALUE 14 ------------------------------------ ----------- ------------------------------ 15 large_pool_size big integer 32M 16 SYS@orcl>
2：排序活动
3：专用模式下排序区的调整
1：sort_area_size
2：sort_area_retained_size
示例：查看所有服务器进程使用的内存的总量
1 SYS@orcl> show parameter pga_aggregate_target; 2 3 NAME TYPE VALUE 4 ------------------------------------ ----------- ------------------------------ 5 pga_aggregate_target big integer 0 6 SYS@orcl>示例：查看 PGA使用的管理方式
1 SYS@orcl> show parameter workarea_size_policy; 2 3 NAME TYPE VALUE 4 ------------------------------------ ----------- ------------------------------ 5 workarea_size_policy string AUTO 6 SYS@orcl>
示例：查看会话信息及堆栈空间的大小
1 SYS@orcl> show parameter sort_area_size; 2 3 NAME TYPE VALUE 4 ------------------------------------ ----------- ------------------------------ 5 sort_area_size integer 65536 6 SYS@orcl>
