Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Sunny.

Asked: August 04, 2016 - 11:25 am UTC

Last updated: August 06, 2016 - 1:41 pm UTC

Version: 11g

Viewed 1000+ times

You Asked



My main question is how to identify cause of sql?

alter session set timed_statistics=true
alter session set max_dump_file_size=unlimited

alter session set tracefile_identifier='MYSESSION_TODAY'


alter session set events '10046 trace name context forever, level 12'

following I post sql trace file.

WAIT #1032161120: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1635086250639
WAIT #1032161120: nam='SQL*Net message from client' ela= 11907 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1635086263237
CLOSE #1032161120:c=0,e=7,dep=0,type=1,tim=1635086263311
=====================
PARSING IN CURSOR #1032068312 len=61 dep=0 uid=91 oct=47 lid=91 tim=1635086263362 hv=356401299 ad='6eb52ee10' sqlid='9m7787camwh4m'
begin :id := sys.dbms_transaction.local_transaction_id; end;
END OF STMT
PARSE #1032068312:c=0,e=24,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=0,tim=1635086263361
BINDS #1032068312:
Bind#0
oacdty=01 mxl=4000(4000) mxlc=00 mal=00 scl=00 pre=00
oacflg=01 fl2=1000000 frm=01 csi=178 siz=4000 off=0
kxsbbbfp=3d852a30 bln=4000 avl=00 flg=05
WAIT #1032068312: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1635086263502
EXEC #1032068312:c=0,e=117,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=0,tim=1635086263520
WAIT #1032068312: nam='SQL*Net message from client' ela= 981 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1635086264534
CLOSE #1032068312:c=0,e=11,dep=0,type=3,tim=1635086264569
=====================
PARSING IN CURSOR #1032164816 len=73 dep=0 uid=91 oct=47 lid=91 tim=1635086264610 hv=2674323898 ad='6afc7bfd8' sqlid='bydf32qgqdwdu'
begin
sys.dbms_output.get_line(line => :line, status => :status);
end;
END OF STMT
PARSE #1032164816:c=0,e=19,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=0,tim=1635086264610
BINDS #1032164816:
Bind#0
oacdty=01 mxl=4000(4000) mxlc=00 mal=00 scl=00 pre=00
oacflg=01 fl2=1000000 frm=01 csi=178 siz=4000 off=0
kxsbbbfp=3d852a30 bln=4000 avl=00 flg=05
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=3d853dd0 bln=22 avl=00 flg=05
WAIT #1032164816: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1635086264750
EXEC #1032164816:c=0,e=109,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=0,tim=1635086264766

*** 2016-08-04 16:43:51.481
WAIT #1032164816: nam='SQL*Net message from client' ela= 41458245 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1635127723041
CLOSE #1032164816:c=0,e=23,dep=0,type=3,tim=1635127723167
PARSE #1032068312:c=0,e=24,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=0,tim=1635127723232
BINDS #1032068312:
Bind#0
oacdty=01 mxl=4000(4000) mxlc=00 mal=00 scl=00 pre=00
oacflg=01 fl2=1000000 frm=01 csi=178 siz=4000 off=0
kxsbbbfp=3d852a30 bln=4000 avl=00 flg=05
WAIT #1032068312: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1635127723375
EXEC #1032068312:c=0,e=125,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=0,tim=1635127723393

and Connor said...

Lots of tools will perform SQL for you on your behalf.

For example, in SQL Plus, if you do "set serveroutput on", then after each call to the database, the SQL Plus tool will need to go to the database to see if there are any lines in the output buffer.

So you would see "dbms_output.getlines" or similar.

Also, you'll always see lots of recursive SQL, that is, SQL that is run *by the database* in order to run the SQL statements that you provide.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

DBMS_OUTPUT

More on PL/SQL routine DBMS_OUTPUT here