Posted by 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.
But whatever the need, accessing a database as a service as opposed to a server, there’s often that fear of “handing over the reins”, that is, that I’ll not be able to do the things I want to do, especially when it comes to OS level access. And for a developer or DBA, perhaps a thing that might raise alarm bells is: “How will I access my trace files ?”
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
SQL> desc V$DIAG_TRACE_FILE
Name Null? Type
----------------------------- -------- --------------------
ADR_HOME VARCHAR2(444)
TRACE_FILENAME VARCHAR2(68)
CHANGE_TIME TIMESTAMP(3) WITH TIME ZONE
MODIFY_TIME TIMESTAMP(3) WITH TIME ZONE
CON_ID NUMBER
SQL> select TRACE_FILENAME
2 from V$DIAG_TRACE_FILE
3 order by 1;
TRACE_FILENAME
-----------------------------------------
db122_cjq0_32100.trc
db122_cjq0_3602.trc
db122_cjq0_3736.trc
db122_cjq0_6044.trc
db122_cjq0_8937.trc
db122_m001_20300.trc
db122_m001_21807.trc
db122_ora_10158.trc
db122_ora_5351.trc
db122_p000_32038.trc
db122_p000_3526.trc
db122_p000_3703.trc
db122_p000_4101.trc
db122_p000_5002.trc
db122_p000_6036.trc
db122_p000_8848.trc
...
...
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'
...
...
...
Easy as that !
Learning is not a spectator sport 
Cool!
Definitely neat. Any idea how this is implemented? Something like external tables? Or are the contents themselves stored in the database?
Great 🙂
I still remember Tom Kyte’s solution for doing this still in Oracle8i :):)
As Tom always says: Times change, things change …
But the old good and great ideas remain with us forever :):)
I am not positive where you are getting your information, but good topic. I must spend a while finding out more or understanding more. Thanks for fantastic information I was searching for this information for my mission.
Great! Nevertheless, I would be even better, if it was possible to see the output in a more human format, like when doing tkprof file_name.trc file_name.txt explain=user_name/pwd@db
Not always it is possible for regular folks to go to db server a provide output by using tkprof.
regards,
Dusan
Hello Connor,
This is really great feature.
But the fear of “handing over the reins” is not completely irrational. For e.g. with a simple SELECT_CATALOG_ROLE, I can easily access important IO metrics data (Average IO Response, MBPS and IOPS) by accessing v$sysmetric_history for last hour on a 11.2 database. But in a “database as a service” offering, I am left with access to a PDB only and these IO metrics are only available in CDB$ROOT. Unless I am missing something obvious, I think this is quite unfortunate change, mainly due to multi-tenant model, combined with DBAAS offering.
I agree. There is always going to be a balance between “As A Service” and not, that has to be found. For some, their definition of service will be “I just want a database and not even have to think about it”. For others it will “Hey, you look after the fundamentals but I need to see all of the metrics to manage my applications”
I could see , V$DIAG_TRACE_FILE_CONTENTS is completely empty in our database. But V$DIAG_TRACE_FILE has the trace file names. Any suggestions why the contents view is empty?