Oraclue

Oracle internals, debugging and undocumented features

Oracle diagnostic events Part 5

I know I already did four blogs on events but there is more to cover.

This short post will focus on some of the  trace option and it’s components.

trace[target ]<components>

In previous post I have used example something like:

alter session set events ‘trace[sql_mon] memory=high,get_time=highres’;

where sql_mon is target of interest

Just for reference here is list of targets that I have already posted:

bind_capture  –  capturing binds

dirpath_load  – direct load

explain       –  explain plan

sql_apa       – access path analysis

sql_analyze   –  sql analyze

sql_perf      –  performance analyzer

sql_tune      –  tuning advisor

sql_mon       –  sql monitor

sql_optimizer –  optimizer

sql_planmanagement – plan management

sql_costing        – cost based analysis

sql_transform     – sql transformation

sql_execution     – sql execution

Now let’s see what other options I can use in conjuction with target.

In my previous example I have used options memory and get_time :

alter session set events ‘trace[sql_mon] memory=high,get_time=highres’;

so this command will trace in memory sql monitor on high resolution.

Here is list of all combination:

[disk=DISABLE | LOW | LOWEST | MEDIUM | HIGH | HIGHEST]

[memory=DISABLE | LOW | LOWEST | MEDIUM | HIGH | HIGHEST]

[get_time=DISABLE | DEFAULT | SEQ | HIGHRES | SEQ_HIGHRES ]

[get_stack=DISABLE | DEFAULT | ENABLED ]

These options are self explanatory.

Just a few examples:

alter session set events ‘trace[sql_mon] memory=high,get_time=highres’;

alter session set events ‘trace[sql_mon] disk=highest,get_time=highres,get_stack=default’;

Advertisements

2 responses to “Oracle diagnostic events Part 5

  1. Mladen Gogala June 18, 2009 at 11:26 am

    Miladin, I am unclear about the memory option. What does the memory=high option means and what additional information will be provided in the resulting trace file? Keep up the good work!

  2. oraclue June 18, 2009 at 5:32 pm

    For example this command:

    alter session set events ‘trace[sql_mon.*] memory=high’;

    will trace in-memory SQL_MONITOR component ( target ) and all sub component at level high.
    There are different targets that you can use.I already posted some of them.Others I intentionally left out ( dangers etc ).

    I did not have a chance to play much with all of these events options because there is so many of them.
    It took me long time just to post a correct syntax.That’s how big event syntax is.And I am not finished yet.More to come.

    Best way would be to run these command with different levels and see the difference.
    I will do it when I get some free time.

    Thanks,

    Miladin

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

%d bloggers like this: