When you fall at the last hurdle......



This week I've upgraded one of our dev databases to 12.1.0.2 (from 11.2.0.2), the upgrade went fine no problems with that. After the upgrade i took the opportunity to have a look at trying to improve some of the SQL performance - i had hoped that maybe 12c would just improve some of the performance with some new tricks but that wasn't to be the case.

So i began looking into a fairly simple statement that was running very regularly and accounting for about 30% of all the work going on.

The statement looked like this

INSERT INTO TRADE_TABLE_temp
        SELECT * FROM TRADE_TABLE
        WHERE pos_del_activity_id IS NULL
        AND (trade_voided = 'Y' OR edit_count > 0);

So pretty simple huh - the source TRADE_TABLE is exactly that just a table - no complexity hidden in a view or something.

When you run that statement 99% of the time it returns 0 rows with occasionally just a handful. It takes about 10 seconds though as the table has quite a few million rows in it.

The current plan is a FTS of the TRADE_TABLE so surely all that is required is an index on whatever is the most selective column(s) in the query and we're done?

Well - it's not that simple unfortunately - there is an unusual data distribution going on here.

A lot of the rows in the table have a pos_del_activity_id of null, a relatively small number have trade_voided set to 'Y' and a 'medium' number have edit_count>0.

So individually there is nothing to really tie down the rows we want returned - the combination however is very unique. Unfortunately there does not seem to be an efficient way to index for this - any combinations i came up with even making use of extended stats on combinations of columns or splitting the SQL into a union all instead of having the OR statement didn't seem to really help.

Then i thought about it again - the data is not changing much (but enough to rule out result cache) - an mview could really help here - lets try that approach.

So i create an mview log on the source table to track changes as i want on an on commit refresh to happen here.

SQL> create materialized view log on schemaname.TRADE_TABLE;

Materialized view log created.

Then i create an mview

SQL> create materialized view schemaname.rich_mview refresh fast on commit as
  2   select * from schemaname.TRADE_TABLE WHERE POS_DEL_ACTIVITY_ID IS NULL AND (TRADE_VOIDED = 'Y' OR EDIT_COUNT > 0)
  3  /

Materialized view created.

Then i try the query again expecting great things and the mview is ignored......

Lets find out why

First i create the rewrite table to enable this

SQL> @?/rdbms/admin/utlxrw.sql

Table created.

Then i run the routine to tell me what is wrong

SQL> DECLARE
 qrytxt VARCHAR2(1000) := 'select * from schemaname.TRADE_TABLE WHERE POS_DEL_ACTIVITY_ID IS NULL AND (TRADE_VOIDED = ''Y'' OR EDIT_COUNT > 0)';
BEGIN
 dbms_mview.Explain_Rewrite(qrytxt,'schemaname.RICH_MVIEW','ID1');
END;
/  2    3    4    5    6

PL/SQL procedure successfully completed.

Then i find the result

SQL> select * from rewrite_table;

QSM-01026: query rewrite is disabled for, RICH_MVIEW  

Seriously - you have to be joking - why isn't this the default! aargh!

So i run it again with the additional clause

create materialized view schemaname.rich_mview refresh fast on commit enable query rewrite as
select * from schemaname.TRADE_TABLE WHERE POS_DEL_ACTIVITY_ID IS NULL AND (TRADE_VOIDED = 'Y' OR EDIT_COUNT > 0)
/

Now when i run the query i see this

SQL>  select * from schemaname.TRADE_TABLE WHERE POS_DEL_ACTIVITY_ID IS NULL AND (TRADE_VOIDED = 'Y' OR EDIT_COUNT > 0)
  2  /

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 4214214500

-------------------------------------------------------------------------------------------
| Id  | Operation                    | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |            |     1 |   268 |     2   (0)| 00:00:01 |
|   1 |  MAT_VIEW REWRITE ACCESS FULL| RICH_MVIEW |     1 |   268 |     2   (0)| 00:00:01 |

Yes! - high fives all round - it works.

Now i should see the batch job massively improve - right?

Wrong :-(

The job ran exactly the same way and completely ignored the mview.

I started to then see if there was anything different about the optimizer/session that was running in the context of the batch job - but there was nothing.

So i was a little confused for a while until i started to look at the PLSQL inside the batch routine - that's when it hit me......

The source table is modified during the earlier stages of the batch and not committed - so the mview is not refreshed and cannot be used as we need a read consistent view of the table.

This can be illustrated by this simple example below

SQL> update TRADE_TABLE set BOOK_ATTR_6='X' where rownum <2;

1 row updated.

At this point the view is not refreshed - so if i run  a statement tat culd in theory get rewritten -it can;t

SQL>  INSERT INTO TRADE_TABLE_temp
        SELECT * FROM TRADE_TABLE
        WHERE pos_del_activity_id IS NULL
        AND (trade_voided = 'Y' OR edit_count > 0);  2    3    4

0 rows created.


Execution Plan
----------------------------------------------------------
Plan hash value: 2310204777

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

| Id  | Operation                | Name            | Rows  | Bytes | Cost (%CPU)
| Time     |

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

|   0 | INSERT STATEMENT         |                 |   115K|    16M|  6944   (1)
| 00:00:01 |

|   1 |  LOAD TABLE CONVENTIONAL | TRADE_TABLE_TEMP |       |       |
|          |

|*  2 |   TABLE ACCESS FULL      | TRADE_TABLE      |   115K|    16M|  6944   (1)
| 00:00:01 |

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


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

   2 - filter(("EDIT_COUNT">0 OR "TRADE_VOIDED"='Y') AND "POS_DEL_ACTIVITY_ID" I
S

              NULL)

So it;s back to the drawing board - so close to victory.......... An interesting investigation nonetheless



Comments