Home » RDBMS Server » Performance Tuning » Performance Issue (Oracle 10g, Windows)
Performance Issue [message #603817] Thu, 19 December 2013 09:23 Go to next message
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 #603819 is a reply to message #603817] Thu, 19 December 2013 09:26 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Since NOBODY can optimize SQL just by looking at it, we need a few more details.
http://www.orafaq.com/forum/m/433888/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) DDL for all tables & indexes
2) EXPLAIN PLAN
3) output from SQL_TRACE & tkprof
Re: Performance Issue [message #603827 is a reply to message #603817] Thu, 19 December 2013 11:50 Go to previous messageGo to next message
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 messageGo to next message
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 #603876 is a reply to message #603855] Fri, 20 December 2013 02:43 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Show us the explain plan for the query.
Re: Performance Issue [message #603912 is a reply to message #603876] Fri, 20 December 2013 05:17 Go to previous messageGo to next message
pstanand
Messages: 133
Registered: February 2005
Location: Chennai,India
Senior Member
Please find the explain plan for your perusal.
Re: Performance Issue [message #603914 is a reply to message #603912] Fri, 20 December 2013 05:23 Go to previous messageGo to next message
cookiemonster
Messages: 13917
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 messageGo to next message
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 #603932 is a reply to message #603930] Fri, 20 December 2013 08:16 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>My materialized view contains nearly 1M records.
I suspect that posted EXPLAIN PLAN is from different database than where problem actually exists since 12 seconds to complete SQL should not be a problem.
Re: Performance Issue [message #603936 is a reply to message #603932] Fri, 20 December 2013 08:54 Go to previous messageGo to next message
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 #603937 is a reply to message #603936] Fri, 20 December 2013 09:07 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
that suggests your stats are inaccurate - does the rows column match the number of rows the query should find?
Re: Performance Issue [message #603972 is a reply to message #603827] Sat, 21 December 2013 07:08 Go to previous messageGo to next message
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 messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
pablolee wrote on Sat, 21 December 2013 18:38
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.


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:38
Lalit Kumar B wrote on Thu, 19 December 2013 17:50
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.


OP mentioned :

pstanand wrote on Thu, 19 December 2013 20:53
But 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:38
Lalit Kumar B wrote on Thu, 19 December 2013 17:50
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?


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 messageGo to next message
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 messageGo to next message
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 messageGo to next message
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 messageGo to next message
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
Re: Performance Issue [message #604018 is a reply to message #604017] Sun, 22 December 2013 23:26 Go to previous messageGo to next message
pstanand
Messages: 133
Registered: February 2005
Location: Chennai,India
Senior Member
Thanks Kevin for your inputs. Let me try those steps to find it out.
Re: Performance Issue [message #604026 is a reply to message #603817] Mon, 23 December 2013 02:25 Go to previous messageGo to next message
michael_bialik
Messages: 621
Registered: July 2006
Senior Member
I don't know if you have an index on REPORT_ID column, but

Quote:
filter(TO_NUMBER("P"."REPORT_ID")=64066)


suggests trying

... REPORT_ID = '64066'
HTH.

[Updated on: Mon, 23 December 2013 02:26]

Report message to a moderator

Re: Performance Issue [message #604030 is a reply to message #604026] Mon, 23 December 2013 03:35 Go to previous messageGo to next message
pstanand
Messages: 133
Registered: February 2005
Location: Chennai,India
Senior Member
Yes The report ID column has index on it. It is the primary key column on rpt_periods table.
Re: Performance Issue [message #604043 is a reply to message #604030] Mon, 23 December 2013 05:19 Go to previous message
michael_bialik
Messages: 621
Registered: July 2006
Senior Member
So did you try my suggestion?
Previous Topic: Oracle alerts
Next Topic: Bitmap causing deadlock
Goto Forum:
  


Current Time: Thu Mar 28 12:51:45 CDT 2024