1 2 Previous Next 21 Replies Latest reply: Jul 19, 2011 6:54 AM by NM RSS

    Async Descriptor Resize

    Catfive Lander
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        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
                          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
                            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
                              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
                                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
                                  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
                                    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