Striving for Optimal Performance
  • Blog
    • Archive
    • Categories
    • Search
  • Troubleshooting Oracle Performance
    • Description
    • Structure
    • Table of Contents
    • Forewords
    • Reviews
    • Downloadable Files
    • Addenda and Errata
    • First Edition
  • Public Appearances
    • Past Public Appearances
  • Contact
  • About

SQL Trace in Oracle Database Exadata Express Cloud Service

25 September 2016 3 Comments Written by Christian Antognini

Today I started having a look to the Oracle Database Exadata Express Cloud Service announced last week at Oracle OpenWorld. Note that since the amount of resources provided (in summary, 1 OCPU, 20 or 50 GB of database storage) is very limited, in general, in my opinion that service will only be useful for functional tests. In fact, if it wasn’t because that is the very first 12.2 release (12.2.0.0.3 according to V$VERSION) available, I doubt I would care about it…

A first important thing to know is that the service gives access to a PDB via SQL*Net only (in addition to the web-based interfaces like APEX). In other words, the OS access is precluded.

Given that no OS access is available, one of the first thing I wanted to test is whether and how SQL trace is supported. In fact, SQL trace remains a critical feature for anyone working with Oracle Database.

The first thing I tried to do is to activate SQL trace for my session through the DBMS_MONITOR package:

SQL> execute dbms_monitor.session_trace_enable
BEGIN dbms_monitor.session_trace_enable; END;
      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'DBMS_MONITOR' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

SQL> execute dbms_monitor.session_trace_enable BEGIN dbms_monitor.session_trace_enable; END; * ERROR at line 1: ORA-06550: line 1, column 7: PLS-00201: identifier 'DBMS_MONITOR' must be declared ORA-06550: line 1, column 7: PL/SQL: Statement ignored

As you can see, no access to that package is provided. BTW, I’m testing with the PDB_ADMIN user, the highest privileged user (AFAIK) that someone gets when using the service.

Given that the package DBMS_MONITOR isn’t limited to activate SQL trace for the current session, I wasn’t too surprised about it. That said, the next three attemps really surprised me:

SQL> ALTER SESSION SET sql_trace = TRUE;
ERROR:
ORA-01031: insufficient privileges
 
SQL> execute dbms_session.session_trace_enable
BEGIN dbms_session.session_trace_enable; END;
 
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SESSION", line 307
ORA-06512: at line 1
 
SQL> ALTER SESSION SET events 'sql_trace';
ERROR:
ORA-01031: insufficient privileges

SQL> ALTER SESSION SET sql_trace = TRUE; ERROR: ORA-01031: insufficient privileges SQL> execute dbms_session.session_trace_enable BEGIN dbms_session.session_trace_enable; END; * ERROR at line 1: ORA-01031: insufficient privileges ORA-06512: at "SYS.DBMS_SESSION", line 307 ORA-06512: at line 1 SQL> ALTER SESSION SET events 'sql_trace'; ERROR: ORA-01031: insufficient privileges

So, it looks like no privileges to activate SQL trace are provided. That must be a bug! Hence, as soon as I publish this post, I’ll immediated open an SR. UPDATE Sep 25, 18:45: as pointed out by Laurent Leturgez, this isn’t a bug, it’s a documented limitation.

Anyway, since I consider the previous issue a bug, I continued my research to find out how to get access to the trace files on the server. And, in similar situations (i.e. when I don’t receive the OS access), I usually take advantage of a database directory. Let’s try to create one for the directory where the trace files are stored:

SQL> SELECT value
  2  FROM v$diag_info
  3  WHERE name = 'Default Trace File';
 
VALUE
------------------------------------------------------------------------------------------------------------------------
/u02/app/oracle/diag/rdbms/cfcdbb1/cfcdbb1_1/trace/cfcdbb1_1_ora_19884.trc
 
SQL> CREATE DIRECTORY diag_trace AS '/u02/app/oracle/diag/rdbms/cfcdbb1/cfcdbb1_1/trace/';
CREATE DIRECTORY diag_trace AS '/u02/app/oracle/diag/rdbms/cfcdbb1/cfcdbb1_1/trace/'
*
ERROR at line 1:
ORA-01031: insufficient privileges

SQL> SELECT value 2 FROM v$diag_info 3 WHERE name = 'Default Trace File'; VALUE ------------------------------------------------------------------------------------------------------------------------ /u02/app/oracle/diag/rdbms/cfcdbb1/cfcdbb1_1/trace/cfcdbb1_1_ora_19884.trc SQL> CREATE DIRECTORY diag_trace AS '/u02/app/oracle/diag/rdbms/cfcdbb1/cfcdbb1_1/trace/'; CREATE DIRECTORY diag_trace AS '/u02/app/oracle/diag/rdbms/cfcdbb1/cfcdbb1_1/trace/' * ERROR at line 1: ORA-01031: insufficient privileges

Unfortunately, but understandably, also creating a directory isn’t allowed. Therefore, I told me that another way of doing it should be available. After browsing through the data dictionary I found out that a number of new dynamic performance views contains DIAG and TRACE in their name:

  • V$DIAG_APP_TRACE_FILE
  • V$DIAG_OPT_TRACE_RECORDS
  • V$DIAG_SESS_OPT_TRACE_RECORDS
  • V$DIAG_SESS_SQL_TRACE_RECORDS
  • V$DIAG_SQL_TRACE_RECORDS
  • V$DIAG_TRACE_FILE
  • V$DIAG_TRACE_FILE_CONTENTS

The last two seems to provide exactly what I was looking for. Let’s have a look to them:

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  WHERE trace_filename LIKE 'cfcdbb1_1_ora_%'
  4  ORDER BY 1;
 
TRACE_FILENAME
-------------------------
cfcdbb1_1_ora_100689.trc
cfcdbb1_1_ora_103319.trc
cfcdbb1_1_ora_104994.trc
cfcdbb1_1_ora_113270.trc
cfcdbb1_1_ora_19243.trc
cfcdbb1_1_ora_19884.trc
cfcdbb1_1_ora_34663.trc
cfcdbb1_1_ora_34665.trc
cfcdbb1_1_ora_45183.trc
cfcdbb1_1_ora_46745.trc
cfcdbb1_1_ora_4706.trc
cfcdbb1_1_ora_4857.trc
cfcdbb1_1_ora_52669.trc
cfcdbb1_1_ora_54810.trc
cfcdbb1_1_ora_76727.trc
cfcdbb1_1_ora_81232.trc
cfcdbb1_1_ora_81236.trc
 
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 = 'cfcdbb1_1_ora_19884.trc';
 
PAYLOAD
--------------------------------------------------------------------------------------
Trace file /u02/app/oracle/diag/rdbms/cfcdbb1/cfcdbb1_1/trace/cfcdbb1_1_ora_19884.trc
Oracle Database 12c Enterprise Edition Release 12.2.0.0.3 - 64bit Production
Build label:    RDBMS_12.2.0.0.3_LINUX.X64_160720
ORACLE_HOME:    /u01/app/oracle/product/12.2.0.0.3/dbhome_1
System name:    Linux
Node name:      cfcldx0170.usdc2.oraclecloud.com
Release:        2.6.39-400.264.1.el6uek.x86_64
Version:        #1 SMP Wed Aug 26 16:42:25 PDT 2015
Machine:        x86_64
Storage:        Exadata
Instance name: cfcdbb1_1
Redo thread mounted by this instance: 1
Oracle process number: 46
Unix process pid: 19884, image: oracle@cfcldx0170.usdc2.oraclecloud.com
 
 
*** 2016-09-25T14:10:17.628206+00:00 (XZECXPHEEJ(10))
 
*** SESSION ID:(636.380) 2016-09-25T14:10:17.628239+00:00
*** CLIENT ID:() 2016-09-25T14:10:17.628244+00:00
*** SERVICE NAME:(xzecxpheej.usdc2.oraclecloud.com) 2016-09-25T14:10:17.628248+00:00
*** MODULE NAME:(SQL*Plus) 2016-09-25T14:10:17.628253+00:00
*** ACTION NAME:() 2016-09-25T14:10:17.628257+00:00
*** CLIENT DRIVER:(SQL*PLUS) 2016-09-25T14:10:17.628261+00:00
*** CONTAINER ID:(10) 2016-09-25T14:10:17.628265+00:00
 
Backoff - 3, time increment - 60000
 
*** 2016-09-25T14:13:49.831644+00:00 (XZECXPHEEJ(10))
 
Backoff - 3, time increment - 60000
 
*** 2016-09-25T14:23:34.711288+00:00 (XZECXPHEEJ(10))

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 WHERE trace_filename LIKE 'cfcdbb1_1_ora_%' 4 ORDER BY 1; TRACE_FILENAME ------------------------- cfcdbb1_1_ora_100689.trc cfcdbb1_1_ora_103319.trc cfcdbb1_1_ora_104994.trc cfcdbb1_1_ora_113270.trc cfcdbb1_1_ora_19243.trc cfcdbb1_1_ora_19884.trc cfcdbb1_1_ora_34663.trc cfcdbb1_1_ora_34665.trc cfcdbb1_1_ora_45183.trc cfcdbb1_1_ora_46745.trc cfcdbb1_1_ora_4706.trc cfcdbb1_1_ora_4857.trc cfcdbb1_1_ora_52669.trc cfcdbb1_1_ora_54810.trc cfcdbb1_1_ora_76727.trc cfcdbb1_1_ora_81232.trc cfcdbb1_1_ora_81236.trc 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 = 'cfcdbb1_1_ora_19884.trc'; PAYLOAD -------------------------------------------------------------------------------------- Trace file /u02/app/oracle/diag/rdbms/cfcdbb1/cfcdbb1_1/trace/cfcdbb1_1_ora_19884.trc Oracle Database 12c Enterprise Edition Release 12.2.0.0.3 - 64bit Production Build label: RDBMS_12.2.0.0.3_LINUX.X64_160720 ORACLE_HOME: /u01/app/oracle/product/12.2.0.0.3/dbhome_1 System name: Linux Node name: cfcldx0170.usdc2.oraclecloud.com Release: 2.6.39-400.264.1.el6uek.x86_64 Version: #1 SMP Wed Aug 26 16:42:25 PDT 2015 Machine: x86_64 Storage: Exadata Instance name: cfcdbb1_1 Redo thread mounted by this instance: 1 Oracle process number: 46 Unix process pid: 19884, image: oracle@cfcldx0170.usdc2.oraclecloud.com *** 2016-09-25T14:10:17.628206+00:00 (XZECXPHEEJ(10)) *** SESSION ID:(636.380) 2016-09-25T14:10:17.628239+00:00 *** CLIENT ID:() 2016-09-25T14:10:17.628244+00:00 *** SERVICE NAME:(xzecxpheej.usdc2.oraclecloud.com) 2016-09-25T14:10:17.628248+00:00 *** MODULE NAME:(SQL*Plus) 2016-09-25T14:10:17.628253+00:00 *** ACTION NAME:() 2016-09-25T14:10:17.628257+00:00 *** CLIENT DRIVER:(SQL*PLUS) 2016-09-25T14:10:17.628261+00:00 *** CONTAINER ID:(10) 2016-09-25T14:10:17.628265+00:00 Backoff - 3, time increment - 60000 *** 2016-09-25T14:13:49.831644+00:00 (XZECXPHEEJ(10)) Backoff - 3, time increment - 60000 *** 2016-09-25T14:23:34.711288+00:00 (XZECXPHEEJ(10))

As you can see, provided it’ll be possible to create a trace file, the view V$DIAG_TRACE_FILE_CONTENTS provides access to its content.

Note that in my case all other dynamic performance views mentioned above return no rows. So, further investigation is necessary… But, based on their names, I expect that they return part of the information stored in a trace file.

12cR2, Oracle Cloud, SQL Trace
Statement-level PARALLEL Hint
Adaptive Query Optimization Configuration: Parameters, Preferences and Fix Controls

3 Comments

1 Ping/Trackback

  1. Jeff Holt Jeff Holt
    8 December 2016    

    Wasn’t there an old, old (version 6 and earlier) trick of adding some text to the SQL statement that would cause it to start tracing? Even if it worked, I wouldn’t consider it extremely helpful but it might be an interesting exercise.

    Reply
    • Christian Antognini Christian Antognini
      9 December 2016    

      Hi Jeff

      I’m not aware of a pre-7.0 trick (I started using Oracle Database with version 7.0.15). So, if you remember how to do it, I would be happy to test it.

      Cheers,
      Chris

      Reply
  2. TKPROF’s Argument PDBTRACE – Cloud Data Architect TKPROF’s Argument PDBTRACE – Cloud Data Architect
    22 March 2018    

    […] version 12.2 to access them (e.g. V$DIAG_TRACE_FILE and V$DIAG_TRACE_FILE_CONTENTS; have a look to this post for some basic information about […]

  1. TKPROF’s Argument PDBTRACE – Cloud Data Architect on 22 March 2018 at 06:19

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.