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

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
>>>
>>>
>>>

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