Re: sql_trace filtering

From: Mikhail Velikikh <mvelikikh_at_gmail.com>
Date: Wed, 22 Dec 2021 21:04:34 +0000
Message-ID: <CALe4HpmdSXCHxA-R620c=wupR6ga1wegR8N3ud2d3BTzzf4wPQ_at_mail.gmail.com>

Hi Martin,

As Sayan pointed out, I got the username() function from `oradebug doc event action username`:

> SQL> oradebug doc event action username
> username
> - Return user log-in name
> Usage
> -------
> username( Action returns: <string>[30])
>

There are some other functions in `oradebug doc event action` and filters in `oradebug doc event filter`.
Regarding action, module, client_id, I do not know how to obtain them in a similar manner.
There is another function/action varaddr that I would use for that:

SQL> oradebug doc event action varaddr
> varaddr
> - Return address of a fixed PGA/SGA/UGA variable
> Usage
> -------
> varaddr( varname <string>[32],
> Action returns: <pointer>)
>

We need to know the name of a variable similar to what Jonathan Lewis described here: https://jonathanlewis.wordpress.com/2010/12/09/geek-stuff-2/ I reviewed some memory dumps and come up with this cryptic command to trace a certain SQL statement for a specific client_id:

> alter system set events 'sql_trace[sql:&sql_id.]
> {streq:refs(refp(varaddr("ksmuh_p"), 16), 4888), "CLIENT_ID_TO_TRACE"}';
>

ksmuh_p is a UGA variable that has V$SESSION.SADDR at offset 16 (oradebug dumpvar uga ksmuh_p; I performed a few global_area dumps and got the first variable that seems to have SADDR in it). 4888 - is the offset of CLIENT_IDENTIFIER in V$SESSION in *21.4*:

SQL> select c.kqfconam column_name,

       c.kqfcodty datatype,
       c.kqfcosiz size_byte,
       c.kqfcooff offset
  from x$kqfta t,
       x$kqfco c
 where t.kqftanam = 'X$KSUSE'

   and c.kqfcotab = t.indx
   and c.kqfconam = 'KSUSECLID'
 order by c.indx
/

COLUMN_NAME DATATYPE SIZE_BYTE OFFSET --------------- ---------- ---------- ----------

KSUSECLID                1         64       *4888*

NB: I specifically chose CLIENT_IDENTIFIER since both MODULE and ACTION have offset 0 - right now I do not know how to get them. *refs* and *refp* - oradebug actions to dereference a pointer to a string and a pointer correspondingly:

> SQL> oradebug doc event action refs
> refs
> - Dereference ptr-to-string: *(oratext **)(((ub1*)<ptr>) +
> <offset>)
> Length is optional; NULL-terminated string is assumed
> Usage
> -------
> refs( ptr <pointer>,
> offset <ub8> default '0',
> length <ub8>,
> Action returns: <string>[256])
>

SQL> oradebug doc event action refp
> refp
> - Dereference ptr-to-ptr: *(ub1**)(((ub1*)<ptr>)) + <offset>)
> Usage
> -------
> refp( ptr <pointer>,
> offset <ub8> default '0',
> Action returns: <pointer>)
>

Here is a short Gist demonstrating how it works: https://gist.github.com/mvelikikh/afe001b9a46fe879f3e681f975ead884

On Wed, 22 Dec 2021 at 16:29, Martin Berger <martin.a.berger_at_gmail.com> wrote:

> Hi Mikhail,
>
> that looks great!
> Do you know of any other values we can compare? (e.g. action, module,
> client_id)
> Or more generic: where did you get the username() from?
>
> thank you,
> Martin
>
> Am Di., 21. Dez. 2021 um 16:59 Uhr schrieb Mikhail Velikikh <
> mvelikikh_at_gmail.com>:
>
>> Is it possible to add a filter for user ID/name as well? I've been
>>> searching, but can't seem to find any examples of this if so.
>>>
>>
>> Yes, it is indeed possible with a proper syntax, e.g.:
>>
>> alter system set events 'sql_trace[sql:bqka14bvd2zmb]
>> {streq:username(),"TC1"}';
>>
>> Please see a full Gist:
>> https://gist.github.com/mvelikikh/27a488e9104e63a55261c480b70a86f6
>>
>> Yes, of course, for example, you can specify a filter by process: ospid
>>> or orapid or pname:
>>>
>>
>> You cannot filter by username with the process filter.
>>
>> On Tue, 21 Dec 2021 at 15:39, Sayan Malakshinov <xt.and.r_at_gmail.com>
>> wrote:
>>
>>> Hi Adric,
>>>
>>> Yes, of course, for example, you can specify a filter by process: ospid
>>> or orapid or pname:
>>>
>>> {process: ospid <string>[20],
>>> orapid <ub4>,
>>> pname <string>[20],
>>> con_id <ub8> }
>>>
>>>
>>> http://orasql.org/2021/05/20/oracle-diagnostic-events-cheat-sheet/
>>>
>>>
>>> http://orasql.org/files/events/doc/event/filter/oradebug-doc-event-filter-process.php
>>>
>>>
>>>
>>> On Tue, Dec 21, 2021 at 6:30 PM Gogala, Mladen <gogala.mladen_at_gmail.com>
>>> wrote:
>>>
>>>> *CAUTION: This email has originated from an extremely suspect and
>>>> sometimes very grumpy sender. Proceed with extreme caution and use the
>>>> Force.*
>>>> On 12/21/2021 10:14 AM, Adric Norris wrote:
>>>>
>>>> Is it possible to add a filter for user ID/name as well? I've been
>>>> searching, but can't seem to find any examples of this if so.
>>>>
>>>> Thanx!
>>>>
>>>> Hi Adric!
>>>>
>>>> You can create on logon trigger for the specified user and use "ALTER
>>>> SESSION" instead of "ALTER SYSTEM".
>>>>
>>>> Regards
>>>> --
>>>> Mladen Gogala
>>>> Oracle DBA
>>>> Tel: (347) 321-1217
>>>> Blog: https://dbwhisperer.wordpress.com
>>>>
>>>
>>>
>>> --
>>> Best regards,
>>> Sayan Malakshinov
>>> Oracle performance tuning engineer
>>> Oracle ACE
>>> http://orasql.org
>>>
>>
>
> --
> Martin Berger Oracle ♠
> martin.a.berger_at_gmail.com _at_martinberx <https://twitter.com/martinberx>
> ^∆x http://berxblog.blogspot.com
>

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

Received on Wed Dec 22 2021 - 22:04:34 CET