Let's Trace! Dive into SQL Tracing

 Let’s get into the world of tracing, Find ways to uncover mysteries and optimizing performance along the way using sessions and tracings.

Step 1: Setting the Scene

let’s activate SQL tracing on our current session. But before this, we must ensure our filesystem have enough space to capture all the action.

-- Set maximum trace file size
ALTER SESSION SET max_dump_file_size='4000m';

-- Set a cool prefix for the trace file
ALTER SESSION SET tracefile_identifier ='perf_test';

Step 2: Proceed with tracing 

We have various levels of tracing to choose from, each revealing different insights into our SQL queries.

• Basic SQL Trace: Just the essentials.
• Basic SQL Trace + Bind Variables: Peek into the bound parameters.
• Basic SQL Trace + Wait Events: Discover what your queries are waiting for.
• Basic SQL Trace + Bind Variables + Wait Events: The ultimate tracing experience!

-- Activate basic SQL trace
ALTER SESSION SET events '10046 trace name context forever, level 1';

-- Ready for more? Activate higher levels of tracing using below options

-- activate basic sql_trace + bind variables
alter session set events '10046 trace name context forever, level 4';

-- activate basic sql_trace + wait events
alter session set events '10046 trace name context forever, level 8';

-- activate basic sql_trace + bind variables + wait events
alter session set events '10046 trace name context forever, level 12';

Leveling Up with Logon Triggers

If you want to automatically trace a specific user upon login, it’s as easy and here is a sample trigger to achieve this.

-- Activate SQL tracing on user by logon trigger
CREATE OR REPLACE TRIGGER active_sql_trace_on_user
AFTER logon ON database
DECLARE
    v_user dba_users.username%TYPE := user;
    sql_stmt1 VARCHAR2(256) := 'ALTER SESSION SET events '||CHR(39)||'10046 trace name context forever, level 12'||CHR(39);
BEGIN
    IF (v_user = 'SCOTT') THEN
        EXECUTE IMMEDIATE sql_stmt1;
    END IF;
END;
/

Conclusion

While armed with these tracing techniques, you’re ready to optimize your queries and conquer performance bottlenecks like a true SQL sorcerer. 

Happy tracing !