RE: tracing specific sql_id in 12.2

From: Noveljic Nenad <nenad.noveljic_at_vontobel.com>
Date: Thu, 14 Dec 2017 20:18:39 +0000
Message-ID: <28387_1513282733_5A32DCAD_28387_3041_1_ECDEF0CC6716EC4596FCBC871F48292AB1922F5A_at_ZRH-S231>

Hey Stefan,

I've understood the process as follows:

1. alter system set events... The events will be built in PGA and transferred to SGA. In particular, the individual elements of the doubly linked list will be copied by the function dbgdCopyEventNode.
2. connecting to the database It is the reversed process. The same function will copy the elements of the linked list from SGA to heap.

What I did, is updated the pointer between step 1 and 2 in the SGA, so the "corrected" configuration was copied from SGA to PGA. The whole test case is reproducible at will.

I've just uploaded the DTrace script for tracing the different function calls which are participating in the event propagation: https://github.com/nenadnoveljic/dtrace/blob/master/dbgd_flow_122.d . While both steps can be traced, you'd need to stop the dedicated process immediately after forking in Step 2, which would allow you to attach to it with the DTrace. By the way, I described this foot-in-the-door technique here: http://nenadnoveljic.com/blog/debugging-session-creation/

Best,
Nenad

http://nenadnoveljic.com/blog/
Twitter: _at_NenadNoveljic

-----Original Message-----
From: Stefan Koehler [mailto:contact_at_soocs.de] Sent: Donnerstag, 14. Dezember 2017 18:25 To: Tanel Poder; Noveljic Nenad
Cc: ORACLE-L
Subject: 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: @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

---

Please consider the environment before printing this e-mail. Bitte denken Sie an die Umwelt, bevor Sie dieses E-Mail drucken.

Important Notice
This message is intended only for the individual named. It may contain confidential or privileged information. If you are not the named addressee you should in particular not disseminate, distribute, modify or copy this e-mail. Please notify the sender immediately by e-mail, if you have received this message by mistake and delete it from your system.  

E-mail transmission may not be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete. Also processing of incoming e-mails cannot be guaranteed. All liability of the Vontobel Group and its affiliates for any damages resulting from e-mail use is excluded. You are advised that urgent and time sensitive messages should not be sent by e-mail and if verification is required please request a printed version.

†Ûiÿü0ÁúÞzX¬¶Ê+ƒün– {ú+iÉ^ Received on Thu Dec 14 2017 - 21:18:39 CET