Yeah, sure it would be cool to crank up some big time powered VM’s in the cloud and let rip, but the reality is – if you’re starting out on a cloud exploration, you probably want to (initially at least) just dip your toes in the water and start with something small. For example, if I wanted to play with 12c Release 2, I can just sign up for an Exadata Express service so I can explore the new features without breaking the bank.
Well, in 12c Release 2, there’s two nifty new views to help out here. You can access trace file information directly from the database. Here’s a simple example
And once I’ve identified a trace file that I’m interested in, I can grab it contents just as easily.
SQL> desc V$DIAG_TRACE_FILE_CONTENTS
Name Null? Type
----------------------------- -------- --------------------
ADR_HOME VARCHAR2(444)
TRACE_FILENAME VARCHAR2(68)
RECORD_LEVEL NUMBER
PARENT_LEVEL NUMBER
RECORD_TYPE NUMBER
TIMESTAMP TIMESTAMP(3) WITH TIME ZONE
PAYLOAD VARCHAR2(4000)
SECTION_ID NUMBER
SECTION_NAME VARCHAR2(64)
COMPONENT_NAME VARCHAR2(64)
OPERATION_NAME VARCHAR2(64)
FILE_NAME VARCHAR2(64)
FUNCTION_NAME VARCHAR2(64)
LINE_NUMBER NUMBER
THREAD_ID VARCHAR2(64)
SESSION_ID NUMBER
SERIAL# NUMBER
CON_UID NUMBER
CONTAINER_NAME VARCHAR2(30)
CON_ID NUMBER
SQL> select PAYLOAD
2 from V$DIAG_TRACE_FILE_CONTENTS
3 where TRACE_FILENAME = 'orcl12c_ora_4163.trc'
4 order by LINE_NUMBER;
PAYLOAD
----------------------------------------------------------------------------------------------------------------------------------
Trace file /u01/app/oracle/diag/rdbms/orcl12c/orcl12c/trace/orcl12c_ora_4163.trc
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Build label: RDBMS_12.2.0.1.0_LINUX.X64_161003
ORACLE_HOME: /u01/app/oracle/product/12.2/db_1
System name: Linux
Node name: vbgeneric
Release: 3.8.13-118.14.1.el7uek.x86_64
Version: #2 SMP Mon Oct 31 17:32:03 PDT 2016
Machine: x86_64
Instance name: orcl12c
Redo thread mounted by this instance: 1
Oracle process number: 49
Unix process pid: 4163, image: oracle@vbgeneric
*** 2017-01-22T23:53:13.661980-05:00 (ORCL(3))
*** SESSION ID:(48.50034) 2017-01-22T23:53:13.662038-05:00
*** CLIENT ID:() 2017-01-22T23:53:13.662050-05:00
*** SERVICE NAME:(orcl) 2017-01-22T23:53:13.662059-05:00
*** MODULE NAME:(SQL*Plus) 2017-01-22T23:53:13.662069-05:00
*** ACTION NAME:() 2017-01-22T23:53:13.662079-05:00
*** CLIENT DRIVER:(SQL*PLUS) 2017-01-22T23:53:13.662087-05:00
*** CONTAINER ID:(3) 2017-01-22T23:53:13.662097-05:00
WAIT #139958764906240: nam='PGA memory operation' ela= 102 p1=0 p2=0 p3=0 obj#=1376 tim=656737684
WAIT #139958764906240: nam='SQL*Net message to client' ela= 38 driver id=1413697536 #bytes=1 p3=0 obj#=1376 tim=656738633
WAIT #139958764906240: nam='SQL*Net message from client' ela= 2608 driver id=1413697536 #bytes=1 p3=0 obj#=1376 tim=656741328
CLOSE #139958764906240:c=0,e=110,dep=0,type=1,tim=656741672
=====================
PARSING IN CURSOR #139958764708168 len=36 dep=0 uid=117 oct=47 lid=117 tim=656745298 hv=4128301241 ad='7429cdd8' sqlid='5t10uu7v11s5t'
BEGIN DBMS_OUTPUT.ENABLE(NULL); END;
END OF STMT
PARSE #139958764708168:c=3000,e=3496,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=656745290
EXEC #139958764708168:c=0,e=147,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=0,tim=656745624
WAIT #139958764708168: nam='SQL*Net message to client' ela= 10 driver id=1413697536 #bytes=1 p3=0 obj#=1376 tim=656745713
WAIT #139958764708168: nam='SQL*Net message from client' ela= 5605 driver id=1413697536 #bytes=1 p3=0 obj#=1376 tim=656751369
CLOSE #139958764708168:c=0,e=64,dep=0,type=0,tim=656751853
=====================
PARSING IN CURSOR #139958859515432 len=332 dep=1 uid=0 oct=3 lid=0 tim=656752148 hv=2698389488 ad='614a4b08' sqlid='acmvv4fhdc9zh'
select obj#,type#,ctime,mtime,stime, status, dataobj#, flags, oid$, spare1, spare2, spare3, signature, spare7, spare8, spare9, nvl
(dflcollid, 16382), creappid, creverid, modappid, modverid, crepatchid, modpatchid from obj$ where owner#=:1 and name=:2 and names
pace=:3 and remoteowner is null and linkname is null and subname is null
END OF STMT
EXEC #139958859515432:c=1000,e=58,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=813480514,tim=656752148
FETCH #139958859515432:c=0,e=32,p=0,cr=3,cu=0,mis=0,r=0,dep=1,og=4,plh=813480514,tim=656752256
CLOSE #139958859515432:c=0,e=2,dep=1,type=3,tim=656752308
EXEC #139958859515432:c=0,e=38,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=813480514,tim=656752540
FETCH #139958859515432:c=0,e=36,p=0,cr=4,cu=0,mis=0,r=1,dep=1,og=4,plh=813480514,tim=656752629
CLOSE #139958859515432:c=0,e=7,dep=1,type=3,tim=656752675
=====================
PARSING IN CURSOR #139958765170592 len=868 dep=3 uid=0 oct=3 lid=0 tim=656755217 hv=3633507567 ad='60fdfec8' sqlid='121ffmrc95v7g'
select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.flags,i.property,i.pctfree$,i.initrans,i.maxtrans,i.blevel,i.leafcnt,i.di
stkey,i.lblkkey,i.dblkkey,i.clufac,i.cols,i.analyzetime,i.samplesize,i.dataobj#,nvl(i.degree,1),nvl(i.instances,1),i.rowcnt,mod(i.
pctthres$,256),i.indmethod#,i.trunccnt,nvl(c.unicols,0),nvl(c.deferrable#+c.valid#,0),nvl(i.spare1,i.intcols),i.spare4,i.spare2,i.
spare6,decode(i.pctthres$,null,null,mod(trunc(i.pctthres$/256),256)),nvl(i.evaledition#,1),nvl(i.unusablebefore#,0),nvl(i.unusable
beginning#,0), ist.cachedblk,ist.cachehit,ist.logicalread from ind$ i, ind_stats$ ist, (select enabled, min(intcols) unicols,min(t
o_number(bitand(defer,1))) deferrable#,min(to_number(bitand(defer,4))) valid# from cdef$ where obj#=:1 and enabled > 1 group by en
abled) c where i.obj#=c.enabled(+) and i.obj# = ist.obj#(+) and i.bo#=:1 order by i.obj#
END OF STMT
EXEC #139958765170592:c=0,e=137,p=0,cr=0,cu=0,mis=0,r=0,dep=3,og=4,plh=2542797530,tim=656755214
FETCH #139958765170592:c=1000,e=235,p=0,cr=3,cu=0,mis=0,r=0,dep=3,og=4,plh=2542797530,tim=656755601
CLOSE #139958765170592:c=0,e=2,dep=3,type=3,tim=656755692
=====================
PARSING IN CURSOR #139958765274984 len=552 dep=3 uid=0 oct=3 lid=0 tim=656755788 hv=1798149220 ad='6148cc98' sqlid='9t4vxdppkv534'
...
...
...
And yes… it’s an Exadata !