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
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.
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.
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.
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.
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
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.
It’s good that SQL trace now is available in the Autonomous Database.
I’m testing this in a version 184.108.40.206.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.
Unfortunately no WAIT stats yet 😦
I’m asking around internally about a timeframe for that.
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.”
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
You are of course correct. I stand corrected.
I still will be waiting for the WAIT stats. 🙂
Hi Connor, I’m with you and Lars on this. I’d love to have access to the waits and binds attributes of sql_trace, as well as control over the plan_stat attribute.
Hi Connor. Have you had any luck selecting from SESSION_CLOUD_TRACE? The documentation says we can do that, but in my experience, you can query it only if you’re connected as ADMIN. And ADMIN apparently cannot grant SELECT upon the view to anyone. I’ve logged an Oracle SR.
Yeah not ideal – but this is a workaround
SQL> conn ADMIN/xxxx@atp_low
SQL> create view admin_SESSION_CLOUD_TRACE as select * from C##CLOUD$SERVICE.SESSION_CLOUD_TRACE;
SQL> grant select on admin_SESSION_CLOUD_TRACE to scott;
SQL> conn scott/xxxx@atp_low
SQL> alter session set sql_trace = true;
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
———- ———- ——— ———- ——— ———- ———- ———-
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 09-DEC-82 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 30
7876 ADAMS CLERK 7788 12-JAN-83 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
SQL> alter session set sql_trace = false;
SQL> select count(*) from admin.admin_SESSION_CLOUD_TRACE;
Connor, thank you for the idea. Oracle ACE Pro Sayan Malakshinov suggested granting READ (instead of SELECT) on SESSION_CLOUD_TRACE to U1. It’s an elegant solution that works perfectly!