求知 文章 文库 Lib 视频 iProcess 课程 角色 咨询 工具 讲座 Modeler   Code  
会员   
 
  
每天15篇文章
不仅获得谋生技能
更可以追随信仰
 
 
 
通过创建用户自定义函数索引优化语句
 

2010-09-09 作者:fuyuncat 来源:HelloDBA.com

 

最近,一个系统升级后,发现系统运行非常慢。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做全表扫描。

首先考虑在bigtab的owner字段上建索引。

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 owner = 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表是每周更新一次的,因此我修改更新bigtab的job,在数据更新完毕后,重建t_users上的函数索引(在PLSQL块中要调用DDL语句的话,要用execute immediate)。

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

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

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

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

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

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

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



MySQL索引背后的数据结构
MySQL性能调优与架构设计
SQL Server数据库备份与恢复
让数据库飞起来 10大DB2优化
oracle的临时表空间写满磁盘
数据库的跨平台设计
更多...   


并发、大容量、高性能数据库
高级数据库架构设计师
Hadoop原理与实践
Oracle 数据仓库
数据仓库和数据挖掘
Oracle数据库开发与管理


GE 区块链技术与实现培训
航天科工某子公司 Nodejs高级应用开发
中盛益华 卓越管理者必须具备的五项能力
某信息技术公司 Python培训
某博彩IT系统厂商 易用性测试与评估
中国邮储银行 测试成熟度模型集成(TMMI)
中物院 产品经理与产品管理
更多...   
 
 
 
 
 
每天2个文档/视频
扫描微信二维码订阅
订阅技术月刊
获得每月300个技术资源
 
 

关于我们 | 联系我们 | 京ICP备10020922号 京公海网安备110108001071号