客服微信
作者：刘晓峰
1.背景
查询 select * from fnd_lookup_values_vl flv where flv.LOOKUP_TYPE 时，当你输入flv.lo ，此时PLSQL developer会自动弹出代码助手，
但是我选择代码助手提供的字段时，等待卡顿时间有5-10秒，而且每次使用弹出的字段都会卡顿
我本地同版本的plsql developer访问本地数据库不会卡顿，首先排除软件的问题，而网络问题即使查出来我也无法解决
考虑到代码助手会频繁访问数据字段视图,所以尝试使用trace看看到底查询那个数据字典产生了问题
理论上来说，如果用idea navicat连接也能进行优化
当然mysql也是一样的，自行搜索mysql trace
2.确认process id 并开启trace
SELECT p.tracefile, p.SPID--6533
FROM v$session s, v$process p
WHERE p.addr = s.paddr
AND s.sid = userenv('sid');
ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';
3.不执行查询，只是用代码助手弹框并选择
select * from flv.LOOKUP_TYPE flv.DESCRIPTION select * from fnd_lookup_values f where f.description= and f.start_date_active= select * from fnd_lookup_values_vl flv where flv.ROW_ID= and flv.ATTRIBUTE_CATEGORY= and flv.ATTRIBUTE1= and flv.ATTRIBUTE2 and flv.ATTRIBUTE3 and flv.DESCRIPTION select * from dba_objects d where d.OBJECT_NAME= select * from fnd_lookup_values_vl select * from f.lookup_type f.attribute13
4.关闭追踪
ALTER SESSION SET EVENTS '10046 trace name context off';
5.获取trace文件并解析
这一步出了问题，开发环境用的WINSCP获取文件，不稳定，取不到文件，因此使用我的本地环境分析（虽然我自己的服务器并不卡）
6.本地trace信息（由于拿不到自己开发环境的trace,所以只能试试看，拿本地的日志去优化开发环境，各位如果使用优化脚本效果不好，建议用自己的6分析，可能会发现新的性能问题）
6.1
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' and table_name = 'V_$SESSION' order by column_id Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ SQL*Net message to client 6 0.00 0.00 SQL*Net message from client 6 0.01 0.06 direct path read 154 0.05 0.38
********************************************************************************
6.2
select comments from sys.all_tab_comments
where owner = :object_owner
and table_name = :object_name
and origin_con_id in (1, sys_context('userenv', 'con_id'))
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 4 0.00 0.00
SQL*Net message from client 4 5.83 9.33
db file sequential read 2 0.00 0.00
*******************************************************************************
6.3
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' and table_name = 'DBA_OBJECTS' order by column_id
*******************************************************************************
6.4拿到开发环境的已有缓存的trace
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' and table_name = 'DBA_COL_COMMENTS' order by column_id call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.63 0.63 0 8 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 0.00 0.00 0 46 0 5 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.63 0.63 0 54 0 5 Misses in library cache during parse: 1
7.分析（目前全是基于本地环境的，非真实开发环境，因为拿不到trace）
看过来主要是sys.all_tab_columns视图的问题,那么问题简单了，直接一把梭，我们尝试一下对比执行计划看看
我的本地环境
--------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | Time | --------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 257 | 33 | 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID BATCHED | OBJ$ | 1 | 40 | 3 | 00:00:01 | | * 2 | INDEX RANGE SCAN | I_OBJ1 | 1 | | 2 | 00:00:01 | | 3 | TABLE ACCESS BY INDEX ROWID BATCHED | OBJ$ | 1 | 40 | 3 | 00:00:01 | | * 4 | INDEX RANGE SCAN | I_OBJ1 | 1 | | 2 | 00:00:01 | | 5 | TABLE ACCESS BY INDEX ROWID BATCHED | OBJ$ | 1 | 40 | 3 | 00:00:01 | | * 6 | INDEX RANGE SCAN | I_OBJ1 | 1 | | 2 | 00:00:01 | | 7 | TABLE ACCESS BY INDEX ROWID BATCHED | OBJ$ | 1 | 40 | 3 | 00:00:01 | | * 8 | INDEX RANGE SCAN | I_OBJ1 | 1 | | 2 | 00:00:01 | | 9 | TABLE ACCESS BY INDEX ROWID BATCHED | OBJ$ | 1 | 40 | 3 | 00:00:01 | | * 10 | INDEX RANGE SCAN | I_OBJ1 | 1 | | 2 | 00:00:01 | | 11 | NESTED LOOPS | | 1 | 31 | 3 | 00:00:01 | | * 12 | INDEX RANGE SCAN | I_OBJ1 | 1 | 8 | 2 | 00:00:01 | | 13 | TABLE ACCESS CLUSTER | USER$ | 1 | 23 | 1 | 00:00:01 | | * 14 | INDEX UNIQUE SCAN | I_USER# | 1 | | 0 | 00:00:01 | | 15 | SORT ORDER BY | | 1 | 257 | 33 | 00:00:01 | | * 16 | FILTER | | | | | | | 17 | NESTED LOOPS OUTER | | 1 | 257 | 14 | 00:00:01 | | 18 | NESTED LOOPS | | 1 | 222 | 13 | 00:00:01 | | 19 | NESTED LOOPS OUTER | | 1 | 209 | 12 | 00:00:01 | | 20 | NESTED LOOPS OUTER | | 1 | 199 | 11 | 00:00:01 | | 21 | NESTED LOOPS OUTER | | 1 | 155 | 7 | 00:00:01 | | 22 | NESTED LOOPS OUTER | | 1 | 127 | 6 | 00:00:01 | | 23 | NESTED LOOPS | | 1 | 122 | 5 | 00:00:01 | | 24 | NESTED LOOPS | | 1 | 74 | 4 | 00:00:01 | | 25 | TABLE ACCESS BY INDEX ROWID | USER$ | 1 | 18 | 1 | 00:00:01 | | * 26 | INDEX UNIQUE SCAN | I_USER1 | 1 | | 0 | 00:00:01 | | * 27 | TABLE ACCESS BY INDEX ROWID BATCHED | OBJ$ | 1 | 56 | 3 | 00:00:01 | | * 28 | INDEX RANGE SCAN | I_OBJ5 | 1 | | 2 | 00:00:01 | | * 29 | TABLE ACCESS CLUSTER | COL$ | 1 | 48 | 1 | 00:00:01 | | * 30 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | 0 | 00:00:01 | | 31 | TABLE ACCESS CLUSTER | TAB$ | 1 | 5 | 1 | 00:00:01 | | * 32 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | 0 | 00:00:01 | | * 33 | TABLE ACCESS CLUSTER | COLTYPE$ | 1 | 28 | 1 | 00:00:01 | | * 34 | TABLE ACCESS BY INDEX ROWID BATCHED | OBJ$ | 1 | 44 | 4 | 00:00:01 | | * 35 | INDEX RANGE SCAN | I_OBJ3 | 21 | | 1 | 00:00:01 | | * 36 | INDEX RANGE SCAN | I_HH_OBJ#_INTCOL# | 1 | 10 | 1 | 00:00:01 | | * 37 | INDEX RANGE SCAN | I_USER2 | 1 | 13 | 1 | 00:00:01 | | 38 | TABLE ACCESS CLUSTER | USER$ | 1 | 35 | 1 | 00:00:01 | | * 39 | INDEX UNIQUE SCAN | I_USER# | 1 | | 0 | 00:00:01 | | * 40 | TABLE ACCESS CLUSTER | TAB$ | 1 | 13 | 2 | 00:00:01 | | * 41 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | 1 | 00:00:01 | | 42 | NESTED LOOPS SEMI | | 1 | 12 | 2 | 00:00:01 | | 43 | FIXED TABLE FULL | X$KZSRO | 2 | 6 | 0 | 00:00:01 | | * 44 | INDEX RANGE SCAN | I_OBJAUTH2 | 1 | 9 | 1 | 00:00:01 | | * 45 | FIXED TABLE FULL | X$KZSPR | 1 | 7 | 0 | 00:00:01 | | * 46 | TABLE ACCESS BY INDEX ROWID BATCHED | USER_EDITIONING$ | 1 | 7 | 2 | 00:00:01 | | * 47 | INDEX RANGE SCAN | I_USER_EDITIONING | 9 | | 1 | 00:00:01 | | * 48 | TABLE ACCESS BY INDEX ROWID BATCHED | USER_EDITIONING$ | 1 | 7 | 2 | 00:00:01 | | * 49 | INDEX RANGE SCAN | I_USER_EDITIONING | 9 | | 1 | 00:00:01 | | 50 | NESTED LOOPS SEMI | | 1 | 18 | 3 | 00:00:01 | | * 51 | INDEX RANGE SCAN | I_OBJ4 | 1 | 9 | 2 | 00:00:01 | | * 52 | INDEX RANGE SCAN | I_USER2 | 3 | 27 | 1 | 00:00:01 | --------------------------------------------------------------------------------------------------------------- 当前的开发环境（来源于共享池缓存，非trace） ------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | | | 1635 (100)| | | 1 | TABLE ACCESS BY INDEX ROWID BATCHED | OBJ$ | 1 | 28 | 4 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | I_OBJ1 | 1 | | 3 (0)| 00:00:01 | | 3 | TABLE ACCESS BY INDEX ROWID BATCHED | OBJ$ | 1 | 28 | 4 (0)| 00:00:01 | |* 4 | INDEX RANGE SCAN | I_OBJ1 | 1 | | 3 (0)| 00:00:01 | | 5 | TABLE ACCESS BY INDEX ROWID BATCHED | OBJ$ | 1 | 28 | 4 (0)| 00:00:01 | |* 6 | INDEX RANGE SCAN | I_OBJ1 | 1 | | 3 (0)| 00:00:01 | | 7 | TABLE ACCESS BY INDEX ROWID BATCHED | OBJ$ | 1 | 28 | 4 (0)| 00:00:01 | |* 8 | INDEX RANGE SCAN | I_OBJ1 | 1 | | 3 (0)| 00:00:01 | | 9 | TABLE ACCESS BY INDEX ROWID BATCHED | OBJ$ | 1 | 28 | 4 (0)| 00:00:01 | |* 10 | INDEX RANGE SCAN | I_OBJ1 | 1 | | 3 (0)| 00:00:01 | | 11 | NESTED LOOPS | | 1 | 160 | 4 (0)| 00:00:01 | |* 12 | INDEX RANGE SCAN | I_OBJ1 | 1 | 10 | 3 (0)| 00:00:01 | | 13 | TABLE ACCESS CLUSTER | USER$ | 1 | 150 | 1 (0)| 00:00:01 | |* 14 | INDEX UNIQUE SCAN | I_USER# | 1 | | 0 (0)| | | 15 | SORT ORDER BY | | 1 | 357 | 1635 (1)| 00:00:01 | |* 16 | FILTER | | | | | | | 17 | NESTED LOOPS OUTER | | 63 | 22491 | 1610 (1)| 00:00:01 | |* 18 | HASH JOIN OUTER | | 63 | 21861 | 1483 (1)| 00:00:01 | | 19 | NESTED LOOPS OUTER | | 63 | 12411 | 1476 (1)| 00:00:01 | | 20 | NESTED LOOPS OUTER | | 63 | 10395 | 278 (0)| 00:00:01 | | 21 | NESTED LOOPS | | 63 | 8568 | 277 (0)| 00:00:01 | | 22 | NESTED LOOPS OUTER | | 1 | 84 | 275 (0)| 00:00:01 | |* 23 | HASH JOIN | | 1 | 78 | 273 (0)| 00:00:01 | |* 24 | TABLE ACCESS BY INDEX ROWID BATCHED| OBJ$ | 5 | 215 | 269 (0)| 00:00:01 | |* 25 | INDEX SKIP SCAN | I_OBJ5 | 5 | | 266 (0)| 00:00:01 | | 26 | NESTED LOOPS | | 579 | 20265 | 4 (0)| 00:00:01 | | 27 | TABLE ACCESS BY INDEX ROWID | USER$ | 1 | 12 | 2 (0)| 00:00:01 | |* 28 | INDEX UNIQUE SCAN | I_USER1 | 1 | | 1 (0)| 00:00:01 | | 29 | INDEX FAST FULL SCAN | I_USER2 | 579 | 13317 | 2 (0)| 00:00:01 | | 30 | TABLE ACCESS CLUSTER | TAB$ | 1 | 6 | 2 (0)| 00:00:01 | |* 31 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | 0 (0)| | |* 32 | TABLE ACCESS CLUSTER | COL$ | 68 | 3536 | 2 (0)| 00:00:01 | |* 33 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | 0 (0)| | | 34 | TABLE ACCESS BY INDEX ROWID | COLTYPE$ | 1 | 29 | 1 (0)| 00:00:01 | |* 35 | INDEX UNIQUE SCAN | I_COLTYPE2 | 1 | | 0 (0)| | |* 36 | TABLE ACCESS BY INDEX ROWID BATCHED | OBJ$ | 1 | 32 | 19 (0)| 00:00:01 | |* 37 | INDEX RANGE SCAN | I_OBJ3 | 85 | | 1 (0)| 00:00:01 | | 38 | TABLE ACCESS FULL | USER$ | 579 | 86850 | 7 (0)| 00:00:01 | |* 39 | INDEX RANGE SCAN | I_HH_OBJ#_INTCOL# | 1 | 10 | 2 (0)| 00:00:01 | |* 40 | TABLE ACCESS CLUSTER | TAB$ | 1 | 14 | 3 (0)| 00:00:01 | |* 41 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | 1 (0)| 00:00:01 | |* 42 | HASH JOIN SEMI | | 1 | 23 | 3 (0)| 00:00:01 | | 43 | FIXED TABLE FULL | X$KZSRO | 2 | 26 | 0 (0)| | |* 44 | INDEX RANGE SCAN | I_OBJAUTH1 | 6 | 60 | 3 (0)| 00:00:01 | |* 45 | FIXED TABLE FULL | X$KZSPR | 1 | 20 | 0 (0)| | |* 46 | TABLE ACCESS FULL | USER_EDITIONING$ | 1 | 7 | 2 (0)| 00:00:01 | | 47 | NESTED LOOPS SEMI | | 1 | 31 | 4 (0)| 00:00:01 | |* 48 | INDEX RANGE SCAN | I_OBJ4 | 1 | 11 | 3 (0)| 00:00:01 | |* 49 | INDEX RANGE SCAN | I_USER2 | 1 | 20 | 1 (0)| 00:00:01 | |* 50 | TABLE ACCESS FULL | USER_EDITIONING$ | 1 | 7 | 2 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------------------------
(补充一下，这里收集数据字典统计信息没有用的，因为数据字典表都是有定时任务自动收集，我后面的附件统计信息也可以体现，统计信息估算的是正确的)
可以看到成本差了50倍，下面说一下细节问题
1.全表扫描USER$的字节数较高，86KB，这个表是是一个聚簇表，查询该聚簇下所有的表可以用如下sql
SELECT d2.object_name, d2.object_type
FROM dba_objects d1, dba_objects d2
WHERE d1.object_name = 'USER$'
AND d1.data_object_id = d2.data_object_id
不过我感觉这个不是核心，因为在开始频繁使用代码助手后，USER$肯定是会缓存的，USER$虽然是全表扫描，但是这个表才几千行，不是瓶颈
2.代码助手每次都是硬编码(所以每个数据库的美化文件一定要一样，每个SQL文本会进行hash函数运算，空1格，空2格都会导致硬解析，最好的方式是尽可能使用函数封装对基表的调用)，导致解析时间较长，次要问题，因为今天看到解析时间0.6秒，而5秒的时间要么是闩锁问题，要么是初次执行的物理读问题，关于IO问题我们可以尝试使用keep pool缓存，但是数据字典表还是慎重一点，这里不去测试了
上面分析只是猜测各自情况，我拿不到开发环境的trace,没办法分析
看下开发环境数据字典表关于table_name的直方图怎么样，通过数据倾斜程度判断是否能够共享游标（这个只是方法，我认为table name列返回行数的数据分布应该非常平均，这一步没必要验证，这里只是演示）
SELECT dtc.column_name, --列名
dtc.num_distinct, --相异基数
dtc.num_nulls, --空值
dtc.num_buckets, --直方图桶个数
dtc.last_analyzed, --上次分析时间
dtc.histogram --直方图类型
FROM dba_tab_col_statistics dtc
WHERE dtc.table_name = 'OBJ$'
AND DTC.COLUMN_NAME in ('NAME' ,'OBJ#');
COLUMN_NAME NUM_DISTINCT NUM_NULLS NUM_BUCKETS LAST_ANALYZED HISTOGRAM
1 NAME 319520 0 254 2023/1/23 22:14:45 HYBRID
2 OBJ# 497879 0 254 2023/1/23 22:14:45 HYBRID
其中HYBRID 是混合直方图，查看其分布如下：
SELECT column_name,
endpoint_value,
endpoint_number
FROM dba_tab_histograms
WHERE table_name = 'OBJ$'
AND column_name in ('NAME' ,'OBJ#')
ORDER BY column_name,endpoint_value
桶数默认254,不同的值有32万，所以采用了混合直方图，先不讨论直方图的问题，（混合直方图经过测试发现没有其它直方图效果好）
select count(1) from (select name , count(1)from sys.OBJ$ group by NAME order by 2 desc)
所以直接修改，为了清楚的演示我要干什么，请看下面的例子（核心就是这个代码助手没有使用绑定变量）
请在自己的测试环境测试
----=========================================================----
alter system flush shared_pool;
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'
AND table_name = 'V_$SESSION'
ORDER BY column_id;
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'
AND table_name = 'V$SESSION'
ORDER BY column_id
select s.sql_text,s.sql_id,s.child_number,s.is_bind_sensitive,s.is_bind_aware,s.bind_data from v$sql s where s.sql_text like '%all_tab_columns%' and s.sql_text like '%''SYS''%' and s.sql_text not like '%v$sql%';
可以看到 代码助手的的SQL有2个子游标，而我自己查询的会产生两条解析
SQL_ID CHILD_NUMBER IS_BIND_SENSITIVE IS_BIND_AWARE BIND_DATA cqzr43zn3w0ma 0 N N --代码助手产生 cqzr43zn3w0ma 1 N N --代码助手产生 cbttrjzr90dra 0 N N --对应'V_$SESSION' 8v3bp2h9c93pg 0 N N --对应'V$SESSION'
开启共享游标
alter system flush shared_pool; alter session set "_optimizer_adaptive_cursor_sharing"=false alter session set cursor_sharing='FORCE';--此命令放入AfterConnect.sql 则可以每次开启新窗口可以自动执行，当然你也可以把会话语言选项放入
测试代码助手
select * from v$parameter p where p.name='cursor_sharing' and p.default_value and p.display_value and p.description select * from v$session s where s.blocking_session_status= and s.blocking_session_status and s.wait_time_micro and s.blocking_instance
再次查询游标,期间多用不同表的代码助手
select s.sql_text,s.sql_id,s.child_number,s.is_bind_sensitive,s.is_bind_aware,s.bind_data from v$sql s where s.sql_text like '%all_tab_columns%' ;
结果如下
SQL_ID CHILD_NUMBER IS_BIND_SENSITIVE IS_BIND_AWARE 8fqtv1mu1y2fv 0 Y N 8fqtv1mu1y2fv 1 Y N
可以看到开启共享游标之后，仍然产生了新的执行计划，我们要避免硬解析，至少能把硬解析改成软解析，所以在代码助手不使用绑定变量的前提下，
不仅要开启游标共享，还需要关闭自适应游标共享
alter session set "_optimizer_extended_cursor_sharing_rel"=none ; alter session set "_optimizer_extended_cursor_sharing"=none ; alter session set "_optimizer_adaptive_cursor_sharing"=false; alter session set cursor_sharing=FORCE;
检查修改是否正确
SELECT a.ksppinm "Parameter", b.KSPPSTDF "Default Value",
b.ksppstvl "Session Value",
c.ksppstvl "Instance Value",
decode(bitand(a.ksppiflg/256,1),1,'TRUE','FALSE') IS_SESSION_MODIFIABLE,
decode(bitand(a.ksppiflg/65536,3),1,'IMMEDIATE',2,'DEFERRED',3,'IMMEDIATE','FALSE') IS_SYSTEM_MODIFIABLE
FROM x$ksppi a,
x$ksppcv b,
x$ksppsv c
WHERE a.indx = b.indx
AND a.indx = c.indx
AND a.ksppinm in ('_optimizer_adaptive_cursor_sharing','cursor_sharing','_optimizer_extended_cursor_sharing_rel','_optimizer_extended_cursor_sharing')
再次检查，发现游标只剩下一个
SQL_ID CHILD_NUMBER IS_BIND_SENSITIVE IS_BIND_AWARE 8fqtv1mu1y2fv 0 N N IS_BIND_SENSITIVE='Y'：--绑定变量更改导致执行计划更改的更改的潜在候选者 IS_BIND_AWARE='Y'：--传入不同的绑定变量，导致执行计划真正改变了
小节：通过开启共享游标，并关闭自适应游标共享，能大大缓解硬编码导致的硬解析问题
我这么操作是因为数据字典表数据不倾斜，同时代码助手全部是硬编码，正式环境切不可使用，因为会影响其它SQL的执行计划
目前我只知道有这么一种防止SQL硬编码的硬解析问题，如果各位有更好的硬编码但是不硬解析的方法可以告诉我，这个优化就能更进一步，或者让PLSQL developer软件使用绑定变量
----=============================================================----------
3.两者执行计划差距较大，核心问题
我的环境，是一个索引范围扫描
| * 27 | TABLE ACCESS BY INDEX ROWID BATCHED | OBJ$ | 1 | 56 | 3 | 00:00:01 |
| * 28 | INDEX RANGE SCAN | I_OBJ5 | 1 | | 2 | 00:00:01 |
* 27 - filter(BITAND("O"."FLAGS",128)=0)
* 28 - access("O"."SPARE3"="U"."USER#" AND "O"."NAME"='V_$SESSION')
而开发环境是一个索引跳跃扫描
|* 24 | TABLE ACCESS BY INDEX ROWID BATCHED| OBJ$ | 5 | 215 | 269 (0)| 00:00:01 |
|* 25 | INDEX SKIP SCAN | 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性能分析器分析对比前后执行计划的性能提升，但是比较花费时间去写
