The Dutch Prutser's Blog

By: Harald van Breederode

  • Disclaimer

    The views expressed on this blog are my own and do not necessarily reflect the views of Oracle.
  • Subscribe

  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 244 other followers

Capturing SQL Statements

Posted by Harald van Breederode on January 11, 2009

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

12 Responses to “Capturing SQL Statements”

  1. Rick van Ek said

    Hello Harold, I enjoyed reading your blog. No GUI stuff but just the power of a plain command line. Showing where it is about, simple and straight forward. To my opinion the best way to show how it works.

  2. Bjørn Engsig said

    Event 10132 does actually not cause lines to be printed in the trace file when you do a hard parse. In stead, it causes the optimizer to dump the execution plan when it is generated. These two things often appear right after each other, but this is not necessarily true. You can parse a statement without generating an execution plan, e.g. using the DBMS_SQL package; and an execution plan can be generated even when you don’t do hard parses, e.g. if a bind value changes sufficiently in Oracle11.

  3. Harald van Breederode said

    Bjorn,

    Thank you for this clarification.

    -Harald

  4. Surachart said

    Hi Harald
    that’s great … thank you.

  5. Patty C. said

    Harald,

    We met at UKOUG and I found your blog by way of OraNA and enjoyed reading what you have posted. Thank-you for taking the time to do this!

    Patty C.

  6. Sandeep said

    Excellent post!. In fact yesterday I was trying a a similar stuff at customer site and 10132 would have been to good to experience something new!. It is good to have you on the blogging world sharing practical tips for other DBAs.

    cheers
    Sandeep

  7. [...] 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,” and his answer lists five [...]

  8. Wouter Wethmar said

    Why didn’t I found this blog before . . . . . . .
    I missed it, I like it and I hope there wil de more . . . . .

    Question :
    Starting with Oracle version 10.2.0.4 you can record database input to replay it again…. I quess that is also on a sql level . . . . . Is there a way to use the replay ‘output’ to capture aql statements ?

  9. Harald van Breederode said

    Hi Wouter,

    You can also capture a workload on a 9.2.0.8.0 database, but since we capture at a pretty low level I don’t think you can extract the SQL out of it directly. You can of course capture the SQL while replaying.
    -Harald

  10. andreas said

    the 10132 event seems only to write SQL statements with a different execution plan.

    Having some similar select statements only the first is shown because the execution plan won’t be recalculated.

    All of this SQL dumping on Oracle is awful, somw ways are only possible with tools available only from metalink…. no GUI…. MS provides everything for free, has a GUI for trace files and so on. Nevertheless Oracle is a constant source for income, because noone does really KNOW more than some small pieces of knowledge and everything is scattered around.

  11. venkatanathen said

    Hi,
    By executing the below stmt,
    alter system set optimizer_use_sql_plan_baselines = false; or alter system set optimizer_use_sql_plan_baselines =
    true;

    I am getting the following error “ORA-02065: illegal option for ALTER SYSTEM”. Please let me know if I need to enable any additional parameter.

    My DB Version is 10.2.0.3

  12. venkatanathen said

    Oh,

    I found, that this new feature is introduced in 11g.

    Thank you guys.
    –VK

Leave a Reply

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 )

Google+ photo

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

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 244 other followers

%d bloggers like this: