RE: tracing specific sql_id in 12.2

Hey Nenad,
i am a little bit surprised to see that it works when you modify the link listed in the SGA as the events are copied to PGA (event propagation): https://twitter.com/OracleSK/status/899924819835277312

Did you modify the list before the event propagation code kicks in?

Best Regards
Stefan Koehler

Independent Oracle performance consultant and researcher Website: http://www.soocs.de
Twitter: _at_OracleSK

> Noveljic Nenad <nenad.noveljic_at_vontobel.com> hat am 14. Dezember 2017 um 17:48 geschrieben:
>
> No multitenant, and tested with both new and existing 2. session. Most probably a bug - quite inconvenient.
>
> However, I’ve managed to reconstruct the doubly linked list, where the system events are stored:
>
>
> linked list ptr to root entry:
>
>
> BCFFDE18+28: *BCFFDE40 = 989423B8
>
>
> root_Entry:
>
>
> 989423B8+ 0: *989423B8 = 00000080
>
>
> 989423B8+B8: *98942470 = 96DDD200
>
>
> Traversing linked list:
>
>
> 96DDD198+68: *96DDD200 = 96DDD580
>
>
> 96DDD518+68: *96DDD580 = 98942470
>
>
> 1. element:
>
>
> 96DDD198+ 0: *96DDD198 = 02160001
>
>
> 96DDD198+28: *96DDD1C0 = 00000006
>
>
> 96DDD198+38: *96DDD1D0 = 00000000
>
>
> 96DDD198+50: *96DDD1E8 = 00000000
>
>
> 96DDD198+68: *96DDD200 = 96DDD580
>
>
> 96DDD198+70: *96DDD208 = 98942470
>
>
> 2. element:
>
>
> 96DDD518+ 0: *96DDD518 = 0216000E
>
>
> 96DDD518+28: *96DDD540 = 80000001
>
>
> 96DDD518+38: *96DDD550 = 96DDD198
>
>
> 96DDD518+50: *96DDD568 = 96DDD250
>
>
> 96DDD518+68: *96DDD580 = 98942470
>
>
> 96DDD518+70: *96DDD588 = 96DDD200
>
>
> Explanation of some important locations:
>
>
> -       BCFFDE18 is the address of x$ksmsp.ksmchcom='dbgdInitEventGr'
>
>
> -       If the third least significant bit of element’s offset+28 is set to 1, the SQL will be traced. This means that the element 1 should trigger tracing in the example above.
>
>
> -       The offsets 38 and 50 should contain the pointers to the other element (see the element 2), but curiously for the first element they are not initialized:
>
>
> 96DDD198+38: *96DDD1D0 = 00000000
>
>
> 96DDD198+50: *96DDD1E8 = 00000000
>
>
> After initializing the offset 0x50 for the first element with the values of the second element the tracing kicked in. (Of course, I did this only in the sandbox environment for experimental purposes.)
>
>
> The question is, which one in the chain of dbgd functions and under which conditions is changing this location. This might give us a clue about a possible workaround.
>
>
> The list above was produced with the following Perl program:
>
> https://github.com/nenadnoveljic/oradb/blob/master/select_x_linked_events.pl
>
>
> Nenad
> http://nenadnoveljic.com/blog/
> _at_NenadNoveljic

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