我在对数据库所有业务表 做完表分析后 有些繁琐点的语句一执行就死那了 结束session后也不释放 只能到服务器上 orakill 进程 到测试服务器上就没事 大哥大姐们有谁知道是什么原因吗
解决方案 »
- Oracle初学者请教存储过程哪里有问题
- 一个pl/sql问题
- 哪里能下载到ORACLE9.2.0.4 for linux的patch文件?
- oracle 查询语句 ,居然要10几秒 ,不能忍受 ,求各位指点
- 新手恳请各位大狭帮忙建表_________creat!
- 查询当前使用游标数的?使用select * from V$OPEN_CURSOR
- 为什么我卸载了oracle 9i,在“服务”(windows版)中还有OracleService项目呢?!
- 请问ORACLE里面的查询包含子字符串位置的函数是什么?就象SQL SERVER里的CHARINDEX()
- 请问如何关闭当前数据库,启动另外一个数据库
- 高分!!!!很急的问题,希望大家帮帮忙!!!
- Oracle同一个连接中取序列的问题
- 在oracle如何创建过程,用于查询商品状态
同时可以查看v$session_wait中的等待主要在卡什么。
然后select * from table(dbms_xplan.display);还有你把v$session_wait的内容全贴出来。不是看那个waited known time
21 10872 null event 1413697536 54435000 1 1 0 0 -1 1726 WAITED KNOWN TIME
33 399 null event 1413697536 54435000 1 1 0 0 -1 0 WAITED KNOWN TIME
46 24 null event 1413697536 54435000 1 1 0 0 -1 31 WAITED KNOWN TIME
1 9980 pmon timer duration 300 0000012C 0 0 0 0 0 1 WAITING
2 17782 rdbms ipc message timeout 300 0000012C 0 0 0 0 0 2 WAITING
3 22343 rdbms ipc message timeout 89 59 0 0 0 0 0 0 WAITING
6 20 rdbms ipc message timeout 180000 0002BF20 0 0 0 0 0 1654 WAITING
7 5738 rdbms ipc message timeout 500 000001F4 0 0 0 0 0 4 WAITING
4 22978 rdbms ipc message timeout 300 0000012C 0 0 0 0 0 2 WAITING
28 29205 enqueue name|mode 1415053316 54580004 id1 393262 0006002E id2 13742 000035AE 0 2 WAITING
5 286 smon timer sleep time 300 0000012C failed 0 0 0 0 0 183 WAITING
37 42247 library cache pin handle address 493631872 1D6C3980 pin address 490564728 1D3D6C78 100*mode+namespace 200 000000C8 0 4 WAITING
9 22913 SQL*Net message from client driver id 1413697536 54435000 #bytes 1 1 0 0 0 4005 WAITING
17 25268 SQL*Net message from client driver id 1413697536 54435000 #bytes 1 1 0 0 0 220 WAITING
30 39558 SQL*Net message from client driver id 1413697536 54435000 #bytes 1 1 0 0 0 42 WAITING
29 29518 SQL*Net message from client driver id 1413697536 54435000 #bytes 1 1 0 0 0 96 WAITING
27 39464 SQL*Net message from client driver id 1413697536 54435000 #bytes 1 1 0 0 0 22 WAITING
25 57804 SQL*Net message from client driver id 1413697536 54435000 #bytes 1 1 0 0 0 8 WAITING
24 32740 SQL*Net message from client driver id 1413697536 54435000 #bytes 1 1 0 0 0 4 WAITING
23 5064 SQL*Net message from client driver id 1413697536 54435000 #bytes 1 1 0 0 0 87 WAITING
20 17611 SQL*Net message from client driver id 1413697536 54435000 #bytes 1 1 0 0 0 9 WAITING
19 1288 SQL*Net message from client driver id 1413697536 54435000 #bytes 1 1 0 0 0 4005 WAITING
18 21993 SQL*Net message from client driver id 1413697536 54435000 #bytes 1 1 0 0 0 0 WAITING
54 27081 SQL*Net message from client driver id 1413697536 54435000 #bytes 1 1 0 0 0 23 WAITING
53 25771 SQL*Net message from client driver id 1413697536 54435000 #bytes 1 1 0 0 0 0 WAITING
51 36024 SQL*Net message from client driver id 1413697536 54435000 #bytes 1 1 0 0 0 16 WAITING
50 5484 SQL*Net message from client driver id 1413697536 54435000 #bytes 1 1 0 0 0 16 WAITING
49 30678 SQL*Net message from client driver id 1413697536 54435000 #bytes 1 1 0 0 0 2 WAITING
48 34693 SQL*Net message from client driver id 1413697536 54435000 #bytes 1 1 0 0 0 0 WAITING
47 2759 SQL*Net message from client driver id 1413697536 54435000 #bytes 1 1 0 0 0 26 WAITING
45 8444 SQL*Net message from client driver id 1413697536 54435000 #bytes 1 1 0 0 0 40 WAITING
44 4966 SQL*Net message from client driver id 1413697536 54435000 #bytes 1 1 0 0 0 55 WAITING
55 3372 SQL*Net message from client driver id 1413697536 54435000 #bytes 1 1 0 0 0 125 WAITING
43 63680 SQL*Net message from client driver id 1413697536 54435000 #bytes 1 1 0 0 0 0 WAITING
41 62564 SQL*Net message from client driver id 1413697536 54435000 #bytes 1 1 0 0 0 4 WAITING
40 50080 SQL*Net message from client driver id 1413697536 54435000 #bytes 1 1 0 0 0 0 WAITING
39 25739 SQL*Net message from client driver id 1413697536 54435000 #bytes 1 1 0 0 0 0 WAITING
35 30906 SQL*Net message from client driver id 1413697536 54435000 #bytes 1 1 0 0 0 17 WAITING
34 29536 SQL*Net message from client driver id 1413697536 54435000 #bytes 1 1 0 0 0 137 WAITING
32 586 SQL*Net message from client driver id 1413697536 54435000 #bytes 1 1 0 0 0 6 WAITING
31 62304 SQL*Net message from client driver id 1413697536 54435000 #bytes 1 1 0 0 0 55 WAITING
16 29455 SQL*Net message from client driver id 1413697536 54435000 #bytes 1 1 0 0 0 9 WAITING
10 18139 SQL*Net message from client driver id 1413697536 54435000 #bytes 1 1 0 0 0 0 WAITING
13 28672 SQL*Net message from client driver id 1413697536 54435000 #bytes 1 1 0 0 0 0 WAITING
14 312 SQL*Net message from client driver id 1413697536 54435000 #bytes 1 1 0 0 0 296 WAITING
15 407 SQL*Net message from client driver id 1413697536 54435000 #bytes 1 1 0 0 0 45 WAITING
12 5165 SQL*Net message from client driver id 1413697536 54435000 #bytes 1 1 0 0 0 58 WAITING
8 1970 wakeup time manager 0 0 0 0 0 0 0 15 WAITING
46里面WAIT_TIME已经是-1了,已经没等待了。
反而是SID37已经等待了4秒钟了。
为什么有library cache pin等待。是不是对表做过DDL,然后分析时导致某些相关的对象无效了?
46 的 dbms_xplan.display
note:cpu costing is off 什么意思呢
--------------------------------------------------------------------------------
------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 22792 | 375 |
|* 1 | HASH JOIN | | 7 | 22792 | 375 |
|* 2 | HASH JOIN SEMI | | 7 | 1659 | 119 |
|* 3 | HASH JOIN | | 672 | 147K| 100 |
|* 4 | TABLE ACCESS FULL | BD_SBTZ_GG | 1928 | 129K| 76 |
| 5 | TABLE ACCESS FULL | PJ_BDSBZTPJJG | 18542 | 2806K| 15 |
| 6 | VIEW | VW_NSO_1 | 185 | 2405 | 17 |
| 7 | SORT GROUP BY | | 185 | 7215 | 17 |
|* 8 | TABLE ACCESS FULL| PJ_BDSBZTPJJG | 185 | 7215 | 15 |
| 9 | VIEW | | 3292 | 9705K| 254 |
| 10 | SORT GROUP BY | | 3292 | 55964 | 254 |
|* 11 | TABLE ACCESS FULL | BD_SBSXZ | 3393 | 57681 | 241 |
------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
1 - access("A"."BH"="B"."SSSB")
2 - access("PJ_BDSBZTPJJG"."BH"="VW_NSO_1"."$nso_col_1")
3 - access("A"."BH"="PJ_BDSBZTPJJG"."SBID")
4 - filter("A"."JLZT">'已删除' AND "A"."JLZT"<'已删除' OR "A"."JLZT" IS
NULL AND "A"."SBLX"=42 AND "A"."GDGS"='长春供电公司')
8 - filter("PJ_BDSBZTPJJG"."SBLXID"=42)
11 - filter("BD_SBSXZ"."SBSX"=139 OR "BD_SBSXZ"."SBSX"=186 OR "BD_S
BSXZ"."SBSX"=187)
Note: cpu costing is off这是46的 session37的 是程序里执行的语句 具体我也不知道是那条
我没表分析的时候 没有过 分析完了 才这样的 重分析 或者把分析delete了 是不是就能好了呢?
测试服务器 我就分析了几个表 没全分析 就没事 很是迷糊啊
where status='INVALID';
还是不知道具体原因啊
还是谢谢你vc555
结贴了 有空在研究吧 @_@!!!