Indexing Nulls by including constants in the index? Careful now

This is just a quick demo to show how sometimes Oracle can get a bit too keen on replacing expressions with virtual columns that are declared. The problem you might have is one that was seen on the OTN recently https://community.oracle.com/message/14281753#14281753; the basic scenario is you have a nullable column that you wish to index  the NULL values for. The problem with indexing nulls is that there’ll be no entry in an index if ALL columns of the index are NULL so a typical workaround is to introduce another column to the index that won’t be NULL, an easy way of doing this is to use a fixed value. Of course, you would normally choose a small value so that your index wasn’t too large – in the past I’ve used ‘x’. The OP of this thread chose -999999 which is rather large (byte-wise) but shouldn’t strike you as being particular bad.

Here’s the problem, if you then use this value as a filter on the table, Oracle can (and will) do some transformation to replace your value with the hidden virtual column (which it created to support the function based index).

A demo using 12.2.0.1:

ANDY@pdb1>create table careful_of_workarounds
  2  as
  3  select * from all_objects;

Table created.

ANDY@pdb1>alter table careful_of_workarounds modify object_id null;

Table altered.

ANDY@pdb1>create index careful_of_workarounds_idx on careful_of_workarounds (object_id);

Index created.

ANDY@pdb1>select min(object_id), max(object_id) from CAREFUL_OF_WORKAROUNDS;

MIN(OBJECT_ID) MAX(OBJECT_ID)                                                                                                                                                                           
-------------- --------------                                                                                                                                                                           
             2          73632                                                                                                                                                                          

ANDY@pdb1>explain plan for select * from careful_of_workarounds where object_id = 987654321;

Explained.
ANDY@pdb1>select * from table(dbms_xplan.display)
  2  /

PLAN_TABLE_OUTPUT                                                                                                                                                                                       
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1786522280                                                                                                                                                                             
                                                                                                                                                                                                        
------------------------------------------------------------------------------------------------------------------                                                                                      
| Id  | Operation                           | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |                                                                                      
------------------------------------------------------------------------------------------------------------------                                                                                      
|   0 | SELECT STATEMENT                    |                            |     1 |   135 |     2   (0)| 00:00:01 |                                                                                      
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| CAREFUL_OF_WORKAROUNDS     |     1 |   135 |     2   (0)| 00:00:01 |                                                                                      
|*  2 |   INDEX RANGE SCAN                  | CAREFUL_OF_WORKAROUNDS_IDX |     1 |       |     1   (0)| 00:00:01 |                                                                                      
------------------------------------------------------------------------------------------------------------------                                                                                      
                                                                                                                                                                                                        
Predicate Information (identified by operation id):                                                                                                                                                     
---------------------------------------------------                                                                                                                                                     
                                                                                                                                                                                                        
   2 - access("OBJECT_ID"=987654321)                                                                                                                                                                   

14 rows selected.

ANDY@pdb1>create index careful_of_workarounds_eg on careful_of_workarounds (object_id,987654321);

Index created.

ANDY@pdb1>explain plan for select * from careful_of_workarounds where object_id = 987654321;

Explained.
ANDY@pdb1>select * from table(dbms_xplan.display)
  2  /

PLAN_TABLE_OUTPUT                                                                                                                                                                                       
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1786522280                                                                                                                                                                             
                                                                                                                                                                                                        
------------------------------------------------------------------------------------------------------------------                                                                                      
| Id  | Operation                           | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |                                                                                      
------------------------------------------------------------------------------------------------------------------                                                                                      
|   0 | SELECT STATEMENT                    |                            |     1 |   135 |     2   (0)| 00:00:01 |                                                                                      
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| CAREFUL_OF_WORKAROUNDS     |     1 |   135 |     2   (0)| 00:00:01 |                                                                                      
|*  2 |   INDEX RANGE SCAN                  | CAREFUL_OF_WORKAROUNDS_IDX |     1 |       |     1   (0)| 00:00:01 |                                                                                      
------------------------------------------------------------------------------------------------------------------                                                                                      
                                                                                                                                                                                                        
Predicate Information (identified by operation id):                                                                                                                                                     
---------------------------------------------------                                                                                                                                                     
                                                                                                                                                                                                        
   2 - access("OBJECT_ID"=987654321)                                                                                                                                                                   

14 rows selected.

ANDY@pdb1>create index careful_of_workarounds_eg2 on careful_of_workarounds (object_id,-987654321);

Index created.

ANDY@pdb1>explain plan for select * from careful_of_workarounds where object_id = -987654321;

Explained.
ANDY@pdb1>select * from table(dbms_xplan.display)
  2  /

PLAN_TABLE_OUTPUT                                                                                                                                                                                       
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2863211044                                                                                                                                                                             
                                                                                                                                                                                                        
--------------------------------------------------------------------------------------------                                                                                                            
| Id  | Operation         | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |                                                                                                            
--------------------------------------------------------------------------------------------                                                                                                            
|   0 | SELECT STATEMENT  |                        |     1 |   135 |   375   (1)| 00:00:01 |                                                                                                            
|*  1 |  TABLE ACCESS FULL| CAREFUL_OF_WORKAROUNDS |     1 |   135 |   375   (1)| 00:00:01 |                                                                                                            
--------------------------------------------------------------------------------------------                                                                                                            
                                                                                                                                                                                                        
Predicate Information (identified by operation id):                                                                                                                                                     
---------------------------------------------------                                                                                                                                                     
                                                                                                                                                                                                        
   1 - filter("OBJECT_ID"=(-987654321))                                                                                                                                                                

13 rows selected.
ANDY@pdb1>explain plan for select /*+index(careful_of_workarounds CAREFUL_OF_WORKAROUNDS_IDX)*/* from careful_of_workarounds where object_id = -987654321;

Explained.

ANDY@pdb1>select * from table(dbms_xplan.display)
  2  /

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2312772364

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                            |     1 |   135 |   154   (1)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| CAREFUL_OF_WORKAROUNDS     |     1 |   135 |   154   (1)| 00:00:01 |
|*  2 |   INDEX FULL SCAN                   | CAREFUL_OF_WORKAROUNDS_IDX |     1 |       |   153   (1)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------

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

   2 - filter("OBJECT_ID"=(-987654321))
See how we are now forced to use our predicate as a filter rather than an access predicate on the index – causing us to have to read the ENTIRITY of the index. This seems to only be the case with the negative valued expression (using -1 did exactly the same), but not for positive. A brief check against dba_tab_cols might shed some hints:
ANDY@pdb1>select column_name, data_default, column_id from dba_tab_cols where table_name ='CAREFUL_OF_WORKAROUNDS' and data_default is not null
  2  /

COLUMN_NAME                    DATA_DEFAULT                                        COLUMN_ID
------------------------------ -------------------------------------------------- ----------
SYS_NC00027$                   987654321
SYS_NC00028$                   (-987654321)
SYS_NC00029$                   (-1)
The positive valued virtual column has no brackets (or parenthesise) surrounding it but the negatively valued ones do – perhaps this is what would class the negatives as a replaceable expression rather than a fixed value.
Okay, that might not be so bad because it’s just one column right? Well…
ANDY@pdb1>create index care_of_workarounds_really on careful_of_workarounds (created_vsnid);

Index created.

ANDY@pdb1>explain plan for select /*+index(careful_of_workarounds care_of_workarounds_really)*/* from careful_of_workarounds where created_vsnid = -987654321;

Explained.

ANDY@pdb1>select * from table(dbms_xplan.display)
  2  /

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2532905546

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                            |     1 |   135 |     0   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| CAREFUL_OF_WORKAROUNDS     |     1 |   135 |     0   (0)| 00:00:01 |
|*  2 |   INDEX FULL SCAN                   | CARE_OF_WORKAROUNDS_REALLY |     1 |       |     0   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------

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

   2 - filter("CREATED_VSNID"=(-987654321))
Oh dear!
Are you safe if you use binds though?
ANDY@pdb1>var object_id number=-987654321
ANDY@pdb1>select /*+index(careful_of_workarounds careful_of_workarounds_idx)*/* from careful_of_workarounds where object_id = :object_id;

no rows selected

ANDY@pdb1>select * from table(dbms_xplan.display_cursor(format=>'typical'))
  2  /

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  9cjjrakjnam15, child number 0
-------------------------------------
select /*+index(careful_of_workarounds careful_of_workarounds_idx)*/*
from careful_of_workarounds where object_id = :object_id

Plan hash value: 1786522280

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                            |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| CAREFUL_OF_WORKAROUNDS     |     1 |   135 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | CAREFUL_OF_WORKAROUNDS_IDX |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------

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

   2 - access("OBJECT_ID"=:OBJECT_ID)
Yes! You may have guessed this but it’s always better to verify. Also, I should clarify that using ‘x’ doesn’t fall into the same problem

SQL*Plus 12.2 (so close)

There’s a few new features in the latest SQL*Plus client that look like they could be super useful, I haven’t seen much mentioning of them so I thought I’d take up the job. The documentation has the changes in https://docs.oracle.com/database/122/SQPUG/release-changes.htm#SQPUG648 so review the docs for the facts.

HISTORY

Syntax
HIST[ORY] [n RUN | EDIT | DEL[ETE]] | [CLEAR | LIST]
Enables users to run, edit, or delete previously used SQL*Plus, SQL, or PL/SQL commands from the history list in the current session

I’m only going to briefly mention history – it’s not something I see myself using that much, I already have F7 in Windows. The one benefit this new option has is that it can work with ed, albeit with some manual work.
ANDY@pdb1>set history 5
ANDY@pdb1>select * from dual;

D
-
X

ANDY@pdb1>ed
Wrote file afiedt.buf

  1* select dummy from dual
ANDY@pdb1>/

D
-
X

ANDY@pdb1>history
  1  select * from dual;
  2  ed
     select dummy from dual
     /
  3  /
Most statements are rerunnable by calling 
History n run
But see how statement 2 has included the ed command? That prevents you from running it directly, you can call
history 2 edit
And then modify it before running, but you can’t just run the command. Not quite there IMO.

Support for input binding by using the VARIABLE command. The VARIABLE command now supports input binding which can be used in SQL and PL/SQL statements

It has long been a gripe of mine that in order to use bind variables in SQL*Plus, you need to declare them and then run a plsql block in order to set them:
var nNumber number
exec :nNumber := 10
My problem here is that in order to save you parsing one statement, you parse another statement instead!
In 12.2, Oracle has extended the variable command to allow you to set the value of the bind as you declare it. So our simple example now becomes
var nNumber number=10
0 parses.
My main problem with the new syntax is that you can’t use defined variables, so if you have a script that allows for input from the user, you can’t use that for your binds without the plsql call.
ANDY@pdb1>var nNumber number=&1
 SP2-0425: "&1" is not a valid NUMBER
Whilst we’re on the subject of parses, the next new feature is statement caching. Statement caching is well known about in general application programming, it basically makes the difference between a soft parse and no parse (the use of shareable sql saves you the hard parse).
A quick demo of the old behaviour (and default 12.2 behaviour)
ANDY@pdb1>get sql_find
 1  select * from (select sql_id, last_active_time, executions, parse_calls, substr(sql_text,1,100) sql_text
 2  from v$sql where sql_text not like '%v$sql%' and upper(sql_text) like UPPER('%&1%') order by last_active_time desc)
 3* where rownum <=15

alter system flush shared_pool;
 var nNumber number
 exec :nNumber := 5
 select * from dual where :nNumber = 10;
 exec :nNumber := 10
 select * from dual where :nNumber = 10;

@sql_find ":nNumber"

SQL_ID        LAST_ACTIVE_TIME    EXECUTIONS PARSE_CALLS SQL_TEXT
 ------------- ------------------- ---------- ----------- ---------------------------------------------
 2azpdtw4dhbbn 29/03/2017 00:54:11          1           1 BEGIN :nNumber := 10; END;
 fhsvcq3xzfmc1 29/03/2017 00:54:11          1           1 BEGIN :nNumber := 5; END;
 2mty35xpvmgph 29/03/2017 00:54:11          2           2 select * from dual where :nNumber = 10

We can see that our parse calls are much more than you'd expect, and we've also got those pesky ones I was talking about earlier. The separate statements for assigning our bind variables were all hard parsed! This is how 12.2 compares:

alter system flush shared_pool;
 set statementcache 50
 var nNumber number=5
 select * from dual where :nNumber = 10;
 var nNumber number=10
 select * from dual where :nNumber = 10;

@sql_find ":nNumber"

SQL_ID        LAST_ACTIVE_TIME    EXECUTIONS PARSE_CALLS SQL_TEXT
 ------------- ------------------- ---------- ----------- -----------------------------------------------------------
 2mty35xpvmgph 29/03/2017 00:56:08          2           1 select * from dual where :nNumber = 10
All of those parse calls are GONE, fantastic! I’m not going to suggest that your actual production DB has a problem caused by SQL*Plus parsing, but this does give you the ability to demonstrate the effects of some typical application performance enhancements. It certainly won’t harm your shared pool.

SET ROWPREFETCH {1 | n}

There’s one more thing that I want to mention, row prefetching. This is something that I wasn’t clear about to start with, the docs describe it as:
Sets the number of rows that SQL*Plus will prefetch from the database at one time.
If we compare this to the arraysize:
Sets the number of rows, called a batch, that SQL*Plus will fetch from the database at one time.
What’s the difference between fetching and prefetching? A demo may help
alter system flush shared_pool;
 set arraysize 10
 set rowprefetch 50
 set pagesize 10
 set pause on
The pagesize and pause are there so that we display a few rows at a time in sqlplus, our cursor remains open and we can see what has been fetched from the cursor from another session. I’ve added fetches and rows_processed to my @sql_find script so we can monitor that too.
ANDY@pdb1>select * from dual connect by rownum <=110;
 

At this point we haven't displayed any rows. If we check in our other session

ANDY@pdb1>@sql_find dual%connect

SQL_ID        LAST_ACTIVE_TIME    EXECUTIONS PARSE_CALLS    FETCHES ROWS_PROCESSED SQL_TEXT
 ------------- ------------------- ---------- ----------- ---------- -------------- -------------------------------------------------
 40p6csxf1r8p3 29/03/2017 01:07:27          1           0          1             50 select * from dual connect by rownum <=110
Apparently we’ve already processed 50 rows in 1 fetch, that’s our prefetch setting coming into play. What happens when we release our pause and get the next page?
D
 -
 X
 X
 X
 X
 X
 X
 X

 
SQL_ID        LAST_ACTIVE_TIME    EXECUTIONS PARSE_CALLS    FETCHES ROWS_PROCESSED SQL_TEXT
 ------------- ------------------- ---------- ----------- ---------- -------------- ---------------------------------------------
 40p6csxf1r8p3 29/03/2017 01:07:27          1           0          1             50 select * from dual connect by rownum <=110
We’ve not done anything with the SQL but we’ve got 7 rows back! That’s because the rows have already been prefetched by sqlplus and are just sitting on the client side, no need to request them from the DB. Let’s do a few more pages. Once we’ve read all 50 of our fetched rows, the next call does another fetch
SQL_ID        LAST_ACTIVE_TIME    EXECUTIONS PARSE_CALLS    FETCHES ROWS_PROCESSED SQL_TEXT
 ------------- ------------------- ---------- ----------- ---------- -------------- ---------------------------------------------------
 40p6csxf1r8p3 29/03/2017 01:20:58          1           0          2            110 select * from dual connect by rownum <=110
You’ll notice that the second fetch was responsible for 60 rows being processed – honestly this was a surprise, it’s the total of our arraysize and rowprefetch but it’s also the total number of rows in the query.
SQL_ID        LAST_ACTIVE_TIME    EXECUTIONS PARSE_CALLS    FETCHES ROWS_PROCESSED SQL_TEXT
 ------------- ------------------- ---------- ----------- ---------- -------------- ---------------------------------------------------
 40p6csxf1r8p3 29/03/2017 01:21:19          1           0          3            110 select * from dual connect by rownum <=110
The third fetch didn’t process any rows, this is normal as it was our final fetch to mark the cursor as finished.
What happens when we use an arraysize of 5 and a total query size of 200 rows (keeping our rowprefetch 50)?
Here is the @sql_find results at the point of second fetch:
 SQL_ID        LAST_ACTIVE_TIME    EXECUTIONS PARSE_CALLS    FETCHES ROWS_PROCESSED SQL_TEXT
 ------------- ------------------- ---------- ----------- ---------- -------------- ------------------------------------------------
 3rwacgmnkkabu 29/03/2017 01:33:26          1           1          1             50 select * from dual connect by rownum <=200

ANDY@pdb1>/

SQL_ID        LAST_ACTIVE_TIME    EXECUTIONS PARSE_CALLS    FETCHES ROWS_PROCESSED SQL_TEXT
 ------------- ------------------- ---------- ----------- ---------- -------------- ------------------------------------------------
 3rwacgmnkkabu 29/03/2017 01:34:02          1           1          2            105 select * from dual connect by rownum <=200
Yes, we do a prefetch and a fetch worth of rows for the secondary fetch.
Finally, Oracle has baked this new behaviour into a fastmode for SQL*Plus that can be used simply with:
sqlplus -f

ARRAYSIZE = 100

  • LOBPREFETCH = 16384
  • PAGESIZE = 50000
  • ROWPREFETCH = 2
  • STATEMENTCACHE = 20
This simply sets some parameters to what Oracle believes are best for general performance, I have no idea why you wouldn’t use them by default – perhaps you’d rather use your own mix. In my honest opinion it is a no brainer to enable these options, you don’t need to set any of the parameters particularly high to start seeing the benefits.
As an aside, the pagesize was a surprise to me, I wonder how noticeable a change in pagesize is on real performance.
Perhaps the real improvements would be obvious for those huge reports or extracts of tables. The markup command has also been enabled for csv output, this is something that’s been missing for a long time. I’ll plan to benchmark csv extract generation of a big table (e.g for sqlldr input) using old sqlplus, fastmode sqlplus and sqlCL but that’s another day.
Let me know what you think of the new settings and if you manage to leverage them for substantial improvements.

The golden rule of indexing

This is something that I have to remind people of frequently on the OTN so I thought it was worthy of a proper blog post where I can properly demonstrate the point.
So here is this very simple but very important rule:

When you have multiple columns in your index, you can access using the column in the index so long as you use equality predicates on all prior columns in the index

An access predicate is one that can be used to reduce the amount we read from an index, the alternative is a filter predicate which reduces the amount we return from our read. Think how we access a table with an index scan – we only read part of the table, but we filter a table during a full table scan – we read the entire thing (we just return less rows). It’s easier to demonstrate with an example, so let’s make a big table with a few columns.
create table golden_indexes as
with gen as (select /*+materialize*/* from dual connect by rownum <=10000)
select rownum pk_col, sysdate-mod(rownum,365) date_col, trunc(dbms_random.value(0,100)) col_100, dbms_random.string('x',100) padding_col
from   gen
cross join gen 
where rownum <=100000000
/

select count(*) from golden_indexes;

  COUNT(*)
----------
 100000000
 
create index golden_indexes_idx1 on golden_indexes (date_col,col_100);
create index golden_indexes_idx2 on golden_indexes (col_100,date_col);
We have 100,000,000 rows in our table with 100 different values of some number and 365 different values of some date.
We now have a query
select date_col, padding_col 
from   golden_indexes
where  col_100  = 50
and    date_col >= trunc(sysdate)-10 
and    date_col <  trunc(sysdate)-9;
Because we know our data, we know that this query is going to find 100,000,000 / 100 / 365 rows = 2700 rows. This is probably appropriate for an index use rather than a full tablescan – we don’t want to read 100 million rows.
Let’s apply our golden rule with this query to our two indexes, first the one leading date_col:
The first column is date_col, we can use our filter on date_col as an access predicate (there’s no prior columns in the index) so we can use it to provide an index selectivity of 1/365. We can’t apply our filter on col_100 as an access predicate because the filter on date_col is not an equality predicate – it is a range predicate.
Of course, we can still use the col_100 predicate as a filter predicate on the index, it just means we can’t use it to aim our reads against the index. This gives us an index selectivity of 1/365 and table selectivity of 1/365*1/100 = 1/36500
And now for the second index, we can use our col_100 predicate to provide an access predicate giving us an index selectivity of 1/100, we can then apply our date_col predicate as an access predicate as well as the prior filter was equality, this gives us a total index selectivity of 1/100 * 1/365 = 1/36500. No further filters exist so our table selectivity is also 1/36500 (as before)
To demonstrate, we can see the number of leaf blocks in the indexes by querying their stats (they’ll be accurate as we’ve only just built it, real life index stats for you will probably be more inaccurate)
select index_name, blevel, leaf_blocks from user_indexes where table_name = 'GOLDEN_INDEXES' order by 1;


INDEX_NAME                         BLEVEL LEAF_BLOCKS
------------------------------ ---------- -----------
GOLDEN_INDEXES_IDX1                     3      306612
GOLDEN_INDEXES_IDX2                     3      306605
We should expect to read 306612 / 365 = 840 blocks in our index range scan using the first index and read 306605 / 36500 = 9 blocks in our range scan of our second index. All index range scans start with traversing the branch blocks of an index so we should expect to read a block per level of the index, both are 3 in this case.
We can demonstrate this by using row source execution statistics (discussed in https://ctandrewsayer.wordpress.com/2017/03/21/4-easy-lessons-to-enhance-your-performance-diagnostics/)
set serverout off
select /*+index_rs_asc(gi golden_indexes_idx1) NO_INDEX_SS(gi golden_indexes_idx1) gather_plan_statistics*/
       count(*)
from   golden_indexes gi
where  col_100  = 50
and    date_col >= trunc(sysdate)-10 
and    date_col <  trunc(sysdate)-9;
select * from table(dbms_xplan.display_cursor(format=>'allstats typical last'));

Plan hash value: 2147962759

------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name                | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                     |      1 |        |       |  1691 (100)|          |      1 |00:00:00.05 |     844 |
|   1 |  SORT AGGREGATE    |                     |      1 |      1 |    11 |            |          |      1 |00:00:00.05 |     844 |
|*  2 |   FILTER           |                     |      1 |        |       |            |          |   2710 |00:00:00.06 |     844 |
|*  3 |    INDEX RANGE SCAN| GOLDEN_INDEXES_IDX1 |      1 |     55 |   605 |  1691   (1)| 00:00:01 |   2710 |00:00:00.05 |     844 |
------------------------------------------------------------------------------------------------------------------------------------

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

   2 - filter(TRUNC(SYSDATE@!)-9>TRUNC(SYSDATE@!)-10)
   3 - access("DATE_COL">=TRUNC(SYSDATE@!)-10 AND "COL_100"=50 AND "DATE_COL"<TRUNC(SYSDATE@!)-9)
       filter("COL_100"=50)

      
select /*+index(gi golden_indexes_idx2) gather_plan_statistics*/
       count(*)
from   golden_indexes gi
where  col_100  = 50
and    date_col >= trunc(sysdate)-10 
and    date_col <  trunc(sysdate)-9;
select * from table(dbms_xplan.display_cursor(format=>'allstats typical last'));

Plan hash value: 1778390985

------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name                | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                     |      1 |        |       |    20 (100)|          |      1 |00:00:00.01 |      12 |
|   1 |  SORT AGGREGATE    |                     |      1 |      1 |    11 |            |          |      1 |00:00:00.01 |      12 |
|*  2 |   FILTER           |                     |      1 |        |       |            |          |   2710 |00:00:00.01 |      12 |
|*  3 |    INDEX RANGE SCAN| GOLDEN_INDEXES_IDX2 |      1 |     55 |   605 |    20   (0)| 00:00:01 |   2710 |00:00:00.01 |      12 |
------------------------------------------------------------------------------------------------------------------------------------

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

   2 - filter(TRUNC(SYSDATE@!)-9>TRUNC(SYSDATE@!)-10)
   3 - access("COL_100"=50 AND "DATE_COL">=TRUNC(SYSDATE@!)-10 AND "DATE_COL"<TRUNC(SYSDATE@!)-9)

The plan helps us out, it tells us what it is using as an access predicate and what gets used as a filter predicate in the predicates section. Unfortunately for us, it includes filter predicates in the access predicates (IMO this is a mistake).

The row source stats demonstrate that the work done by range scan is much less when the columns are ordered with the columns used in equality predicates first. See how our buffers on the index range scan lines are almost exactly what I mentioned before. This is fairly conclusive that Oracle was able to limit what it read from the index using the predicate only when prior predicates are equalities.

You’ll notice I’ve used a no_index_ss hint in the first query, this was because otherwise the CBO decided it was a good idea to skip scan the index because there are few distinct values in our range it decided that it could just skip around them (which is sensible but probably not realistic where you have dates that have time elements that vary). I’ll make a note of this and hopefully give it the attention it deserves in another post.
Something else that will have a large influence on our cost in using the two indexes here is the clustering factor – we are going to 2710 rows (although the optimizer has only predicted 55) it would certainly be better if these rows were clustered in few blocks rather than spread to 2710 different blocks. Because my data was generated to have the interesting data all over the table, the clustering factors of both indexes were huge. I didn’t share these stats as they would certainly not be representative of your real data, my tables took hours to generate and index on my little Surface Pro and I didn’t have time to make it more realistic – another time (or homework for the reader)?
The problem with real data is that usually the clustering factor of your indexes leading with a date column (which usually relates in some way to insert date) will have a much lower clustering factor than if it began with your equality predicate column. The reality is that using either index you will visit the same table blocks so both clustering factors should be the same. If you expect to return a large number of rows from an index and you find that the CBO tries to avoid the real best index you may need to play with the clustering factor of the index manually – in this case you would copy the clustering factor from the reverse ordered index, or you would increase the clustering factor manually for the “bad” index (or just not make it!). 12c introduced some new functionality for clustering factor statistics gathering that I haven’t yet played with – maybe Oracle has solved this problem, I will update if I find a nice solution.

Summary

Stick the columns you do range scans on last in the index, filters that get equality predicates should come first. 
Of course, there’s always rare cases when this doesn’t apply. One example, if you wish to support order by queries without a sort then the columns should be in the same order as they appear in your order by clause. 
-Aside
You’ll notice I changed my query to do a count(*) rather than selecting actual columns from the table; this was not just to reduce output. When going between the index and table, Oracle releases it’s pins on the index buffers, this means you’ll see more consistent reads in this situation. When Oracle uses the index access by batch rowid (new for 12.1), Oracle will keep reading from the index until it has a sufficient batch of rowids (I’m not completely clear on the full mechanics) before going to the table, so we only have to release and reget our index lead blocks rarely.

4 EASY Lessons to enhance your performance diagnostics

This is just a quick post that covers everything I believe anyone should know about their Oracle performance problems. Most problems seen on the OTN are trivial to solve as soon as you can see this further information, and this information really is EASY to get.

1) Use dbms_application_info

This is a small but powerful package, all it does it is tell Oracle what your session is doing. It means that information is recorded against your v$session row, so when you notice some session is burning through CPU or writing tons of redo
or doing tons of physical IO, you can easily associate that session with the application process so you know exactly what code is being called. Think about it the other way, some end user complains that their critical process is taking a long time
you have hundreds of sessions connected, how on earth do you associate what the end user is doing and their session on the database? Their client identifier, because the application uses this package to set it.
Seriously, start instrumenting your code with it everywhere. Oracle already does this in dbms_scheduler jobs – by default a job will have the module of dbms_scheduler and the action will be the job_name.

Here’s a basic query that I can use to see what sessions exist for a module and what are they running and what event are they currently waiting on (or most recently waited on).

ANDY@pdb1>select sid, sql_id, sql_child_number, event, state from v$session where module ='SillyModule';

       SID SQL_ID        SQL_CHILD_NUMBER EVENT                          STATE
---------- ------------- ---------------- ------------------------------ -------------------
       259 74ah5jrb24sq5                0 latch: shared pool             WAITED SHORT TIME

2) You can’t get aggregate from detail

That last query tells us very little, you can’t assume that what a session was doing when you sampled it was what it’s been doing all day. Luckily there are many views aggregated over the life of the session that can help us.

v$session_event is one of the views I use all the time, here’s a quick and dumb script using it:

get session_event
var sid number
exec :sid := &1
select event, total_waits, time_waited, time_waited_micro, average_wait, max_wait
from v$session_event where sid = :sid
order by time_waited_micro;

If we call @session_event 259 (the sid from before) then we get the following:

ANDY@pdb1>@session_event 259

EVENT                          TOTAL_WAITS TIME_WAITED TIME_WAITED_MICRO AVERAGE_WAIT   MAX_WAIT
------------------------------ ----------- ----------- ----------------- ------------ ----------
buffer busy waits                        1           0                14            0          0
SQL*Net break/reset to client            4           0               630          .02          0
events in waitclass Other              278           0              3369            0          0
SQL*Net message to client               63           0              3495          .01          0
latch: shared pool                      33           2             17696          .05          0
Disk file operations I/O                 4           2             19183          .48          1
db file scattered read                 108          23            225372          .21          1
db file sequential read                181          24            239792          .13          0
SQL*Net message from client             62      354755        3547550028      5721.85     191541

This tells us that most of the wait events are down to SQLNet message from client, that’s because the client is SQLPlus and the session is mainly inactive until I tell it to do something. This is an idle event, nothing is really happening on the DB.
The second largest time waited is for db file sequential read, this is a wait event associated with index reads of tables. We can see we do 181 of these and on average they take 0.13 hundredths of a second (yes, that is the unit).
In (very close) third place is the db file scattered read event, this is associated with full table scans and.

It’s important to note that just because these wait events exist, doesn’t mean we can do much about them. It may be that it is much easier (which means cheaper in human cost) to solve whatever was causing the Disk file operations I/O and that might be okay.
Maybe your critical process only needs to be a 0.02 seconds quicker (okay maybe it’d be a little more in real life, it’s hard to demonstrate performance problems on a tablet computer!).

If a session isn’t waiting, it’s working, we also should know how much time it actually spent working – that is on the CPU. Luckily, that’s something else that’s captured for us, the v$sess_time_model view gives us this information and even helps explain why:
Here’s the output of a query against that same session using v$sess_time_model

ANDY@pdb1>select stat_name, value from v$sess_time_model where value > 0 and sid = :sid order by value
  2  /

STAT_NAME                                                             VALUE
---------------------------------------------------------------- ----------
repeated bind elapsed time                                             6802
failed parse elapsed time                                              8812
sequence load elapsed time                                            12471
connection management call elapsed time                              174237
hard parse (sharing criteria) elapsed time                           212315
PL/SQL compilation elapsed time                                      315875
PL/SQL execution elapsed time                                        888086
hard parse elapsed time                                             1815114
parse time elapsed                                                  1854917
DB CPU                                                            100468750
sql execute elapsed time                                          102401294
DB time                                                           102934270

We need to appreciate that some of these statistics overlap, the most obvious one being DB CPU time is part of DB time and part of it will be sql execute elapsed time too.
This output clearly tells us that our session was mainly working and not waiting, our DB CPU is a huge percentage of our DB time.

3) Read the REAL execution plans being executed

Stop using explain plan to tell you what’s going on, explain plan is flawed. Luckily, you can just ask Uncle Oracle to give you the real plan that has been used from memory. All you need to know is the sql_id, which you can identify
using v$session if a known session is executing it currently (remember if your session is instrumented with dbms_application_info this is EASY) or you can try and search v$sql – I have a script I use all the time for this, here’s a simplified version:

get sql_find
select * from (select sql_id, last_active_time, executions, substr(sql_text,1,100) sql_text from v$sql where sql_text not like '%v$sql%' and upper(sql_text) like UPPER('%&1%') order by last_active_time desc)
where rownum <=15
/

Another thing to remember is that the first session to parse a child cursor will have it’s module and action reportable in v$sql – another win for instrumentation. Once you have the sql_id, it’s just a matter of calling dbms_xplan.display_cursor

ANDY@pdb1>@sql_find "select object_type, count(*) from big_table group by object_type"

SQL_ID        LAST_ACTIVE_TIME    EXECUTIONS
------------- ------------------- ----------
SQL_TEXT
-------------------------------------------------------------------------------------------------------
22tq3p0gsd63k 20/03/2017 21:39:53          1
select object_type, count(*) from big_table group by object_type

ANDY@pdb1>select * from table(dbms_xplan.display_cursor('22tq3p0gsd63k'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------
SQL_ID  22tq3p0gsd63k, child number 0
-------------------------------------
select object_type, count(*) from big_table group by object_type

Plan hash value: 3721451536

-------------------------------------------------------------------------------------------------
| Id  | Operation           | Name      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |           |       |       |   377 (100)|          |       |       |
|   1 |  PARTITION LIST ALL |           |    35 |   350 |   377   (2)| 00:00:01 |     1 |    36 |
|   2 |   HASH GROUP BY     |           |    35 |   350 |   377   (2)| 00:00:01 |       |       |
|   3 |    TABLE ACCESS FULL| BIG_TABLE | 68112 |   665K|   374   (1)| 00:00:01 |     1 |    36 |
-------------------------------------------------------------------------------------------------


15 rows selected.

See how I just used the sql_id as the first argument? Sometimes a sql_id is responsible for many child cursors which could mean multiple plans here, if you know which child cursor you’re using (it’s v$session.sql_child_number) then you can use it as the second parameter

ANDY@pdb1>select * from table(dbms_xplan.display_cursor('22tq3p0gsd63k',0));

Would give you the same plan as earlier (you can see the child number in the previous output).

4) Row source execution statistics

But what if I told you, you could get Oracle to not only tell you the plan, but actually the REAL work done on each line of the plan? And you don’t even have to pay for the diagnostics pack? Yes, I’m not kidding. All you need to do is
tell Oracle to collect these extra statistics for your execution, that can be done in two ways

Either
set statistics_level=all
or
hint gather_plan_statistics

And then we just ask Oracle to share the collected information, this is just an extra parameter to the dbms_xplan call – the format parameter. I’ll demonstrate using the statistics_level parameter:

alter session set statistics_level=all;
select object_type, count(*) from big_table group by object_type;
select * from table(dbms_xplan.display_cursor('22tq3p0gsd63k',NULL,'allstats typical last'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  22tq3p0gsd63k, child number 0
-------------------------------------
select object_type, count(*) from big_table group by object_type

Plan hash value: 3721451536

-----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name      | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | Pstart| Pstop |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |           |        |       |   377 (100)|          |       |       |       |       |          |
|   1 |  PARTITION LIST ALL |           |     35 |   350 |   377   (2)| 00:00:01 |     1 |    36 |       |       |          |
|   2 |   HASH GROUP BY     |           |     35 |   350 |   377   (2)| 00:00:01 |       |       |  1394K|  1394K|  508K (0)|
|   3 |    TABLE ACCESS FULL| BIG_TABLE |  68112 |   665K|   374   (1)| 00:00:01 |     1 |    36 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------

Note
-----
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level

SQL_ID  22tq3p0gsd63k, child number 1
-------------------------------------
select object_type, count(*) from big_table group by object_type

Plan hash value: 3721451536

----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name      | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | Pstart| Pstop | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |           |      1 |        |       |   377 (100)|          |       |       |     35 |00:00:00.02 |    1703 |       |       |          |
|   1 |  PARTITION LIST ALL |           |      1 |     35 |   350 |   377   (2)| 00:00:01 |     1 |    36 |     35 |00:00:00.02 |    1703 |       |       |          |
|   2 |   HASH GROUP BY     |           |     36 |     35 |   350 |   377   (2)| 00:00:01 |       |       |     35 |00:00:00.02 |    1703 |  1394K|  1394K|  497K (0)|
|   3 |    TABLE ACCESS FULL| BIG_TABLE |     36 |  68112 |   665K|   374   (1)| 00:00:01 |     1 |    36 |  68112 |00:00:00.01 |    1703 |       |       |          |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------


36 rows selected.

You may notice that I used NULL for my child number, that’s because in changing this parameter I won’t be able to reuse other child cursor I’ve already created because I’ve changed my optimizer settings. I choose the format parameter of

format=>'allstats typical last'

That means:
include both memory and IO stats (who knows what could be important until you look)
include typical plan options (otherwise you won’t see the cost column and others)
only show statistics for the last execution (if you wanted to aggregate many executions then don’t use this, also this means that parallel slaves won’t have their statistics included so you wouldn’t use last there either)

The output tells us that we did 36 full table scans against big_table (starts column on line 3) this is because it has 36 partitions (it was partitioned when I was playing around with the auto list partitioning feature of 12.2 so has number of object_types + 1).
The total number of rows selected in those 36 full table scans is 68112 (A-Rows on line 3) and it took 1703 buffers to do this work (but no physical reads, this would be shown in the reads column which is missing because we did none).
Next the row source moves up to the hash group by, we can tell that this line started 36 times too, it happened once per partition – each time we only did a hash group by on the rows of one partition.
The total rows returned from our hash group by on line 2 is 35, it takes 497K in memory in order to execute these hash group bys.

dbms_xplan.display_cursor also has a very neat default option, it can use the most recently executed sql for that session without you knowing the sql_id. The thing to remember is that when you have serveroutput turned on, sqlplus (and other clients)
will automatically fire off it’s own queries after yours to check for lines in the dbms_output buffer – so turn it off first! Here’s a demo where I use the gather_plan_statistics hint to enable the row source execution statistics gathering

alter session set statistics_level=typical;
set serverout off 
select /*+gather_plan_statistics*/object_id from big_table where object_type='TABLE' and object_name like 'BIG_TABLE%';
select * from table(dbms_xplan.display_cursor(NULL,NULL,'allstats typical last'));

SQL_ID  g91kfkbn2m9a4, child number 0
-------------------------------------
select /*+gather_plan_statistics*/object_id from big_table where
object_type='TABLE' and object_name like 'BIG_TABLE%'

Plan hash value: 4051153611

---------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name      | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | Pstart| Pstop | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |           |      1 |        |       |    14 (100)|          |       |       |      0 |00:00:00.01 |      52 |
|   1 |  PARTITION LIST SINGLE|           |      1 |      1 |    29 |    14   (0)| 00:00:01 |   KEY |   KEY |      0 |00:00:00.01 |      52 |
|*  2 |   TABLE ACCESS FULL   | BIG_TABLE |      1 |      1 |    29 |    14   (0)| 00:00:01 |     3 |     3 |      0 |00:00:00.01 |      52 |
---------------------------------------------------------------------------------------------------------------------------------------------

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

   2 - filter("OBJECT_NAME" LIKE 'BIG_TABLE%')

Not only do we have our plan, we have our execution statistics and we have the sql_id. EASY!

But how does this help you? Well in a complex real life plan, you may have no idea where to start looking. By looking at the row source execution statistics you can see exactly where your time is going, you can choose exactly where to start your battle.

Summary

Hopefully, you’ll agree that these are really easy to do. You can’t start to solve a performance problem until you know what’s causing it, and these 4 things will help you identify that cause very quickly with little effort.

Peeked binds and cursor_sharing

This post is just a brief demo post inspired by an idea that was posted on the OTN by Franck Pachot https://community.oracle.com/ideas/17321. I’ll show that cursor_sharing=similar allows binds to be peeked when a frequency histogram exists against the column. The behaviour depends on Adaptive Cursor Sharing which is one of those complex beasts that works very nicely in your test environments but in the real world is a bit of a mystery.

The problem is that a cursor is only marked bind aware when certain combinations of bind variables are used, in testing you might do lowly selective bind, highly selective bind, highly selective bind and ACS will kick in on the third. If you do lowly selective bind, lowly selective bind, highly selective bind, highly selective bind: ACS won’t kick in yet so you may dismiss it as not working. If you want to learn more then https://www.amazon.co.uk/d/Books/Database-Problem-Solving-Troubleshooting-Handbook/0134429206 has a chapter on the behaviour.

But let’s not get side tracked, on to the demo. First we create a table that obviously has some skew over one of the columns, I’ve used a hard coded rownum limit so that if you were to copy, you’d get the same output. I’ll also do some setup here to make sure we have a frequency histogram and I’ll turn off serverout so we can easily see our plans.

set serverout off
set feed off
drop table skewed_data;
create table skewed_data
as
select rownum pk_col
      ,trunc(ln(rownum)) skew_col
      ,dbms_random.string('x',500) padding_col
from   all_objects where rownum <=93502;
create unique index skewed_data_pk on skewed_data(pk_col);
create index skewed_data_skew_idx on skewed_data (skew_col);
select count(*) from skewed_data;
 COUNT(*)
---------
    93502
select column_name, num_distinct, histogram 
from   user_tab_columns 
where  table_name = 'SKEWED_DATA';
COLUMN_NAME         NUM_DISTINCT HISTOGRAM
------------------- ------------ ---------------
PK_COL                     93502 NONE
SKEW_COL                      12 NONE
PADDING_COL                93312 NONE
select skew_col 
      ,count(*)
from   skewed_data
group by skew_col
order by 1;
  SKEW_COL   COUNT(*)
---------- ----------
         0          2
         1          5
         2         13
         3         34
         4         94
         5        255
         6        693
         7       1884
         8       5123
         9      13923
        10      37848
        11      33628
exec dbms_stats.gather_table_stats(user,'SKEWED_DATA',method_opt=>'for columns size 254 skew_col')
select column_name, num_distinct, histogram 
from   user_tab_columns 
where  table_name = 'SKEWED_DATA';
COLUMN_NAME         NUM_DISTINCT HISTOGRAM
------------------- ------------ ---------------
PADDING_COL                93312 NONE
SKEW_COL                      12 FREQUENCY
PK_COL                     93502 NONE
Next to demonstrate that the skew in our skew_col is enough to lead to different plans being used for values 1 and 10.
explain plan for
select max(padding_col) 
from   skewed_data
where  skew_col = 1;
select * from table(dbms_xplan.display);
Plan hash value: 4257315069
------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                      |     5 |  2545 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| SKEWED_DATA          |     5 |  2545 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | SKEWED_DATA_SKEW_IDX |     5 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("SKEW_COL"=1)
explain plan for
select max(padding_col) 
from   skewed_data
where  skew_col = 10;
select * from table(dbms_xplan.display);

Plan hash value: 746880940

---------------------------------------------------------------------------------
| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |             | 37848 |    18M|  1844   (1)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| SKEWED_DATA | 37848 |    18M|  1844   (1)| 00:00:01 |
---------------------------------------------------------------------------------

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

   1 - filter("SKEW_COL"=10)
So far, so good. Next we want to demonstrate that if we were to use binds, ACS kicks in on our third execution if we do lowly-highly-highly:
var bSkew number
exec :bSkew := 1
select max(padding_col)
from   skewed_data
where  skew_col = :bSkew;
select * from table(dbms_xplan.display_cursor(format=>'advanced -projection -alias -outline'));
Plan hash value: 1617942411
-------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                      |       |       |     2 (100)|          |
|   1 |  SORT AGGREGATE                      |                      |     1 |   504 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| SKEWED_DATA          |     5 |  2520 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN                  | SKEWED_DATA_SKEW_IDX |     5 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------

   1 - :BSKEW (NUMBER): 1

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

   3 - access("SKEW_COL"=:BSKEW)

exec :bSkew := 10
select max(padding_col)
from   skewed_data
where  skew_col = :bSkew;
select * from table(dbms_xplan.display_cursor(format=>'advanced -projection -alias -outline'));

Plan hash value: 1617942411

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                      |       |       |     2 (100)|          |
|   1 |  SORT AGGREGATE                      |                      |     1 |   504 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| SKEWED_DATA          |     5 |  2520 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN                  | SKEWED_DATA_SKEW_IDX |     5 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------

   1 - :BSKEW (NUMBER): 1

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

   3 - access("SKEW_COL"=:BSKEW)

exec :bSkew := 10
select max(padding_col)
from   skewed_data
where  skew_col = :bSkew;
select * from table(dbms_xplan.display_cursor(format=>'advanced -projection -alias -outline'));

Plan hash value: 3993148105

----------------------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |       |       |  1844 (100)|          |
|   1 |  SORT AGGREGATE    |             |     1 |   504 |            |          |
|*  2 |   TABLE ACCESS FULL| SKEWED_DATA | 37848 |    18M|  1844   (1)| 00:00:01 |
----------------------------------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------

   1 - :BSKEW (NUMBER): 10

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

   2 - filter("SKEW_COL"=:BSKEW)
Cool, the second execution didn’t use the full table scan plan because at this point the cursor was only marked as bind sensitive, not bind aware (I haven’t shown this but the column exists in v$sql, you can check for yourself if you like). Next we want to turn on cursor_sharing=similar and demonstrate the same behaviour occurs when we use literals.
alter session set cursor_sharing=similar;
select max(padding_col)
from   skewed_data
where  skew_col = 1;
select * from table(dbms_xplan.display_cursor(format=>'advanced -projection -alias -outline'));

Plan hash value: 1617942411

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                      |       |       |     2 (100)|          |
|   1 |  SORT AGGREGATE                      |                      |     1 |   504 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| SKEWED_DATA          |     5 |  2520 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN                  | SKEWED_DATA_SKEW_IDX |     5 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------

   1 - :SYS_B_0 (NUMBER): 1

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

   3 - access("SKEW_COL"=:SYS_B_0)

exec :bSkew := 10
select max(padding_col)
from   skewed_data
where  skew_col = 10;
select * from table(dbms_xplan.display_cursor(format=>'advanced -projection -alias -outline'));

Plan hash value: 1617942411

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                      |       |       |     2 (100)|          |
|   1 |  SORT AGGREGATE                      |                      |     1 |   504 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| SKEWED_DATA          |     5 |  2520 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN                  | SKEWED_DATA_SKEW_IDX |     5 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------

   1 - :SYS_B_0 (NUMBER): 1

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

   3 - access("SKEW_COL"=:SYS_B_0)

exec :bSkew := 10
select max(padding_col)
from   skewed_data
where  skew_col = 10;
select * from table(dbms_xplan.display_cursor(format=>'advanced -projection -alias -outline'));

Plan hash value: 3993148105

----------------------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |       |       |  1844 (100)|          |
|   1 |  SORT AGGREGATE    |             |     1 |   504 |            |          |
|*  2 |   TABLE ACCESS FULL| SKEWED_DATA | 37848 |    18M|  1844   (1)| 00:00:01 |
----------------------------------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------

   1 - :SYS_B_0 (NUMBER): 10

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

   2 - filter("SKEW_COL"=:SYS_B_0)
And there you have it, two different plans for our similar cursor sharing. Of course, one of the biggest difficulties in getting this to occur on production databases is that your real users may not use the right predicates in the right order (those pesky real users!). To get around this you might want to use the bind_aware hint which makes the peeked behaviour always occur.
select /*+ BIND_AWARE */ max(padding_col)
from   skewed_data
where  skew_col = 1;
select * from table(dbms_xplan.display_cursor(format=>'advanced -projection -alias -outline'));



Plan hash value: 1617942411

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                      |       |       |     2 (100)|          |
|   1 |  SORT AGGREGATE                      |                      |     1 |   504 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| SKEWED_DATA          |     5 |  2520 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN                  | SKEWED_DATA_SKEW_IDX |     5 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------

   1 - :SYS_B_0 (NUMBER): 1

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

   3 - access("SKEW_COL"=:SYS_B_0)
select /*+ BIND_AWARE */ max(padding_col)
from   skewed_data
where  skew_col = 10;
select * from table(dbms_xplan.display_cursor(format=>'advanced -projection -alias -outline'));

Plan hash value: 3993148105

----------------------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |       |       |  1844 (100)|          |
|   1 |  SORT AGGREGATE    |             |     1 |   504 |            |          |
|*  2 |   TABLE ACCESS FULL| SKEWED_DATA | 37848 |    18M|  1844   (1)| 00:00:01 |
----------------------------------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------

   1 - :SYS_B_0 (NUMBER): 10

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

   2 - filter("SKEW_COL"=:SYS_B_0)
The peeked bind behaviour might not be so good for your shared pool:
select /*+ BIND_AWARE */ max(padding_col)
from   skewed_data
where  skew_col = 9;
select * from table(dbms_xplan.display_cursor(format=>'advanced -projection -alias -outline'));

SQL_ID  gczdkuca2dzt2, child number 2
-------------------------------------
select /*+ BIND_AWARE */ max(padding_col) from   skewed_data where
skew_col = :"SYS_B_0"

Plan hash value: 1617942411

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                      |       |       |  1023 (100)|          |
|   1 |  SORT AGGREGATE                      |                      |     1 |   504 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| SKEWED_DATA          | 13923 |  6852K|  1023   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN                  | SKEWED_DATA_SKEW_IDX | 13923 |       |    28   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------

   1 - :SYS_B_0 (NUMBER): 9

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

   3 - access("SKEW_COL"=:SYS_B_0)


select /*+ BIND_AWARE */ max(padding_col)
from   skewed_data
where  skew_col = 11;
select * from table(dbms_xplan.display_cursor(format=>'advanced -projection -alias -outline'));
SQL_ID  gczdkuca2dzt2, child number 3
-------------------------------------
select /*+ BIND_AWARE */ max(padding_col) from   skewed_data where
skew_col = :"SYS_B_0"

Plan hash value: 3993148105

----------------------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |       |       |  1844 (100)|          |
|   1 |  SORT AGGREGATE    |             |     1 |   504 |            |          |
|*  2 |   TABLE ACCESS FULL| SKEWED_DATA | 33628 |    16M|  1844   (1)| 00:00:01 |
----------------------------------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------

   1 - :SYS_B_0 (NUMBER): 11

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

   2 - filter("SKEW_COL"=:SYS_B_0)
See how we’ve got an extra child cursor per bind, this behaviour also occurs without the hint. Perhaps the behaviour would be nicer with a height balanced histogram where ranges of values will share the same selectivity. I’ll leave that as homework for the reader.
For now, I think this satisfies the requirement that Franck is after, you get roughly the same behaviour as if you had used literals (when you account for ACS).

Real Time Materialized Views (Part 2)

In part one I played around with some different materialized view logs on a standard table to get a feel for them. Now I’ll try and demonstrate what you’re really here for

REAL TIME MATERIALIZED VIEWS!

Baby steps though, let’s start with a materialized view which is just a replica of a base table. We’ll use the same base table from part one and we’ll leave the final materialized view log in place:

create materialized view mv_t_1
as
select pk_col, number_col, string_col
from   base_t_1;
Materialized view created.

You might wonder why such a simple materialized view could be of any use. A couple of things that come to mind:

  • You want to (heavily) index the table but don’t want your DML to hit those indexes (bitmaps would be a killer to OLTP performance in a multiuser environment)
  • The base table is on a remote table (because you don’t want your batch/reporting queries to interfere with your OLTP

We have our fresh MV and we have an empty materialized view log, so let’s get started trying to keep a fresh view. We’ll take this one DML operation at a time, gradually building up our fresh view.

Inserting a new row

ANDY@orcl>insert into base_t_1 (pk_col, number_col, string_col)
  2  select seq_1.nextVal, trunc(dbms_random.value(0,100)), dbms_random.string('x',10)
  3  from   dual;

1 row created.

Elapsed: 00:00:00.14
ANDY@orcl>@print "select * from MLOG$_BASE_T_1"

PK_COL                        : 1021
NUMBER_COL                    : 73
STRING_COL                    : 1KHJWYSQBE
SEQUENCE$$                    : 310008
SNAPTIME$$                    : 01/01/4000 00:00:00
DMLTYPE$$                     : I
OLD_NEW$$                     : N
CHANGE_VECTOR$$               : FE
XID$$                         : 1970337721882360
-----------------

We’ve seen this so many times now. How do we replicate this in the MV? Well easy: it’s an insert (dmltype$$ = ‘I’ and we know that this is the new version of the row (old_new$$=’N’), so we can just union all the row in:

select pk_col, number_col, string_col from mv_t_1
union all
select pk_col, number_col, string_col from mlog$_base_t_1;

Updates

Of course, we know that this is going to cause problems when the base table row is updated – we’ll get a row per version of the row in the base table and therefore many rows in this query. We want to project only the latest version of the row, luckily we can do this with analytics as we have our primary key and we know that the sequence$$ value gives us an order to the DML.

select pk_col, number_col, string_col from mv_t_1
union all
select pk_col, number_col, string_col
from  (
select pk_col, number_col, string_col
     ,row_number() over (partition by pk_col order by sequence$$ desc) rown 
from mlog$_base_t_1
      )
where  rown = 1;

That should do the trick, right? Well how do we know? I don’t fancy querying a 1000+ row base table and comparing it to a 1000+ row query. Maybe we should create a reconciliation routine, my favourite method for doing this would be the group by method demonstrated by Stew Ashton here: https://stewashton.wordpress.com/2014/02/04/compare-and-sync-tables-tom-kyte-and-group-by/ . If we let our fresh mv be defined as a view, it’ll be easier to use, I’ve just used created a view called fresh_t_1 and used the query from earlier.

Here’s our reconciliation query, it should return zero rows if fresh_t_1 is exactly the same as base_t_1 (what we want). We’ll save this as a script so we can easily rerun it.

select pk_col, number_col, string_col
,sum(OLD_CNT) OLD_CNT, sum(NEW_CNT) NEW_CNT
FROM (
  select pk_col, number_col, string_col 
   , 1 OLD_CNT, 0 NEW_CNT
  from base_t_1 o
  union all
  select pk_col, number_col, string_col 
    , 0 OLD_CNT, 1 NEW_CNT
  from fresh_t_1 n
)
group by pk_col, number_col, string_col 
having sum(OLD_CNT) != sum(NEW_CNT);
save recon.sql

No rows, fantastic! So what happens when we do our update:

ANDY@orcl>update base_t_1 set number_col = 100 where pk_col = 1021;
1 row updated.
@recon
no rows selected

So far, so good. What about if we update one of the rows that already existed?

ANDY@orcl>update base_t_1 set number_col = 100 where pk_col = 1;

1 row updated.
ANDY@orcl>@recon
    PK_COL NUMBER_COL STRING_COL                                  OLD_CNT    NEW_CNT
---------- ---------- ---------------------------------------- ---------- ----------
         1         71 H2QDH5Z0SO                                        0          1

1 row selected.
What’s gone wrong? Hmm, let’s look for clues:
select * from fresh_t_1 where pk_col = 1;    PK_COL NUMBER_COL STRING_COL
---------- ---------- ---------------------
         1         71 H2QDH5Z0SO
         1        100 H2QDH5Z0SO

2 rows selected.
It looks like we’ve still got the old version of our row here, this will be because it exists in the MView. What can we do to fix that? One option would be to not project rows in the MView that have a row in the MView log for that PK:
create or replace view fresh_t_1 as
select pk_col, number_col, string_col 
from   mv_t_1
where  pk_col not in (select pk_col from mlog$_base_t_1)
union all
select pk_col, number_col, string_col
from  (
select pk_col, number_col, string_col
     ,row_number() over (partition by pk_col order by sequence$$ desc) rown 
from mlog$_base_t_1
      )
where  rown = 1;
Another option could be to left join from the MView to the analytic query and use NVL2 on the MView log pk column to decide which row source to take a column from. We would then not report the row in the second part of the union all- checking whether the earliest version of the row logged was an Old row could do this. Because we’ll be doing that analytic in both parts, we could use a WITH clause to make this easier to write (and maybe more performant). We can get check the earliest row with another analytic, this is the full thing:
create or replace view fresh_t_1_alt as
with mloglytic as (select pk_col, number_col, string_col, first_oldnew
                   from  (
                   select pk_col, number_col, string_col
                        ,row_number() over (partition by pk_col order by sequence$$ desc) rown 
                        ,max(old_new$$) keep (dense_rank first order by sequence$$) over (partition by pk_col) first_oldnew
                   from mlog$_base_t_1
                         )
                   where  rown = 1
                  )
select mv.pk_col, nvl2(mv_log.pk_col,mv_log.number_col,mv.number_col) number_col, nvl2(mv_log.pk_col,mv_log.string_col,mv.string_col) string_col
from   mv_t_1 mv
left join mloglytic mv_log
  on   mv.pk_col = mv_log.pk_col
union all
select pk_col, number_col, string_col
from   mloglytic
where  first_oldnew = 'N';
This looks slightly more complex than the prior solution, but that doesn’t make it bad. Both versions reconcile with the current data set, maybe one will perform better than the other? We can investigate later, there’s one more obvious DML operation we need to think about first.

Deletes

Let’s just see what happens with our two solutions when we do a delete on an existing row:

ANDY@orcl>delete base_t_1 where pk_col = 5;

1 row deleted.

Elapsed: 00:00:00.01
ANDY@orcl>@recon

    PK_COL NUMBER_COL STRING_COL                                  OLD_CNT    NEW_CNT
---------- ---------- ---------------------------------------- ---------- ----------
         5         22 8HGNZQAX2R                                        0          1
And the second view:
    PK_COL NUMBER_COL STRING_COL                                  OLD_CNT    NEW_CNT
---------- ---------- ---------------------------------------- ---------- ----------
         5         22 8HGNZQAX2R                                        0          1
They both suffer, the deleted row hasn’t been removed from our fresh views.
The first view will suffer because we are projecting all of the latest versions of each row in our second part of the union all. We can address this by checking that the latest version of the row is a New version rather than the old (we’ve already had to do a similar thing in the previous run using the first version of row).
create or replace view fresh_t_1 as
select pk_col, number_col, string_col 
from   mv_t_1
where  pk_col not in (select pk_col from mlog$_base_t_1)
union all
select pk_col, number_col, string_col
from  (
select pk_col, number_col, string_col
      ,row_number() over (partition by pk_col order by sequence$$ desc) rown 
      ,old_new$$
from mlog$_base_t_1
      )
where  rown = 1
and    old_new$$ = 'N';
ANDY@orcl>@recon

no rows selected
Can we fix the alternate view? Sure, we just need to make sure when we make the left join to our analytic function, we filter out all rows that match up to a deleted row. For this, we pull through the latest old_new$$ value.
create or replace view fresh_t_1_alt as
with mloglytic as (select pk_col, number_col, string_col, first_oldnew, old_new$$
                   from  (
                   select pk_col, number_col, string_col
                        ,row_number() over (partition by pk_col order by sequence$$ desc) rown 
                        ,max(old_new$$) keep (dense_rank first order by sequence$$) over (partition by pk_col) first_oldnew
                         ,old_new$$ old_new$$
                   from mlog$_base_t_1
                         )
                   where  rown = 1
                  )
select mv.pk_col, nvl2(mv_log.pk_col,mv_log.number_col,mv.number_col) number_col, nvl2(mv_log.pk_col,mv_log.string_col,mv.string_col) string_col
from   mv_t_1 mv
left join mloglytic mv_log
  on   mv.pk_col = mv_log.pk_col
where  mv_log.pk_col is null or mv_log.old_new$$ = 'N'
union all
select pk_col, number_col, string_col
from   mloglytic
where  first_oldnew = 'N';

Does it reconcile?

no rows selected
Yes!
What about deletes on a row we’ve inserted ourselves (not contained in the MV):
ANDY@orcl>delete base_T_1 where pk_col = 1021;

1 row deleted.

Elapsed: 00:00:00.01
ANDY@orcl>@recon

no rows selected

And the second?

    PK_COL NUMBER_COL STRING_COL                                  OLD_CNT    NEW_CNT
---------- ---------- ---------------------------------------- ---------- ----------
      1021         73 1KHJWYSQBE                                       0          1
Not so good, so where’s our latest problem? We have forgotten to make sure the latest version of the row is not a delete in our second part. Let’s fix it:
create or replace view fresh_t_1_alt as
with mloglytic as (select pk_col, number_col, string_col, first_oldnew, old_new$$
                   from  (
                   select pk_col, number_col, string_col
                        ,row_number() over (partition by pk_col order by sequence$$ desc) rown 
                        ,max(old_new$$) keep (dense_rank first order by sequence$$) over (partition by pk_col) first_oldnew
                         ,old_new$$ old_new$$
                   from mlog$_base_t_1
                         )
                   where  rown = 1
                  )
select mv.pk_col, nvl2(mv_log.pk_col,mv_log.number_col,mv.number_col) number_col, nvl2(mv_log.pk_col,mv_log.string_col,mv.string_col) string_col
from   mv_t_1 mv
left join mloglytic mv_log
  on   mv.pk_col = mv_log.pk_col
where  mv_log.pk_col is null or mv_log.old_new$$ = 'N'
union all
select pk_col, number_col, string_col
from   mloglytic
where  first_oldnew = 'N'
and    old_new$$ = 'N';
Does it recon? Yes!

Performance

We have two views now, lets see what performance we can expect. I’m going to do explain plan just to see what Oracle is capable of doing for us, all I’m looking at is where filters are being applied, I don’t care that full table scans happen I care that they happen and pull through all of the rows (we can index for the opposite scenario). First a primary key lookup of the fresh view:

ANDY@orcl>explain plan for select * from fresh_t_1 where pk_col = :x;

Explained.

Elapsed: 00:00:00.01
ANDY@orcl>@x

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------
Plan hash value: 454948185

----------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                |     2 |    96 |     9  (12)| 00:00:01 |
|   1 |  VIEW                             | FRESH_T_1      |     2 |    96 |     9  (12)| 00:00:01 |
|   2 |   UNION-ALL                       |                |       |       |            |          |
|*  3 |    HASH JOIN ANTI NA              |                |     1 |    31 |     5   (0)| 00:00:01 |
|   4 |     MAT_VIEW ACCESS BY INDEX ROWID| MV_T_1         |     1 |    18 |     2   (0)| 00:00:01 |
|*  5 |      INDEX UNIQUE SCAN            | SYS_C0014867   |     1 |       |     1   (0)| 00:00:01 |
|   6 |     TABLE ACCESS FULL             | MLOG$_BASE_T_1 |     7 |    91 |     3   (0)| 00:00:01 |
|*  7 |    VIEW                           |                |     1 |    63 |     4  (25)| 00:00:01 |
|*  8 |     WINDOW SORT PUSHED RANK       |                |     1 |    63 |     4  (25)| 00:00:01 |
|*  9 |      TABLE ACCESS FULL            | MLOG$_BASE_T_1 |     1 |    63 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

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

   3 - access("PK_COL"="PK_COL")
   5 - access("PK_COL"=TO_NUMBER(:X))
   7 - filter("ROWN"=1 AND "OLD_NEW$$"='N')
   8 - filter(ROW_NUMBER() OVER ( PARTITION BY "PK_COL" ORDER BY
              INTERNAL_FUNCTION("SEQUENCE$$") DESC )<=1)
   9 - filter("PK_COL"=TO_NUMBER(:X))
Note
—–
– dynamic statistics used: dynamic sampling (level=2)
Here we access the MView log twice, we apply filters on both of them so potentially indexing on pk_col could help. If we were worried about going to the table twice we could WITH it but that might effect our ability to push that predicate.
Here’s the plan with the alt view
Plan hash value: 2918522968

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                           |    14 |   672 |     6   (0)| 00:00:01 |
|   1 |  VIEW                               | FRESH_T_1_ALT             |    14 |   672 |     6   (0)| 00:00:01 |
|   2 |   TEMP TABLE TRANSFORMATION         |                           |       |       |            |          |
|   3 |    LOAD AS SELECT                   | SYS_TEMP_0FD9D6686_6B752F |       |       |            |          |
|*  4 |     VIEW                            |                           |     7 |   455 |     4  (25)| 00:00:01 |
|   5 |      WINDOW SORT                    |                           |     7 |   441 |     4  (25)| 00:00:01 |
|   6 |       TABLE ACCESS FULL             | MLOG$_BASE_T_1            |     7 |   441 |     3   (0)| 00:00:01 |
|   7 |    UNION-ALL                        |                           |       |       |            |          |
|*  8 |     FILTER                          |                           |       |       |            |          |
|   9 |      NESTED LOOPS OUTER             |                           |     7 |   476 |     4   (0)| 00:00:01 |
|  10 |       MAT_VIEW ACCESS BY INDEX ROWID| MV_T_1                    |     1 |    18 |     2   (0)| 00:00:01 |
|* 11 |        INDEX UNIQUE SCAN            | SYS_C0014867              |     1 |       |     1   (0)| 00:00:01 |
|* 12 |       VIEW                          |                           |     7 |   350 |     2   (0)| 00:00:01 |
|  13 |        TABLE ACCESS FULL            | SYS_TEMP_0FD9D6686_6B752F |     7 |   364 |     2   (0)| 00:00:01 |
|* 14 |     VIEW                            |                           |     7 |   364 |     2   (0)| 00:00:01 |
|  15 |      TABLE ACCESS FULL              | SYS_TEMP_0FD9D6686_6B752F |     7 |   364 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------

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

   4 - filter("ROWN"=1)
   8 - filter("MV_LOG"."PK_COL" IS NULL OR "MV_LOG"."OLD_NEW$$"='N')
  11 - access("MV"."PK_COL"=TO_NUMBER(:X))
  12 - filter("MV_LOG"."PK_COL"(+)=TO_NUMBER(:X))
  14 - filter("FIRST_OLDNEW"='N' AND "OLD_NEW$$"='N' AND "PK_COL"=TO_NUMBER(:X))
Note
—–
– dynamic statistics used: dynamic sampling (level=2)
Now, we only go to the mview log table once, but it hasn’t pushed our primary key filter into it, that could effect performance when there is large amounts of data in the mview log and we only care about few rows. Is that going to be a problem? Are you likely to use filters on the primary key (this would be aimed for the cases where this is a replication environment)?
What about if we filter on one of the other columns:
ANDY@orcl>explain plan for select * from fresh_t_1 where number_col =4;

Explained.

Elapsed: 00:00:00.02
ANDY@orcl>@x

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
Plan hash value: 2930466682

---------------------------------------------------------------------------------------------
| Id  | Operation                  | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                |     8 |   384 |    11  (10)| 00:00:01 |
|   1 |  VIEW                      | FRESH_T_1      |     8 |   384 |    11  (10)| 00:00:01 |
|   2 |   UNION-ALL                |                |       |       |            |          |
|*  3 |    HASH JOIN ANTI NA       |                |     7 |   217 |     7   (0)| 00:00:01 |
|*  4 |     MAT_VIEW ACCESS FULL   | MV_T_1         |    10 |   180 |     4   (0)| 00:00:01 |
|   5 |     TABLE ACCESS FULL      | MLOG$_BASE_T_1 |     7 |    91 |     3   (0)| 00:00:01 |
|*  6 |    VIEW                    |                |     1 |    63 |     4  (25)| 00:00:01 |
|*  7 |     WINDOW SORT PUSHED RANK|                |     7 |   441 |     4  (25)| 00:00:01 |
|   8 |      TABLE ACCESS FULL     | MLOG$_BASE_T_1 |     7 |   441 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

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

   3 - access("PK_COL"="PK_COL")
   4 - filter("NUMBER_COL"=4)
   6 - filter("ROWN"=1 AND "OLD_NEW$$"='N' AND "NUMBER_COL"=4)
   7 - filter(ROW_NUMBER() OVER ( PARTITION BY "PK_COL" ORDER BY
              INTERNAL_FUNCTION("SEQUENCE$$") DESC )<=1)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

We’re no longer able to push our predicate into the mview log table, that’s because we have to get all the rows belonging to a particular pk_col value first. This means we do two large full table scans, one has to get sorted too. How does that compare to our alternative?

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------
Plan hash value: 3702722754

---------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                           |  1011 | 48528 |     8   (0)| 00:00:01 |
|   1 |  VIEW                       | FRESH_T_1_ALT             |  1011 | 48528 |     8   (0)| 00:00:01 |
|   2 |   TEMP TABLE TRANSFORMATION |                           |       |       |            |          |
|   3 |    LOAD AS SELECT           | SYS_TEMP_0FD9D6687_6B752F |       |       |            |          |
|*  4 |     VIEW                    |                           |     7 |   455 |     4  (25)| 00:00:01 |
|   5 |      WINDOW SORT            |                           |     7 |   441 |     4  (25)| 00:00:01 |
|   6 |       TABLE ACCESS FULL     | MLOG$_BASE_T_1            |     7 |   441 |     3   (0)| 00:00:01 |
|   7 |    UNION-ALL                |                           |       |       |            |          |
|*  8 |     FILTER                  |                           |       |       |            |          |
|*  9 |      HASH JOIN RIGHT OUTER  |                           |  1004 | 68272 |     6   (0)| 00:00:01 |
|  10 |       VIEW                  |                           |     7 |   350 |     2   (0)| 00:00:01 |
|  11 |        TABLE ACCESS FULL    | SYS_TEMP_0FD9D6687_6B752F |     7 |   364 |     2   (0)| 00:00:01 |
|  12 |       MAT_VIEW ACCESS FULL  | MV_T_1                    |  1000 | 18000 |     4   (0)| 00:00:01 |
|* 13 |     VIEW                    |                           |     7 |   364 |     2   (0)| 00:00:01 |
|  14 |      TABLE ACCESS FULL      | SYS_TEMP_0FD9D6687_6B752F |     7 |   364 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------

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

   4 - filter("ROWN"=1)
   8 - filter(NVL2("MV_LOG"."PK_COL","MV_LOG"."NUMBER_COL","MV"."NUMBER_COL")=4 AND
              ("MV_LOG"."PK_COL" IS NULL OR "MV_LOG"."OLD_NEW$$"='N'))
   9 - access("MV"."PK_COL"="MV_LOG"."PK_COL"(+))
  13 - filter("FIRST_OLDNEW"='N' AND "OLD_NEW$$"='N' AND "NUMBER_COL"=4)
Note
—–
– dynamic statistics used: dynamic sampling (level=2)
We do our one big WITH clause with the mview log (the same as before) and then we join on the pk_col value a little later again. You can appreciate that performance could be better as the big tablescan and sort is only done once, the later reads just use that materialized data set.
Our final query type would be extracting the entire view:
ANDY@orcl>explain plan for select * from fresh_t_1;

Explained.

Elapsed: 00:00:00.04
ANDY@orcl>@x

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
Plan hash value: 1423242017

---------------------------------------------------------------------------------------------
| Id  | Operation                  | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                |  1001 | 48048 |    11  (10)| 00:00:01 |
|   1 |  VIEW                      | FRESH_T_1      |  1001 | 48048 |    11  (10)| 00:00:01 |
|   2 |   UNION-ALL                |                |       |       |            |          |
|*  3 |    HASH JOIN RIGHT ANTI NA |                |  1000 | 31000 |     7   (0)| 00:00:01 |
|   4 |     TABLE ACCESS FULL      | MLOG$_BASE_T_1 |     7 |    91 |     3   (0)| 00:00:01 |
|   5 |     MAT_VIEW ACCESS FULL   | MV_T_1         |  1000 | 18000 |     4   (0)| 00:00:01 |
|*  6 |    VIEW                    |                |     1 |    63 |     4  (25)| 00:00:01 |
|*  7 |     WINDOW SORT PUSHED RANK|                |     7 |   441 |     4  (25)| 00:00:01 |
|   8 |      TABLE ACCESS FULL     | MLOG$_BASE_T_1 |     7 |   441 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

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

   3 - access("PK_COL"="PK_COL")
   6 - filter("ROWN"=1 AND "OLD_NEW$$"='N')
   7 - filter(ROW_NUMBER() OVER ( PARTITION BY "PK_COL" ORDER BY
              INTERNAL_FUNCTION("SEQUENCE$$") DESC )<=1)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

27 rows selected.

Elapsed: 00:00:00.28
ANDY@orcl>explain plan for select * from fresh_t_1_alt;

Explained.

Elapsed: 00:00:00.03
ANDY@orcl>@X

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------
Plan hash value: 3702722754

---------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                           |  1011 | 48528 |     8   (0)| 00:00:01 |
|   1 |  VIEW                       | FRESH_T_1_ALT             |  1011 | 48528 |     8   (0)| 00:00:01 |
|   2 |   TEMP TABLE TRANSFORMATION |                           |       |       |            |          |
|   3 |    LOAD AS SELECT           | SYS_TEMP_0FD9D6688_6B752F |       |       |            |          |
|*  4 |     VIEW                    |                           |     7 |   455 |     4  (25)| 00:00:01 |
|   5 |      WINDOW SORT            |                           |     7 |   441 |     4  (25)| 00:00:01 |
|   6 |       TABLE ACCESS FULL     | MLOG$_BASE_T_1            |     7 |   441 |     3   (0)| 00:00:01 |
|   7 |    UNION-ALL                |                           |       |       |            |          |
|*  8 |     FILTER                  |                           |       |       |            |          |
|*  9 |      HASH JOIN RIGHT OUTER  |                           |  1004 | 68272 |     6   (0)| 00:00:01 |
|  10 |       VIEW                  |                           |     7 |   350 |     2   (0)| 00:00:01 |
|  11 |        TABLE ACCESS FULL    | SYS_TEMP_0FD9D6688_6B752F |     7 |   364 |     2   (0)| 00:00:01 |
|  12 |       MAT_VIEW ACCESS FULL  | MV_T_1                    |  1000 | 18000 |     4   (0)| 00:00:01 |
|* 13 |     VIEW                    |                           |     7 |   364 |     2   (0)| 00:00:01 |
|  14 |      TABLE ACCESS FULL      | SYS_TEMP_0FD9D6688_6B752F |     7 |   364 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------

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

   4 - filter("ROWN"=1)
   8 - filter("MV_LOG"."PK_COL" IS NULL OR "MV_LOG"."OLD_NEW$$"='N')
   9 - access("MV"."PK_COL"="MV_LOG"."PK_COL"(+))
  13 - filter("FIRST_OLDNEW"='N' AND "OLD_NEW$$"='N')

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
Again, I can appreciate the alternative view could be quicker as we do the big work just once.

Summary

We’ve managed to create two versions of a view that will provide Real Time Materialized View queries using versions of Oracle below 12.2. The two versions have their different performance profiles, it’s possible that some manual work can be done if you know your use case better.

Our final views:

create or replace view fresh_t_1 as
select pk_col, number_col, string_col 
from   mv_t_1
where  pk_col not in (select pk_col from mlog$_base_t_1)
union all
select pk_col, number_col, string_col
from  (
select pk_col, number_col, string_col
      ,row_number() over (partition by pk_col order by sequence$$ desc) rown 
      ,old_new$$
from mlog$_base_t_1
      )
where  rown = 1
and    old_new$$ = 'N';

create or replace view fresh_t_1_alt as
with mloglytic as (select pk_col, number_col, string_col, first_oldnew, old_new$$
                   from  (
                   select pk_col, number_col, string_col
                        ,row_number() over (partition by pk_col order by sequence$$ desc) rown 
                        ,max(old_new$$) keep (dense_rank first order by sequence$$) over (partition by pk_col) first_oldnew
                         ,old_new$$ old_new$$
                   from mlog$_base_t_1
                         )
                   where  rown = 1
                  )
select mv.pk_col, nvl2(mv_log.pk_col,mv_log.number_col,mv.number_col) number_col, nvl2(mv_log.pk_col,mv_log.string_col,mv.string_col) string_col
from   mv_t_1 mv
left join mloglytic mv_log
  on   mv.pk_col = mv_log.pk_col
where  mv_log.pk_col is null or mv_log.old_new$$ = 'N'
union all
select pk_col, number_col, string_col
from   mloglytic
where  first_oldnew = 'N'
and    old_new$$ = 'N';

In reviewing this post to put it live, I realised that actually another possibility would use the first part of the original view and the second part could use an existence check to check if the row in the mlog is the latest per PK. I believe this would allow for predicate pushing for both tables. I’ll post this revised solution when I have the time.

Part 3 may be a couple of weeks away, I’ll be sharing my solution for real time aggregate mviews – with and without a group by.

-Update 18/03/2017:

And here’s my solution which I believe gives the best performance is most situations, SQL and plan first:

create index mlog$_base_t_1_pk_seq on mlog$_base_t_1 (pk_col, sequence$$);
create or replace view fresh_t_1_perf as
select pk_col, number_col, string_col 
from   mv_t_1
where  pk_col not in (select pk_col from mlog$_base_t_1)
union all
select pk_col, number_col, string_col
from   mlog$_base_t_1 b1
where  not exists (select null from mlog$_base_t_1 ssq where ssq.pk_col = b1.pk_col and ssq.sequence$$ > b1.sequence$$)
and    old_new$$ = 'N';
explain plan for select * from fresh_t_1_perf where number_col between 50 and 51;

Plan hash value: 213855121

-------------------------------------------------------------------------------------------------
| Id  | Operation               | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |                       |   316 | 15168 |    58   (0)| 00:00:01 |
|   1 |  VIEW                   | FRESH_T_1_PERF        |   316 | 15168 |    58   (0)| 00:00:01 |
|   2 |   UNION-ALL             |                       |       |       |            |          |
|*  3 |    HASH JOIN ANTI NA    |                       |   315 |  9765 |    35   (0)| 00:00:01 |
|*  4 |     MAT_VIEW ACCESS FULL| MV_T_1                |   326 |  5868 |    13   (0)| 00:00:01 |
|   5 |     TABLE ACCESS FULL   | MLOG$_BASE_T_1        |  9284 |   117K|    22   (0)| 00:00:01 |
|   6 |    NESTED LOOPS ANTI    |                       |     1 |    89 |    23   (0)| 00:00:01 |
|*  7 |     TABLE ACCESS FULL   | MLOG$_BASE_T_1        |     1 |    63 |    22   (0)| 00:00:01 |
|*  8 |     INDEX RANGE SCAN    | MLOG$_BASE_T_1_PK_SEQ |     1 |    26 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

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

   3 - access("PK_COL"="PK_COL")
   4 - filter("NUMBER_COL">=50 AND "NUMBER_COL"<=51)
   7 - filter("OLD_NEW$$"='N' AND "NUMBER_COL">=50 AND "NUMBER_COL"<=51)
   8 - access("SSQ"."PK_COL"="B1"."PK_COL" AND "SSQ"."SEQUENCE$$">"B1"."SEQUENCE$$" AND
              "SSQ"."SEQUENCE$$" IS NOT NULL)
Here the optimizer has decided to opt for a full tablescan of our MView believing it will find enough rows to make a hash join against our MView Log worthwhile (full scanning will cost 22 as opposed to doing 326 index look ups). The second part of the union all is also doing a full scan of the MView Log, it’s pushed our main predicate and believes it will only have to check if one row from the MView log is the latest for that primary key (this time via a nested loop anti join).
I’ve managed to get 12.2 running on my sandbox setup at home and felt it was the perfect opportunity to do some comparisons. It turns out that this simple materialized view is not eligible for on query computation – or at least it wasn’t immediately available to me. Hopefully a fair competition is available when we do our aggregate materialized views.

Real Time Materialized Views pre 12.2? (Part 1)

Oh, yes!

Like many others, I was excited when Oracle announced that in 12.2 you could have always fresh MViews with a change to your fast refresh Mviews. If you want to read further then the first step is the documentation https://docs.oracle.com/database/122/SQLRF/CREATE-MATERIALIZED-VIEW.htm ENABLE ON QUERY COMPUTATION is the clause you’re after. Franck Pachot has a very detailed write up as well https://blog.dbi-services.com/12cr2-real-time-materialized-view-on-query-computation/. Now, you might be in the same boat as many – you don’t have 12.2 because you’re using on-premises and your company doesn’t have plans to upgrade to 12.2 because upgrading is time consuming… but you still want to use real time MVs don’t you?

So do I! So I gave it a shot, my theory: if Oracle can refresh a materialized view using only a materialized view log then one can view the real time data using just the materialized data and the materialized view log. We will be limited by only materialized views that are fast refreshable, and we’ll have to work out the logic ourselves. I will present solutions for some basic (but useful!) materialized views, you will have to adapt them to fit your scenario which means you will have to understand what’s going on. This will be delivered in parts, this post will serve as an introduction, it may even be enough to get you started on your own solution!

First things first, we’re going to need to understand what goes into a materialized view log. With so many options, it’s easy to get a little mixed up so let’s do some sample cases. Try these on your own system, I tested them on my home system running Oracle 12.1.0.2 EE.

First a basic table with a primary key and some sample data:

create table base_t_1 (pk_col number primary key, number_col number, string_col varchar2(40));
create sequence seq_1;
insert into base_t_1 (pk_col, number_col, string_col)
select seq_1.nextVal, trunc(dbms_random.value(0,100)), dbms_random.string('x',10) 
from   dual connect by rownum <=1000;
commit;

We’ve got a basic table with a thousand rows and a few random numbers in one column and random strings in another column. Now we create a materialized view log on the table, without using any special clauses:

create materialized view log on base_t_1;
Materialized view log created.

That was easy, so what does the materialized view log look like? We can use the user_mview_logs data dictionary view to describe the mview logs of the logged in user , the master column is the table that the mview log is on. I’m using a saved script called print, I’ll share this another time but essentially it is the same as Tom Kyte’s print_table stored procedure but as an anonymous block.

ANDY@orcl>@print "select * from user_mview_logs where master = 'BASE_T_1'"

LOG_OWNER                     : ANDY
MASTER                        : BASE_T_1
LOG_TABLE                     : MLOG$_BASE_T_1
LOG_TRIGGER                   :
ROWIDS                        : NO
PRIMARY_KEY                   : YES
OBJECT_ID                     : NO
FILTER_COLUMNS                : NO
SEQUENCE                      : NO
INCLUDE_NEW_VALUES            : NO
PURGE_ASYNCHRONOUS            : NO
PURGE_DEFERRED                : NO
PURGE_START                   :
PURGE_INTERVAL                :
LAST_PURGE_DATE               :
LAST_PURGE_STATUS             :
NUM_ROWS_PURGED               :
COMMIT_SCN_BASED              : NO
STAGING_LOG                   : NO
-----------------

ANDY@orcl>desc MLOG$_BASE_T_1
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- -------------------
 PK_COL                                                         NUMBER
 SNAPTIME$$                                                     DATE
 DMLTYPE$$                                                      VARCHAR2(1)
 OLD_NEW$$                                                      VARCHAR2(1)
 CHANGE_VECTOR$$                                                RAW(255)
 XID$$                                                          NUMBER
We can see our primary key column has automatically been added. Ifwe included rowid in the create mview log statement we’d get that too. The log is currently empty, so let’s see what we get when we insert a new row:
insert into base_t_1 (pk_col, number_col, string_col)
select seq_1.nextVal, trunc(dbms_random.value(0,100)), dbms_random.string('x',10) 
from   dual;
@print "select * from MLOG$_BASE_T_1"

PK_COL                        : 1001
SNAPTIME$$                    : 01/01/4000 00:00:00
DMLTYPE$$                     : I
OLD_NEW$$                     : N
CHANGE_VECTOR$$               : FE
XID$$                         : 562962838326956
-----------------

How can we interpret this? It’s telling us the row affected has a primary key of 1001 (the next value in the sequence), it was added to the log using a dml type of I (Insert), and it’s the New version of the row being logged. The other columns: snaptime$$ is used by Oracle to coordinate refreshes of multiple mviews (and we may not get into that), change_vector$$ is an internal representation of what’s changed – it’s just a hex string that we can convert to binary, here FE->11111110, some of those 1s must mean we’ve made new values for 3 of our columns, we don’t have much information to base this on so let’s just leave it to one side for now. The final column is xid$$, that must be something to do with the transaction of the insert, I’ve kept the transaction open so let’s see what we can find. Here is a query that fetches the v$transaction row for the users session (by looking up the taddr value from v$session for the Sid of the users session) 

ANDY@orcl>@print "select * from v$transaction where addr = (select taddr from v$session where sid = sys_context('userenv','sid'))"

ADDR                          : 00007FF866C8CCB8
XIDUSN                        : 2
XIDSLOT                       : 3
XIDSQN                        : 3756
UBAFIL                        : 5
UBABLK                        : 148
UBASQN                        : 774
UBAREC                        : 33
STATUS                        : ACTIVE
START_TIME                    : 02/28/17 22:56:30
START_SCNB                    : 7161539
START_SCNW                    : 0
START_UEXT                    : 0
START_UBAFIL                  : 5
START_UBABLK                  : 148
START_UBASQN                  : 774
START_UBAREC                  : 30
SES_ADDR                      : 00007FF86AEE7730
FLAG                          : 3587
SPACE                         : NO
RECURSIVE                     : NO
NOUNDO                        : NO
PTX                           : NO
NAME                          :
PRV_XIDUSN                    : 0
PRV_XIDSLT                    : 0
PRV_XIDSQN                    : 0
PTX_XIDUSN                    : 0
PTX_XIDSLT                    : 0
PTX_XIDSQN                    : 0
DSCN-B                        : 0
DSCN-W                        : 0
USED_UBLK                     : 1
USED_UREC                     : 4
LOG_IO                        : 69
PHY_IO                        : 2
CR_GET                        : 9640
CR_CHANGE                     : 0
START_DATE                    : 28/02/2017 22:56:30
DSCN_BASE                     : 0
DSCN_WRAP                     : 0
START_SCN                     : 7161539
DEPENDENT_SCN                 : 0
XID                           : 02000300AC0E0000
PRV_XID                       : 0000000000000000
PTX_XID                       : 0000000000000000
CON_ID                        : 0
-----------------
v$transaction has an xid column in hexadecimal and our mview log has one using decimal, let’s compare:
ANDY@orcl>select to_char(562962838326956,'fm0xxxxxxxxxxxxx') bin_to_hex from dual;

BIN_TO_HEX
---------------
02000300000eac
Close, but perhaps we’re just comparing to the wrong XID. If we consider the 3 elements of the transaction describing where the undo exists:
@print "select XIDUSN, XIDSlot, XIDSQN, to_char(XIDUSN,'fm0X') XIDUSN_hex, to_char(xidSlot,'fm0XXX') xidSlot_hex, to_char(xidSQN,'fm0XXXXXXXXXX') XIDSQN_hex from v$transaction where addr = (select taddr from v$session where sid = sys_context('userenv','sid'))"

ANDY@orcl>/
XIDUSN                        : 2
XIDSLOT                       : 3
XIDSQN                        : 3756
XIDUSN_HEX                    : 02
XIDSLOT_HEX                   : 0003
XIDSQN_HEX                    : 00000000EAC
-----------------
That’s more like it! Maybe we should move on with another DML? Let’s update the row we just inserted and see what happens
update base_t_1 set number_col = 2 where pk_col = 1001;
ANDY@orcl>@print "select * from MLOG$_BASE_T_1"

PK_COL                        : 1001
SNAPTIME$$                    : 01/01/4000 00:00:00
DMLTYPE$$                     : I
OLD_NEW$$                     : N
CHANGE_VECTOR$$               : FE
XID$$                         : 562962838326956
-----------------
PK_COL                        : 1001
SNAPTIME$$                    : 01/01/4000 00:00:00
DMLTYPE$$                     : U
OLD_NEW$$                     : U
CHANGE_VECTOR$$               : 04
XID$$                         : 562962838326956
-----------------
First thing you should notice is the original row still exists, you should appreciate that it might complicate matters for us (on the other hand, without knowing the full history of a row would also give us problems). The new row tells us we did an update to the row with primary key 1001. The old_new$$ column has been set to ‘U’, this must mean that the row has been updated – it doesn’t matter whether you’re looking at the old or new version as all we have is the primary key, to get the information about the row we’d have to join back to the master table. The change_vector$$ translates (hex to bin) to 00000100, we know that we’ve modified the second column of base_t_1. What happens when we modify it again for a second time?
update base_t_1 set string_col = '3' where pk_col = 1001;
ANDY@orcl>@print "select * from MLOG$_BASE_T_1"

----
Including the previous rows
-----------------
PK_COL                        : 1001
SNAPTIME$$                    : 01/01/4000 00:00:00
DMLTYPE$$                     : U
OLD_NEW$$                     : U
CHANGE_VECTOR$$               : 08
XID$$                         : 562962838326956
-----------------
This change_vector$$ translates to 00001000
So have we got anything there that we can use for a materialized view? Not immediately, in order to get the data needed for a fresh MView we’d need to go back to the master table to get the new row. That doesn’t sound very effective, and presumably we can do better? Yes!
Let’s rollback, and create the materialized view log with some extra columns:
ANDY@orcl>create materialized view log on base_t_1 with (number_col, string_col);

Materialized view log created.
And the same tests:
insert into base_t_1 (pk_col, number_col, string_col)
select seq_1.nextVal, trunc(dbms_random.value(0,100)), dbms_random.string('x',10) 
from   dual;
ANDY@orcl>@print "select * from MLOG$_BASE_T_1"

PK_COL                        : 1003
NUMBER_COL                    : 0
STRING_COL                    : 92V1H71O2K
SNAPTIME$$                    : 01/01/4000 00:00:00
DMLTYPE$$                     : I
OLD_NEW$$                     : N
CHANGE_VECTOR$$               : FE
XID$$                         : 1407490847674749
-----------------

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.05
ANDY@orcl>
ANDY@orcl>update base_t_1 set number_col = 2 where pk_col = 1003;

1 row updated.

Elapsed: 00:00:00.00
ANDY@orcl>@print "select * from MLOG$_BASE_T_1"

PK_COL                        : 1003
NUMBER_COL                    : 0
STRING_COL                    : 92V1H71O2K
SNAPTIME$$                    : 01/01/4000 00:00:00
DMLTYPE$$                     : I
OLD_NEW$$                     : N
CHANGE_VECTOR$$               : FE
XID$$                         : 1407490847674749
-----------------
PK_COL                        : 1003
NUMBER_COL                    : 0
STRING_COL                    : 92V1H71O2K
SNAPTIME$$                    : 01/01/4000 00:00:00
DMLTYPE$$                     : U
OLD_NEW$$                     : O
CHANGE_VECTOR$$               : 04
XID$$                         : 1407490847674749
-----------------
I’ve only done one insert and one update here just to demonstrate the first problem: even though we have the rest of the columns from the table, we only have the Old state of the row that was updated. Let’s refer to the documentation to see what we can do better

new_values_clause::=

Description of new_values_clause.gif follows

So trying with including new values:
create materialized view log on base_t_1 with (number_col, string_col) including new values;

insert into base_t_1 (pk_col, number_col, string_col)
select seq_1.nextVal, trunc(dbms_random.value(0,100)), dbms_random.string('x',10) 
from   dual;
ANDY@orcl>@print "select * from MLOG$_BASE_T_1"

PK_COL                        : 1004
NUMBER_COL                    : 54
STRING_COL                    : YB3XWVR9GI
SNAPTIME$$                    : 01/01/4000 00:00:00
DMLTYPE$$                     : I
OLD_NEW$$                     : N
CHANGE_VECTOR$$               : FE
XID$$                         : 1126041640766467
-----------------

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.04
ANDY@orcl>
ANDY@orcl>update base_t_1 set number_col = 2 where pk_col = 1004;

1 row updated.

Elapsed: 00:00:00.01
ANDY@orcl>@print "select * from MLOG$_BASE_T_1"

PK_COL                        : 1004
NUMBER_COL                    : 54
STRING_COL                    : YB3XWVR9GI
SNAPTIME$$                    : 01/01/4000 00:00:00
DMLTYPE$$                     : I
OLD_NEW$$                     : N
CHANGE_VECTOR$$               : FE
XID$$                         : 1126041640766467
-----------------
PK_COL                        : 1004
NUMBER_COL                    : 54
STRING_COL                    : YB3XWVR9GI
SNAPTIME$$                    : 01/01/4000 00:00:00
DMLTYPE$$                     : U
OLD_NEW$$                     : O
CHANGE_VECTOR$$               : 04
XID$$                         : 1126041640766467
-----------------
PK_COL                        : 1004
NUMBER_COL                    : 2
STRING_COL                    : YB3XWVR9GI
SNAPTIME$$                    : 01/01/4000 00:00:00
DMLTYPE$$                     : U
OLD_NEW$$                     : N
CHANGE_VECTOR$$               : 04
XID$$                         : 1126041640766467
-----------------
We’ve got the complete new version of the row. Can you spot the next problem? Is it obvious which version of the 1004 row is the latest? We know that we couldn’t have inserted the row after the update. For the update, the new version of the row has the old_new$$ flag as ‘N’, so we’ve got that. What happens when we modify the row further?
ANDY@orcl>update base_t_1 set number_col = 1 where pk_col = 1004;

1 row updated.

Elapsed: 00:00:00.03
ANDY@orcl>@print "select * from MLOG$_BASE_T_1"

PK_COL                        : 1004
NUMBER_COL                    : 54
STRING_COL                    : YB3XWVR9GI
SNAPTIME$$                    : 01/01/4000 00:00:00
DMLTYPE$$                     : I
OLD_NEW$$                     : N
CHANGE_VECTOR$$               : FE
XID$$                         : 1126041640766467
-----------------
PK_COL                        : 1004
NUMBER_COL                    : 54
STRING_COL                    : YB3XWVR9GI
SNAPTIME$$                    : 01/01/4000 00:00:00
DMLTYPE$$                     : U
OLD_NEW$$                     : O
CHANGE_VECTOR$$               : 04
XID$$                         : 1126041640766467
-----------------
PK_COL                        : 1004
NUMBER_COL                    : 2
STRING_COL                    : YB3XWVR9GI
SNAPTIME$$                    : 01/01/4000 00:00:00
DMLTYPE$$                     : U
OLD_NEW$$                     : N
CHANGE_VECTOR$$               : 04
XID$$                         : 1126041640766467
-----------------
PK_COL                        : 1004
NUMBER_COL                    : 2
STRING_COL                    : YB3XWVR9GI
SNAPTIME$$                    : 01/01/4000 00:00:00
DMLTYPE$$                     : U
OLD_NEW$$                     : O
CHANGE_VECTOR$$               : 04
XID$$                         : 1126041640766467
-----------------
PK_COL                        : 1004
NUMBER_COL                    : 1
STRING_COL                    : YB3XWVR9GI
SNAPTIME$$                    : 01/01/4000 00:00:00
DMLTYPE$$                     : U
OLD_NEW$$                     : N
CHANGE_VECTOR$$               : 04
XID$$                         : 1126041640766467
-----------------
We could be “clever” and weave through the different versions of the rows linking New’s to Old’s etc and joining on the column values – it may work but I sure hope there’s a better way. What else is in the documentation?

“Use the WITH clause to indicate whether the materialized view log should record the primary key, rowid, object ID, or a combination of these row identifiers when rows in the master are changed. You can also use this clause to add a sequence to the materialized view log to provide additional ordering information for its records.”

Let’s try the sequence clause, rolling back and dropping the mview log first.

create materialized view log on base_t_1 with sequence (number_col, string_col) including new values;

 into base_t_1 (pk_col, number_col, string_col)
select seq_1.nextVal, trunc(dbms_random.value(0,100)), dbms_random.string(‘x’,10)
from   dual;
update base_t_1 set number_col = 2 where pk_col = 1005;
update base_t_1 set number_col = 3 where pk_col = 1005;
@print “select * from MLOG$_BASE_T_1”
PK_COL                        : 1005
NUMBER_COL                    : 14
STRING_COL                    : SH5LQKHTIV
SEQUENCE$$                    : 310001
SNAPTIME$$                    : 01/01/4000 00:00:00
DMLTYPE$$                     : I
OLD_NEW$$                     : N
CHANGE_VECTOR$$               : FE
XID$$                         : 1688897104907956
-----------------
PK_COL                        : 1005
NUMBER_COL                    : 14
STRING_COL                    : SH5LQKHTIV
SEQUENCE$$                    : 310002
SNAPTIME$$                    : 01/01/4000 00:00:00
DMLTYPE$$                     : U
OLD_NEW$$                     : O
CHANGE_VECTOR$$               : 04
XID$$                         : 1688897104907956
-----------------
PK_COL                        : 1005
NUMBER_COL                    : 2
STRING_COL                    : SH5LQKHTIV
SEQUENCE$$                    : 310003
SNAPTIME$$                    : 01/01/4000 00:00:00
DMLTYPE$$                     : U
OLD_NEW$$                     : N
CHANGE_VECTOR$$               : 04
XID$$                         : 1688897104907956
-----------------
PK_COL                        : 1005
NUMBER_COL                    : 2
STRING_COL                    : SH5LQKHTIV
SEQUENCE$$                    : 310004
SNAPTIME$$                    : 01/01/4000 00:00:00
DMLTYPE$$                     : U
OLD_NEW$$                     : O
CHANGE_VECTOR$$               : 04
XID$$                         : 1688897104907956
-----------------
PK_COL                        : 1005
NUMBER_COL                    : 3
STRING_COL                    : SH5LQKHTIV
SEQUENCE$$                    : 310005
SNAPTIME$$                    : 01/01/4000 00:00:00
DMLTYPE$$                     : U
OLD_NEW$$                     : N
CHANGE_VECTOR$$               : 04
XID$$                         : 1688897104907956
-----------------
Perfect! We’ve got our sequence$$ column now, the highest value per primary key tells us the most up to date version of the row.
I think that’s enough for part one. Part two we will create a simple materialized view on this base table (select * from base_table) and see how we can combine the mview log and the mview to create a fresh picture of the base table for query. I plan on publishing part two Wednesday 8th March so stay tuned, if you want further clarification on anything then leave me a comment or reach out to me on twitter @AndrewSayer_