Home » RDBMS Server » Performance Tuning » Query Problem (Oracle 9.2.0.8(HP-UX))
Query Problem [message #477755] |
Mon, 04 October 2010 04:38 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
indrajit2002
Messages: 53 Registered: November 2007 Location: INDIA
|
Member |
|
|
Hi All,
I need another help for the following query,
WITH "Union15"
AS (SELECT DISTINCT CASE
WHEN( Substr(To_char(Floor("EES_ENERGY_MASTER"."DISC_MON" / 100)), 1, 4
) IS
NULL )
OR (
CASE Substr(To_char(Floor("EES_ENERGY_MASTER"."DISC_MON"/100)), 5,
1
)
WHEN '1' THEN 'Q1'
WHEN '2' THEN 'Q2'
WHEN '3' THEN 'Q3'
WHEN '4' THEN 'Q4'
END IS NULL ) THEN NULL
ELSE ( Substr(To_char(Floor("EES_ENERGY_MASTER"."DISC_MON" / 100)), 1,
4)
||
CASE Substr(To_char(Floor("EES_ENERGY_MASTER"."DISC_MON"/100)), 5,
1)
WHEN '1' THEN 'Q1'
WHEN '2' THEN 'Q2'
WHEN '3' THEN 'Q3'
WHEN '4' THEN 'Q4'
END )
END "QUARTER",
"EES_ENERGY_MASTER"."PAT_KEY" "Cases_Using_Energy",
0 "Total_Cases"
FROM "MDM_DBA"."EES_ENERGY_MASTER" "EES_ENERGY_MASTER",
"MDM_DBA"."EES_CONS_PROV" "EES_CONS_PROV"
WHERE "EES_ENERGY_MASTER"."DISC_MON" >= 2006101
AND "EES_ENERGY_MASTER"."PDCT_CAT" = 'Energy'
AND Substr("EES_ENERGY_MASTER"."ICD_CODE", 1, 2) = '68-gyn'
AND "EES_ENERGY_MASTER"."MANUFACTURER" = 'EES'
AND "EES_CONS_PROV"."CONS_06_09_YN" IN ( 'N', 'Y' )
AND "EES_ENERGY_MASTER"."PROV_ID" = "EES_CONS_PROV"."PROV_ID"
UNION
SELECT CASE
WHEN( Substr(To_char(Floor("MVW_EES_ICD_STATS"."DISC_QTR")), 1
, 4)
IS
NULL )
OR (
CASE Substr(To_char(Floor("MVW_EES_ICD_STATS"."DISC_QTR")),
5
, 1)
WHEN '1' THEN 'Q1'
WHEN '2' THEN 'Q2'
WHEN '3' THEN 'Q3'
WHEN '4' THEN 'Q4'
END IS NULL ) THEN NULL
ELSE ( Substr(To_char(Floor("MVW_EES_ICD_STATS"."DISC_QTR")),
1, 4)
||
CASE Substr(To_char(Floor("MVW_EES_ICD_STATS"."DISC_QTR")), 5
,
1)
WHEN '1' THEN 'Q1'
WHEN '2' THEN 'Q2'
WHEN '3' THEN 'Q3'
WHEN '4' THEN 'Q4'
END )
END "QUARTER",
0 "Cases_Using_Energy",
COUNT("MVW_EES_ICD_STATS"."CASES") "Total_Cases"
FROM "MDM_DBA"."MVW_EES_ICD_STATS" "MVW_EES_ICD_STATS",
"MDM_DBA"."EES_CONS_PROV" "EES_CONS_PROV",
"MDM_DBA"."EES_HOSP_CHG_PROV" "EES_HOSP_CHG_PROV"
WHERE "MVW_EES_ICD_STATS"."DISC_QTR" >= 20061
AND "MVW_EES_ICD_STATS"."ICD_GROUP" = '68-gyn'
AND "EES_CONS_PROV"."CONS_06_09_YN" IN ( 'N', 'Y' )
AND "EES_HOSP_CHG_PROV"."PDCT_CAT" = 'Energy'
AND "EES_HOSP_CHG_PROV"."MANUFACTURER" = 'EES'
AND "MVW_EES_ICD_STATS"."PROV_ID" = "EES_CONS_PROV"."PROV_ID"
AND "MVW_EES_ICD_STATS"."PROV_ID" =
"EES_HOSP_CHG_PROV"."PROV_ID"
GROUP BY CASE
WHEN( Substr(To_char(Floor("MVW_EES_ICD_STATS"."DISC_QTR")), 1, 4) IS
NULL
)
OR ( CASE Substr(To_char(Floor("MVW_EES_ICD_STATS"."DISC_QTR")),
5, 1
)
WHEN '1' THEN 'Q1'
WHEN '2' THEN 'Q2'
WHEN '3' THEN 'Q3'
WHEN '4' THEN 'Q4'
END IS NULL ) THEN NULL
ELSE ( Substr(To_char(Floor("MVW_EES_ICD_STATS"."DISC_QTR")), 1, 4)
||CASE Substr(To_char(Floor("MVW_EES_ICD_STATS"."DISC_QTR")),
5, 1)
WHEN '1' THEN 'Q1'
WHEN '2' THEN 'Q2'
WHEN '3' THEN 'Q3'
WHEN '4' THEN 'Q4'
END )
END)
SELECT "T1"."C0" "levelkey",
"T1"."C1" "Cases_Using_Energy",
"T0"."C0" "Cases_Using_Energy1",
"T1"."C2" "Total_Cases"
FROM (SELECT COUNT(DISTINCT "Union15"."Cases_Using_Energy") "C0"
FROM "Union15") "T0",
(SELECT "Union15"."QUARTER" "C0",
COUNT(DISTINCT "Union15"."Cases_Using_Energy") "C1",
SUM("Union15"."Total_Cases") "C2"
FROM "Union15"
GROUP BY "Union15"."QUARTER") "T1"
The explain plan for the query is also attached. Please help
-
Attachment: Explain.txt
(Size: 4.64KB, Downloaded 1684 times)
[Updated on: Mon, 04 October 2010 04:45] Report message to a moderator
|
|
|
Re: Query Problem [message #477756 is a reply to message #477755] |
Mon, 04 October 2010 04:49 ![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: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
It helps to tell us what the actual problem is. I assume from the explain plan that it's taking too long. So I've moved the thread to the performance tuning section.
It also helps if you post the explain plan in line in code tags:
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 50 | | 22 |
| 2 | TEMP TABLE TRANSFORMATION | | | | | |
| 1 | RECURSIVE EXECUTION | SYS_LE_2_0 | | | | |
| 0 | INSERT STATEMENT | | 3053 | 128K| | 1372 |
| 1 | LOAD AS SELECT | | | | | |
| 2 | SORT UNIQUE | | 3053 | 128K| 376K| 1372 |
| 3 | UNION-ALL | | | | | |
|* 4 | HASH JOIN | | 3052 | 128K| | 185 |
|* 5 | TABLE ACCESS FULL | EES_CONS_PROV | 622 | 3732 | | 2 |
|* 6 | TABLE ACCESS BY INDEX ROWID | EES_ENERGY_MASTER | 3055 | 110K| | 182 |
|* 7 | INDEX RANGE SCAN | IDX_ENERGY_PDCT_CAT | 1978 | | | 2478 |
| 8 | SORT GROUP BY | | 1 | 40 | | 1160 |
|* 9 | TABLE ACCESS BY INDEX ROWID | EES_HOSP_CHG_PROV | 4 | 72 | | 1 |
| 10 | NESTED LOOPS | | 1 | 40 | | 1157 |
| 11 | NESTED LOOPS | | 1 | 22 | | 1156 |
|* 12 | TABLE ACCESS BY INDEX ROWID| MVW_EES_ICD_STATS | 1 | 16 | | 1155 |
|* 13 | INDEX RANGE SCAN | MVWICDSTATS_DISCQTR_IDX | 13M| | | 26068 |
|* 14 | TABLE ACCESS BY INDEX ROWID| EES_CONS_PROV | 1 | 6 | | 1 |
|* 15 | INDEX UNIQUE SCAN | EES_CONS_PROV_PK | 1 | | | |
|* 16 | INDEX RANGE SCAN | PROV_ID_IDX_2 | 65 | | | 1 |
| 3 | MERGE JOIN CARTESIAN | | 1 | 50 | | 22 |
| 4 | VIEW | | 1 | 13 | | 3 |
| 5 | SORT GROUP BY | | 1 | 13 | | |
| 6 | VIEW | | 3053 | 39689 | | 3 |
| 7 | TABLE ACCESS FULL | SYS_TEMP_0FD9D68B3_C112F95D | 3053 | 110K| | 3 |
| 8 | VIEW | | 1 | 37 | | 19 |
| 9 | SORT GROUP BY | | 1 | 37 | | 19 |
| 10 | VIEW | | 3053 | 110K| | 3 |
| 11 | TABLE ACCESS FULL | SYS_TEMP_0FD9D68B3_C112F95D | 3053 | 110K| | 3 |
--------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("EES_ENERGY_MASTER"."PROV_ID"="EES_CONS_PROV"."PROV_ID")
5 - filter("EES_CONS_PROV"."CONS_06_09_YN"='N' OR "EES_CONS_PROV"."CONS_06_09_YN"='Y')
6 - filter("EES_ENERGY_MASTER"."DISC_MON">=2006101 AND SUBSTR("EES_ENERGY_MASTER"."ICD_CODE",1,2)='68-gyn'
AND "EES_ENERGY_MASTER"."MANUFACTURER"='EES')
7 - access("EES_ENERGY_MASTER"."PDCT_CAT"='Energy')
9 - filter("EES_HOSP_CHG_PROV"."PDCT_CAT"='Energy' AND "EES_HOSP_CHG_PROV"."MANUFACTURER"='EES')
12 - filter("MVW_EES_ICD_STATS"."ICD_GROUP"='68-gyn')
13 - access("MVW_EES_ICD_STATS"."DISC_QTR">=20061 AND "MVW_EES_ICD_STATS"."DISC_QTR" IS NOT NULL)
14 - filter("EES_CONS_PROV"."CONS_06_09_YN"='N' OR "EES_CONS_PROV"."CONS_06_09_YN"='Y')
15 - access("MVW_EES_ICD_STATS"."PROV_ID"="EES_CONS_PROV"."PROV_ID")
16 - access("MVW_EES_ICD_STATS"."PROV_ID"="EES_HOSP_CHG_PROV"."PROV_ID")
Note: cpu costing is off
|
|
|
|
Re: Query Problem [message #477785 is a reply to message #477761] |
Mon, 04 October 2010 08: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) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Well this:
CASE
WHEN( Substr(To_char(Floor("MVW_EES_ICD_STATS"."DISC_QTR")), 1, 4) IS
NULL
)
OR ( CASE Substr(To_char(Floor("MVW_EES_ICD_STATS"."DISC_QTR")),
5, 1
)
WHEN '1' THEN 'Q1'
WHEN '2' THEN 'Q2'
WHEN '3' THEN 'Q3'
WHEN '4' THEN 'Q4'
END IS NULL ) THEN NULL
ELSE ( Substr(To_char(Floor("MVW_EES_ICD_STATS"."DISC_QTR")), 1, 4)
||CASE Substr(To_char(Floor("MVW_EES_ICD_STATS"."DISC_QTR")),
5, 1)
WHEN '1' THEN 'Q1'
WHEN '2' THEN 'Q2'
WHEN '3' THEN 'Q3'
WHEN '4' THEN 'Q4'
END )
END)
Can be rewritten as:
CASE WHEN Substr(To_char(Floor(MVW_EES_ICD_STATS.DISC_QTR)),5,1) IN ('1','2','3','4')
THEN Substr(To_char(Floor(MVW_EES_ICD_STATS.DISC_QTR)), 1, 4)
||'Q'
||Substr(To_char(Floor(MVW_EES_ICD_STATS.DISC_QTR)),5,1)
ELSE NULL
END
Which might help a bit. It'll certainly make the query easier to read.
I think the problems are down to the calculations you are doing. But without knowing details of what you are trying to calculate and how the data is stored it is very difficult to suggest alternatives.
I suspect this can be solved with analytics with a lot less nesting of queries than you've currently got.
|
|
|
Re: Query Problem [message #477796 is a reply to message #477785] |
Mon, 04 October 2010 09:36 ![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) |
indrajit2002
Messages: 53 Registered: November 2007 Location: INDIA
|
Member |
|
|
Thanks Sir, I will surely give you the other details. Could you please give some suggestions about my other post today in which out of two queries I have given the explain plan of the problem query? It will be really nice of you.
Thanks in advance.
|
|
|
Re: Query Problem [message #477853 is a reply to message #477796] |
Mon, 04 October 2010 22:01 ![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) |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
This looks like a clue:
|* 12 | TABLE ACCESS BY INDEX ROWID| MVW_EES_ICD_STATS | 1 | 16 | | 1155 |
|* 13 | INDEX RANGE SCAN | MVWICDSTATS_DISCQTR_IDX | 13M| | | 26068 |
...
12 - filter("MVW_EES_ICD_STATS"."ICD_GROUP"='68-gyn')
13 - access("MVW_EES_ICD_STATS"."DISC_QTR">=20061 AND "MVW_EES_ICD_STATS"."DISC_QTR" IS NOT NULL)
A range scan of 13 MILLION rows from an index, all of them looked up in the table and filtered down to ONE MATCHING ROW.
I'd create an index MVW_EES_ICD_STATS(ICD_GROUP, DISC_QTR)
Ross Leishman
|
|
|
Re: Query Problem [message #478013 is a reply to message #477755] |
Tue, 05 October 2010 11:52 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
S.Rajaram
Messages: 1027 Registered: October 2006 Location: United Kingdom
|
Senior Member |
|
|
It's difficult to suggest something with what you have posted. CBO could be using a completely different plan. What you have posted is what oracle thinks it might use but it may not be the plan when you actually run the query. In order obtain the actual plan turn on 10046 trace and post the output of the tkprof.
How to do that check this link.
http://www.oracle-base.com/articles/10g/SQLTrace10046TrcsessAndTkprof10g.php
Without these information it will be anybody's guess what could be the issue.
Regards
Raj
[Edit: ] Removed the option for gather_plan_statistics. Didn't realise OP is on 9i
[Updated on: Tue, 05 October 2010 11:54] Report message to a moderator
|
|
|
Goto Forum:
Current Time: Thu Sep 12 10:07:43 CDT 2024
|