Home » RDBMS Server » Performance Tuning » Performance Issue (oracle 10g,windows)
Performance Issue [message #304415] Wed, 05 March 2008 01:53 Go to next message
ramanajv1968
Messages: 168
Registered: December 2005
Location: HYDERABAD
Senior Member
Hi,

I am facing a Performance issue with the below query.It is taking too much time even though all the columns
in the where clause of the below query are indexed and the below query is dynamically generated through java.

TableNmae --------No_of_recd
agnt_dim ----415884
clear_daily_fact_vw --- 3298510
PRDCT_DIM ----8446
cov_dim ---3165302
CLNT_DIM ---2098316

SELECT *
FROM   (SELECT A.PLCY_NBR,
               A.CO_CD,
               A.PLCY_STATUS_CD PLCY_STATUS_CD,
               TO_CHAR(B.RISK_COMMNCD_DT, 'YYYY-MM-DD') RISK_COMMNCD_DT,
               C.PLAN_TXT_DESC PRDCT_CATGRY_NAME,
               DECODE(B.PYMT_FREQ_CD, 'A', 1, 'M', 12, 'Q', 4, 'S', 2, 0) * B.TOTL_PREM_INSMNT_AMT CRRNT_ANNUAL_PREM_AMT,
               TO_CHAR(B.HOME_OFFC_RCVD_DT, 'YYYY-MM-DD') HOME_OFFC_RCVD_DT,
               C.BASIC_RIDER_IND,
               ROW_NUMBER() OVER(PARTITION BY B.PLCY_NBR, B.CO_CD ORDER BY C.BASIC_RIDER_IND, B.ELMNT_NBR DESC) SEQ_NBR
        FROM   CLEAR_DAILY_FACT_VW A,
               COV_DIM             B,
               PRDCT_DIM           C,
               CLNT_DIM            E
        WHERE  A.COV_DIM_ID = B.COV_DIM_ID 
        AND A.PRDCT_DIM_ID = C.PRDCT_DIM_ID 
        AND A.PLCY_STATUS_CD = 'A' 
        AND E.FIRST_NAME LIKE 'AL%' 
        AND A.CLNT_REF_NBR = E.CLNT_REF_NBR 
        AND (B.SVCNG_AGNT_NBR IN (SELECT AGNT_NBR FROM AGNT_DIM WHERE 1 = 1 
                                  AND AGNT_FIRST_NAME LIKE 'RE%')
            )
       )
WHERE  SEQ_NBR = 1
ORDER  BY PLCY_NBR,
          CO_CD


Any suggestions please.
Re: Performance Issue [message #304523 is a reply to message #304415] Wed, 05 March 2008 08:50 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
Remove "CLNT_DIM E" out of FROM clause because it contributes no data to the SELECT clause.
Subordinate it into the WHERE clause using EXISTS

Re: Performance Issue [message #304530 is a reply to message #304415] Wed, 05 March 2008 09:20 Go to previous message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Quote:
It is taking too much time even though all the columns
in the where clause of the below query are indexed and the below query is dynamically generated through java

Quantify your statement when you say it is taking too much time what's the time taken by your query to execute.

How about doing/answering the following questions/queries:
a) Posting the explain plan of your query
b) Are the stats updated
c) Going through the performance tuning sticky link in this forum
d) How many records you are expecting

Regards

Raj
Previous Topic: query hangs on order by
Next Topic: Tuning Suggestion
Goto Forum:
  


Current Time: Wed Dec 07 22:13:41 CST 2016

Total time taken to generate the page: 0.12812 seconds