1.内存调优
注:SGA中的shared pool中,系统自动优先缓存datadictionary cache,对系统性能影响较大的是library cache。
1.1 library cache 调优(v$librarycache/v$sql/v$sqlarea/v$sqltext/v$db_object_cache)
判断:要不要调整library cache?判断条件:v$librarycache.pinhitratio|gethitratio尽量接近100%
1.1.1 存储对象所消耗的内存预估:
select sum(SHARABLE_MEM) from v$db_object_cache;
1.1.2 经常执行的sql消耗的内存预估:
select sum(SHARABLE_MEM) from v$sqlarea where EXECUTIONS>100;
1.1.3 每个用户打开游标,需要250个字节:
select sum(250*USERS_OPENING) from v$sqlarea;
1.1.4 keep在shared pool中:
先创建dbms_shared_pool:
@?/rdbms/admin/dbmspool.sql
创建后:
execute dbms_shared_pool.keep('package_name');----------------------将package keep
execute dbms_shared_pool.keep('address,hash_value');----------------将匿名的块 keep
1.2 data dictionary cache 调优(v$rowcache)
判断:要不要调整data dictionary cache?
判断标准:v$rowcache.(gets-getmisses)/gets尽量接近100%
1.2.1 由于没有单独调整数据字典高速缓冲的参数,但是系统又优先缓存data dictionary cache,只能调整shared pool的总体大小。
1.3 UGA
1.3.1 使用共享服务器模式的时候,如没有配置large pool,用户会话数据(排序等等)和游标状态(共享sql)会在SGA。
因此如果发现一个报表系统shared pool特别大,有没有配置large pool,请检查是否使用了共享服务器模式。
1.3.2 UGA预估:
SELECT 'current session uga memory',SUM(VALUE)/1024 size_kb FROM v$mystat a,v$statname b
WHERE a.STATISTIC#=b.STATISTIC#
AND b.NAME IN ('session uga memory')
GROUP BY NAME
UNION ALL
SELECT decode(name,'session uga memory','all session uga memory',name),SUM(VALUE)/1024 size_kb FROM v$sesstat a,v$statname b
WHERE a.STATISTIC#=b.STATISTIC#
AND b.NAME IN ('session uga memory','session uga memory max')
GROUP BY NAME;
1.4 db buffer cache(V$sysstat/v$buffer_pool_statistics/v$buffer_pool/v$sysstat/v$sess_io/v$wait_stat)
1.4.1 db buffer caceh和datafile之间的交互:
------DBWn-------->
buffer cache datafile
<---服务器进程-----
1.4.2dbbuffercache和LRU列表:
传入的块从LRU的冷端复制到缓冲区中,然后该缓冲区的数据会放到LRU列表的中部,在根据情况上下浮动。
1.4.3DB_CACHE_ADVICEoff-ready-on
1.4.4dbwr什么时候将dirtybuffer写出:A.checkpoint 队列到达阈值;B.LRU中显示没有空余的buffer;C.logwr指示已经发出checkpoint信号;D.表空间变化;E.Dropobject;F.cleanshutdown
判断:要不要调整dbcachesize:命中率小于90%
判断标准:SQL> SELECT 1 - (phy.value – lob.value – dir.value)
/ ses.value "CACHE HIT RATIO"
2 FROM v$sysstat ses, v$sysstat lob,
3 v$sysstat dir, v$sysstat phy
3 WHERE ses.name = 'session logical reads'
4 AND dir.name = ‘physical reads direct'
5 AND lob.name = 'physical reads direct (lob)'
6 AND phy.name = 'physical reads';
注:因为直接读和lob的读取不经过buffer pool缓存,因此整个physical read要减去这2项。
启动开始有较多的物理读,会影响buffer caceh命中率的统计
1.4.5 影响buffer cache的因素:全表扫描、程序、命中率分布不均、可随机访问的大表(其实也是全表扫描)
1.4.6 keep一般为default的10%,recycle一般是default的2倍
1.4.7 空闲列表 :A.单cpu增加空闲列表不会增加性能 B.
1.4.8 相关视图: v$sess_io:预估recycle
1.5 log buffer
1.5.1 诊断标准:A.v$sysstat中的redo buffer allocation retries/redo entries<10% B.$session_wait中不应出现logbufferspace
1.5.2 对策:加大logbuffer,或者将relog转移到性能好的磁盘上
1.5.3 深入分析,lgwr变慢的原因:lgwr之前和之后的工作是在logbuffer和redolog中,分析这2者的情况:
1.5.3.1redolog太小,而logbuffer较大,因此需要频繁的切换redolog,当本次切换还未完成时,又轮到下次切换,出现等待(表现为system_event中的logfileswitchcompletion%过多)
1.5.3.2redolog太大或者redolog组不够,当上次的logbuffer中的内容还没完全写入到redolog中,需要用到这块的logbuffer了,出现等待,即redolog为高active状态(非current和inactive,未完成checkpoint),需要加多redolog组或者调整调整FAST_START_MTTR_TARGET,加大checkpoint频率(表现为system_event中'logfileswitch (check%';检查点未完成过多)
1.5.3.3redolog切换不了,可能是写archlog过慢,或者archlog空间爆满。(表现为system_event中logfileswitch (arch%';archlog未完成过多)
1.6javapool 的优化
判断:要不要调整javapool:v$sgastat中javapool 的freememory 和memoryinuse的比例
2IO优化
2.1v$filestat
2.2 条带化:A.raidB.数据文件条带化 C.altertableallocateextent
2.3 增加db_file_multiblock_read_count,减少IO次数
2.4 减少v$sysstat中的longtables(大于4blocks的表)
2.5 关于checkpoint:
2.5.1 增量chkpt:
checkpoint队列:1.dirtybuffer:第一次数据块被修改时就写入chkpt队列中。2.RBA:redolog中第一次修改数据块的RBA也被记录在chkpt队列
每隔3秒做一次增量chkpt,不强制写数据文件,只是将chkpt队列中的第一个(即时间至今最长的一个)RBA写入到控制文件,实例crash的恢复,即redolog中该RBA之后的内容。
增量chkpt发生条件:1.每隔3秒做增量chkpt,更新控制文件;2.switchlogfile,更新控制文件和数据文件头
2.5.2 全chkpt:
完整检查点:1.dirtybuffer写入到datafile ;2.cleanshutdown;3.altersystemcheckpoint
表空间检查点:表空间变化时(backup、offline)
3 排序优化
3.1sort_area_size和sort_area_retained_size
3.2CREATE_BITMAP_AREA_SIZE和BITMAP_MERGE_AREA_SIZE:创建索引速度有关
3.3 需要排序的操作:1.建检索 2.维护索引 3.orderby/groupby/distinct/union/minus/intersect4.SortMergeJoin5.analyze
3.4 避免排序的操作:1.createindexnosort(要求事先已经asc排序) 2.unionall3.analyzeforcolumn4.analyzeestimate
3.5 判断:要不要调整排序区:(v$sysstat.name='sorts (disk)'/v$sysstat.name='sorts (memory)')<5% 注意:dss中该比例不适用。
3.6 对策:增加sort_area_size和PGA_AGGREGATE_TARGET
4latch优化
4.1 重要的latchfree
4.1.1sharedpool和librarycache的:sql没有绑定变量(检查v$sqlarea.parse_call,v$sqlarea.EXECUTIONS)或者存在热块或者为共享服务器模式但是没有配largepool
4.1.2dbbuffercache的lru:基于dbbuffercache大量的排序或者大量的indexfullscan和fulltablescan
5undo优化
5.1undo使用:(1)读一致性 (2)dml的事务回退 (3)事务故障恢复,打开数据库时rollback未commit的事务
5.2 何时需要加回退段(手工管理):
SELECTsum(waits)* 100 /sum(gets) "Ratio",
sum(waits) "Waits", sum(gets) "Gets"
FROMv$rollstat;
SELECTevent, total_waits, total_timeouts
FROMv$system_event
WHEREeventLIKE 'undosegmenttxslot';
SELECTclass, countFROMv$waitstat
WHEREclassLIKE '%undo%';
5.3 大事务指定回滚段(提交后结束):
sys@ORA11G(192.168.0.11)> set transaction use rollback segment "_SYSSMU1_1193229161$";
事务处理集。
5.4 shared server
题外话:判断是否shared server:
(1).show parameter shared_server 大于0为共享服务器模式
(2).select server from v$session 看到DEDICATED为专用;看到SHARED即为共享,且shared_server_process正在对其其他服务;看到NONE为也为共享,且shared_server_process尚未对其其他服务。
5.4.1 监控视图:V$SHARED_SERVER_MONITOR
V$DISPATCHER繁忙率=busy/(busy+idle) 等待时间=wait/totalq
V$DISPATCHER_RATE
5.4.2 调整参数:SHARED_SERVERS--共享服务器进程的数量
DISPATCHERS-----调度进程的数量
5.5 sql调优
5.5.1 optimizer_mode:choose(默认,如果涉及的任一表有统计信息,就用CBO的all_rows,否则使用RBO)
5.5.2 执行计划中的信息:行访问方法,连接顺序,连接方法,分布式事务访问(不包含远程节点),子查询
5.5.3 创建存储概要:参数文件 CREATE_STORED_OUTLINES------------------------------------------指定创建存储概要的名称(可取名为OLTP、DSS,或者TRUE、FALSE)。
参数文件 USE_STORED_OUTLINES---------------------------------------------指定需要使用的存储概要名称(可取名为OLTP、DSS,或者TRUE、FALSE,在session级或system级指定后select。
参数文件 USE_PRIVATE_OUTLINES--------------------------------------------专用概要,仅用于当前会话,如不存在,不使用共用的概要。
create or replace outline XXX for catalog OLTP on select ……--------------创建存储概要的名称。
5.5.4 trace:
5.5.4.1 @?/rdbms/admin/utlxplan.sql
explain plan for
@?/rdbms/admin/utlxpls.sql or @?/rdbms/admin/utlxplp.sql or select * from table(dbms_xplan.display);
5.5.4.2 alter session set sql_trace=true;
tkprof
5.5.4.3 exec dbms_session.set_sql_trace=true;
5.5.4.4 exec dbms_system.set_sql_trace_in_session(sid,serial#,true);
5.5.4.5 set autotrace on
5.5.5 表的统计信息包含:NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AVG_SPACE,CHAIN_CNT,ROW_MOVEMENT,AVG_ROW_LEN,LAST_ANALYZED (dba_tables)
5.5.6 索引的统计信息包含:DEGREE,LEAF_BLOCKS,DISTINCT_KEYS,AVG_LEAF_BLOCKS_PER_KEY,AVG_DATA_BLOCKS_PER_KEY,NUM_ROWS,CLUSTERING_FACTOR (dba_indexes)
5.5.7 列统计信息包含:NUM_DISTINCT,LOW_VALUE,HIGH_VALUE(DBA_TAB_COL_STATISTICS)
5.5.8 直方图:EXECUTE DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'HR',TABNAME=>'EMPLOYEES', METHOD_OPT => 'FOR COLUMNS salary SIZE 10');
5.5.9 dbms_stats.gather_system_stats:收集信息
dbms_stats.get_system_stats:验证统计信息
dbms_stats.set_system_stats:明确设置系统统计信息
5.6 块调优
5.6.1 将extent大小设置为5*DB_FILE_MULTIBLOCK_READ_COUNT的倍数。(原因:一个extent=5×DB_FILE_MULTIBLOCK_READ_COUNT),注:对非全表扫描,该优化无效。
5.6.2 HWM:以5 block递增,
5.6.3 减少行迁移:提高pctfree
5.7 索引调优
5.7.1 重建浪费大于20%的索引:
analyze index index_name validate structure;
SELECT name, (DEL_LF_ROWS_LEN/LF_ROWS_LEN) * 100 AS wastage FROM index_stats;
5.7.2 找出未使用的索引
alter index index_name monitoring usage;
alter index index_name nomonitoring usage;
select index_name,used from v$object_usage;
5.7.3 注函数索引必须在CBO下使用,对RBO无效
6 应用调优
6.1 数据访问类型:
6.1.1 聚簇:使用同一个物理地址存放两个或者多个表。先建簇,再建簇表,再向簇表insert数据。分两类:hash 和 index
6.1.2 b-tree索引:叶块填满后,将分成两半,50%在旧叶块,50%到新叶块;分支类推,一直到根
6.1.3 位图索引:lock位图段
6.1.4 反向索引:为了避免降低索引高度。
6.1.5 IOT表:注:IOT表上可使用位图索引,但是需要建立map。
6.1.6 分区表:若range 分区中可能含有null,需要设置maxvalue
若hash,为了数据分布均匀,分区个数需设置为2的n次方。提高单键查找的效率,不提高range scan的效率。
若list,需提前了解有多少种类
若复合,支持range-hash和range-list
6.1.7 分区索引:local index:与表分区一一对应关系;可以是list、hash、range、compsite任一种类;bitmap索引必须是local index
global index:与表分区可以是多对一的关系;只能是range且需设置maxvlaue;表分区变化后需重建。
另外的分法:
prefix index:分区的key为组合索引的最左端字段;可以是唯一索引也可以是非唯一索引
non-prefix index:分区的key不是组合索引的最左端字段;可以是唯一索引也可以是非唯一索引;唯一的non-prefix index条件:分区键是组合索引的子集。
注:non-prefix的global index不存在。
6.1.8 使用物化视图
- 浏览: 414349 次
- 性别:
- 来自: 北京
文章分类
- 全部博客 (239)
- java (59)
- JQuery (5)
- oracle (12)
- javascript (14)
- struts1 (1)
- spring (10)
- Quartz (2)
- ibatis (1)
- linux (32)
- weblogic (3)
- ibaits (2)
- servlet (1)
- log4j (5)
- 编程人生 (2)
- apache (3)
- window (3)
- web前端 (25)
- 错误集合 (2)
- 框架配置 (1)
- 开发工具 (15)
- 小插件应用 (1)
- lucene (1)
- sonar (1)
- velocity (1)
- 代码检查 (0)
- error (1)
- jndi (1)
- oracle函数 (1)
- tools (1)
- 代码管理 (3)
- mysql (4)
- maven (9)
- bat (1)
- git (3)
- aix (2)
- freemarker (1)
- svn (2)
- nginx (6)
最新评论
-
masuweng:
f
修改eclipse的内存 -
洞渊龙王:
...
maven-surefire-plugin学习 -
phf717601206:
WEB-INF/lib下就没有servlet-api.jar ...
Unable to read TLD "META-INF/c.tld"错误 -
sziitjiang:
很好,非常不错
Java读写excel之poi -
dengminghua1016:
doris1bruce 写道一、二、 ...
java创建对象的几种方式
Oracle调优笔记
- 博客分类:
- oracle
发表评论
-
oracle的体系结构
2014-03-28 13:52 833三、oracle 体系结构 1、oracle内存由SG ... -
Oracle并发连接数的参数设置
2013-11-04 18:34 1079提示相关的错误 OERR: ORA-12519 TNS: ... -
oracle创建job
2013-07-29 16:19 980创建 variable job_feei ... -
[官方文档] oracle官方文档总汇(9i,10g,11gR1, 11gR2)
2013-06-06 01:00 850Oracle不同版本的官方文档在线参考和打包下载地址 ... -
oracle总结
2013-05-19 21:27 0一.Oracle数据库中常用的数据类型 varchar2(长度 ... -
oracle 的几个开发工具比较
2013-04-13 23:20 1309oracle 的几个开发工具比较 Or ... -
oracle_去掉重复记录
2012-10-21 11:17 805比如现在有一人员表 (表名:peosons) 若想将姓名、身 ... -
OracleOralDb10g_home1TNSListener无法启动
2012-05-30 16:59 787一、打开 $oracle_home\NETWORK\ADMIN ... -
sql左连接右连接和全连接
2012-03-01 15:46 2577JOIN: 如果表中有至少一个匹配,则返回行 LEFT JO ... -
如何管理oracle的表空间和数据文件
2011-12-23 17:01 1437如何管理oracle的表空间 ... -
oracle中的日期函数
2011-12-11 00:32 855--取当前时间的下一个月时间(格式为:yyyy-MM) ... -
oracle(一)
2011-08-13 16:59 9482.Alter Table --添加字段 alter tab ...
相关推荐
最近几年进行ORACLE性能调优做的笔记,值得参考
Oracle性能调优笔记
本人精心整理的oracle调优笔记,包含很多调优技巧很调优工具
ORACLE DBA工作笔记 运维数据迁移与性能调优 清晰版 。。。
Oracle DBA性能调优学习笔记
Oracle 12c-优化 Hint详解.pdf Oracle 12c-优化 Oracle 索引技术.pdf Oracle 12c 执行计划.pdf Oracle 12c锁.pdf Oracle 12c查询优化器.pdf Oracle 12c 使用绑定变量 VS 不使用绑定变量.pdf Oracle 12c 自动工作负载...
在介绍Oracle系统管理的知识以后,将介绍与开发相关的内容(如PL/SQL基础知识、存储过程、函数、包等),并介绍数据库性能调整,《成功之路:Oracle 11g学习笔记》重点介绍SQL语句调优。SQL语句调优是《成功之路:Oracle...
ORACLE DBA工作笔记 运维数据迁移与性能调优 简单,详细
本人通过对oracle11g的性能优化指南 做了全面的翻译和整理工作,并将每一部分整理成文档,后续会陆续更新,希望对大家有帮助,尤其是不爱看英文原版的朋友特别有帮助。
[三思笔记]ORACLE9I服务器的一次调优实施过程.pdf [三思笔记]ORACLE 10gR2 RAC环境增加及删除节点.pdf [三思笔记]Oracle+RAC数据库配置DataGuard.pdf [三思笔记]oracle著名及非著名函数介绍.doc [三思笔记]RHEL AS4...
成功之路 Oracle 11g 中文学习笔记 专为开发人员编写,先引导开发新手熟悉Oracle环境,然后进入数据库开发,并要掌握项目开发的一些技巧。开发技巧展示是本书一大特色(这一点很值得资深的开发人员借鉴)! 本书共24...
关于Oracle中各个命中率的计算以及相关的调优的笔记整理,结合相关实例
三思笔记\ORACLE9I服务器的一次调优实施过程.pdf
oracle 12C DBA笔记 性能调优,新特性最佳实践,其实实战经验
接下来进行 SQL2 的调优,和优化 SQL1 时一样首先开始查看分析 SQL2 语句的执行 计划,发现 SQL2 的执行计划也是全表扫描,这里 t1.name=的取值为 cc 的返回仅仅 10 条 记录,而 T1 表记录都在 5 千万左右, T2 表在...
ORACLE DBA工作笔记 运维数据迁移与性能调优,数据迁移 ,性能调优
环境部署redhat LINUX环境部署运维redhat 基本操作edhat
一、表空间的操作 二、用户、角色、权限 三、表操作 四、视图操作 五、索引、约束操作 六存储过程、函数、触发器 七、同义词操作八、dblink操作 九、Sequences操作 十、数据库必须掌握的技巧 ...十三oracle性能调优问题
11.1 备份知识点回顾 oracle基础 11.2 rman 备份实验,迁移实验讲解 12.1 容灾管理技术 12.1.1 容灾组网,双活 12.2 双活实验,备份实验,容灾实验思路整理 181208课堂笔记 181209课堂笔记
│ │ 鲁班学院-上课笔记mybaits源码分析9-05.docx │ │ │ └─mybaits源码分析 │ mybaits源码分析.mp4 │ ├─面试必问-springcloud架构微服务项目 │ springcloud架构微服务项目.mp4 │ ├─面试必问-...