COUNT STOPKEY – NVL

Yesterday I mentioned issues with a recursive delete operation on statistics history. This is a quick illustration of the last points I made on that post regarding the lack of a COUNT STOPKEY optimisation because of the use of NVL. COUNT STOPKEY is an optimisation which allows processing to stop once the target number of rows has been reached. For example:

create table t1 as select * from dba_objects;

alter session set statistics_level = all;

var rn number
exec :rn := 10;

select *
from   t1
where  rownum <= :rn
and    mod(object_id,5) = 0;

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

Plan hash value: 3836375644

-------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |     10 |00:00:00.01 |       4 | 
|*  1 |  COUNT STOPKEY     |      |      1 |        |     10 |00:00:00.01 |       4 |
|*  2 |   TABLE ACCESS FULL| T1   |      1 |  26148 |     10 |00:00:00.01 |       4 |
-------------------------------------------------------------------------------------

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

   1 - filter(ROWNUM<=:RN)
   2 - filter(MOD("OBJECT_ID",5)=0) 

COUNT STOPKEY kicks in after we have fetched the relevant rows and stops any unnecessary further execution – note in particular A-Rows & Buffers for STEP 2 However, if we use NVL around our ROWNUM limitation, then this optimisation is prevented.

var rn number
exec :rn := 10;

select *
from   t1
where  rownum <= nvl(:rn,rownum)
and    mod(object_id,5) = 0;

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

Plan hash value: 624922415

--------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |      1 |        |     10 |00:00:00.09 |    2310 |
|   1 |  COUNT              |      |      1 |        |     10 |00:00:00.09 |    2310 |
|*  2 |   FILTER            |      |      1 |        |     10 |00:00:00.09 |    2310 |
|*  3 |    TABLE ACCESS FULL| T1   |      1 |  26148 |  29630 |00:00:00.08 |    2310 |
--------------------------------------------------------------------------------------

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

   2 - filter(ROWNUM<=NVL(:RN,ROWNUM))
   3 - filter(MOD("OBJECT_ID",5)=0)

Understanding vs Resolution – Statistics History Cleanup

Today I helped resolve a problem quickly but to the detriment of my understanding of exactly what was going on and why.

And as part of the quicker resolution, I had to destroy the evidence which would have helped understand better.

So… now need to go back and figure it out if I have time to model it properly, etc.

Here’s what little I know so far.

What happened was that there were a number of application sessions experiencing slowness and contention when executing a recursive stats history cleanup statement.

Verified via ASH that this recursive delete was somehow being called by app ETL code (TOP_LEVEL_SQL_ID, PLSQL_ENTRY_OBJECT_ID & USER_ID columns), four of them each running slow statement and also because of that nature of the statement below and being blocked by mode 6 TX locks from the leading execution.

Version is 11.2.0.3

Statement was sql id 9v9n97qj8z1dg:

delete /*+ dynamic_sampling(4) */ 
from sys.wri$_optstat_histhead_history 
where savtime < :1 
and rownum <= NVL(:2, rownum);

First up, resolution was quick and easy according to, by a quirk of coincidence, my last post:
https://orastory.wordpress.com/2015/02/16/dbms_stats-purge_stats/

We just got rid of all the statistics history using the magic PURGE_ALL truncate flag.
The history is of limited usefulness day-to-day anyway.

The slowness was holding up ETL jobs which were just trying to calculate stats for their own partition.

I was brought into the situation towards the end of the situation but here’s an illustration of the slowness:

SQL Text
------------------------------
delete /*+ dynamic_sampling(4) */ from sys.wri$_optstat_histhead_history where savtime < :1 and rownum <= NVL(:2, rownum)

Global Information
------------------------------
 Status              :  EXECUTING               
 Instance ID         :  1                       
 SQL ID              :  9v9n97qj8z1dg           
 SQL Execution ID    :  16777249                
 Execution Started   :  02/24/2015 19:11:25     
 First Refresh Time  :  02/24/2015 19:13:06     
 Last Refresh Time   :  02/25/2015 09:10:35     
 Duration            :  50351s                  
 Module/Action       :  JDBC Thin Client/-      
 Program             :  JDBC Thin Client        

Binds
========================================================================================================================
| Name | Position |  Type  |                                           Value                                           |
========================================================================================================================
| :2   |        2 | NUMBER | 10000                                                                                     |
========================================================================================================================

Global Stats
===============================================================================================
| Elapsed |   Cpu   |    IO    | Application | Concurrency |  Other   | Buffer | Read | Read  |
| Time(s) | Time(s) | Waits(s) |  Waits(s)   |  Waits(s)   | Waits(s) |  Gets  | Reqs | Bytes |
===============================================================================================
|   50359 |   35199 |     0.16 |       14669 |         254 |      237 |     1G |   63 | 504KB |
===============================================================================================

SQL Plan Monitoring Details (Plan Hash Value=2348801730)
=======================================================================================================================================================================================================
| Id   |            Operation             |             Name              |  Rows   | Cost  |   Time    | Start  | Execs |   Rows   | Read | Read  | Activity |            Activity Detail            |
|      |                                  |                               | (Estim) |       | Active(s) | Active |       | (Actual) | Reqs | Bytes |   (%)    |              (# samples)              |
=======================================================================================================================================================================================================
|    0 | DELETE STATEMENT                 |                               |         |       |     50262 |   +101 |  1423 |        1 |      |       |          |                                       |
|    1 |   DELETE                         | WRI$_OPTSTAT_HISTHEAD_HISTORY |         |       |     50361 |     +2 |  1423 |        1 |      |       |    29.74 | enq: TX - row lock contention (14630) |
|      |                                  |                               |         |       |           |        |       |          |      |       |          | buffer busy waits (231)               |
|      |                                  |                               |         |       |           |        |       |          |      |       |          | Cpu (82)                              |
|      |                                  |                               |         |       |           |        |       |          |      |       |          | resmgr:cpu quantum (5)                |
|    2 |    COUNT                         |                               |         |       |     50262 |   +101 |  1423 |      14M |      |       |     0.00 | Cpu (1)                               |
|    3 |     FILTER                       |                               |         |       |     50266 |    +97 |  1423 |      14M |      |       |    30.14 | Cpu (15146)                           |
|    4 |      TABLE ACCESS BY INDEX ROWID | WRI$_OPTSTAT_HISTHEAD_HISTORY |      6M | 23218 |     50253 |   +100 |  1423 |       4G |   22 | 176KB |    23.17 | buffer busy waits (18)                |
|      |                                  |                               |         |       |           |        |       |          |      |       |          | Cpu (11627)                           |
| -> 5 |       INDEX RANGE SCAN           | I_WRI$_OPTSTAT_HH_ST          |    990K |  5827 |     50264 |   +101 |  1423 |       4G |   41 | 328KB |    16.94 | buffer busy waits (3)                 |
|      |                                  |                               |         |       |           |        |       |          |      |       |          | Cpu (8332)                            |
|      |                                  |                               |         |       |           |        |       |          |      |       |          | resmgr:cpu quantum (180)              |
=======================================================================================================================================================================================================
   3 - filter(ROWNUM<=NVL(:2,ROWNUM))
   5 - access("WRI$_OPTSTAT_HISTHEAD_HISTORY"."SYS_NC00024$"<SYS_EXTRACT_UTC(:1))

So, first thought was that:

1. I thought that MMON was responsible for purging old stats… clearly not in this case as there were multiple concurrent application connections purging old data as part of their ETL process.

2. The DELETE is deleting any old history older than a parameterised date, the first 10000 rows thereof. There is no connection to the object on whose statistics the application ETL jobs were working on.

3. I would expect a COUNT STOPKEY operation but then the NVL predicate avoids this optimisation.

4. UPDATE: The real time sql monitoring output is also reporting the metrics for 1423 executions of this statement. The clue is in the 14M rows reported in the COUNT + FILTER operations. 1423 * 10000 rows = 14M. But I requested the output for what I thought was a single execution id – 16777249 – strange… bulk/batched delete ?

More questions than answers…

DBMS_STATS.PURGE_STATS

Prior to 11.2.0.4, the optimizer history tables are unpartitioned and DBMS_STATS.PURGE_STATS has little choice but to do do a slow delete of stats before the parameterised input timestamp.

Why might you be purging? Here’s one such illustration:
https://jhdba.wordpress.com/tag/dbms_stats-purge_stats/

This delete can be slow if these tables are large and there are a number of reasons why they might be so, notably if MMON cannot complete the purge within its permitted timeframe.

But note that if you’re happy to purge all history, there is a special TRUNCATE option if you make the call with a magic timestamp:

exec DBMS_STATS.PURGE_STATS(DBMS_STATS.PURGE_ALL);

but Oracle Support emphasises that:

This option is planned to be used as a workaround on urgent cases and under the advice of Support…

Ah… the old magic value pattern / antipattern!

PURGE_ALL CONSTANT TIMESTAMP WITH TIME ZONE :=
 TO_TIMESTAMP_TZ('1001-01-0101:00:00-00:00','YYYY-MM-DDHH:MI:SSTZH:TZM');

As part of the upgrade to 11.2.0.4, one gotcha is that these history tables become partitioned.

I don’t have a copy of 11.2.0.4 to hand but I do have 12.1.0.2 and the tables here are daily interval partitioned so I presume this is the same.

One plus side of this newly partitioned table is that the PURGE_STATS can now drop old partitions which is quicker than delete but a minor downside is that the tables have global indexes so the recursive/internal operations have to be done with UPDATE GLOBAL INDEXES

One curiosity in the trace file from this operation was this statement:

delete /*+ dynamic_sampling(4) */ 
from   sys.wri$_optstat_histhead_history
where  savtime_date < to_date('01-01-1900', 'dd-mm-yyyy') 
and    savtime not in (select timestamp '0000-01-01 00:00:00 -0:0' + sid + serial#/86400
                       from   gv$session 
                       where  status = 'ACTIVE' 
                       and    con_id in (0, sys_context('userenv', 'con_id')))       
and    rownum <= NVL(:1, rownum)

This is deleting from the P_PERMANENT default partition but why is this necessary and what is that subquery all about, particularly the timestamp ‘0000-01-01 00:00:00 -0:0′ + sid + serial#/86400 bit?

Call vs Exec

Just a reference to a really simple difference between CALL and EXEC.
I thought I had mentioned this before but couldn’t find it so…

EXEC/EXECUTE is a SQL*Plus command which wraps the proc call in an anonymous BEGIN … END; block.

CALL is a SQL command hence it is limited to SQL data types and there are other restrictions which the documentation sums up pretty well.

Because CALL is SQL, there is on key behavioural difference which caused a bug on a project a few years ago when Java code was calling a stored proc not with BEGIN … END; but with CALL and ended up swallowing certain exceptions:

SQL> l
  1  create or replace procedure p1
  2  as
  3  begin
  4    raise no_data_found;
  5* end;
SQL> /

Procedure created.

SQL> exec p1;
BEGIN p1; END;

*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at "PGPS_UAT1.P1", line 4
ORA-06512: at line 1


SQL> call p1();

Call completed.

SQL>

SQL expects and handles certain exceptions.

So always use BEGIN and END; blocks in application code rather than CALL.

Partition gotchas

Two minor partition gotchas on partition extent sizing and on the number of partitions in an interval partitioned.

First – old news – note that initial extent size for partitioned tables changed in 11.2.0.2.
Somehow this passed me by and I was late to the large extent party – duh!
Influenced by parameter _partition_large_extents, the default was changed to 8MB rather than 64K for autoallocated ASSM extent sizing.

Pre 11.2.0.2 (non-Exadata):

alter session set "_partition_large_extents" = false;

create table t1
(col1   number not null)
partition by range (col1)    interval ( 1 )
(partition p0 values less than (1));

insert into t1 (col1) values (1);

See initial extent size of 64k:

select table_name
,      partition_name
,      high_value
,      initial_extent
,      next_extent 
from user_tab_partitions where table_name = 'T1';

TABLE_NAME PARTITION_NAME HIGH_VALUE INITIAL_EXTENT NEXT_EXTENT
---------- -------------- ---------- -------------- -----------
T1         P0             1          
T1         SYS_P133592    2          65536          1048576 

Onwards from 11.2.0.2, the following is the default behaviour:

alter session set "_partition_large_extents" = true;

drop table t1;

create table t1
(col1   number not null)
partition by range (col1)    interval ( 1 )
(partition p0 values less than (1));

insert into t1 (col1) values (1);

Back to default initial extent of 8MB:

select table_name
,      partition_name
,      high_value
,      initial_extent
,      next_extent 
from   user_tab_partitions where table_name = 'T1';

TABLE_NAME PARTITION_NAME HIGH_VALUE INITIAL_EXTENT NEXT_EXTENT
---------- -------------- ---------- -------------- -----------
T1         P0             1          
T1         SYS_P133593    2          8388608        1048576 

A friend highlighted this to me on his DB where a lot of space had been wasted on small or empty partitions.
If empty, why was space wasted? Did not deferred segment creation benefit?
Deferred segment creation was off on this DB because of historic occurrence of bug 12535346 with deferred constraints combined with other features with “deferred” behaviour (deferred segment creation or interval partition).

Second gotcha – if you are considering INTERVAL PARTITIONING, particularly with a RANGE of 1, think about the impact of the maximum number of interval partitions.

Following on from example T1 table above:

select table_name
,      partition_name
,      high_value 
from   user_tab_partitions where table_name = 'T1';

TABLE_NAME PARTITION_NAME HIGH_VALUE 
---------- -------------- -----------
T1         P0             1                                                                                
T1         SYS_P133593    2        
insert into t1 (col1) values (1048574);

1 rows inserted

insert into t1 (col1) values (1048575);

ORA-14300: partitioning key maps to a partition outside maximum permitted number of partitions

The maximum number of partitions in an interval partition is documented as 1024K -1 = 1048575.

select table_name
,      partition_name
,      high_value 
from   user_tab_partitions where table_name = 'T1';

TABLE_NAME PARTITION_NAME HIGH_VALUE 
---------- -------------- -----------
T1         P0             1                                                                                
T1         SYS_P133593    2                                                                                
T1         SYS_P133594    1048575   

Although we only have two materialised partitions, the possible future partition boundaries inbetween are effectively reserved for numbers between the lower and upper boundaries.

There’s not a lot we can do about this UNLESS you know that you’re not going to need those reserved partitions OR if you’re happy for other numbers to go into existing partitions, effectively having ranges much larger than the original 1.

At the moment, if we go higher than our reserved boundaries it fails, but for example if the key “1048573” comes along with is within our reserved boundaries, it will create a new partition:

insert into t1 (col1) values (1048573);

1 row inserted

select table_name
,      partition_name
,      high_value 
,      interval
from   user_tab_partitions where table_name = 'T1';

TABLE_NAME PARTITION_NAME HIGH_VALUE INTERVAL
---------- -------------- ---------- --------
T1         P0             1          YES                                                              
T1         SYS_P133593    2          YES
T1         SYS_P133594    1048575    YES
T1         SYS_P133595    1048574    YES

But we can do is convert our existing partitions to normal RANGE partitions:

alter table t1 set interval ();

table altered

select table_name
,      partition_name
,      high_value 
,      interval
from   user_tab_partitions where table_name = 'T1';

TABLE_NAME PARTITION_NAME HIGH_VALUE INTERVAL
---------- -------------- ---------- --------
T1         P0             1          NO                                                              
T1         SYS_P133593    2          NO
T1         SYS_P133594    1048575    NO
T1         SYS_P133595    1048574    NO

And then we can set the table back to interval partitioning without affecting those existing partitions:

alter table t1 set interval (1);

Now if a new values comes along, if it falls within an existing range, it will use the existing range partition:

insert into t1 (col1) values (1048572);

1 row inserted

select table_name
,      partition_name
,      high_value 
,      interval
from   user_tab_partitions where table_name = 'T1';

TABLE_NAME PARTITION_NAME HIGH_VALUE INTERVAL
---------- -------------- ---------- --------
T1         P0             1          NO                                                              
T1         SYS_P133593    2          NO
T1         SYS_P133594    1048575    NO
T1         SYS_P133595    1048574    NO

select col1
, (select subobject_name from dba_objects where data_object_id = rd) pname 
from (
select col1
,      dbms_rowid.rowid_object(rowid) rd
from t1 where col1 IN (1048572,1048573));

      COL1 PNAME                        
---------- ------------------------------
   1048573 SYS_P133595                    
   1048572 SYS_P133595      

But if a new high comes along, we can now create our new interval partition:

insert into t1 (col1) values (1048575);

1 row inserted

select table_name
,      partition_name
,      high_value 
,      interval
from   user_tab_partitions where table_name = 'T1';

TABLE_NAME PARTITION_NAME HIGH_VALUE INTERVAL
---------- -------------- ---------- --------
T1         P0             1          NO                                                              
T1         SYS_P133593    2          NO
T1         SYS_P133594    1048575    NO
T1         SYS_P133595    1048574    NO
T1         SYS_P133596    1048576    YES

And our limit of 1048575 partitions still exists but the reserved future interval partitions can move out:

insert into t1 (col1) values (2097145);

1 row inserted

insert into t1 (col1) values (2097146);

ORA-14300: partitioning key maps to a partition outside maximum permitted number of partitions

This workaround has very limited usefulness as mentioned.
Bottom line – if using interval partitioning, pay careful consideration to this limit.

RELY DISABLE

Learning, relearning or unforgetting…

What value is there in a DISABLEd constraint?

This was a question on the OTN forums this week and a) my first reply was wrong and b) I couldn’t find a clear demonstration elsewhere.

The key is RELY.

The documentation is clear.

RELY Constraints

The ETL process commonly verifies that certain constraints are true. For example, it can validate all of the foreign keys in the data coming into the fact table. This means that you can trust it to provide clean data, instead of implementing constraints in the data warehouse. You create a RELY constraint as follows:

ALTER TABLE sales ADD CONSTRAINT sales_time_fk FOREIGN KEY (time_id) REFERENCES times (time_id) RELY DISABLE NOVALIDATE;

This statement assumes that the primary key is in the RELY state. RELY constraints, even though they are not used for data validation, can:

– Enable more sophisticated query rewrites for materialized views. See Chapter 18, “Basic Query Rewrite” for further details.
– Enable other data warehousing tools to retrieve information regarding constraints directly from the Oracle data dictionary.

Creating a RELY constraint is inexpensive and does not impose any overhead during DML or load. Because the constraint is not being validated, no data processing is necessary to create it.

We can prove the value of a RELY DISABLEd CONSTRAINT by playing withTom Kyte’s illustrations on the value of ENABLEd constraints.

EMP/DEPT Table:

drop table emp;
drop table dept;

create table dept 
(deptno number(2)     not null,
 dname  varchar2(15),
 loc    varchar2(15));

insert into dept values (10, 'ACCOUNTING', 'NEW YORK');
insert into dept values (20, 'RESEARCH', 'DALLAS');
insert into dept values (30, 'SALES', 'CHICAGO');
insert into dept values (40, 'OPERATIONS', 'BOSTON');

create table emp
(empno    number(4) not null
,ename    varchar2(10)
,job      varchar2(9)
,mgr      number(4)
,hiredate date
,sal      number(7, 2)
,comm     number(7, 2)
,deptno   number(2) not null);

insert into emp values (7369, 'SMITH', 'CLERK',    7902, to_date('17-DEC-1980', 'DD-MON-YYYY'), 800, null, 20);
insert into emp values (7499, 'ALLEN', 'SALESMAN', 7698, to_date('20-FEB-1981', 'DD-MON-YYYY'), 1600, 300, 30);
insert into emp values (7521, 'WARD',  'SALESMAN', 7698, to_date('22-FEB-1981', 'DD-MON-YYYY'), 1250, 500, 30);
insert into emp values (7566, 'JONES', 'MANAGER',  7839, to_date('2-APR-1981',  'DD-MON-YYYY'), 2975, null, 20);
insert into emp values (7654, 'MARTIN', 'SALESMAN', 7698,to_date('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30);
insert into emp values (7698, 'BLAKE', 'MANAGER', 7839,to_date('1-MAY-1981', 'DD-MON-YYYY'), 2850, null, 30);
insert into emp values (7782, 'CLARK', 'MANAGER', 7839,to_date('9-JUN-1981', 'DD-MON-YYYY'), 2450, null, 10);
insert into emp values (7788, 'SCOTT', 'ANALYST', 7566,to_date('09-DEC-1982', 'DD-MON-YYYY'), 3000, null, 20);
insert into emp values (7839, 'KING', 'PRESIDENT', null,to_date('17-NOV-1981', 'DD-MON-YYYY'), 5000, null, 10);
insert into emp values (7844, 'TURNER', 'SALESMAN', 7698,to_date('8-SEP-1981', 'DD-MON-YYYY'), 1500, 0, 30);
insert into emp values (7876, 'ADAMS', 'CLERK', 7788,to_date('12-JAN-1983', 'DD-MON-YYYY'), 1100, null, 20);
insert into emp values (7900, 'JAMES', 'CLERK', 7698,to_date('3-DEC-1981', 'DD-MON-YYYY'), 950, null, 30);
insert into emp values (7902, 'FORD', 'ANALYST', 7566,to_date('3-DEC-1981', 'DD-MON-YYYY'), 3000, null, 20);
insert into emp values (7934, 'MILLER', 'CLERK', 7782,to_date('23-JAN-1982', 'DD-MON-YYYY'), 1300, null, 10);

begin
  dbms_stats.set_table_stats
  ( user, 'EMP', numrows=>1000000, numblks=>100000 );
  dbms_stats.set_table_stats
  ( user, 'DEPT', numrows=>100000, numblks=>10000 );
end; 
/

First, there’s nearly always an oddity to observe or tangent to follow:

alter table dept add constraint dept_pk primary key(deptno);
alter table emp add constraint emp_fk_dept foreign key(deptno) references dept(deptno) rely disable novalidate;

Results in:

SQL Error: ORA-25158: Cannot specify RELY for foreign key if the associated primary key is NORELY
25158. 00000 -  "Cannot specify RELY for foreign key if the associated primary key is NORELY"
*Cause:    RELY is specified for the foreign key contraint, when the
           associated primary key constraint is NORELY.
*Action:   Change the option of the primary key also to RELY.

But this is ok?

alter table emp add constraint emp_fk_dept foreign key(deptno) references dept(deptno) disable novalidate;
alter table emp modify constraint emp_fk_dept rely;

Odd!

Anyway, first, we can show a clear demonstration of JOIN ELIMINATION.

No FK constraint:

alter table emp drop constraint emp_fk_dept;

create or replace view emp_dept
as
select emp.ename, dept.dname
from   emp, dept
where  emp.deptno = dept.deptno; 

select ename from emp_dept;
select * from table(dbms_xplan.display_cursor);

Gives plan:

Plan hash value: 4269077325
------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |       |       | 21974 (100)|          |
|   1 |  NESTED LOOPS      |         |  1000K|    31M| 21974   (1)| 00:04:24 |
|   2 |   TABLE ACCESS FULL| EMP     |  1000K|    19M| 21924   (1)| 00:04:24 |
|*  3 |   INDEX UNIQUE SCAN| DEPT_PK |     1 |    13 |     0   (0)|          |
------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")

Now with added constraint, RELY DISABLE:

alter table emp add constraint emp_fk_dept foreign key(deptno) references dept(deptno) disable novalidate;
alter table emp modify constraint emp_fk_dept rely;

select ename from emp_dept;
select * from table(dbms_xplan.display_cursor);

And we get:

Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       | 21925 (100)|          |
|*  1 |  TABLE ACCESS FULL| EMP  | 50000 |   976K| 21925   (1)| 00:04:24 |
--------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
    1 - filter("EMP"."DEPTNO" IS NOT NULL)

And just to confirm our constraint state:

select constraint_name, status, validated, rely from user_constraints where constraint_name = 'EMP_FK_DEPT';
CONSTRAINT_NAME                STATUS   VALIDATED     RELY
------------------------------ -------- ------------- ----
EMP_FK_DEPT                    DISABLED NOT VALIDATED RELY 

Now we can also see benefit in MV query_rewrite:

create materialized view mv_emp_dept
enable query rewrite
as
select dept.deptno, dept.dname, count (*) 
from   emp, dept
where  emp.deptno = dept.deptno
group by dept.deptno, dept.dname;

begin
   dbms_stats.set_table_stats
   ( user, 'mv', numrows=>100000, numblks=>10000 );
end; 
/

alter session set query_rewrite_enabled = false;
select count(*) from emp;
select * from table(dbms_xplan.display_cursor);
Plan hash value: 2083865914
-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       | 21917 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| EMP  |  1000K| 21917   (1)| 00:04:24 |
-------------------------------------------------------------------

Enable query_rewrite and we can use MV instead:

alter session set query_rewrite_enabled = true;
alter session set query_rewrite_integrity = trusted;
select count(*) from emp;
select * from table(dbms_xplan.display_cursor);
Plan hash value: 632580757
---------------------------------------------------------------------------------------------
| Id  | Operation                     | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |             |       |       |     3 (100)|          |
|   1 |  SORT AGGREGATE               |             |     1 |    13 |            |          |
|   2 |   MAT_VIEW REWRITE ACCESS FULL| MV_EMP_DEPT |     3 |    39 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

The mess that is fast-refresh join-only Materialized Views

Every now and then you come across a feature or a combination of features which has turned into such a dog’s dinner that you wonder how many people can possibly be using it.

This week – fast fresh materialized views.
I would have thought that this was a very commonly used feature.

This is quite a long article so I will do a top-level TOC first

  1. Why am I looking at a fast-refresh, on-commit, join-only materialized view?
  2. Show me it working
  3. What’s it done to my commit time?
  4. How can this be avoided this with _mv_refresh_use_stats?
  5. Is it any different if I use MLOG stats?
  6. Why might I not want to mess with _mv_use_refresh_stats?


First, why am I looking at a fast refresh, on-commit join-only materialized view.

I have got two datasets which only produce a small resultset when they are joined.
But neither is, on their own, highly selective.

The model below might not be 100% representative of my real-world situation.
I’ve tried to sanitise and simplify but as a result I’ve then had to bump up the volumes a bit to make the point.

drop table t1;
drop table t2;

create table t1
as
select rownum oid
,      case when rownum <= 1000000 then 'Y' else 'N' end flag
,      rownum oid_t2
,      rpad('X',200,'X') padding
from   dual
connect by rownum <= 10000000
order by dbms_random.value;

create unique index i_t1_1 on t1 (oid);
create index i_t1_2 on t1(flag);
create index i_t1_3 on t1(oid_t2);

create table t2
as
select rownum oid
,      case when rownum >= 999901 then 'Y' else 'N' end flag
,      rpad('X',200,'X') padding
from   dual
connect by rownum <= 10000000
order by dbms_random.value;

create unique index i_t2_1 on t2 (oid);
create index i_t2_2 on t2(flag);

Currently, I can crunch my data in a few seconds:

select *
from   t1
,      t2
where  t1.flag   = 'Y'
and    t1.oid_t2 = t2.oid
and    t2.flag   = 'Y';

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

Gives

-------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |      1 |        |    100 |00:00:15.91 |     606K|    303K|       |       |          |
|*  1 |  HASH JOIN                   |        |      1 |    860K|    100 |00:00:15.91 |     606K|    303K|   238M|  7667K|  292M (0)|
|   2 |   TABLE ACCESS BY INDEX ROWID| T1     |      1 |    860K|   1000K|00:00:01.54 |     303K|      0 |       |       |          |
|*  3 |    INDEX RANGE SCAN          | I_T1_2 |      1 |    860K|   1000K|00:00:00.21 |    1814 |      0 |       |       |          |
|*  4 |   TABLE ACCESS FULL          | T2     |      1 |   8275K|   9000K|00:00:09.78 |     303K|    303K|       |       |          |
------------------------------------------------------------------------------------------------------------------------------------- 

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

   1 - access("T1"."OID_T2"="T2"."OID")
   3 - access("T1"."FLAG"='Y')
   4 - filter("T2"."FLAG"='Y')

But I want to query these tables quite frequently and preferably I want this driving logic to be subsecond.
And there will have reasonable inserts and updates incoming at various times.

So, one of the better options is a join-only materialized view.


So, let’s get going:

create materialized view log on t1 with rowid;
create materialized view log on t2 with rowid;

create materialized view mv_t1_t2
refresh fast on commit
enable query rewrite
as
select t1.rowid   t1_rowid
,      t1.oid     t1_oid
,      t1.flag    t1_flag
,      t1.oid_t2  t1_oid_t2
,      t1.padding t1_padding
,      t2.rowid   t2_rowid
,      t2.oid     t2_oid
,      t2.flag    t2_flag
,      t2.padding t2_padding
from   t1
,      t2
where  t1.flag   = 'Y'
and    t1.oid_t2 = t2.oid
and    t2.flag   = 'Y';

create index i_mv_t1_t2_1 on mv_t1_t2(t1_rowid);
create index i_mv_t1_t2_2 on mv_t1_t2(t2_rowid);

Tangent alert!
Now, if I wasn’t going to go off on this tangent because the article will be long enough anyway but when running up the article, a number of questions about
“hy was this not being rewritten?” came up so, we might as well cover more bases.

So, if we run our SELECT now, what happens?

select *
from   t1
,      t2
where  t1.flag   = 'Y'
and    t1.oid_t2 = t2.oid
and    t2.flag   = 'Y';

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |      1 |        |    100 |00:00:08.22 |     606K|       |       |          |
|*  1 |  HASH JOIN                   |        |      1 |    860K|    100 |00:00:08.22 |     606K|   238M|  7667K|  260M (0)|
|   2 |   TABLE ACCESS BY INDEX ROWID| T1     |      1 |    860K|   1000K|00:00:01.58 |     303K|       |       |          |
|*  3 |    INDEX RANGE SCAN          | I_T1_2 |      1 |    860K|   1000K|00:00:00.21 |    1814 |       |       |          |
|*  4 |   TABLE ACCESS FULL          | T2     |      1 |   8275K|   9000K|00:00:02.69 |     303K|       |       |          |
---------------------------------------------------------------------------------------------------------------------------- 

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

   1 - access("T1"."OID_T2"="T2"."OID")
   3 - access("T1"."FLAG"='Y')
   4 - filter("T2"."FLAG"='Y')

Bother!

We could go digging around but let’s try the easy way out – DBMS_MVIEW.EXPLAIN_REWRITE

CREATE TABLE REWRITE_TABLE(
  statement_id          VARCHAR2(30),   -- id for the query
  mv_owner              VARCHAR2(30),   -- owner of the MV
  mv_name               VARCHAR2(30),   -- name of the MV
  sequence              INTEGER,        -- sequence no of the msg
  query                 VARCHAR2(2000), -- user query
  query_block_no        INTEGER,        -- block no of the current subquery
  rewritten_txt         VARCHAR2(2000), -- rewritten query
  message               VARCHAR2(512),  -- EXPLAIN_REWRITE msg
  pass                  VARCHAR2(3),    -- rewrite pass no
  mv_in_msg             VARCHAR2(30),   -- MV in current message
  measure_in_msg        VARCHAR2(30),   -- Measure in current message
  join_back_tbl         VARCHAR2(30),   -- Join back table in message
  join_back_col         VARCHAR2(30),   -- Join back column in message
  original_cost         INTEGER,        -- Cost of original query
  rewritten_cost        INTEGER,        -- Cost of rewritten query
  flags                 INTEGER,        -- associated flags
  reserved1             INTEGER,        -- currently not used
  reerved2              VARCHAR2(10))   -- currently not used;

DECLARE
l_sql CLOB :=
q'{select *
from   t1
,      t2
where  t1.flag   = 'Y'
and    t1.oid_t2 = t2.oid
and    t2.flag   = 'Y'}';
BEGIN
 DBMS_MVIEW.EXPLAIN_REWRITE(l_sql,'MV_T1_T2','mysql');
END;
/

select message from rewrite_table;
MESSAGE
-------------------------------------
QSM-01150: query did not rewrite
QSM-01001: query rewrite not enabled

Yep – easy one.
Let’s set query_rewrite_enabled to true (would be system level if this was not just a test/demo) and repeat.

alter session set query_rewrite_enabled = true;
select *
from   t1
,      t2
where  t1.flag   = 'Y'
and    t1.oid_t2 = t2.oid
and    t2.flag   = 'Y';

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
---------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |      1 |        |    101 |00:00:00.01 |      16 |
|   1 |  MAT_VIEW REWRITE ACCESS FULL| MV_T1_T2 |      1 |    101 |    101 |00:00:00.01 |      16 |
--------------------------------------------------------------------------------------------------

Great – I have my subsecond response time.

But what about updates to the data?

First of all, for completeness, let’s observe what happens when we make an uncommitted change.

update t1 set flag = 'Y' where oid = 1000001;

1 rows updated
select *
from   t1
,      t2
where  t1.flag   = 'Y'
and    t1.oid_t2 = t2.oid
and    t2.flag   = 'Y';

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |      1 |        |    101 |00:00:08.27 |     606K|       |       |          |
|*  1 |  HASH JOIN                   |        |      1 |    860K|    101 |00:00:08.27 |     606K|   238M|  7667K|  260M (0)|
|   2 |   TABLE ACCESS BY INDEX ROWID| T1     |      1 |    860K|   1000K|00:00:01.63 |     303K|       |       |          |
|*  3 |    INDEX RANGE SCAN          | I_T1_2 |      1 |    860K|   1000K|00:00:00.22 |    1814 |       |       |          |
|*  4 |   TABLE ACCESS FULL          | T2     |      1 |   8275K|   9000K|00:00:02.65 |     303K|       |       |          |
---------------------------------------------------------------------------------------------------------------------------- 

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

   1 - access("T1"."OID_T2"="T2"."OID")
   3 - access("T1"."FLAG"='Y')
   4 - filter("T2"."FLAG"='Y')

I’ve lost my usage of the MV.
This is expected.
Why?
DBMS_MVIEW.EXPLAIN_REWRITE tells us:

MESSAGE
------------------------------------------------------------------------------------------------------------------------
QSM-01150: query did not rewrite
QSM-01279: query rewrite not possible because DML operation occurred on a table referenced by materialized view MV_T1_T2

We can’t use the materialized view because it’s stale.
To use it we have to consider whether we want to run with query_rewrite_integrity set to stale_tolerated.
In this case, not.

If I continue to leave the update uncommitted and check another session, the it continues to use the MV.

But, another tangent!!
In that other session, if I set statistics_level to all, then it won’t use the MV and DBMS_MVIEW.EXPLAIN_REWRITE can’t tell me why.
I’m going to conveniently leave that investigation for another time but just show the evidence:

alter session set statistics_level = all;
alter session set query_rewrite_enabled = true;

select *
from   t1
,      t2
where  t1.flag   = 'Y'
and    t1.oid_t2 = t2.oid
and    t2.flag   = 'Y';

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |      1 |        |    101 |00:00:08.10 |     606K|       |       |          |
|*  1 |  HASH JOIN                   |        |      1 |    860K|    101 |00:00:08.10 |     606K|   238M|  7667K|  260M (0)|
|   2 |   TABLE ACCESS BY INDEX ROWID| T1     |      1 |    860K|   1000K|00:00:01.49 |     303K|       |       |          |
|*  3 |    INDEX RANGE SCAN          | I_T1_2 |      1 |    860K|   1000K|00:00:00.22 |    1814 |       |       |          |
|*  4 |   TABLE ACCESS FULL          | T2     |      1 |   8275K|   9000K|00:00:02.72 |     303K|       |       |          |
----------------------------------------------------------------------------------------------------------------------------

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

   1 - access("T1"."OID_T2"="T2"."OID")
   3 - access("T1"."FLAG"='Y')
   4 - filter("T2"."FLAG"='Y')

But

alter session set statistics_level = typical;
alter session set query_rewrite_enabled = true;

select *
from   t1
,      t2
where  t1.flag   = 'Y'
and    t1.oid_t2 = t2.oid
and    t2.flag   = 'Y';

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
----------------------------------------------------------
| Id  | Operation                    | Name     | E-Rows |
----------------------------------------------------------
|   0 | SELECT STATEMENT             |          |        |
|   1 |  MAT_VIEW REWRITE ACCESS FULL| MV_T1_T2 |    101 |
----------------------------------------------------------   

Note
-----
   - Warning: basic plan statistics not available.....

So… anyway… we have our fast-refresh on commit materialized view.


What happens when we commit my previous change?

set timing on
update t1 set flag = 'Y' where oid = 1000001;

1 rows updated.
Elapsed: 00:00:00.029

commit

committed.
Elapsed: 00:00:02.098

Gadzooks! My COMMIT now takes two seconds!

At this point, if you read a lot of technical articles, you might get lucky and have that vague sense of nagging familiarity about having read about this at some point…

Alberto Dell’Era has an excellent set of articles which helps understand what is going on here and what we might do about it.

If we trace my session, we can find out what is contributing to this unpleasant two seconds.
I’m going to cheat, flush the shared pool, do the update and commit and check v$sql
Here is a summary of the significant contributing sql snippets.

SQL_ID        CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS ELAPSED_TIME/1000/1000 SQL_FULLTEXT
------------- ------------ --------------- ---------- ---------------------- --------------------------------------------------------------------------------
dt1mmbxbp6uk7            1                          1                 1.9356 /* MV_REFRESH (INS) */ INSERT INTO "DOM"."MV_T1_T2" SELECT /*+ NO_MERGE(
5qah9xjyhapg1            0       828077931          1               0.010237 /* MV_REFRESH (DEL) */ DELETE FROM "E668983_DBA"."MV_T1_T2" SNA$ WHERE "T1_ROWID
4agmycb29dd1p            0                          1               0.001747 delete from "DOM"."MLOG$_T2" where xid$$ = :1
f8wzsn9dzcusb            0      1745227344          1               0.000589 delete from "DOM"."MLOG$_T1" where xid$$ = :1
ar6vnyxkss2kq            0       375101422          1               0.000518 select dmltype$$ from "DOM"."MLOG$_T1"  where xid$$ = :1  group by dmlty

A couple of interesting observations.
First is that we can see the XID$$ mechanism which Alberto mentions in his articles linked to above.

Second is that the INSERT is the major contributor to our commit time, followed by the DELETE.
We shall focus on these.

These are the prettified statements:

/* MV_REFRESH (INS) */
INSERT INTO "DOM"."MV_T1_T2"
SELECT /*+ NO_MERGE("JV$") */ "JV$"."RID$","JV$"."OID","JV$"."FLAG","JV$"."OID_T2","JV$"."PADDING","MAS$0".ROWID,"MAS$0"."OID","MAS$0"."FLAG","MAS$0"."PADDING"
FROM   ( SELECT "MAS$"."ROWID" "RID$"  ,  "MAS$".*
         FROM "DOM"."T1" "MAS$"
         WHERE ROWID IN (SELECT  /*+ HASH_SJ */  CHARTOROWID("MAS$"."M_ROW$$") RID$
                         FROM "DOM"."MLOG$_T1" "MAS$"
                         WHERE "MAS$".XID$$ = :1 )) "JV$", "T2" AS OF SNAPSHOT(:B_SCN)  "MAS$0"
WHERE  "JV$"."FLAG"='Y'
AND    "JV$"."OID_T2"="MAS$0"."OID"
AND    "MAS$0"."FLAG"='Y';

With plan:

------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                 |            |      1 |        |      0 |00:00:01.92 |     303K|       |       |          |
|   1 |  LOAD TABLE CONVENTIONAL         |            |      1 |        |      0 |00:00:01.92 |     303K|       |       |          |
|   2 |   NESTED LOOPS                   |            |      1 |        |      1 |00:00:01.92 |     303K|       |       |          |
|   3 |    NESTED LOOPS                  |            |      1 |      1 |      1 |00:00:01.92 |     303K|       |       |          |
|   4 |     VIEW                         |            |      1 |      1 |      1 |00:00:01.92 |     303K|       |       |          |
|*  5 |      HASH JOIN RIGHT SEMI        |            |      1 |      1 |      1 |00:00:01.92 |     303K|   832K|   832K|  387K (0)|
|   6 |       TABLE ACCESS BY INDEX ROWID| MLOG$_T1   |      1 |      1 |      1 |00:00:00.01 |       2 |       |       |          |
|*  7 |        INDEX RANGE SCAN          | I_MLOG$_T1 |      1 |      1 |      1 |00:00:00.01 |       1 |       |       |          |
|   8 |       TABLE ACCESS BY INDEX ROWID| T1         |      1 |    860K|   1000K|00:00:01.58 |     303K|       |       |          |
|*  9 |        INDEX RANGE SCAN          | I_T1_2     |      1 |    860K|   1000K|00:00:00.21 |    1814 |       |       |          |
|* 10 |     INDEX UNIQUE SCAN            | I_T2_1     |      1 |      1 |      1 |00:00:00.01 |       3 |       |       |          |
|* 11 |    TABLE ACCESS BY INDEX ROWID   | T2         |      1 |      1 |      1 |00:00:00.01 |       1 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------ 

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

   5 - access(ROWID=CHARTOROWID("MAS$"."M_ROW$$"))
   7 - access("MAS$"."XID$$"=:1)
   9 - access("MAS$"."FLAG"='Y')
  10 - access("JV$"."OID_T2"="MAS$0"."OID")
  11 - filter("MAS$0"."FLAG"='Y')
/* MV_REFRESH (DEL) */
DELETE
FROM   "DOM"."MV_T1_T2" SNA$
WHERE  "T1_ROWID" IN (SELECT /*+ NO_MERGE  HASH_SJ  */ *
                      FROM   (SELECT  CHARTOROWID("MAS$"."M_ROW$$") RID$
                              FROM "DOM"."MLOG$_T1" "MAS$"
                              WHERE "MAS$".XID$$ = :1 )MAS$)
-----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT              |              |      1 |        |      0 |00:00:00.01 |       9 |       |       |          |
|   1 |  DELETE                       | MV_T1_T2     |      1 |        |      0 |00:00:00.01 |       9 |       |       |          |
|*  2 |   HASH JOIN SEMI              |              |      1 |    101 |      1 |00:00:00.01 |       3 |   963K|   963K| 1252K (0)|
|   3 |    INDEX FULL SCAN            | I_MV_T1_T2_1 |      1 |    101 |    101 |00:00:00.01 |       1 |       |       |          |
|   4 |    TABLE ACCESS BY INDEX ROWID| MLOG$_T1     |      1 |      1 |      1 |00:00:00.01 |       2 |       |       |          |
|*  5 |     INDEX RANGE SCAN          | I_MLOG$_T1   |      1 |      1 |      1 |00:00:00.01 |       1 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------------  

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

   2 - access("T1_ROWID"=CHARTOROWID("MAS$"."M_ROW$$"))
   5 - access("MAS$"."XID$$"=:1)

If you have read Alberto’s blogs then you will notice the problematic HASH JOIN RIGHT SEMI in the INSERT
And you will also be familiar with a couple of solutions which now present themselves.

It’s great that there are known workarounds to this problem but, as I hope to demonstrate, the fact that there are at least three separate codepaths through the fast refresh mechanism worries me particularly when we see that
at least one of these alternative code paths causes bugs with other variations on the MV refresh mechanism. What a mess!!


First solution to the problem is paramter _mv_refresh_use_stats.
Repeat update & commit:

alter session set "_mv_refresh_use_stats" = true;

session SET altered.

set timing on
update t1 set flag = 'Y' where oid = 1000001;

Elapsed: 00:00:00.035

commit;
committed.
Elapsed: 00:00:00.174

That’s better – 100ms for my commit.

What do our INSERT and DELETE statements look like now?
Note the different SQL IDs – these are completely different statements.

SQL_ID        CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS ELAPSED_TIME/1000/1000 SQL_FULLTEXT
------------- ------------ --------------- ---------- ---------------------- --------------------------------------------------------------------------------
av53npjd112vx            1       374625119          1               0.013106 /* MV_REFRESH (INS) */ INSERT INTO "DOM"."MV_T1_T2" SELECT /*+ NO_MERGE("JV$") *
5a6ugwh5v1j5x            0       466548951          1               0.009088 /* MV_REFRESH (DEL) */ DELETE FROM "DOM"."MV_T1_T2" SNA$ WHERE "T1_ROWID" IN (SE
gpk46p11kbp3d            0      1735585849          1               0.001665 SELECT OLD_NEW$$, COUNT(*)  FROM "DOM"."MLOG$_T1"   WHERE SNAPTIME$$ > :1 AND SN
4agmycb29dd1p            0                          1               0.001615 delete from "DOM"."MLOG$_T2" where xid$$ = :1
f8wzsn9dzcusb            0      1745227344          1               0.000538 delete from "DOM"."MLOG$_T1" where xid$$ = :1
ar6vnyxkss2kq            0       375101422          1               0.000515 select dmltype$$ from "DOM"."MLOG$_T1"  where xid$$ = :1  group by dmltype$$     

For both the INSERT and the DELETE, we’ve lost the HASH_SJ hint and we’ve gained a CARDINALITY hint and a NO_SEMIJOIN.

/* MV_REFRESH (INS) */
INSERT INTO "DOM"."MV_T1_T2"
SELECT /*+ NO_MERGE("JV$") */ "JV$"."RID$","JV$"."OID","JV$"."FLAG","JV$"."OID_T2","JV$"."PADDING","MAS$0".ROWID,"MAS$0"."OID","MAS$0"."FLAG","MAS$0"."PADDING"
FROM ( SELECT "MAS$"."ROWID" "RID$"  ,  "MAS$".*
       FROM   "DOM"."T1" "MAS$"
       WHERE  ROWID IN (SELECT /*+ CARDINALITY(MAS$ 0)  NO_SEMIJOIN */  CHARTOROWID("MAS$"."M_ROW$$") RID$
                        FROM   "DOM"."MLOG$_T1" "MAS$"
                        WHERE  "MAS$".XID$$ = :1 )) "JV$", "T2" AS OF SNAPSHOT(:B_SCN)  "MAS$0"
WHERE "JV$"."FLAG"='Y'
AND   "JV$"."OID_T2"="MAS$0"."OID"
AND    "MAS$0"."FLAG"='Y'

Giving plan:

--------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                   |            |      1 |        |      0 |00:00:00.01 |      15 |       |       |          |
|   1 |  LOAD TABLE CONVENTIONAL           |            |      1 |        |      0 |00:00:00.01 |      15 |       |       |          |
|   2 |   NESTED LOOPS                     |            |      1 |        |      1 |00:00:00.01 |       7 |       |       |          |
|   3 |    NESTED LOOPS                    |            |      1 |      1 |      1 |00:00:00.01 |       6 |       |       |          |
|   4 |     VIEW                           |            |      1 |      1 |      1 |00:00:00.01 |       3 |       |       |          |
|   5 |      NESTED LOOPS                  |            |      1 |      1 |      1 |00:00:00.01 |       3 |       |       |          |
|   6 |       VIEW                         | VW_NSO_1   |      1 |      1 |      1 |00:00:00.01 |       2 |       |       |          |
|   7 |        SORT UNIQUE                 |            |      1 |      1 |      1 |00:00:00.01 |       2 | 73728 | 73728 |          |
|   8 |         TABLE ACCESS BY INDEX ROWID| MLOG$_T1   |      1 |      1 |      1 |00:00:00.01 |       2 |       |       |          |
|*  9 |          INDEX RANGE SCAN          | I_MLOG$_T1 |      1 |      2 |      1 |00:00:00.01 |       1 |       |       |          |
|* 10 |       TABLE ACCESS BY USER ROWID   | T1         |      1 |      1 |      1 |00:00:00.01 |       1 |       |       |          |
|* 11 |     INDEX UNIQUE SCAN              | I_T2_1     |      1 |      1 |      1 |00:00:00.01 |       3 |       |       |          |
|* 12 |    TABLE ACCESS BY INDEX ROWID     | T2         |      1 |      1 |      1 |00:00:00.01 |       1 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------------------

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

   9 - access("MAS$"."XID$$"=:1)
  10 - filter("MAS$"."FLAG"='Y')
  11 - access("JV$"."OID_T2"="MAS$0"."OID")
  12 - filter("MAS$0"."FLAG"='Y')
/* MV_REFRESH (DEL) */
DELETE FROM "DOM"."MV_T1_T2" SNA$
WHERE  "T1_ROWID" IN (SELECT /*+ NO_MERGE  NO_SEMIJOIN  */ *
                      FROM   (SELECT  /*+ CARDINALITY(MAS$ 0) */  CHARTOROWID("MAS$"."M_ROW$$") RID$
                              FROM "DOM"."MLOG$_T1" "MAS$"   WHERE "MAS$".XID$$ = :1 )MAS$)

with plan:

-------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT                |              |      1 |        |      0 |00:00:00.01 |       9 |       |       |          |
|   1 |  DELETE                         | MV_T1_T2     |      1 |        |      0 |00:00:00.01 |       9 |       |       |          |
|   2 |   NESTED LOOPS                  |              |      1 |      1 |      1 |00:00:00.01 |       3 |       |       |          |
|   3 |    VIEW                         | VW_NSO_1     |      1 |      1 |      1 |00:00:00.01 |       2 |       |       |          |
|   4 |     SORT UNIQUE                 |              |      1 |      1 |      1 |00:00:00.01 |       2 | 73728 | 73728 |          |
|   5 |      TABLE ACCESS BY INDEX ROWID| MLOG$_T1     |      1 |      1 |      1 |00:00:00.01 |       2 |       |       |          |
|*  6 |       INDEX RANGE SCAN          | I_MLOG$_T1   |      1 |      2 |      1 |00:00:00.01 |       1 |       |       |          |
|*  7 |    INDEX RANGE SCAN             | I_MV_T1_T2_1 |      1 |      1 |      1 |00:00:00.01 |       1 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------------------

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

   6 - access("MAS$"."XID$$"=:1)
   7 - access("T1_ROWID"="RID$")

There’s no doubt that that is faster for our specific circumstances.

What about the second workaround available?

Let’s reset “_mv_refresh_use_stats” and look at locking stats on the MV logs.

alter session set "_mv_refresh_use_stats" = false;

session SET altered.

begin
  dbms_stats.gather_table_stats(USER,'MLOG$_T1');
  dbms_stats.gather_table_stats(USER,'MLOG$_T2');
  dbms_stats.lock_table_stats(USER,'MLOG$_T1');
  dbms_stats.lock_table_stats(USER,'MLOG$_T2');
end;
/

anonymous block completed

select table_name, num_rows from user_tables where table_name in ('MLOG$_T1','MLOG$_T2');

TABLE_NAME                     NUM_ROWS
------------------------------ --------
MLOG$_T1                              0
MLOG$_T2                              0

What happens then?

update t1 set flag = 'Y' where oid = 1000001;

Elapsed: 00:00:00.026

commit;

committed.
Elapsed: 00:00:00.169

Very similar to the effects of “mv_refresh_use_stats”, but exactly the same? No.

SQL_ID        CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS ELAPSED_TIME/1000/1000 SQL_FULLTEXT
------------- ------------ --------------- ---------- ---------------------- --------------------------------------------------------------------------------
69xq38c4ts0j2            1       190791364          1               0.013053 /* MV_REFRESH (INS) */ INSERT INTO "DOM"."MV_T1_T2" SELECT /*+ NO_MERGE("JV$") *
dzzb43k6h3vhs            0                          1               0.009172 /* MV_REFRESH (DEL) */ DELETE FROM "DOM"."MV_T1_T2" SNA$ WHERE "T1_ROWID" IN (SE
4agmycb29dd1p            0                          1               0.000924 delete from "DOM"."MLOG$_T2" where xid$$ = :1
f8wzsn9dzcusb            0      1745227344          1                0.00064 delete from "DOM"."MLOG$_T1" where xid$$ = :1
ar6vnyxkss2kq            0       375101422          1               0.000632 select dmltype$$ from "DOM"."MLOG$_T1"  where xid$$ = :1  group by dmltype$$

We’ve got completely different SQL IDs again.

In both INSERT and DELETE we’ve lost any hint to do or avoid a semi-join and there’s no CARDINALITY hint.

/* MV_REFRESH (INS) */
INSERT INTO "DOM"."MV_T1_T2"
SELECT /*+ NO_MERGE("JV$") */ "JV$"."RID$","JV$"."OID","JV$"."FLAG","JV$"."OID_T2","JV$"."PADDING","MAS$0".ROWID,"MAS$0"."OID","MAS$0"."FLAG","MAS$0"."PADDING"
FROM ( SELECT "MAS$"."ROWID" "RID$"  ,  "MAS$".*  FROM "DOM"."T1" "MAS$"
       WHERE ROWID IN (SELECT  CHARTOROWID("MAS$"."M_ROW$$") RID$
                       FROM "DOM"."MLOG$_T1" "MAS$"
                       WHERE "MAS$".XID$$ = :1 )) "JV$", "T2" AS OF SNAPSHOT(:B_SCN)  "MAS$0"
WHERE "JV$"."FLAG"='Y' AND "JV$"."OID_T2"="MAS$0"."OID" AND "MAS$0"."FLAG"='Y'
-------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                  |            |      1 |        |      0 |00:00:00.01 |      15 |       |       |          |
|   1 |  LOAD TABLE CONVENTIONAL          |            |      1 |        |      0 |00:00:00.01 |      15 |       |       |          |
|   2 |   NESTED LOOPS                    |            |      1 |        |      1 |00:00:00.01 |       7 |       |       |          |
|   3 |    NESTED LOOPS                   |            |      1 |      1 |      1 |00:00:00.01 |       6 |       |       |          |
|   4 |     VIEW                          |            |      1 |      1 |      1 |00:00:00.01 |       3 |       |       |          |
|   5 |      NESTED LOOPS                 |            |      1 |      1 |      1 |00:00:00.01 |       3 |       |       |          |
|   6 |       SORT UNIQUE                 |            |      1 |      1 |      1 |00:00:00.01 |       2 | 73728 | 73728 |          |
|   7 |        TABLE ACCESS BY INDEX ROWID| MLOG$_T1   |      1 |      1 |      1 |00:00:00.01 |       2 |       |       |          |
|*  8 |         INDEX RANGE SCAN          | I_MLOG$_T1 |      1 |      1 |      1 |00:00:00.01 |       1 |       |       |          |
|*  9 |       TABLE ACCESS BY USER ROWID  | T1         |      1 |      1 |      1 |00:00:00.01 |       1 |       |       |          |
|* 10 |     INDEX UNIQUE SCAN             | I_T2_1     |      1 |      1 |      1 |00:00:00.01 |       3 |       |       |          |
|* 11 |    TABLE ACCESS BY INDEX ROWID    | T2         |      1 |      1 |      1 |00:00:00.01 |       1 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------------------

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

   8 - access("MAS$"."XID$$"=:1)
   9 - filter("MAS$"."FLAG"='Y')
  10 - access("JV$"."OID_T2"="MAS$0"."OID")
  11 - filter("MAS$0"."FLAG"='Y')
DELETE FROM "DOM"."MV_T1_T2" SNA$
WHERE  "T1_ROWID" IN (SELECT /*+ NO_MERGE  */ *
                      FROM (SELECT  CHARTOROWID("MAS$"."M_ROW$$") RID$
                            FROM "DOM"."MLOG$_T1" "MAS$"   WHERE "MAS$".XID$$ = :1 )MAS$)
--------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT              |              |      1 |        |      0 |00:00:00.01 |      13 |
|   1 |  DELETE                       | MV_T1_T2     |      1 |        |      0 |00:00:00.01 |      13 |
|   2 |   NESTED LOOPS SEMI           |              |      1 |    101 |      1 |00:00:00.01 |       5 |
|   3 |    INDEX FULL SCAN            | I_MV_T1_T2_1 |      1 |    101 |    101 |00:00:00.01 |       1 |
|*  4 |    TABLE ACCESS BY INDEX ROWID| MLOG$_T1     |    101 |      1 |      1 |00:00:00.01 |       4 |
|*  5 |     INDEX RANGE SCAN          | I_MLOG$_T1   |    101 |      1 |    101 |00:00:00.01 |       3 |
--------------------------------------------------------------------------------------------------------

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

   4 - filter("T1_ROWID"=CHARTOROWID("MAS$"."M_ROW$$"))
   5 - access("MAS$"."XID$$"=:1)

So, to briefly summarise the above, we have at least three clear codepaths through the fast refresh mechanism which result in completely different internal SQL being generated.
Multiple code paths are added complexity when it comes to combining other features, upgrades, testing, etc

It’s then no great surprise when such multiple codepaths lead to other bugs.

So which approach are we meant to do?

Should we just take the default longer commit on the chin?

I’m not sure.

But I can come up with an illustration of why we should think twice about doing down the “_mv_refresh_use_stats” approach.
Firstly it’s an underscore parameter so we should really get Oracle Support approval before setting it (which means a fair bit of back and forth until you get someone who knows what you’re going on about)


Secondly, we can quickly find some bugs around the usage of this parameter with the newer COMMIT SCN (also covered in Alberto’s blogs above)

drop materialized view log on t1;
drop materialized view log on t2;
create materialized view log on t1 with rowid, commit scn;
create materialized view log on t2 with rowid, commit scn;

drop materialized view mv_t1_t2;
create materialized view mv_t1_t2...

Then go back to our update and commit:

alter session set "_mv_refresh_use_stats" = true;

session SET altered.

update t1 set flag = 'Y' where oid = 1000001;

1 rows updated

commit;

SQL Error: ORA-12008: error in materialized view refresh path
ORA-00904: "SNAPTIME$$": invalid identifier
12008. 00000 -  "error in materialized view refresh path"
*Cause:    Table SNAP$_<mview_name> reads rows from the view
           MVIEW$_<mview_name>, which is a view on the master table
           (the master may be at a remote site).  Any
           error in this path will cause this error at refresh time.
           For fast refreshes, the table <master_owner>.MLOG$_<master>
           is also referenced.
*Action:   Examine the other messages on the stack to find the problem.
           See if the objects SNAP$_<mview_name>, MVIEW$_<mview_name>,
           <mowner>.<master>@<dblink>, <mowner>.MLOG$_<master>@<dblink>
           still exist.

We don’t actually need COMMIT SCN in our ON-COMMIT MV because we’re using the XID.
It’s a newer mechanism for FAST REFRESH ON DEMAND

I know that I don’t need COMMIT SCN in my case above but the fact that it’s so easy to find an issue means that I’m reluctant to do down the parameter route.

Note that there are quite a few bug notes around this in Oracle Support.
A lot of the advice is conflicting, not least because the behaviour of “_mv_refresh_use_stats” changes mid 10.1 something.

At the moment, I think I’m going to go with the locked stats on the MLOGs but it’s worrying.

What a mess!

References:
Lots of Oracle doc ids…

Follow

Get every new post delivered to your Inbox.

Join 84 other followers