RE: tracing specific sql_id in 12.2
Date: Thu, 14 Dec 2017 16:48:48 +0000
Message-ID: <28387_1513270140_5A32AB7C_28387_2362_1_ECDEF0CC6716EC4596FCBC871F48292AB19229F1_at_ZRH-S231>
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
96DDD198+ 0: *96DDD198 = 02160001
96DDD198+28: *96DDD1C0 = 00000006
96DDD198+38: *96DDD1D0 = 00000000
96DDD198+50: *96DDD1E8 = 00000000
96DDD198+68: *96DDD200 = 96DDD580
96DDD198+70: *96DDD208 = 98942470
- 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
From: tanel_at_poderc.com [mailto:tanel_at_poderc.com] On Behalf Of Tanel Poder
Sent: Donnerstag, 14. Dezember 2017 17:15
To: Noveljic Nenad
Cc: ORACLE-L (oracle-l_at_freelists.org)
Subject: Re: tracing specific sql_id in 12.2
Maybe there's some bug in 12.2 with event propagation when using ALTER SYSTEM, maybe a variation of what's described here:
... Although eventdump seems to show the event is enabled for the session too. Did you start session 2 before or after issuing ALTER SYSTEM in st?
If 12.2 happens to use the Multitenant config, perhaps test running the ALTER SYSTEM at the root CDB level...
--
Tanel Poder
http://blog.tanelpoder.com
On Tue, Dec 12, 2017 at 5:02 PM, Noveljic Nenad <nenad.noveljic_at_vontobel.com<mailto:nenad.noveljic_at_vontobel.com>> wrote: Has somebody tried tracing a specific sql_id in Oracle 12.2? The feature doesn’t seem to work properly.
Here’s the test case:
Session 1:
alter system set events='sql_trace[sql: 2asugy1n1r7y7] level=12' ;
SQL> alter system set events='sql_trace[sql: 2asugy1n1r7y7] level=12' ;
System altered.
Please consider the environment before printing this e-mail. Bitte denken Sie an die Umwelt, bevor Sie dieses E-Mail drucken.
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<style type="text/css">p { font-family: Arial;font-size:9pt }</style>
</head>
<body>
<p>
<br>Important Notice</br>
<br>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.</br>
<br>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.<br/>
</p>
</body>
</html>
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Dec 14 2017 - 17:48:48 CET