【技术文章】优化代码助手查询数据字典的执行计划

云贝教育 2023-02-17

188

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

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'));

优化完成

SQL_ID  8fqtv1mu1y2fv, child number 0
-------------------------------------
select column_name, nullable, data_type, data_type_mod,
data_type_owner, data_length, data_precision, data_scale, char_used,
char_length from sys.all_tab_columns where owner = :"SYS_B_0" and
table_name = :"SYS_B_1" order by column_id


Plan hash value: 4146289287


-----------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   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

select 代码优化 sql优化数据字典

文章转载自云贝教育，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

【技术文章】优化代码助手查询数据字典的执行计划

评论