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@xxxxxxxxx> 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 refsSQL> oradebug doc event action refp
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])
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@xxxxxxxxx>
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@xxxxxxxxx>:
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@xxxxxxxxx>
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@xxxxxxxxx>
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@xxxxxxxxx @martinberx <https://twitter.com/martinberx>
^∆x http://berxblog.blogspot.com
