发现对localusage表做了全表扫描，什么记录也没有返回居然用了2分多钟。

SQL> select nvl(sum(LOCALCHARGE),0) from LOCALUSAGE where to_char(ENDTIME,''''YYYYMMDD'''')=20040816 and caller like ''''0543888%'''';

NVL(SUM(LOCALCHARGE),0)
-----------------------
                   27.6

Elapsed: 00:03:56.46

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=13435 Card=1 Bytes=4
          0)

   1    0   SORT (AGGREGATE)
   2    1     PARTITION RANGE (ALL)
   3    2       TABLE ACCESS (FULL) OF ''''LOCALUSAGE'''' (Cost=13435 Card=3
          615 Bytes=144600)

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

这个SQL语句有结果返回，发现也是对localusage表做了全表扫描，但速度也很慢，用了3分多钟。

SQL> select count(*) from localusage;

  COUNT(*)
----------
   5793776

该表有579万多条记录，数据量很大，全表扫描已经不再适合。

5.检查该表的类型：

SQL> SELECT INDEX_NAME, TABLE_NAME, STATUS, PARTITIONED FROM USER_INDEXES WHERE TABLE_NAME=''''LOCALUSAGE'''';

INDEX_NAME                     TABLE_NAME                     STATUS   PAR
------------------------------ ------------------------------ -------- ---
I_LOCALUSAGE_SID               LOCALUSAGE                     N/A      YES
UI_LOCALUSAGE_ST_SEQ           LOCALUSAGE                     N/A      YES

SQL> SELECT index_name,table_name,locality FROM user_part_indexes where table_name=''''LOCALUSAGE'''';

INDEX_NAME                     TABLE_NAME                     LOCALI
------------------------------ ------------------------------ ------
I_LOCALUSAGE_SID               LOCALUSAGE                     LOCAL
UI_LOCALUSAGE_ST_SEQ           LOCALUSAGE                     LOCAL

发现该表是分区表，并在SERVICEID,STARTIME和CDRSEQUENCE列上建立了分区索引，索引类型是local索引。

6.查看分区索引的索引键值:

SQL> select INDEX_NAME,COLUMN_NAME,INDEX_OWNER from dba_ind_columns where TABLE_NAME=''''LOCALUSAGE'''';

INDEX_NAME           COLUMN_NAME          INDEX_OWNER
-------------------- -------------------- ------------------------------
I_LOCALUSAGE_SID     SERVICEID            WACOS
UI_LOCALUSAGE_ST_SEQ STARTTIME            WACOS
UI_LOCALUSAGE_ST_SEQ CDRSEQUENCE          WACOS

发现在endtime和caller列上都没有建立索引，这也是导致SQL语句做全表扫描的最终原因。

7.决定创建新的分区索引以消除全表扫描：

(1).首先查看localusage表分区情况：

SQL> select PARTITION_NAME,tablespace_name from user_tab_partitions where table_name=''''LOCALUSAGE'''';

PARTITION_NAME                 TABLESPACE_NAME
------------------------------ ------------------------------
LOCALUSAGE_200312              WACOS
LOCALUSAGE_200401              WACOS
LOCALUSAGE_200402              WACOS
LOCALUSAGE_200404              WACOS
LOCALUSAGE_200405              WACOS
LOCALUSAGE_200406              WACOS
LOCALUSAGE_200407              WACOS
LOCALUSAGE_200409              WACOS
LOCALUSAGE_200410              WACOS
LOCALUSAGE_200411              WACOS
LOCALUSAGE_200403              WACOS
LOCALUSAGE_200408              WACOS
LOCALUSAGE_200412              WACOS

13 rows selected.

(2).在caller列上创建local分区索引：
SQL>set timing on
SQL>create index I_LOCALUSAGE_CALLER on localusage(caller)
LOCAL
(      
        PARTITION LOCALUSAGE_200312,
 PARTITION LOCALUSAGE_200401,
 PARTITION LOCALUSAGE_200402,
 PARTITION LOCALUSAGE_200404,
 PARTITION LOCALUSAGE_200405,
 PARTITION LOCALUSAGE_200406,
 PARTITION LOCALUSAGE_200407,
 PARTITION LOCALUSAGE_200409,
 PARTITION LOCALUSAGE_200410,
 PARTITION LOCALUSAGE_200411,
 PARTITION LOCALUSAGE_200403,
 PARTITION

上一页  [1] [2] [3]  下一页