分區表索引

根據位址，找對應的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秒鐘傳回結果，性能明顯提高了很多。