今天帮助看了一个哥们的数据库,帮他抓了一下等待事件,刚好有一个sql在等待事件中,顺便看看
监控等待事件
select
a.SID,
a.EVENT,
b.OSUSER,
b.username,
b.MACHINE,
b.PROGRAM,
b.MODULE,
b.SQL_ID,
b.sql_id
from v$session_wait a, v$session b
where a.sid = b.sid
and a.sid in (select sid
from v$session_wait
where event not like 'SQL%'
and event not like '%message%'
and event not like 'Streams AQ%'
and event not in ('jobq slave wait',
'class slave wait',
'DIAG idle wait',
'pmon timer',
'ASM background timer',
'smon timer'))
select * from v$sql where sql_id='sql_id';
有问题的sql如下
SELECT count(*) AS rown
FROM (
select t.KSGCXH,t.LSH,t.KSKM,t.KSXM,t.KSXH,t.XMKSXH,t.ZP,
t.CS,to_char(t.ZPSJ,'yyyy-mm-dd hh24:mi:ss') ZPSJ,
to_char(t.XTZPSJ,'yyyy-mm-dd hh24:mi:ss') XTZPSJ,
t.CQBJ,t.JYW,t.FZJG,
dbms_lob.getlength(zp) zplen
from MON_ADMIN.DRV_MON_EXAM_PHOTO t
where t.Fzjg='辽F'
and zp is not null
and dbms_lob.getlength(zp)<204800
and t.cqbj=''
and t.Xtzpsj>to_date('2015-09-17 10:38:22','yyyy-mm-dd hh24:mi:ss')
and t.Xtzpsj<=to_date('2015-09-17 11:08:11','yyyy-mm-dd hh24:mi:ss')) pagetable
这个sql超级简单,就是简单的查询,连个多表连接都没有,运行4s,返回7行数据
那问题一看就是没有建立索引,并且全表扫描了
查看索引信息,发现现有的索引跟他鸟关系都没有
select * from dba_indexes where owner='MON_ADMIN' and table_name='DRV_MON_EXAM_PHOTO';
select t.*,i.index_type from dba_ind_columns t,dba_indexes i where t.index_name = i.index_name and
owner='MON_ADMIN' and t.table_name='DRV_MON_EXAM_PHOTO';
查看一下这个表的执行计划
SQL> select * from table(dbms_xplan.display);PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2462891873-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 101 | 29460 (1)| 00:05:54 |
| 1 | SORT AGGREGATE | | 1 | 101 | | |
|* 2 | TABLE ACCESS FULL| DRV_MON_EXAM_PHOTO | 1 | 101 | 29460 (1)| 00:05:54 |
-----------------------------------------------------------------------------------------Predicate Information (identified by operation id):
--------------------------------------------------- 2 - filter("T"."XTZPSJ">TO_DATE(' 2015-09-17 10:38:22', 'syyyy-mm-dd
hh24:mi:ss') AND "T"."FZJG"='辽F' AND "DBMS_LOB"."GETLENGTH"("ZP")<204800 AND
"T"."CQBJ"='' AND "T"."XTZPSJ"<=TO_DATE(' 2015-09-17 11:08:11', 'syyyy-mm-dd
hh24:mi:ss'))已选择17行。
果然走了全表扫描,查询这个表的总行数有5017932行,查询体积为896M
结果集返回7条数据,那么7/5017932约等于0,相当于全表扫描每一行数据才能找到这七条数据
我们知道全表扫描是多快读,读取1M的数据大概15ms,全读完大概13s,现在用时4s,看来还是很快的哦。哈哈哈
现在就是建立索引就可以解决问题
create index IDX_DRV_MON_EXAM_PHOTO3 on MON_ADMIN.DRV_MON_EXAM_PHOTO(Fzjg,cqbj,Xtzpsj,KSGCXH,LSH,KSKM,KSXM,KSXH,XMKSXH,CS,ZPSJ,JYW);
创建完索引之后再看执行计划
SQL> select * from table(dbms_xplan.display);PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2782271437--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 101 | 4 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 101 | | |
|* 2 | TABLE ACCESS BY INDEX ROWID| DRV_MON_EXAM_PHOTO | 1 | 101 | 4 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IDX_DRV_MON_EXAM_PHOTO3 | 1 | | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
--------------------------------------------------- 2 - filter("DBMS_LOB"."GETLENGTH"("ZP")<204800)
3 - access("T"."FZJG"='辽F' AND "T"."CQBJ"='' AND "T"."XTZPSJ">TO_DATE(' 2015-09-17
10:38:22', 'syyyy-mm-dd hh24:mi:ss') AND "T"."XTZPSJ"<=TO_DATE(' 2015-09-17 11:08:11',
'syyyy-mm-dd hh24:mi:ss'))已选择18行。
执行计划变成了索引范围扫描
运行时间有4s编程现在的0.03s,绝对秒杀
对于这种单表运行速度慢,主要看有没有建立索引。