rwp,全称为real-world performance,rwp团队是oracle总部数据库研发的一个部门,在业界内,rwp团队是公认的sql优化顶级团队。这一次有幸参加了rwp团队的成都行的培训,对oracle的优化相关知识进行现场学习,收获也是非常大的,这里我主要想mark一下我这一次参加培训后,映像比较深的收获有哪些(接受和转达能力有限,所以总结的东西可能比较散,但是个人感觉对后续的工作中非常有用)。
1. cpu utilization:
我们通常在主机中会用到top来观察一个主机的cpu使用情况,而cpu utilization,即使用率,则是从cpu等待的角度进行描述的,比如cpu的使用率达到80%,此时则认为只有20%的进程可以获取cpu进行运算,而80%的进程则会等待cpu,这个时候其实系统的压力是非常大的,大部分的进程都是在等待cpu。而最佳的cpu使用率上限是在cpu的60%-65%左右;
2.cpu的数量和数据库processes(session)的数量:
我们将cpu以core来计算,当数据库处理进程为1 process/core的时候,性能会线性增长;当10 process/core的时候,性能会出现近线形的曲线;当50 processes/core的时候,性能则几乎不会增长,此时cpu的处理能力会收到限制。因此,当10 processes/core的cpu时候(或者session与cpu core的比值为10:1时,此时可认为数据库cpu的处理能力最优);
3.oracle默认并行度计算:
我们知道当在sql语句中写了parallel,但是没有制定具体的并行度,那么我们的sql语句会默认开启最大并行,该并行度的计算方式是:
parallel_count=cpu_count * parallel_threads_per_cpu * instance_number
但是cpu_count表示是cpu的线程数,即cpu颗数 x cpu核数 x cpu线程数,在linux中,我们可以通过lscpu进行cpu的信息查看;
4.扩展统计信息的使用:
当我们通过sqlmonitor、ash等方式查看sql的执行计划汇总数据行数预估值和实际值相差较大,执行计划即使走正确也无法得到一个好的效果,那么我们此时可以观察在谓词条件涉及的列是否具有关联性,比如国家和地区,年和月,内部账户和机构号等。此时,我们则可以收集相关列的扩展统计信息,收集方法是:
select dbms_stats.create_extended_stats(user,'table_name','(col1,col2)') from dual;
收集扩展列统计信息,的实际效果就是对相关列联合创建了一个虚拟列,新的统计信息会使oracle的cbo使用该虚拟列,但是需要特别注意的是,我们在收集了相关列的扩展统计信息后,该虚拟列并不能被正常使用,只有再次执行统计信息收集进行直方图收集才能正常使用:
exec dbms_stats.gather_table_schema(ownname=>'&table_owner',tabname=>'&table_name',degree=>16);
这是由于在oracle中,拥有直方图统计信息的列会优先被使用。
我们可以通过以下方式自动捕获数据库中列相关性信息,辅助我们创建扩展统计信息:
select dbms_stats.seed_col_usage(null,null,120) from dual;
select dbms_stats.report_col_usage(user,'table_name') from dual;
select dbms_stats.create_extended_stats(user,null) from dual;
5.并行统计信息收集
这里说的并行统计信息收集不是gather_table_schema中的degree,而是在数据库全局层面设置并行:
开启:
exec dbms_stats.set_global_prefs('concurrent','true');
关闭:
exec dbms_stats.set_global_prefs('concurrent','false');
需要注意的是,这个设置是一个全局设置,开启后会对整个数据库的任务并行度产生影响,因此,在开启使用完成后,一定要记得对该参数进行关闭,且该参数的并行度与job_queue_processes相关,即当开启concurrent后,数据库会根据该参数的值发起响应的并行,同时收集多个对象的统计信息。此外,当开启并行后,如果进行统计信息收集,需要使用resource manager功能,如果我们将resource manager功能禁用后,则无法执行gather_stats。该操作由于是全局的并行度设置,所以在生产上一般不建议随意设置,一般是在数据库迁移后,资源较为空闲的时候使用。
6.为什么在做hash join的时候会使用大量的hash表空间
hash join的本质是数据库将表通过hash的方式创建一个新的hash表,然后通过条件进行筛选;而构建的hash表则会被放到内存(pga)中,从而更快的进行匹配筛选,但是当hash表过大的时候,内存无法存储下,则会使用临时表空间进行存储.因此,我们可以通过调整pga的参数扩大pga的使用内存,从而减小temp表空间的使用率:
show parameter sort_area_size
show parameter hash_area_size
单位为bytes
7.cursor_sharing设置为true时,可能会造成的问题
在一些数据库中,由于应用程序没有使用绑定变量,会使数据库的硬解析极高。此时,很多数据库的cursor_sharing会被调整为true。这样的调整,会让我们的硬解析率下降,但是会造成每个sql语句都被解析成绑定变量模式,从而被存储在share pool中,此时share pool会存储大量的垃圾,不仅影响效率,也容易出发ora-04031的错误。
另外一点,从安全性来讲,也可能会造成sql注入的风险。
8.中间件、应用连接池的配置
为了适应前端用户的动态变化(比如,秒杀活动、抢购活动、双十一等),通常在应用及中间件上设置一个动态的连接池,min pool和max pool(可能会有defalut pool)。
需要注意的是,当应用的连接池数量突然增加后,会在短时间内造成连接风暴,大量的消耗数据库的cpu资源,此时数据库可能会出现卡顿。
因此,我们为了防止连接风暴的出现,我们通常还是将应用、中间件的连接池进行固定,即最小连接数和最大连接数(默认连接数)的值设置成一样,且这个连接池设置的数量一般可以设置为数据库服务器cpu的core的3-10倍最佳。
9.open_cursor的数量及如何协助开发人员查看是否有cursor泄漏
数据库open_cursor表示的是一个会话可以同时打开的cursor数,这个参数的默认值是300,我们通常在初始化参数时,可能会将该参数调整的比较大,避免ora-1000的错误,但是根据rwp老师描述,一个资深的oracle研发工程师,可以非常好的在一个session里面控制最多10个cursor。因此,如果我们在未调整默认值或者调整默认值后仍然出现了ora-1000的错误,则基本可以确认在应用程序端出现了cursor的泄漏。
那么我们应该怎么去帮助开发人员去确认cursor泄漏的代码呢?此时则可以根据v$open_cursor的视图进行查看,执行以下sql语句(em中也可以看到):
select o.sid,osuser,machine,o.sql_id,o.sql_text,o.cursor_type,count(*) num_curs from v$open_cursor o,v$session s where user_name='&username' and o.sid=s.sid group by o.sid,osuser,machine,o.sql_id,o.sql_text,o.cursor_type order by num_curs desc;
或者
select * from (select sql_id,sql_text,count(*) c from v$open_cursor t group by t.sql_id,t.sql_text order by 3 desc) where rownum < 50;
这个sql中,我们需要重点关注的num_curs,如果num_curs出现大于1的情况,那么我们可以查看对应的sql_id和sql文本。这代表此时cursor是重复打开的,则很有可能出现泄漏的情况,如果该值比较大,则可以反馈开发对当前的sql代码进行定位检查,是否存在泄漏情况。
10.oracle 12c以后的自适应执行计划
12c以后的oracle新特性有一个自适应执行计划,该功能通过optimizer_adaptive_plan参数进行控制:
alter session optimizer_adaptive_plan=true/false;
开启后,我们通过sqlmon、display_cursor的方式可以看到有两种执行计划,自适应执行计划可以修复错误的执行计划;
11.bitmap索引为什么不支持交易系统
这是因为bitmap索引在进行update等语句的时候,会锁定segment,这是由于bitmap的索引结构导致,bitmap索引保存的结构都是位图,比如000001000101010000,存在的数据将bitmap的位置为1,在满足条件的时候,做位运算即可得到结果,因此效率极高,但是会对整个对象上加锁。
上述的11点,是本次rwp之旅中影响最为深刻的11点,也是对本次培训中的一个简单的总结。虽然,oracle数据库在国内的市场份额减少了不少,但是一些数据库的内容和原理也是有相通的地方,所以rwp的培训,如果有机会,还是建议大家可以听一听。