云贝教育 |【技术文章】plsql developer代码助手卡顿分析

1.背景

查询select*fromfnd_lookup_values__TYPE时，当你输入，此时PLSQLdeveloper会自动弹出代码助手，

但是我选择代码助手提供的字段时，等待卡顿时间有5-10秒，而且每次使用弹出的字段都会卡顿

我本地同版本的plsqldeveloper访问本地数据库不会卡顿，首先排除软件的问题，而网络问题即使查出来我也无法解决

考虑到代码助手会频繁访问数据字段视图,所以尝试使用trace看看到底查询那个数据字典产生了问题

理论上来说，如果用ideanavicat连接也能进行优化

当然mysql也是一样的，自行搜索mysqltrace

2.确认processid并开启trace

,$sessions,v$==userenv('sid');ALTERSESSIONSETEVENTS'10046tracenamecontextforever,level12';

3.不执行查询，只是用代码助手弹框并选择

select*_*fromfnd_lookup_=_date_active=select*fromfnd_lookup_values__ID=_CATEGORY==*fromdba__NAME=select*fromfnd_lookup_values_vlselect*_

4.关闭追踪

ALTERSESSIONSETEVENTS'10046tracenamecontextoff';

5.获取trace文件并解析

这一步出了问题，开发环境用的WINSCP获取文件，不稳定，取不到文件，因此使用我的本地环境分析（虽然我自己的服务器并不卡）

6.本地trace信息（由于拿不到自己开发环境的trace,所以只能试试看，拿本地的日志去优化开发环境，各位如果使用优化脚本效果不好，建议用自己的6分析，可能会发现新的性能问题）

--6.1

selectcolumn_name,nullable,data_type,data_type_mod,data_type_owner,data_length,data_precision,data_scale,char_used,char__tab_columnswhereowner='SYS'andtable_name='V_$SESSION'orderbycolumn_idElapsedtimesincludewaitingonfollowingevents:--------------------SQL**

********************************************************************************

--6.2

_tab_commentswhereowner=:object_ownerandtable_name=:object_nameandorigin_con_idin(1,sys_context('userenv','con_id'))Elapsedtimesincludewaitingonfollowingevents:--------------------SQL**

*******************************************************************************

--6.3

selectcolumn_name,nullable,data_type,data_type_mod,data_type_owner,data_length,data_precision,data_scale,char_used,char__tab_columnswhereowner='SYS'andtable_name='DBA_OBJECTS'orderbycolumn_id

--6.4拿到开发环境的已有缓存的trace

selectcolumn_name,nullable,data_type,data_type_mod,data_type_owner,data_length,data_precision,data_scale,char_used,char__tab_columnswhereowner='SYS'andtable_name='DBA_COL_COMMENTS'orderbycolumn_idcallcountcpuelapseddiskquerycurrentrows--------------------------------------------------------------------------------------:1

7.分析（目前全是基于本地环境的，非真实开发环境，因为拿不到trace）

看过来主要是_tab_columns视图的问题,那么问题简单了，直接一把梭，我们尝试一下对比执行计划看看

我的本地环境

---------------------------------------------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost|Time|---------------------------------------------------------------------------------------------------------------|0|SELECTSTATEMENT||1|257|33|00:00:01||1|TABLEACCESSBYINDEXROWIDBATCHED|OBJ$|1|40|3|00:00:01||*2|INDEXRANGESCAN|I_OBJ1|1||2|00:00:01||3|TABLEACCESSBYINDEXROWIDBATCHED|OBJ$|1|40|3|00:00:01||*4|INDEXRANGESCAN|I_OBJ1|1||2|00:00:01||5|TABLEACCESSBYINDEXROWIDBATCHED|OBJ$|1|40|3|00:00:01||*6|INDEXRANGESCAN|I_OBJ1|1||2|00:00:01||7|TABLEACCESSBYINDEXROWIDBATCHED|OBJ$|1|40|3|00:00:01||*8|INDEXRANGESCAN|I_OBJ1|1||2|00:00:01||9|TABLEACCESSBYINDEXROWIDBATCHED|OBJ$|1|40|3|00:00:01||*10|INDEXRANGESCAN|I_OBJ1|1||2|00:00:01||11|NESTEDLOOPS||1|31|3|00:00:01||*12|INDEXRANGESCAN|I_OBJ1|1|8|2|00:00:01||13|TABLEACCESSCLUSTER|USER$|1|23|1|00:00:01||*14|INDEXUNIQUESCAN|I_USER|1||0|00:00:01||31|TABLEACCESSCLUSTER|TAB$|1|5|1|00:00:01||*32|INDEXUNIQUESCAN|I_OBJ_INTCOL|1||0|00:00:01||*40|TABLEACCESSCLUSTER|TAB$|1|13|2|00:00:01||*41|INDEXUNIQUESCAN|I_OBJ|1||0(0)|||15|SORTORDERBY||1|357|1635(1)|00:00:01||*16|FILTER|||||||17|NESTEDLOOPSOUTER||63|22491|1610(1)|00:00:01||*18|HASHJOINOUTER||63|21861|1483(1)|00:00:01||19|NESTEDLOOPSOUTER||63|12411|1476(1)|00:00:01||20|NESTEDLOOPSOUTER||63|10395|278(0)|00:00:01||21|NESTEDLOOPS||63|8568|277(0)|00:00:01||22|NESTEDLOOPSOUTER||1|84|275(0)|00:00:01||*23|HASHJOIN||1|78|273(0)|00:00:01||*24|TABLEACCESSBYINDEXROWIDBATCHED|OBJ$|5|215|269(0)|00:00:01||*25|INDEXSKIPSCAN|I_OBJ5|5||266(0)|00:00:01||26|NESTEDLOOPS||579|20265|4(0)|00:00:01||27|TABLEACCESSBYINDEXROWID|USER$|1|12|2(0)|00:00:01||*28|INDEXUNIQUESCAN|I_USER1|1||1(0)|00:00:01||29|INDEXFASTFULLSCAN|I_USER2|579|13317|2(0)|00:00:01||30|TABLEACCESSCLUSTER|TAB$|1|6|2(0)|00:00:01||*31|INDEXUNIQUESCAN|I_OBJ|1||0(0)|||34|TABLEACCESSBYINDEXROWID|COLTYPE$|1|29|1(0)|00:00:01||*35|INDEXUNIQUESCAN|I_COLTYPE2|1||0(0)|||*36|TABLEACCESSBYINDEXROWIDBATCHED|OBJ$|1|32|19(0)|00:00:01||*37|INDEXRANGESCAN|I_OBJ3|85||1(0)|00:00:01||38|TABLEACCESSFULL|USER$|579|86850|7(0)|00:00:01||*39|INDEXRANGESCAN|I_HH_OBJ|1|10|2(0)|00:00:01||*40|TABLEACCESSCLUSTER|TAB$|1|14|3(0)|00:00:01||*41|INDEXUNIQUESCAN|I_OBJ39;_object_id=_object_id

不过我感觉这个不是核心，因为在开始频繁使用代码助手后，USER$肯定是会缓存的，USER$虽然是全表扫描，但是这个表才几千行，不是瓶颈

2.代码助手每次都是硬编码(所以每个数据库的美化文件一定要一样，每个SQL文本会进行hash函数运算，空1格，空2格都会导致硬解析，最好的方式是尽可能使用函数封装对基表的调用)，导致解析时间较长，次要问题，因为今天看到解析时间0.6秒，而5秒的时间要么是闩锁问题，要么是初次执行的物理读问题，关于IO问题我们可以尝试使用keeppool缓存，但是数据字典表还是慎重一点，这里不去测试了

上面分析只是猜测各自情况，我拿不到开发环境的trace,没办法分析

看下开发环境数据字典表关于table_name的直方图怎么样，通过数据倾斜程度判断是否能够共享游标（这个只是方法，我认为tablename列返回行数的数据分布应该非常平均，这一步没必要验证，这里只是演示）

_name,--列名_distinct,--相异基数_nulls,--空值_buckets,--直方图桶个数_analyzed,--上次分析时间直方图类型FROMdba_tab_col__name='OBJ');COLUMN_NAMENUM_DISTINCTNUM_NULLSNUM_BUCKETSLAST_ANALYZEDHISTOGRAM1NAME31952002542023/1/2322:14:45HYBRID2OBJ39;ANDcolumn_namein('NAME','OBJ"AND"O"."NAME"='V_$SESSION')

而开发环境是一个索引跳跃扫描

|*24|TABLEACCESSBYINDEXROWIDBATCHED|OBJ$|5|215|269(0)|00:00:01||*25|INDEXSKIPSCAN|I_OBJ5|5||266(0)|00:00:01|24-filter(BITAND("O"."FLAGS",128)=0)25-access("O"."NAME"='FND_LOOKUP_VALUES_VL')filter("O"."NAME"='FND_LOOKUP_VALUES_VL')

且驱动顺序不一样，更多细节不进行讨论（主要我也没时间分析）

按平时我的操作，肯定是慢慢调整，但是这个是标准数据字典视图，我可以把这个SQL在本地开发环境调整的非常好，但是花费非常多的时间，而且失去了普遍性，因为每个环境的数据字典表的数据分布不一样，有没有“快速”，且“自动化调优”的方式？即使性能差一点也可以接受

1.把我的本地环境的执行计划基线，使用数据泵和DBMS_SPM导出，然后导入到开发环境（本地是云桌面，文件导入不进来，导入进来也没有权限导入到数据库中，所以可以是可以，不建议,这个方法可以在DBA协助下，把测试环境的性能较好的执行计划写入正式环境中，使用场景不高，我也不演示了）

2.使用自动调优助手，生成性能较好的执行计划，然后固定。

详细过程见文件《优化代码助手查询数据字典的执行计划.sql》

验证结果略，因为我拿不到trace,分析不了，可以用SPM性能分析器分析对比前后执行计划的性能提升，但是比较花费时间去写

很赞哦!（81）