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

原因分析

代码助手卡顿来源于（不考虑网络和软件版本等影响）

• A.从已连接的数据库的数据字典中，读取该表的列信息
• B.将读取到的列信息返回到plsql编辑器，并进行字符处理，然后显示 

如何优化B

这个勾勾是维持IDE的代码和数据库数据字典大小写一致，影响性能

（我是怎么找到这个勾勾会影响性能呢，当然不是一个个按钮试的，得去搜这个IDE的官方文档，文档倒是一行行看的）

如果优化完第三步，可以将下面的delay由500毫秒改成100毫秒或者50毫秒，速度将会非常快

去掉此勾勾

如何优化A

如果你在代码助手弹出列的时候就很卡，就可以接着往下看

详细说明见另一个文档，这里只给出操作步骤，另外只涉及到colunm的优化，package的代码助手同理，只不过优化的数据字典原始表不同，大家可以按照此方法自行优化

1. 检查数据字典的统计信息是否最新

作用：是使得对于数据字典的查询，生成执行计划更符合真实的数据分布

操作方法：在PLSQL窗口执行下面命令（如果你确认数据字典统计信息最新可以不执行，比如我就没执行）

begin 
 dbms_stats.gather_dictionary_stats;
end;
注：dbms_stats.gather_fixed_objects_stats; 可以用于收集X$表统计信息

2. 开启共享游标，关闭自适应游标共享

作用：大大缓解了代码助手硬解析问题

操作方法:（一定要保证只有这些代码，否则有人写一写破坏性的代码在里面，风险就会很大）

1. 找到plsql developer安装路径，然后找到文件AfterConnect.sql所在位置，比如我的在这里

2.将下面代码复制进这个文件中

DECLARE
  l_instance_name VARCHAR2(240);
BEGIN

  SELECT i.instance_name INTO l_instance_name FROM v$instance i;
  IF l_instance_name = 'ORCL'
  THEN
    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 IF;
EXCEPTION
  WHEN OTHERS THEN
    NULL;
END;

结果会像这样

作用：会在新开窗口后自动执行上述代码

隐藏参数基于会话修改，且是开发环境，问题不是很大。

Force一定一定不要在生产环境设置

按时间顺序来说是绑定变量-》共享游标-》自适应共享游标，后者都修复了前者的一些问题

ORCL 是需要替换的

请自行替换测试环境此SQL的查询结果： 

SELECT i.instance_name  FROM v$instance i;

像这样：

重启plsql developer之后就可以测试代码助手的速度了

测试环境的并发请求不会影响，如果需要在plsql developer进行SQL调优可以先注释掉上述代码

如果你操作之后速度变慢了，可以直接清空AfterConnect.sql文件

3.优化代码助手查询数据字典的执行计划

一定要执行2才能继续往下执行，否则优化没有意义

作用：通过游标缓存记录的信息，发现开发环境的执行计划很差，所以你要是不差就不用执行。

怎么判断差不差？

有一个简单的方法

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 = :A
and table_name = :B
order by column_id

拷贝到解释计划窗口，

若成本大于300小于1000则是需要考虑优化

成本大于1000那肯定是要优化的

成本小于50是正常情况，不用优化（我本地才30，跟我的基表少也有关系）

通过下面脚本，将会生成更好的执行计划并替换（繁琐但是有用，后续可以写一个数据泵和SPM导出的文档， 简单一点，但是效果可能没有我这个好）

云贝教育 | 【技术文章】优化代码助手查询数据字典的执行计划 - 课程体系 - 云贝教育 (yunbee.net)

如果你操作之后速度变慢了，可以直接清空AfterConnect.sql文件，因为我固定的是带绑定变量的执行计划，你关闭共享游标那么全是硬编码了。

所以计划基线不删也没问题，想删除的就可以自行删除，文档之前都在群里发过

分析过程

感兴趣看看，我是怎么发现问题，解决问题的

云贝教育 |【技术文章】plsql developer代码助手卡顿分析 - 课程体系 - 云贝教育 (yunbee.net)