Sql statement takes time in Package....
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: Sql statement takes time in Package....

  1. #1
    Join Date
    May 2001
    Location
    London
    Posts
    149

    Sql statement takes time in Package....

    Dear All,

    Following query works fine in SQLplus...it takes around 90 secs to execute but when I put it in Package, it runs forever...
    I know that in package it ignores the RULE hint, thats why it takes time to run..Is there any way I can optimize this sql statement in Package.

    Thanks in advance,

    Paresh

    /*****************************************************************/

    SELECT /*+RULE*/
    PEIT.PEF_FUND_REF_ID PEF_IVD_FUND_REF_ID
    ,CONT.PCT_ID PEF_IVD_PEI_CONTACT_ID
    ,FTR.JS_LEGAL_NO PEF_IVD_LEGAL_NO
    ,STK_PFOL.JS_PFOL_NO PEF_IVD_PFOL_NO
    ,PFOL_NAME.JS_NAME PEF_IVD_PORTFOLIO_NAME
    ,FTR.JS_SECURITYID PEF_IVD_SEDOL_NO
    ,STK_NAME.JS_INV_NAME PEF_IVD_INV_NAME
    ,CCY_NAME.CURRENCY_SHORT_CODE PEF_IVD_INV_CURR_NAME
    ,SUM(DECODE(FTR.JSLOGIC_ISCAPEVENT,1
    ,DECODE(FTR.JS_METHOD,5,0,FTR.JS_NUM_UNITS),FTR.JS_NUM_UNITS))
    PEF_IVD_INVESTOR_UNITS_HELD
    ,SUM(DECODE(FTR.JSLOGIC_ISCAPEVENT,1
    ,DECODE(FTR.JS_METHOD,5,0,FTR.JS_BKC_TOTAL),FTR.JS_BKC_TOTAL))
    PEF_IVD_INVESTOR_COST_VALUE
    ,0 PEF_IVD_TOT_UNITS_HELD
    ,0 PEF_IVD_TOT_COST_VALUE
    ,SUM(DECODE(FTR.JSLOGIC_ISCAPEVENT,1
    ,DECODE(FTR.JS_METHOD,5,FTR.JS_BKC_TOTAL),0))
    PEF_IVD_PFOLIO_REVAL_COST
    ,0 PEF_IND_FUND_REVAL_COST
    FROM
    STK_LDGR@JSORA FTR
    ,CHS_REFERENCE_CODE CRC
    -- ,PEF_PEI_TRUSTS PEIT
    ,STOCKS@JSORA STK_NAME
    ,CHS_TRUST_ROLE_ALLOCATIONS CONT --CONTACTS
    ,STK_HOLD@JSORA STK_PFOL
    ,PFOLIO@JSORA PFOL_NAME
    ,PEF_CURRENCY CCY_NAME
    ,(SELECT
    PEIT.PEF_PEI_TRUST_REF_ID
    ,PEIT.PEF_TRUST_TYPE_ID
    ,HLP.PEF_FUND_REF_ID
    FROM
    PEF_PEI_TRUSTS PEIT
    ,PEF_PEI_TRUSTS HLP
    WHERE
    PEIT.PEF_TRUST_TYPE_ID = 21
    AND PEIT.PEF_FUND_REF_ID = HLP.PEF_PEI_TRUST_REF_ID
    ) PEIT
    WHERE
    PEIT.PEF_PEI_TRUST_REF_ID = CRC.REFERENCE_CODE
    AND FTR.JS_LEGAL_NO = CRC.JS_REF
    AND STK_NAME.JS_SECURITYID = FTR.JS_SECURITYID
    AND TRUST_ROLE_ALLOC_ID > 0 --REDUNDANT OPTIMIZER CONDITION
    AND CONT.TRUST_ROLE_ID IN (36,37,38,39,40,41,42,43,44,45) --NEW ROLES FOR PEF CONTACTS
    AND CRC.PCT_ID = CONT.TRUST_PCT_ID
    AND STK_PFOL.JS_SECURITYID = STK_NAME.JS_SECURITYID
    AND PFOL_NAME.JS_LEGAL_NO = STK_PFOL.JS_LEGAL_NO
    AND PFOL_NAME.JS_PFOL_NO = STK_PFOL.JS_PFOL_NO
    AND CCY_NAME.CURRENCY_CODE = STK_NAME.JS_CURRENCY
    GROUP BY
    PEIT.PEF_FUND_REF_ID
    ,CONT.PCT_ID
    ,FTR.JS_SECURITYID
    ,FTR.JS_LEGAL_NO
    ,STK_PFOL.JS_PFOL_NO
    ,PFOL_NAME.JS_NAME
    ,STK_NAME.JS_INV_NAME
    ,CCY_NAME.CURRENCY_SHORT_CODE
    ;

    /******************************************************************/

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    of course it ignores your hint, it's the wrong syntax.

    Try /*+ RULE */ instead.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439

    Re: Sql statement takes time in Package....

    Originally posted by pareshg
    SELECT /*+RULE*/ ...
    Originally posted by marist89
    of course it ignores your hint, it's the wrong syntax.

    Try /*+ RULE */ instead.
    Jeff, what's wrong with pareshg's hint syntax? AFAIK it is not required to put blank between "/*+" and the first hint keyword. So /*+RULE*/ is syntacticaly exactly as good as /*+ RULE */.

    However there might be some salt in what you said. Not because the sintax is wrong, it's because there is a bug in some earlier releases related to this. Namely, in some releases the SQL parser in PL/SQL replaces the first character after the "/*+" with blank. So in PL/SQL, after the parsing of the following statement
    Code:
    SELECT /*+RULE*/ ...
    it actualy becomes
    Code:
    SELECT /*+ ULE*/ ...
    and of course this suddenly becomes a no-hint, an ordinary comment. This happened only in queries inside the PL/SQL, the same queries executed directly in SQL happily recognized the hint and used it.

    I personaly was hit by that bug, I'm not sure if it was on 7.3.4 or on 8.1.5. The workaround of course was to put an extra space between the "/*+" and the actual hint. Anyway, the bug number is 697121 and is fixed in 8.1.7 and above.
    Last edited by jmodic; 11-13-2003 at 05:54 PM.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    While the docs say...
    The space between the plus sign and the hint is optional. If the comment contains multiple hints, separate the hints by at least one space.
    ... that has not been my experience. If your hint isn't working, try the space.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  5. #5
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    More interestingly, there is the question of why the RULE hint makes the query run faster. Presumably the cost-based optimizer is over-estimating the cost of index access, wich the RBO tends to favour.

    Have you looked into this issue at all?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  6. #6
    Join Date
    May 2001
    Location
    London
    Posts
    149
    Jeff & Jurij,

    Is there any way by changing the hint in the sql statement of the package, the package will run fast??

    Also can you explain why does it run fast, if I put Rule hint in sql statement?

    Thanks

    Paresh

  7. #7
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Read Performace Guide
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  8. #8
    Join Date
    May 2001
    Location
    London
    Posts
    149
    Thanks buddy for ur advice....

    But can you suggest me which things will improve the performance of this query??

  9. #9
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447

    Re: Re: Sql statement takes time in Package....

    Originally posted by jmodic
    I personaly was hit by that bug, I'm not sure if it was on 7.3.4 or on 8.1.5. The workaround of course was to put an extra space between the "/*+" and the actual hint. Anyway, the bug number is 697121 and is fixed in 8.1.7 and above.

    not sure if thatīs fixed, last week I saw some funny hints in some PL/SQL package in 8.1.7.4 (Solaris)

    SELECT /*+INDEX(X A) */ * from X ---> inside package

    after sql tracing it I see in the trace file

    SELECT /*+ NDEX(X A) */ * FROM X

  10. #10
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by pareshg
    Thanks buddy for ur advice....

    But can you suggest me which things will improve the performance of this query??
    Wass ur Driving table? ( is it the table on Local DB or Remote )

    Check this link on how oracle evaluates the plan in respect to Remote Operations
    Remote Operations

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width