A reader, December 08, 2014 - 4:55 pm UTC
The query itself is bad here. No indexes. No predicates in the query. It will do full table scan on all tables used here.
Test
A reader, December 09, 2014 - 7:18 am UTC
tets
A reader, December 09, 2014 - 10:09 am UTC
Tom,
Report would not pull 1.1. Billion data. There are many conditions in the query which restricts the records. Report might be using max of 1k of records. I made an attempt to explain you the bottleneck we face is in the UNION ALL part.
It would be like this, we have 3 users and when I run the reports from user1 the performance is good and retrieving the data as much as possible but when I run the report in user2 (view of objects user1 and user3), the performance is very slow, the GL_BLANACES view is taking too much time as mentioned below in the explain plan.
Here is the SQL report Query,
SELECT /*+ ORDERED USE_NL(ls l2 bal) INDEX(per GL_PERIOD_STATUSES_U4)
INDEX(l GL_LEDGERS_U2) INDEX(ls GL_LEDGER_SET_ASSIGNMENTS_N1)
INDEX(l2 GL_LEDGERS_U2) INDEX(bal GL_BALANCES_N1) */
l2.NAME ledger_name, l2.ledger_id ledger_id,
MAX (bal.period_name) period_name,
SUM (NVL (bal.begin_balance_dr, 0)) begin_dr,
SUM (NVL (bal.begin_balance_cr, 0)) begin_cr,
SUM (NVL (bal.begin_balance_dr, 0) + NVL (bal.period_net_dr, 0)
) end_dr,
SUM (NVL (bal.begin_balance_cr, 0) + NVL (bal.period_net_cr, 0)
) end_cr
FROM gl_ledgers l,
gl_ledger_set_assignments ls,
gl_ledgers l2,
gl_period_statuses per,
gl_code_combinations cc,
gl_balances bal
WHERE cc.segment1 = '01'
AND cc.segment2 BETWEEN '000' AND '000'
AND cc.segment3 = '1000'
AND cc.segment4 = 'T'
AND cc.segment5 = 'T'
AND cc.chart_of_accounts_id = 101
AND l.ledger_id = 1
AND ls.ledger_set_id(+) = l.ledger_id
AND l2.ledger_id = NVL (ls.ledger_id, l.ledger_id)
AND l2.currency_code = 'USD'
AND bal.ledger_id IN (SELECT acc.ledger_id
FROM gl_access_set_ledgers acc
WHERE acc.access_set_id = 1017)
AND per.application_id = 101
AND per.ledger_id = l2.ledger_id
AND per.effective_period_num BETWEEN 19980005
AND 20100009
AND bal.code_combination_id = cc.code_combination_id
AND bal.period_name = per.period_name
AND bal.actual_flag = 'A'
AND 1 = 1
AND bal.currency_code = 'USD'
AND bal.translated_flag IS NULL
AND bal.ledger_id = l2.ledger_id
--AND bal.template_id IS NULL
GROUP BY l2.NAME, l2.ledger_id, per.period_year, per.period_num
ORDER BY l2.NAME, per.period_year, per.period_num
Explain Plan,
PLAN_TABLE_OUTPUT
Plan hash value: 3342997214
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 214 | 7564K (1)| 25:12:54 |
| 1 | SORT GROUP BY | | 1 | 214 | 7564K (1)| 25:12:54 |
| 2 | NESTED LOOPS | | 1 | 214 | 7564K (1)| 25:12:54 |
| 3 | MERGE JOIN CARTESIAN | | 1 | 127 | 61 (2)| 00:00:01 |
| 4 | NESTED LOOPS | | | | | |
| 5 | NESTED LOOPS | | 3 | 282 | 37 (3)| 00:00:01 |
| 6 | NESTED LOOPS | | 1 | 66 | 4 (25)| 00:00:01 |
| 7 | NESTED LOOPS OUTER | | 1 | 38 | 3 (34)| 00:00:01 |
| 8 | MERGE JOIN CARTESIAN | | 1 | 30 | 2 (50)| 00:00:01 |
| 9 | SORT UNIQUE | | 1 | 26 | 1 (0)| 00:00:01 |
|* 10 | INDEX RANGE SCAN | GL_ACCESS_SET_LEDGERS_U1 | 1 | 26 | 1 (0)| 00:00:01 |
| 11 | BUFFER SORT | | 1 | 4 | 1 (100)| 00:00:01 |
|* 12 | INDEX UNIQUE SCAN | GL_LEDGERS_U2 | 1 | 4 | 0 (0)| 00:00:01 |
|* 13 | INDEX RANGE SCAN | GL_LEDGER_SET_ASSIGNMENTS_N1 | 1 | 8 | 1 (0)| 00:00:01 |
|* 14 | TABLE ACCESS BY INDEX ROWID| GL_LEDGERS | 1 | 28 | 1 (0)| 00:00:01 |
|* 15 | INDEX UNIQUE SCAN | GL_LEDGERS_U2 | 1 | | 0 (0)| 00:00:01 |
|* 16 | INDEX RANGE SCAN | GL_PERIOD_STATUSES_U4 | 91 | | 2 (0)| 00:00:01 |
| 17 | TABLE ACCESS BY INDEX ROWID | GL_PERIOD_STATUSES | 91 | 2548 | 33 (0)| 00:00:01 |
| 18 | BUFFER SORT | | 1 | 33 | 28 (4)| 00:00:01 |
|* 19 | TABLE ACCESS BY INDEX ROWID | GL_CODE_COMBINATIONS | 1 | 33 | 8 (0)| 00:00:01 |
|* 20 | INDEX RANGE SCAN | GL_CODE_COMBINATIONS_N3 | 44 | | 1 (0)| 00:00:01 |
|* 21 | VIEW | GL_BALANCES | 11 | 957 | 7564K (1)| 25:12:53 |
| 22 | UNION-ALL | | | | | |
|* 23 | FILTER | | | | | |
|* 24 | TABLE ACCESS BY INDEX ROWID | GL_BALANCES | 114K| 4253K| 7564K (1)| 25:12:53 |
| 25 | INDEX FULL SCAN | GL_BALANCES_N1 | 8222K| | 25083 (1)| 00:05:01 |
| 26 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
|* 27 | TABLE ACCESS BY INDEX ROWID | GL_BALANCES | 1 | 100 | 1 (0)| 00:00:01 |
| 28 | INDEX FULL SCAN | GL_BALANCES_N1 | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
10 - access("ACC"."ACCESS_SET_ID"=1017)
12 - access("L"."LEDGER_ID"=1)
13 - access("LS"."LEDGER_SET_ID"(+)=1)
14 - filter("L2"."CURRENCY_CODE"='USD')
15 - access("L2"."LEDGER_ID"=NVL("LS"."LEDGER_ID","L"."LEDGER_ID"))
16 - access("PER"."APPLICATION_ID"=101 AND "PER"."LEDGER_ID"="L2"."LEDGER_ID" AND
PLAN_TABLE_OUTPUT
"PER"."EFFECTIVE_PERIOD_NUM">=19980005 AND "PER"."EFFECTIVE_PERIOD_NUM"<=20100009)
19 - filter("CC"."SEGMENT2"='000' AND "CC"."SEGMENT4"='T' AND "CC"."SEGMENT5"='T' AND
"CC"."SEGMENT1"='01' AND "CC"."CHART_OF_ACCOUNTS_ID"=101)
20 - access("CC"."SEGMENT3"='1000')
21 - filter("BAL"."CODE_COMBINATION_ID"="CC"."CODE_COMBINATION_ID" AND
"BAL"."PERIOD_NAME"="PER"."PERIOD_NAME" AND "BAL"."LEDGER_ID"="L2"."LEDGER_ID" AND
"BAL"."LEDGER_ID"="ACC"."LEDGER_ID")
23 - filter( (SELECT UPPER(NVL("FND_PROFILE"."VALUE_WNPS"('A4SOAE_VIEW'),'Y')) FROM "SYS"."DUAL"
"DUAL")='Y')
24 - filter("CURRENCY_CODE"='USD' AND "ACTUAL_FLAG"='A' AND "TRANSLATED_FLAG" IS NULL)
27 - filter("TRANSLATED_FLAG" IS NULL AND "ACTUAL_FLAG"='A' AND "CURRENCY_CODE"='USD')
Note
-----
- dynamic sampling used for this statement (level=2)
December 09, 2014 - 5:58 pm UTC
I wish I could review questions - I'd give this question a zero star rating.
CREATE VIEW TABLE1_view AS
SELECT * FROM TABLE1
UNION ALL
SELECT * FROM TABLE2;
Schema1: In data almost same 1 billion of data’s.
Schema2: It has the view of the schema1 and schema3 data’s. As shown in above
Schema3: Almost 100 million of data’s.
forgive me for thinking you were pulling 1.1 billion rows. I have no idea how I could have made that conclusion.
OH WAIT
it is because you said you were - doh!give me a break.
step one for you: remove all of the hints. You are probably a huge part of the problem here
step two for you: read this:
http://www.oracle.com/technetwork/issue-archive/2014/14-nov/o64asktom-2298498.html step three for you: ask what you mean to ask, don't ask something 100% opposite of what you are asking. All you do is waste my time and some of your time, that's it. Nothing productive could have possibly come from your question. Nothing
and if you plan to post code here again, I beg you to use the CODE button on the user interface and put your code in the code tags. It is unreadable as you've posted above, I'm not even going to look at that plan.
But undoubtedly it is because you are forcing indexes if you have a query that is taking hours and it is using indexes, it is taking hours BECAUSE it is using indexes.
To the above question
Rajeshwaran, Jeyabal, December 09, 2014 - 12:55 pm UTC
December 09, 2014 - 6:26 pm UTC
thanks :)
ledger info
Chuck Jolley, January 05, 2015 - 5:00 pm UTC
This looks like a query that was written for apps 11 and updated for ledger functionality for apps 12.
Vidhyasekar, you should (after you take out the hints) look at the entire logic accessing the tables containing ledger info. It's a mess.
Also "AND 1 = 1" really?
Cooler, January 15, 2015 - 2:52 pm UTC
Isnt 'AND 1=1' a method to suppress the use of indexes thus forcing full table scan?
David Aldridge, February 18, 2015 - 11:44 am UTC
>> 'AND 1=1' a method to suppress the use of indexes thus forcing full table scan?
No, the optimiser will probably recognise that it is equal to "true" and transform the predicate out of the query execution.
Most commonly it's seen in queries that have been created by concatenating multiple predicates together, as it can obviate the need to work out whether to include an AND or not.