首页 > 代码库 > SPA 最佳实践

SPA 最佳实践

SPA 最佳实践

SPA官方描绘的蛮完美的,但在实践中,还是有很多的坑。下面会一一展示本人在SPA实践项目中遇到的各种坑,以及解决方案。

OPTIMIZER_MODE问题

SQL在目标库实际运行时需要跟源库保持一致,以SIEBEL库为例,它都会在SESSION级别设置以下影响CBO的参数。

alter session set optimizer_mode=FIRST_ROWS_10;
alter session set "_hash_join_enabled"=FALSE;
alter session set "_optimizer_sortmerge_join_enabled"=FALSE;
alter session set "_optimizer_join_sel_sanity_check"=TRUE;

官方提供的方法如下:

exec dbms_sqlpa.set_analysis_task_parameter(‘SPA_TASK_NAME‘,‘APPLY_CAPTURED_COMPILENV‘, 1);

实际测试中,发现这种方法对于用DBLINK连过去执行的方式无效果呀(源库目标库版本均为 10.2.0.5 SPA测试库版本 11.2.0.3 )。所以只能在取STS时把在SESSION级别改了OPTIMER的用户的SQL单独取一个STS,然后在测试库系统级别改参数后运行。对于要用DBLINK的方案目前没太好的解决办法。

SQL实际运行时间长,不可控

  • SPA 实际执行SQL时是串行执行,要做并行参考这位哥哥的博客(http://hongyedba.cn/2014/05/20/thought_about_parallel_spa/),贴几张自己画的流程图。
  • 设置参数限定每个SQL执行的时间如设定每个SQL最长执行1分钟dbms_advisor.arglist(‘LOCAL_TIME_LIMIT‘,‘60‘)



SPA PLAN_CHANGE项不准

SPA分析报告中会列出执行计划改变的SQL以供后继调优使用,但SPA判断PLAN 是否是同一个时,标准就是PLAN_HASH_VALUE。然而PHV是会受大小端影响的,也就是同样的一个PLAN在大端算出来的PHV跟小端算出来的是不一样。(文章可参考:http://carlos-sierra.net/tag/plan-hash-value/)更坑的是,这个标准是不能改的。但我们就是想知道在实际执行中计划发生改变的那些SQL,怎么办呢。SPA 中ORACLE是没有提供好的办法,只能自己动手通过dba_advisor_sqlplansdba_sqlset_plans 这两张表对比来实现了。

解决方案:

create or replace function plan_change(p_sql_id IN VARCHAR2,
p_task_name  IN VARCHAR2,
p_execution_name IN VARCHAR2,
p_sqlset_nameIN VARCHAR2)
  return VARCHAR2 is
  l_plan_changed number := 0;
begin

  select count(1)
into l_plan_changed
from (select id, operation, options, object_owner, object_name
from dba_advisor_sqlplans
   where sql_id = p_sql_id
 and task_name = p_task_name
 and execution_name = p_execution_name
  minus
  select id, operation, options, object_owner, object_name
from dba_sqlset_plans
   where sql_id = p_sql_id
 and sqlset_name = p_sqlset_name) diff;

  if l_plan_changed <> 0 then
return ‘Y‘;
  end if;

  select count(1)
into l_plan_changed
from (select id, operation, options, object_owner, object_name
from dba_sqlset_plans
   where sql_id = p_sql_id
 and sqlset_name = p_sqlset_name
  minus
  select id, operation, options, object_owner, object_name
from dba_advisor_sqlplans
   where sql_id = p_sql_id
 and task_name = p_task_name
 and execution_name = p_execution_name) diff;

  if l_plan_changed <> 0 then
return ‘Y‘;
  else
return ‘N‘;
  end if;

end plan_change;

在不考虑业务的情况下,上面的函数已经比较准确了,对于业务中有星形查询,主表走得是对的,然后其他的表的连接顺序交换了或是有个别表的索引换了,上面的函数会认为计划是不一样的。不过这种SQL其实也不用太关心,只要下边的谓词没变。BUFFER GETS没太多增长。

找出并分析问题SQL思路

分析出哪些SQL在目标库执行时有问题,并不像官方报告里展示的那样简单,下面我们就来看看难点在哪儿。

  • 执行计划改变的SQL(报告中的不准)
  • 性能下降的SQL(对比的维度选择)
    • ELASPED TIME因为SPA执行时是没有CACHE的,这个不准
    • CPU TIME 源库是并发环境,SPA默认是串行执行。这个也不太准
    • BUFFER GETS 这个相对比较准,但源库SQL的统计信息是平均值,目标库是单次的实际值。其实差别还是蛮大的。所以最通过BUFFER GETS看出来性能下降的SQL,还要带入绑定变量值到源库,目标库实际运行一下,对比真实的BUFFER GETS
  • TIME OUT的SQL
    • 这种SQL是重点,其实还要结合PLAN CHANGE来看,也就是超时且执行计划改变的。
    • SPA中对于SQL 执行时间的限定这块儿只能是一个固定的时间,不能按每个SQL历史的执行时间动态的给每个SQL设定执行时间,所以有的SQL本来执行时间就很长,而且垫计划也没变的,其实也没问题
    • TIME OUT的SQL找绑定变量值比较麻烦一点,报告里不会有绑定变量值,源库也可能没这个SQL信息。在测试库抓取时也会发现SPA运行SQL时会在SQL文本前加上一些注释使得SQL_ID发生变化,直接按文本找会发现可能是换行,空格等原因也找不出来。解决方案在测试库中把TIME OUT的SQL重新跑一遍,然后在V$SQL中按SQL_FULLTEXT like ‘%条件1%‘的结果集 intersect SQL_FULLTEXT like ‘%条件2%‘的结果集,或更多条件。