| s processed
发现对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] 下一页 | 
注：本站部分文章源于互联网，版权归原作者所有！如有侵权，请原作者与本站联系，本站将立即删除！ 本站文章除特别注明外均可转载，但需注明出处！ [MinTao学以致用网] 