DDL Parsing

Normally most DML/Select Statements are processed in like create cursor,parse,execute and fetch.

So, how about DDL statements ,how do they execute ? Answer is they execute in only two pass. creating cursor and parsing. Parse phase itself execute it. Let’s see example and put tracing on.

 


create table taral.test1(col1 number);

PARSING IN CURSOR #33 len=37 dep=0 uid=0 oct=1 lid=0 tim=1663011419048 hv=2791204537 ad='30c534d4' sqlid='ad60h5qm5wtpt'
create table taral.test1(col1 number)
END OF STMT
PARSE #33:c=0,e=3415,p=0,cr=2,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=1663011419047

EXEC #33:c=890625,e=2330367,p=855,cr=3343,cu=52,mis=0,r=0,dep=0,og=1,plh=0,tim=1663013749499
WAIT #33: nam='log file sync' ela= 95 buffer#=558 sync scn=7375981 p3=0 obj#=528 tim=1663013749698
WAIT #33: nam='SQL*Net message to client' ela= 2 driver id=1111838976 #bytes=1 p3=0 obj#=528 tim=1663013750627

*** 2010-11-29 15:29:04.822
WAIT #33: nam='SQL*Net message from client' ela= 3531454 driver id=1111838976 #bytes=1 p3=0 obj#=528 tim=1663017282121
CLOSE #33:c=0,e=23,dep=0,type=0,tim=1663017282280

This was the only cursor in trace file. But it did say that it got executed. Let’s see what happend here using another example.


SYS@taral AS SYSDBA> variable x1 number
SYS@taral AS SYSDBA> exec dbms_sql.parse ( : x1 ,'create table taral.test2(col1 number)',dbms_sql.native);
BEGIN dbms_sql.parse ( : x1 ,'create table taral.test2(col1 number)',dbms_sql.native); END;

*
ERROR at line 1:
ORA-29471: DBMS_SQL access denied
ORA-06512: at "SYS.DBMS_SQL", line 1053
ORA-06512: at line 1

Because, we have to open a cursor first.

SYS@taral AS SYSDBA> variable x1 number
SYS@taral AS SYSDBA> exec : x1:=dbms_sql.open_cursor;

PL/SQL procedure successfully completed.
----- Session Cursor Dump -----
Current cursor: 0, pgadep=0

Open cursors(pls, sys, hwm, max): 2(0, 2, 64, 300)
 NULL=1 SYNTAX=0 PARSE=0 BOUND=1 FETCH=0 ROW=0
Cached frame pages(total, free):
 4k(5, 5), 8k(0, 0), 16k(0, 0), 32k(0, 0)
----- Session Open Cursors -----

Lets parse DDL Statement

SYS@taral AS SYSDBA> exec dbms_sql.parse ( : x1 ,'create table taral.test2(col1 number)',dbms_sql.native);

PL/SQL procedure successfully completed.

SYS@taral AS SYSDBA> desc taral.test2;
 Name
 --------------------------------------
 COL1

So, it has created table in parse phase itself.


Cursor#3(0x0D1E0E1C) state=NULL curiob=0x0D6D5970
 curflg=4d fl2=0 par=0x00000000 ses=0x35A25B5C
----- Dump Cursor sql_id=bmv42n748kx2r xsc=0x0D6D5970 cur=0x0D1E0E1C -----

LibraryHandle:  Address=35d2fe4c Hash=c8897457 LockMode=N PinMode=0 LoadLockMode=0 Status=VALD
  ObjectName:  Name=create table taral.test2(col1 number)

    FullHashValue=8eef83d6a3fe2933b9ec82a1c8897457 Namespace=SQL AREA(00) Type=CURSOR(00) Identifier=3364451415 OwnerIdn=0
  Statistics:  InvalidationCount=1 ExecutionCount=1 LoadCount=2 ActiveLocks=1 TotalLockCount=1 TotalPinCount=1
  ...
  ...
  ...
 cursor instantiation=0x0D6D5970 used=1291067219 exec_id=0 exec=1
 child#0(0x00000000) pcs=0x2FCBD450
  clk=0x00000000 ci=0x00000000 pn=0x00000000 ctx=0x00000000
 kgsccflg=0 llk[0x0D6D5974,0x0D6D5974] idx=0
 xscflg=80002036 fl2=4060000 fl3=2042008 fl4=8000
 Frames pfr 0x0D6D5114 siz=0 efr 0x0D6D50C4 siz=0
 Cursor frame dump
 kxscphp=0x0D6D0034 siz=1000 inu=140 nps=140

We can see that executioncount is 1 here. Let’s check for select statement what happen


SYS@taral AS SYSDBA> variable y1 number

SYS@taral AS SYSDBA> exec : y1 := dbms_sql.open_cursor;
PL/SQL procedure successfully completed.

SYS@taral AS SYSDBA> exec dbms_sql.parse ( : y1 ,'select * from scott.emp where ename in(7369,7499,7521)',dbms_sql.native);
PL/SQL procedure successfully completed.

Cursor#6(0x0D1E0F3C) state=BOUND curiob=0x0D2819C8
 curflg=4d fl2=0 par=0x00000000 ses=0x35A25B5C
----- Dump Cursor sql_id=6mbszcqzu6wzp xsc=0x0D2819C8 cur=0x0D1E0F3C -----

LibraryHandle:  Address=2f55bbf4 Hash=bfa373f5 LockMode=N PinMode=0 LoadLockMode=0 Status=VALD
  ObjectName:  Name=select * from scott.emp where ename in(7369,7499,7521)

    FullHashValue=b636966355abdb0569af1f65bfa373f5 Namespace=SQL AREA(00) Type=CURSOR(00) Identifier=3215160309 OwnerIdn=0
  Statistics:  InvalidationCount=0 ExecutionCount=0 LoadCount=2 ActiveLocks=1 TotalLockCount=1 TotalPinCount=1
  Counters:  BrokenCount=1 RevocablePointer=1 KeepDependency=1 KeepHandle=1 BucketInUse=0
....
....
cursor instantiation=0x0D2819C8 used=1291068865 exec_id=0 exec=0
 child#0(0x2F55BABC) pcs=0x2E35E450
  clk=0x30C819B4 ci=0x2E35D0E4 pn=0x35DF458C ctx=0x2DFDDCF0
 kgsccflg=0 llk[0x0D2819CC,0x0D2819CC] idx=0
 xscflg=100076 fl2=40000 fl3=2062000 fl4=100
 Frames pfr 0x00000000 siz=3044 efr 0x00000000 siz=3036
 Cursor frame dump
  enxt: 2.0x00000100  enxt: 1.0x00000adc
  pnxt: 1.0x00000008

Now, lets execute it

SYS@taral AS SYSDBA> variable z number
SYS@taral AS SYSDBA> execute : z := dbms_sql.execute (:y1);

----- Dump Cursor sql_id=6mbszcqzu6wzp xsc=0x0D2819C8 cur=0x0D1E0F3C -----

LibraryHandle:  Address=2f55bbf4 Hash=bfa373f5 LockMode=N PinMode=0 LoadLockMode=0 Status=VALD
  ObjectName:  Name=select * from scott.emp where ename in(7369,7499,7521)

    FullHashValue=b636966355abdb0569af1f65bfa373f5 Namespace=SQL AREA(00) Type=CURSOR(00) Identifier=3215160309 OwnerIdn=0
  Statistics:  InvalidationCount=0 ExecutionCount=1 LoadCount=2 ActiveLocks=1 TotalLockCount=1 TotalPinCount=1
  Counters:  BrokenCount=1 RevocablePointer=1 KeepDependency=1 KeepHandle=1 BucketInUse=0 HandleInUse=0
  Concurrency:  DependencyMutex=2f55bc5c(0, 0, 0, 0) Mutex=2f55bcac(9, 19, 0, 6)
  Flags=RON/PIN/TIM/PN0/DBN/[10012841]
  WaitersLists:
    Lock=2f55bc4c[2f55bc4c,2f55bc4c]
    Pin=2f55bc54[2f55bc3c,2f55bc3c]
  Timestamp:  Current=11-29-2010 16:14:25
  LibraryObject:  Address=2e35e060 HeapMask=0000-0001-0001 Flags=EXS[0000] Flags2=[0000] PublicFlags=[0000]
    ChildTable:  size='16'
      Child:  id='0' Table=2e35efb0 Reference=2e35e66c Handle=2f55babc
    Children:
      Child:  childNum='0'
        LibraryHandle:  Address=2f55babc Hash=0 LockMode=N PinMode=S LoadLockMode=0 Status=VALD
          Name:  Namespace=SQL AREA(00) Type=CURSOR(00)
          Statistics:  InvalidationCount=0 ExecutionCount=1 LoadCount=1 ActiveLocks=1 TotalLockCount=1 TotalPinCount=2
          Counters:  BrokenCount=1 RevocablePointer=1 KeepDependency=0 KeepHandle=0 BucketInUse=0 HandleInUse=0
          Concurrency:  DependencyMutex=2f55bb24(0, 0, 0, 0) Mutex=2f55bcac(9, 19, 0, 6)
          Flags=RON/PIN/PN0/EXP/[10012111]
....
....
....
 cursor instantiation=0x0D2819C8 used=1291069118 exec_id=16777216 exec=1
 child#0(0x2F55BABC) pcs=0x2E35E450
  clk=0x30C819B4 ci=0x2E35D0E4 pn=0x35DF458C ctx=0x2DFDDCF0
 kgsccflg=0 llk[0x0D2819CC,0x0D2819CC] idx=0
 xscflg=c0100076 fl2=4040400 fl3=2262008 fl4=100
 Frames pfr 0x0E0F0610 siz=3044 efr 0x0E0F05B4 siz=3036
 Cursor frame dump
  enxt: 2.0x00000100  enxt: 1.0x00000adc
  pnxt: 1.0x00000008
 kxscphp=0x0DFA10B4 siz=1000 inu=152 nps=152

So, DDL is executed in two pass.

Advertisements

About Taral
I am Rookie to Oracle Technology so let's see where it goes

2 Responses to DDL Parsing

  1. Thiago says:

    Very nice post.

    It is possible to validate DDLs (see what changes the DDL will make) without executing them ?

    I am trying to implement a DDL configurantion management system, so I can see what changes a developer is proposing and its correspondings impacts.

    • Taral says:

      Thanks Thiago for visiting

      This post was just for understanding how DDL work. But as per your question it seems like it’s a process that you want to implement. But isn’t DEV/QA/UAT environment are for. What we have done is we have different box which just hold metadata and always sync with prod and any change made are flashback to just check DDL impact or validity

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: