需要优化的语句如下
select count(*)
from t_addr_list tl , t_addr_group tg
where tl.group_id=tg.group_id
and tg.user_id=to_number('24886');
开始执行时间在5秒左右

一.设置参数
SQL> SET AUTOTRACE ON
打开执行计划和统计信息

SQL> SET TIMING ON
打开语句执行的时间

二.查询所用到的两个表的行数
SQL> select count(*) from t_addr_list;

COUNT(*)
----------
1963920
已用时间: 00: 00: 07.07

SQL> select count(*) from t_addr_group where tg.user_id=to_number('24885');

COUNT(*)
----------
154416
已用时间: 00: 00: 01.08

三.查看语句的执行计划
SQL> select count(*)
2 from t_addr_list tl , t_addr_group tg
3 where tl.group_id=tg.group_id
4 and tg.user_id=to_number('24885');

COUNT(*)
----------
1

已用时间: 00: 00: 05.06

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=6 Card=1 Bytes=678781)
1 0 SORT (AGGREGATE)
2 1 HASH JOIN (Cost=6 Card=819 Bytes=9828)
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'T_ADDR_GROUP' (Cost=
2 Card=2 Bytes=16)

4 3 INDEX (RANGE SCAN) OF 'IDX_GROUP_USERID' (NON-UNIQUE
) (Cost=1 Card=2)

5 2 INDEX (RANGE SCAN) OF 'IDX_ADDR_LIST_GROUPID' (NON-UNI
QUE) (Cost=4 Card=1688998 Bytes=41544)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
89991 consistent gets
16751 physical reads
0 redo size
403 bytes sent via SQL*Net to client
504 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
注意此处的几个地方46行的Optimizer=CHOOSE表示优化器模式设置为CHOOSE
如下面看到的参数optimizer_mode（85行）
48行显示此时两个表采用HASH JOIN 的连接方式
61行显示的consistent gets（一致读）很高（89991）
62行显示的physical reads（物理读）也很高

SQL> SHOW PARAMETER Optimizer

NAME TYPE VALUE
------------------------------------ ----------- -------------
optimizer_dynamic_sampling integer 1
optimizer_features_enable string 9.2.0
optimizer_index_caching integer 0
optimizer_index_cost_adj integer 100
optimizer_max_permutations integer 2000
optimizer_mode string CHOOSE

optimizer_mode设置为CHOOSE时表示如果表有统计信息，则数据库用基于成本（cost）
如果没有统计信息，则数据库用基于规则（rule）对sql生成执行计划，其可以设置的参数如下：
OPTIMIZER_MODE =
{first_rows_[1 | 10 | 100 | 1000] | first_rows | all_rows | choose | rule}
ORACLE 9i中默认为choose

四.查询所使用的表有无统计信息
SQL> select NUM_ROWS from user_tables where TABLE_NAME='T_ADDR_GROUP';

NUM_ROWS
----------

已用时间: 00: 00: 00.08
（说明如果表有统计信息则在数据库系统表中会存有表的相关信息，如上所看到的NUM_ROWS（行总数）
上面查询返回空表示次表没有统计信息,可以判断此时使用的是rule,修改执行计划可以对表生成统计
信息）

五.生成统计信息，分析表
分析的行数可以根据表的大小和所用时间选择，一般非常大的表选总行数的5%行就可以了
分析的行数越多越好，统计信息越准确
SQL>
SQL> analyze table t_addr_group estimate statistics sample 100000 rows;

表已分析。

已用时间: 00: 00: 08.04
SQL> analyze table t_addr_list estimate statistics sample 100000 rows;

表已分析。

已用时间: 00: 00: 38.09
SQL> select NUM_ROWS from user_tables where TABLE_NAME='T_ADDR_GROUP';

NUM_ROWS
----------
154416

已用时间: 00: 00: 00.09（此时表明已经有统计信息了）

六.再次查询语句检查
SQL>
SQL> select count(*)
2 from t_addr_list tl , t_addr_group tg
3 where tl.group_id=tg.group_id
4 and tg.user_id=to_number('24885');

COUNT(*)
----------
1

已用时间: 00: 00: 00.06

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=6 Card=1 Bytes=12)
1 0 SORT (AGGREGATE)
2 1 NESTED LOOPS (Cost=6 Card=819 Bytes=9828)
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'T_ADDR_GROUP' (Cost=
2 Card=2 Bytes=16)

4 3 INDEX (RANGE SCAN) OF 'IDX_GROUP_USERID' (NON-UNIQUE
) (Cost=1 Card=2)

5 2 INDEX (RANGE SCAN) OF 'IDX_ADDR_LIST_GROUPID' (NON-UNI
QUE) (Cost=2 Card=411 Bytes=1644)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
9 consistent gets
3 physical reads
0 redo size
403 bytes sent via SQL*Net to client
504 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

此时用时为00.06秒，表连接方式已经修改为NESTED LOOPS（嵌套循环连接）
consistent gets（一致性）和physical reads（物理读）数据已经变小

等待一定时间再次测试（以便数据从内存中移走）
SQL>
SQL>
SQL> select count(*)
2 from t_addr_list tl , t_addr_group tg
3 where tl.group_id=tg.group_id
4 and tg.user_id=to_number('25115');

COUNT(*)
----------
1

已用时间: 00: 00: 00.07

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=6 Card=1 Bytes=12)
1 0 SORT (AGGREGATE)
2 1 NESTED LOOPS (Cost=6 Card=819 Bytes=9828)
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'T_ADDR_GROUP' (Cost=
2 Card=2 Bytes=16)

4 3 INDEX (RANGE SCAN) OF 'IDX_GROUP_USERID' (NON-UNIQUE
) (Cost=1 Card=2)

5 2 INDEX (RANGE SCAN) OF 'IDX_ADDR_LIST_GROUPID' (NON-UNI
QUE) (Cost=2 Card=411 Bytes=1644)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
9 consistent gets
4 physical reads
0 redo size
403 bytes sent via SQL*Net to client
504 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

等待一定时间再次测试
SQL> select count(*)
2 from t_addr_list tl , t_addr_group tg
3 where tl.group_id=tg.group_id
4 and tg.user_id=to_number('32955');

COUNT(*)
----------
1

已用时间: 00: 00: 00.07

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=6 Card=1 Bytes=12)
1 0 SORT (AGGREGATE)
2 1 NESTED LOOPS (Cost=6 Card=819 Bytes=9828)
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'T_ADDR_GROUP' (Cost=
2 Card=2 Bytes=16)

4 3 INDEX (RANGE SCAN) OF 'IDX_GROUP_USERID' (NON-UNIQUE
) (Cost=1 Card=2)

5 2 INDEX (RANGE SCAN) OF 'IDX_ADDR_LIST_GROUPID' (NON-UNI
QUE) (Cost=2 Card=411 Bytes=1644)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
9 consistent gets
6 physical reads
0 redo size
403 bytes sent via SQL*Net to client
504 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL>
几次测试时间基本在00.07秒左右，问题基本解决
分析相关的语句
（
analyze table t_addr_list estimate statistics sample 100000 rows;
analyze table t_addr_group estimate statistics sample 100000 rows;

analyze table t_addr_list delete statistics;
analyze table t_addr_group delete statistics;

）