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
Post a Comment