| Problem queries of Materialized Views [message #490975] |
Fri, 28 January 2011 06:01  |
indrajit2002
Messages: 53
Registered: November 2007
Location: INDIA
|
Member |
|
|
Hi,
We are using the below query to create a Materialized View but it has been running since 3 hours. It is an Oracle 9i database running in HP-UX.The quey is as follows,
(SELECT
/*+ use_nl(A) parallel (A,4)*/
A.ICD_CODE AS ICD_CODE,
A.ICD_DESC AS ICD_DESC,
A.PROC_GROUP as PROC_GROUP,
B.COMPL_ICD_CODE AS COMPL_ICD_CODE,
B.COMPL_GRP_TXT AS COMPL_GRP_TXT,
C.PROC_TYPE AS PROC_TYPE ,
C.I_O_IND AS I_O_IND,
C.DISC_MON AS QUARTER ,
B.PAT_KEY AS PAT_KEY ,
D.COMPL_TYPE_TXT AS COMPL_TYPE_TXT ,
C.PROV_ID AS PROV_ID ,
A.SPECIALTY AS SPECIALTY
FROM
EES_ICD_9_CODE A ,
EES_CLINICAL_COMPL_DATA B,
EES_CLINICAL_DATA C,
EES_CLINCL_COMPL_ICD D
WHERE A.ICD_CODE= B.ICD_CODE
AND B.ICD_CODE= C.ICD_CODE
AND B.COMPL_ICD_CODE=D.ICD_9_CD
AND B.PAT_KEY=C.PAT_KEY
AND B.COMPL_ICD_CODE<>B.ICD_CODE
AND C.PROC_TYPE <> 'L'
AND B.COMPL_GRP_TXT<>'Reoperations'
AND D.COMPL_TYPE_TXT<>'Intra-operative Misadventure'
AND C.DISC_MON>='2003101'
AND A.SPECIALTY='Colo-Rectal')
union
(SELECT
/*+ use_nl(A) parallel (A,4)*/
A.ICD_CODE AS ICD_CODE,
A.ICD_DESC AS ICD_DESC,
A.PROC_GROUP as PROC_GROUP,
B.COMPL_ICD_CODE AS COMPL_ICD_CODE,
B.COMPL_GRP_TXT AS COMPL_GRP_TXT,
C.PROC_TYPE AS PROC_TYPE ,
C.I_O_IND AS I_O_IND,
C.DISC_MON AS QUARTER ,
B.PAT_KEY AS PAT_KEY ,
D.COMPL_TYPE_TXT AS COMPL_TYPE_TXT ,
C.PROV_ID AS PROV_ID ,
A.SPECIALTY AS SPECIALTY
FROM
EES_ICD_9_CODE A ,
EES_CLINICAL_COMPL_DATA B,
EES_CLINICAL_DATA C,
EES_CLINCL_COMPL_ICD D
WHERE A.ICD_CODE= B.ICD_CODE
AND B.ICD_CODE= C.ICD_CODE
AND B.COMPL_ICD_CODE=D.ICD_9_CD
AND B.PAT_KEY=C.PAT_KEY
AND B.COMPL_ICD_CODE<>B.ICD_CODE
AND C.PROC_TYPE <> 'L'
--AND B.COMPL_GRP_TXT<>'Reoperations'
AND D.COMPL_TYPE_TXT='Intra-operative Misadventure'
AND B.PROC_DAY=C.PROC_DAY
AND C.DISC_MON>='2003101'
AND A.SPECIALTY='Colo-Rectal')
union
(SELECT
/*+ use_nl(A) parallel (A,4)*/
A.ICD_CODE AS ICD_CODE,
A.ICD_DESC AS ICD_DESC,
A.PROC_GROUP as PROC_GROUP,
B.COMPL_ICD_CODE AS COMPL_ICD_CODE,
B.COMPL_GRP_TXT AS COMPL_GRP_TXT,
C.PROC_TYPE AS PROC_TYPE ,
C.I_O_IND AS I_O_IND,
C.DISC_MON AS QUARTER ,
B.PAT_KEY AS PAT_KEY ,
D.COMPL_TYPE_TXT AS COMPL_TYPE_TXT ,
C.PROV_ID AS PROV_ID ,
A.SPECIALTY AS SPECIALTY
FROM
EES_ICD_9_CODE A ,
EES_CLINICAL_COMPL_DATA B,
EES_CLINICAL_DATA C,
EES_CLINCL_COMPL_ICD D
WHERE A.ICD_CODE= B.ICD_CODE
AND B.ICD_CODE= C.ICD_CODE
AND B.COMPL_ICD_CODE=D.ICD_9_CD
AND B.PAT_KEY=C.PAT_KEY
AND B.COMPL_ICD_CODE<>B.ICD_CODE
AND C.PROC_TYPE <> 'L'
AND B.COMPL_GRP_TXT='Reoperations'
--AND D.COMPL_TYPE_TXT='Intra-operative Misadventure'
AND B.PROC_DAY>C.PROC_DAY
AND C.DISC_MON>='2003101'
AND A.SPECIALTY='Colo-Rectal')
The explain plan is as follows,
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 46935 | 6716K| | 10648 | | | |
| 1 | SORT UNIQUE | | 46935 | 6716K| 14M| 10648 | 45,14 | P->S | QC (RAND) |
| 2 | UNION-ALL | | | | | | 45,13 | P->P | HASH |
|* 3 | HASH JOIN | | 42801 | 6102K| | 4640 | 45,13 | PCWP | |
|* 4 | TABLE ACCESS FULL | EES_CLINCL_COMPL_ICD | 875 | 18375 | | 2 | 45,02
|* 5 | HASH JOIN | | 41552 | 5072K| 14M| 4638 | 45,13 | PCWP | |
|* 6 | TABLE ACCESS FULL | EES_CLINICAL_DATA | 1430K| 40M| | 2855 | 45,03
|* 7 | HASH JOIN | | 628K| 56M| | 494 | 45,10 | P->P | HASH |
|* 8 | TABLE ACCESS FULL | EES_ICD_9_CODE | 37 | 2183 | | 1 | 45,08
|* 9 | TABLE ACCESS BY INDEX ROWID | EES_CLINICAL_COMPL_DATA | 2090K| 71M| | 49
| 10 | BITMAP CONVERSION TO ROWIDS | | | | | | |
|* 11 | BITMAP INDEX FULL SCAN | INX_COMPL_GRP | | | | | |
|* 12 | TABLE ACCESS BY INDEX ROWID | EES_CLINICAL_DATA | 1 | 33 | | 1
| 13 | NESTED LOOPS | | 1327 | 196K| | 947 | 45,13 | PCWP | |
|* 14 | HASH JOIN | | 60205 | 6996K| | 495 | 45,13 | PCWP | |
| 15 | TABLE ACCESS BY INDEX ROWID | EES_CLINCL_COMPL_ICD | 36 | 756 | |
|* 16 | INDEX RANGE SCAN | COMPL_TYPE_TXT_3 | 1 | | | 1 |
|* 17 | HASH JOIN | | 1420K| 132M| | 494 | 45,13 | PCWP | |
|* 18 | TABLE ACCESS FULL | EES_ICD_9_CODE | 37 | 2183 | | 1 | 45,11
|* 19 | TABLE ACCESS BY INDEX ROWID | EES_CLINICAL_COMPL_DATA | 4722K| 175M| | 49
| 20 | BITMAP CONVERSION TO ROWIDS| | | | | | | |
| 21 | BITMAP INDEX FULL SCAN | INX_COMPL_GRP | | | | | |
|* 22 | INDEX RANGE SCAN | EES_CLINICAL_DATA_IND1 | 1 | | | 2 | 45,13
|* 23 | HASH JOIN | | 2807 | 416K| | 4695 | 45,13 | PCWP | |
| 24 | TABLE ACCESS FULL | EES_CLINCL_COMPL_ICD | 911 | 19131 | | 2 | 45,06
|* 25 | HASH JOIN | | 2617 | 334K| 15M| 4693 | 45,13 | PCWP | |
|* 26 | TABLE ACCESS FULL | EES_CLINICAL_DATA | 1430K| 45M| | 2855 | 45,07
|* 27 | HASH JOIN | | 791K| 74M| | 305 | 45,12 | P->P | HASH |
|* 28 | TABLE ACCESS FULL | EES_ICD_9_CODE | 37 | 2183 | | 1 | 45,09
|* 29 | TABLE ACCESS BY INDEX ROWID | EES_CLINICAL_COMPL_DATA | 2632K| 97M| | 30
| 30 | BITMAP CONVERSION TO ROWIDS | | | | | | |
|* 31 | BITMAP INDEX SINGLE VALUE | INX_COMPL_GRP | | | | | |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("B"."COMPL_ICD_CODE"="D"."ICD_9_CD")
4 - filter("D"."COMPL_TYPE_TXT"<>'Intra-operative Misadventure')
5 - access("B"."ICD_CODE"="C"."ICD_CODE" AND "B"."PAT_KEY"="C"."PAT_KEY")
6 - filter("C"."PROC_TYPE"<>'L' AND "C"."DISC_MON">=2003101)
7 - access("A"."ICD_CODE"="B"."ICD_CODE")
8 - filter("A"."SPECIALTY"='Colo-Rectal')
9 - filter("B"."COMPL_ICD_CODE"<>"B"."ICD_CODE")
11 - filter("B"."COMPL_GRP_TXT"<>'Reoperations')
12 - filter("B"."ICD_CODE"="C"."ICD_CODE" AND "C"."PROC_TYPE"<>'L' AND "B"."PROC_DAY"="C"."PROC_DA
14 - access("B"."COMPL_ICD_CODE"="D"."ICD_9_CD")
16 - access("D"."COMPL_TYPE_TXT"='Intra-operative Misadventure')
17 - access("A"."ICD_CODE"="B"."ICD_CODE")
18 - filter("A"."SPECIALTY"='Colo-Rectal')
19 - filter("B"."COMPL_ICD_CODE"<>"B"."ICD_CODE")
22 - access("B"."PAT_KEY"="C"."PAT_KEY")
23 - access("B"."COMPL_ICD_CODE"="D"."ICD_9_CD")
25 - access("B"."ICD_CODE"="C"."ICD_CODE" AND "B"."PAT_KEY"="C"."PAT_KEY")
filter("B"."PROC_DAY">"C"."PROC_DAY")
26 - filter("C"."PROC_TYPE"<>'L' AND "C"."DISC_MON">=2003101)
27 - access("A"."ICD_CODE"="B"."ICD_CODE")
28 - filter("A"."SPECIALTY"='Colo-Rectal')
29 - filter("B"."COMPL_ICD_CODE"<>"B"."ICD_CODE")
31 - access("B"."COMPL_GRP_TXT"='Reoperations')
Note: cpu costing is off
Please help.
|
|
|
|
| Re: Problem queries of Materialized Views [message #490989 is a reply to message #490975] |
Fri, 28 January 2011 06:33  |
OraKaran
Messages: 183
Registered: March 2008
Location: United Kingdom
|
Senior Member |
|
|
Hello Indrajit
In my experience where most of the data is retrieved using index access parallel clause haven't helped much. I would have tried removing it.
Also many rows are accessed for index access on EES_CLINICAL_COMPL_DATA table
I am not an expert like others in this forum but many would expect to see result of user_ind_columns for the above mentioned table
Regards,
OraKaran
|
|
|
|
| Re: Problem queries of Materialized Views [message #491000 is a reply to message #490989] |
Fri, 28 January 2011 07:46 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
|
Seeing as how the only difference between each of the 3 selects is three lines in the where clause the first thing I'd try is merging them into a single select. Should save you a lot of unecessary table scans.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Problem queries of Materialized Views [message #491017 is a reply to message #491014] |
Fri, 28 January 2011 08:27 |
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
|
Senior Member |
|
|
To be "that guy"...
cookiemonster wrote on Fri, 28 January 2011 14:14Have you tried my suggestion?
This
As for if the hash is slowing it, I've no idea. I'd ask the guy that put the NL hint in and why as a starting point.
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Problem queries of Materialized Views [message #491033 is a reply to message #491028] |
Fri, 28 January 2011 09:06 |
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
|
Senior Member |
|
|
Kick it into one select first and see what that does. It'll probably save you a lot of time.
Imho you should only use hints which change the execution plan if (and only if) the optimizer is getting it wrong - it sounds to me like you cannot assert that for sure so I'd be inclined to leave it be for now, maybe remove the NL hint for readability as it seems to not be doing anything...
Parallel hint may make it faster, it may make it slower...it depends on the hardware/environment/runtime loads/a lot of other things I probably forgot. Ask your DBA about using parallel.
Sorry I cant be more specific, the answer to so many questions (as a wise chap once said to me) is "it depends"
|
|
|
|
| Re: Problem queries of Materialized Views [message #491038 is a reply to message #491033] |
Fri, 28 January 2011 09:34 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
I've got no good suggestions for hints, I avoid them as much as possible.
The query as it stands looks like an attempt to out-smart oracle. Given a reasonable query and upto date statistics oracle will come up with an effiecient plan 99.9% of the time.
The 3 selects above force oracle to scan each table three times. If they were combined then oracle could find all the data it needs in a single pass on each. All the parallel will do, assuming it helps at all, will be to claw back some of the effort wasted by splitting the selects in the first place.
|
|
|
|
| Re: Problem queries of Materialized Views [message #492440 is a reply to message #491038] |
Mon, 31 January 2011 04:40 |
OraKaran
Messages: 183
Registered: March 2008
Location: United Kingdom
|
Senior Member |
|
|
Hello Indrajit
I do not know your data but I would have tried
1) Removing at least parallel hint first
2) adding a condition in where clause
3) making indices composite
4) using Decode if possible to avoid union which is scanning tables 3 times as 'cookiemonster' has already pointed.
You have commented "--AND B.COMPL_GRP_TXT<>'Reoperations'" in the second section (of 3 sections joined by union).
May be you can use condition something like
decode(B.COMPL_GRP_TXT='Reoperations',D.COMPL_TYPE_TXT='Intra-operative Misadventure',B.COMPL_GRP_TXT<>'Reoperations',D.COMPL_TYPE_TXT<>'Intra-operative Misadventure')
Of course this rough idea
Regards,
OraKaran
|
|
|
|
|
|
| Re: Problem queries of Materialized Views [message #492468 is a reply to message #490975] |
Mon, 31 January 2011 05:45 |
OraKaran
Messages: 183
Registered: March 2008
Location: United Kingdom
|
Senior Member |
|
|
Hello Cookiemonster
Quote:
For point 2 - why would you be adding conditions? Wouldn't that change the result?
Many times adding certain in 'where' clause helps query. Not necessarily it will change the results always. It is because developers may not have considered a particular condition. I myself have seen queries where certain combination of dates and/ or flags (e.g.'CR','DR') etc. have less amount of data scanned for giving the required results only. Of course this needs in depth knowledge of data.
Regarding using 'OR' condition I agree with you
Regards,
OraKaran
|
|
|
|
| Re: Problem queries of Materialized Views [message #492660 is a reply to message #490975] |
Tue, 01 February 2011 11:36 |
Art Trifonov
Messages: 11
Registered: June 2007
Location: Boston
|
Junior Member |
|
|
Do you need to use UNION instead of UNION ALL?
The three data sets will never intersect because you include COMPL_GRP_TXT and COMPL_TYPE_TXT with different filters on these columns for each set.
If you expect duplicates within each set, then you should first do a DISTINCT on the driving data set, and then join to reference tables. This will reduce the number of rows to be joined and the size of SORT.
As someone already mentioned, NL joins rarely work well in PARALLEL. That could be why the optimizer is overruling the NL hint.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
