分区表索引

根据地址，找对应的sql语句

v$sqlarea   v$sqltext   v$sql

top。pid==v$process.spid--->v$process.addr

v$process.addr==v$session.paddr--->v$session.sql_address

找到sql_address后便可kill session或查看对应sql语句

1，kill session

 alter system kill session 'sid,serial#';

 其中sid,serial#都在v$session视图中，kill完status如果标为killed 则可反向找出pid 从系统kill -9 $pid

2. 查看对应的sql语句

v$session.sql_address==v$sqlarea.address---->v$sqlarea.sql_text

v$session.sql_address==v$sqltext.address---->v$sqlarea.sql_text

v$session.sql_address==v$sql.address---->v$sqlarea.sql_text

pid查对应的sql

SELECT sqlarea.SQL_TEXT

FROM V$session sess,V$process pro,V$sqlarea sqlarea

WHERE pro.SPID=5296

AND sess.PADDR = pro.ADDR

AND sess.SQL_ADDRESS = sqlarea.ADDRESS

4.使用相关用户连接到数据库，检查其执行计划:

SQL>connect wacos/oss

Connected.

SQL>@?/rdbms/admin/utlxplan.sql

Table created.

SQL>set autotrace on

SQL>set timing on

SQL>Select nvl(sum(LOCALCHARGE),0),nvl(sum(usage),0) from LOCALUSAGE where to_char(ENDTIME,'YYYYMMDD')=20041016

and LOCALCHARGE>0 and caller like '0543886%';

NVL(SUM(LOCALCHARGE),0) NVL(SUM(USAGE),0)

----------------------- -----------------

                      0                 0

Elapsed: 00:02:56.37

Execution Plan

----------------------------------------------------------

   0      SELECT STATEMENT ōptimizer=CHOOSE (Cost=13435 Card=1 Bytes=5

          3)

   1    0   SORT (AGGREGATE)

   2    1     PARTITION RANGE (ALL)

   3    2       TABLE ACCESS (FULL) OF 'LOCALUSAGE' (Cost=13435 Card=1

          81 Bytes=9593)

Statistics

        258 recursive calls

          0 db block gets

      88739 consistent gets

      15705 physical reads

          0 redo size

        580 bytes sent via SQL*Net to client

        651 bytes received via SQL*Net from client

          2 SQL*Net roundtrips to/from client

          8 sorts (memory)

          0 sorts (disk)

          1 rows 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

   0      SELECT STATEMENT ōptimizer=CHOOSE (Cost=13435 Card=1 Bytes=4

          0)

   3    2       TABLE ACCESS (FULL) OF 'LOCALUSAGE' (Cost=13435 Card=3

          615 Bytes=144600)

          0 recursive calls

      88588 consistent gets

      15615 physical reads

        507 bytes sent via SQL*Net to client

          0 sorts (memory)

这个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>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 LOCALUSAGE_200408,

PARTITION LOCALUSAGE_200412

)

TABLESPACE wacos

STORAGE(

INITIAL 6553600

NEXT 6553600

MAXEXTENTS unlimited

PCTINCREASE 0)

PCTFREE 5

NOLOGGING;

Index created.

Elapsed: 00:06:27.90 (由于数据量比较大，耗时6分钟）

8.再次查看执行计划：

Elapsed: 00:00:03.00

   0      SELECT STATEMENT ōptimizer=CHOOSE (Cost=22 Card=1 Bytes=53)

   3    2       TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'LOCALUSAGE' (Cost=22 Card=181 Bytes=9593)

   4    3         INDEX (RANGE SCAN) OF 'I_LOCALUSAGE_CALLER' (NON-UNIQUE) (Cost=14 Card=65063)

      16813 consistent gets

        569 physical reads

这次走了索引后速度明显快多了，用了3秒钟就返回了结果。

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

Elapsed: 00:00:24.73

   0      SELECT STATEMENT ōptimizer=CHOOSE (Cost=22 Card=1 Bytes=40)

   3    2       TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'LOCALUSAGE' (Cost=22 Card=3615 Bytes=144600)

     129336 consistent gets

       7241 physical reads

这个SQL语句走了索引，用了24秒钟返回结果，性能明显提高了很多。