Skip to content

Instantly share code, notes, and snippets.

@mvelikikh

mvelikikh/output.sql

Created January 31, 2022 18:57
Show Gist options
  • Save mvelikikh/eee1de10c00c07f6a81743c4b935f833 to your computer and use it in GitHub Desktop.
Save mvelikikh/eee1de10c00c07f6a81743c4b935f833 to your computer and use it in GitHub Desktop.
Download ZIP
SQL Trace with filter by FORCE_MATCHING_SIGNATURE
Raw
output.sql
SQL> @q
SQL>
SQL> conn / as sysdba
Connected.
SQL>
SQL> alter session set container=pdb;
Session altered.
SQL>
SQL> drop user tc1 cascade;
User dropped.
SQL>
SQL> grant alter session, create session, select_catalog_role to tc1 identified by tc1;
Grant succeeded.
SQL>
SQL> alter system flush shared_pool;
System altered.
SQL>
SQL> col column_name for a11
SQL> select c.kqfconam column_name,
2 c.kqfcodty datatype,
3 c.kqfcosiz size_byte,
4 c.kqfcooff offset
5 from x$kqfta t,
6 x$kqfco c
7 where t.kqftanam = 'X$KSUSE'
8 and c.kqfcotab = t.indx
9 and c.kqfconam = 'KSUSESQL'
10 order by c.indx;
COLUMN_NAME DATATYPE SIZE_BYTE OFFSET
----------- ---------- ---------- ----------
KSUSESQL 23 8 2424
SQL>
SQL> conn tc1/tc1@db-21/pdb
Connected.
SQL>
SQL> set num 20
SQL>
SQL> col sig_to_trace old_v sig_to_trace
SQL>
SQL> with function sig(sql_text varchar2) return number
2 as
3 begin
4 return dbms_sqltune.sqltext_to_signature(sql_text, true);
5 end;
6 select sig('select 3735928559, 0, 1 from dual') sig_to_trace
7 from dual;
8 /
SIG_TO_TRACE
--------------------
4343055653486697370
SQL>
SQL> def saddr='refp(varaddr("ksmuh_p"), 16)'
SQL> def sql_addr='refp(&saddr., 2424)'
SQL> def kglob='refp(&sql_addr., 16)'
SQL> def sig='refn(&kglob., 8, 408)'
SQL>
SQL> alter session set events 'sql_trace {eq:&sig_to_trace.,&sig.}';
old 1: alter session set events 'sql_trace {eq:&sig_to_trace.,&sig.}'
new 1: alter session set events 'sql_trace {eq: 4343055653486697370,refn(refp(refp(refp(varaddr("ksmuh_p"), 16), 2424), 16), 8, 408)}'
Session altered.
SQL>
SQL> select 3735928559, 0 from dual;
3735928559 0
-------------------- --------------------
3735928559 0
SQL> select 3735928559, 0, 0 from dual;
3735928559 0 0
-------------------- -------------------- --------------------
3735928559 0 0
SQL> select 3735928559, 1 from dual;
3735928559 1
-------------------- --------------------
3735928559 1
SQL> select 3735928559, 2 from dual;
3735928559 2
-------------------- --------------------
3735928559 2
SQL> select 3735928559, 0, 'x' from dual;
3735928559 0 '
-------------------- -------------------- -
3735928559 0 x
SQL> /
3735928559 0 '
-------------------- -------------------- -
3735928559 0 x
SQL> select 3735928559, 3 from dual;
3735928559 3
-------------------- --------------------
3735928559 3
SQL>
SQL> col sql_text for a35
SQL> select sql_id, sql_text, force_matching_signature
2 from v$sqlarea
3 where sql_text like 'select 3735928559%';
SQL_ID SQL_TEXT FORCE_MATCHING_SIGNATURE
------------- ----------------------------------- ------------------------
bch5zwtd4pp4s select 3735928559, 1 from dual 13853489014012980200
dk9nzz846xrvx select 3735928559, 0, 'x' from dual 4343055653486697370
bt7c9rga9jrwb select 3735928559, 0, 0 from dual 4343055653486697370
8tukuanxc1xaq select 3735928559, 3 from dual 13853489014012980200
bbq7u1qzvbjyk select 3735928559, 0 from dual 13853489014012980200
0tw90fcdmvmvc select 3735928559, 2 from dual 13853489014012980200
6 rows selected.
SQL>
SQL> col trace_file old_v trace_file
SQL> select value trace_file from v$diag_info where name='Default Trace File';
TRACE_FILE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_26296.trc
SQL>
SQL> select banner_full from v$version;
BANNER_FULL
--------------------------------------------------------------------------------
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.5.0.0.0
SQL>
SQL> !cat &trace_file.
Trace file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_26296.trc
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.5.0.0.0
Build label: RDBMS_21.3.0.0.0_LINUX.X64_210727
ORACLE_HOME: /u01/app/oracle/product/21.0.0/dbhome_1
System name: Linux
Node name: db-21.localdomain
Release: 5.4.17-2136.300.7.el8uek.x86_64
Version: #2 SMP Fri Oct 8 16:23:01 PDT 2021
Machine: x86_64
VM name: KVM CPUID feature flags: 0x01000089
Instance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 70
Unix process pid: 26296, NID: 4026531836, image: oracle@db-21.localdomain
*** 2022-01-31T18:54:20.643731+00:00 (PDB(3))
*** SESSION ID:(108.46894) 2022-01-31T18:54:20.643760+00:00
*** CLIENT ID:() 2022-01-31T18:54:20.643778+00:00
*** SERVICE NAME:(pdb) 2022-01-31T18:54:20.643796+00:00
*** MODULE NAME:(SQL*Plus) 2022-01-31T18:54:20.643814+00:00
*** ACTION NAME:() 2022-01-31T18:54:20.643831+00:00
*** CLIENT DRIVER:(SQL*PLUS) 2022-01-31T18:54:20.643847+00:00
*** CONTAINER ID:(3) 2022-01-31T18:54:20.643864+00:00
*** CLIENT IP:(10.0.2.15) 2022-01-31T18:54:20.643889+00:00
*** CONNECTION ID:(1uZPYihXZojgUw8CAAr+og==) 2022-01-31T18:54:20.643907+00:00
=====================
PARSING IN CURSOR #140238389397144 len=33 dep=0 uid=149 oct=3 lid=149 tim=28276584229 hv=3566788491 ad='b5fad620' sqlid='bt7c9rga9jrwb'
select 3735928559, 0, 0 from dual
END OF STMT
EXEC #140238389397144:c=16,e=16,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1388734953,tim=28276584228
FETCH #140238389397144:c=6,e=6,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=1388734953,tim=28276585145
FETCH #140238389397144:c=1,e=1,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=1388734953,tim=28276585792
CLOSE #140238389397144:c=7,e=8,dep=0,type=0,tim=28276586568
=====================
PARSING IN CURSOR #140238389397144 len=35 dep=0 uid=149 oct=3 lid=149 tim=28276594436 hv=141483901 ad='b5faa320' sqlid='dk9nzz846xrvx'
select 3735928559, 0, 'x' from dual
END OF STMT
EXEC #140238389397144:c=14,e=14,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1388734953,tim=28276594435
FETCH #140238389397144:c=5,e=5,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=1388734953,tim=28276594567
FETCH #140238389397144:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=1388734953,tim=28276594848
CLOSE #140238389397144:c=0,e=8,dep=0,type=0,tim=28276595692
=====================
PARSING IN CURSOR #140238389397144 len=35 dep=0 uid=149 oct=3 lid=149 tim=28276596902 hv=141483901 ad='b5faa320' sqlid='dk9nzz846xrvx'
select 3735928559, 0, 'x' from dual
END OF STMT
EXEC #140238389397144:c=20,e=21,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1388734953,tim=28276596902
FETCH #140238389397144:c=11,e=11,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=1388734953,tim=28276598202
FETCH #140238389397144:c=1,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=1388734953,tim=28276598541
CLOSE #140238389397144:c=0,e=10,dep=0,type=0,tim=28276599355
Raw
sql_trace_filter_by_force_matching_signature.sql
set echo on lin 80
conn / as sysdba
alter session set container=pdb;
drop user tc1 cascade;
grant alter session, create session, select_catalog_role to tc1 identified by tc1;
alter system flush shared_pool;
col column_name for a11
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 = 'KSUSESQL'
order by c.indx;
conn tc1/tc1@db-21/pdb
set num 20
col sig_to_trace old_v sig_to_trace
with function sig(sql_text varchar2) return number
as
begin
return dbms_sqltune.sqltext_to_signature(sql_text, true);
end;
select sig('select 3735928559, 0, 1 from dual') sig_to_trace
from dual;
/
def saddr='refp(varaddr("ksmuh_p"), 16)'
def sql_addr='refp(&saddr., 2424)'
def kglob='refp(&sql_addr., 16)'
def sig='refn(&kglob., 8, 408)'
alter session set events 'sql_trace {eq:&sig_to_trace.,&sig.}';
select 3735928559, 0 from dual;
select 3735928559, 0, 0 from dual;
select 3735928559, 1 from dual;
select 3735928559, 2 from dual;
select 3735928559, 0, 'x' from dual;
/
select 3735928559, 3 from dual;
col sql_text for a35
select sql_id, sql_text, force_matching_signature
from v$sqlarea
where sql_text like 'select 3735928559%';
col trace_file old_v trace_file
select value trace_file from v$diag_info where name='Default Trace File';
select banner_full from v$version;
!cat &trace_file.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment