Home » RDBMS Server » Performance Tuning » Performance Issue (Oracle 10g, Windows)
Performance Issue [message #603817] |
Thu, 19 December 2013 09:23 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
pstanand
Messages: 133 Registered: February 2005 Location: Chennai,India
|
Senior Member |
|
|
Hi,
I'm facing performance issue in my sql.
My sql is having UNION ALL clause with a materialized view and a table. But there is no join condition for this materialized view and the table. The query when executed running for a long time and got suspended. Requesting your help is there any other way to rewrite this query to improve the performance. My materialized view contains nearly 1M records.
Please find the sample query below.
SELECT * FROM
(
SELECT v.category
,v.name
,v.parent_project
FROM prnt_mat_v v
,rpt_tbl r
WHERE r.rpt_id = 10850
UNION ALL
SELECT v.category
,v.name
,v.parent_project
FROM chld_mat_v v
,rpt_tbl r
WHERE r.rpt_id = 10850
)
Thanks.
Regards,
Pst
|
|
|
|
Re: Performance Issue [message #603827 is a reply to message #603817] |
Thu, 19 December 2013 11:50 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
pstanand wrote on Thu, 19 December 2013 20:53
I'm facing performance issue in my sql.
My sql is having UNION ALL clause with a materialized view and a table.
1. First of all, the "SELECT * FROM" statement as an outer query is useless.
2. What's the relation between "prnt_mat_v" and "chld_mat_v"?
I know, the answer for my 2nd question would be :
Quote:
But there is no join condition for this materialized view and the table.
I hope you are working on a Relational database. Without any relationship among them, why do these objects even exist at all? It has to a be design issue.
|
|
|
Re: Performance Issue [message #603855 is a reply to message #603827] |
Thu, 19 December 2013 23:40 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
pstanand
Messages: 133 Registered: February 2005 Location: Chennai,India
|
Senior Member |
|
|
Hi please find the DDL for materialized views,table script, explain pland attached for your reference. Is there any other way this query can be rewritten using WITH clause or using any materialized HINTS. Please see below the original sql I used. Please suggest
me.
( SELECT *
FROM
(
SELECT v.division_function DIVISION
, v.bu_meaning BUSINESS_UNIT
, v.category CATEGORY
, v.country_meaning COUNTRY
, v.brand_meaning BRAND
, v.proposal_id PROJECT_ID
, v.name TITLE
, v.description DESCRIPTION
, v.parent_project PARENT_PROJECT
, v.project_class_meaning PROJECT_CLASS
, v.benefit_category_m BENEFIT_CATEGORY
, v.feeder_stream FEEDER_STREAM
, v.feeder_stream_detail_meaning FEEDER_STREAM_DETAIL
, v.commodity_meaning COMMODITY
, v.pl_meaning PL_LINE
, v.status STATUS
, v.have_fin_validated HAVE_FIN_VALIDATED
, v.date_savings_start DATE_SAVINGS_START
, v.project_manager PROJECT_MANAGER
, v.location LOCATION
, v.supplier_diversity SUPPLIER_DIVERSITY
, v.pso_indirect PSO_INDIRECT
, v.project_probability PROJECT_PROBABILITY
, round(CLX_CSDB_GET_PARENT_PLAN_ACT3(v.benefit_line_id,null, null, 'NET_SAVINGS', null, null, 58254),0) NET_SAVINGS
, p.short_name PERIOD
, round(decode(p.month, 'YEAR', 0, 'QUARTER', 0, CLX_CSDB_GET_PARENT_PLAN_ACT3(v.benefit_line_id,p.period_id, P.FS_SOURCE, p.month, p.quarter, p.year, 58254)),0) AMOUNT
FROM CLX_CSDB_PROP_V_MAT v
, CLX_CSDB_RPT_PERIODS p
WHERE p.report_id = 58254
AND instr('ONLY_STRUCTURAL', v.benefit_category, 1) > 0
--ORDER BY v.proposal_id desc
UNION ALL
SELECT v.division_function DIVISION
, v.bu_meaning BUSINESS_UNIT
, v.category CATEGORY
, v.country_meaning COUNTRY
, v.brand_meaning BRAND
, v.PARENT_PROJECT_ID PROJECT_ID
, v.name TITLE
, v.description DESCRIPTION
, v.parent_project PARENT_PROJECT
, v.project_class_meaning PROJECT_CLASS
, v.benefit_category_m BENEFIT_CATEGORY
, v.feeder_stream FEEDER_STREAM
, v.feeder_stream_detail_meaning FEEDER_STREAM_DETAIL
, v.commodity_meaning COMMODITY
, v.pl_meaning PL_LINE
, v.status STATUS
, v.have_fin_validated HAVE_FIN_VALIDATED
, v.date_savings_start DATE_SAVINGS_START
, v.project_manager PROJECT_MANAGER
, v.location LOCATION
, v.supplier_diversity SUPPLIER_DIVERSITY
, v.pso_indirect PSO_INDIRECT
, v.project_probability PROJECT_PROBABILITY
, round(CLX_CSDB_GET_PRJ_ACTUALS3(V.CHILD_FS_ID, V.FS_ID, v.benefit_line_id, V.BENEFIT_TYPE, V.BENEFIT_CATEGORY, V.USER_DATA_ID, null, null, 'NET_SAVINGS', null, null, 58254),0) NET_SAVINGS
, p.short_name PERIOD
, round(decode(p.month, 'YEAR', 0, 'QUARTER', 0, CLX_CSDB_GET_PRJ_ACTUALS3(V.CHILD_FS_ID, V.FS_ID, v.benefit_line_id, V.BENEFIT_TYPE, V.BENEFIT_CATEGORY, V.USER_DATA_ID, p.period_id, P.FS_SOURCE, P.MONTH, p.quarter, p.year, 58254)),0) AMOUNT
FROM CLX_CSDB_PRJ_CHILD_V_MAT v
, CLX_CSDB_RPT_PERIODS p
WHERE p.report_id = 58254
AND instr('ONLY_STRUCTURAL', v.benefit_category, 1) > 0
ORDER BY v.PARENT_PROJECT_ID desc
)
PIVOT
(SUM(NVL(AMOUNT, 0)) as unadj
FOR PERIOD IN ('Jul 13','Aug 13','Sep 13','Oct 13','Nov 13','Dec 13','Jan 14','Feb 14','Mar 14','Apr 14','May 14','Jun 14','Q1 14','Q2 14','Q3 14','Q4 14','Total 14','Jul 14','Aug 14','Sep 14','Oct 14','Nov 14','Dec 14','Jan 15','Feb 15','Mar 15','Apr 15','May 15','Jun 15','Q1 15','Q2 15','Q3 15','Q4 15','Total 15','Jul 15','Aug 15','Sep 15','Oct 15','Nov 15','Dec 15','Jan 16','Feb 16','Mar 16','Apr 16','May 16','Jun 16','Q1 16','Q2 16','Q3 16','Q4 16','Total 16','Jul 16','Aug 16','Sep 16','Oct 16','Nov 16','Dec 16','Jan 17','Feb 17','Mar 17','Apr 17','May 17','Jun 17','Q1 17','Q2 17','Q3 17','Q4 17','Total 17'))
)
|
|
|
|
|
Re: Performance Issue [message #603914 is a reply to message #603912] |
Fri, 20 December 2013 05:23 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13921 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Please generate the explain plan in sqlplus using the below method then post it here in code tags as described here:
How to use [code] tags and make your code easier to read?
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>
|
|
|
Re: Performance Issue [message #603930 is a reply to message #603914] |
Fri, 20 December 2013 07:17 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
pstanand
Messages: 133 Registered: February 2005 Location: Chennai,India
|
Senior Member |
|
|
Hi Please find the sql and the explain plan as requested for your perusal. Kindly suggest me a way.
Thanks.
SQL> ed
1 explain plan for
2 ( SELECT *
3 FROM
4 (
5 SELECT
6 v.division_function DIVISION
7 , v.bu_meaning BUSINESS_UNIT
8 , v.category CATEGORY
9 , v.country_meaning COUNTRY
10 , v.brand_meaning BRAND
11 , v.proposal_id PROJECT_ID
12 , v.name TITLE
13 , v.description DESCRIPTION
14 , v.parent_project PARENT_PROJECT
15 , v.project_class_meaning PROJECT_CLASS
16 , v.benefit_category_m BENEFIT_CATEGORY
17 , v.feeder_stream FEEDER_STREAM
18 , v.feeder_stream_detail_meaning FEEDER_STREAM_DETAIL
19 , v.commodity_meaning COMMODITY
20 , v.pl_meaning PL_LINE
21 , v.status STATUS
22 , v.have_fin_validated HAVE_FIN_VALIDATED
23 , v.date_savings_start DATE_SAVINGS_START
24 , v.project_manager PROJECT_MANAGER
25 , v.location LOCATION
26 , v.supplier_diversity SUPPLIER_DIVERSITY
27 , v.pso_indirect PSO_INDIRECT
28 , v.project_probability PROJECT_PROBABILITY
29 , round(CLX_CSDB_GET_PARENT_PLAN_ACT3(v.benefit_line_id,null, null, 'NET_SAVINGS', null, null, 64066),0) NET_SAVINGS
30 , p.short_name PERIOD
31 , round(decode(p.month, 'YEAR', 0, 'QUARTER', 0, CLX_CSDB_GET_PARENT_PLAN_ACT3(v.benefit_line_id,p.period_id, P.FS_SOURCE, p.month, p.quarter, p.year, 64066)),0) AMOUNT
32 FROM CLX_CSDB_PROP_V_MAT v
33 CROSS JOIN CLX_CSDB_RPT_PERIODS p
34 WHERE p.report_id = 64066
35 AND instr('ONLY_STRUCTURAL', v.benefit_category, 1) > 0
36 --ORDER BY v.proposal_id desc
37 UNION ALL
38 SELECT
39 v.division_function DIVISION
40 , v.bu_meaning BUSINESS_UNIT
41 , v.category CATEGORY
42 , v.country_meaning COUNTRY
43 , v.brand_meaning BRAND
44 , v.PARENT_PROJECT_ID PROJECT_ID
45 , v.name TITLE
46 , v.description DESCRIPTION
47 , v.parent_project PARENT_PROJECT
48 , v.project_class_meaning PROJECT_CLASS
49 , v.benefit_category_m BENEFIT_CATEGORY
50 , v.feeder_stream FEEDER_STREAM
51 , v.feeder_stream_detail_meaning FEEDER_STREAM_DETAIL
52 , v.commodity_meaning COMMODITY
53 , v.pl_meaning PL_LINE
54 , v.status STATUS
55 , v.have_fin_validated HAVE_FIN_VALIDATED
56 , v.date_savings_start DATE_SAVINGS_START
57 , v.project_manager PROJECT_MANAGER
58 , v.location LOCATION
59 , v.supplier_diversity SUPPLIER_DIVERSITY
60 , v.pso_indirect PSO_INDIRECT
61 , v.project_probability PROJECT_PROBABILITY
62 , round(CLX_CSDB_GET_PRJ_ACTUALS3(V.CHILD_FS_ID, V.FS_ID, v.benefit_line_id, V.BENEFIT_TYPE, V.BENEFIT_CATEGORY, V.USER_DATA_ID, null, null, 'NET_SAVINGS', null, null, 64066),0) NET_SAVINGS
63 , p.short_name PERIOD
64 , round(decode(p.month, 'YEAR', 0, 'QUARTER', 0, CLX_CSDB_GET_PRJ_ACTUALS3(V.CHILD_FS_ID, V.FS_ID, v.benefit_line_id, V.BENEFIT_TYPE, V.BENEFIT_CATEGORY, V.USER_DATA_ID, p.period_id, P.FS_SOURCE, P.MONTH, p.quarter, p.year, 64066)),0) AMOUNT
65 FROM CLX_CSDB_PRJ_CHILD_V_MAT v
66 CROSS JOIN CLX_CSDB_RPT_PERIODS p
67 WHERE p.report_id = 64066
68 AND instr('ONLY_STRUCTURAL', v.benefit_category, 1) > 0
69 ORDER BY v.PARENT_PROJECT_ID desc
70 )
71 PIVOT
72 (SUM(NVL(AMOUNT, 0)) as unadj
73* FOR PERIOD IN ('Jul 13','Aug 13','Sep 13','Oct 13','Nov 13','Dec 13','Jan 14','Feb 14','Mar 14','Apr 14','May 14','Jun 14','Q1 14','Q2 14','Q3 14','Q4 14','Total 14','Jul 14','Aug 14','Sep 14','Oct 14','Nov 14','Dec 14','Jan 15','Feb 15','Mar 15','Apr 15','May 15','Jun 15','Q1 15','Q2 15','Q3 15','Q4 15','Total 15','Jul 15','Aug 15','Sep 15','Oct 15','Nov 15','Dec 15','Jan 16','Feb 16','Mar 16','Apr 16','May 16','Jun 16','Q1 16','Q2 16','Q3 16','Q4 16','Total 16','Jul 16','Aug 16','Sep 16','Oct 16','Nov 16','Dec 16','Jan 17','Feb 17','Mar 17','Apr 17','May 17','Jun 17','Q1 17','Q2 17','Q3 17','Q4 17','Total 17')))
74 /
Explained.
SQL>
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 2886296404
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1207 | 20M| 951 (1)| 00:00:12 |
| 1 | VIEW | | 1207 | 20M| 951 (1)| 00:00:12 |
| 2 | TRANSPOSE | | | | | |
| 3 | SORT GROUP BY PIVOT | | 1207 | 433K| 951 (1)| 00:00:12 |
| 4 | VIEW | | 1207 | 433K| 950 (1)| 00:00:12 |
| 5 | UNION-ALL | | | | | |
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
| 6 | MERGE JOIN CARTESIAN | | 132 | 70224 | 95 (2)| 00:00:02 |
|* 7 | TABLE ACCESS FULL | CLX_CSDB_RPT_PERIODS | 8 | 328 | 23 (0)| 00:00:0
| 8 | BUFFER SORT | | 17 | 8347 | 71 (0)| 00:00:01 |
|* 9 | MAT_VIEW ACCESS FULL| CLX_CSDB_PROP_V_MAT | 17 | 8347 | 9 (0)| 00:00:01 |
| 10 | MERGE JOIN CARTESIAN | | 1075 | 438K| 856 (1)| 00:00:11 |
|* 11 | TABLE ACCESS FULL | CLX_CSDB_RPT_PERIODS | 8 | 328 | 23 (0)| 00:00:0
| 12 | BUFFER SORT | | 135 | 50895 | 832 (1)| 00:00:10 |
|* 13 | MAT_VIEW ACCESS FULL| CLX_CSDB_PRJ_CHILD_V_MAT | 135 | 50895 | 104 (0)| 00:00:02 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
---------------------------------------------------
7 - filter(TO_NUMBER("P"."REPORT_ID")=64066)
9 - filter(INSTR('ONLY_STRUCTURAL',"V"."BENEFIT_CATEGORY",1)>0)
11 - filter(TO_NUMBER("P"."REPORT_ID")=64066)
13 - filter(INSTR('ONLY_STRUCTURAL',"V"."BENEFIT_CATEGORY",1)>0)
28 rows selected.
SQL>
|
|
|
|
Re: Performance Issue [message #603936 is a reply to message #603932] |
Fri, 20 December 2013 08:54 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
pstanand
Messages: 133 Registered: February 2005 Location: Chennai,India
|
Senior Member |
|
|
Hi,
but when I executed the query it took 12:04 minutes time to complete it. The explain plan is from the same database only.
|
|
|
|
Re: Performance Issue [message #603972 is a reply to message #603827] |
Sat, 21 December 2013 07:08 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
pablolee
Messages: 2882 Registered: May 2007 Location: Scotland
|
Senior Member |
|
|
Lalit Kumar B wrote on Thu, 19 December 2013 17:50
1. First of all, the "SELECT * FROM" statement as an outer query is useless.
Not useless, no. Not necessary, but not useless.
Quote:2. What's the relation between "prnt_mat_v" and "chld_mat_v"?
I hope you are working on a Relational database . The op has already stated that he is working with tables, so yes, he is working with a relational database.
Quote:Without any relationship among them, why do these objects even exist at all? It has to a be design issue.
That's quite a statement to make when you don't have the first clue as to the design and purpose of his warehouse wouldn't you say?
|
|
|
Re: Performance Issue [message #603975 is a reply to message #603972] |
Sat, 21 December 2013 12:27 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
pablolee wrote on Sat, 21 December 2013 18:38Lalit Kumar B wrote on Thu, 19 December 2013 17:50
1. First of all, the "SELECT * FROM" statement as an outer query is useless.
Not useless, no. Not necessary, but not useless.
I saw the query which OP posted, and based on that, YES, "select * from" as an outer query is not required. At least in that same query without any filter predicates, it seems unnecessary.
pablolee wrote on Sat, 21 December 2013 18:38Lalit Kumar B wrote on Thu, 19 December 2013 17:502. What's the relation between "prnt_mat_v" and "chld_mat_v"?
I hope you are working on a Relational database . The op has already stated that he is working with tables, so yes, he is working with a relational database.
OP mentioned :
pstanand wrote on Thu, 19 December 2013 20:53But there is no join condition for this materialized view and the table.
Here there are 3 objects being referred to, 2 MVs and a table. And there is no relationship maintained.
pablolee wrote on Sat, 21 December 2013 18:38Lalit Kumar B wrote on Thu, 19 December 2013 17:50Without any relationship among them, why do these objects even exist at all? It has to a be design issue.
That's quite a statement to make when you don't have the first clue as to the design and purpose of his warehouse wouldn't you say?
I don't expect to know the application/DB design just by looking at an incomplete post, however, based on OPs information it is evident that there is a design flaw.
Again, nobody could be perfect at all, however, I replied to OP based on the information provided in his original post.
|
|
|
Re: Performance Issue [message #603979 is a reply to message #603975] |
Sat, 21 December 2013 16:25 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
pablolee
Messages: 2882 Registered: May 2007 Location: Scotland
|
Senior Member |
|
|
Lalit Kumar B wrote on Sat, 21 December 2013 18:27
Here there are 3 objects being referred to, 2 MVs and a table. And there is no relationship maintained.
What does that have to do with anything? A relation is basically a table (tuples grouped together) see here It does not mean that tables are necessarily related to each other. And just because two tables involved in a query are not joined on key fields does not mean that the database is not relational.
Quote:
I don't expect to know the application/DB design just by looking at an incomplete post, however, based on OPs information it is evident that there is a design flaw. Really? I don't think that it is evident, it's certainly possible and potentially worth investigating, but if you can see a clear design flaw please give the details of that flaw as you see it.
Quote:Again, nobody could be perfect at all, however, I replied to OP based on the information provided in his original post.
Not really Lalit, what you did was respond with a post that points out 'useless' code, that is in fact, simply unnecessary, rather than useless. You made swinging statements that alluded to faults in the database design without actually knowing if there ARE any faults in the design. Certainly there are parts of that query that deserve investigating, but it is ENTIRELY possible that that query is valid and that the model is valid (do you think that it would be possible to write a query using two tables that didn't have a direct relationship if it wasn't, sometimes, a valid thing to do?)
|
|
|
Re: Performance Issue [message #604004 is a reply to message #603979] |
Sun, 22 December 2013 11:06 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
@Pablolee :
OK. I understand what you said. And I know what is a RDBMS, lest I should not be a part of the forum.
I believe that the two objects that OP mentioned in his original post, "prnt_mav_v" and chld_mat_v" must have a relation. At least, by looking at the nomenclature of the PARENT and CHILD objects, there must be a relationship maintained.
That's all I meant, and that's all I have had in my mind.
|
|
|
Re: Performance Issue [message #604008 is a reply to message #604004] |
Sun, 22 December 2013 12:54 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
pablolee
Messages: 2882 Registered: May 2007 Location: Scotland
|
Senior Member |
|
|
Lalit Kumar B wrote on Sun, 22 December 2013 17:06@Pablolee :
OK. I understand what you said. And I know what is a RDBMS, lest I should not be a part of the forum. This makes no real sense, what are you trying to say here?
Quote:I believe that the two objects that OP mentioned in his original post, "prnt_mav_v" and chld_mat_v" must have a relation .
And this is where your misunderstanding of the terminology presents itself. The two segments you mentioned ARE relations (did you read the link that I posted?). It may be that they should have relationSHIPS -the two terms are different, you seem to be trying to use them interchangeably -(although it is entirely possible that this is not necessary. Since you have no idea as to what the model is, all you can do is ASK the op for some sample data and an explanation as to how the data is meant to work. You CANNOT say with any certainty that the model is flawed without further information, that is for sure.
Quote:At least, by looking at the nomenclature of the PARENT and CHILD objects, there must be a relationship maintained.
That's all I meant, and that's all I have had in my mind. (emphasis added)
Why, why must there be a relationship maintained? Purely based on the column names? That's no reason to flat out state that someone's model is flawed without at least asking some pertinent questions. The point that I'm trying to make with you is that you rarely get to see the full story when someone posts a question, especially within the first post or two, if you feel that there might be an issue aside from the question that the op is actually asking, then you need to try to ascertain more info before you go making comments such as flawed design, when flawed design is not clear (as is the case here)
What CAN be said, is that the query is most likely flawed. Since it doesn't call on any data from rpt_tbl, other than in the where clause all that is happening is that a Cartesian product is being created and then restricted back down, probably back to what you would get if you didn't involve the rpt table at all. That looks worth asking about.
|
|
|
Re: Performance Issue [message #604017 is a reply to message #604008] |
Sun, 22 December 2013 23:20 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/56289.jpg) |
Kevin Meade
Messages: 2103 Registered: December 1999 Location: Connecticut USA
|
Senior Member |
|
|
I assume the following:
1. you know what a materialized view is
2. prnt_mat_v is a MVIEW
3. chld_mat_v is also a MVIEW
4. rpt_tbl is a table
I also assume that column RPT_ID is a Primary Key of table RPT_TBL and therefore the predicate R.RPT_ID = 10850 is expected to return one row.
Given these, the query plan I expect to see for the query you posted would include the following
FULL TABLE SCAN OF PRNT_MAT_V
FULL TABLE SCAN OF CHLD_MAT_V
UNION-ALL OF THESE TWO SCANS
INDEX LOOKUP OF RPT_TBL
MERGE JOIN CARTESIAN BETWEEN RPT_TBL AND THE UNION-ALL OF THE OTHER TWO TABLES
As was earlier suggested, the first problem with the query plan is that is shows an unbelievable cardinality for the scans of the materialized views. By your account these should be one million, not 17 and 135.
Additionally the PIVOT may be costing you a lot.
I suggest you do some query decomposition and reconstruction in order to determine what parts of the query are causing you to spend time. For example, do some CREATE TABLE AS SELECT commands for pieces of the query.
First collect statistics correctly DBMS_STATS.GATHER_TABLE_STATS(null,'prnt_mat_v'). Check to see if for your version of Oracle, the percentage sampled is 100 by default. If not, then use the percent parameter to make it so, otherwise accept the default.
Second check to see that you actually got valid stats. NUM_ROWS on DBA_TABLES and NDV on DBA_TAB_COL_STATISTICS.
Third do the decomposition tests.
a. the query without the pivot
b. the first subquery (no pivot)
c. the second subquery (no pivot)
These tests will give you a good idea where your time is going. From there you can determine if the problem is in data acquisition, or a follow on step (like the pivot).
Good luck, Kevin
|
|
|
|
|
|
|
Goto Forum:
Current Time: Sat May 04 06:34:36 CDT 2024
|