Hi All!
We are using Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 on AIX 5.3
There is a customized report developed by the vendor in which he called a procedure. There is a select statement used in a cursor, the report executed successfully, but we found the very poor performance of the same, the cose is about 254,000.
I want to tune the select statement of the procedure, when I tried to view the execution plan in PL/SQL developer I found the subjected error:
Below is the quer
SELECT 'TB_LINES' DATA_TYPE,
GJL.LEDGER_ID,
GCC.CHART_OF_ACCOUNTS_ID,
GJH.CURRENCY_CODE,
SEGMENT1,
SEGMENT2,
SEGMENT3,
SEGMENT4,
SEGMENT5,
SEGMENT6,
SEGMENT7,
SEGMENT8,
SEGMENT9,
SEGMENT10,
SEGMENT11,
SEGMENT12,
SEGMENT13,
SEGMENT14,
SEGMENT15,
SUM(NVL(GJL.ENTERED_DR, 0)) ENTERED_DEBIT,
SUM(NVL(GJL.ENTERED_CR, 0)) ENTERED_CREDIT,
SUM(NVL(GJL.ACCOUNTED_DR, 0)) ACCOUNTED_DEBIT,
SUM(NVL(GJL.ACCOUNTED_CR, 0)) ACCOUNTED_CREDIT
FROM APPS.GL_JE_HEADERS GJH,
APPS.GL_JE_LINES GJL,
APPS.GL_CODE_COMBINATIONS GCC,
APPS.FND_FLEX_VALUES FFV,
APPS.FND_ID_FLEX_SEGMENTS FIFS
WHERE GJH.JE_HEADER_ID = GJL.JE_HEADER_ID
AND GJL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID(+)
AND GJL.STATUS = 'P'
AND FFV.FLEX_VALUE =
(CASE FIFS.APPLICATION_COLUMN_NAME WHEN 'SEGMENT1' THEN GCC.SEGMENT1 WHEN
'SEGMENT2' THEN GCC.SEGMENT2 WHEN 'SEGMENT3' THEN GCC.SEGMENT3 WHEN
'SEGMENT4' THEN GCC.SEGMENT4 WHEN 'SEGMENT5' THEN GCC.SEGMENT5 WHEN
'SEGMENT6' THEN GCC.SEGMENT6 WHEN 'SEGMENT7' THEN GCC.SEGMENT7 WHEN
'SEGMENT8' THEN GCC.SEGMENT8 WHEN 'SEGMENT9' THEN GCC.SEGMENT9 WHEN
'SEGMENT10' THEN GCC.SEGMENT10 WHEN 'SEGMENT11' THEN GCC.SEGMENT11 WHEN
'SEGMENT12' THEN GCC.SEGMENT12 WHEN 'SEGMENT13' THEN GCC.SEGMENT13 WHEN
'SEGMENT14' THEN GCC.SEGMENT14 ELSE GCC.SEGMENT15 END)
AND FFV.FLEX_VALUE_SET_ID = FIFS.FLEX_VALUE_SET_ID
AND FIFS.ID_FLEX_NUM = GCC.CHART_OF_ACCOUNTS_ID
AND FIFS.ID_FLEX_CODE = 'GL#'
AND FIFS.ENABLED_FLAG = 'Y'
AND FIFS.APPLICATION_COLUMN_NAME = '&LV_ACCOUNT_SEG'
/*
(
SELECT FSAV.APPLICATION_COLUMN_NAME
FROM APPS.FND_SEGMENT_ATTRIBUTE_VALUES FSAV
WHERE FSAV.ID_FLEX_CODE = 'GL#'
AND FSAV.SEGMENT_ATTRIBUTE_TYPE = 'GL_ACCOUNT'
AND FSAV.ATTRIBUTE_VALUE = 'Y'
AND FSAV.ID_FLEX_NUM = GCC.CHART_OF_ACCOUNTS_ID
)
*/
AND GJH.ACTUAL_FLAG = NVL('&P_BALANCE_TYPE', GJH.ACTUAL_FLAG)
AND GJL.LEDGER_ID = NVL('&P_LEDGER_ID', GJL.LEDGER_ID)
AND UPPER(GJH.CURRENCY_CODE) =
UPPER(NVL('&P_CURRENCY_TYPE', GJH.CURRENCY_CODE))
AND GCC.CHART_OF_ACCOUNTS_ID =
NVL('&P_CHART_OF_ACCOUNTS_ID', GCC.CHART_OF_ACCOUNTS_ID)
AND GJL.EFFECTIVE_DATE >=
(CASE WHEN
SUBSTR(FFV.COMPILED_VALUE_ATTRIBUTES, 5, 1) = 'E' OR
SUBSTR(FFV.COMPILED_VALUE_ATTRIBUTES, 5, 1) = 'R'
/* START NEW LINES */
OR
('&LV_RET_EARN_ACCOUNT_NUM' =
(CASE '&LV_ACCOUNT_SEG' WHEN 'SEGMENT1' THEN GCC.SEGMENT1 WHEN
'SEGMENT2' THEN GCC.SEGMENT2 WHEN 'SEGMENT3' THEN GCC.SEGMENT3 WHEN
'SEGMENT4' THEN GCC.SEGMENT4 WHEN 'SEGMENT5' THEN GCC.SEGMENT5 WHEN
'SEGMENT6' THEN GCC.SEGMENT6 WHEN 'SEGMENT7' THEN GCC.SEGMENT7 WHEN
'SEGMENT8' THEN GCC.SEGMENT8 WHEN 'SEGMENT9' THEN GCC.SEGMENT9 WHEN
'SEGMENT10' THEN GCC.SEGMENT10 WHEN 'SEGMENT11' THEN GCC.SEGMENT11 WHEN
'SEGMENT12' THEN GCC.SEGMENT12 WHEN 'SEGMENT13' THEN GCC.SEGMENT13 WHEN
'SEGMENT14' THEN GCC.SEGMENT14 ELSE GCC.SEGMENT15 END) AND
'&P_YEAR_END_CLOSED' = 'N')
/* END NEW LINES */
THEN TRUNC('&LV_YEAR_START_DATE')
/*
(
SELECT TRUNC(YEAR_START_DATE)
FROM APPS.GL_PERIODS
WHERE PERIOD_NAME = P_PERIOD
AND PERIOD_SET_NAME =
(
SELECT PERIOD_SET_NAME
FROM APPS.GL_LEDGERS
WHERE LEDGER_ID = GJL.LEDGER_ID
)
)
*/
ELSE TO_DATE('01-JAN-1000', 'DD-MON-YYYY') END)
AND GJL.EFFECTIVE_DATE <=
TO_CHAR(TO_DATE('&P_AS_ON_DATE', 'YYYY/MM/DD HH24:MI:SS'),
'DD-MON-YYYY')
AND NVL(GCC.SEGMENT1, '%') BETWEEN
NVL('&LV_SEG1_FROM', NVL(GCC.SEGMENT1, '%')) AND
NVL('&LV_SEG1_TO', NVL(GCC.SEGMENT1, '%'))
AND NVL(GCC.SEGMENT2, '%') BETWEEN
NVL('&LV_SEG2_FROM', NVL(GCC.SEGMENT2, '%')) AND
NVL('&LV_SEG2_TO', NVL(GCC.SEGMENT2, '%'))
AND NVL(GCC.SEGMENT3, '%') BETWEEN
NVL('&LV_SEG3_FROM', NVL(GCC.SEGMENT3, '%')) AND
NVL('&LV_SEG3_TO', NVL(GCC.SEGMENT3, '%'))
AND NVL(GCC.SEGMENT4, '%') BETWEEN
NVL('&LV_SEG4_FROM', NVL(GCC.SEGMENT4, '%')) AND
NVL('&LV_SEG4_TO', NVL(GCC.SEGMENT4, '%'))
AND NVL(GCC.SEGMENT5, '%') BETWEEN
NVL('&LV_SEG5_FROM', NVL(GCC.SEGMENT5, '%')) AND
NVL('&LV_SEG5_TO', NVL(GCC.SEGMENT5, '%'))
AND NVL(GCC.SEGMENT6, '%') BETWEEN
NVL('&LV_SEG6_FROM', NVL(GCC.SEGMENT6, '%')) AND
NVL('&LV_SEG6_TO', NVL(GCC.SEGMENT6, '%'))
AND NVL(GCC.SEGMENT7, '%') BETWEEN
NVL('&LV_SEG7_FROM', NVL(GCC.SEGMENT7, '%')) AND
NVL('&LV_SEG7_TO', NVL(GCC.SEGMENT7, '%'))
AND NVL(GCC.SEGMENT8, '%') BETWEEN
NVL('&LV_SEG8_FROM', NVL(GCC.SEGMENT8, '%')) AND
NVL('&LV_SEG8_TO', NVL(GCC.SEGMENT8, '%'))
AND NVL(GCC.SEGMENT9, '%') BETWEEN
NVL('&LV_SEG9_FROM', NVL(GCC.SEGMENT9, '%')) AND
NVL('&LV_SEG9_TO', NVL(GCC.SEGMENT9, '%'))
AND NVL(GCC.SEGMENT10, '%') BETWEEN
NVL('&LV_SEG10_FROM', NVL(GCC.SEGMENT10, '%')) AND
NVL('&LV_SEG10_TO', NVL(GCC.SEGMENT10, '%'))
AND NVL(GCC.SEGMENT11, '%') BETWEEN
NVL('&LV_SEG11_FROM', NVL(GCC.SEGMENT11, '%')) AND
NVL('&LV_SEG11_TO', NVL(GCC.SEGMENT11, '%'))
AND NVL(GCC.SEGMENT12, '%') BETWEEN
NVL('&LV_SEG12_FROM', NVL(GCC.SEGMENT12, '%')) AND
NVL('&LV_SEG12_TO', NVL(GCC.SEGMENT12, '%'))
AND NVL(GCC.SEGMENT13, '%') BETWEEN
NVL('&LV_SEG13_FROM', NVL(GCC.SEGMENT13, '%')) AND
NVL('&LV_SEG13_TO', NVL(GCC.SEGMENT13, '%'))
AND NVL(GCC.SEGMENT14, '%') BETWEEN
NVL('&LV_SEG14_FROM', NVL(GCC.SEGMENT14, '%')) AND
NVL('&LV_SEG14_TO', NVL(GCC.SEGMENT14, '%'))
AND NVL(GCC.SEGMENT15, '%') BETWEEN
NVL('&LV_SEG15_FROM', NVL(GCC.SEGMENT15, '%')) AND
NVL('&LV_SEG15_TO', NVL(GCC.SEGMENT15, '%'))
AND GJL.PERIOD_NAME NOT IN
(SELECT PERIOD_NAME
FROM APPS.GL_PERIODS
WHERE TO_CHAR(TO_DATE('&P_AS_ON_DATE', 'YYYY/MM/DD HH24:MI:SS'),
'DD-MON-YYYY') BETWEEN TRUNC(START_DATE) AND
TRUNC(END_DATE)
AND PERIOD_NUM > '&LV_PERIOD_NUM'
/*
(
SELECT PERIOD_NUM FROM APPS.GL_PERIODS WHERE PERIOD_NAME = P_PERIOD AND PERIOD_SET_NAME =
(
SELECT PERIOD_SET_NAME FROM APPS.GL_LEDGERS WHERE LEDGER_ID = GJL.LEDGER_ID
)
)
*/
)
GROUP BY GJL.LEDGER_ID,
GCC.CHART_OF_ACCOUNTS_ID,
GJH.CURRENCY_CODE,
SEGMENT1,
SEGMENT2,
SEGMENT3,
SEGMENT4,
SEGMENT5,
SEGMENT6,
SEGMENT7,
SEGMENT8,
SEGMENT9,
SEGMENT10,
SEGMENT11,
SEGMENT12,
SEGMENT13,
SEGMENT14,
SEGMENT15
UNION ALL
SELECT 'TB_RE' DATA_TYPE,
GLB.LEDGER_ID,
GCC.CHART_OF_ACCOUNTS_ID,
GLB.CURRENCY_CODE,
GCC.SEGMENT1,
GCC.SEGMENT2,
GCC.SEGMENT3,
GCC.SEGMENT4,
GCC.SEGMENT5,
GCC.SEGMENT6,
GCC.SEGMENT7,
GCC.SEGMENT8,
GCC.SEGMENT9,
GCC.SEGMENT10,
GCC.SEGMENT11,
GCC.SEGMENT12,
GCC.SEGMENT13,
GCC.SEGMENT14,
GCC.SEGMENT15,
SUM(NVL(GLB.BEGIN_BALANCE_DR, 0)) ENTERED_DEBIT,
SUM(NVL(GLB.BEGIN_BALANCE_CR, 0)) ENTERED_CREDIT,
SUM(NVL(GLB.BEGIN_BALANCE_DR_BEQ, 0)) ACCOUNTED_DEBIT,
SUM(NVL(GLB.BEGIN_BALANCE_CR_BEQ, 0)) ACCOUNTED_CREDIT
FROM APPS.GL_BALANCES GLB, APPS.GL_CODE_COMBINATIONS GCC
WHERE GLB.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
AND GLB.LEDGER_ID = NVL('&P_LEDGER_ID', GLB.LEDGER_ID)
AND GCC.CHART_OF_ACCOUNTS_ID =
NVL('&P_CHART_OF_ACCOUNTS_ID', GCC.CHART_OF_ACCOUNTS_ID)
--AND GLB.PERIOD_NAME = NVL(P_PERIOD, GLB.PERIOD_NAME)
/* START NEW LINES */
AND GLB.PERIOD_YEAR = '&LV_PERIOD_YEAR'
AND GLB.PERIOD_NUM = 1
/* END NEW LINES */
AND GLB.ACTUAL_FLAG = NVL('&P_BALANCE_TYPE', GLB.ACTUAL_FLAG)
AND GLB.CURRENCY_CODE = NVL('&P_CURRENCY_TYPE', GLB.CURRENCY_CODE)
AND 'N' = '&P_YEAR_END_CLOSED'
AND '&LV_RET_EARN_ACCOUNT_NUM' =
(CASE '&LV_ACCOUNT_SEG' WHEN 'SEGMENT1' THEN GCC.SEGMENT1 WHEN
'SEGMENT2' THEN GCC.SEGMENT2 WHEN 'SEGMENT3' THEN GCC.SEGMENT3 WHEN
'SEGMENT4' THEN GCC.SEGMENT4 WHEN 'SEGMENT5' THEN GCC.SEGMENT5 WHEN
'SEGMENT6' THEN GCC.SEGMENT6 WHEN 'SEGMENT7' THEN GCC.SEGMENT7 WHEN
'SEGMENT8' THEN GCC.SEGMENT8 WHEN 'SEGMENT9' THEN GCC.SEGMENT9 WHEN
'SEGMENT10' THEN GCC.SEGMENT10 WHEN 'SEGMENT11' THEN GCC.SEGMENT11 WHEN
'SEGMENT12' THEN GCC.SEGMENT12 WHEN 'SEGMENT13' THEN GCC.SEGMENT13 WHEN
'SEGMENT14' THEN GCC.SEGMENT14 ELSE GCC.SEGMENT15 END)
GROUP BY GLB.LEDGER_ID,
GCC.CHART_OF_ACCOUNTS_ID,
GLB.CURRENCY_CODE,
GCC.SEGMENT1,
GCC.SEGMENT2,
GCC.SEGMENT3,
GCC.SEGMENT4,
GCC.SEGMENT5,
GCC.SEGMENT6,
GCC.SEGMENT7,
GCC.SEGMENT8,
GCC.SEGMENT9,
GCC.SEGMENT10,
GCC.SEGMENT11,
GCC.SEGMENT12,
GCC.SEGMENT13,
GCC.SEGMENT14,
GCC.SEGMENT15
ORDER BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19
There are Indexes already created on the tables calling in the above SELECT.
Regards,
Hassan