This discussion is archived
6 Replies Latest reply: Mar 28, 2013 6:46 AM by riedelme RSS

Issue with creating materialized view

998977 Newbie
Currently Being Moderated
Hi,

We have a select query (containing joins, aggregates and UNION ALL’s) using which we are creating materialized views. We were able to create these mat views in development environment, however when tried to run the same scripts in a higher environment the creation never completes. The higher environment has three times more data than in Dev currently.

The below operations complete well in time , but when we add “CREATE MATERIALIZED VIEW MAT_VIEW_NAME” to the select query it takes forever (we have cancelled the operation after waiting for more 1 hour)

Select count(1) from the complete Mat. View query -     takes 3.2 min to complete - the query resullts in      3,010,068 rows
Create Normal VIEW using complete Mat. View select query -     takes      3.06 sec     to complete
Create table using complete Mat. View select query     takes 5.75 min to complete - the query resullts in           3,010,068

Does anyone have an idea why this could be happening ? if you have ever faced this kind of issue, can you please provide pointers on how you were able to solve the problem. We are using Oracle 11g.

Let me know if I have to provide any other information for you to understand the issue better.

Thanks
  • 1. Re: Issue with creating materialized view
    riedelme Expert
    Currently Being Moderated
    user10367248 wrote:
    Hi,

    We have a select query (containing joins, aggregates and UNION ALL’s) using which we are creating materialized views. We were able to create these mat views in development environment, however when tried to run the same scripts in a higher environment the creation never completes. The higher environment has three times more data than in Dev currently.

    The below operations complete well in time , but when we add “CREATE MATERIALIZED VIEW MAT_VIEW_NAME” to the select query it takes forever (we have cancelled the operation after waiting for more 1 hour)

    Select count(1) from the complete Mat. View query -     takes 3.2 min to complete - the query resullts in      3,010,068 rows
    The count will be a different query. Oracle can simplify the select process when no columns are required.
    Create Normal VIEW using complete Mat. View select query -     takes      3.06 sec     to complete
    Views don't store anything. Under normal circumstances creating a view should always be pretty fast. Selecting from the view (using it) may be a different matter ...
    Create table using complete Mat. View select query     takes 5.75 min to complete - the query resullts in           3,010,068
    Since you said it never completes in production the time you listed is in development?

    >
    Does anyone have an idea why this could be happening ? if you have ever faced this kind of issue, can you please provide pointers on how you were able to solve the problem. We are using Oracle 11g.
    Post the create statement and execution plans from the underlying SQL from both environments.
  • 2. Re: Issue with creating materialized view
    998977 Newbie
    Currently Being Moderated
    riedelme wrote:
    user10367248 wrote:
    Hi,

    We have a select query (containing joins, aggregates and UNION ALL’s) using which we are creating materialized views. We were able to create these mat views in development environment, however when tried to run the same scripts in a higher environment the creation never completes. The higher environment has three times more data than in Dev currently.

    The below operations complete well in time , but when we add “CREATE MATERIALIZED VIEW MAT_VIEW_NAME” to the select query it takes forever (we have cancelled the operation after waiting for more 1 hour)

    Select count(1) from the complete Mat. View query -     takes 3.2 min to complete - the query resullts in      3,010,068 rows
    The count will be a different query. Oracle can simplify the select process when no columns are required.
    Create Normal VIEW using complete Mat. View select query -     takes      3.06 sec     to complete
    Views don't store anything. Under normal circumstances creating a view should always be pretty fast. Selecting from the view (using it) may be a different matter ...
    Create table using complete Mat. View select query     takes 5.75 min to complete - the query resullts in           3,010,068
    Since you said it never completes in production the time you listed is in development?
    no.. all the query execution times I have mentioned is from the enviornment we are trying to create the mat views. The create table, select queries takes above mentioned times to execute in our QA, but mat views doesn't get created
    >
    >>
    Does anyone have an idea why this could be happening ? if you have ever faced this kind of issue, can you please provide pointers on how you were able to solve the problem. We are using Oracle 11g.
    Post the create statement and execution plans from the underlying SQL from both environments.
    its a real real huge create statement, the select has 12 UNION ALL's, also its a compliance issue for me to post the script :(
  • 3. Re: Issue with creating materialized view
    rp0428 Guru
    Currently Being Moderated
    >
    its a real real huge create statement, the select has 12 UNION ALL's, also its a compliance issue for me to post the script
    >
    Well we can't debug what we can't see.

    So if you can't post the DDL for the CREATE statement there is nothing for us to look at.

    Modify your code to reproduce the problem and eliminate the compliance issue and then post it.
  • 4. Re: Issue with creating materialized view
    998977 Newbie
    Currently Being Moderated
    SELECT vis.uid, findet.yr, findet.ect, vis.ind,
    tm_view.col1_id, tm_view.col1_name,
    tm_view.col2_id, tm_view.col2_name,
    tm_view.col3_id, tm_view.col3_name,
    clnt.cl_id, clnt.cl_nm,
    prodparent_view.parent_cd,
    prodparent_view.parent_desc,
    prod_view.parent_cd,
    prod_view.parent_desc,
    prod_view.child_cd,
    prod_view.child_desc,
    SUM (value1), SUM (value2),
    SUM (value3), SUM (value4),
    SUM (value5), SUM (value6),
    SUM (value7), SUM (value8),
    SUM (value9), SUM (value10),
    SUM (value11), SUM (value12)
    FROM vis,
    (SELECT *
    FROM analytic_e,
    (SELECT table_val
    FROM TAB_CHECK s
    WHERE s.tgt_table_nm = 'ANALYTIC'
    AND s.table_val = 'ANALYTIC_E')
    WHERE table_val = 'ANALYTIC_E'
    UNION ALL
    SELECT *
    FROM analytic_o,
    (SELECT switch_val
    FROM tab_check s
    WHERE s.tgt_table_nm = 'ANALYTIC'
    AND s.switch_val = 'ANALYTIC_O')
    WHERE switch_val = 'ANALYTIC_O') findet,
    prod_view,
    prodparent_view,
    tm_view,
    clnt,
    (select to_number(to_char(ref_dt,'yyyy'))-1 year_agg from DATE_TABLE) tbabt
    WHERE tbabt.yr = findet.yr
    AND vis.cl_key = findet.cl_key
    AND tm_view.hi_key = findet.hi_key
    AND prod_view.child_cd = findet.prod_cd
    AND clnt.cl_key = findet.cl_key
    AND prodparent_view.child_cd = prod_view.parent_cd
    GROUP BY vis.uid, findet.yr, findet.ect, vis.ind,
    tm_view.col1_id, tm_view.col1_name,
    tm_view.col2_id, tm_view.col2_name,
    tm_view.col3_id, tm_view.col3_name,
    clnt.cl_id, clnt.cl_nm,
    prodparent_view.parent_cd
    prodparent_view.parent_desc
    prod_view.parent_cd
    prod_view.parent_desc
    prod_view.child_cd
    prod_view.child_desc


    Higher Environment

    ------------------------------------------------------------------------------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
    ------------------------------------------------------------------------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 57M| 65G| | 20M (1)| 66:48:28 | | |
    | 1 | HASH GROUP BY | | 57M| 65G| 73G| 20M (1)| 66:48:28 | | |
    |* 2 | HASH JOIN | | 57M| 65G| | 109K (2)| 00:21:58 | | |
    | 3 | TABLE ACCESS BY INDEX ROWID | HIER | 2100 | 244K| | 172 (0)| 00:00:03 | | |
    |* 4 | INDEX RANGE SCAN | UK_HIER | 2100 | | | 16 (0)| 00:00:01 | | |
    |* 5 | HASH JOIN | | 57M| 59G| | 109K (1)| 00:21:52 | | |
    | 6 | VIEW | VW_GBF_25 | 1908 | 868K| | 2612 (1)| 00:00:32 | | |
    | 7 | HASH GROUP BY | | 1908 | 141K| | 2612 (1)| 00:00:32 | | |
    | 8 | VIEW | | 45107 | 3347K| | 2609 (1)| 00:00:32 | | |
    | 9 | UNION-ALL | | | | | | | | |
    | 10 | HASH UNIQUE | | 22518 | 1473K| 1872K| 1010 (1)| 00:00:13 | | |
    |* 11 | TABLE ACCESS FULL | HIER | 22518 | 1473K| | 650 (1)| 00:00:08 | | |
    | 12 | HASH UNIQUE | | 22518 | 1165K| 1512K| 947 (1)| 00:00:12 | | |
    |* 13 | TABLE ACCESS FULL | HIER | 22518 | 1165K| | 650 (1)| 00:00:08 | | |
    | 14 | HASH UNIQUE | | 71 | 1917 | | 652 (1)| 00:00:08 | | |
    |* 15 | TABLE ACCESS FULL | HIER | 22518 | 593K| | 650 (1)| 00:00:08 | | |
    |* 16 | HASH JOIN | | 64M| 38G| 4936K| 106K (1)| 00:21:16 | | |
    | 17 | VIEW | | 45107 | 4404K| | 2609 (1)| 00:00:32 | | |
    | 18 | UNION-ALL | | | | | | | | |
    | 19 | HASH UNIQUE | | 22518 | 1473K| 1872K| 1010 (1)| 00:00:13 | | |
    |* 20 | TABLE ACCESS FULL | HIER | 22518 | 1473K| | 650 (1)| 00:00:08 | | |
    | 21 | HASH UNIQUE | | 22518 | 1165K| 1512K| 947 (1)| 00:00:12 | | |
    |* 22 | TABLE ACCESS FULL | HIER | 22518 | 1165K| | 650 (1)| 00:00:08 | | |
    | 23 | HASH UNIQUE | | 71 | 1917 | | 652 (1)| 00:00:08 | | |
    |* 24 | TABLE ACCESS FULL | HIER | 22518 | 593K| | 650 (1)| 00:00:08 | | |
    |* 25 | HASH JOIN | | 3021K| 1550M| 15M| 24492 (1)| 00:04:54 | | |
    | 26 | PARTITION HASH ALL | | 491K| 10M| | 1059 (1)| 00:00:13 | 1 | 16 |
    | 27 | MAT_VIEW ACCESS FULL | VIS | 491K| 10M| | 1059 (1)| 00:00:13 | 1 | 16 |
    |* 28 | HASH JOIN | | 388K| 190M| 6056K| 12929 (1)| 00:02:36 | | |
    | 29 | TABLE ACCESS FULL | CLNT | 64540 | 5294K| | 411 (1)| 00:00:05 | | |
    |* 30 | HASH JOIN | | 388K| 159M| | 4072 (1)| 00:00:49 | | |
    | 31 | TABLE ACCESS FULL | DATE_TABLE | 2 | 16 | | 3 (0)| 00:00:01 | | |
    | 32 | VIEW | | 582K| 235M| | 4065 (1)| 00:00:49 | | |
    | 33 | UNION-ALL | | | | | | | | |
    | 34 | NESTED LOOPS | | 272K| 52M| | 1860 (1)| 00:00:23 | | |
    |* 35 | TABLE ACCESS BY INDEX ROWID| TAB_CHECK | 1 | 46 | | 1 (0)| 00:00:01 | | |
    |* 36 | INDEX UNIQUE SCAN | SYS_C0041157 | 1 | | | 0 (0)| 00:00:01 | | |
    | 37 | PARTITION RANGE ALL | | 272K| 40M| | 1859 (1)| 00:00:23 | 1 |1048575|
    | 38 | TABLE ACCESS FULL | ANALYTIC_E | 272K| 40M| | 1859 (1)| 00:00:23 | 1 |1048575|
    | 39 | NESTED LOOPS | | 309K| 58M| | 2205 (1)| 00:00:27 | | |
    |* 40 | TABLE ACCESS BY INDEX ROWID| TAB_CHECK | 1 | 46 | | 1 (0)| 00:00:01 | | |
    |* 41 | INDEX UNIQUE SCAN | SYS_C0041157 | 1 | | | 0 (0)| 00:00:01 | | |
    | 42 | PARTITION RANGE ALL | | 309K| 44M| | 2204 (1)| 00:00:27 | 1 |1048575|
    | 43 | TABLE ACCESS FULL | ANALYTIC_O | 309K| 44M| | 2204 (1)| 00:00:27 | 1 |1048575|
    ------------------------------------------------------------------------------------------------------------------------------------------------


    Development
    -----------------------------------------------------------------------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
    -----------------------------------------------------------------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 1696K| 1276M| | 291K (1)| 00:58:20 | | |
    | 1 | HASH GROUP BY | | 1696K| 1276M| 1325M| 291K (1)| 00:58:20 | | |
    |* 2 | HASH JOIN | | 1696K| 1276M| | 9721 (2)| 00:01:57 | | |
    | 3 | VIEW | | 15464 | 1132K| | 1855 (1)| 00:00:23 | | |
    | 4 | UNION-ALL | | | | | | | | |
    | 5 | HASH UNIQUE | | 7681 | 502K| | 618 (1)| 00:00:08 | | |
    |* 6 | TABLE ACCESS FULL | HIER | 7681 | 502K| | 617 (1)| 00:00:08 | | |
    | 7 | HASH UNIQUE | | 7681 | 375K| | 618 (1)| 00:00:08 | | |
    |* 8 | TABLE ACCESS FULL | HIER | 7681 | 375K| | 617 (1)| 00:00:08 | | |
    | 9 | HASH UNIQUE | | 102 | 2448 | | 618 (1)| 00:00:08 | | |
    |* 10 | TABLE ACCESS FULL | HIER | 7681 | 180K| | 617 (1)| 00:00:08 | | |
    |* 11 | HASH JOIN | | 371K| 252M| | 7847 (2)| 00:01:35 | | |
    | 12 | VIEW | | 15464 | 1510K| | 1855 (1)| 00:00:23 | | |
    | 13 | UNION-ALL | | | | | | | | |
    | 14 | HASH UNIQUE | | 7681 | 502K| | 618 (1)| 00:00:08 | | |
    |* 15 | TABLE ACCESS FULL | HIER | 7681 | 502K| | 617 (1)| 00:00:08 | | |
    | 16 | HASH UNIQUE | | 7681 | 375K| | 618 (1)| 00:00:08 | | |
    |* 17 | TABLE ACCESS FULL | HIER | 7681 | 375K| | 617 (1)| 00:00:08 | | |
    | 18 | HASH UNIQUE | | 102 | 2448 | | 618 (1)| 00:00:08 | | |
    |* 19 | TABLE ACCESS FULL | HIER | 7681 | 180K| | 617 (1)| 00:00:08 | | |
    |* 20 | HASH JOIN | | 122K| 71M| | 5987 (2)| 00:01:12 | | |
    |* 21 | TABLE ACCESS FULL | HIER | 7681 | 915K| | 617 (1)| 00:00:08 | | |
    |* 22 | HASH JOIN | | 122K| 57M| 4512K| 5368 (2)| 00:01:05 | | |
    |* 23 | HASH JOIN | | 9556 | 4395K| 3856K| 2409 (2)| 00:00:29 | | |
    | 24 | TABLE ACCESS FULL | CLNT | 74426 | 2979K| | 310 (1)| 00:00:04 | | |
    |* 25 | HASH JOIN | | 9556 | 4012K| | 1710 (2)| 00:00:21 | | |
    | 26 | TABLE ACCESS FULL | DATE_TABLE | 1 | 7 | | 3 (0)| 00:00:01 | | |
    | 27 | VIEW | | 19112 | 7894K| | 1706 (2)| 00:00:21 | | |
    | 28 | UNION-ALL | | | | | | | | |
    | 29 | MERGE JOIN CARTESIAN | | 19111 | 4068K| | 1701 (2)| 00:00:21 | | |
    |* 30 | TABLE ACCESS FULL | TAB_CHECK | 1 | 49 | | 3 (0)| 00:00:01 | | |
    | 31 | BUFFER SORT | | 248K| 40M| | 1698 (2)| 00:00:21 | | |
    | 32 | PARTITION RANGE ALL| | 248K| 40M| | 1698 (2)| 00:00:21 | 1 |1048575|
    | 33 | TABLE ACCESS FULL | ANALYTIC_E | 248K| 40M| | 1698 (2)| 00:00:21 | 1 |1048575|
    | 34 | MERGE JOIN CARTESIAN | | 1 | 537 | | 5 (0)| 00:00:01 | | |
    | 35 | PARTITION RANGE ALL | | 1 | 488 | | 2 (0)| 00:00:01 | 1 |1048575|
    | 36 | TABLE ACCESS FULL | ANALYTIC_O | 1 | 488 | | 2 (0)| 00:00:01 | 1 |1048575|
    | 37 | BUFFER SORT | | 1 | 49 | | 3 (0)| 00:00:01 | | |
    |* 38 | TABLE ACCESS FULL | TAB_CHECK | 1 | 49 | | 3 (0)| 00:00:01 | | |
    | 39 | PARTITION HASH ALL | | 810K| 16M| | 1456 (2)| 00:00:18 | 1 | 16 |
    | 40 | MAT_VIEW ACCESS FULL | VIS | 810K| 16M| | 1456 (2)| 00:00:18 | 1 | 16 |
    -----------------------------------------------------------------------------------------------------------------------------------------
  • 5. Re: Issue with creating materialized view
    998977 Newbie
    Currently Being Moderated
    Hi,

    Above is how the query and plans looks like, i have mimicked only one query from the 13 queries involved in creating the mat view. All the other 12 queries would look similar to this.please see if you can help.

    Thanks

    Edited by: user10367248 on Mar 28, 2013 3:08 AM
  • 6. Re: Issue with creating materialized view
    riedelme Expert
    Currently Being Moderated
    The code and plans posted were helfpul but a bit hard to read with the normal proportional font. Can you edit the post to include the [ code ] and [ code ] tags (the spaces were included here so they would render and should not be used because actually using them would look like
    this
    ) to use a fixed-width font to make them easier to read? You can check this with the Preview tab above the edit field.

    Working with what we have ...

    GROUP BY query with lots of inline views. Inline views of inline views. Scalar subqueries. View joins. Uniions. All of these add complexity to SQL and are tuning considerations.

    Different plans on each system = 20M vs. 291K with smaller bytes and rows on the development system. The magnitude different of the cost and bytes between systems suggests data volume as a contributing factor to the difference; at least the optimizer is determining that a LOT more work needs to be done on production than development. The MERGE JOIN CARTESIAN in development was scary but happily did not appear in the production plan. Oracle will sometimes to MERGE JOIN CARTESIAN operations even when joins are specified correctly as an optimization technique; I always find it useful to investigate this operation (but its not in the production plan so your joins are probably okay).

    Both systems are doing a lot of PARTION RANGE ALL operations and could probably benefit by partition joins - partition the tables to be joined by the same keys so partition elimination ("pruning") can occur. This is probably not a feasable short-term solution.

    Another if problematic possiblity is global indexes on the partitioned tables if they do not already exist. You are doing cross-partition lookups anyway. Global partion indexes can help performance when reading data from multiple partitions (what they are for) but can be difficult to work with.

    Can the SQL be simplified to change the scalar subqueries and inline views to joins which may/may not help performance?

    You are doing a lot of full table scans. Could running portions of the SQL in parallel help? This idea conflicts with the global indexes.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points