通过创建用户自定义函数索引优化语句(fuyuncat)
最近,一个系统升级后,发现系统运行非常慢。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这张表。
转自 http://blog.csdn.net/haoxiaoyan/article/details/14557079