ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 通过创建用户自定义函数索引优化语句

通过创建用户自定义函数索引优化语句

原创 Linux操作系统 作者:babylonia 时间:2009-06-16 13:43:35 0 删除 编辑

最近,一个系统升级后,发现系统运行非常慢。CPU消耗将近100%。

top查看系统,发现一个进程占用了几乎90%的CPU。用以下语句查出进程相应会话正在执行的操作:

select b.spid, a.sid, a.username, s.sql_text from v$session a , v$process 
b, v$sqlarea s where a.PADDR = b.ADDR and a.sql_hash_value = s.hash_value 
and b.spid=2585;

发现会话正在运行以下的代码(语句内容和表名已做替换,下面内容与现场实际有些出入,但过程相同):

SELECT username, created
FROM t_users a,
(SELECT COUNT(*) tabnum, owner
FROM bigtab
GROUP BY owner) b
 WHERE b.owner = a.username
 and b.tabnum > 10;

 这条语句真的让人很抓狂!竟然在字句中对一个巨大无比的表bigtab。做了group by。看下这条语句的查询计划:

Execution Plan
----------------------------------------------------------
Plan hash value: 42345627
 
--------------------------------------------------------------------------------
 
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time   |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |     1 |    32 | 1685   (4)| 00:00:21|
|   1 |  TABLE ACCESS BY INDEX ROWID| T_USERS     |     1 |    15 |    1   (0)| 00:00:01|
|   2 |   NESTED LOOPS              |             |     1 |    32 | 1685   (4)| 00:00:21|
|   3 |    VIEW                     |             |     1 |    17 | 1684   (4)| 00:00:21|
|*  4 |     FILTER                  |             |       |       |            |        |
|   5 |      HASH GROUP BY          |             |     1 |     7 | 1684   (4)| 00:00:21|
|   6 |       TABLE ACCESS FULL     | BIGTAB      |   529K|  3618K| 1639   (1)| 00:00:20|
|*  7 |    INDEX RANGE SCAN         | T_USER_IDX2 |     1 |       |    0   (0)| 00:00:01|
--------------------------------------------------------------------------------

 

由于通过查询计划看,需要对一张大表bigtab做全表扫描。

首先考虑在bigtabowner字段上建索引。

SQL> create index BIGTAB_IDX2 on BIGTAB (OWNER)
  2    tablespace EDGARDEMO
  3    pctfree 10
  4    initrans 2
  5    maxtrans 255
  6    storage
  7    (
  8      initial 64K
  9      minextents 1
 10      maxextents unlimited
 11    );
 
index created.
 
SQL>
 
SQL> analyze table bigtab compute statistics for table for all indexes for all indexed columns;
 
Table analyzed.
 

 

然后再看查询计划:

Execution Plan
----------------------------------------------------------
Plan hash value: 2769335568
---------------------------------------------------------------------------------------
| Id  | Operation               | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |             |    41 |  1312 |   346  (17)| 00:00:05 |
|*  1 |  FILTER                 |             |       |       |            |      |
|   2 |   HASH GROUP BY         |             |    41 |  1312 |   346  (17)| 00:00:05 |
|*  3 |    HASH JOIN            |             |   529K|    16M|   301   (5)| 00:00:04 |
|   4 |     TABLE ACCESS FULL   | T_USERS     |    23 |   598 |     3   (0)| 00:00:01 |
|   5 |     INDEX FAST FULL SCAN| BIGTAB_IDX2 |   529K|  3101K|   292   (3)| 00:00:04 |
--------------------------------------------------------------------------------

性能有所提高,bigtab的全表扫描已经没有了(t_users的数据量相对少多了),但是还存在全索引扫描。

索引建立后,会话对CPU的消耗占到50%左右,但是还是不能接受。

 

由于语句涉及到的功能是系统升级后一个相对比较重要的功能,因此不能被屏蔽。而且功能需要马上被使用,问题必须短时间内解决或规避,没有时间(或要争取时间)对相关模块重新设计。

分析一下涉及到的相关表bigtab,尽管这张表很大,但它存储的是统计数据,每周更新一次。并且系统在周末时处在低运行状态,只跑后台job,不对外服务。因此我考虑在t_user上建一个自定义函数索引,作为短期解决问题的方法。

 

首先,创建一个自定义函数(一定要指定DETERMINISTIC,并且是一个自治),函数的作用是是实现已owner为条件统计bigtab的记录数:

CREATE OR REPLACE FUNCTION f_counttab(p_user VARCHAR2) 
RETURN INTEGER
DETERMINISTIC
AS
PRAGMA AUTONOMOUS_TRANSACTION;
v_res PLS_INTEGER;
BEGIN
SELECT COUNT(*)
INTO v_res
FROM bigtab
WHERE wner = p_user;

RETURN v_res;
END;

           

然后,在t_users表上创建一个函数索引:

 

SQL> create index t_users_udf_idx on t_users (f_counttab (username))
  2    tablespace EDGARDEMO
  3    pctfree 10
  4    initrans 2
  5    maxtrans 255
  6    storage
  7    (
  8      initial 64K
  9      minextents 1
 10      maxextents unlimited
 11    );
 
index created.
 
SQL>
 
SQL> analyze table t_users compute statistics for table for all indexes for all indexed columns;
 
Table analyzed.
 

 

再将以上问题语句重写:

SELECT username, created
FROM t_users a
 WHERE f_counttab(username) > 10;

 

看看新的查询计划:

 
Execution Plan
----------------------------------------------------------
Plan hash value: 2295207410
-------------------------------------------------------------------------------
| Id  | Operation                   | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                  |    23 |   299 |     2 (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T_USERS          |    23 |   299 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T_USERS_UDF_IDX1 |    23 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------

 

从查询计划上看,从查询计划上看,只需要做索引扫描,效率得到大大的提高。

 

接下来的问题就是对索引的更新的。因为我们的索引是依赖于另外一张表bigtab的,因此一旦bigtab中的数据发生了变化,就会造成索引数据的错误,导致查询出来的结果会有问题。好在bigtab表是每周更新一次的,因此我修改更新bigtabjob,在数据更新完毕后,重建t_users上的函数索引(在PLSQL块中要调用DDL语句的话,要用execute immediate)。

 

将优化措施实施到生产系统后,CPU消耗迅速下降,系统恢复正常。

 

但是,关于自定义函数索引,并不提倡频繁使用,因为它会存在以下问题:

1、            首先,就是前面提到的索引数据错误问题。因为函数中相关表的数据修改是不会反映到索引中去的(除非没有依赖其他表);

2、            函数中如果涉及到其他数据库对象,如表,当依赖的数据库对象发生结构变化、或者失效时,函数会失效,导致索引失效,最终导致索引所在表的很多操作无法进行。

3、            自定义函数索引需要特定的系统参数配置。它需要在CBD模式下,并且设置query_rewrite_enabled=truequery_rewrite_integerity=trusted才能使优化器选择到函数索引。

 

此次使用自定义函数来解决语句性能问题不是长远之计。出现这个问题,应该是设计上的问题。因此,我给开发组提出以下建议作为此问题的长远解决办法:

另外建一张表,按owner分组统计bigtab,在每周对bigtab进行数据更新时,更新这张统计表,查询时,让t_users join这张表。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/12712263/viewspace-606493/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-01-31

  • 博文量
    44
  • 访问量
    41297