Skip to content

PostgreSQL explain plan operations

PostgreSQL explain plan operations published on Комментариев к записи PostgreSQL explain plan operations нет

to be continued

> — what does «Bitmap Heap Scan» phase do?

A plain indexscan fetches one tuple-pointer at a time from the index,
and immediately visits that tuple in the table. A bitmap scan fetches
all the tuple-pointers from the index in one go, sorts them using an
in-memory «bitmap» data structure, and then visits the table tuples in
physical tuple-location order. The bitmap scan improves locality of
reference to the table at the cost of more bookkeeping overhead to
manage the «bitmap» data structure — and at the cost that the data
is no longer retrieved in index order, which doesn’t matter for your
query but would matter if you said ORDER BY.

> — what is «Recheck condition» and why is it needed?

If the bitmap gets too large we convert it to «lossy» style, in which we
only remember which pages contain matching tuples instead of remembering
each tuple individually. When that happens, the table-visiting phase
has to examine each tuple on the page and recheck the scan condition to
see which tuples to return.

> — why are proposed «width» fields in the plan different between the two
> plans?

Updated statistics about average column widths, presumably.

> (actually, a nice explanation what exactly are those widths would also
> be nice :) )

Sum of the average widths of the columns being fetched from the table.

> — I thought «Bitmap Index Scan» was only used when there are two or more
> applicable indexes in the plan, so I don’t understand why is it used
> now?

True, we can combine multiple bitmaps via AND/OR operations to merge
results from multiple indexes before visiting the table … but it’s
still potentially worthwhile even for one index. A rule of thumb is
that plain indexscan wins for fetching a small number of tuples, bitmap
scan wins for a somewhat larger number of tuples, and seqscan wins if
you’re fetching a large percentage of the whole table.

regards, tom lane

https://www.postgresql.org/message-id/12553.1135634231@sss.pgh.pa.us

Oracle, find query witch runs more than &1 sec

Oracle, find query witch runs more than &1 sec published on Комментариев к записи Oracle, find query witch runs more than &1 sec нет
with stats as (
select /*+ materialize */
 * from (
select  parsing_schema_name,t.start#,t.end#,t.sql_id,t.plan_hash_value,t.execs,t.avg_sec,s.sql_text from (
 select  s.parsing_schema_name,min(begin_interval_time) start#,max(begin_interval_time) end#, sql_id, plan_hash_value,
sum(nvl(executions_delta,0)) execs,
 round ((sum(elapsed_time_delta)/(sum(executions_delta)))/1000000) avg_sec
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
where 1=1
and ss.begin_interval_time > trunc(sysdate-4,'iw')
and ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
and executions_delta > 0
and (elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 > &1
group by  sql_id, plan_hash_value,parsing_schema_name
 )t
join dba_hist_sqltext s on s.sql_id=t.sql_id
order by execs*avg_sec desc ) tt where
 rownum <= 50 )
 select * from stats s 
 where parsing_schema_name not like 'SYS%'
  and s.sql_text not like '%/* DS_SVC */%'
  and s.sql_text not like '%_job_proc%'
  and s.sql_text not like '%SQL Analyze%'
  and lower(s.sql_text) not like '%dbms_feature%'
  and s.sql_text not like '%SYS_DBA_SEGS%'
  and lower(s.sql_text) not like '%v$sys%'
  and lower(s.sql_text) not like '%dba_audit_trail%'
  and lower(s.sql_text) not like '%no_index(mytab)%';

Postgresql how-to find top query for last 15 seconds

Postgresql how-to find top query for last 15 seconds published on Комментариев к записи Postgresql how-to find top query for last 15 seconds нет
DROP TABLE IF EXISTS pg_stat_statements_temp; 
  create table pg_stat_statements_temp as
  SELECT  d.datname,u.usename, s.queryid,
round(s.total_time::numeric, 2) AS total_time,
s.calls,
round(s.mean_time::numeric, 2) AS mean,
now() as cur_date
FROM    pg_stat_statements s
    join pg_database d on s.dbid=d.oid
    join pg_user u on s.userid=u.usesysid;
 
    select pg_sleep(15);



 with stat as (SELECT  d.datname,u.usename, s.queryid,s.query,
round(s.total_time::numeric, 2) AS total_time,
s.calls,
round(s.mean_time::numeric, 2) AS mean
FROM    pg_stat_statements s
    join pg_database d on s.dbid=d.oid
    join pg_user u on s.userid=u.usesysid  )
    select  s.datname,s.usename,s.queryid,regexp_replace(s.query, E'[\n\r]+', ' ', 'g' )::varchar(80) AS short_query,
    s.total_time-t.total_time as time,
    s.calls-t.calls as calls,
    s.mean -t.mean as mean, 
    round( 
      (100 * (s.total_time-t.total_time) /sum(s.total_time-t.total_time) OVER ()
            )::numeric, 2)     as cpu,
    now()-t.cur_date as diff_time, 
CASE WHEN s.calls-t.calls >0  THEN 
     (s.calls-t.calls )/(extract ( second from now()-t.cur_date) + extract ( minutes from now()-t.cur_date)*60 + extract ( minutes from now()-t.cur_date)*60*60 ) 
    else '0' 
    end as tps
     from stat s
    	join pg_stat_statements_temp t on s.datname=t.datname and s.usename=t.usename and s.queryid=t.queryid
    	order by cpu desc limit 20;

oracle12c how-to get detailed info about gather stat job

oracle12c how-to get detailed info about gather stat job published on Комментариев к записи oracle12c how-to get detailed info about gather stat job нет

from oracle 12c it is possible to find out detailed information about gather stats job
in em you may find it at

or use dbms_stats.report_* to get info
detailed at https://docs.oracle.com/database/121/TGSQL/tgsql_astat.htm#TGSQL95102

I use it this way:

variable mystatsreport clob
declare
    begin
    :mystatsreport := dbms_stats.report_stats_operations(
    since=>SYSTIMESTAMP-3,
    until=>SYSTIMESTAMP,
    detail_level=>'ALL',
    format=>'TEXT');
    end;
   /

print mystatsreport
variable mystatsreport clob
declare
    begin
    :mystatsreport := dbms_stats.report_single_stats_operation ( opid => 11898,detail_level => 'TYPICAL', format =>'TEXT' );
    end;
   /
print mystatsreport
variable mystatsreport clob
declare
    begin
    :mystatsreport := dbms_stats.report_single_stats_operation ( opid => 11898,detail_level => 'TYPICAL', format =>'TEXT' );
    end;
   /
   print mystatsreport

postgresql top 10 tables by io operations

postgresql top 10 tables by io operations published on Комментариев к записи postgresql top 10 tables by io operations нет

helps to keep focus on «hot» tables
top 10 tables by sum of io operatoins:

SELECT
st.schemaname||'.'||st.relname,
round (100*( 
		sum (coalesce(st.n_tup_ins,0)+coalesce(st.n_tup_upd,0)-coalesce(st.n_tup_hot_upd,0)+coalesce(st.n_tup_del,0)) 
) 
	/ 
(
		sum (coalesce(st.n_tup_ins,0)+coalesce(st.n_tup_upd,0)-coalesce(st.n_tup_hot_upd,0)+coalesce(st.n_tup_del,0)) over () 
),2) as pct_io_ops
 from pg_stat_user_tables st
JOIN pg_class c ON c.oid=st.relid 
LEFT JOIN pg_tablespace t ON t.oid=c.reltablespace
WHERE
coalesce(n_tup_ins,0)+coalesce(n_tup_upd,0)-coalesce(n_tup_hot_upd,0)+coalesce(n_tup_del,0)>100
group by st.schemaname||'.'||st.relname,st.n_tup_ins,st.n_tup_upd,st.n_tup_hot_upd,st.n_tup_del
order by pct_io_ops desc 
limit 10;

top 10 tables by index scans:

   SELECT 
        pg_stat_all_tables.schemaname||'.'||relid::regclass AS table, 
        round (100* (sum(pg_stat_user_indexes.idx_scan))
                      / (sum(pg_stat_user_indexes.idx_scan) over () )
                       ,2) pct_indx_scans
    FROM 
        pg_stat_user_indexes 
        JOIN pg_index USING (indexrelid)
        join pg_stat_all_tables using (relid)
        group by pg_stat_all_tables.schemaname||'.'||relid::regclass,pg_stat_user_indexes.idx_scan
        order by 2 desc 
        limit 10;

generate ash report from sqlplus

generate ash report from sqlplus published on Комментариев к записи generate ash report from sqlplus нет

plan to make a script with time range as input values

19:22:41 (1)system@xxxxx> select * from table (dbms_workload_repository.ash_global_report_html( 4281370344,1,timestamp'2016-05-05 13:50:00',timestamp'2016-05-05 14:05:00') );

open in browser:
add for mac os x

host open %SQLPATH%\tmp\output_&_connect_identifier..html

add for linux:

host firefox %SQLPATH%\tmp\output_&_connect_identifier..html --linux

find query running more than 5 sec

find query running more than 5 sec published on Комментариев к записи find query running more than 5 sec нет
 select t.start#,t.end#,t.sql_id,t.plan_hash_value,t.execs,t.avg_sec,s.sql_text from (
 select  min(begin_interval_time) start#,max(begin_interval_time) end#, sql_id, plan_hash_value,
sum(nvl(executions_delta,0)) execs,
 round ((sum(elapsed_time_delta)/(sum(executions_delta)))/1000000) avg_sec
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
where 1=1
and ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
and executions_delta > 0
and (elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 > 5
group by  sql_id, plan_hash_value
 )t
join dba_hist_sqltext s on s.sql_id=t.sql_id
order by execs desc;

Screen Shot 2016-03-28 at 10.16.48

Calculating TPS from ASH via SQL_EXEC_ID

Calculating TPS from ASH via SQL_EXEC_ID published on Комментариев к записи Calculating TPS from ASH via SQL_EXEC_ID нет

save to my blog ( original )

to calculate tps by sql_id we can use sql_exec_id , ash and exel

SELECT TO_CHAR (sample_time, 'HH24:MI'),inst_id,
       MAX (sql_exec_id) - MIN (sql_exec_id) EXECUTIONS_PER_MINUTE
  FROM gv$active_Session_history
 WHERE    sql_id = 'b6asbgkghps5h' and sample_time > sysdate-5/24
group by TO_CHAR (sample_time, 'HH24:MI'),inst_id
order by 1 asc;

Screen Shot 2016-02-08 at 15.50.27

or to split results it frame by 10 minutes:

select date#,"'1'" as first_node,"'2'" as second_node from (
SELECT TRUNC(sample_time, 'MI') - MOD(TO_CHAR(sample_time, 'MI'), 10) / (24 * 60) as date#,instance_number,
       MAX (sql_exec_id) - MIN (sql_exec_id) EXECUTIONS_PER_10_MINUTE
  FROM gv$active_Session_history
 WHERE    sql_id = '77qx41mkwcm92' 
group by TRUNC(sample_time, 'MI') - MOD(TO_CHAR(sample_time, 'MI'), 10) / (24 * 60),instance_number
order by 1 asc )
pivot 
(
   sum(EXECUTIONS_PER_10_MINUTE)
   for instance_number in ('1'  ,'2' )
) order by date# asc;

what happens when table does not fit to keep pool

what happens when table does not fit to keep pool published on Комментариев к записи what happens when table does not fit to keep pool нет

create big table for tests:

create table bigtab (
    id      number(12,6),
    v1      varchar2(10),
    padding varchar2(50)
)
nologging   -- just to save a bit of time
/

insert /*+ append ordered full(s1) use_nl(s2) */
into bigtab
select
        3000 + trunc((rownum-1)/500,6),
        to_char(rownum),
        rpad('x',50,'x')
from
        all_objects s1,      -- you'll need the privilege
        all_objects s2
where
        rownum <= 2000000
/
commit;

check size:

21:07:01 (1)[PDBORCL]c##bushmelev_aa@orcl> @seg_space bigtab
old   2: where segment_name=upper ('&1')
new   2: where segment_name=upper ('bigtab')

OWNER           | SEGMENT_NAME                             |     MB_SIZE
--------------- | ---------------------------------------- | -----------
C##BUSHMELEV_AA | BIGTAB                                   |       152.0

Elapsed: 00:00:07.42
23:28:41 (1)[PDBORCL]c##bushmelev_aa@orcl> l
  1  select owner,segment_name,sum(bytes)/1024/1024 as MB_SIZE from dba_extents
  2  where segment_name=upper ('&1')
  3* group by segment_name,owner

ps: describe of x$kcbwds ds, x$kcbwbpd pd you may find at oracle x$ tables

usefull links:
http://enkitec.tv/2012/05/19/oracle-full-table-scans-direct-path-reads-object-level-checkpoints-ora-8103s/
Direct path read and fast full index scans

lets_check:

 select ksppinm as hidden_parameter  , ksppstvl as value
from x$ksppi join x$ksppcv using (indx)
 where ksppinm like '\_small_table_threshold%' escape '\' order by ksppinm;

HIDDEN_PARAMETER          | VALUE
------------------------- | ----------
_small_table_threshold    | 2203

00:25:34 (1)[PDBORCL]sys@orcl> select 2203*8192/1024/1024 from dual;

2203*8192/1024/1024
-------------------
         17.2109375

so our 17 mb table should do DPR on full table scan

now lets full scan bigtable ( there is no indexes on table, so i use count (*) for minimize network influence on results ) an check sga info and session stats :

00:13:23 (1)[PDBORCL]c##bushmelev_aa@orcl> select count (*) from bigtab;

  COUNT(*)
----------
   2000000

using:
Script to Identify Objects and Amount of Blocks in the Buffer Pools — Default, Keep, Recycle, nK Cache (Doc ID 373472.1)

get info about sement in sga:

select BP_ID, decode(pd.bp_id,1,'KEEP',2,'RECYCLE',3,'DEFAULT',4,'2K SUBCACHE',5,'4K SUBCACHE',6,'8K SUBCACHE',7,'16K SUBCACHE',8,'32K SUBCACHE','UNKNOWN') subcache,
bh.object_name, round (sum(bh.blocks)* (select to_number(value) from v$parameter where name='db_block_size') /(1024*1024),2) used_mb
from x$kcbwds ds, x$kcbwbpd pd,
(select /*+ use_hash(x) */ set_ds, o.name object_name, count(*) BLOCKS from obj$ o, x$bh x where o.dataobj#=x.obj and x.state!=0 and o.owner#!=0 and upper(o.name)=upper('&mytable') group by set_ds, o.name) bh
where ds.set_id>=pd.bp_lo_sid and ds.set_id<=pd.bp_hi_sid and pd.bp_size!=0 and ds.addr=bh.set_ds group by pd.BP_ID, decode(pd.bp_id,1,'KEEP',2,'RECYCLE',3,'DEFAULT',4,'2K SUBCACHE',5,'4K SUBCACHE',6,'8K SUBCACHE',7,'16K SUBCACHE',8,'32K SUBCACHE','UNKNOWN') , bh.object_name; 00:50:04 (1)[PDBORCL]sys@orcl> @db_seg_cache2
Enter value for mytable: bigtab

     BP_ID | SUBCACHE   | OBJECT_NAME     |    USED_MB
---------- | ---------- | --------------- | ----------
         3 | DEFAULT    | BIGTAB          |        .01

Screen Shot 2016-01-03 at 00.29.56

now put full table (152mb) to keep_pool:

alter system set db_keep_cache_size=200m;
alter table bigtab storage (buffer_pool keep );

now fullscan works from cache:

Screen Shot 2016-01-03 at 00.37.23

now check buffer pools :

select BP_ID, decode(pd.bp_id,1,'KEEP',2,'RECYCLE',3,'DEFAULT',4,'2K SUBCACHE',5,'4K SUBCACHE',6,'8K SUBCACHE',7,'16K SUBCACHE',8,'32K SUBCACHE','UNKNOWN') subcache,
bh.object_name, round (sum(bh.blocks)* (select to_number(value) from v$parameter where name='db_block_size') /(1024*1024),2) used_mb
from x$kcbwds ds, x$kcbwbpd pd,
(select /*+ use_hash(x) */ set_ds, o.name object_name, count(*) BLOCKS from obj$ o, x$bh x where o.dataobj#=x.obj and x.state!=0 and o.owner#!=0 and upper(o.name)=upper('&mytable') group by set_ds, o.name) bh
where ds.set_id>=pd.bp_lo_sid and ds.set_id<=pd.bp_hi_sid and pd.bp_size!=0 and ds.addr=bh.set_ds
group by pd.BP_ID, decode(pd.bp_id,1,'KEEP',2,'RECYCLE',3,'DEFAULT',4,'2K SUBCACHE',5,'4K SUBCACHE',6,'8K SUBCACHE',7,'16K SUBCACHE',8,'32K SUBCACHE','UNKNOWN') , bh.object_name;

     BP_ID | SUBCACHE   | OBJECT_NAME     |    USED_MB
---------- | ---------- | --------------- | ----------
         3 | DEFAULT    | BIGTAB          |        .01
         1 | KEEP       | BIGTAB          |     148.64

change keep pool to 140mb and repeat test:

alter system set db_keep_cache_size=120m;

00:39:38 (1)[PDBORCL]sys@orcl> /
Enter value for mytable: bigtab

     BP_ID | SUBCACHE   | OBJECT_NAME     |    USED_MB
---------- | ---------- | --------------- | ----------
         3 | DEFAULT    | BIGTAB          |        .01
         1 | KEEP       | BIGTAB          |     133.27

even when 87% of table are in current mode in keep pool session do DPR

01:01:19 (1)[PDBORCL]sys@orcl> @db_seg_cache c##bushmelev_aa bigtab

AREA         | STATUS                         | DISTINCT_BLOCK_COUNT                     | BLOCK_COUNT
------------ | ------------------------------ | ---------------------------------------- | ------------------------------------------------------------------------------------------------------------------------
BUFFER CACHE | free                           | 3                                        | 3
BUFFER CACHE | xcur                           | 17060                                    | 17060
BUFFER CACHE | summary                        | 17063                                    | 17063
DATABASE     | db blocks                      | 19456                                    |
SGA          | BUFFER CACHE of MAX SGA SIZE   | 754,974,720 of 1,426,063,360             | (Resizeable)

Elapsed: 00:00:00.11
01:01:20 (1)[PDBORCL]sys@orcl> l
  1  with OBJLIST as
  2   (select DATA_OBJECT_ID
  3      from dba_objects
  4     where (owner, object_name) in ((upper(nvl('&&1', 'user')), upper('&&2')))
  5       and DATA_OBJECT_ID is not null)
  6  select 'BUFFER CACHE' as AREA,
  7         nvl(status,'summary') as STATUS,
  8         to_char(count(distinct(file# || '#' || block#))) as DISTINCT_BLOCK_COUNT,
  9         to_char(count(*)) as BLOCK_COUNT
 10    from V$BH, OBJLIST
 11   where objd = OBJLIST.DATA_OBJECT_ID
 12   group by rollup(status)
 13  union all
 14  select 'DATABASE',
 15         'db blocks',
 16         to_char(blocks),
 17         '' as BH_COUNT
 18  from dba_segments where (owner, segment_name) in ((upper(nvl('&&1', 'user')), upper('&&2')))
 19  union all
 20  select 'SGA',
 21         'BUFFER CACHE of MAX SGA SIZE',
 22         trim(to_char(s1.bytes, '999,999,999,999,999')) ||
 23         ' of '||
 24         trim(to_char(s2.bytes, '999,999,999,999,999')),
 25         '(' || decode(s1.resizeable, 'Yes', 'Resizeable', 'Fixed') || ')'
 26* from v$sgainfo s1, v$sgainfo s2 where s1.name = 'Buffer Cache Size' and s2.name = 'Maximum SGA Size'

and FTS goes by DRP:

Screen Shot 2016-01-03 at 00.41.24

what_happens_when_table_does_not_fet_to_keep_pool

Oracle X$ Tables

Oracle X$ Tables published on 2 комментария к записи Oracle X$ Tables

original at yong321.freeshell.org

X$ Tables


Oracle X$ Tables

Updated to Oracle 12.1.0.2. The X$ tables not included here are too obvious, too obscure, or too uninteresting.

Table Name Guessed Acronym Comments
x$activeckpt active checkpoint Ckpt_type 2 for MR checkpoint (Ref), 3 for interval (Ref) or thread checkpoint (Ref), 7 for incremental checkpoint, 10 for object reuse/truncate checkpoint, 11 for object checkpoint (Ref).
x$bh buffer header This table is commonly used to find the object and the file# and block# of its header when there’s high cache buffers chains latch contention: select obj, dbarfil, dbablk from x$bh a, v$latch_children b where a.hladdr = b.addr for the said latch (whose sleeps you think are too high). You can also use this table to see if a specific buffer has too many clones: select dbarfil, dbablk, count(*) from x$bh group by dbarfil, dbablk having count(*) > 2. Note obj column matches dba_objects.data_object_id, not object_id. For performance reason, don’t merge dba_extents with the query of x$bh that has a group by, unless you use in-line view and no_merge hint (see J. Lewis Practical Oracle8i, p.215) The tch column, touch count, records how many times a particular buffer has been accessed. Its flag column is explained by J. Lewis (some unused columns are later used; e.g. bit 29 means plugged_from_foreign_db in 12c); explanation of state, mode and indx can be found in Anjo Kolk’s paper. Tim is time the buffer touch happened (Note 1). Lru_flag is about the buffer’s position on LRU lists (Ref and 136312.1); 2 moved_to_tail, 4 on_auxiliary_list (auxliary LRU), 8 hot_buffer (on hot end of main LRU), and numbers can be added e.g. 6=2+4.
x$ckptbuf checkpoint buffer (queue) Lists the buffers on the checkpoint queue. Immediately after a full checkpoint, the buffers with non-zero buf_ptr and buf_dbablk should go down.
x$dbgalertext debug alert extented One use is to find old alert.log text long after you recycled the physical file: select originating_timestamp, message_text from x$dbgalertext. The message_id and message_group columns are also interesting and are not available in alert.log.
x$dbglogext debug log extended 12c
x$dbgricx, x$dbgrifx, x$dbgrikx, x$dbgripx debug ? You can quickly summarize what kind of errors the database has had: select error_facility||’-‘||error_number, count(*) from x$dbgricx group by error_facility||’-‘||error_number order by 2, and optionally restrict to a certain time range. You can of course summarize on a more granular level, such as (e.g.) shared pool vs large pool on error_arg2 in case of ORA-4031. You can of course find records of these errors in (undocumented) v$diag_incident or v$diag_diagv_incident. In any case, you may find this easier than grep alert.log. For each incident, its session info is in x$dbgrikx.
x$dbkece debug kernel error, critical error Base table of undocumented v$diag_critical_error but includes facility dbge (Diagnostic Data Extractor or dde)
x$dbkefefc debug kernel error, fatal error flood control Rules for flood control on too many fatal errors.
x$dglparam data guard logical parameters Base table of dba_logstdby_parameters but includes invisible parameters.
x$diag_alert_ext diagnostics alert extended Base table of v$diag_alert_ext. Same as x$dbgalertext but has more lines, slower to query
x$diag_hm_run, x$diag_vhm_run diagnostics health monitor runs Base table of undocumented v$diag_(v)hm_run. Health monitor job records. Maybe complementary to v$hm_run?
x$diag_ips_configuration diagnostics incident packaging service configuration Base table of v$diag_ips_configuration. Some ADR IPS related config info. Like a few other v$diag* (or x$diag*) tables, some columns such as adr_home, name, can’t be exactly matched as if there’re trailing characters. CTAS to create a regular table against which you query, or use subquery factoring with /*+materialize*/ hint.
x$dnfs_meta dNFS metadata Some metadata related to dNFS, SGA memory, message timeout, ping timeout, etc.
x$dra_failure data recovery advisor failures DRA failure names and descriptions.
x$drm_history, x$drm_history_stats dynamic remastering history, stats History of RAC DRM and stats. Parent_key is object_id. If an object is remastered to another node (new_master) too frequently, consider partitioning the app sessions. In 12.1.0.2, there’s also x$drm_wait_stats.
x$jskjobq job scheduling ?, job queue Internal job queue. Job_oid is object_id in dba_objects. If you must query this table, exit the session as soon as you’re done with your work because your session after the query holds an exclusive JS lock, which will block CJQ process! Rollback or commit won’t release the lock.
x$k2gte,
x$k2gte2
kernel 2-phase commit, global transaction entry See Note:104420.1. Find sessions coming from or going to a remote database; in short, x$k2gte.k2gtdses matches v$session.saddr, .k2gtdxcb matches v$transaction.addr.

select /*+ ordered */
substr(s.ksusemnm,1,10)||’-‘|| substr(s.ksusepid,1,10) origin,
substr(g.k2gtitid_ora,1,35) gtxid,
substr(s.indx,1,4)||’.’|| substr(s.ksuseser,1,5) lsession,
s.ksuudlna username,
substr(decode(bitand(ksuseidl,11), 1,’ACTIVE’, 0, decode( bitand(ksuseflg,4096) , 0,’INACTIVE’,’CACHED’),
2,’SNIPED’, 3,’SNIPED’, ‘KILLED’),1,1) status,
e.kslednam waiting
from x$k2gte g, x$ktcxb t, x$ksuse s, x$ksled e
where g.k2gtdxcb=t.ktcxbxba
and g.k2gtdses=t.ktcxbses
and s.addr=g.k2gtdses
and e.indx=s.ksuseopc;

It’s Continue reading Oracle X$ Tables

Primary Sidebar

Яндекс.Метрика