表分析后 查询僵死 什么原因啊 我在对数据库所有业务表 做完表分析后 有些繁琐点的语句一执行就死那了 结束session后也不释放 只能到服务器上 orakill 进程 到测试服务器上就没事 大哥大姐们有谁知道是什么原因吗 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 可能是作分析后,使用了不当的执行计划,所以作繁琐点的SQL就卡住。你可以trace一下,看看是否用了错误的执行计划。同时可以查看v$session_wait中的等待主要在卡什么。 trace不行啊 执行不完 卡在哪里不动 看不到trace waited known timebw555 表初始化 是怎么个搞法?? 你explain plan for 执行的sql然后select * from table(dbms_xplan.display);还有你把v$session_wait的内容全贴出来。不是看那个waited known time SID SEQ# EVENT P1TEXT P1 P1RAW P2TEXT P2 P2RAW P3TEXT P3 P3RAW WAIT_TIME SECONDS_IN_WAIT STATE21 10872 null event 1413697536 54435000 1 1 0 0 -1 1726 WAITED KNOWN TIME33 399 null event 1413697536 54435000 1 1 0 0 -1 0 WAITED KNOWN TIME46 24 null event 1413697536 54435000 1 1 0 0 -1 31 WAITED KNOWN TIME1 9980 pmon timer duration 300 0000012C 0 0 0 0 0 1 WAITING2 17782 rdbms ipc message timeout 300 0000012C 0 0 0 0 0 2 WAITING3 22343 rdbms ipc message timeout 89 59 0 0 0 0 0 0 WAITING6 20 rdbms ipc message timeout 180000 0002BF20 0 0 0 0 0 1654 WAITING7 5738 rdbms ipc message timeout 500 000001F4 0 0 0 0 0 4 WAITING4 22978 rdbms ipc message timeout 300 0000012C 0 0 0 0 0 2 WAITING28 29205 enqueue name|mode 1415053316 54580004 id1 393262 0006002E id2 13742 000035AE 0 2 WAITING5 286 smon timer sleep time 300 0000012C failed 0 0 0 0 0 183 WAITING37 42247 library cache pin handle address 493631872 1D6C3980 pin address 490564728 1D3D6C78 100*mode+namespace 200 000000C8 0 4 WAITING9 22913 SQL*Net message from client driver id 1413697536 54435000 #bytes 1 1 0 0 0 4005 WAITING17 25268 SQL*Net message from client driver id 1413697536 54435000 #bytes 1 1 0 0 0 220 WAITING30 39558 SQL*Net message from client driver id 1413697536 54435000 #bytes 1 1 0 0 0 42 WAITING29 29518 SQL*Net message from client driver id 1413697536 54435000 #bytes 1 1 0 0 0 96 WAITING27 39464 SQL*Net message from client driver id 1413697536 54435000 #bytes 1 1 0 0 0 22 WAITING25 57804 SQL*Net message from client driver id 1413697536 54435000 #bytes 1 1 0 0 0 8 WAITING24 32740 SQL*Net message from client driver id 1413697536 54435000 #bytes 1 1 0 0 0 4 WAITING23 5064 SQL*Net message from client driver id 1413697536 54435000 #bytes 1 1 0 0 0 87 WAITING20 17611 SQL*Net message from client driver id 1413697536 54435000 #bytes 1 1 0 0 0 9 WAITING19 1288 SQL*Net message from client driver id 1413697536 54435000 #bytes 1 1 0 0 0 4005 WAITING18 21993 SQL*Net message from client driver id 1413697536 54435000 #bytes 1 1 0 0 0 0 WAITING54 27081 SQL*Net message from client driver id 1413697536 54435000 #bytes 1 1 0 0 0 23 WAITING53 25771 SQL*Net message from client driver id 1413697536 54435000 #bytes 1 1 0 0 0 0 WAITING51 36024 SQL*Net message from client driver id 1413697536 54435000 #bytes 1 1 0 0 0 16 WAITING50 5484 SQL*Net message from client driver id 1413697536 54435000 #bytes 1 1 0 0 0 16 WAITING49 30678 SQL*Net message from client driver id 1413697536 54435000 #bytes 1 1 0 0 0 2 WAITING48 34693 SQL*Net message from client driver id 1413697536 54435000 #bytes 1 1 0 0 0 0 WAITING47 2759 SQL*Net message from client driver id 1413697536 54435000 #bytes 1 1 0 0 0 26 WAITING45 8444 SQL*Net message from client driver id 1413697536 54435000 #bytes 1 1 0 0 0 40 WAITING44 4966 SQL*Net message from client driver id 1413697536 54435000 #bytes 1 1 0 0 0 55 WAITING55 3372 SQL*Net message from client driver id 1413697536 54435000 #bytes 1 1 0 0 0 125 WAITING43 63680 SQL*Net message from client driver id 1413697536 54435000 #bytes 1 1 0 0 0 0 WAITING41 62564 SQL*Net message from client driver id 1413697536 54435000 #bytes 1 1 0 0 0 4 WAITING40 50080 SQL*Net message from client driver id 1413697536 54435000 #bytes 1 1 0 0 0 0 WAITING39 25739 SQL*Net message from client driver id 1413697536 54435000 #bytes 1 1 0 0 0 0 WAITING35 30906 SQL*Net message from client driver id 1413697536 54435000 #bytes 1 1 0 0 0 17 WAITING34 29536 SQL*Net message from client driver id 1413697536 54435000 #bytes 1 1 0 0 0 137 WAITING32 586 SQL*Net message from client driver id 1413697536 54435000 #bytes 1 1 0 0 0 6 WAITING31 62304 SQL*Net message from client driver id 1413697536 54435000 #bytes 1 1 0 0 0 55 WAITING16 29455 SQL*Net message from client driver id 1413697536 54435000 #bytes 1 1 0 0 0 9 WAITING10 18139 SQL*Net message from client driver id 1413697536 54435000 #bytes 1 1 0 0 0 0 WAITING13 28672 SQL*Net message from client driver id 1413697536 54435000 #bytes 1 1 0 0 0 0 WAITING14 312 SQL*Net message from client driver id 1413697536 54435000 #bytes 1 1 0 0 0 296 WAITING15 407 SQL*Net message from client driver id 1413697536 54435000 #bytes 1 1 0 0 0 45 WAITING12 5165 SQL*Net message from client driver id 1413697536 54435000 #bytes 1 1 0 0 0 58 WAITING8 1970 wakeup time manager 0 0 0 0 0 0 0 15 WAITING 恩。你查询v$session_wait的时候那个sql还卡住的吧?确定是sid=46的会话卡住的?46里面WAIT_TIME已经是-1了,已经没等待了。反而是SID37已经等待了4秒钟了。 前面trace出来了吗?你再把37正在执行的sql也trace出来。为什么有library cache pin等待。是不是对表做过DDL,然后分析时导致某些相关的对象无效了? 恩 必卡 我自己刚写的统计语句46 的 dbms_xplan.display note:cpu costing is off 什么意思呢 PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------------------------------------------------------------------------------| 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的 是程序里执行的语句 具体我也不知道是那条 这个问题 是我上一次对所有程序用到的表 做dbms_stats.gather_table_stats后才出现的 你看执行计划全是TABLE ACCESS FULL了。 只执行这个sql才卡吗?全表扫描慢,慢的久了不就是卡了。 同样的效果 有几个语句都是这样的 都在程序里 现在没时间找这种语句 只能是 过段时间 看看数据库 有这样的session 就到服务器 orakill我没表分析的时候 没有过 分析完了 才这样的 重分析 或者把分析delete了 是不是就能好了呢?测试服务器 我就分析了几个表 没全分析 就没事 很是迷糊啊 测试服务器上 同样的语句 0.5秒就查出来了 到正式的上面 一执行就卡 准准的 而且只能orakill进程 你的hash_area_size设置的够用吗? hash_area_size=1m 够了吧 select * from dba_objectswhere status='INVALID'; 我把 所有表都dbms_stats.delete_table_stats 了 现在那条语句就好使了 执行时间0.313秒还是不知道具体原因啊还是谢谢你vc555结贴了 有空在研究吧 @_@!!! 走全表扫描时有时候会很久的,试过一次执行一个sql三小时没出数据 后来强制指定了索引 五分钟跑完 Oracle监听程序配置 oracle中的公共同义词 数据库装在本地和远程,存储检索效率哪个高? 求助关于replace语句的应用 一个带输入和输出参数的存储过程如何执行 高分,在线等,现结.在Oracle里对两个64位的整数求与,怎么办? 如何改写这条语句? 请教关于ORACLE表锁定的问题!谢谢 [紧急问题]关于重复录入master表数据的问题送分送分!!!!!!!! linux装的oralce11g,表空间迁移修改地址报错 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
结贴了 有空在研究吧 @_@!!!