同义词优化案例

在分析一个客户数据库中的statspack报告时，发现有1条SQL引起了大量的逻辑读，执行次数也比较高，在2小时内执行了74,131次，每次的逻辑读为517.9，主要内容如下所示：

通过和开发部门的沟通，了解到应用基础架构，就是从数据字典中查询相关信息（如表结构、字段及约束），然后动态拼装SQL，继而执行并返回结果，这种做法在系统负荷低时可能会无关紧要，但是，当业务系统并发量大、解析频繁时，这样的查询就可能带来严重的性能问题。

该条SQL的全文整理如下：

SELECT NULL AS table_cat,
       t.owner AS table_schem,
       t.table_name AS table_name,
       t.column_name AS column_name,
       DECODE (t.data_type, 'CHAR', 1, 'VARCHAR2', 12, 'NUMBER', 3,'LONG', -1, 'DATE', 93, 'RAW', -3, 'LONG RAW', -4, 1111) AS data_type,
       t.data_type AS type_name,
       DECODE (t.data_precision, null, t.data_length,t.data_precision) AS column_size,
       0 AS buffer_length,
       t.data_scale AS decimal_digits,
       10 AS num_prec_radix,
       DECODE (t.nullable, 'N', 0, 1) AS nullable,
       NULL AS remarks,
       t.data_default AS column_def,
       0 AS sql_data_type,
       0 AS sql_datetime_sub,
       t.data_length AS char_octet_length,
       t.column_id AS ordinal_position,
       DECODE (t.nullable, 'N', 'NO', 'YES') AS is_nullable
FROM all_tab_columns t
WHERE t.owner LIKE :1 ESCAPE '/'
  AND t.table_name LIKE :2 ESCAPE '/'
  AND t.column_name LIKE :3
  ESCAPE '/'
 ORDER BY table_schem, table_name, ordinal_position；

我们可以先来观察以下该SQL的执行计划： 

SQL> explain plan for ....;
 Explained
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
------------------------------------------------------------------
| Id  | Operation                            | Name              |
------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                   |
|   1 |  TABLE ACCESS BY INDEX ROWID         | OBJ$              |
|   2 |   INDEX RANGE SCAN                   | I_OBJ1            |
|   3 |  TABLE ACCESS BY INDEX ROWID         | OBJ$              |
|   4 |   INDEX RANGE SCAN                   | I_OBJ1            |
|   5 |  TABLE ACCESS BY INDEX ROWID         | OBJ$              |
|   6 |   INDEX RANGE SCAN                   | I_OBJ1            |
|   7 |  TABLE ACCESS BY INDEX ROWID         | OBJ$              |
|   8 |   INDEX RANGE SCAN                   | I_OBJ1            |
|   9 |  TABLE ACCESS BY INDEX ROWID         | OBJ$              |
|  10 |   INDEX RANGE SCAN                   | I_OBJ1            |
|  11 |  SORT ORDER BY                       |                   |
|  12 |   FILTER                             |                   |
|  13 |    NESTED LOOPS OUTER                |                   |
|  14 |     NESTED LOOPS OUTER               |                   |
|  15 |      NESTED LOOPS OUTER              |                   |
|  16 |       NESTED LOOPS OUTER             |                   |
|  17 |        NESTED LOOPS                  |                   |
|  18 |         NESTED LOOPS                 |                   |
|  19 |          NESTED LOOPS                |                   |
|  20 |           TABLE ACCESS BY INDEX ROWID| USER$             |
|  21 |            INDEX UNIQUE SCAN         | I_USER1           |
|  22 |           TABLE ACCESS BY INDEX ROWID| OBJ$              |
|  23 |            INDEX RANGE SCAN          | I_OBJ5            |
|  24 |          TABLE ACCESS CLUSTER        | USER$             |
|  25 |           INDEX UNIQUE SCAN          | I_USER#           |
|  26 |         TABLE ACCESS CLUSTER         | COL$              |
|  27 |          INDEX UNIQUE SCAN           | I_OBJ#            |
|  28 |        TABLE ACCESS CLUSTER          | COLTYPE$          |
|  29 |       INDEX RANGE SCAN               | I_HH_OBJ#_INTCOL# |
|  30 |      TABLE ACCESS BY INDEX ROWID     | OBJ$              |
|  31 |       INDEX RANGE SCAN               | I_OBJ3            |
|  32 |     TABLE ACCESS CLUSTER             | USER$             |
|  33 |      INDEX UNIQUE SCAN               | I_USER#           |
|  34 |    TABLE ACCESS CLUSTER              | TAB$              |
|  35 |     INDEX UNIQUE SCAN                | I_OBJ#            |
|  36 |    NESTED LOOPS                      |                   |
|  37 |     FIXED TABLE FULL                 | X$KZSRO           |
|  38 |     INDEX RANGE SCAN                 | I_OBJAUTH2        |
|  39 |    FIXED TABLE FULL                  | X$KZSPR           |
|  40 |    NESTED LOOPS                      |                   |
|  41 |     INDEX RANGE SCAN                 | I_OBJ4            |
|  42 |     TABLE ACCESS CLUSTER             | USER$             |
|  43 |      INDEX UNIQUE SCAN               | I_USER#           |
------------------------------------------------------------------

由于all_tab_columns是sys用户下的一个视图，从执行计划中可以看出，下面嵌套多层基础数据字典表，因此，执行计划较为复杂。由此我们应当获得一个基本常识：在业务系统的底层，应该尽量避免频繁使用复杂的数据字典视图。

对于这个案例，用户很难去修改底层的框架，所以只能尝试通过其他手段进行优化。由于这个查询是用于获得数据表的字段信息等，对于已经长时间运行的业务系统，除了特殊的业务变更，基本上不可能再去修改表结构及增减字段，因此，为了改善执行计划，我们想到了用一张预先创建好的表来取代all_tab_columns，精确的说应该是取代对sys下all_tab_columns的访问。

我们计划将该表创建在应用用户下，取名为all_tab_columns_temp，并使用all_tab_columns来作为它的同义词，使应用对all_tab_columns的访问需求从all_tab_columns_temp表中直接获取。

由于sys下的all_tab_columns中存在LONG数据类型，因此，对all_tab_columns_temp表的创建采用sqlplus提供的copy命令来实现：

SQL> copy from oti/oti@fwx to oti/oti@fwx create all_tab_columns_temp using select * from all_tab_columns t where t.owner = 'OTI';

创建同义词：

SQL> create synonym all_tab_columns for oti.all_tab_columns_temp;
Synonym created

根据查询条件创建组合索引：

SQL> create index oti.all_tab_columns_temp_inx1 on oti.all_tab_columns_temp(owner,table_name,column_name);
Index created

调整后的执行计划如下：

SQL> explain plan for ....;
 Explained
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
------------------------------------------------------------------
| Id  | Operation                    | Name                      |
------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                           |
|   1 |  SORT ORDER BY               |                           |
|   2 |   TABLE ACCESS BY INDEX ROWID| ALL_TAB_COLUMNS_TEMP      |
|   3 |    INDEX RANGE SCAN          | ALL_TAB_COLUMNS_TEMP_INX1 |
------------------------------------------------------------------

调整后，该SQL查询的逻辑读降低为4，系统的性能获得了大幅度的提升。 在深入了解了Oracle的表、视图、同义词的逻辑之后，就可以据此做出很多有趣的尝试。