Oracle Result Cache with VPD and RAS

The Oracle (server side ) Result Cache introduced with Oracle 11 is another lesser known and under-used Oracle feature (have I mentioned the Connection Manager recently  ? :)).

It is particular useful with BI tools – which tend to spawn aggregate queries just to refresh some codelists – which may take some seconds on billion-row tables even with all non-cache optimizations  like in-memory or Exadata smart scan (a concrete example you will see below: on an Exadata X6 a “select column,count(*) from table group by column” takes around 5 seconds for a table with half a billion records and columns with not many distinct values – with in-memory still 2.5 seconds. With result cache below a millisecond).

I will be talking here about the standard query result cache – the pl/sql result cache seems to be in general more tricky – see for example here .

That the result cache works nicely with VPD was already shown in this quite old but still very useful article, but how about the 12c Real Application Security (RAS) ? And how does it actually work – how does the cache know about VPD/RAS ?

As this articles will be mainly about the Result Cache I will not introduce RAS in details – see here if you are interested in a good demo.

We create 2 RAS users associated to different RAS roles – eventually resolving in restriction predicates on one table restricting on column to different values .

See below the results for a count on this table – with both users, always two executions. Timing and auto trace enabled .

sqlplus myuser@csc

SQL> set timing on;
SQL> set autotrace on;
SQL> select /*+ RESULT_CACHE */  iscore,count(*) from csc.test4 group by iscore;

ISC   COUNT(*)
--- ----------
YES     181920
NO    13913216

Elapsed: 00:00:05.40

Execution Plan
----------------------------------------------------------
Plan hash value: 605068319

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

| Id  | Operation                         | Name                       | Rows  |
 Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |

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

|   0 | SELECT STATEMENT                  |                            |     2 |
    14 |   144K  (1)| 00:00:06 |        |      |            |

|   1 |  RESULT CACHE                     | 3ffn6v40jjd0rasurkxb97ssm5 |       |
       |            |          |        |      |            |

|   2 |   PX COORDINATOR                  |                            |       |
       |            |          |        |      |            |

|   3 |    PX SEND QC (RANDOM)            | :TQ10001                   |     2 |
    14 |   144K  (1)| 00:00:06 |  Q1,01 | P->S | QC (RAND)  |

|   4 |     HASH GROUP BY                 |                            |     2 |
    14 |   144K  (1)| 00:00:06 |  Q1,01 | PCWP |            |

|   5 |      PX RECEIVE                   |                            |     2 |
    14 |   144K  (1)| 00:00:06 |  Q1,01 | PCWP |            |

|   6 |       PX SEND HASH                | :TQ10000                   |     2 |
    14 |   144K  (1)| 00:00:06 |  Q1,00 | P->P | HASH       |

|   7 |        HASH GROUP BY              |                            |     2 |
    14 |   144K  (1)| 00:00:06 |  Q1,00 | PCWP |            |

|   8 |         PX BLOCK ITERATOR         |                            |    14M|
    94M|   144K  (1)| 00:00:06 |  Q1,00 | PCWC |            |

|*  9 |          TABLE ACCESS STORAGE FULL| TEST4                      |    14M|
    94M|   144K  (1)| 00:00:06 |  Q1,00 | PCWP |            |

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


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

   9 - storage("FREQUENCY"='MO')
       filter("FREQUENCY"='MO')

Result Cache Information (identified by operation id):
------------------------------------------------------

   1 - column-count=2; dependencies=(CSC.TEST4); attributes=(vpd); parameters=(n
ls); name="select /*+ RESULT_CACHE */  iscore,count(*) from csc.test4 group by i
score"


Note
-----
   - Degree of Parallelism is 64 because of table property


Statistics
----------------------------------------------------------
        421  recursive calls
          0  db block gets
   30828842  consistent gets
   30728562  physical reads
        104  redo size
        452  bytes sent via SQL*Net to client
        500  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed

SQL> select /*+ RESULT_CACHE */  iscore,count(*) from csc.test4 group by iscore;

ISC   COUNT(*)
--- ----------
YES     181920
NO    13913216

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 605068319

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

| Id  | Operation                         | Name                       | Rows  |
 Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |

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

|   0 | SELECT STATEMENT                  |                            |     2 |
    14 |   144K  (1)| 00:00:06 |        |      |            |

|   1 |  RESULT CACHE                     | 3ffn6v40jjd0rasurkxb97ssm5 |       |
       |            |          |        |      |            |

|   2 |   PX COORDINATOR                  |                            |       |
       |            |          |        |      |            |

|   3 |    PX SEND QC (RANDOM)            | :TQ10001                   |     2 |
    14 |   144K  (1)| 00:00:06 |  Q1,01 | P->S | QC (RAND)  |

|   4 |     HASH GROUP BY                 |                            |     2 |
    14 |   144K  (1)| 00:00:06 |  Q1,01 | PCWP |            |

|   5 |      PX RECEIVE                   |                            |     2 |
    14 |   144K  (1)| 00:00:06 |  Q1,01 | PCWP |            |

|   6 |       PX SEND HASH                | :TQ10000                   |     2 |
    14 |   144K  (1)| 00:00:06 |  Q1,00 | P->P | HASH       |

|   7 |        HASH GROUP BY              |                            |     2 |
    14 |   144K  (1)| 00:00:06 |  Q1,00 | PCWP |            |

|   8 |         PX BLOCK ITERATOR         |                            |    14M|
    94M|   144K  (1)| 00:00:06 |  Q1,00 | PCWC |            |

|*  9 |          TABLE ACCESS STORAGE FULL| TEST4                      |    14M|
    94M|   144K  (1)| 00:00:06 |  Q1,00 | PCWP |            |

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


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

   9 - storage("FREQUENCY"='MO')
       filter("FREQUENCY"='MO')

Result Cache Information (identified by operation id):
------------------------------------------------------

   1 - column-count=2; dependencies=(CSC.TEST4); attributes=(vpd); parameters=(n
ls); name="select /*+ RESULT_CACHE */  iscore,count(*) from csc.test4 group by i
score"


Note
-----
   - Degree of Parallelism is 64 because of table property


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        452  bytes sent via SQL*Net to client
        500  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed

SQL> exit
sqlplus myuser2@csc

SQL> set timing on;
SQL> set autotrace on;
SQL> select /*+ RESULT_CACHE */  iscore,count(*) from csc.test4 group by iscore;

ISC   COUNT(*)
--- ----------
YES     250032
NO     9886736

Elapsed: 00:00:05.35

Execution Plan
----------------------------------------------------------
Plan hash value: 605068319

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

| Id  | Operation                         | Name                       | Rows  |
 Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |

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

|   0 | SELECT STATEMENT                  |                            |     2 |
    14 |   144K  (1)| 00:00:06 |        |      |            |

|   1 |  RESULT CACHE                     | 4rd3f3ph653gmdw3pn6p63007f |       |
       |            |          |        |      |            |

|   2 |   PX COORDINATOR                  |                            |       |
       |            |          |        |      |            |

|   3 |    PX SEND QC (RANDOM)            | :TQ10001                   |     2 |
    14 |   144K  (1)| 00:00:06 |  Q1,01 | P->S | QC (RAND)  |

|   4 |     HASH GROUP BY                 |                            |     2 |
    14 |   144K  (1)| 00:00:06 |  Q1,01 | PCWP |            |

|   5 |      PX RECEIVE                   |                            |     2 |
    14 |   144K  (1)| 00:00:06 |  Q1,01 | PCWP |            |

|   6 |       PX SEND HASH                | :TQ10000                   |     2 |
    14 |   144K  (1)| 00:00:06 |  Q1,00 | P->P | HASH       |

|   7 |        HASH GROUP BY              |                            |     2 |
    14 |   144K  (1)| 00:00:06 |  Q1,00 | PCWP |            |

|   8 |         PX BLOCK ITERATOR         |                            |    10M|
    67M|   144K  (1)| 00:00:06 |  Q1,00 | PCWC |            |

|*  9 |          TABLE ACCESS STORAGE FULL| TEST4                      |    10M|
    67M|   144K  (1)| 00:00:06 |  Q1,00 | PCWP |            |

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


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

   9 - storage("FREQUENCY"='QU')
       filter("FREQUENCY"='QU')

Result Cache Information (identified by operation id):
------------------------------------------------------

   1 - column-count=2; dependencies=(CSC.TEST4); attributes=(vpd); parameters=(n
ls); name="select /*+ RESULT_CACHE */  iscore,count(*) from csc.test4 group by i
score"


Note
-----
   - Degree of Parallelism is 64 because of table property


Statistics
----------------------------------------------------------
        417  recursive calls
          0  db block gets
   30828823  consistent gets
   30728561  physical reads
          0  redo size
        452  bytes sent via SQL*Net to client
        501  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed

SQL> select /*+ RESULT_CACHE */  iscore,count(*) from csc.test4 group by iscore;

ISC   COUNT(*)
--- ----------
YES     250032
NO     9886736

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 605068319

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

| Id  | Operation                         | Name                       | Rows  |
 Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |

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

|   0 | SELECT STATEMENT                  |                            |     2 |
    14 |   144K  (1)| 00:00:06 |        |      |            |

|   1 |  RESULT CACHE                     | 4rd3f3ph653gmdw3pn6p63007f |       |
       |            |          |        |      |            |

|   2 |   PX COORDINATOR                  |                            |       |
       |            |          |        |      |            |

|   3 |    PX SEND QC (RANDOM)            | :TQ10001                   |     2 |
    14 |   144K  (1)| 00:00:06 |  Q1,01 | P->S | QC (RAND)  |

|   4 |     HASH GROUP BY                 |                            |     2 |
    14 |   144K  (1)| 00:00:06 |  Q1,01 | PCWP |            |

|   5 |      PX RECEIVE                   |                            |     2 |
    14 |   144K  (1)| 00:00:06 |  Q1,01 | PCWP |            |

|   6 |       PX SEND HASH                | :TQ10000                   |     2 |
    14 |   144K  (1)| 00:00:06 |  Q1,00 | P->P | HASH       |

|   7 |        HASH GROUP BY              |                            |     2 |
    14 |   144K  (1)| 00:00:06 |  Q1,00 | PCWP |            |

|   8 |         PX BLOCK ITERATOR         |                            |    10M|
    67M|   144K  (1)| 00:00:06 |  Q1,00 | PCWC |            |

|*  9 |          TABLE ACCESS STORAGE FULL| TEST4                      |    10M|
    67M|   144K  (1)| 00:00:06 |  Q1,00 | PCWP |            |

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


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

   9 - storage("FREQUENCY"='QU')
       filter("FREQUENCY"='QU')

Result Cache Information (identified by operation id):
------------------------------------------------------

   1 - column-count=2; dependencies=(CSC.TEST4); attributes=(vpd); parameters=(n
ls); name="select /*+ RESULT_CACHE */  iscore,count(*) from csc.test4 group by i
score"


Note
-----
   - Degree of Parallelism is 64 because of table property


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        452  bytes sent via SQL*Net to client
        501  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed

SQL>

As we can see RAS works nicely with The Result Cache – for both users the first query does not use the cache, the second does. This can actually not been seen in the plan itself- but in the execution times and statistics. Resulting counts are correct (and different between the users) – take note as well of the timings , the whole table has more than 500 million rows.

But how does this actually work – what is stored in the cache ? We see already above that the cache ids (RESULT CACHE line in the plan) are different. There is a Result Cache specific trace event :

 ALTER SESSION SET EVENTS '43905 trace name context forever, level 1';

Using this event we see the following – first execution for the second user:

 

Top level query block
 Objects for this node : 97033
 -------------------------------------
 Query[len=74]: select /*+ RESULT_CACHE */ iscore,count(*) from csc.test4 group by iscore
 Normalized Query[len=83]: 172 1?"ISCORE" 219 1?"COUNT" 225 232 229 70 1?"CSC" 226 1?"TEST4" 75 18 1?"ISCORE"
 Cache id1: 4rd3f3ph653gmdw3pn6p63007f
 Cache id2: 4rd3f3ph653gmdw3pn6p63007f
 Column count: 2
 NLS Dependent: YES User Referenced: NO Ordered: NO Auto: NO XLATE: NO
 Dependencies: (97033 - CSC.TEST4)
 VPD Predicates: ((((FREQUENCY= 'QU'))));

We see that the Result Cache entry is parametrised with the “VPD predicate” – this is reused for RAS – one can see a reference to this as well in the execution plan above. So this is how the Result Cache handles RAS/VPD – there is a cache entry not only per normalized query, but as well per predicate(s).

Advertisements

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