【Oracle】优化代码助手查询数据字典的执行计划

作者：刘晓峰

这么多步骤，也能写自动化脚本，感兴趣的可以自己尝试一下

1.确认一定要开启共享游标

begin    
    EXECUTE IMMEDIATE ' alter session set "_optimizer_extended_cursor_sharing_rel"=none ';
    EXECUTE IMMEDIATE ' alter session set "_optimizer_extended_cursor_sharing"=none ';
    EXECUTE IMMEDIATE ' alter session set "_optimizer_adaptive_cursor_sharing"=false';
    EXECUTE IMMEDIATE 'alter session set cursor_sharing=FORCE ';
 end;

2.flush shared_pool的替代方式,方便找到代码助手产生的执行计划

DECLARE
  CURSOR a_cur IS
    SELECT DISTINCT s.address || ',' || s.hash_value addr
      FROM v$sql s
     WHERE upper(s.sql_text) LIKE upper('%all_tab_columns%')
       AND s.sql_text LIKE '%:%'
       AND s.sql_text NOT LIKE '%v$sql%';

BEGIN
  FOR a_rec IN a_cur LOOP
    EXECUTE IMMEDIATE 'begin  sys.dbms_shared_pool.purge(:1, ''c''); end;'
      USING a_rec.addr;
  END LOOP;
END; 

3.使用代码助手确认执行计划

fnd_lookup_values_vl.attribute2
v$session.user#
select * from v$instance k where k.blocked 

4.找到sql_id

select s.sql_id ,s.plan_hash_value,s.child_number from v$sql s where upper(s.sql_text) like upper('%all_tab_columns%') and s.sql_text like '%:%' and s.sql_text not like '%v$sql%'
   	SQL_ID PLAN_HASH_VALUE	CHILD_NUMBER
SQL_ID	PLAN_HASH_VALUE	 CHILD_NUMBER
8fqtv1mu1y2fv 1708561845 0 

请自行替换后续的8fqtv1mu1y2fv 和1708561845为你环境的值

5.检查执行计划

select * from table(dbms_xplan.display_cursor('8fqtv1mu1y2fv','0','advanced'));
1	SQL_ID  8fqtv1mu1y2fv, child number 0
2	-------------------------------------
3	select column_name, nullable, data_type, data_type_mod, 
4	data_type_owner, data_length, data_precision, data_scale, char_used, 
5	char_length from sys.all_tab_columns where owner = :"SYS_B_0" and 
6	table_name = :"SYS_B_1" order by column_id
7	 
8	Plan hash value: 1708561845
9	 
10	-----------------------------------------------------------------------------------------------
11	| Id  | Operation               | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
12	-----------------------------------------------------------------------------------------------
13	|   0 | SELECT STATEMENT        |           |       |       |  1635 (100)|          | 

6.开始使用SPM优化助手优化

DECLARE
  tuning_task_name VARCHAR2(240);
BEGIN

  tuning_task_name := dbms_sqltune.create_tuning_task(sql_id          => '8fqtv1mu1y2fv',
                                                      plan_hash_value => '1708561845',
                                                      scope           => 'COMPREHENSIVE',
                                                      time_limit      => 300, --优化时间上线，单位秒
                                                      task_name       => 'SQLTURNING_CODE_ASSISTANT',
                                                      description     => 'optimize sql',
                                                      con_name        => NULL);
END; 

7.执行任务

begin
  dbms_sqltune.execute_tuning_task(task_name=>'SQLTURNING_CODE_ASSISTANT');
end;

8.查询任务

select * from user_advisor_log u where u.task_name='SQLTURNING_CODE_ASSISTANT';

9.打印结果

select dbms_sqltune.report_tuning_task('SQLTURNING_CODE_ASSISTANT') from dual;

10.找到关键词“SQL Profile Finding”，

比如我的长这样

1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
  A potentially better execution plan was found for this statement.

  Recommendation (estimated benefit: 77.34%)
  ------------------------------------------
  - Consider accepting the recommended SQL profile.
    execute dbms_sqltune.accept_sql_profile(task_name =>
            'SQLTURNING_CODE_ASSISTANT', task_owner => 'APPS', replace =>
            TRUE);

你能看到更多信息

旧成本Plan hash value: 1062139556

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                     | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                              |                   |     2 |   714 |  7435   (1)| 00:00:01 | 

优化后的新成本Plan hash value: 4146289287

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                   |     1 |   357 |   292   (1)| 00:00:04 | 

11.执行sql配置更改

begin
dbms_sqltune.accept_sql_profile(task_name =>
            'SQLTURNING_CODE_ASSISTANT', task_owner => 'APPS', replace =>
            TRUE);
end; 

12.检查执行计划

select* from v$sql s where s.sql_id='8fqtv1mu1y2fv';--子游标1性能更好
select * from table(dbms_xplan.display_cursor('8fqtv1mu1y2fv','0','advanced'));
select * from table(dbms_xplan.display_cursor('8fqtv1mu1y2fv','1','advanced'));

13.计划基线捕获

declare 
l_plans_loaded pls_integer;
begin
 l_plans_loaded:=dbms_spm.load_plans_from_cursor_cache(sql_id=>'8fqtv1mu1y2fv'); --这里有参数可以直接固定，我为了演示没有使用fixed=>'YES'
 dbms_output.put_line(l_plans_loaded);
end;

14.检查计划基线

SELECT sql_handle,plan_name,enabled
      , --    指示计划基准是已启用（YES）还是已禁用（NO）
       accepted
      , --   表示计划基线是否被接受（YES）否（NO）
       fixed
      , --    指示计划基准是否固定（YES）（NO）  
       substr(sql_text,1,100)
  FROM dba_sql_plan_baselines s
 WHERE  upper(s.sql_text) like upper('%all_tab_columns%') and s.sql_text like '%:%' and s.sql_text not like '%v$sql%'
 SQL_HANDLE  PLAN_NAME ENABLED ACCEPTED  FIXED
SQL_a66bfc0020f65c85  SQL_PLAN_acuzw00hgcr453ebe2368  YES YES NO
SQL_a66bfc0020f65c85  SQL_PLAN_acuzw00hgcr458b0d60a7  YES YES NO 

15.检查并找到性能较好的执行计划对应的基线

select * from table(dbms_xplan.DISPLAY_SQL_PLAN_BASELINE(plan_name => 'SQL_PLAN_acuzw00hgcr453ebe2368',sql_handle =>'SQL_a66bfc0020f65c85' ) ); 
select * from table(dbms_xplan.DISPLAY_SQL_PLAN_BASELINE(plan_name => 'SQL_PLAN_acuzw00hgcr458b0d60a7',sql_handle =>'SQL_a66bfc0020f65c85' ) );
  --此成本较低，性能好

可以看到执行计划从跳跃扫描变成了索引顺序扫描，同时驱动顺序变化（正常的执行计划应该是顺序扫描才对，因为索引的先导列就是name,不应该执行跳跃扫描，正常情况下优化我直接写hint完事了）

16.固定计划基线

declare 
l_plans_altered pls_integer;
begin
l_plans_altered:=dbms_spm.alter_sql_plan_baseline(sql_handle      =>'SQL_a66bfc0020f65c85' ,
                                                  plan_name       =>'SQL_PLAN_acuzw00hgcr458b0d60a7' ,
                                                  attribute_name  =>'fixed' ,
                                                  attribute_value =>'YES' );

  
end; 

17.重复步骤2,3，然后检查新产生的执行计划

select  s.sql_id ,s.plan_hash_value,s.child_number from v$sql s where upper(s.sql_text) like upper('%all_tab_columns%') and s.sql_text like '%:%' and s.sql_text not like '%v$sql%'
select * from table(dbms_xplan.display_cursor('8fqtv1mu1y2fv','0','advanced'));

优化完成

1 SQL_ID  8fqtv1mu1y2fv, child number 0
2 -------------------------------------
3 select column_name, nullable, data_type, data_type_mod, 
4 data_type_owner, data_length, data_precision, data_scale, char_used, 
5 char_length from sys.all_tab_columns where owner = :"SYS_B_0" and 
6 table_name = :"SYS_B_1" order by column_id
7  
8 Plan hash value: 4146289287
9  
10  -----------------------------------------------------------------------------------------------------------
11  | Id  | Operation                             | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
12  -----------------------------------------------------------------------------------------------------------
13  |   0 | SELECT STATEMENT                      |                   |       |       |   292 (100)|          | 

18.可以自行尝试修改代码助手的delay，由默认的500毫秒改成100毫秒甚至更低，

现在我的开发环境，代码助手弹出column直接起飞,完全不卡，以前是5-10秒，非常卡顿，优化效果非常好（如果我能拿到trace就更有说服力）

如果还是存在卡顿，请按前面的教程，跑trace然后自行分析

另外package的代码助手优化原理一模一样，这里不再赘述

select from fnd_lookup_types_vl flv where flv.lookup_type