Now that I have built the setup I would like to demonstrate that if the name of the TEMP table transformation

SYS_TEMP_0FD9D6668_32BBFD

changes it will not prevent both the SQL Profile and the SPM baseline from constraining the initial query to stick with the fixed execution plan. Before going further I would like to get the PHV2 of the above execution plan:

which, as expected, has not prevented both the SPM and the SQL Profile from being used. Furthermore, despite the TEMP table name change, the PHV2 of the generated plan is still identical to the first one mentioned above:

Bottom Line : don’t worry about your materialized Common Table Expression used in critical queries that you want to stabilize via a SQL profile or a SQL Plan Baseline. Their changing name will not affect these two execution plan stabilizing techniques.

April 18, 2015

I have been asked to troubleshoot a monthly on demand materialized view refresh job which has got the bad idea to crash with the ORA-01555 error after 25,833 seconds (more than 7 hours) of execution. Despite my several years of professional experience this is the first time I have been asked to look at a materialized view refresh. This issue came up Friday afternoon so I was given a week-end to familiarize myself with materialized views. Coincidentally a couple of days before there was an Oracle webcast on Materialized view basics, architecture and internal working which I have replayed on Saturday and practiced its demo. Christian Antognini book contains a chapter on this topic which I have also gone through as far as Christian book is from where I always like to start when trying to learn an Oracle concept.

Materialized view capabilities

The following Monday morning, armed with this week-end accelerated auto-training, I opened again the e-mail I have been sent about the failing refresh job and started re-reading it. The first thing that has retained my attention this time, in contrast to my last Friday quick pass through reading, was a suggestion made by the DBA to try fast refreshing the materialized view instead of completely refreshing it. I learnt from the Oracle webcast that Oracle is able to let us know wether a materialized view can be fast (also know as incremental) refreshed or not. Here below the steps to do if you want to get this information:

You need first to create the mv_capabilities_table table (in the schema you are going to use against it the dbms_mview package) using the following script :

As spotted above, fast refreshing this materialized view is impossible.

The first learned lesson: instead of trying the create a materialized view log and fast refreshing a complex materialized view which might be impossible to be refreshed incrementally, try first getting the capabilities of the view using the explain_mview procedure. You will certainly save time and resource.

Have you noticed that parallel 16 clause in the materialized view create script? The developer intention was to create the materialized view using parallel process. Having a Production equivalent database I was happy enough to try re-creating this materialized view:

Surprisingly the materialized view has been created in less than 23 minutes. And this creation has been parallelised with a DOP of 16 as shown by the corresponding Real Time Sql Monitoring report (RTSM).The master table has been henceforth created with a DOP of 16 as shown below:

You might have already pointed out in the above RTSM report that the select part of the “create as select” statement has been parallelised as well. It is as if the parallel 16 clause of the “create” part of the SQL materialized view script induced implicitly its “select” part to be done in parallel with a DOP of 16.

Parallel clause in the SQL create statement : any effect on the mview refresh ?

As far as I am concerned, the problem I have been asked to trouble shoot resides in refreshing the materialized view and not in creating it. Since, the materialized view has been created in 23 minutes, I should be optimistic for its refresh time; isn’t it?

In contrast to the creation process, the materialized view refresh has been done serially. This confirms that the above parallel 16 clause in the create DDL script concerns only the parallel materialized view creation and not its refresh process.

The second learned lesson : I think that a parallel clause specified in the create statement of a materialized view is not used during the refresh of the same materialized view. The parallel run is considered in this kind of situations only at the materialized view creation time.

dbms_mview.refresh and its parallelism parameter : any effect on the mview refresh ?

The tables on which the materialized view is based have all a degree = 1

I’ve added the pq_distribute (tab1 hash hash) hint above because several refreshes crashed because of the broadcast distribution that ended up by overconsuming TEMP space raising the now classical error:

The fourth learned lesson: if you want to parallelise your materialized view refresh process you had better to include the parallel hint in the select part of the materialized view. This is better than to change the parallel degree of the tables on which the materialized view is based on.

“Books to the ceiling, Books to the sky, My pile of books is a mile high. How I love them! How I need them! I'll have a long beard by the time I read them”—Lobel, Arnold. Whiskers and Rhymes. William Morrow & Co, 1988.