This discussion is archived
1 2 Previous Next 21 Replies Latest reply: Jul 19, 2011 4:54 AM by NM RSS

Async Descriptor Resize

Catfive Lander Explorer
Currently Being Moderated
I'm seeing a lot of "async descriptor resize" wait events on one of my databases running 11gR2 on 64-bit Linux.

I can't find any information on this wait event, though, and whether it implies doom or not. Anyone have information on this, please?
  • 1. Re: Async Descriptor Resize
    sb92075 Guru
    Currently Being Moderated
    I'm seeing a lot of "async descriptor resize" wait events on one of my databases running 11gR2 on 64-bit Linux.
    I can't find it in Doc set
    http://download.oracle.com/docs/cd/E11882_01/server.112/e10820/waitevents003.htm#BGGIBDJI
    or in DB below

    SELECT name FROM V$EVENT_NAME ORDER BY name DESC
    /
  • 2. Re: Async Descriptor Resize
    Catfive Lander Explorer
    Currently Being Moderated
    Try this:
    SQL> select name from v$event_name where lower(name) like '%async%';
    
    NAME
    ----------------------------------------------------------------
    LNS ASYNC archive log
    LNS ASYNC dest activation
    LNS ASYNC end of log
    db file async I/O submit
    kfk: async disk IO
    asynch descriptor resize
    IPC busy async request
    One up from the bottom. I missed the 'h' off in my subject.
  • 3. Re: Async Descriptor Resize
    amardeep.sidhu Pro
    Currently Being Moderated
    Metalink has a small note 1081977.1 on this. It is described as an undocumented wait event.

    Regards,
    Amardeep Sidhu
  • 4. Re: Async Descriptor Resize
    Catfive Lander Explorer
    Currently Being Moderated
    Thanks for that. Very informative note, actually.

    I had no idea that there were asynch descriptors inside the OS kernel, nor that they have to be increased in number when the number of asynch I/O's submitted by a process increases. Nor indeed that because the Linux kernel does not allow the number of descriptors to increase when there are outstanding I/O's inside the kernel, Oracle has to wait for the kernel to deal with all existing I/Os before the descriptors can be adjusted.

    I am guessing that all of this means that I'm doing too much I/O! Or that my default number of descriptors is too low.

    It would be nice to think that there was a way of configuring these descriptors high enough in the first place, however -but I am unaware of any such kernel parameters etc.
  • 5. Re: Async Descriptor Resize
    428730 Newbie
    Currently Being Moderated
    Hello,

    I have the same problem with Oracle 11.2.0.1 in RHEL 5.5 x86_64

    I have reviewed the kernel parameters and they fit with the values recomended in the Oracle installation guide, more in detail, the aio-max-nr parameter is set to 1048576.
    The aio-nr has a low value and never gets the aio-max-nr value. I've read the MOS Note 1081977.1 and 2 related bugs, but I didn't find a solution.

    Did you find a solution to this performance problem?

    Thank you very much.
  • 6. Re: Async Descriptor Resize
    793399 Newbie
    Currently Being Moderated
    Hello,

    I'm also seeing this wait event on Oracle 11.2.0.1 in RHEL 5.5 x86_64. It's causing major performance problems for one our nightly loads. Were you able to find a workaround or solution for this?

    Thanks,
    Matt
  • 7. Re: Async Descriptor Resize
    799647 Newbie
    Currently Being Moderated
    I too ran into similar Issue, one session is with this wait event, and is blocking other sessions which are waiting on row cache lock/latch free. ( I could get the details from blocking_session in v$session). Is there anyone who can shed more light onto this wait event? I don't see anything on the metalink either. I am not sure if this issue can be reproducible. I will try and update to the forum.

    Thanks,
    Murthy
  • 8. Re: Async Descriptor Resize
    510471 Newbie
    Currently Being Moderated
    I see this in our DB as well...
    Younes Naguib recomnends this http://blog.younes.ca/2010/07/asynch-descriptor-resize.html on his blog.

    Edited by: prdola on Oct 1, 2010 1:23 AM
  • 9. Re: Async Descriptor Resize
    Ospino Newbie
    Currently Being Moderated
    Hi,

    Could you please show us the query or queries cuasing the "async descriptor resize"? And the other wait events on the same session.

    John Ospino Rivas
  • 10. Re: Async Descriptor Resize
    Tanel Poder Oracle ACE Director
    Currently Being Moderated
    This is late reply, but I just wrote a short explanation of the in my blog. Basically it happens when Oracle increases/decreases the number of direct path IO slots used for direct path table scans and temp tablespace sort/hash/workarea temp-segments IO... probably nocache LOBs too...

    http://blog.tanelpoder.com/2010/11/23/asynch-descriptor-resize-wait-event-in-oracle

    Tanel
  • 11. Re: Async Descriptor Resize
    xaviermorera Explorer
    Currently Being Moderated
    Hi all,

    I'm suffering the same problem with 11.2.0.2 and RHEL 5.5 x86-64 and have not been able to find a solution to this. Opened a SR and the first answer is asking to check the note some of you have already mentioned here which explains the problem but does not give a solution.

    Has anybody found a solution for this?? I'll keep on with the SR and post if as soon as I get news as I think Oracle must give the solution although it may be an OS param tuning


    thanks!
  • 12. Re: Async Descriptor Resize
    mbobak Oracle ACE
    Currently Being Moderated
    Ok, so, you're seeing this wait event. But, is that a problem? Does it appear in Top 5 wait events section of AWR when your system is performing poorly? If not, you may not need to worry about it.

    Did you read Tanel's blog post, mentioned above?

    -Mark
  • 13. Re: Async Descriptor Resize
    NM Newbie
    Currently Being Moderated
    Hi,

    Recently we upgraded the Database server 10.2.0.4 to 11.2.0.2 on Solaris 10,X86 64bit.

    We had query which used to work fine on 10.2.0.4 and it used to take <1 sec but if i run the same Query on 11.2.0.2 then the Query Hangs and Async Descriptor Resize wait event seen.I was not able to reproduce the same problem on the different box with 11.2.0.2 version.

    One more interesting thing is when i switch back the optimizer enable feature to 10.2.0.4 on 11.2.0.2 DB then the same query works fine on the Issue DB.
    WAIT #18446741324892108344: nam='SQL*Net message to client' ela= 4 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=581070361957
    WAIT #18446741324892108344: nam='SQL*Net message from client' ela= 292 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=581070386312
    =====================
    PARSING IN CURSOR #18446741324892131656 len=52 dep=0 uid=97 oct=47 lid=97 tim=581070386473 hv=1029988163 ad='15cae0eb0' sqlid='9babjv8yq8ru3'
    BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;
    END OF STMT
    PARSE #18446741324892131656:c=0,e=87,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=0,tim=581070386471
    BINDS #18446741324892131656:
     Bind#0
      oacdty=123 mxl=4000(4000) mxlc=00 mal=00 scl=00 pre=00
      oacflg=00 fl2=1000000 frm=00 csi=00 siz=4000 off=0
    toid ptr value=4B40F078 length=16
    0F3463FDC3344449E04400093D11A0A7
      kxsbbbfp=fffffd7ffdb74dd8  bln=4000  avl=00  flg=15
     Bind#1
      oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
      oacflg=01 fl2=1000000 frm=00 csi=00 siz=24 off=0
      kxsbbbfp=fffffd7ffdb76178  bln=22  avl=22  flg=05
      value=###
      An invalid number has been seen.Memory contents are :
    Dump of memory from 0xFFFFFD7FFDB76178 to 0xFFFFFD7FFDB7618E
    FFFFFD7FFDB76170                   000010C1 00000000          [........]
    FFFFFD7FFDB76180 00000000 00000000 00000000 00000000  [................]
    WAIT #18446741324892131656: nam='SQL*Net message to client' ela= 6 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=581070454040
    EXEC #18446741324892131656:c=10000,e=67480,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=0,tim=581070454202
    WAIT #18446741324892131656: nam='SQL*Net message from client' ela= 645 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=581070485346
    CLOSE #18446741324892108344:c=0,e=14,dep=0,type=1,tim=581070485435
    CLOSE #18446741324892131656:c=0,e=33,dep=0,type=1,tim=581070485512
    =====================
    PARSING IN CURSOR #18446741324892167936 len=37 dep=1 uid=0 oct=3 lid=0 tim=581070486470 hv=1398610540 ad='15bed9700' sqlid='grwydz59pu6mc'
    select text from view$ where rowid=:1
    END OF STMT
    PARSE #18446741324892167936:c=0,e=429,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=581070486469
    BINDS #18446741324892167936:
     Bind#0
      oacdty=11 mxl=16(16) mxlc=00 mal=00 scl=00 pre=00
      oacflg=18 fl2=0001 frm=00 csi=00 siz=16 off=0
      kxsbbbfp=fffffd7ffdb75ac0  bln=16  avl=16  flg=05
      value=0000FF2F.0003.0001
    EXEC #18446741324892167936:c=0,e=799,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=3684871272,tim=581070487438
    FETCH #18446741324892167936:c=0,e=40,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,plh=3684871272,tim=581070487520
    STAT #18446741324892167936 id=1 cnt=1 pid=0 pos=1 obj=63 op='TABLE ACCESS BY USER ROWID VIEW$ (cr=1 pr=0 pw=0 time=25 us cost=1 size=15 card=1)'
    CLOSE #18446741324892167936:c=0,e=57,dep=1,type=0,tim=581070487616
    =====================
    PARSING IN CURSOR #18446741324892167936 len=37 dep=1 uid=0 oct=3 lid=0 tim=581070487789 hv=1398610540 ad='15bed9700' sqlid='grwydz59pu6mc'
    select text from view$ where rowid=:1
    END OF STMT
    PARSE #18446741324892167936:c=0,e=26,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=3684871272,tim=581070487788
    BINDS #18446741324892167936:
     Bind#0
      oacdty=11 mxl=16(16) mxlc=00 mal=00 scl=00 pre=00
      oacflg=18 fl2=0001 frm=00 csi=00 siz=16 off=0
      kxsbbbfp=fffffd7ffdb75ac0  bln=16  avl=16  flg=05
      value=00001AF9.0004.0001
    EXEC #18446741324892167936:c=0,e=180,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=3684871272,tim=581070488150
    FETCH #18446741324892167936:c=0,e=13,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,plh=3684871272,tim=581070488190
    STAT #18446741324892167936 id=1 cnt=1 pid=0 pos=1 obj=63 op='TABLE ACCESS BY USER ROWID VIEW$ (cr=1 pr=0 pw=0 time=8 us cost=1 size=15 card=1)'
    CLOSE #18446741324892167936:c=0,e=33,dep=1,type=0,tim=581070488259
    .........................
    
    WAIT #18446741324892168800: nam='asynch descriptor resize' ela= 3 outstanding #aio=0 current aio limit=4294967295 new aio limit=261 obj#=-1 tim=581074364909
    WAIT #18446741324892168800: nam='SQL*Net message to dblink' ela= 4 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=581074373252
    WAIT #18446741324892168800: nam='SQL*Net message from dblink' ela= 429 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=581074373774
    WAIT #18446741324892168800: nam='SQL*Net message to dblink' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=581074373884
    WAIT #18446741324892168800: nam='SQL*Net message from dblink' ela= 16660 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=581074390580
    WAIT #18446741324892168800: nam='SQL*Net more data from dblink' ela= 20 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=581074390898
    WAIT #18446741324892168800: nam='SQL*Net more data from dblink' ela= 11 driver id=1413697536 #bytes=9 p3=0 obj#=-1 tim=581074391117
    WAIT #18446741324892168800: nam='SQL*Net more data from dblink' ela= 11 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=581074391334
    WAIT #18446741324892168800: nam='SQL*Net more data from dblink' ela= 2240 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=581074393800
    WAIT #18446741324892168800: nam='asynch descriptor resize' ela= 2 outstanding #aio=0 current aio limit=4294967295 new aio limit=326 obj#=-1 tim=581074401418
    WAIT #18446741324892168800: nam='asynch descriptor resize' ela= 3 outstanding #aio=0 current aio limit=4294967295 new aio limit=326 obj#=-1 tim=581074426972
    WAIT #18446741324892168800: nam='asynch descriptor resize' ela= 4 outstanding #aio=0 current aio limit=4294967295 new aio limit=326 obj#=-1 tim=581074519379
    WAIT #18446741324892168800: nam='asynch descriptor resize' ela= 5 outstanding #aio=0 current aio limit=4294967295 new aio limit=326 obj#=-1 tim=581074536063
    WAIT #18446741324892168800: nam='asynch descriptor resize' ela= 5 outstanding #aio=0 current aio limit=4294967295 new aio limit=326 obj#=-1 tim=581074557605
    WAIT #18446741324892168800: nam='asynch descriptor resize' ela= 5 outstanding #aio=0 current aio limit=4294967295 new aio limit=261 obj#=-1 tim=581074594520
    
    *** 2011-06-24 13:30:28.286
    WAIT #18446741324892168800: nam='asynch descriptor resize' ela= 4 outstanding #aio=0 current aio limit=4294967295 new aio limit=261 obj#=-1 tim=581074689833
    WAIT #18446741324892168800: nam='SQL*Net message to dblink' ela= 3 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=581074720881
    WAIT #18446741324892168800: nam='SQL*Net message from dblink' ela= 258 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=581074721249
    Regards
    NM
  • 14. Re: Async Descriptor Resize
    xaviermorera Explorer
    Currently Being Moderated
    Hi Mark

    It really is a problem as the session gets stuck at this wait event getting 1 CPU al 100%. After 24 hours it was still stuck at the same point and I had to manually kill the session.

    We use OWB (warehouse builder) and we are getting this problem when executing one of the reports that comes with this tool through OWB Repository Browser.

    Opened a SR with MOS but still haven't a solution or a workaround. As soon as I have news I'll update...

    Thanks!
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points