Oracle数据库数据恢复、性能优化来问问AskMaclean - ParnassusData诗檀软件旗下网站

找回密码
注册
搜索
热搜: 活动 交友 discuz
发新帖

42

积分

0

好友

0

主题
发表于 2012-1-4 17:07:04 | 查看: 12239| 回复: 8
环境 linux +rac
连接工具plsql 8.0.1

在连接数据库之后,执行select * from v$session 一直处于查询状态,出不来结果
在sqlplus 下执行正常.这是为啥

PS:部分表(用户创建的表)是这种情况,但也有一部分是正常.

附件为10046 trace

[ 本帖最后由 popowin 于 2012-1-4 17:43 编辑 ]

racdb2_ora_9402.rar

1.48 KB, 下载次数: 1930

发表于 2012-1-4 17:12:56
plsql 是指PL/SQL Developer?

"在连接数据库之后,执行select * from v$session 一直处于查询状态,出不来结果"

请对PL/SQL Developer的session 做一个10046 or sql trace 并上传结果
发表于 2012-1-4 19:04:33
ODM Data
  1. /oracle/admin/racdb/udump/racdb2_ora_9402.trc
  2. Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
  3. With the Partitioning, Real Application Clusters, OLAP, Data Mining
  4. and Real Application Testing options
  5. ORACLE_HOME = /oracle/ora10g
  6. System name:    Linux
  7. Node name:    oracle-2
  8. Release:    2.6.18-194.el5
  9. Version:    #1 SMP Tue Mar 16 21:52:39 EDT 2010
  10. Machine:    x86_64
  11. Instance name: racdb2
  12. Redo thread mounted by this instance: 2
  13. Oracle process number: 56
  14. Unix process pid: 9402, image: oracle@oracle-2

  15. *** ACTION NAME:(Command Window - New) 2012-01-04 17:40:15.176
  16. *** MODULE NAME:(PL/SQL Developer) 2012-01-04 17:40:15.176
  17. *** SERVICE NAME:(racdb) 2012-01-04 17:40:15.176
  18. *** SESSION ID:(1053.2913) 2012-01-04 17:40:15.176
  19. WAIT #1: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1294599624196111
  20. WAIT #1: nam='SQL*Net message from client' ela= 14278 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1294599624210695
  21. =====================
  22. PARSING IN CURSOR #2 len=61 dep=0 uid=63 oct=47 lid=63 tim=1294599624210792 hv=356401299 ad='59bee200'
  23. begin :id := sys.dbms_transaction.local_transaction_id; end;
  24. END OF STMT
  25. PARSE #2:c=0,e=22,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1294599624210788
  26. BINDS #2:
  27. kkscoacd
  28. Bind#0
  29.   oacdty=01 mxl=2000(2000) mxlc=00 mal=00 scl=00 pre=00
  30.   oacflg=01 fl2=1000000 frm=01 csi=852 siz=2000 off=0
  31.   kxsbbbfp=2b87021f5d58  bln=2000  avl=00  flg=05
  32. WAIT #2: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1294599624210996
  33. EXEC #2:c=0,e=152,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,tim=1294599624211024
  34. *** 2012-01-04 17:40:27.639
  35. WAIT #2: nam='SQL*Net message from client' ela= 12155349 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1294599636366428
  36. =====================
  37. PARSING IN CURSOR #1 len=21 dep=0 uid=63 oct=3 lid=63 tim=1294599636367721 hv=2564406738 ad='59bef4c8'
  38. select 'x' from dual
  39. END OF STMT
  40. PARSE #1:c=1000,e=1116,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1294599636367715
  41. BINDS #1:
  42. EXEC #1:c=1000,e=71,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1294599636367864
  43. WAIT #1: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1294599636367898
  44. WAIT #1: nam='SQL*Net message from client' ela= 633 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1294599636368583
  45. WAIT #1: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1294599636368633
  46. FETCH #1:c=0,e=35,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,tim=1294599636368657
  47. WAIT #1: nam='SQL*Net message from client' ela= 3347 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1294599636372074
  48. STAT #1 id=1 cnt=1 pid=0 pos=1 obj=0 op='FAST DUAL  (cr=0 pr=0 pw=0 time=3 us)'
  49. =====================
  50. PARSING IN CURSOR #2 len=61 dep=0 uid=63 oct=47 lid=63 tim=1294599636372247 hv=356401299 ad='59bee200'
  51. begin :id := sys.dbms_transaction.local_transaction_id; end;
  52. END OF STMT
  53. PARSE #2:c=0,e=25,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1294599636372243
  54. BINDS #2:
  55. kkscoacd
  56. Bind#0
  57.   oacdty=01 mxl=2000(2000) mxlc=00 mal=00 scl=00 pre=00
  58.   oacflg=01 fl2=1000000 frm=01 csi=852 siz=2000 off=0
  59.   kxsbbbfp=2b87021f5d58  bln=2000  avl=00  flg=05
  60. WAIT #2: nam='SQL*Net message to client' ela= 0 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1294599636372460
  61. EXEC #2:c=0,e=175,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,tim=1294599636372493
  62. WAIT #2: nam='SQL*Net message from client' ela= 617 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1294599636373160
  63. =====================
  64. PARSING IN CURSOR #2 len=37 dep=1 uid=0 oct=3 lid=0 tim=1294599636374899 hv=1398610540 ad='5af5c758'
  65. select text from view$ where rowid=:1
  66. END OF STMT
  67. PARSE #2:c=1000,e=998,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=1294599636374895
  68. BINDS #2:
  69. kkscoacd
  70. Bind#0
  71.   oacdty=11 mxl=16(16) mxlc=00 mal=00 scl=00 pre=00
  72.   oacflg=18 fl2=0001 frm=00 csi=00 siz=16 off=0
  73.   kxsbbbfp=2b87021f6290  bln=16  avl=16  flg=05
  74.   value=000001CC.000C.0001
  75. EXEC #2:c=2000,e=2021,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=1294599636377019
  76. FETCH #2:c=0,e=44,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,tim=1294599636377104
  77. STAT #2 id=1 cnt=1 pid=0 pos=1 obj=63 op='TABLE ACCESS BY USER ROWID VIEW$ (cr=1 pr=0 pw=0 time=28 us)'
  78. =====================
  79. PARSING IN CURSOR #1 len=25 dep=0 uid=63 oct=3 lid=63 tim=1294599636388424 hv=3206370776 ad='58566068'
  80. select * from v$session
  81. END OF STMT
  82. PARSE #1:c=15997,e=15178,p=0,cr=2,cu=0,mis=1,r=0,dep=0,og=1,tim=1294599636388420
  83. BINDS #1:
  84. EXEC #1:c=0,e=45,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1294599636388545
  85. WAIT #1: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1294599636388635
  86. WAIT #1: nam='SQL*Net more data to client' ela= 24 driver id=1413697536 #bytes=2001 p3=0 obj#=-1 tim=1294599636388750
  87. WAIT #1: nam='SQL*Net more data to client' ela= 11 driver id=1413697536 #bytes=2005 p3=0 obj#=-1 tim=1294599636388847
复制代码
wait event  "SQL*Net more data to client"



related bug :
  1. Bug 9087501: SELECT FROM V$SESSION AND SOME OTHER DICTIONARY VIEWS HANG FROM A REMOTE CLIENT      

  2. Show Bug Attributes Bug Attributes
  3. Type     B - Defect     Fixed in Product Version     -
  4. Severity     2 - Severe Loss of Service     Product Version     10.2.0.4
  5. Status     92 - Closed, Not a Bug     Platform     226 - Linux x86-64
  6. Created     04-Nov-2009     Platform Version     RED HAT ENTERPRISE LINUX 5
  7. Updated     11-Dec-2009     Base Bug     -
  8. Database Version     10.2.0.4         
  9. Affects Platforms     Generic         
  10. Product Source     Oracle         

  11. Show Related Products Related Products
  12. Line     Oracle Database Products     Family     Oracle Database
  13. Area     Net Services     Product     115 - Oracle Net Services

  14. Hdr: 9087501 10.2.0.4 NET 10.2.0.4 PRODID-115 PORTID-226
  15. Abstract: SELECT FROM V$SESSION AND SOME OTHER DICTIONARY VIEWS HANG FROM A REMOTE CLIENT

  16. *** 11/04/09 02:45 pm ***
  17. TAR:
  18. ----
  19. SR:2-5813605    (Previous 7711612.992)

  20. PROBLEM:
  21. --------
  22. select * from v$session hang from a remote client connected through listener.
  23. But same SQL runs ok with a local connection.

  24. systemstate dump shows the session is waiting on client. So there might be
  25. some problem with sql*net. However if turn on sql*net server trace, the
  26. problem is gone.

  27. DIAGNOSTIC ANALYSIS:
  28. --------------------
  29. checked sql*net trace and find many entries like:

  30. nioqrc: exit  
  31. nsmore2recv: entry  
  32. nsmore2recv: testing for MOREDATA from transport  
  33. sntpoltsts: fd 14 need 11 readiness event,wait

  34. Asked customer to try patch 6734871 but it does not help.

  35. WORKAROUND:
  36. -----------

  37. RELATED BUGS:
  38. -------------

  39. REPRODUCIBILITY:
  40. ----------------

  41. TEST CASE:
  42. ----------

  43. STACK TRACE:
  44. ------------

  45. SUPPORTING INFORMATION:
  46. -----------------------

  47. 24 HOUR CONTACT INFORMATION FOR P1 BUGS:
  48. ----------------------------------------

  49. DIAL-IN INFORMATION:
  50. --------------------

  51. IMPACT DATE:
  52. ------------


  53. new info: only windows client has this issue. A linux client at customer site
  54. does not have the issue. For windows client, customer tested 9.2.0.1,
  55. 10.2.0.1 and 10.2.0.4, all have problem.


  56. Did you try sqlnet tracing on the windows client? Did problem reproduce?.
  57. Did you enable sqlnet tracing at both client and server sides?
  58. Could you please set event 10046 level 12 and reproduce the hang?
  59. Does the hang happen if array size is changed to one in the sqplus session?
  60. Please also get an errorstack dump when session hangs.
  61. Thank you,
  62. Agustin Amador.


  63. Thank you for the update.

  64. sqlnet client trace files shows that connections are using different naming
  65. methods. On the hang case (client_8164.trc) it used TNSNAMES.

  66. Could you please simplify the test case by:

  67. 1) changing NAMES.DIRECTORY_PATH = (TNSNAMES, EZCONNECT) to
  68. NAMES.DIRECTORY_PATH = (TNSNAMES)

  69. so that TNSNAMES is always used

  70. 2) Create a new tnsnames.ora file with just one basic connection string to
  71. the server.

  72. Reproduce the problem again and provide the same data:

  73. a) sqlnet trace client (for both cases hang and no hang)
  74. b) 10046 level 12 and errorstack level 3 dumps while session is hanging


  75. Trace file client_6196_1.trc shows that client received the first package
  76. from the server. Then it stopped waiting for more information from the
  77. server.

  78. ...
  79. [12-NOV-2009 12:03:36:728] nsprecv: 631 bytes from transport
  80. [12-NOV-2009 12:03:36:728] nsprecv: packet dump
  81. [12-NOV-2009 12:03:36:728] nsprecv: 07 DB 00 00 06 00 00 00  |........|
  82. [12-NOV-2009 12:03:36:728] nsprecv: 00 00 10 17 8D C9 C8 00  |........|
  83. [12-NOV-2009 12:03:36:728] nsprecv: EA F8 1C 90 C5 DA E4 C3  |........|
  84. ...
  85. [12-NOV-2009 12:03:36:728] nsprecv: 00 00 22 00 01 80 00 00  |..".....|
  86. [12-NOV-2009 12:03:36:728] nsprecv: 20 00 00 00 00 00 00 00  |........|
  87. [12-NOV-2009 12:03:36:728] nsprecv: 00 00 00 00 00 00 00 00  |........|
  88. [12-NOV-2009 12:03:36:728] nsprecv: 00 00 69                 |..i     |
  89. [12-NOV-2009 12:03:36:728] nsprecv: normal exit
  90. [12-NOV-2009 12:03:36:728] nsrdr: got NSPTDA packet
  91. [12-NOV-2009 12:03:36:728] nsrdr: NSPTDA flags: 0x0
  92. [12-NOV-2009 12:03:36:728] nsrdr: normal exit
  93. [12-NOV-2009 12:03:36:728] nsdo: *what=1, *bl=2001
  94. [12-NOV-2009 12:03:36:728] nsdo: nsctxrnk=0
  95. [12-NOV-2009 12:03:36:728] nsdo: normal exit
  96. [12-NOV-2009 12:03:36:728] nioqrc: exit
  97. [12-NOV-2009 12:03:36:728] nioqrc: entry
  98. [12-NOV-2009 12:03:36:728] nsdo: entry
  99. [12-NOV-2009 12:03:36:728] nsdo: cid=0, opcode=85, *bl=0, *what=0, uflgs=0x0,
  100. cflgs=0x3
  101. [12-NOV-2009 12:03:36:728] nsdo: rank=64, nsctxrnk=0
  102. [12-NOV-2009 12:03:36:728] nsdo: nsctx: state=8, flg=0x400d, mvd=0
  103. [12-NOV-2009 12:03:36:728] nsdo: gtn=127, gtc=127, ptn=10, ptc=2011
  104. [12-NOV-2009 12:03:36:728] nsdo: switching to application buffer
  105. [12-NOV-2009 12:03:36:728] nsrdr: entry
  106. [12-NOV-2009 12:03:36:728] nsrdr: recving a packet
  107. [12-NOV-2009 12:03:36:728] nsprecv: entry
  108. [12-NOV-2009 12:03:36:728] nsprecv: reading from transport...
  109. [12-NOV-2009 12:03:36:728] nttrd: entry
  110. ...

  111. On the other hand, trace file mpint1_ora_25450.trc shows that server is
  112. waiting for the client.

  113. Call stack
  114. ...
  115. ksdxfdmp <- ksdxcb <- sspuser <- funlockfile <- read_nocancel
  116. <- snttread <- nttrd <- nsprecv <- nsrdr <- nsdo
  117. <- nsbrecv <- nioqrc <- opikndf2 <- opitsk <- opiino
  118. <- opiodr <- opidrv <- sou2o <- opimai_real <- main
  119. <- libc_start_main
  120. ...
  121.     ----------------------------------------
  122.     SO: 0xdc169ed8, type: 4, owner: 0xdc0086e0, flag: INIT/-/-/0x00
  123.     (session) sid: 421 trans: (nil), creator: 0xdc0086e0, flag: (41) USR/-
  124. -/-/-/-/-/-
  125.               DID: 0001-0025-0000070B, short-term DID: 0001-0025-0000070A
  126.               txn branch: (nil)
  127.               oct: 3, prv: 0, sql: 0x7581a1a0, psql: 0x65d983a0, user:
  128. 5/SYSTEM
  129.     O/S info: user: mng, term: INFRA-31, ospid: 1296:3904, machine:
  130. FXALL\INFRA-31
  131.               program: sqlplusw.exe
  132.     application name: SQL*Plus, hash value=3669949024
  133.     waiting for 'SQL*Net message from client' blocking sess=0x(nil) seq=135
  134. wait_time=0 seconds since wait started=6
  135.                 driver id=54435000, #bytes=1, =0
  136.     Dumping Session Wait History
  137.      for 'SQL*Net more data to client' count=1 wait_time=5
  138.                 driver id=54435000, #bytes=7d0, =0
  139.      for 'SQL*Net more data to client' count=1 wait_time=42
  140.                 driver id=54435000, #bytes=7d4, =0
  141.      for 'SQL*Net message to client' count=1 wait_time=3
  142.                 driver id=54435000, #bytes=1, =0
  143.      for 'row cache lock' count=1 wait_time=357
  144.                 cache id=10, mode=0, request=3
  145. ----

  146. It seems client and server are going out of sync during the communication.

  147. Questions:

  148. - Is the client running windows vista? If yes please review note:444694.1   

  149. - Does select sid from v$session run without problems (less data)?

  150. - Please ask customer to set SDU=32768 and see if hang reproduces.



  151. Thank you for the update.

  152. Could you please try SDU parameter in the DESCRIPTION part. For example:

  153. (DESCRIPTION =
  154.    (SDU=32768)
  155.    (ADDRESS_LIST =
  156.      (ADDRESS = (PROTOCOL = TCP)(HOST = numpintdb01)(PORT = 1521))
  157.    )
  158.    (CONNECT_DATA =
  159.      (INSTANCE_NAME = MPINT1)
  160.    )
  161. )

  162. test again and provide the client's sqlnet trace.
复制代码
发表于 2012-1-4 19:10:02
分析

1. 可能由 Bug 9087501引起 远程查询v$session 后hang住

2. 等待事件与bug描述 一致为 SQL*Net message to client

3.  PL/SQL 所在操作系统 是否是 windows vista?

若是 则参考Note ID 444694.1
  1. Windows Vista Client Using SQL*Net Against a 10gR2 Database May Hang or Crash [ID 444694.1]

  2. Applies to:
  3. Oracle Net Services - Version: 10.2.0.3 to 10.2.0.3


  4. Symptoms

  5. A Windows Vista machine running Oracle client version  10.2.0.3 and connecting to the 10gR2 version of database may hang or crash when retrieving large amounts of data.  You may be able to connect and select from smaller tables without a problem.  Also, you might not experience this problem using the same client against a 10gR1 version of the database.

  6. A server sqlnet trace may show the following stack:

  7. [000001 02-JUL-2007 16:21:10:796] nioqper: error from nioqsn
  8. [000001 02-JUL-2007 16:21:10:796] nioqper: ns main err code: 12571
  9. [000001 02-JUL-2007 16:21:10:796] nioqper: ns (2) err code: 12560
  10. [000001 02-JUL-2007 16:21:10:796] nioqper: nt main err code: 530
  11. [000001 02-JUL-2007 16:21:10:796] nioqper: nt (2) err code: 54
  12. [000001 02-JUL-2007 16:21:10:796] nioqper: nt OS err code: 0

  13. A client sqlnet  trace will probably show a hang in a read like this:

  14. (5888) [02-JUL-2007 13:57:25:036] nsprecv: reading from transport...
  15. (5888) [02-JUL-2007 13:57:25:036] nttrd: entry
  16. Changes

  17. The installation of a Windows Vista OS or the upgrade of the database from pre-10gR2 to 10gR2 may trigger this problem where it did not exist before.
  18. Cause

  19. This problem is caused by the default setting for TCP Window Scaling in the Windows Vista operating system.  TCP Window scaling or "Receive Window Auto-Tuning" is enabled by default on Vista.  All other Windows operating systems have this feature set to disabled by default.

  20. There are known issues with TCP connections of any kind and this feature.
  21. Solution
  22. Disable the TCP Window Scaling feature. See the following Microsoft Knowledge Base note:

  23. http://support.microsoft.com/kb/934430

  24. Instructions for disabling this feature are included in the note.
复制代码
在vista 上建议关闭TCP Window Scaling feature

4.
尝试在tnsname.ora 中设置SDU 可能可以解决该问题, 如

(DESCRIPTION =
   (SDU=32768)
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = numpintdb01)(PORT = 1521))
   )
   (CONNECT_DATA =
     (INSTANCE_NAME = MPINT1)
   )
)
发表于 2012-1-4 20:43:20
谢谢ML了
PL/SQL 所在操作系统 是win2003.
加了SDU 也不成,仍然有这个问题
等周五晚上再试一下重启数据库吧
发表于 2012-1-4 21:59:04

回复 5# 的帖子

就其网络等待事件"SQL*Net message to client" 和 现有的Note来看 和 Server端的数据库实例关系不大,
可能是由于 客户端所在操作系统OS的网络设置引起的。
发表于 2012-1-7 18:11:13

回复 6# 的帖子

嗯,确定不是服务端的问题,应该是中间路由防火墙的问题.
跨网段连接会有部分表查询不出来问题,但同一个网段的却正常
发表于 2012-1-10 21:12:19
找到问题了
是因为网关防火墙采用的是JUNIPER, 默认情况下Application Layer Gateway 设置了对SQL的控制造成的

  1. High Virtual Circuit Wait Running SQL Through a Juniper Firewall [ID 1293202.1]
  2.         修改时间 18-MAR-2011     类型 PROBLEM     状态 PUBLISHED         
  3. In this Document
  4.   Symptoms
  5.   Changes
  6.   Cause
  7.   Solution
  8.   References

  9. Applies to:

  10. Oracle Net Services - Version: 9.2.0.1 to 11.2.0.2 - Release: 9.2 to 11.2
  11. Information in this document applies to any platform.
  12. Symptoms


  13. From DBControl, High 'virtual circuit wait' is seen on a 'SELECT' SQL.


  14. From ASH Report in 65 minutes elapsed time:
  15. Top User Events


  16. Event                "Event Class"      % Event "Avg Active Sessions"
  17. virtual circuit wait  Network           98.32    7.99


  18. The database is experiencing low performance.


  19. Changes

  20. Firewall policy was implemented for security purposes.

  21. Firewall Device: Juniper Firewall  SSG-520 with Firmware Version 6.2.0 R4.0 .
  22. Cause


  23. "SQL" feature in Juniper Firewall Application Layer Gateway (ALG) is causing the hang.  This feature is documented as a known issue in several other documents:



  24. Document 1075432.1 - Archive Log Shipments Failing in Environment With Juniper Firewall (Doc ID Document 1068158.1 - Shared Server Connections Hung, Waiting on Virtual Circuit Status via Juniper Firewall






  25. Solution

  26. Disable "SQL" feature in Juniper Firewall.

  27. Solution Step:
  28. From the Admin Interface in Juniper Web Interface,
  29. under Security -> Deep Inspection -> ALG (Application Layer Gateway),

  30. Deselect the "SQL" feature (disable the feature).

  31. The result is as in following graph:
复制代码
FirewallAdjust.jpg

回复 显示全部楼层 道具 举报

发表于 2012-1-10 21:56:19

回复 8# 的帖子

Very Good!

就是要有这样一个 troubleshooting 的过程!

回复 显示全部楼层 道具 举报

您需要登录后才可以回帖 登录 | 注册

QQ|手机版|Archiver|Oracle数据库数据恢复、性能优化来问问AskMaclean - ParnassusData诗檀软件旗下网站

GMT+8, 2020-9-26 20:23 , Processed in 0.057453 second(s), 25 queries .

Powered by Discuz! X2.5

© 2001-2012 Comsenz Inc.

回顶部
TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569