overmars 发表于 2013-12-31 10:21:11

还有啥情况下SQLplan_hash_value为0呢？

本帖最后由 overmars 于 2013-12-31 12:40 编辑

       3节点oracle rac 11.2.0.3版本，运行在64位 linux上面，今天下午出现了cursor:pin S wait on X等待事件。
通过上面的图可以看到，EM找到了堵塞的源头会话397，并对这个会话的进程ID=8900进行了dump，在这个dump文件中发现，有如下片段：
      LibraryObjectLock:  Address=0x7b4f83860 Handle=0x7c44f7910 Mode=N CanBeBrokenCount=1 Incarnation=1 ExecutionCount=0         
        ClusterLock=0x7b35fdf20 Context=0x7f58d1b593a8 User=0x8da2eb028 Session=0x8da2eb028 ReferenceCount=1         
        Flags=CBK/ SavepointNum=0
      LibraryHandle:  Address=0x7c44f7910 Hash=0 LockMode=N PinMode=X LoadLockMode=0 Status=VALD
        Name:  Namespace=SQL AREA(00) Type=CURSOR(00)
        Statistics:  InvalidationCount=0 ExecutionCount=0 LoadCount=1 ActiveLocks=4 TotalLockCount=4 TotalPinCount=5
        Counters:  BrokenCount=1 RevocablePointer=1 KeepDependency=0 BucketInUse=0 HandleInUse=0 HandleReferenceCount=0
        Concurrency:  DependencyMutex=0x7c44f79c0(0, 0, 0, 0) Mutex=0x8d54f1bd8(0, 4090, 0, 0)
        Flags=RON/PIN/PN0/EXP/CHD/
        WaitersLists:  
          Lock=0x7c44f79a0
          Pin=0x7c44f7980
          LoadLock=0x7c44f79f8
        LibraryObject:  Address=0x75c12e0b0 HeapMask=0000-0001-0001-0000 Flags=EXS Flags2= PublicFlags=
          DataBlocks:  
            Block:  #='0' name=KGLH0^d9a12e7 pins=0 Change=NONE   
              Heap=0x7c10730f0 Pointer=0x75c12e150 Extent=0x75c12e030 Flags=I/-/P/A/-/-
              FreedLocation=0 Alloc=2.210938 Size=3.976562 LoadTime=8831333230
            Block:  #='6' name=SQLA^d9a12e7 pins=0 Change=NONE   
              Heap=0x77aaba778 Pointer=0x829471ef8 Extent=0x8294712a0 Flags=I/-/P/A/-/E
              FreedLocation=0 Alloc=0.000000 Size=0.000000 LoadTime=0
        NamespaceDump:  
          Child Cursor:  Heap0=0x75c12e150 Heap6=0x829471ef8 Heap0 Load Time=12-30-2013 11:45:10 Heap6 Load Time=12-30-2013 11:45:10       ----------------------------------------
      KGX Atomic Operation Log 0x7b35fdf20
       Mutex 0x77aaba6d0(397, 0) idn d9a12e7 oper LONG_EXCL
       Cursor Pin uid 397 efd 0 whr 1 slp 0
       opr=3 pso=0x7b4f83860 flg=0
       pcs=0x77aaba638 nxt=(nil) flg=35 cld=0 hd=0x7c44f7910 par=0x77aaba150
       ct=0 hsh=0 unp=(nil) unn=0 hvl=7aabaff8 nhv=1 ses=0x8da2eb028
       hep=0x77aaba6d0 flg=80 ld=1 ob=0x75c12e0b0 ptr=0x829471ef8 fex=0x8294712a0

关于导致cursor:pin S wait X的sql语句：
sql_id=afm8a7w6tn4r7 plan_hash_value=0 problem_type=0
----- Current SQL Statement for this session (sql_id=afm8a7w6tn4r7) -----
select s.C_ACCEPT_ID as cid  ,s.V_SSTATUS_OPERATOR as voperator  ,s.D_SSTATUS_FINISH daccept,decode(s.C_SSTATUS_TYPE,'6','R','7','A','8','F','9') as ctype   ,s.V_SSTATUS_SIGN as vsign from sstatus s where s.C_SSTATUS_TYPE in ('6','7','8','9') and s.C_SSTATUS_OFFICE like '%01' and to_char(s.D_SSTATUS_FINISH,'yyyy-mm-dd')='2013-12-30' and s.C_ACCEPT_ID not in ('C2013122401643','C2013122401671','C2013122401674','C2013122401678','C2013122401680','C2013122401686','C2013122401693','Z2013121603778','Z2013121701747','Z2013121702524','Z2013121703562','Z2013121801969','Z2013121801992','Z2013121802280','Z2013121802429','Z2013121901621','Z2013122300178','Z2013122300617','Z2013122300698','Z2013122302554','Z2013122303196','Z2013122303203','Z2013122304107','Z2013122304117','Z2013122304308','Z2013122304754','Z2013122400024','Z2013122401036','Z2013122401513','Z2013122401541','Z2013122401548','Z2013122401563','Z2013122401587','Z2013122401604','Z2013122401605','Z2013122401639','Z2013122401670','Z2013122401730','Z2013122401756','Z2013122401846','Z2013122500819','Z2013122600425','Z2013122600439','Z2013122600456','Z2013122600541','Z2013122600549','Z2013122600555','Z2013122600568','Z2013122600589','Z2013122600795','Z2013122600803','Z2013122600815','Z2013122600821','Z2013122600831','Z2013122600841','Z2013122600850','Z2013122600856','Z2013122600865','Z2013122600878','Z2013122600882','Z2013122600888','Z2013122600897','Z2013122600907','Z2013122600920','Z2013122700299','Z2013122700306','Z2013122700312','Z2013122700317','Z2013122700320','Z2013122700321','Z2013122700328','Z2013122700343','Z2013122700352','Z2013122700363','Z2013122700370','Z2013122700635','Z2013122700655','Z2013122700704','Z2013122700720','Z2013122700738','Z2013122700780','Z2013122700800','Z2013122700812','Z2013122700842','Z2013122700873','Z2013122701180','Z2013122701189','Z2013122701206','Z2013122701222','Z2013122701227','Z2013122701236','Z2013122701247','Z2013122701259') and s.I_SSTATUS_TOWNLEVEL!=4
sql_text_length=1972
sql=select s.C_ACCEPT_ID as cid  ,s.V_SSTATUS_OPERATOR as voperator  ,s.D_SSTATUS_FINISH daccept,decode(s.C_SSTATUS_TYPE,'6','R','7','A','8','F','9') as ctype   ,s.V_SSTATUS_SIGN as vsign from sstatus s where s.C_SSTATUS_TYPE in ('6','7','8','9') and s.C_SSTAT
sql=US_OFFICE like '%01' and to_char(s.D_SSTATUS_FINISH,'yyyy-mm-dd')='2013-12-30' and s.C_ACCEPT_ID not in ('C2013122401643','C2013122401671','C2013122401674','C2013122401678','C2013122401680','C2013122401686','C2013122401693','Z2013121603778','Z2013121701747
sql=','Z2013121702524','Z2013121703562','Z2013121801969','Z2013121801992','Z2013121802280','Z2013121802429','Z2013121901621','Z2013122300178','Z2013122300617','Z2013122300698','Z2013122302554','Z2013122303196','Z2013122303203','Z2013122304107','Z2013122304117'
sql=,'Z2013122304308','Z2013122304754','Z2013122400024','Z2013122401036','Z2013122401513','Z2013122401541','Z2013122401548','Z2013122401563','Z2013122401587','Z2013122401604','Z2013122401605','Z2013122401639','Z2013122401670','Z2013122401730','Z2013122401756',
sql='Z2013122401846','Z2013122500819','Z2013122600425','Z2013122600439','Z2013122600456','Z2013122600541','Z2013122600549','Z2013122600555','Z2013122600568','Z2013122600589','Z2013122600795','Z2013122600803','Z2013122600815','Z2013122600821','Z2013122600831','
sql=Z2013122600841','Z2013122600850','Z2013122600856','Z2013122600865','Z2013122600878','Z2013122600882','Z2013122600888','Z2013122600897','Z2013122600907','Z2013122600920','Z2013122700299','Z2013122700306','Z2013122700312','Z2013122700317','Z2013122700320','Z
sql=2013122700321','Z2013122700328','Z2013122700343','Z2013122700352','Z2013122700363','Z2013122700370','Z2013122700635','Z2013122700655','Z2013122700704','Z2013122700720','Z2013122700738','Z2013122700780','Z2013122700800','Z2013122700812','Z2013122700842','Z2
sql=013122700873','Z2013122701180','Z2013122701189','Z2013122701206','Z2013122701222','Z2013122701227','Z2013122701236','Z2013122701247','Z2013122701259') and s.I_SSTATUS_TOWNLEVEL!=4

查过metalink，plan_hash_value的情况一般是有绑定变量的情况下，如果sql已经分析，但是最后没有执行的情况下，plan_hash_value是为0的。但是，我这里的这个SQL没有绑定变量，为何plan_hash_value还是为0，难道一直在分析导致一直对这X mode pin这个cursor，没有执行？
但是，我在sqlplus中执行过上面的SQL语句，不到一秒就出结果了，很奇怪！
另外cursor:pin S wait on X,为啥X会一直被Mutex 0x77aaba6d0(397, 0) idn d9a12e7持有，而不释放呢？

kevinlin.ora 发表于 2013-12-31 11:11:33

因为cursor:pin S wait on X等待是10ms一次的，直到获得相关资源，所以要确定是否是不断地在等待cursor:pin S wait on X。对blocking session与blocked session做10046 trace跟踪一下。
或者多dump几次，看是否是相同的SQL或address

可以通过V$session.(event,p1,p2,p3,seq#)来确定一个等待，如果event相同，但其他如seq#一直在变，就不能算是同一个等待
页: [1]
查看完整版本: 还有啥情况下SQLplan_hash_value为0呢？