When create mview takes forever....

After having real work get in the way for a couple of weeks ( a major 9i->12c is proving 'interesting' at the moment - maybe even worthy of a talk at some point) I've been slacking a little on writing up any blog posts.

So lets make amends for that now with a short post on some mview creation quirks.

I've been working in the past day or so on a browsing system for our configuration database (using Apex). The configuration database is however hosted on SQL Server and i don't want to develop anything directly there for a few reasons:

1. I don't really know sql server (at least from a development point of view)
2. This is easy in Apex
3. I don't really know sql server...

So all i need to do is query SQL Server directly from oracle which I've blogged about before and is dead easy to set up - so i went ahead and did that and now i can access all the SQL Server data directly just using the @remote_sql_db syntax.

OK that's all good but one of the things i want to be able to do in my cmdb browser is build a tree view pane to let me hunt around and navigate for configurations. SQL Server doesn't support this (well it at least doesn't support the Oracle syntax of the connect by prior type statements). I would imagine that it supports that kind of syntax on some way but i didn't want to waste any more time on researching it.

So i can't query the tables directly over the db link and treewalk them - so what do i do?

Well - the obvious solution is just to create an mview which stores the results of the query against SQL Server - now it's an oracle table and i can do what i like with it. It also has the benefit that is doesn't have to run the complex query over the db link many times (data model is horrendous in the sql server db design by the way). It also decouples the system so an outage on SQL Server doesn't prevent the system running.

So it's all good then - i just need to create the mview with the same definition as the view

So lets do that

The view is a view of multiple views i created in oracle to make the remote data model into something remotely usable - so essentially the view text looks like this

select * from business_service
union all
select * from business_applications
union all
select * from RUNNING_SOFTWARE
union all
select * from computers

That runs fine and returns all the rows in 4 seconds.

However when i do this......

create materialized view jj refresh complete start with sysdate next (sysdate+1)
as
(select * from business_service
union all
select * from business_applications
union all
select * from RUNNING_SOFTWARE
union all
select * from computers)

It just hangs......and hangs and actually never comes back (at least not in a reasonable time period).

Hmmmm.....

Lets try the same thing as a CTAS and see what happens

create table jj as
(select * from business_service
union all
select * from business_applications
union all
select * from RUNNING_SOFTWARE
union all
select * from computers)

That creates in 4 seconds - so whats going on here?

Well i can see that the create mview is doing loads of extra work and seem to be constantly reading and writing from temp -it's not executing the query in the same way.

So how do i fix that?

Well i could mess about for hours trying to find out exactly what is going wrong but this time i going to take the easy shortcut and do the following.

I know the CTAS was fine - this is what the create mview will run (but its obvious that its doing it some other way due to a quirk - so i just need to give it a helping hand in the right direction)

So lets gather some outline data from the executions details of the good CTAS and embed those in the create mview statement.

So i rerun the CTAS

create table jj as
(select * from business_service
union all
select * from business_applications
union all
select * from RUNNING_SOFTWARE
union all
select * from computers)

followed directly by

select * from table(dbms_xplan.display_cursor(null,null,'ADVANCED ROWS ALLSTATS LAST')); 

Part of the output of this is a whole series of hints (the 'outline' data) near the bottom of the output

In my case its this selection of delights

Outline Data

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

 

  /*+

      BEGIN_OUTLINE_DATA

      IGNORE_OPTIM_EMBEDDED_HINTS

      OPTIMIZER_FEATURES_ENABLE('12.1.0.2')

      DB_VERSION('12.1.0.2')

      DRIVING_SITE(@"SET$1" "A1")

      ALL_ROWS

      OUTLINE_LEAF(@"SEL$A9F4D2D2")

      OUTER_JOIN_TO_INNER(@"SEL$2" "S"@"SEL$2")

      OUTLINE_LEAF(@"SEL$1")

      OUTLINE_LEAF(@"SEL$07BDC5B4")

      MERGE(@"SEL$4")

      OUTLINE_LEAF(@"SEL$ABDE6DFF")

      MERGE(@"SEL$6")

      OUTLINE_LEAF(@"SEL$8A3193DA")

      MERGE(@"SEL$8")

      OUTLINE_LEAF(@"SET$1")

      OUTLINE(@"SEL$2")

      OUTLINE(@"SEL$3")

      OUTLINE(@"SEL$4")

      OUTLINE(@"SEL$5")

      OUTLINE(@"SEL$6")

      OUTLINE(@"SEL$7")

      OUTLINE(@"SEL$8")

      NO_ACCESS(@"SEL$1" "BUSINESS_SERVICE"@"SEL$1")

      FULL(@"SEL$A9F4D2D2" "O"@"SEL$2")

      FULL(@"SEL$A9F4D2D2" "S"@"SEL$2")

      FULL(@"SEL$A9F4D2D2" "L2"@"SEL$2")

      FULL(@"SEL$A9F4D2D2" "R"@"SEL$2")

      FULL(@"SEL$A9F4D2D2" "L"@"SEL$2")

      LEADING(@"SEL$A9F4D2D2" "O"@"SEL$2" "S"@"SEL$2" "L2"@"SEL$2" "R"@"SEL$2" "L"@"SEL$2")

      USE_HASH(@"SEL$A9F4D2D2" "S"@"SEL$2")

      USE_HASH(@"SEL$A9F4D2D2" "L2"@"SEL$2")

      USE_HASH(@"SEL$A9F4D2D2" "R"@"SEL$2")

      USE_HASH(@"SEL$A9F4D2D2" "L"@"SEL$2")

      PQ_DISTRIBUTE(@"SEL$A9F4D2D2" "L"@"SEL$2" NONE NONE)

      SWAP_JOIN_INPUTS(@"SEL$A9F4D2D2" "S"@"SEL$2")

      SWAP_JOIN_INPUTS(@"SEL$A9F4D2D2" "R"@"SEL$2")

      USE_HASH_AGGREGATION(@"SEL$A9F4D2D2")

      END_OUTLINE_DATA

  */

So if i take these and embed then in the create mview statement like this

create materialized view jq refresh complete start with sysdate next (sysdate+1)

as

(select /*+

      BEGIN_OUTLINE_DATA

      IGNORE_OPTIM_EMBEDDED_HINTS

      OPTIMIZER_FEATURES_ENABLE('12.1.0.2')

      DB_VERSION('12.1.0.2')

      DRIVING_SITE(@"SET$1" "A1")

      ALL_ROWS

      OUTLINE_LEAF(@"SEL$A9F4D2D2")

      OUTER_JOIN_TO_INNER(@"SEL$2" "S"@"SEL$2")

      OUTLINE_LEAF(@"SEL$1")

      OUTLINE_LEAF(@"SEL$07BDC5B4")

      MERGE(@"SEL$4")

      OUTLINE_LEAF(@"SEL$ABDE6DFF")

      MERGE(@"SEL$6")

      OUTLINE_LEAF(@"SEL$8A3193DA")

      MERGE(@"SEL$8")

      OUTLINE_LEAF(@"SET$1")

      OUTLINE(@"SEL$2")

      OUTLINE(@"SEL$3")

      OUTLINE(@"SEL$4")

      OUTLINE(@"SEL$5")

      OUTLINE(@"SEL$6")

      OUTLINE(@"SEL$7")

      OUTLINE(@"SEL$8")

      NO_ACCESS(@"SEL$1" "BUSINESS_SERVICE"@"SEL$1")

      FULL(@"SEL$A9F4D2D2" "O"@"SEL$2")

      FULL(@"SEL$A9F4D2D2" "S"@"SEL$2")

      FULL(@"SEL$A9F4D2D2" "L2"@"SEL$2")

      FULL(@"SEL$A9F4D2D2" "R"@"SEL$2")

      FULL(@"SEL$A9F4D2D2" "L"@"SEL$2")

      LEADING(@"SEL$A9F4D2D2" "O"@"SEL$2" "S"@"SEL$2" "L2"@"SEL$2" "R"@"SEL$2" "L"@"SEL$2")

      USE_HASH(@"SEL$A9F4D2D2" "S"@"SEL$2")

      USE_HASH(@"SEL$A9F4D2D2" "L2"@"SEL$2")

      USE_HASH(@"SEL$A9F4D2D2" "R"@"SEL$2")

      USE_HASH(@"SEL$A9F4D2D2" "L"@"SEL$2")

      PQ_DISTRIBUTE(@"SEL$A9F4D2D2" "L"@"SEL$2" NONE NONE)

      SWAP_JOIN_INPUTS(@"SEL$A9F4D2D2" "S"@"SEL$2")

      SWAP_JOIN_INPUTS(@"SEL$A9F4D2D2" "R"@"SEL$2")

      USE_HASH_AGGREGATION(@"SEL$A9F4D2D2")

      END_OUTLINE_DATA

  */* from business_service

union all

select * from business_applications

union all

select * from RUNNING_SOFTWARE

union all

select * from computers)

Now with those hints in place that oracle created for me it now runs in 4 seconds and i have the solution i wanted.

To be honest I've used MViews (or snapshots as they were in the olden days) and this is the first time i ever had to do this 'fix' - it must some some quirk about the SQL that is causing the optimizer to get things wrong somehow.

Relatively simple workaround though and as this is just a 'utility' system rather than a full blown business app i;m not too bothered that i have all those hardcoded hints in place.