根据地址，找对应的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
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ōptimizer=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 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.再次查看执行计划：
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:00:03.00
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ōptimizer=CHOOSE (Cost=22 Card=1 Bytes=53)
1 0 SORT (AGGREGATE)
2 1 PARTITION RANGE (ALL)
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)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
16813 consistent gets
569 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
0 sorts (memory)
0 sorts (disk)
1 rows processed
这次走了索引后速度明显快多了，用了3秒钟就返回了结果。
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:00:24.73
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ōptimizer=CHOOSE (Cost=22 Card=1 Bytes=40)
1 0 SORT (AGGREGATE)
2 1 PARTITION RANGE (ALL)
3 2 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'LOCALUSAGE' (Cost=22 Card=3615 Bytes=144600)
4 3 INDEX (RANGE SCAN) OF 'I_LOCALUSAGE_CALLER' (NON-UNIQUE) (Cost=14 Card=65063)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
129336 consistent gets
7241 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语句走了索引，用了24秒钟返回结果，性能明显提高了很多。
