【技术文章】plsql developer代码助手卡顿分析

云贝教育 2023-02-17

380

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性能分析器分析对比前后执行计划的性能提升，但是比较花费时间去写

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

【技术文章】plsql developer代码助手卡顿分析

评论

相关阅读