Home > Oracle > Faster access to transaction version query

Faster access to transaction version query


Beware when you query flashback_transaction_query specially in a hyper active database.   If you query without a proper WHERE cluase, it is going cause lot of block access. 

In a test system, no other users active, my query puased for a while before  returning the records from flashback_transaction_query and I felt that was bit long.

SQL> select UNDO_SQL  from flashback_transaction_query where TABLE_NAME=’TEST’;

UNDO_SQL
——————————————————————————–
update “USER1″.”TEST” set “SALARY” = ‘5000’ where ROWID = ‘AAAEuRAAHAAAACFAAG’;
update “USER1″.”TEST” set “SALARY” = ‘5000’ where ROWID = ‘AAAEuRAAHAAAACFAAF’;
update “USER1″.”TEST” set “SALARY” = ‘5000’ where ROWID = ‘AAAEuRAAHAAAACFAAE’;
update “USER1″.”TEST” set “SALARY” = ‘5000’ where ROWID = ‘AAAEuRAAHAAAACFAAD’;
update “USER1″.”TEST” set “SALARY” = ‘5000’ where ROWID = ‘AAAEuRAAHAAAACFAAC’;
update “USER1″.”TEST” set “SALARY” = ‘5000’ where ROWID = ‘AAAEuRAAHAAAACFAAB’;

8 rows selected.

Elapsed: 00:00:02.98

The reason was it was doing a full table scan on a base table X$KTUQQRY as below with high number of recurrsive calls and data block access. 

 ——————————————————————————
| Id  | Operation        | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
——————————————————————————
|   0 | SELECT STATEMENT |           |     1 |  2132 |     0   (0)| 00:00:01 |
|*  1 |  FIXED TABLE FULL| X$KTUQQRY |     1 |  2132 |     0   (0)| 00:00:01 |
——————————————————————————

Predicate Information (identified by operation id):
—————————————————

   1 – filter(“TABLE_NAME”=’TEST’)
Statistics
———————————————————-
      11793  recursive calls
         10  db block gets
      61042  consistent gets
          7  physical reads
          0  redo size
       1086  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
        341  sorts (memory)
          0  sorts (disk)
          8  rows processed

The better way to access flashback_transaction_query is first identify the XID from the table using a flashback query.  Use the other sudo columns to identify the interested transaction XID.

SQL> select distinct VERSIONS_XID from test 
VERSIONS BETWEEN TIMESTAMP MINVALUE AND MAXVALUE;

VERSIONS_XID
—————-

0009000F0000063F
0002000900000651
000800210000063F
0002000B0000064E

Query flashback_transaction_query using the XID value in the where clause with HEXTORAW conversion.

SQL> select UNDO_SQL  from flashback_transaction_query where TABLE_NAME=’TEST’
and xid = HEXTORAW(‘0009000F0000063F’);

UNDO_SQL
——————————————————————————-
update “USER1″.”TEST” set “SALARY” = ‘5000’ where ROWID = ‘AAAEuRAAHAAAACFAAG’;
update “USER1″.”TEST” set “SALARY” = ‘5000’ where ROWID = ‘AAAEuRAAHAAAACFAAF’;
update “USER1″.”TEST” set “SALARY” = ‘5000’ where ROWID = ‘AAAEuRAAHAAAACFAAE’;
update “USER1″.”TEST” set “SALARY” = ‘5000’ where ROWID = ‘AAAEuRAAHAAAACFAAD’;
update “USER1″.”TEST” set “SALARY” = ‘5000’ where ROWID = ‘AAAEuRAAHAAAACFAAC’;
update “USER1″.”TEST” set “SALARY” = ‘5000’ where ROWID = ‘AAAEuRAAHAAAACFAAB’;

6 rows selected.

Elapsed: 00:00:00.02
———————————————————————————————
| Id  | Operation               | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
———————————————————————————————
|   0 | SELECT STATEMENT        |                   |     1 |  2138 |     0   (0)| 00:00:01 |
|*  1 |  FIXED TABLE FIXED INDEX| X$KTUQQRY (ind:1) |     1 |  2138 |     0   (0)| 00:00:01 |
———————————————————————————————

Predicate Information (identified by operation id):
—————————————————

   1 – filter(“TABLE_NAME”=’TEST’ AND “XID”=HEXTORAW(‘0009000F0000063F’) )
Statistics
———————————————————-
         28  recursive calls
          1  db block gets
         74  consistent gets
          0  physical reads
          0  redo size
       1074  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
          6  rows processed

The WHERE clause will force an index read, considerably reduce the resouce usage.

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: