| 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.
|
|
|
|
|
|
| Re: Tune the materialized view [message #570762 is a reply to message #570761] |
Thu, 15 November 2012 07:38  |
cookiemonster
Messages: 13958
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.
|
|
|
|
|
|
|
|
|
|
| Re: Tune the materialized view [message #571028 is a reply to message #571025] |
Tue, 20 November 2012 05:03 |
 |
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Michel Cadot wrote on Tue, 20 November 2012 08:04Michel Cadot wrote on Mon, 19 November 2012 13:07Michel Cadot wrote on Mon, 19 November 2012 12:20Michel Cadot wrote on Mon, 19 November 2012 08:21Michel Cadot wrote on Fri, 16 November 2012 15:38With 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:29Please 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:54So 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.
|
|
|
|
| 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
|
|
|
|
| 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);
|
|
|
|
| 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
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
