RE: sql_trace filtering
Date: Fri, 31 Dec 2021 11:28:44 -0500
Message-ID: <7b2601d7fe63$7c2780a0$747681e0$_at_rsiz.com>
x$le likewise, which is relevant to x$bh, ,which has an outer join to x$le so that non-RAC deployments return rows with x$le having zero rows.
caution this may be version specific.
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jared Still
Sent: Thursday, December 30, 2021 4:30 PM
To: ORACLE-L
Cc: Mikhail Velikikh; Martin Berger; Sayan Malakshinov; Mladen Gogala; Jonathan Lewis
Subject: 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 <https://twitter.com/martinberx> _at_martinberx ^∆x http://berxblog.blogspot.com
--
http://www.freelists.org/webpage/oracle-l
Received on Fri Dec 31 2021 - 17:28:44 CET