OraFAQ Forum: Performance Tuning » Tune the materialized view

Home » RDBMS Server » Performance Tuning » Tune the materialized view (10.2.4.0)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Tune the materialized view [message #570758]

Thu, 15 November 2012 07:06

grpatwari
Messages: 288
Registered: June 2008
Location: Hyderabad

Senior Member

Hi,

I have one materialized view and it is taking nearly 2 1/2 hrs and I want to tune the below query.

create materialized view WMV_CFS_CHILD
build deferred
refresh force on demand
as
select tt_cfs_assoc_cfs.fk_to_cfs as ALL_CFS_ID,
CONNECT_BY_ROOT tt_cfs_assoc_cfs.fk_from_cfs as CFS_BBACCESS_ID
from tt_cfs_assoc_cfs
CONNECT BY NOCYCLE tt_cfs_assoc_cfs.fk_from_cfs = PRIOR tt_cfs_assoc_cfs.fk_to_cfs
START WITH tt_cfs_assoc_cfs.fk_from_cfs IN (
select tt_cfs_realise_rfs.fk_from_cfs AS CFS
from (select tte.fk_from_rfs
from tt_rfs_uses_ethlifc tte
union
select tta.fk_from_rfs
from tt_rfs_uses_atmlifc tta
union
select ttp.fk_from_rfs
from tt_rfs_uses_poltifc ttp
) tt_rfs_lifc,
tt_cfs_realise_rfs
where tt_rfs_lifc.fk_from_rfs = tt_cfs_realise_rfs.fk_to_rfs)
UNION
select tt_cfs_realise_rfs.fk_from_cfs as ALL_CFS_ID,
tt_cfs_realise_rfs.fk_from_cfs as CFS_BBACCESS_ID
from (select tte.fk_from_rfs
from tt_rfs_uses_ethlifc tte
union
select tta.fk_from_rfs
from tt_rfs_uses_atmlifc tta
union
select ttp.fk_from_rfs
from tt_rfs_uses_poltifc ttp
) tt_rfs_lifc,
tt_cfs_realise_rfs
where tt_rfs_lifc.fk_from_rfs = tt_cfs_realise_rfs.fk_to_rfs;

I am attaching the execution plan.

Please advice.

Attachment: execution_plan.jpg
(Size: 768.05KB, Downloaded 1272 times)

Report message to a moderator

Re: Tune the materialized view [message #570761 is a reply to message #570758]

Thu, 15 November 2012 07:13

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel

Report message to a moderator

Re: Tune the materialized view [message #570762 is a reply to message #570761]

Thu, 15 November 2012 07:38

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

And get the explain plan from sqlplus and post it here as text like this:

SQL> explain plan for select * from dual;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3543395131

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

8 rows selected.

SQL>

No one is likely to read the image and it has a scrollbar - so not all of the plan is displayed.

Report message to a moderator

Re: Tune the materialized view [message #571018 is a reply to message #570762]

Tue, 20 November 2012 03:19

grpatwari
Messages: 288
Registered: June 2008
Location: Hyderabad

Senior Member

Attached explain plan in sql format and this query is taking 2 1/2 hrs.

Please advice.

Attachment: explain_plan.sql
(Size: 3.44KB, Downloaded 1625 times)

Report message to a moderator

Re: Tune the materialized view [message #571020 is a reply to message #571018]

Tue, 20 November 2012 03:55

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Do it ONLINE and in text like cookiemonster showed you.
Carefully read my previous post and the links it contains.

Regards
Michel

Report message to a moderator

Re: Tune the materialized view [message #571025 is a reply to message #571020]

Tue, 20 November 2012 04:26

grpatwari
Messages: 288
Registered: June 2008
Location: Hyderabad

Senior Member

I could not able to indent the plan table output. Please advice for indentation.

Report message to a moderator

Re: Tune the materialized view [message #571028 is a reply to message #571025]

Tue, 20 November 2012 05:03

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Michel Cadot wrote on Tue, 20 November 2012 08:04

Michel Cadot wrote on Mon, 19 November 2012 13:07
Michel Cadot wrote on Mon, 19 November 2012 12:20
Michel Cadot wrote on Mon, 19 November 2012 08:21
Michel Cadot wrote on Fri, 16 November 2012 15:38
With any SQL or PL/SQL question, please, Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

And from one of your previous topics:

Michel Cadot wrote on Mon, 16 July 2012 09:29
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify....

Regards
Michel

If there is anything you don't understand in this, please tell us.

Regards
Michel

Michel Cadot wrote on Tue, 20 November 2012 10:54

So you didn't read the link. Do you understand what is the "code tag"?
You have a "Test" forum at the bottom of forum main page to practice.

Regards
Michel

Maaher wrote on Tue, 20 November 2012 11:25

...
Edit: please do read Michel's reply. Use [code] tags to get a clean code sample. Provide create table and insert statements so we all have the same starting point. You can use the "Test" forum to check whether the post layout is ok.

[Updated on: Tue, 20 November 2012 05:05]

Report message to a moderator

Re: Tune the materialized view [message #571072 is a reply to message #571028]

Wed, 21 November 2012 00:20

grpatwari
Messages: 288
Registered: June 2008
Location: Hyderabad

Senior Member

SQL> explain plan for
    select tt_cfs_assoc_cfs.fk_to_cfs as ALL_CFS_ID,
           CONNECT_BY_ROOT tt_cfs_assoc_cfs.fk_from_cfs as CFS_BBACCESS_ID
    from tt_cfs_assoc_cfs
    CONNECT BY NOCYCLE tt_cfs_assoc_cfs.fk_from_cfs = PRIOR tt_cfs_assoc_cfs.fk_to_cfs
    START WITH tt_cfs_assoc_cfs.fk_from_cfs IN (
                  select tt_cfs_realise_rfs.fk_from_cfs AS CFS
                    from (select tte.fk_from_rfs
                          from tt_rfs_uses_ethlifc tte
                         union
                         select tta.fk_from_rfs
                         from  tt_rfs_uses_atmlifc tta
                         union
                         select ttp.fk_from_rfs
                         from  tt_rfs_uses_poltifc ttp
                         ) tt_rfs_lifc,
                        tt_cfs_realise_rfs
                  where tt_rfs_lifc.fk_from_rfs = tt_cfs_realise_rfs.fk_to_rfs)
   UNION
   select tt_cfs_realise_rfs.fk_from_cfs as ALL_CFS_ID,
          tt_cfs_realise_rfs.fk_from_cfs as CFS_BBACCESS_ID
   from (select tte.fk_from_rfs
                   from tt_rfs_uses_ethlifc tte
                   union
                   select tta.fk_from_rfs
                   from  tt_rfs_uses_atmlifc tta
                   union
                   select ttp.fk_from_rfs
                   from  tt_rfs_uses_poltifc ttp
                   ) tt_rfs_lifc,
                  tt_cfs_realise_rfs
   where tt_rfs_lifc.fk_from_rfs = tt_cfs_realise_rfs.fk_to_rfs;
 
Explained

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1303356951
--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name               | Rows  | Bytes |	TempSpc	|Cost (%CPU)	|Time		|
--------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                    |  1583K|    31M|		|    29441 (100)|      00:05:54	|
|   1 |  SORT UNIQUE                   |                    |  1583K|    31M|	     85M|    29441 (100)|      00:05:54	|
|   2 |   UNION-ALL                    |                    |       |       |		|		|		|
|*  3 |    CONNECT BY WITH FILTERING   |                    |       |       |		|		|		|
|   4 |     TABLE ACCESS BY INDEX ROWID| TT_CFS_ASSOC_CFS   |       |       |		|		|		|
|*  5 |      HASH JOIN                 |                    |  7837K|   209M|        49M|      59325 (9)|      00:11:52	|
|   6 |       NESTED LOOPS             |                    |  1583K|    31M|      	|     18536 (13)|      00:03:43 |
|   7 |        TABLE ACCESS FULL       | TT_CFS_REALISE_RFS |    11M|   149M|		|     17275  (7)|      00:03:28 |
|   8 |        VIEW                    |                    |     1 |     7 |       	|     1      (0)|      00:00:01 |
|   9 |         SORT UNIQUE            |                    |       |       |       	|            	|          	|
|  10 |          UNION-ALL PARTITION   |                    |       |       |      	|            	|          	|
|* 11 |           INDEX RANGE SCAN     | SYS_C0050935       |     1 |     7 |      	|     1   (0)	|      00:00:01	|
|* 12 |           INDEX RANGE SCAN     | SYS_C00221036      |     1 |     7 |      	|     1   (0)	|      00:00:01 |
|* 13 |           INDEX RANGE SCAN     | SYS_C00405454      |     1 |     7 |      	|     1   (0)	|      00:00:01 |
|  14 |       TABLE ACCESS FULL        | TT_CFS_ASSOC_CFS   |    14M|    97M|       	| 21392   (6)	|      00:04:17 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------
|  15 |     NESTED LOOPS               |                    |       |       |     	|            	|          	|
|  16 |      CONNECT BY PUMP           |                    |       |       |    	|            	|          	|
|* 17 |      INDEX RANGE SCAN          | SYS_C0050602       |    11 |   154 |       	|     1   (0)	|      00:00:01 |
|  18 |    NESTED LOOPS                |                    |  1583K|    31M|       	| 18536  (13)	|      00:03:43	|
|  19 |     TABLE ACCESS FULL          | TT_CFS_REALISE_RFS |    11M|   149M|      	| 17275   (7)	|      00:03:28 |
|  20 |     VIEW                       |                    |     1 |     7 |      	|     1   (0)	|      00:00:01 |
|  21 |      SORT UNIQUE               |                    |       |       |       	|            	|         	|
|  22 |       UNION-ALL PARTITION      |                    |       |       |       	|            	|          	|
|* 23 |        INDEX RANGE SCAN        | SYS_C0050935       |     1 |     7 |      	|     1   (0)	|      00:00:01 |
|* 24 |        INDEX RANGE SCAN        | SYS_C00221036      |     1 |     7 |      	|     1   (0)	|      00:00:01 |
|* 25 |        INDEX RANGE SCAN        | SYS_C00405454      |     1 |     7 |      	|     1   (0)	|      00:00:01 |
--------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("TT_CFS_ASSOC_CFS"."FK_FROM_CFS"=PRIOR "TT_CFS_ASSOC_CFS"."FK_TO_CFS")
   5 - access("TT_CFS_ASSOC_CFS"."FK_FROM_CFS"="TT_CFS_REALISE_RFS"."FK_FROM_CFS")
  11 - access("TTE"."FK_FROM_RFS"="TT_CFS_REALISE_RFS"."FK_TO_RFS")
  12 - access("TTA"."FK_FROM_RFS"="TT_CFS_REALISE_RFS"."FK_TO_RFS")
  13 - access("TTP"."FK_FROM_RFS"="TT_CFS_REALISE_RFS"."FK_TO_RFS")

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------
  17 - access("TT_CFS_ASSOC_CFS"."FK_FROM_CFS"=PRIOR "TT_CFS_ASSOC_CFS"."FK_TO_CFS")
  23 - access("TTE"."FK_FROM_RFS"="TT_CFS_REALISE_RFS"."FK_TO_RFS")
  24 - access("TTA"."FK_FROM_RFS"="TT_CFS_REALISE_RFS"."FK_TO_RFS")
  25 - access("TTP"."FK_FROM_RFS"="TT_CFS_REALISE_RFS"."FK_TO_RFS")

45 rows selected

SQL>

The above query is taking 2 1/2 hrs.

Please advice.

Report message to a moderator

Re: Tune the materialized view [message #571159 is a reply to message #571072]

Wed, 21 November 2012 20:39

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

How many rows are in the three tables tt_rfs_uses_ethlifc, tt_rfs_uses_atmlifc, and tt_rfs_uses_poltifc?

If there are not many then it would probably be better to drive from the UNION query and then nested-loop join to tt_cfs_realise_rfs
rather than the other way around.

Is there an index on tt_cfs_realise_rfs.fk_to_rfs? If so, it seems to ignored by Oracle, probably because it thinks the 3-way UNION will return several million rows.

Ross Leishman

Report message to a moderator

Re: Tune the materialized view [message #571166 is a reply to message #571159]

Wed, 21 November 2012 23:34

grpatwari
Messages: 288
Registered: June 2008
Location: Hyderabad

Senior Member

Please check the number of records and indexes in tables.

SQL> select count(*) from tt_rfs_uses_ethlifc;
 
  COUNT(*)
----------
   1084495
 
SQL> select count(*) from  tt_rfs_uses_atmlifc; 
 
  COUNT(*)
----------
       464
 
SQL> select count(*) from  tt_rfs_uses_poltifc;
 
  COUNT(*)
----------
     14278

SQL> select count(*) from tt_cfs_realise_rfs;
 
  COUNT(*)
----------
  11237361

SQL> select count(*) from tt_cfs_assoc_cfs;
 
  COUNT(*)
----------
  14713152

Primary keys and indexes are


SQL> alter table ISI_SCHEMA.TT_CFS_REALISE_RFS add primary key (FK_FROM_CFS, FK_TO_RFS);

SQL> create index ISI_SCHEMA.IDX_ISI3456 on ISI_SCHEMA.TT_CFS_REALISE_RFS (FK_CFS_REALISE_RFS);

SQL> create index ISI_SCHEMA.IDX_ISI3457 on ISI_SCHEMA.TT_CFS_REALISE_RFS (FK_TO_RFS);

SQL> alter table ISI_SCHEMA.TT_CFS_ASSOC_CFS add primary key (FK_FROM_CFS, FK_TO_CFS);

SQL> create index ISI_SCHEMA.IDX_ISI3450 on ISI_SCHEMA.TT_CFS_ASSOC_CFS (FK_CFS_ASSOC_CFS);

SQL> create index ISI_SCHEMA.IDX_ISI3451 on ISI_SCHEMA.TT_CFS_ASSOC_CFS (FK_TO_CFS);

SQL> create index ISI_SCHEMA.IDX_ISI4084 on ISI_SCHEMA.TT_RFS_USES_ETHLIFC (FK_RFS_USES_ETHLIFC);

SQL> create index ISI_SCHEMA.IDX_ISI4085 on ISI_SCHEMA.TT_RFS_USES_ETHLIFC (FK_TO_ETHLIFC);

SQL> create index ISI_SCHEMA.IDX_ISI5471 on ISI_SCHEMA.TT_RFS_USES_ATMLIFC (FK_RFS_USES_ATMLIFC);

SQL> create index ISI_SCHEMA.IDX_ISI5472 on ISI_SCHEMA.TT_RFS_USES_ATMLIFC (FK_TO_ATMLIFC);

SQL> create index ISI_SCHEMA.IDX_ISI5663 on ISI_SCHEMA.TT_RFS_USES_POLTIFC (FK_RFS_USES_POLTIFC);

SQL> create index ISI_SCHEMA.IDX_ISI5664 on ISI_SCHEMA.TT_RFS_USES_POLTIFC (FK_TO_POLTIFC);

Report message to a moderator

Re: Tune the materialized view [message #571167 is a reply to message #571166]

Thu, 22 November 2012 00:03

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

There are 1M rows in tt_rfs_uses_ethlifc, which is around 10% of the number of rows in tt_cfs_realise_rfs, so Oracle is probably right not to drive off the UNION subquery.

Try this:

WITH tt_rfs_lifc AS (
                  select /*+ MATERIALIZE */ tt_cfs_realise_rfs.fk_from_cfs AS CFS
                    from (select tte.fk_from_rfs
                          from tt_rfs_uses_ethlifc tte
                         union
                         select tta.fk_from_rfs
                         from  tt_rfs_uses_atmlifc tta
                         union
                         select ttp.fk_from_rfs
                         from  tt_rfs_uses_poltifc ttp
                         ) tt_rfs_lifc,
                        tt_cfs_realise_rfs
                  where tt_rfs_lifc.fk_from_rfs = tt_cfs_realise_rfs.fk_to_rfs
)
    select tt_cfs_assoc_cfs.fk_to_cfs as ALL_CFS_ID,
           CONNECT_BY_ROOT tt_cfs_assoc_cfs.fk_from_cfs as CFS_BBACCESS_ID
    from tt_cfs_assoc_cfs
    CONNECT BY NOCYCLE tt_cfs_assoc_cfs.fk_from_cfs = PRIOR tt_cfs_assoc_cfs.fk_to_cfs
    START WITH tt_cfs_assoc_cfs.fk_from_cfs IN (
                  select CFS
                    from tt_rfs_lifc) 
    UNION
    select cfs as ALL_CFS_ID,
           cfs as CFS_BBACCESS_ID
    from  tt_rfs_lifc;

This will run the UNION and join just once instead of twice, but it will probably still perform the Nested Loops join. It seems to prefer the NL join because the triple UNION can be satisfied with simple Indexes only (no need to lookup the table). It is possible that a Fast Full Scan of these indexes plus a hash join would be better, so also compare performance with the 2nd line reading:

select /*+ MATERIALIZE ORDERED USE_HASH(tt_cfs_realise_rfs) */ 
       tt_cfs_realise_rfs.fk_from_cfs AS CFS

This will force a hash join instead of the Nested Loops. If the Cost Based Optimizer is right then this will not be any faster.

Ross Leishman

Report message to a moderator

Re: Tune the materialized view [message #571540 is a reply to message #571167]

Tue, 27 November 2012 00:40

grpatwari
Messages: 288
Registered: June 2008
Location: Hyderabad

Senior Member

Hi,

Below query is also taking same time as my previous query.

Please advice.

Report message to a moderator

Re: Tune the materialized view [message #571598 is a reply to message #571540]

Tue, 27 November 2012 16:54

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

What below query?

Report message to a moderator

Re: Tune the materialized view [message #571620 is a reply to message #571598]

Wed, 28 November 2012 03:22

grpatwari
Messages: 288
Registered: June 2008
Location: Hyderabad

Senior Member

I mean WITH clause query (which has given by Ross) also taking same time as my previous query.

Report message to a moderator

Previous Topic:	Query rewrite - high temp utilization
Next Topic:	Top 5 long running using DBA_* hist views

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Apr 20 06:18:45 CDT 2024