Capturing SQL Statements

A frequently asked question by students while I am teaching DBA classes at Oracle University is: “How can I capture the SQL statements issued on my database?”.
Usually my counter question is: “Why would you?”, and quite often students reply that they have this need because of a performance related issue they are investigating. While looking at SQL statements alone without paying attention to the big picture isn’t very productive, but there are situations thinkable where this might be indeed a useful thing to do.

Thinking about the answer I came to the following list of information sources for executed SQL statements:

1. V$SQL
2. A SQL_TRACE trace file
3. DBA_SQL_PLAN_BASELINES
4. DBA_OUTLINES
5. An event 10132 trace file

As always it depends which one works best for a given situation, but first let us have a look at each one in more detail before jumping to conclusions.

V$SQL

Probably the most well known source for executed SQL statements is V$SQL, or any other V$ view that retrieves information out of the library cache, because it has been around for such a long time. Basically a V$SQL solution works as follows:

SQL> select job from scott.emp where ename = 'KING';

JOB
---------
PRESIDENT

SQL> select sql_text from v$sql where sql_text like '%scott.emp%'
  2  and sql_text not like '%v$sql%';

SQL_TEXT
--------------------------------------------------------------------------------
select job from scott.emp where ename = 'KING'

In a real world scenario you have to query V$SQL at regular intervals and store the results somewhere for later analysis. You can develop your own solution based on V$SQL, but you can also use tools like Statspack or the Automatic Workload Repository, but keep in mind that these tools only capture high profile SQL statements. Although V$SQL is easy to use I don’t recommend it because there is absolutely no guarantee that you capture all SQL statements because objects in the library cache age out after a while if not used frequently, or they can be flushed out under space pressure. If a V$SQL based solution is your only choice, keep an eye on the extra load you place on the system because querying V$SQL can be expensive. .

SQL TRACE

Another well known source for executed SQL statements is inside a SQL_TRACE trace file. This technique boils down to enable SQL_TRACE in one, Several or all sessions for a pre defined time while the application is in use and examine the generated trace files afterwards. Here is a little example:

SQL> alter session set sql_trace = true;

Session altered.

SQL> select job from scott.emp where ename = 'SCOTT';

JOB
---------
ANALYST

SQL> alter session set sql_trace = false;

Session altered.

SQL> col value new_value value
SQL> select value from v$diag_info where name = 'Default Trace File';

VALUE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/v1110/v1110/trace/v1110_ora_26412.trc

SQL> !grep scott.emp &value
select job from scott.emp where ename = 'SCOTT'

There are many ways to enable SQL_TRACE, but because I don’t recommend using this technique I will not explain them here. The reason for this is that SQL_TRACE can generate a massive amount of trace data which is probably too much for what we want to achieve. Also tracing many or all sessions can have a negative impact on your overall performance.

DBA_SQL_PLAN_BASELINES

Yet another technique is misusing the capturing part of the SQL Plan Management feature in Oracle11g. Using this technique you enable SQL execution plan capturing without the intention to reuse them. Before continuing, let us first look at a small demo:

SQL> alter system set optimizer_use_sql_plan_baselines = false;

System altered.

SQL> alter session set optimizer_capture_sql_plan_baselines = true;

Session altered.

SQL> select job from scott.emp where ename = 'MILLER';

JOB
---------
CLERK

SQL> alter session set optimizer_capture_sql_plan_baselines =false;

Session altered.

SQL> select sql_text from dba_sql_plan_baselines;

SQL_TEXT
--------------------------------------------------------------------------------
select job from scott.emp where ename = 'MILLER'

There are a few important things to remember when you choose this technique. By default Oracle11g will use execution plans stored in the SQL plan management base whenever they are available. Thus if you enable the capturing, you also enable the usage of the execution plans you capture. Therefore I recommend disabling the execution plan usage from the SQL plan base if capturing is not already enabled. Another thing to remember is to empty the SQL plan base and reset the parameters you changed back to their defaults once your investigation is over.

DBA_OUTLINES

A similar technique is the misusage of the Oracle8i (and above) SQL Plan Stability feature. This is almost identical to the misusage of SQL Plan Management, but then without the drawbacks we saw above. Again we first look at a small demo.

SQL> alter session set create_stored_outlines = prutser;

Session altered.

SQL> select job from scott.emp where ename = 'JONES';

JOB
---------
MANAGER

SQL> select sql_text from dba_outlines where category = 'PRUTSER';

SQL_TEXT
--------------------------------------------------------------------------------
select job from scott.emp where ename = 'JONES'

SQL> alter session set create_stored_outlines =false;

Session altered.

SQL> exec dbms_outln.drop_by_cat('PRUTSER')

PL/SQL procedure successfully completed.

nice about stored outlines is that they support categories which can prevent existing execution plans from being overwritten, and it makes cleaning things up much easier. Also given the fact that using the stored execution plans is switched off by default makes this technique very easy to use without causing (new) performance issues.

An event 10132 trace file

The final technique I can think of is by looking in an event 10132 trace file. This event is documented in Christian Antognini’s
Troubleshooting Oracle Performance book which is worthwhile reading. In short this event causes Oracle9i (and above) to dump SQL statements into a trace file when they are hard parsed. Here is a short demo:

SQL> ALTER SESSION SET events  '10132 trace name context forever';

Session altered.

SQL> select job from scott.emp where ename = 'ADAMS';

JOB
---------
CLERK

SQL> ALTER SESSION SET events  '10132 trace name context off';

Session altered.

SQL> col value new_value value
SQL> select value from v$diag_info where name = 'Default Trace File';

VALUE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/v1110/v1110/trace/v1110_ora_26451.trc

SQL> !grep scott.emp &value
select job from scott.emp where ename = 'ADAMS'
sql=select job from scott.emp where ename = 'ADAMS'

Although this might look like the SQL_TRACE technique we looked at before, it is actually quite different. The big difference is that a SQL_TRACE trace file records information about each and every SQL statement execution including each parse, execute and fetch call, while an event 10132 trace file only records hard parse calls whenever they occur. This results in much smaller trace files and the overall impact for an event 10132 trace is next to nothing compared to a SQL_TRACE trace.

Wrapping Up

As shown above there are several techniques available when it comes to capturing SQL statements. Some of them have more or less overhead than others, but they all work. Sometimes one can suffice by enabling a feature just for one session, while in other situations a feature has to be enabled at the system level. Some techniques store their information inside the database whilst others store there results in a trace file. Always remember to disable things once they are no longer needed, and try them out on a test system before using them on your production environment.
-Harald

About these ads