There are so many great performance monitoring tools that have come along in the Oracle Database since 10g - things like AWR, ASH, ADDM, SPM...the list of acronyms is seemingly endless Smile

But sometimes, for those really really hard to nut out problems, you need to go back to one of the fundamental diagnostic tools: SQL Trace, and up until recently, that has not been possible on most of our fully managed cloud database offerings. Trace files require OS access which of course is typically a non-starter for managed databases.

However, a few tweaks to the database engine and now trace files can be stored in an object store in your cloud account, which opens up more performance tuning opportunities for developers and DBAs. Here's a quick walkthrough of the process, but make sure you also check out the complete docs here.

Bucket setup

Once you've logged into Oracle Cloud, go into the Storage option from the cloud menu and choose Buckets, and create a new bucket. Take note of the namespace because you'll need that for the fully qualified name. Alternatively, once you've created your bucket, simply upload a sample file into it and click Details. The full link to the file can be used to get the bucket link.

image

Cloud Credentials

You'll need an Auth Token in order store some database credentials to access the bucket from within the database, and you'll need to know your full username as well.  So click on the Profile at the top right and go to User Settings.

image

So whilst the username I log in with is "connor" my full username is "oracleidentitycloudservice/connor" which is what I'll need for the database credentials.

Next I create an Auth Token which I'll also end. Make sure you note it down, because once created you cannot go back and access the auth value again.

image

Database Credentials

With the username and auth token, you can now configure access to the bucket with a database credential. Once created, do a LIST_OBJECTS call to ensure that access to the bucket is working correctly from the database.

  SQL> BEGIN   2        DBMS_CLOUD.CREATE_CREDENTIAL(   3          credential_name => 'DEF_CRED_NAME',   4          username => 'oracleidentitycloudservice/connor',   5          password => 'WasdasdZDirYasdasd'  -- not my real token :-)   6      );   7  END;   8  /  PL/SQL procedure successfully completed.  SQL> select count(*)   2  from dbms_cloud.LIST_OBJECTS('DEF_CRED_NAME','https://objectstorage.....com/n/mynamespace/b/bucket1/o/');    COUNT(*) ----------          0

Start tracing!

Now you can set defaults in the database to let it know where session tracing files should be stored and then start tracing!

  SQL> alter database property   2    set default_credential = 'ADMIN.DEF_CRED_NAME';  Database altered.  SQL> alter database property   2    set default_logging_bucket = 'https://objectstorage....com/n/mynamespace]/b/bucket1/o/';  Database altered.

Note: The trailing slash after the /o/ is important, since this is setting the "root" from where trace files will be created.

  SQL> alter session set sql_trace = true;  Session altered.  SQL> select count(*) from emp;    COUNT(*) ----------         14  SQL> select count(*) from dept;    COUNT(*) ----------          4  SQL> alter session set sql_trace = false;  Session altered.  SQL> select object_name, bytes   2  from   dbms_cloud.LIST_OBJECTS('DEF_CRED_NAME','https://objectstorage....com/n/mynamespace]/b/bucket1/o/');  OBJECT_NAME                                   BYTES ---------------------------------------- ---------- /sqltrace/sqltrace_9149_29286.trc            100540

The default trace file name seems to be tied to the SID and SERIAL#:

  SQL> select sid, serial#   2  from v$session   3  where sid = sys_context('USERENV','SID');         SID    SERIAL# ---------- ----------       9149      29286

Some of the facilities you might use on-premises will not be available, for example, the tracefile identifier:

 SQL> alter session set tracefile_identifier = connor; ERROR: ORA-01031: insufficient privileges

However, the various settings available via DBMS_APPLICATION_INFO can be used to add some more structure to how the trace files will be stored in the bucket. In the example below, the MODULE value at the time of tracing being ended is used to store the trace file.

 SQL> exec dbms_application_info.set_module('Connor', null);  PL/SQL procedure successfully completed.  SQL> alter session set sql_trace = true;  Session altered.  SQL> select count(*) from emp;    COUNT(*) ----------         14  SQL> exec dbms_application_info.set_module('McDonald', null);  PL/SQL procedure successfully completed.  SQL> select count(*) from dept;    COUNT(*) ----------          4  SQL> alter session set sql_trace = false;  Session altered.  SQL> SQL> select object_name, bytes   2  from   dbms_cloud.LIST_OBJECTS('DEF_CRED_NAME','https://objectstorage....com/n/mynamespace]/b/bucket1/o/');  OBJECT_NAME                                      BYTES ------------------------------------------- ---------- /sqltrace/McDonald/sqltrace_24819_30267.trc       3143 /sqltrace/sqltrace_9149_29286.trc               100540

There will possibly be more evolution of the functionality as time goes on, but even with this first release, the valuable function of tracing is now available on your managed databases.