-
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
;
/******************************************************************/
-
of course it ignores your hint, it's the wrong syntax.
Try /*+ RULE */ instead.
Jeff Hunter
-
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 06:54 PM.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
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
-
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?
-
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
-
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"
-
Thanks buddy for ur advice....
But can you suggest me which things will improve the performance of this query??
-
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|