Re: sql_trace filtering

Nice one.

It had occurred to me in the past that the "zero offset" entries in the x$ might be an indicator that the value is returned from a call to a function that probes the fixed sga/pga/uga for a value, and the INT_TYPE column would tell you where or what type.

Now I'm tempted do a few more experiments in the area to find other values for a session.

Regards
Jonathan Lewis

On Wed, 22 Dec 2021 at 21:04, Mikhail Velikikh <mvelikikh_at_gmail.com> wrote:

> 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