Virtual Columns and Row Shipping

In 11.1.0.6, there appears to be a bug regarding the interaction of Virtual Columns with the ‘row shipping’ enhancement.

Thanks to Miladin Modrakovic for the heads-up on row shipping.
Wide Table Select Row Shipping

SQL*Plus: Release 11.1.0.6.0 - Production on Mon Oct 5 09:26:33 2009
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning option

SQL> create table t2_vc
  2  ( col0 varchar2(30) generated always as ('SAMPLE'),
  3    col1 number,
  4    col2 number,
  5    col3 number,
  6    col4 number
  7    );

Table created.

SQL> insert into t2_vc (col1) values (1);

1 row created.

SQL> select * from t2_vc;

COL0        COL1       COL2       COL3       COL4
----- ---------- ---------- ---------- ----------
               1

Note COL0 is incorrectly returned as NULL.

Turn on row shipping notes in the explain plan

SQL> alter session set "_row_shipping_explain" = true;

Session altered.

SQL> explain plan for select * from t2_vc;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3599290104
---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |     1 |    69 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T2_VC |     1 |    69 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------
Note
-----
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
   - dynamic sampling used for this statement
   - row shipping is enabled for this statement

13 rows selected.

Virtual columns fail with row shipping enabled. Try again without row shipping:

SQL> alter session set "_enable_row_shipping" = false;

Session altered.

SQL> select * from t2_vc;

COL0        COL1       COL2       COL3       COL4
----- ---------- ---------- ---------- ----------
SAMPLE          1

COL0 is correctly returned.

SQL> explain plan for select * from t2_vc;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3599290104
---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |     1 |    69 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T2_VC |     1 |    69 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------
Note
-----
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
   - dynamic sampling used for this statement

12 rows selected.

Virtual columns now work correctly.
Test the threshold (default is 80%). If we select more than 4 columns from our 5 column table, the threshold is exceeded and row shipping is enabled. Select only 4 columns and row shipping is disabled and everything is fine.

SQL> alter session set "_enable_row_shipping" = true;

Session altered.

SQL> select col0,col1,col2,col3,col4 from t2_vc;

COL0        COL1       COL2       COL3       COL4
----- ---------- ---------- ---------- ----------
               1

Execution Plan
----------------------------------------------------------
Plan hash value: 3599290104
---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |     1 |    69 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T2_VC |     1 |    69 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------
Note
-----
   - dynamic sampling used for this statement
   - row shipping is enabled for this statement

SQL> select col0,col1,col2,col3 from t2_vc;

COL0        COL1       COL2       COL3
----- ---------- ---------- ----------
SAMPLE          1

Execution Plan
----------------------------------------------------------
Plan hash value: 3599290104
---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |     1 |    56 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T2_VC |     1 |    56 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------
Note
-----
   - dynamic sampling used for this statement

Interestingly, the threshold calculation also seems a bit broken, at least it does the way I interpret it. Even though I’m selecting all columns, if I set the threshold to 81% row shipping is disabled for the virtual column table.

See the following attachment for an example of the threshold seemingly behaving in an unexpected way when virtual columns are involved.

rowship.txt

Summarise redo by object

Here is a Perl script and instructions for summarising a redo log by object. It operates on the formatted dump version of the redo, NOT the actual binary redo log itself. To save needing a lot of disk space for the formatted dump, you can use a named pipe to directly pipe the text from Oracle into the Perl script.

In the instructions below, the SQL*Plus session and the Unix session must be two separate sessions as the Perl will hang until you press Ctrl-C.

This takes a long time so it’s not actually all that practical and please don’t do it anywhere important. Save it for test databases. I’m also not a Perl expert by any means so no guarantees.

SQL> select vp.spid
from v$session vs,
 V$PROCESS vp
where vs.sid = (select sid from v$mystat where rownum = 1)
and vp.addr = vs.paddr;

SPID
————
13229

$ cd /oracle/admin/CNR/udump/
$ mkfifo ora_13229.trc
$ perl redo_summary.pl ora_13229.trc

This session will then wait for data on the pipe

SQL> ALTER SYSTEM DUMP LOGFILE 'filename';

filename should be an archived log file.

Wait until…

Statement processed.

Then Ctrl-C on the perl session

And you’ll get something like this:

       OBJECT_ID  REDO_SIZE
         3081559  131457104
           63261  127620100
         3845955  100570888
         3845956  100565432
           63268  70334108
 NON-OBJECT REDO  44421440
           36990  40602644

Cheers,
Tim

redo_summary