Skip to content

performance tuning

在12.2之前,如果你想获得实时的数据,那么在利用query rewrite前,你必须得用on commit的刷新方式刷新物化视图。但是on commit的刷新方式有众多限制。所以,以往的方式中需要采用on command的方式来进行刷新(不管是全量刷新还是增量刷新)。

在使用on command刷新的时候,通过设置job来定时的刷新物化视图,一次job运行之后,下一次job到来之前,如果基表有数据变化是不会体现到物化视图的查询结果里的,实时物化视图就是解决这种问题的,实时获取数据而且免去频繁刷新mv。

 

下面的测试仅供参考:

 

SQL> create table t1 (x not null primary key, y not null) as
2 select rownum x, mod(rownum, 10) y from dual connect by level <= 1000000;

Table created.

SQL> create materialized view log on t1 with rowid (x, y) including new values;

Materialized view log created.

SQL>
SQL> create materialized view mv_old
2 refresh fast on demand
3 enable on query computation
4 enable query rewrite
5 as
6 select y , count(*) c1
7 from t1
8 group by y;

Materialized view created.

create mv时的关键字:enable on query computation

SQL> create table t2 (x not null primary key, y not null) as
2 select rownum x, mod(rownum, 10) y from dual connect by level <= 1000000;

Table created.

SQL> create materialized view log on t2 with rowid (x, y) including new values;

Materialized view log created.

SQL>
SQL> create materialized view mv_new
2 refresh fast on demand
3 enable on query computation
4 enable query rewrite
5 as
6 select y , count(*) c1
7 from t2
8 group by y;

Materialized view created.

SQL> show parameter rewrite

NAME TYPE VALUE
———————————— ———– ——————————
query_rewrite_enabled string TRUE
query_rewrite_integrity string enforced

SQL> set autotrace on explain stat

SQL> select mview_name,staleness,on_query_computation from user_mviews;

MVIEW_NAME STALENESS O
—————————————- ——————- –
MV_OLD FRESH N
MV_NEW FRESH Y

SQL>
SQL> select count(*) from MLOG$_T1;

COUNT(*)
———-
0

SQL> select count(*) from MLOG$_T2;

COUNT(*)
———-
0

SQL>

SQL> insert into t1
2 select 1000000+rownum, mod(rownum, 3) from dual connect by level <= 999;

999 rows created.

SQL>
SQL> insert into t2
2 select 1000000+rownum, mod(rownum, 3) from dual connect by level <= 999;

999 rows created.

SQL> commit;

Commit complete.

SQL>
SQL> select mview_name,staleness,on_query_computation from user_mviews;

MVIEW_NAME STALENESS O
—————————————- ——————- –
MV_OLD NEEDS_COMPILE N
MV_NEW NEEDS_COMPILE Y

SQL>
SQL> select count(*) from MLOG$_T1;

COUNT(*)
———-
999

SQL> select count(*) from MLOG$_T2;

COUNT(*)
———-
999

SQL>

由于数据stale,且没有set query_rewrite_integrity=stale_tolerated,传统mv没有进行query write。
SQL> select y as y_new_parse1, count(*) from t1
2 group by y;

Y_NEW_PARSE1 COUNT(*)
———— ———-
1 100333
6 100000
2 100333
4 100000
5 100000
8 100000
3 100000
7 100000
9 100000
0 100333

10 rows selected.

Execution Plan
———————————————————-
Plan hash value: 136660032

—————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————
| 0 | SELECT STATEMENT | | 10 | 30 | 515 (4)| 00:00:01 |
| 1 | HASH GROUP BY | | 10 | 30 | 515 (4)| 00:00:01 |
| 2 | TABLE ACCESS FULL| T1 | 1000K| 2929K| 498 (1)| 00:00:01 |
—————————————————————————

Statistics
———————————————————-
1975 recursive calls
30 db block gets
4167 consistent gets
1786 physical reads
5440 redo size
754 bytes sent via SQL*Net to client
608 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
131 sorts (memory)
0 sorts (disk)
10 rows processed

SQL>

real time进行了query rewrite,且查到的数据是最新实时数据

SQL> select y as y_new_parse1, count(*) from t2
2 group by y;

Y_NEW_PARSE1 COUNT(*)
———— ———-
6 100000
4 100000
5 100000
8 100000
3 100000
7 100000
9 100000
1 100333
2 100333
0 100333

10 rows selected.

Execution Plan
———————————————————-
Plan hash value: 542978159

——————————————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————————————
| 0 | SELECT STATEMENT | | 12 | 312 | 22 (14)| 00:00:01 |
| 1 | VIEW | | 12 | 312 | 22 (14)| 00:00:01 |
| 2 | UNION-ALL | | | | | |
|* 3 | VIEW | VW_FOJ_0 | 10 | 290 | 9 (12)| 00:00:01 |
|* 4 | HASH JOIN FULL OUTER | | 10 | 240 | 9 (12)| 00:00:01 |
| 5 | VIEW | | 1 | 7 | 6 (17)| 00:00:01 |
| 6 | HASH GROUP BY | | 1 | 22 | 6 (17)| 00:00:01 |
|* 7 | TABLE ACCESS FULL | MLOG$_T2 | 999 | 21978 | 5 (0)| 00:00:01 |
| 8 | VIEW | | 10 | 170 | 3 (0)| 00:00:01 |
| 9 | MAT_VIEW ACCESS FULL | MV_NEW | 10 | 60 | 3 (0)| 00:00:01 |
| 10 | VIEW | | 2 | 52 | 13 (16)| 00:00:01 |
| 11 | UNION-ALL | | | | | |
|* 12 | FILTER | | | | | |
| 13 | NESTED LOOPS OUTER | | 1 | 32 | 6 (17)| 00:00:01 |
| 14 | VIEW | | 1 | 26 | 6 (17)| 00:00:01 |
|* 15 | FILTER | | | | | |
| 16 | HASH GROUP BY | | 1 | 22 | 6 (17)| 00:00:01 |
|* 17 | TABLE ACCESS FULL | MLOG$_T2 | 999 | 21978 | 5 (0)| 00:00:01 |
|* 18 | INDEX UNIQUE SCAN | I_SNAP$_MV_NEW | 1 | 6 | 0 (0)| 00:00:01 |
| 19 | NESTED LOOPS | | 1 | 35 | 7 (15)| 00:00:01 |
| 20 | VIEW | | 1 | 29 | 6 (17)| 00:00:01 |
| 21 | HASH GROUP BY | | 1 | 22 | 6 (17)| 00:00:01 |
|* 22 | TABLE ACCESS FULL | MLOG$_T2 | 999 | 21978 | 5 (0)| 00:00:01 |
|* 23 | MAT_VIEW ACCESS BY INDEX ROWID| MV_NEW | 1 | 6 | 1 (0)| 00:00:01 |
|* 24 | INDEX UNIQUE SCAN | I_SNAP$_MV_NEW | 1 | | 0 (0)| 00:00:01 |
——————————————————————————————————

Predicate Information (identified by operation id):
—————————————————

3 – filter(“AV$0″.”OJ_MARK” IS NULL)
4 – access(SYS_OP_MAP_NONNULL(“SNA$0″.”Y”)=SYS_OP_MAP_NONNULL(“AV$0”.”GB0″))
7 – filter(“MAS$”.”SNAPTIME$$”>TO_DATE(‘ 2017-07-12 14:35:01’, ‘syyyy-mm-dd hh24:mi:ss’))
12 – filter(CASE WHEN ROWID IS NOT NULL THEN 1 ELSE NULL END IS NULL)
15 – filter(SUM(1)>0)
17 – filter(“MAS$”.”SNAPTIME$$”>TO_DATE(‘ 2017-07-12 14:35:01’, ‘syyyy-mm-dd hh24:mi:ss’))
18 – access(“MV_NEW”.”SYS_NC00003$”(+)=SYS_OP_MAP_NONNULL(“AV$0”.”GB0″))
22 – filter(“MAS$”.”SNAPTIME$$”>TO_DATE(‘ 2017-07-12 14:35:01’, ‘syyyy-mm-dd hh24:mi:ss’))
23 – filter(“MV_NEW”.”C1″+”AV$0″.”D0″>0)
24 – access(SYS_OP_MAP_NONNULL(“Y”)=SYS_OP_MAP_NONNULL(“AV$0”.”GB0″))

Note
—–
– dynamic statistics used: dynamic sampling (level=2)
– this is an adaptive plan

Statistics
———————————————————-
906 recursive calls
64 db block gets
1232 consistent gets
14 physical reads
10548 redo size
744 bytes sent via SQL*Net to client
608 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
64 sorts (memory)
0 sorts (disk)
10 rows processed

对比结果还是比较清楚的。

Real time mv利用原来的已经stale的物化视图,结合mv log,通过计算后,帮你获取实时的数据。即能获得实时数据,又避免频繁的刷新mv。

12c以后 Real-time materialized view 实时物化视图的应用

性能优化的一点体会

1、优化是基于目标的,我们的最终目的是达到一个目标,而不是做优化。目标的合理性决定了优化项目的成败。刚刚开始给用户优化的时候,我会把所有能够调整的东西一次性全部调整完毕。哪怕有些调整给系统性能带来的好处不到0.1%。对于生产系统,不确定因素十分多,而很多参数方面的调整本身就是双刃剑,如果你无法预期其调整的影响,那么这种调整是存在风险的,在实施的时候就应该慎重考虑。现在我做优化的项目,往往会根据用户的优化目标,然后在此基础上进行分析,制定方案,实施的结果虽然一般会超出客户的期望,但是我不会在生产系统上做一些没把握的事情。锦上添花的事情,有时候也要考虑考虑,是否值得,因为弄不好,锦上添花会变成画蛇添足。
2、1+1不一定大于1,在优化过程中,抓住主要矛盾,解决主要问题,而不要胡子眉毛一把抓。很多调整之间有关联性,甚至是互斥的,不合理的调整可能带来更坏的结果。
3、客户需要的是系统的优化,而不仅仅是DB的优化。客户的目标里,看到的是一个系统,而不是一个孤立的DB。在10年前,我可能会说,OS的问题,你们还是找一下厂家。而现在,我会对客户说,你放心,我们做的是系统优化。
4、用适当的方式和应用开发厂商配合。很多优化项目由于无法和应用厂商有效的配合,其效果大打折扣。因为应用是和系统性能关系最为紧密的。如果应用开发厂商不能很好的配合,那么优化项目将举步维艰。如果你和开发厂商说,“你这个SQL开销太大,需要修改一下”,那么得到可能就是强烈的反对。如果你说“这个SQL开销太大,我给你们提供了几个方案,第一是,。。。”,这样你很可能会得到比较好的结果。你是优化专家,找出几个TOP SQL这样的工作,不需要专家来完成,而专家的职责,不仅仅是发现TOP SQL,而是如何解决掉TOP SQL。
5、不要相信什么优化规则,实际上并没有条条框框限制你,实现目标的任何方法你都可以使用。对于一个初级DBA来说,可能老DBA会告诉你,什么是对的,什么是不对的。而对错都是相对的,如果你已经深刻的理解了Oracle以及系统优化,那么就像金庸小说里的内功高手一样,无招胜有招。

对于老白的几个观点,深深的赞同!

SPM固定执行计划以及踩bug事一件

原有2个sql语句有多个表连接,执行计划一直在走错误的执行计划.表级统计信息以及索引规划都已经是最新(这里统计信息有狗血不做描述),只是SQL里还有六个绑定变量以及字段的柱状图影响了执行计划,在这个优化里没有删除柱状图和对绑定变量的影响进行处理(星形连接不建议使用绑定变量),现场环境微妙最终选择通过sql profile以及spm对这2个sql的执行计划进行固定处理.先用sqlprofile固定后让sql重新解析后发现未能生效,逐用spm的方式固定.

这里以其中一个sql_id为bwwnw7r1gzhdf的语句为例,这是收集到对应1个小时内的sqlrpt,其中plan_hash_value为711942702执行计划为正确的执行计划,从报告中可以看到这个sql选择了错误的执行计划,并且从中也可以看到sql有多个执行计划.当中执行计划正确与否的判断方式就不做描述.
 

SQL ID: bwwnw7r1gzhdf

# Plan Hash Value Total Elapsed Time(ms) Executions 1st Capture Snap ID Last Capture Snap ID
1 3052678239 13,512,877 10 25060 25060
2 3392573872 0 0 25060 25060
3 4134955434 0 0 25060 25060
4 1564064893 0 0 25060 25060
5 2504448979 0 0 25060 25060
6 147966509 0 0 25060 25060
7 711942702 0 0 25060 25060

 
通过coe_xfr_sql_profile.sql脚本对bwwnw7r1gzhdf的sql进行固定711942702,生成sql profile的名字为coe_bwwnw7r1gzhdf_711942702.
(该部分可以参考
1.Using Sqltxplain to create a ‘SQL Profile’ to consistently reproduce a good plan (文档 ID 1487302.1)
2.Automatic SQL Tuning and SQL Profiles (文档 ID 271196.1)
3.Correcting Optimizer Cost Estimates to Encourage Good Execution Plans Using the COE XFR SQL Profile Script (文档 ID 1955195.1))

让sql从新解析后从v$sql视图中的sql profile字段没有看到生效的迹象,原因是在脚本coe_xfr_sql_profile.sql中对创建的sqlprofile默认的生效是false的,所以创建出来的profile不会失效,监控中的执行计划未变(现场我对此处的profile drop).

 

SQL>  select name,created,status from dba_sql_profiles;

NAME                           CREATED                        STATUS
------------------------------ ------------------------------ --------
coe_bwwnw7r1gzhdf_711942702    26-JUN-15 02.09.30.000000 PM   ENABLED
coe_g87an0j5djjpm_334801256    26-JUN-15 11.30.25.000000 AM   ENABLED

SQL>  select SQL_ID, SQL_PROFILE,PLAN_HASH_VALUE from V$SQL where SQL_ID='bwwnw7r1gzhdf' and sql_profile is not null;

no rows

SQL>  select sql_profile,EXECUTIONS,PLAN_HASH_VALUE,parse_calls,ELAPSED_TIME/1000000,
ELAPSED_TIME/1000000/EXECUTIONS,LAST_LOAD_TIME,ROWS_PROCESSED
from v$sql where EXECUTIONS>0 and sql_id='bwwnw7r1gzhdf' order by LAST_LOAD_TIME desc;
...

逐对profile进行disable并drop

=====disable profile==============
BEGIN
DBMS_SQLTUNE.ALTER_SQL_PROFILE(
name =&gt; 'coe_bwwnw7r1gzhdf_711942702',
attribute_name =&gt; 'STATUS',
value =&gt; 'DISABLED');
END;
/

BEGIN
DBMS_SQLTUNE.ALTER_SQL_PROFILE(
name =&gt; 'coe_g87an0j5djjpm_334801256',
attribute_name =&gt; 'STATUS',
value =&gt; 'ENABLED');
END;
/

=====drop profile=================
begin
DBMS_SQLTUNE.DROP_SQL_PROFILE(name => 'coe_bwwnw7r1gzhdf_711942702');
end;
/

begin
DBMS_SQLTUNE.DROP_SQL_PROFILE(name => 'coe_g87an0j5djjpm_334801256');
end;
/

由于已经存在了正确的执行计划,所以通过DBMS_SPM直接创建baseline,并通过DBMS_SPM包对该sql的baseline的enable,accept,fixed三个属性指定为yes.

该部分可以参考:
Plan Stability Features (Including SQL Plan Management (SPM)) (文档 ID 1359841.1)

为sql创建baseline

variable cnt number;
execute :cnt :=DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(SQL_ID => 'bwwnw7r1gzhdf', PLAN_HASH_VALUE => 711942702) ;

验证该baseline已经生成

SQL> set linesize 200
SQL> Select Sql_Handle, Plan_Name, Origin, Enabled, Accepted,Fixed,Optimizer_Cost,Sql_Text
From Dba_Sql_Plan_Baselines
Where Sql_Text Like '%FROM P1EDBADM.MES_PROCESSOPERATIONSPEC%' Order By Last_Modified;


SQL_HANDLE                     PLAN_NAME                      ORIGIN         ENA ACC FIX OPTIMIZER_COST SQL_TEXT
------------------------------ ------------------------------ -------------- --- --- --- -------------- --------------------------------------------------------------------------------
SQL_995463d3d1edd710           SQL_PLAN_9kp33ug8yvpsh4af503b5 MANUAL-LOAD    YES YES NO              69 SELECT D.LOTNAME LOT, D.PRODUCTNAME GLASS, TO_CHAR(D.CREATETIME, 'YYYY-MM-DD HH2

为sqlbaseline的fixed属性改为yes

variable cnt number;
execute :cnt :=DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(SQL_ID => 'bwwnw7r1gzhdf', PLAN_HASH_VALUE => 711942702,fixed => 'yes') ;
验证修改完成
SQL> set linesize 200
SQL> Select Sql_Handle, Plan_Name, Origin, Enabled, Accepted,Fixed,Optimizer_Cost,Sql_Text
  2  From Dba_Sql_Plan_Baselines
  3  Where Sql_Text Like '%FROM P1EDBADM.MES_PROCESSOPERATIONSPEC%' Order By Last_Modified;

SQL_HANDLE                     PLAN_NAME                      ORIGIN         ENA ACC FIX OPTIMIZER_COST SQL_TEXT
------------------------------ ------------------------------ -------------- --- --- --- -------------- --------------------------------------------------------------------------------
SQL_995463d3d1edd710           SQL_PLAN_9kp33ug8yvpsh4af503b5 MANUAL-LOAD    YES YES YES            574 SELECT D.LOTNAME LOT, D.PRODUCTNAME GLASS, TO_CHAR(D.CREATETIME, 'YYYY-MM-DD HH2

最终验证生效

SQL> Select Sql_Handle, Plan_Name, Origin, Enabled, Accepted,Fixed,Optimizer_Cost,Sql_Text
  2  From Dba_Sql_Plan_Baselines
  3  Where Sql_Text Like '%FROM P1EDBADM.MES_PROCESSOPERATIONSPEC%' Order By Last_Modified;

SQL_HANDLE                     PLAN_NAME                      ORIGIN         ENA ACC FIX OPTIMIZER_COST SQL_TEXT
------------------------------ ------------------------------ -------------- --- --- --- -------------- --------------------------------------------------------------------------------
SQL_995463d3d1edd710           SQL_PLAN_9kp33ug8yvpsh4af503b5 MANUAL-LOAD    YES YES YES            574 SELECT D.LOTNAME LOT, D.PRODUCTNAME GLASS, TO_CHAR(D.CREATETIME, 'YYYY-MM-DD HH2
SQL_2e1c8025edb165b3           SQL_PLAN_2w7404rqv2tdm56eb6fa8 MANUAL-LOAD    YES YES YES            311 SELECT 1 " ", D.LOTNAME LOT, D.PRODUCTNAME GLASS, TO_CHAR(MAX(H.EVENTTIME), 'YYY

SPM主要和2个参数有关,一个是baseline生效(optimizer_user_sql_plan_baselines,前提是accept属性要为yes,否则会产生干扰),一个是捕获sql语句生成baseline(optimizer_cature_sql_plan_baselines).在数据库中我一般不开启捕获,但是开启baseline生效.
开启的语法:

alter system set optimizer_user_sql_plan_baselines=true scope=both;
alter system set optimizer_cature_sql_plan_baselines=true scope=both;

关闭的语法:

alter system set optimizer_user_sql_plan_baselines=false scope=both;
alter system set optimizer_cature_sql_plan_baselines=false scope=both;

开启捕获的情况在一些11g版本中会触发该bug
Bug 9910484 – SQL Plan Management Capture uses excessive space in SYSAUX (文档 ID 9910484.8)
此bug会造成sysaux的表空间暴增,主要为sqllob$data,我遇见的是在一天内从2g增长到4g.关闭了捕获后,该现象消失.
删除掉不必要的baseline后可以通过shrink的方式回收sysaux的空间,具体可以参考
Reducing the Space Usage of the SQL Management Base in the SYSAUX Tablespace (文档 ID 1499542.1)

Cardinality Feedback与_optimizer_use_feedback的使用建议

该参数与Cardinality Feedback特性有关,优化器可以估算基数不正确的原因有很多,如缺少的统计信息,不准确的统计数据,或复杂的谓词,基数统计反馈有助于优化器生成更合理的执行计划.对于此特性我不作科普了,比较详细的资料可以参考以下文档:

1.Tuning-by-Cardinality-Feedback.pdf
2.Statistics (Cardinality) Feedback – Frequently Asked Questions (文档 ID 1344937.1)

该特性其实从10g版本就已经开始引入,只是它的应用受限于optimizer_dynamic_sampling参数,在11g以前的10g版本中,只有在optimizer_dynamic_sampling=4以上的情况下才会被用到该特性.这个在10g版本中可以通过HINT语法
/*+ dynamic_sampling (customer 4) */或者在session/system中将optimizer_dynamic_sampling设置为4进行测试.

判断SQL是否使用了CF可以通过sql的执行计划中的note提示或者视图V$SQL_SHARED_CURSOR的USE_FEEDBACK_STATS字段进行.

执行计划中的note提示

Predicate Information (identified by operation id):
---------------------------------------------------

 6 - filter(ROWNUM>0)
 8 - access("DATA"."OBJECT_NAME"="T"."COLUMN_VALUE")

Note
-----
- Cardinality Feedback used for this statement

V$SQL_SHARED_CURSOR


column use_feedback_stats format a18
column sql_text format a80

select c.child_number, c.use_feedback_stats , s.sql_text from v$sql_shared_cursor c,v$sql s
where s.sql_id=c.sql_id and c.sql_id = 'an4zdfz0h7513'
and s.child_number= c.child_number;

CHILD_NUMBER USE_FEEDBACK_STATS SQL_TEXT
------------ ------------------ ------------------------------------------------------------
           0 Y                   select * from TABLE(cast( str_func('A,B,C' ) as s_type) ) t
           1 N                   select * from TABLE(cast( str_func('A,B,C' ) as s_type) ) t

对使用了基数统计回馈特性的sql,可以在10053的trace文件中发现sql被转化成带了hint opt_estimate的语法.
CF特性的功能是非常不错的,oracle在使用了cf反馈评估后更准确的统计数据后会根据此数据生成对于的plan,在再次的执行中还会持续评估统计信息是否准确,以此重复解析,参考如下:

When a cursor is found to be a candidate for Statistics Feedback it will be hard parsed again using the new estimates. The child cursor will be marked as not being shareable and USE_FEEDBACK_STATS set to ‘Y’ in V$SQL_SHARED_CURSOR.
Note: As the need for Statistics Feedback was only detected while execution of this cursor, Statistics Feedback will not actually be used for this child. However it will be used for all further child cursors created.

At the next execution, as a result of the cursor being marked as not shareable, a hard parse will again be performed and a new child created with the optimizer having used the new estimates for creating an optimizer plan.

但是由于cf的评估结果数据只存在内存中(重启之后就需要重新来过),在session之间是不可共用的,并且由于在11g中存在了过多的bug,常见的问题就是在第二次执行sql时候性能下降较多.因此我在11g的数据库中往往会对11.2.0.4以下的数据库会将该特性关闭.

部分bug list

feedback bug

关闭CF特性的方法即是将_optimizer_use_feedback设置为false.

alter session set "_optimizer_use_feedback" = false;
或者
alter system set "_optimizer_use_feedback" = false;