专治Oracle疑难杂症

生成执行计划
EXPLAIN PLAN
  SET STATEMENT_ID = 'st1' FOR

.........................................................

SQL> SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY(null,'st1','basic'));
;

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------
Plan hash value: 3156184124

--------------------------------------------------------
| Id  | Operation                    | Name            |
--------------------------------------------------------
|   0 | SELECT STATEMENT             |                 |
|   1 |  SORT GROUP BY               |                 |
|   2 |   TABLE ACCESS BY INDEX ROWID| PS_OPEN_ITEM_GL |
|   3 |    NESTED LOOPS              |                 |
|   4 |     TABLE ACCESS FULL        | PS_GL_OI_TMP003 |
|   5 |     INDEX RANGE SCAN         | PSAOPEN_ITEM_GL |
--------------------------------------------------------

已选择12行。

检查当前的统计信息，发现是19日的。
alter session set nls_date_format = 'dd.mm.yyyy hh24:mi:ss';
SELECT last_analyzed from user_tables where table_name='PS_OPEN_ITEM_GL';
LAST_ANALYZED
-------------------
19.05.2010 06:24:03
创建一个表备份19日的统计信息
 EXEC DBMS_STATS.CREATE_STAT_TABLE(user, STATTAB=>'STAT_TABLE_19');
 EXEC DBMS_STATS.EXPORT_TABLE_STATS(user, tabname=>'PS_OPEN_ITEM_GL', stattab=>'STAT_TABLE_19');

SQL> select dbms_stats.get_stats_history_availability from dual;

GET_STATS_HISTORY_AVAILABILITY
---------------------------------------------------------------------------
23-4月 -10 11.10.50.961842000 下午 +08:00

    select TABLE_NAME, STATS_UPDATE_TIME from user_tab_stats_history where table_name='PS_OPEN_ITEM_GL';
    SQL> select TABLE_NAME, STATS_UPDATE_TIME from user_tab_stats_history where tabl
e_name='PS_OPEN_ITEM_GL';

TABLE_NAME
------------------------------------------------------------
STATS_UPDATE_TIME
---------------------------------------------------------------------------
PS_OPEN_ITEM_GL
24-4月 -10 06.20.54.478324 上午 +08:00

PS_OPEN_ITEM_GL
25-4月 -10 06.21.10.914586 上午 +08:00
......后面略

恢复14日的统计信息
set nls_lang=american_america.zhs16gbk
execute dbms_stats.restore_table_stats (user,'PS_OPEN_ITEM_GL','14-MAY-10 11.00.00.000000 AM +08:00');

SELECT last_analyzed from user_tables where table_name='PS_OPEN_ITEM_GL';

SQL> alter session set nls_date_format = 'dd.mm.yyyy hh24:mi:ss';

Session altered.

SQL> SELECT last_analyzed from user_tables where table_name='PS_OPEN_ITEM_GL';

LAST_ANALYZED
-------------------
14.05.2010 06:22:47

查询统计信息中的记录条数和实际的记录条数
SQL> SELECT NUM_ROWS FROM USER_TABLES WHERE TABLE_NAME='PS_OPEN_ITEM_GL';

  NUM_ROWS
----------
   1754105

SQL> SELECT COUNT(*) FROM PS_OPEN_ITEM_GL;

  COUNT(*)
----------
   1780199
  
  
   EXPLAIN PLAN
  SET STATEMENT_ID = 'st_19' FOR

...........................................

SQL> SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY(null,'st_19','basic'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------
--------------------
Plan hash value: 1864191602

--------------------------------------------------------
| Id  | Operation                    | Name            |
--------------------------------------------------------
|   0 | SELECT STATEMENT             |                 |
|   1 |  SORT GROUP BY               |                 |
|   2 |   TABLE ACCESS BY INDEX ROWID| PS_OPEN_ITEM_GL |
|   3 |    NESTED LOOPS              |                 |
|   4 |     TABLE ACCESS FULL        | PS_GL_OI_TMP003 |
|   5 |     INDEX RANGE SCAN         | PSBOPEN_ITEM_GL |
--------------------------------------------------------

12 rows selected.

19日用的是PSAOPEN_ITEM_GL索引，14日用的是PSBOPEN_ITEM_GL 索引

备份一个14日的统计信息
EXEC DBMS_STATS.CREATE_STAT_TABLE(user, STATTAB=>'STAT_TABLE_14');
 EXEC DBMS_STATS.EXPORT_TABLE_STATS(user, tabname=>'PS_OPEN_ITEM_GL', stattab=>'STAT_TABLE_14');

恢复19日的统计信息：
SQL> EXEC DBMS_STATS.IMPORT_TABLE_STATS(user, tabname=>'PS_OPEN_ITEM_GL', statta
b=>'STAT_TABLE_19');

PL/SQL procedure successfully completed.

SQL> SELECT NUM_ROWS FROM USER_TABLES WHERE TABLE_NAME='PS_OPEN_ITEM_GL';

  NUM_ROWS
----------
   1771495

SQL> SELECT last_analyzed from user_tables where table_name='PS_OPEN_ITEM_GL';

LAST_ANALYZED
-------------------
19.05.2010 06:24:03

两个索引的定义如下：
SQL> select dbms_metadata.get_ddl('INDEX','PSAOPEN_ITEM_GL') FROM DUAL;

DBMS_METADATA.GET_DDL('INDEX',
--------------------------------------------------------------------------------

  CREATE INDEX "SYSADM"."PSAOPEN_ITEM_GL" ON "SYSADM"."PS_OPEN_ITEM_GL" ("ACCOUN
T", "OPEN_ITEM_KEY")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 40960 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "PSINDEX2"

SQL> select dbms_metadata.get_ddl('INDEX','PSBOPEN_ITEM_GL') FROM DUAL;

DBMS_METADATA.GET_DDL('INDEX',
--------------------------------------------------------------------------------

  CREATE INDEX "SYSADM"."PSBOPEN_ITEM_GL" ON "SYSADM"."PS_OPEN_ITEM_GL" ("OPEN_I
TEM_KEY", "DEPTID",
CCOUNT")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 40960 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "PSINDEX2"

解决方案：
可以在sql中增加一个hint指定使用PSBOPEN_ITEM_GL 索引
可以建一个outline指定用这个索引
将14日的统计信息锁定，以后不收集了。