Re: Anyway of specifying lifetime when tracing SQL_ID?

Since SQL_TRACE is just enabling of trace event #10046, and

https://blog.tanelpoder.com/2009/03/03/the-full-power-of-oracles-diagnostic-events-part-1-syntax-for-ksd-debug-event-handling/

> 10046 on the other hand is not an actual error, but just a condition which
> is checked in few locations in Oracle kernel code (such OPI parse, execute,
> fetch and wait interface functions). Whenever these functions run, they
> check whether an event number 10046 is enabled for current process or
> session and if yes, then take appropriate action (which normally means
> tracing).

so occurence is just one call of this trace event check.

ps. A bit more details about that:
https://alexanderanokhin.com/2012/12/24/timing-query-execution-statistics-rowsource-statistics-part-1-how-it-works/

On Wed, Sep 4, 2019 at 7:27 PM Mikhail Velikikh <mvelikikh_at_gmail.com> wrote:

> Hi folks,
>
> Occurence (with one 'r') is not the number of executions as Jonathan Lewis
> blogged about recently:
> https://jonathanlewis.wordpress.com/2019/05/03/occurence/
> It is something else (maybe the number of times the diagnostic event was
> invoked).
> Yet, it is still possible to limit the amount of data written by SQL_TRACE
> using appropriate numbers.
>
> Regards,
> Mikhail.
>
>
>
>
> <https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail> Virus-free.
> www.avast.com
> <https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail>
> <#m_5845853417855480143_DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>
>
> On Wed, 4 Sep 2019 at 17:09, Stefan Koehler <contact_at_soocs.de> wrote:
>
>> Hello Mladen,
>> I am on road and have no Oracle database at hand right now to test it but
>> the occurrence filter should do it if you specify an interval of 1 or +1.
>>
>> SQL> ORADEBUG DOC EVENT FILTER occurence
>> occurence: filter to implement counting for event checks
>>
>> Usage
>> -------
>> {occurence: start_after <ub4>,
>> end_after <ub4> }
>>
>>
>> Best Regards
>> Stefan Koehler
>>
>> Independent Oracle performance consultant and researcher
>> Website: http://www.soocs.de
>> Twitter: _at_OracleSK
>>
>> > Mladen Gogala <gogala.mladen_at_gmail.com> hat am 4. September 2019 um
>> 02:13 geschrieben:
>> >
>> >
>> > I can trace an error and specify number of occurrences that I want
>> traced:
>> >
>> > SQL> alter session set events '942 trace name errorstack lifetime 3,
>> > level 12';
>> > Session altered.
>> >
>> > That will only trace next 3 "table not found" errors.
>> >
>> > I know that I can do something like this:
>> >
>> > SQL> alter session set events='sql_trace[sql: 77hjjr9qgwtzm] level 16';
>> > Session altered.
>> >
>> > However, when I try using lifetime with sql_id syntax, it doesn't work.
>> > Is it possible to trace just first invocation of that SQL? The problem
>> > is that the SQL I am interested in is executed in a loop and I don't
>> > want a ginormous trace file.
>> >
>> > --
>> > Mladen Gogala
>> > Database Consultant
>> > Tel: (347) 321-1217
>> >
>> > --
>> > http://www.freelists.org/webpage/oracle-l
>> --
>> http://www.freelists.org/webpage/oracle-l
>>
>>
>>

-- 
Best regards,
Sayan Malakshinov
Oracle performance tuning engineer
Oracle ACE Associate
http://orasql.org

--
http://www.freelists.org/webpage/oracle-l