Home » RDBMS Server » Performance Tuning » Help with problem query (Oracle 9.2.0.8 (HP-UX B11.23))
Help with problem query [message #488177] Wed, 05 January 2011 08:59 Go to next message
indrajit2002
Messages: 53
Registered: November 2007
Location: INDIA
Member
Hi,
I have a query that gives output after running for over 1 hour.We need to tune the query. Please help on whether any hint will be useful.Here is the query,

SELECT 
A.ICD_CODE AS ICD_CODE,
A.ICD_DESC AS ICD_DESC,
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_DATA1 B,
EES_CLINICAL_DATA1 C ,
EES_CLINCL_COMPL_ICD D                                                                             
WHERE A.ICD_CODE= B.ICD_CODE 
                AND B.ICD_CODE= C.ICD_CODE 
                AND A.ICD_CODE=D.ICD_9_CD 
                AND B.PAT_KEY=C.PAT_KEY
                AND B.COMPL_GRP_TXT<>'Reoperations'
                AND D.COMPL_TYPE_TXT<>'Intra-operative Misadventure'
                AND A.SPECIALTY='GYN'


And here is the explain plan

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------
| Id  | Operation                        |  Name                     | Rows  | Bytes | Cost  |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                           | 11107 |  1431K| 18802 |
|*  1 |  TABLE ACCESS BY INDEX ROWID     | EES_CLINCL_COMPL_ICD      |     1 |    21 |     1 |
|   2 |   NESTED LOOPS                   |                           | 11107 |  1431K| 18802 |
|   3 |    NESTED LOOPS                  |                           | 10814 |  1172K| 18586 |
|   4 |     NESTED LOOPS                 |                           |   878K|    67M|  1007 |
|*  5 |      TABLE ACCESS FULL           | EES_ICD_9_CODE            |    21 |   903 |     2 |
|*  6 |      TABLE ACCESS BY INDEX ROWID | EES_CLINICAL_COMPL_DATA1  | 42873 |  1590K|  1007 |
|   7 |       BITMAP CONVERSION TO ROWIDS|                           |       |       |       |
|*  8 |        BITMAP INDEX SINGLE VALUE | IND_ICDCD                 |       |       |       |
|*  9 |     TABLE ACCESS BY INDEX ROWID  | EES_CLINICAL_DATA1        |     1 |    30 |     1 |
|* 10 |      INDEX RANGE SCAN            | IND_ICD_CD                | 16800 |       |     1 |
|* 11 |    INDEX RANGE SCAN              | ICD_CODE_INDEX_2          |     1 |       |     1 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("D"."COMPL_TYPE_TXT"<>'Intra-operative Misadventure')
   5 - filter("A"."SPECIALTY"='GYN')
   6 - filter("B"."COMPL_GRP_TXT"<>'Reoperations')
   8 - access("A"."ICD_CODE"="B"."ICD_CODE")
   9 - filter("B"."PAT_KEY"="C"."PAT_KEY")
  10 - access("B"."ICD_CODE"="C"."ICD_CODE")
  11 - access("A"."ICD_CODE"="D"."ICD_9_CD")

Note: cpu costing is off

30 rows selected.
Re: Help with problem query [message #488179 is a reply to message #488177] Wed, 05 January 2011 09:13 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
SQL appears tuned to me.

not every SQL can be made to run faster
Re: Help with problem query [message #488180 is a reply to message #488179] Wed, 05 January 2011 09:16 Go to previous messageGo to next message
knight
Messages: 111
Registered: January 2009
Senior Member
did you try to find if the query is waiting on something?
Re: Help with problem query [message #488181 is a reply to message #488180] Wed, 05 January 2011 09:18 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>did you try to find if the query is waiting on something?

In Oracle, READERs do NOT wait on anyone.
Re: Help with problem query [message #488183 is a reply to message #488181] Wed, 05 January 2011 09:27 Go to previous messageGo to next message
knight
Messages: 111
Registered: January 2009
Senior Member
wait events??
Re: Help with problem query [message #488185 is a reply to message #488183] Wed, 05 January 2011 09:28 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>wait events??
post reproducible test case, please.
Re: Help with problem query [message #488396 is a reply to message #488185] Fri, 07 January 2011 01:44 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
On table EES_CLINICAL_DATA1, I am betting that PAT_KEY is highly selective and ICD_CODE is not very selective (Explain Plan reckons there are 17K rows per value!).

Yet it chooses to use the non-selective index. Either:
  • PAT_KEY is not indexed, or
  • The index on PAT_KEY is not analyzed.

Make sure an index exists with PAT_KEY as the 1st column. Ideally, make ICD_CODE the second column.

As an alternative approach...


It looks as though your filter criteria are not very selective. Would you say that the 3 filter predicates filter less then 90% of all rows (i.e. more than 10% remain)?
                AND B.COMPL_GRP_TXT<>'Reoperations'
                AND D.COMPL_TYPE_TXT<>'Intra-operative Misadventure'
                AND A.SPECIALTY='GYN'


If so, a hash join approach might be preferable. Try the following for comparison:

SELECT /*+ ordered use_hash(a b c d) */
A.ICD_CODE AS ICD_CODE,
A.ICD_DESC AS ICD_DESC,
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_DATA1 B,
EES_CLINICAL_DATA1 C ,
EES_CLINCL_COMPL_ICD D                                                                             
WHERE A.ICD_CODE= B.ICD_CODE 
                AND B.ICD_CODE= C.ICD_CODE 
                AND A.ICD_CODE=D.ICD_9_CD 
                AND B.PAT_KEY=C.PAT_KEY
                AND B.COMPL_GRP_TXT<>'Reoperations'
                AND D.COMPL_TYPE_TXT<>'Intra-operative Misadventure'
                AND A.SPECIALTY='GYN'


If your filter predicates really are just leaving with a tiny proportion of available rows, you need to find out why an index on C.PAT_KEY is not being used.

Ross Leishman
Re: Help with problem query [message #488518 is a reply to message #488396] Sat, 08 January 2011 10:01 Go to previous message
indrajit2002
Messages: 53
Registered: November 2007
Location: INDIA
Member
Hi Ross,
Sorry for replying late but your suggestion has really helped me a lot. I executed the query with the hint that you have provided and it took only 41 seconds to run. It was really unbeleivable.
Thanks a lot for all your help.
Previous Topic: Disk Performance Issues
Next Topic: performance issue
Goto Forum:
  


Current Time: Fri Mar 29 09:17:07 CDT 2024