Home -> Usenet -> comp.databases.oracle -> Query Performance Problem

Query Performance Problem

From: Jim <jimmy.liew_at_ogilvy.com>
Date: 23 Jul 2002 23:54:44 -0700
Message-ID: <e7d269ce.0207232254.1a6448ad_at_posting.google.com> 






I have a query which is taking forever to run. Once I ran it for 3
days before finally killing it. I realize that I am working on rather
big tables (table1 has 260000 rows, while table2 has 70 million,
table3 has only 2000) but the execution time is ridiculous



I have tried different hints such as ORDERED, USE_HASH without much
success.


I hope someone can give a clue as to how to tune this statement
properly


The query:



SELECT  ind_lbr_batch_number,

        ina.ina_qua_answer_uid   ,
        sum(case when ind.ind_parent_urn || ind.ind_suppress is null
then 1 else 0 end) Ars_Valid,
        sum(case when ind.ind_parent_urn || ind.ind_suppress is not
null then 1 else 0 end) Ars_Invalid,
        count(distinct ind_urn)  ars_total
        FROM  individuals ind, individual_answers ina, quest_life ql,
              (SELECT ina_ind_urn,
                      c.ord ord  ,
                      MAX(b.ina_inr_uid)   ina_inr_uid
                 FROM individual_answers b,
                     quest_life  c
                 where b.ina_qua_answer_uid = c.ans_uid
                 group by b.ina_ind_urn,
                 c.ord            )   mx
       where  ind.ind_urn= ina.ina_ind_urn
         and ina.ina_qua_answer_uid = ql.ans_uid
         and ind_urn = mx.ina_ind_urn
         AND ina.ina_inr_uid = mx.ina_inr_uid
         and ql.ord = mx.ord
         and ind.ind_oup_code || ind.ind_ctrl_cell is null


    GROUP BY ind_lbr_batch_number , ina.ina_qua_answer_uid



here is the plan:


Plan Table




| Operation                  |  Name    |  Rows | Bytes|  Cost  |  TQ 


|IN-OUT|





| SELECT STATEMENT           |          |    42K|    7M|4923856 |     


|      |

|  SORT GROUP BY             |          |    42K|    7M|4923856 |     


|      |

|   NESTED LOOPS             |          |    42K|    7M|4919186 |     


|      |

|    NESTED LOOPS            |          |    33M|    5G|4919185 |     


|      |

|     MERGE JOIN             |          |     6M|  877M|4261834 |     


|      |

|      SORT JOIN             |          |    68M|    2G|4259109 |     


|      |

|       VIEW                 |          |    68M|    2G|4259109 |     


|      |

|        SORT GROUP BY       |          |    68M|    3G|4259109 |     


|      |

|         HASH JOIN          |          |    70M|    4G|  43924 |     



|      |




Plan Table




|          TABLE ACCESS FULL |QUEST_LIF |     2K|   84K|      8 |     


|      |

|          TABLE ACCESS FULL |INDIVIDUA |    70M|    1G|  37810 |     


|      |

|      SORT JOIN             |          |    13K|    1M|   2725 |     


|      |

|       TABLE ACCESS FULL    |INDIVIDUA |    13K|    1M|   1914 |     


|      |

|     INDEX RANGE SCAN       |INA_PK_NE |    70M|    1G|      1 |     


|      |

|    INDEX RANGE SCAN        |QUL_PK    |     2K|   56K|        |     



|      |


Received on Wed Jul 24 2002 - 08:54:44 CEST












Original text of this message