Oracle 10g/11g OCP,11g OCM,YEP成员(Oracle Young Expert Program,Oracle用户组年轻专家项目),我不是DBA,但我的爱好是Oracle,微信公众号:bisal的个人杂货铺
有一个应用,需要创建索引,创建索引一般有两种方法,一种是 CREATE INDEX ...; 一种是 CREATE INDEX ... ONLINE; 字面意思上看,一个是在线,一个是非在线,有什么不同? 1.语句执行时间的不同 创建测试表, 使用非在线创建索引,用时00.06秒, 使用在线方式创建索引,用时00.32秒, 表只有一条数据,ONLINE是非ONLINE用时的5倍以上了。 2.阻塞对象的不同 非在线方式创建索引期间,执行任何DML语句,会hang住,直至索引创建完, insert into tbl_index select * from tbl_index where rownum=1; 无响应直至索引创建完成 出现hang是现象,原理则是锁等待。 从V$LOCKED_OBJECT视图可以了解锁等待信息,使用DBA_OBJECTS视图可以知道,OBJECT_ID是18和168111代表的对象是什么, 在线方式创建索引期间,允许任何DML语句的执行,不会阻塞。但实际从V$LOCKED_OBJECT看,是有一些锁等待信息的, 167111知道是TBL_INDEX表,我们看下168114(此处截图问题请忽略,默认168141就是168114),他代表的对象是SYS_JOURNAL_168113,查看168113代表的对象则是我们创建TBL_INDEX表的索引IDX_TBL_INDEX_01, 我们看下SYS_JOURNAL_168113,他是一张表, 表有四个字段, 记录为空(此处截图问题请忽略,默认168112是168114), 表大小为0, SELECT SUM(bytes)/1024/1024 FROM dba_segments WHERE segment_name='SYS_JOURNAL_168113'; 3.执行逻辑的不同 我们对这两种方法执行10046,看下Oracle执行了什么, (1) 非在线方式的trace主要内容, 首先,我们看见了以SHARE NOWAIT模式LOCK了TBL_INDEX整张表, 向obj$、seg$、icol$、ind$这些数据字典中维护索引相关信息, 完成非唯一索引的创建, (2) 在线方式的trace主要内容, 首先,以ROW SHARE模式LOCK表TBL_INDEX,这是和非在线方式一点不同, 另外的不同,就是会创建一张叫”SYS_JOURNAL_92450”的表,索引创建用的是这张“临时表”,因此不会直接影响原表的DML语句, 这张表用完了,会被drop purge,因此回收站找不着痕迹, 删除一些con$、seg$数据字典的记录, 我们从这两种创建索引生成的trace文件大小也可以得出一些结论,online方式创建索引的trace文件大小是非online方式创建索引的trace文件大小的10倍,说明online方式创建索引要执行更多的工作,尽管不会影响原表的DML语句,因此用时要久一些, 总结: (1) online和非online方式创建索引,效果相同。 (2) online方式创建索引,由于使用了一张临时表,以ROW SHARE锁表,不会阻塞原表DML的语句,非online方式创建索引,则会以SHARE NOWAIT锁表,阻塞原表DML语句。 (3) 由于online方式创建索引,Oracle执行工作复杂,因此比非online方式创建索引用时要久。 (4) 一句话“不能什么便宜均占着”,要么选择可以快速创建索引的非online方式但创建期间会锁表阻塞DML语句,要么选择不会阻塞原表DML语句的online方式创建索引但用时较久。从实际来看,我理解,若小表选择任何一种均可,大表,尤其是生产系统,找不着非高峰时间,选择online更合理一些,若不关注是否影响DML操作,则两种方式均可以了。
今天单位值班,有一些时间可以继续完成这篇连载文章。首先祝所有朋友新年快乐!感谢你们在这一年当中对我文章的关注和指点,来年我们共同继续努力! 之前的几篇文章: 《一个执行计划异常变更的案例 - 前传》 《一个执行计划异常变更的案例 - 外传之绑定变量窥探》 《一个执行计划异常变更的案例 - 外传之查看绑定变量值的几种方法》 《一个执行计划异常变更的案例 - 外传之rolling invalidation》 《一个执行计划异常变更的案例 - 外传之聚簇因子(Clustering Factor)》 《一个执行计划异常变更的案例 - 外传之查询执行计划的几种方法》 《一个执行计划异常变更的案例 - 外传之AWR》 《一个执行计划异常变更的案例 - 外传之ASH》 《一个执行计划异常变更的案例 - 外传之SQL AWR》 这篇文章我们聊聊直方图。 首先我们看下统计学中对直方图的定义: 直方图(Histogram)又称质量分布图。是一种统计报告图,由一系列高度不等的纵向条纹或线段表示数据分布的情况。 一般用横轴表示数据类型,纵轴表示分布情况。 可以看出,直方图可以用来描述数据分布的情况。Oracle中也是如此,直方图可以准确预测列数据的分布,尤其在出现数据分布倾斜的情况下,通过直方图信息,可以选择最优的执行计划。 P.S. 关于直方图的介绍,推荐dbsnake的书,其中第五章详细介绍了11g的直方图,非常详细受用。 11g下有两种类型的直方图(12c又多了其他类型的直方图): Height-Balanced Histograms Frequency Histograms 查询USER/DBA_TAB_COL_STATISTICS视图的HISTGRAM列可以知道存储的是何种类型的直方图(取值为HEIGHT BALANCED,FREQUENCY,NONE)。 创建测试表,name列有100000行值为A,1行值为B,数据出现了倾斜,name列存在非唯一二叉树索引,采集统计信息时不收集直方图, 从HISTGRAM列可以看出未有任何直方图统计, 根据name=’A’检索,选择了全表扫描的执行计划, 根据name=’B’检索,同样选择了全表扫描的执行计划, 从数据分布看,A的记录有100000条,B的记录有1条,该列有索引,按说A为条件的SQL应该选择全表扫描采用多块读的方式最高效,B为条件的SQL应该使用索引采用索引扫描的方式最高效,但实际情况是两者均采用了全表扫描的执行计划。 原因就是此时Oracle认为name列值是均匀分布的,根据Cardinality的计算, Computed Cardinality = Original Cardinality * Selectivity Selectivity= 1 / NUM_DISTINCT 计算如下: Computed Cardinality = 100001 * 1 / 2 约等于50001,可以从上面两个执行计划中Rows预估行看出两个SQL的预估行均为50001。 接着我们收集name列的直方图,此处未指定method_opt会由Oracle自行来判断收集的直方图信息和类型, 可以看出name列采集了FREQUENCY类型的直方图信息, 我们再执行刚才的两条SQL,name=’A’的仍选择了全表扫描,我们要重点看下B的SQL,此时选择了索引范围扫描,不是全表扫描了,说明Oracle知道了这列的数据分布,CBO认为索引扫描成本值更低,从10053事件可以查看具体计算值, 但有一处要注意,就是Rows这是18,我们之前知道name=’B’只有1条记录,怀疑这和采用默认的统计信息收集比率有关,默认未必采用了100%的数据作为样本,重新以100%的比例采集统计信息, 可以看出Rows是1了,说明Oracle此时已经知道了数据的分布,CBO计算时知道使用索引扫描成本值更低了。 Oracle直方图使用一种称为Bucket(桶)的方式来描述列的数据分布,每个Bucket就是一组,其中会存储一个或多个列数据,Bucket使用ENDPOINT NUMBER和ENDPOINT VALUE两个维度来描述,其中ENDPOINT VALUE记录列的distinct值,ENDPOINT NUMBER表示到此distinct值为止总计有多少条记录(即这条distinct值对应的ENDPOINT NUMBER减上条记录distinct值对应的ENDPOINT NUMBER就会是这条distinct值的记录数),上面示例中name列是FREQUENCY类型的直方图,对于这种类型的直方图,Bucket的数量就是列distinct值的数量,从NUM_DISTINCT知道有2个distinct值, 因此user_tab_histograms中列name对应的记录(Bucket)应该是2条, 我们看出第一条记录: ENDPOINT VALUE:337499295804764000000000000000000000 ENDPOINT NUMBER:100000 第二条记录: ENDPOINT VALUE:342691592663299000000000000000000000 ENDPOINT NUMBER:100001 上面说ENDPOINT VALUE是distinct值,我们看下如何推导出,以A为例,A对应的十六进制是0x41,将0x41右补至15个字节长度的0,再将其转换为十进制,即3.3750E+35,正如上面对应的第一条记录ENDPOINT VALUE值, 第一条记录的ENDPOINT NUMBER是100000,说明有100000条记录值是A,第二条记录的ENDPOINT NUMBER是100001,说明有(100001-10000=1)条记录值是B。 对于这种FREQUENCY的直方图,dbsnake书中明确说明了其缺点,就是适合于一些distinct值少的情况,因为11g的FREQUENCY直方图对应的Bucket数量不能超过254(12c不受此限制),如果列值distinct值超过254,则不能使用这种类型的直方图。而且若列值类型是文本型,采集直方图时只会采集文本值头32个字节,换句话说,若多个列值distinct的头32个字节相同,则Oracle可能会将他们作为一个值来采集,就会对采集结果产生影响,这是错误。 对于列值distinct超过254的情况,Oracle会采集HEIGHT BALANCED类型的直方图。这种类型的直方图首先会根据列的所有记录按从小到大的顺序排序,用总记录数处于需要使用的Bucket数量,决定每一个Bucket中要存储的记录数,对于相邻Bucket的仅ENDPOINT NUMBER不同,ENDPOINT VALUE值相同记录数做合并存储,ENDPOINT VALUE存储的是到此记录所描述的Bucket为止之前所有Bucket描述的记录中列的最大值,通过实验我们体会下,创建测试数据, name列有301个distinct值,其中值为201有700条记录,采集统计信息时指定Bucket数量是10,此时查看user_tab_col_statistics的HISTOGRAM值变为了HEIGHT BALANCED, select 1000/10 from dual; 知道每一个Bucket应该存储100条记录数, 0号Bucket存储的是列最小值,即1,1-10号Bucket存储的是到此记录所描述的Bucket位置之前所有Bucket描述的记录中列的最大值,每个Bucket存储100条记录数,因此这可以推测出1号Bucket的ENDPOINT VALUE是之前存储的最大值100,ENDPOINT NUMBER是1-0=1,因为每一个distinct这只有一条,值为201的记录有700条,一个Bucket不足以存储,需要7个Bucket,从顺序上看,是2号至9号,由于这几个Bucket的ENDPOINT NUMBER不同,ENDPOINT VALUE值相同,因此做了合并,这种合并后的ENDPOINT VALUE称为popular value,该值记录的ENDPOINT NUMBER和上一记录的ENDPOINT NUMBER差值越大,则意味着这个popular value在表中所占比例也就越大,对应的Cardinality就越大了,进而影响执行计划的成本计算。 此时我们根据name=201执行,选择了全表扫描的执行计划, 根据name=1执行,此时选择了索引扫描的执行计划, 对于这种distinctr超过254的情况,HEIGHT BALANCED用这种方式存储了直方图信息,计算成本时参考,因此选择了正确的执行计划。 总结: 直方图描述了列的数据分布情况,对于列值数据分布倾斜的表,使用直方图可以帮助选择正确的执行计划,11g有两种直方图类型,FREQUENCY和HEIGHT BALANCED,其中FREQUENCY适合于distinct不超过254的表,而且有错误预测的可能。HEIGHT BALANCED采用这种popular value的合并方式来存储直方图信息且对执行计划Cardinality的预测提供参考依据。
为了开发运维工具,我们采用自行搭建的GitLab来管理所有代码。悲催的是最近忘记了管理员账户的密码,而且没有邮件服务器,因此无法接收密码找回的邮件,导致无法新建用户或者项目,这样一来,岂不就成为了一个”永不能扩展”的代码库?难道只能重建? 重建可以解决问题,可是实在有些LOW,好在GitLab留下了可以修改管理员账号密码的方法,一定意义上可以认为就是一个后门。 尝试各种可能的管理员账户密码,无效, 尝试使用忘记密码,由于未配置邮件服务器,所以显示发送了邮件,但实际没有任何的作用。 接下来,尝试使用”后门”。首先登录GitLab后台服务器,并且切换至git用户, 登录GitLab的Rails控制台(GitLab使用RoR语言开发), 接下来使用user = User.where(id: 1).first,定位到id=1的用户,就是超级管理员, 我们可以从回显中可以看出,id=1的用户电子邮箱是admin@example.com,密码加了密,还有关于这个用户的所有属性信息,例如skype、linkedin、twitter、username等。 我们还可以使用User.find_by(email: ‘xxx’)来查找指定邮箱的用户, 接下来为这个用户设置password, 保存设置,可以猜出此时会触发一个邮件发送的JOB,告知所做变更, 再次登录, 确实可以了, 总结: GitLab的任何用户账号信息都可以从后台Rails控制台来查出来,包括所有的用户属性信息,由于其使用的是PG数据库,因此这些信息从相应表中同样可以检索出来,一方面方便了这种密码忘记的找回,另一方面实际是开了后门,任何访问GitLab服务器的用户,都有方法查询到甚至直接修改所有用户的信息,听起来还是很可怕的一件事情,因此便捷性和安全性有时可能就是互相冲突的一对矛盾,此消彼长。
之前的几篇文章: 《一个执行计划异常变更的案例 - 前传》 《一个执行计划异常变更的案例 - 外传之绑定变量窥探》 《一个执行计划异常变更的案例 - 外传之查看绑定变量值的几种方法》 《一个执行计划异常变更的案例 - 外传之rolling invalidation》 《一个执行计划异常变更的案例 - 外传之聚簇因子(Clustering Factor)》 《一个执行计划异常变更的案例 - 外传之查询执行计划的几种方法》 《一个执行计划异常变更的案例 - 外传之AWR》 上篇文章介绍了AWR,他的默认采集周期是一小时,这一小时内对系统负载或性能产生持续性影响的会话、SQL、等待事件等的信息,AWR可以提供一个完整的镜像说明,但有时往往产生资源高消耗的就是一个或某几个会话,对于AWR,除非手工收集AWR,否则会有一小时的延迟,另外,如果我现在就需要查看系统中的负载,或查找性能最差的一条SQL,此时就需要另一种工具的支持,ASH,即Active Session History,顾名思义,他是基于session级别的统计信息收集工具,比AWR粒度更细。 ASH的信息以vsession为基础,每秒采集一次,较新的信息保存在vactive_session_history视图,历史数据保存在dba_hist_active_sess_history视图,只记录活动会话等待的事件,不活动的会话不采样,采样工作由后台进程MMNL完成(AWR信息采集由MMON进程完成)。 11g下默认ASH存储空间是2MB, ASH空间写满后,会由MMNL进程写入AWR负载中,而且也不是所有ASH信息全部写入,一般只写入10%的数据,内存中的信息可以使用vactivesessionhistory查询,已写入AWR的ASH信息可以使用wrh_active_session_history/dba_hist_active_sess_history视图查询,可以说ASH是AWR的子集,但AWR中的信息不仅只有ASH,还会收集其他一些统计信息。 如下一些和ASH相关的视图, 实验: 1.创建ASH报告, 首先选择报告格式,HTML或文本文件。 2.若是RAC,可以选择具体实例的序号, 3.选择采集开始时间,默认是15分钟之前, 选择持续时间,默认是使用SYSDATE-begin_time, 4.提示信息, 5.输入生成的报告名称,默认是“实例序号_MMDD_HH24MM.html”, 6.生成ASH报告, 7.打开ASH报告, 可以看出和AWR报告相比,ASH少了一些系统负载信息,更多还是 TOP SQL、TOP EVENTS这些信息。 总结: 相比AWR默认跨度一小时的间隔,ASH基于v$session提供更多session级别的统计信息,每秒会采集一次,其存储于SGA分配的空间,写满会写入AWR中,虽然少一些AWR中包含的系统负载信息,但对于一些查找当前性能最差的SQL、session负载等的场景,可能比较适合,当然使用一些数据字典视图SQL可以做相同的工作,毕竟这些报告后台就是执行相应的脚本、视图SQL得出的,这方面罗大师、建荣等同仁有类似的经验分享,可以参考。
之前的几篇文章: 《一个执行计划异常变更的案例 - 前传》 《一个执行计划异常变更的案例 - 外传之绑定变量窥探》 《一个执行计划异常变更的案例 - 外传之查看绑定变量值的几种方法》 《一个执行计划异常变更的案例 - 外传之rolling invalidation》 《一个执行计划异常变更的案例 - 外传之聚簇因子(Clustering Factor)》 《一个执行计划异常变更的案例 - 外传之查询执行计划的几种方法》 作为一款成熟的商业软件,Oracle提供了非常丰富的问题诊断方法和工具,AWR就是其中之一。 AWR(Automatic Workload Repository),从Oracle 10g开始引入,之前同质的工具叫Statspack(Oracle 8.1.6引入),两个报告都可以提供一段时间内数据库系统负载、Top等待事件、Top SQL等相关统计信息,辅助故障的排查和处理,AWR会比Statspack提供的信息更加丰富,因此会更加常用一些。 eygle曾经有一篇系列文章介绍了Statspack: 《Statspack专题》(http://www.eygle.com/archives/2004/11/statspack_list.html) 还有一些大师对如何分析AWR报告有比较详细的讲解,例如, 韩锋老师的《循序渐进解读Oracle AWR性能分析报告》(http://dbaplus.cn/news-10-734-1.html) Maclean Liu的《Oracle调优鹰眼,深入理解AWR性能报告》(http://www.askmaclean.com/archives/awr-hawk-eyes-training.html) 都是不错的学习教材。 下面摘录一些当时看尼大师(尼米克)著作AWR这一章节做的笔记,精辟地说明了AWR涉及的一些知识,虽然针对的是10g,但大部分内容11g还是适用。 1、AWR全称是Automatic Workload Repository,内容基于AWR资料库中存储的数据,前提是已经购买了相应许可。 2、AWR默认60分钟采集一次统计数据,保存一周,然后删除。统计数据保存在数据库中。 3、为了正确收集统计数据,STATISTICS_LEVEL设置为TYPICAL(默认)或ALL。 4、AWR由许多表组成,这些表属于SYS模式,通常保存在SYSAUX表空间。所有AWR表名都以标识符“WR”开始:元数据(WRM)、历史/可变数据(WRH、WRR和WRI)和和顾问(advisor)功能相关的AWR表(WRI$)。可以对AWR仓库进行查询的DBA视图,以DBA_HIST开头。 5、可以使用DBMS_WORKLOAD_REPOSITORY程序包修改快照收集间隔时间。 exec dbms_workload_repository.modify_snapshot_settings - (retention=>20160, interval=>15); 使用dbms_workload_repository包的modify_snapshot_settings过程修改快照收集参数,即修改15分钟收集一次,保留时间20160分钟(14天)。 将间隔时间设置为0,则表示停止所有AWR统计数据的收集。 6、查看AWR当前保留时间和时间间隔设置: select * from dba_hist_wr_control; 这里的列TOPNSQL,在《一个执行计划异常变更的案例 - 外传之查看绑定变量值的几种方法》这篇文章中曾介绍过他的含义以及修改方法,可以参考。 7、创建或删除快照: exec dbms_workload_repository.create_snapshot; exec dbms_workload_repository.drop_snapshot_range(low_snap_id=>1107, high_snap_id=>1108); 8、查看所有快照: select snap_id, begin_interval_time, end_interval_time from dba_hist_snapshot order by 1; 9、10g使用名为GATHER_STATS_JOB的调度作业收集AWR统计信息。创建Oracle数据库时,就会自动创建并激活这项作业。查看作业,可参考视图: select a.job_name, a.enabled, c.window_name, c.repeat_interval from dba_scheduler_jobs a, dba_scheduler_wingroup_members b, dba_scheduler_windows c where job_name=’GATHER_STATS_JOB’ and a.schedule_name=b.window_group_name and b.window_name=c.window_name; 回显: GATHER_STATS_JOB TRUE WEEKEND_WINDOW freq=daily;byday=SAT;byhour=0;byminute=0;bysecond=0 GATHER_STATS_JOB TRUE WEEKNIGHT_WINDOW freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=22;byminute=0; bysecond=0 表示有两个窗口执行统计信息收集的作业。WEEKEND_WINDOW是每周六00:00执行。WEEKNIGHT_WINDOW是每周一至周五22:00执行。 10g的统计信息自动收集有一些缺陷,例如只有两个维护窗口,调整不灵活,且并没有对这两个维护窗口施加资源限制,可能会影响正常的业务。 10、11g则优化了统计信息的自动收集策略,引入了七个维护窗口,可以看出每个维护窗口会有资源限制,周一至周五是22:00开始,最长执行4小时,周六日是06:00开始,最长执行20小时, 11、禁用和启动作业的方法: exec dbms_scheduler.disable(‘GATHER_STATS_JOB’); exec dbms_scheduler.enable(‘GATHER_STATS_JOB’); 12、可以使用如下脚本运行AWR快照: $ORACLE_HOME/rdbms/admin/awrrpt.sql或awrrpti.sql。 13、AWR内创建基线,定义为某个范围内的快照,可以用来与其它快照进行比较。 创建基线: exec dbms_workload_repository.create_baseline (start_snap_id=>1109, end_snap_id=>1111, baseline_name=>’EOM Baseline’); 查看基线: select baseline_id, baseline_name, start_snap_id, end_snap_id from dba_hist_baseline; 删除基线: exec dbms_workload_repository.drop_baseline(baseline_name=>’EOM Baseline’, Cascade=>FALSE); 参数Cascade如果设置为true,就会删除所有相关的快照,此处会删除1109和1111这两个相关的快照。否则AWR自动进程会自动清除这些快照。 实验: 1.执行$ORACLE_HOME/rdbms/admin/awrrpt.sql, 选择输出文件类型,可以试HTML或文本文件,HTML展示更清晰,而且有超链接可用。 2.若是单实例此处无需选择,若是RAC,则需要选择创建的具体实例(也有针对所有RAC节点的统一AWR报告生成脚本),还需要选择创建的快照日期,默认是当天, 3.针对(2)日期的所有快照列表,需要选择开始和结束的快照ID, 4.选择输出文件名称,默认是awrrpt_实例序号开始快照ID结束快照ID.html, 5.输出生成的html文件源码, 此时这份AWR报告就创建在当前目录下。 6.可以使用浏览器打开AWR, 7.接下来就可以查看AWR报告内容了, AWR报告中会介绍操作系统的配置信息、系统负载情况、TOP等待事件、CPU/IO/MEMORY的分析数据、TOP SQL(按照执行事件、CPU消耗时间、逻辑读、物理读、执行次数等)、参数设置建议等。 总结: AWR报告的创建其实很简单,只要找出需要分析的时间段,且在快照保存的周期之内,就可以采集出指定时间段的系统负载、TOP等待事件、TOP SQL等指标。难点在于对AWR报告的分析,而且需要综合分析各种指标,才能得到一个问题的真正原因,只是片面地看一个指标,很可能会被假象迷惑,我现在仍在学习的路上,欢迎大家有问题一起探讨。