Home » RDBMS Server » Performance Tuning » Query Problem (Oracle 9.2.0.8(HP-UX))
| Query Problem [message #477755] |
Mon, 04 October 2010 04:38  |
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 1711 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  |
cookiemonster
Messages: 13958
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 |
cookiemonster
Messages: 13958
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 |
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 |
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 |
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: Fri Nov 22 07:39:52 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
