SQL trace on your cloud database

Posted by

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.

5 comments

  1. Hi Connor

    It’s good that SQL trace now is available in the Autonomous Database.
    I’m testing this in a version 19.4.0.1.0 Autonomous Database in region eu-stockholm-1.
    It’s seems that the trace functionality don’t emit “WAIT” lines to the trace file.

    I have ensured that timed_statistics=true by calling “alter session set statistics_level=ALL;” before enabling trace with “alter session set sql_trace = true;”
    I even validated with “select name, value from GV$PARAMETER where name like ‘timed_statistics’;”

    Am I doing something wrong?
    Is this due to the version 19c? I’m not able to test in a region where 21c is installed because I only have a Free account and then limited to one region.
    Is this a bug/limitation in the trace functionality in Autonomous Database?

    I really would love to see the “WAIT” lines in the trace files.

      1. OK. Thanks for the reply.
        Is there a feature request for this that we can vote up?
        Using tkprof to process the trace file as described in the docs will not give much value.

        Or to use the words of AskTom:
        “using sql_trace without timed_statistics is useless. Without the timings, the tkprof report is virutally (not entirely) useless.”
        😉

        1. yes (and whilst I 100% on having the WAIT stats), timed_statistics is not about the wait stats but the timings – which *are* there in all versions including autonomous

Got some thoughts? Leave a comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

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