这几天看了几篇关于捕获高cpu语句的文章,按照里边的方法测试了一下我们的软件,但是由于自
己知识浅薄没有发现什么问题,现在贴出来请大家指点一下.
1 查看会话数.
SQL> select sid,serial# from v$session where username='PMV4';
SID SERIAL#
---------- ----------
113 20
114 13
115 58
116 105 24746 22601
117 840 24533
118 52
119 506
120 325
122 365
123 795
124 728
SID SERIAL#
---------- ----------
125 1605
126 24
127 291
128 34
129 14
130 349
131 195
132 10716
133 4137
136 3753
140 2321
SID SERIAL#
---------- ----------
141 36
153 50
158 34
我只打开了三个客户端, 而且未做任何操作,会话达到25个,这是不是有问题呢?[B]
2 top查看高cpu进程
注意到pid=24598
利用eygle提供的sql捕获脚本得到:
SQL_TEXT
----------------------------------------------------------------
select volumenode0_.id as id,
volumenode0_.volumeName as volumeName51_,
volumenode0_.volumeID as volumeID51_,
..........
..........
from PMV4.tblVolumeNode volumenode0_
where volumenode0_.id in (544 , 585 , 1357 , 1367 , 1369 , 56);
怀疑PMV4.tblVolumeNode没有索引,查询一下:
SQL>select index_name,index_type,table_name from user_indexes
where table_name='PMV4.tblvolumenode'
结果是有的.(由于当时没记录下来,所以没办法贴出来).
查看一下执行计划,结果如下:
--------------
| Id | Operation | Name | Rows | Bytes | Cost
U)| Time |
---------------------------------------------------------------------------
--------------
| 0 | SELECT STATEMENT | | 6 | 1002 |
0)| 00:00:01 |
| 1 | INLIST ITERATOR | | | |
| |
| 2 | TABLE ACCESS BY INDEX ROWID| TBLVOLUMENODE | 6 | 1002 |
0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | SYS_C005630 | 6 | |
0)| 00:00:01 |
---------------------------------------------------------------------------
--------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("VOLUMENODE0_"."ID"=56 OR "VOLUMENODE0_"."ID"=544 OR
"VOLUMENODE0_"."ID"=585 OR "VOLUMENODE0_"."ID"=1357 OR "VOLUM
0_"."ID"=1367 OR
"VOLUMENODE0_"."ID"=1369)
????
----------------------------------------------------------
1 recursive calls
0 db block gets
19 consistent gets
0 physical reads
0 redo size
3424 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
6 rows processed
也是通过索引得到数据.
请问我以上做的分析步骤有什么疏漏吗?
以上信息能看出为什么这只有4万行数据的表却要2分钟的查询时间?