Query:
SELECT (SELECT ffv.attribute6
FROM apps.fnd_flex_values ffv
WHERE ffv.flex_value = gcc.segment1
AND ffv.attribute6 IS NOT NULL
AND ffv.flex_value_set_id = '1003330') "Company Parent",
gcc.segment1 Company,
gcc.segment2 LOB,
gcc.segment3 Location,
gcc.segment4 Dept,
gcc.segment5 Account,
gcc.segment6 Product,
gcc.segment7 Project,
gcc.segment8 Intercompany,
NVL((SELECT ffv.attribute4
FROM apps.fnd_flex_values ffv
WHERE ffv.flex_value = gcc.segment5
AND ffv.attribute4 IS NOT NULL
AND ffv.flex_value_set_id = '1003334'),'No') "Interest Bearing",
bal.period_name "Period Name",
CASE WHEN gcc.segment5 >= '4000000'
THEN (NVL(bal.period_net_dr,0) - NVL(bal.period_net_cr,0))
ELSE ( NVL(bal.begin_balance_dr,0) - NVL(bal.begin_balance_cr,0) + NVL(bal.period_net_dr,0) - NVL(bal.period_net_cr,0) )
END "Actual Balance",
CASE WHEN bal.set_of_books_id = 41
THEN (((bal.period_net_dr - bal.period_net_cr) + (SELECT B.period_net_dr - B.period_net_cr
FROM apps.gl_balances B
WHERE B.code_combination_id = bal.code_combination_id
AND B.period_name = TO_CHAR(ADD_MONTHS(TO_DATE('01-'||bal.period_name,'DD-MON-RRRR'),-1),'MON-RR')
AND B.set_of_books_id = bal.set_of_books_id
AND B.actual_flag = 'A'
AND B.currency_code = 'USD')) / 2)
ELSE (SELECT period_average_to_date
FROM apps.gl_daily_balances_v A
WHERE A.accounting_date = last_day('01-'||bal.period_name)
AND A.code_combination_id = bal.code_combination_id
AND A.period_set_name = 'CALENDAR'
AND A.currency_type = 'U'
AND A.currency_code = 'USD'
AND A.set_of_books_id = bal.set_of_books_id)
END "Average balance",
bal.currency_code "Currency Code",
bal.set_of_books_id "SOB ID"
FROM apps.gl_balances bal,
apps.gl_code_combinations gcc,
apps.gl_periods period
WHERE bal.code_combination_id = gcc.code_combination_id
AND period.period_name=bal.period_name
AND gcc.segment5 in (SELECT DISTINCT ACCOUNT FROM apps.etfn_gl_ac_map)
/* etfn_gl_ac_map contains our custom data, as you can see GL accounts, so basically we wanted to restrict fetched data to Accounts that exist in our custom table. There are around 3000 accounts in etfn_gl_ac_map custom table. */
AND period.start_date >= '01-'||(SELECT upper(flv.meaning)
FROM apps.fnd_lookup_types flt,
apps.fnd_lookup_values flv
WHERE flt.lookup_type = 'ETFN_GL_DISTPLAN_PERIOD'
AND flv.language = 'US'
AND flt.lookup_type = flv.lookup_type)
/* Returns 'JAN-09' , hence period.start_date>= '01-JAN-09' */
AND bal.actual_flag = 'A'
AND bal.currency_code = 'USD'
AND gcc.chart_of_accounts_id = 101
AND (( bal.set_of_books_id = 4
AND gcc.segment1 < '2000'
AND gcc.segment1 NOT IN (SELECT upper(lp.lookup_code)
FROM apps.fnd_lookup_values_vl lp
WHERE lp.lookup_type = 'ETFN_HYP_BANK_EXCLUSIONS'
AND lp.start_date_active <= SYSDATE
AND nvl(lp.end_date_active,SYSDATE) = SYSDATE
AND nvl(lp.enabled_flag, 'Y') = 'Y') )
OR (bal.set_of_books_id = 41 AND gcc.segment1 > '1999')
OR (bal.set_of_books_id = 41 AND gcc.segment1 IN (SELECT upper(lp.lookup_code)
FROM apps.fnd_lookup_values_vl lp
WHERE lp.lookup_type = 'ETFN_HYP_BANK_EXCLUSIONS'
AND lp.start_date_active <= SYSDATE
AND nvl(lp.end_date_active,SYSDATE) = SYSDATE
AND nvl(lp.enabled_flag, 'Y') = 'Y') ))
AND period.period_set_name='CALENDAR';
Database: 10g Enterprise
Oracle Apps: 11.5.10.2
SQL> show parameter optimizer
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_optimizer_cost_based_transformation string OFF
optimizer_dynamic_sampling integer 2
optimizer_features_enable string 10.2.0.3
optimizer_index_caching integer 0
optimizer_index_cost_adj integer 100
optimizer_mode string all_rows
optimizer_secure_view_merging boolean TRUE
SQL> show parameter db_file_multi
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count integer 59
SQL> show parameter db_block_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_size integer 8192
SQL> show parameter cursor_sharing
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing string exact
SQL> select sname, pname, pval1, pval2 from sys.aux_stats$;
SNAME PNAME PVAL1 PVAL2
-------------------- -------------------- ---------- --------------------
SYSSTATS_INFO STATUS COMPLETED
SYSSTATS_INFO DSTART 09-13-2008 14:13
SYSSTATS_INFO DSTOP 09-13-2008 14:13
SYSSTATS_INFO FLAGS 1
SYSSTATS_MAIN CPUSPEEDNW 1138.0999
SYSSTATS_MAIN IOSEEKTIM 10
SYSSTATS_MAIN IOTFRSPEED 4096
SYSSTATS_MAIN SREADTIM
SYSSTATS_MAIN MREADTIM
SYSSTATS_MAIN CPUSPEED
SYSSTATS_MAIN MBRC
SNAME PNAME PVAL1 PVAL2
-------------------- -------------------- ---------- --------------------
SYSSTATS_MAIN MAXTHR
SYSSTATS_MAIN SLAVETHR
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 30 | 3870 | 18371 |
| 1 | TABLE ACCESS BY INDEX ROWID | FND_FLEX_VALUES | 1 | 20 | 2 |
| 2 | INDEX RANGE SCAN | FND_FLEX_VALUES_N1 | 1 | | 1 |
| 3 | TABLE ACCESS BY INDEX ROWID | FND_FLEX_VALUES | 1 | 20 | 2 |
| 4 | INDEX RANGE SCAN | FND_FLEX_VALUES_N1 | 1 | | 1 |
| 5 | TABLE ACCESS BY INDEX ROWID | GL_BALANCES | 1 | 29 | 7 |
| 6 | INDEX RANGE SCAN | GL_BALANCES_N1 | 3 | | 3 |
| 7 | HASH JOIN | | 1 | 223 | 9 |
| 8 | NESTED LOOPS | | 1 | 190 | 5 |
| 9 | TABLE ACCESS BY INDEX ROWID | FND_CURRENCIES | 1 | 21 | 1 |
| 10 | INDEX UNIQUE SCAN | FND_CURRENCIES_U1 | 1 | | 0 |
| 11 | TABLE ACCESS BY INDEX ROWID | GL_DAILY_BALANCES | 1 | 169 | 4 |
| 12 | INDEX RANGE SCAN | GL_DAILY_BALANCES_N1 | 2 | | 2 |
| 13 | INDEX SKIP SCAN | GL_DATE_PERIOD_MAP_U1 | 1 | 33 | 3 |
| 14 | FILTER | | | | |
| 15 | HASH JOIN RIGHT SEMI | | 686 | 88494 | 18367 |
| 16 | TABLE ACCESS FULL | ETFN_GL_AC_MAP | 3339 | 26712 | 10 |
| 17 | HASH JOIN | | 3538 | 418K| 18356 |
| 18 | NESTED LOOPS | | 3538 | 231K| 17323 |
| 19 | TABLE ACCESS BY INDEX ROWID| GL_PERIODS | 1 | 32 | 2 |
| 20 | INDEX UNIQUE SCAN | GL_PERIODS_U1 | 1 | | 1 |
| 21 | NESTED LOOPS | | 1 | 64 | 4 |
| 22 | INDEX RANGE SCAN | FND_LOOKUP_TYPES_U1 | 1 | 20 | 2 |
| 23 | INDEX RANGE SCAN | FND_LOOKUP_VALUES_U2 | 1 | 44 | 2 |
| 24 | TABLE ACCESS BY INDEX ROWID| GL_BALANCES | 3538 | 120K| 17321 |
| 25 | INDEX RANGE SCAN | GL_BALANCES_N2 | 304K| | 1334 |
| 26 | TABLE ACCESS FULL | GL_CODE_COMBINATIONS | 319K| 16M| 1030 |
| 27 | TABLE ACCESS BY INDEX ROWID | FND_LOOKUP_VALUES | 1 | 38 | 4 |
| 28 | INDEX RANGE SCAN | FND_LOOKUP_VALUES_U2 | 2 | | 3 |
| 29 | TABLE ACCESS BY INDEX ROWID | FND_LOOKUP_VALUES | 1 | 38 | 4 |
| 30 | INDEX RANGE SCAN | FND_LOOKUP_VALUES_U1 | 1 | | 3 |
-----------------------------------------------------------------------------------------
Note
-----
- 'PLAN_TABLE' is old version
Explanation: This query is used to extract data from Oracle GL and sent to Hyperion Planning for Forecasting Purposes.
Problem: This query extracts data starting 01/JAN/09 - till date. Its taking very long time to execute, at times more than an hour. Looks like its taking time when it access gl_balances and gl_code_combinations tables. Please help me to optimize this Query.
Any help is greatly appreciated!!
Regards
Abhi
Edited by: abhi_apps333 on Nov 24, 2009 11:37 AM
Edited by: abhi_apps333 on Nov 24, 2009 11:38 AM