EXADATA and SuperCluster : Check if long running transactions are preventing min active scn from progressing, resulting in Storage Indexes not being used

Asset ID:	1-72-2081483.1
Update Date:	2016-10-24
Keywords:

Solution Type Problem Resolution Sure

Solution 2081483.1 : EXADATA and SuperCluster : Check if long running transactions are preventing min active scn from progressing, resulting in Storage Indexes not being used

Applies to:

Oracle Exadata Storage Server Software - Version 11.2.2.3.0 and later
Oracle SuperCluster M7 Hardware - Version All Versions and later
Oracle SuperCluster T5-8 Full Rack - Version All Versions and later
Oracle SuperCluster M6-32 Hardware - Version All Versions and later
Linux x86-64
Oracle Solaris on SPARC (64-bit)

Symptoms

Storage Indexes are not being used.

Cause

Long running transactions prevent min active scn from progressing, there by causing scans to not use Storage Index.

Solution

To check if a long running transaction is preventing the use of Storage Index, follow the steps below:

Step 1: Get global min active scn

Set system event 55703. Min active scn is printed out to database alert log once every 3 mins.

alter system set events '55703 trace name context forever, level 1';

Example output from alert log. gmin-scn is the global min scn.

master-status: grec-scn:0x0000.000a66ba gmin-scn:0x0000.000a6567 gcalc-scn:0x0000.000a66bb

Step 2: Convert gmin-scn from hex to dec (shift the 2 bytes of wrap by 32 and add the base)

example: 0x0000.000a6567 => (0x0000 << 32) + (0x00a6567) = 0 + 681319

Unset system event 55703

SQL> alter system set events '55703 trace name context off';

Step 3: Use scn_to_timestamp to compare the scns from step 1 to current_scn from v$database. If they differ by a good amount, identify the long running transaction to take action.

SQL> Select scn_to_timestamp(681319) from dual; <---- SCN from step 1

SCN_TO_TIMESTAMP(681319)

---------------------------------------------------------------------------

03-DEC-12 10.06.22.000000000 AM

SQL> Select scn_to_timestamp(current_scn) from v$database;

SCN_TO_TIMESTAMP(683319)

---------------------------------------------------------------------------

03-DEC-12 10.41.48.000000000 AM

*** SCN from step 1 is 35 minutes behind current_scn.

Step 4: Find the list of transactions that are not inactive, which may prevent the SCN from progressing.

sql> SELECT KTUXEUSN "undo_seg_number",

                KTUXESLT "slot_number",

                KTUXESCNW * power(2, 32) + KTUXESCNB "scn"

         FROM x$ktuxe

         WHERE KTUXESTA not like 'INACTIVE';

undo_seg_number slot_number        scn

--------------- ----------- ----------
                2          19     682601

                5          33     681319 <------ same as min active scn in alert log

The row has the same scn as the min active scn printed in the alert log.

Step 5: Identify the process id of the long running transaction

SQL> SELECT v.inst_id, p.spid

       FROM gv$transaction v, v$session s, v$process p

        WHERE v.XIDUSN= 5 AND   <--- Undo segment number of long running txn

                v.XIDSLOT = 33 AND <--- Slot number of long running txn

                v.SES_ADDR = s.SADDR AND

                s.PADDR = p.addr;

     INST_ID SPID

---------- ------------------------

           1 14565

This query can help in identifying all long running transaction as well

SQL> select v.inst_id,
v.XIDUSN, v.XIDSLOT ,
v.XIDSQN ,v. START_TIME,
v.start_scnb, v.USED_UBLK,
o.object_id,
o.oracle_username,
d.object_name from
gv$transaction v,
gv$locked_object o,
dba_objects d
where v.XIDUSN= o.XIDUSN and
v.xidslot=o.xidslot and
v.xidsqn=o.xidsqn and
o.object_id = d.object_id
order by 10,5,1
/

Summary:

Transaction originating from OS process id 14565 on instance 1 is the long running transaction that is causing suboptimal storage index usage.

Database administrator can either kill this process or do a pstack on this process to get more visibility into why the transaction is stuck.

Attachments

This solution has no attachment