Re: sql_trace filtering

Sometimes an X$ table may be unpopulated as it is irrelevant for the instance type.

x$kffof for instance is empty when I query it in a database instance. When checked in an ASM instance, it contains one row per database file.

Re oradebug docs, I haven't updated this in a while, but it still seems to be useful for dumping quite a few oradebug command details.

https://github.com/jkstill/oracle-script-lib/blob/master/sql/oradebug_doc.sql

Pull requests are considered if anyone cares to update it.

Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist Principal Consultant at Pythian
Oracle ACE Alumni
Pythian Blog http://www.pythian.com/blog/author/still/ Github: https://github.com/jkstill
Personality: http://www.personalitypage.com/INTJ.html

On Thu, Dec 23, 2021 at 2:00 AM Jonathan Lewis <jlewisoracle_at_gmail.com> wrote:

>
> 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