2 Replies Latest reply: Jan 30, 2013 2:46 PM by Centinul RSS

    Question on sql_id of a proc

    Chilakamarthi
      Lets say if I have a pl/sql block with the following components.

      A CTAS,and index creation on the table and gather stats on the table.

      Will there be a sql_id associated with each of the component of the pl/sql?

      Thanks,
      Rav.
        • 1. Re: Question on sql_id of a proc
          sb92075
          Chilakamarthi wrote:
          Lets say if I have a pl/sql block with the following components.

          A CTAS,and index creation on the table and gather stats on the table.

          Will there be a sql_id associated with each of the component of the pl/sql?

          Thanks,
          Rav.
          yes
          • 2. Re: Question on sql_id of a proc
            Centinul
            You can also verify your theory by running a SQL trace. For example if I had the following anonymous PL/SQL block:
            DECLARE
                 x VARCHAR2(1);
            BEGIN
                 SELECT ID INTO x FROM TEST;
            END;
            /
            A portion of the trace would look like this:
            =====================
            PARSING IN CURSOR #18446744071484252128 len=63 dep=0 uid=173 oct=47 lid=173 tim=4043002705267 hv=703347738 ad='57c348f88' sqlid='2kzwtphnysg0u'
            DECLARE
                 x VARCHAR2(1);
            BEGIN
                 SELECT ID INTO x FROM TEST;
            END;
            END OF STMT
            PARSE #18446744071484252128:c=10000,e=4824,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=4043002705265
            =====================
            PARSING IN CURSOR #18446744071484312192 len=19 dep=1 uid=173 oct=3 lid=173 tim=4043002708492 hv=3777061920 ad='57e282980' sqlid='59ytkh3hk2t10'
            SELECT ID FROM TEST
            END OF STMT
            PARSE #18446744071484312192:c=0,e=2808,p=0,cr=8,cu=0,mis=1,r=0,dep=1,og=1,plh=217508114,tim=4043002708490
            EXEC #18446744071484312192:c=0,e=40,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=217508114,tim=4043002708685
            FETCH #18446744071484312192:c=0,e=133,p=0,cr=7,cu=0,mis=0,r=1,dep=1,og=1,plh=217508114,tim=4043002708880
            STAT #18446744071484312192 id=1 cnt=1 pid=0 pos=1 obj=1849558 op='TABLE ACCESS FULL TEST (cr=7 pr=0 pw=0 time=116 us cost=3 size=2 card=1)'
            CLOSE #18446744071484312192:c=0,e=5,dep=1,type=3,tim=4043002709066
            EXEC #18446744071484252128:c=0,e=3622,p=0,cr=15,cu=0,mis=0,r=1,dep=0,og=1,plh=0,tim=4043002709130
            CLOSE #18446744071484252128:c=0,e=41,dep=0,type=0,tim=4043002720463
            =====================
            As you can see from the output above they both get there own SQL IDs (last attribute on the line PARSING IN CURSOR).