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