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

[English]

作者： fuyuncat

来源： www.HelloDBA.com

日期： 2008-05-06 14:50:13

分享到 

最近，一个系统升级后，发现系统运行非常慢。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这张表。