Re: set events: control the number of sessions that dump trace

Hi Mladen,

That MOS article that Jared mentioned has some examples of setting event created incidents: Monitoring 11g Database Alert Log Errors in Enterprise Manager 10g and 11g Grid Control (Doc ID 949858.1) Other than that, there are also some ORADEBUG commands showing how to create incidents, namely:

SQL> oradebug doc event
...

• Create an incident labeled 'table_missing' when external error 942 is signaled by process id 14534: alter session set events '942 {process: 14534} incident(table_missing)'; ...

<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> <#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>

On Wed, 21 Aug 2019 at 00:59, Mladen Gogala <gogala.mladen_at_gmail.com> wrote:

> Hi Mikhail!
>
> Where can see the syntax for alter system set events='<errno> incident'.
> Is there a document which describes the syntax?
>
> I was unable to find it.
>
> TIA
> On 8/20/19 4:56 PM, Mikhail Velikikh wrote:
>
> It looks like you have not fired any incidents. The key thing in my
> message was that incident keyword in the event syntax:
> alter system set events '1555 *incident*(sto)';
>
> By doing that, any ORA-1555 will lead to a user-defined incident
> generation. That message about the flood-controlled incident, I actually
> copied from the instance where I tested ORA-1555 with the exact event
> syntax that I used in my message.
>
> I will try to do more tests tomorrow.
>
> On Tue, Aug 20, 2019, 21:38 Jared Still <jkstill_at_gmail.com> wrote:
>
>> I did some tests, the results were not quite what I expected, but in any
>> case, flood control was not invoked.
>>
>> Note: hopefully this formatting is preserved by the list server.
>>
>> Testing ORA-1555 for Flood Control
>>
>> When setting system events to trap an error and dump a stack trace, there is the potential for many files to be generated.
>>
>> Oracle sometimes employs an internal 'flood control' when many errors of the same type occur to prevent using too many resources to store the same msg over and over.
>>
>> This experiment is to see if flood control might be employed when trace files are forced via 'alter system set events'.
>>
>> sqlrun was used to start many sessions running the same SQL over and over
>> https://github.com/jkstill/sqlrun
>>
>> Enable ORA-1555 events
>>
>>
>> *
>> sqlplus -L -s jkstill/XXX_at_p1 << EOF
>> set feedback off term off heading off
>> alter system set events '1555 trace name errorstack level 3, lifetime 2';
>> exit;
>> EOF
>> *
>>
>> Note: when run without the lifetime argument, default lifetime appears to
>> be 1. That is, just one event dumped per session. The SQL being executed
>>
>> *
>> declare
>> ora1555 exception;
>> pragma exception_init(ora1555,-1555);
>> begin
>> -- the error will still cause a trace file to be created via the 'set events' command
>> -- without causing the script to fail
>> begin
>> raise ora1555;
>> exception
>> when others then null;
>> end;
>> end;
>> *
>>
>> When run with 35 sessions, each executing the SQL every .1 seconds for 60
>> seconds, there was not any flood control employed, both without the
>> lifetime argument, and with lifetime 2. With a lifetime of 20, there was
>> still only a single stack trace created per session. Earlier experiments
>> via sqlplus did generate multiple call stacks via the 'set events', so I
>> don't know why this is acting differently. In any case, flood control was
>> not invoked.
>>
>>
>>
>> On Tue, Aug 20, 2019 at 12:18 PM Jared Still <jkstill_at_gmail.com> wrote:
>>
>>> Thank you Mikhail.
>>>
>>> I certainly have seen a number of trace files with 'flood controlled' in
>>> them due to a large volume of the same error.
>>>
>>> Simple enough to test this, going to go try it now.
>>>
>>>
>>>
>>> On Tue, Aug 20, 2019 at 11:55 AM Mikhail Velikikh <mvelikikh_at_gmail.com>
>>> wrote:
>>>
>>>> Hi Jared,
>>>>
>>>> It is possible to setup an event generated incident like this:
>>>>
>>>> alter system set events '1555 incident(sto)';
>>>>
>>>> Then, a standard flood-control kicks in after a certain number of
>>>> incidents (please see the following MOS article for the thresholds:
>>>> DIA-49435 When Trying To Create An Incident Packet (Doc ID 1358087.1)).
>>>>
>>>> For instance, in my 19c database I have got five incidents and the
>>>> sixth was flood controlled.
>>>> Here is a relevant excerpt from the trace file:
>>>>
>>>> DDE: Problem Key 'ORA 700 [EVENT_CREATED_INCIDENT] [1555] [STO]' was
>>>> flood controlled (0x2) (incident: 111062)
>>>>
>>>> That is not exactly what is required but it is the closest I can come
>>>> up with.
>>>>
>>>> Best regards,
>>>> Mikhail Velikikh
>>>>
>>>> On Tue, 20 Aug 2019 at 17:49, Jared Still <jkstill_at_gmail.com> wrote:
>>>>
>>>>> Hello,
>>>>>
>>>>> Does anyone know if it is possible to control the total number of
>>>>> trace files created by 'alter system set events' ?
>>>>>
>>>>> For example, the following command will cause all sessions to dump
>>>>> stack traces when there have been at least 2 occurrences (armcount) of
>>>>> ORA-1555 in a session:
>>>>>
>>>>> alter system set events '1555 trace name errorstack level 3,
>>>>> armcount 2, lifetime 2';
>>>>>
>>>>> After 2 events have occurred (lifetime) then the session will stop
>>>>> dumping stacks for this error.
>>>>>
>>>>> What I would like to be able to do is limit this system wide.
>>>>>
>>>>> Say for instance 3 sessions dump stack traces for ORA-1555.
>>>>>
>>>>> I would like to limit this to N system wide dumps of stack trace.
>>>>>
>>>>> So after 3 sessions have created a trace file, no other sessions
>>>>> should do so.
>>>>>
>>>>> The reason is that if left unattended, when many session incur
>>>>> ORA-1555, then this could potentially be quite a few rather large files.
>>>>>
>>>>> The idea of course is to be able to set and forget, until the next
>>>>> event.
>>>>>
>>>>> And yes, I know that TFA could be setup to deal with this, but that is
>>>>> another story...
>>>>>
>>>>>
>>>>> Jared Still
>>>>> Certifiable Oracle DBA and Part Time Perl Evangelist
>>>>> Principal Consultant at Pythian
>>>>> Pythian Blog http://www.pythian.com/blog/author/still/
>>>>> Github: https://github.com/jkstill
>>>>>
>>>>>
>>>>> --
> Mladen Gogala
> Database Consultant
> Tel: (347) 321-1217
>
>
<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> <#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>

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