Poor Performance of Posting: Single Ledger: Query#1

We were upgrading to Oracle database 12c. During regression testing we found that following sql is running very poorly for some JV Batches. Query is given below. If you look at the query you would notice that POSTING_RUN_ID is hard coded. It means this is a dynamic sql and Oracle developer had appended POSTING_RUN_ID to the query instead of using a bind variable. Biggest drawback of hard coded value: Every time sql is different, it has different sql_id, plan hash value and i can not pin a sqlplan for this query. This query works fine when a JE header has less number of lines. In our case it does not work very well when one header has 200k lines. it trying to access GL_JE_LINES using unique index but LINE_NUM is not used in the query so it ends up accessing 200K for each Header record and hence slow performance. following bug were filed longtime back but no resolution

Bug 6415832: No resolution

Bug 7044466: No resolution. As per this bug customer suggested creating a new index on JE_HEADER_ID, CODE_COMBINATION_ID and asked Oracle's blessing but i think it was not approved because no such index in Rel12.1.3. To solve our problem in PROD, we are thinking about creating an index on CODE_COMBINATION_ID , PERIOD_NAME and JE_HEADER_ID. This index works great for us.

I wonder what is the use of where clause in this query? Subquery has same where clause. So if record in not found pi.period_net_dr and pi.period_net_dr will be just overwritten. Am i right?

UPDATE GL_POSTING_INTERIM PI SET (PI.period_net_dr, PI.period_net_cr) = (SELECT nvl(PI.period_net_dr,0) + nvl(sum(decode(JEL.period_name, PI.period_name, decode(sign(JEL.stat_amount), 1, abs(JEL.stat_amount), 0 ), 0 )), 0), nvl(PI.period_net_cr,0) + nvl(sum(decode(JEL.period_name, PI.period_name, decode(sign(JEL.stat_amount), 1, 0, abs(JEL.stat_amount)), 0 )), 0) FROM GL_JE_BATCHES JEB, GL_JE_HEADERS JEH, GL_JE_LINES JEL WHERE JEB.status = 'I' AND JEB.chart_of_accounts_id = 50225 AND JEB.posting_run_id = 229329 AND JEB.average_journal_flag = 'N' AND JEH.je_batch_id = JEB.je_batch_id AND JEH.status != 'P' AND JEL.je_header_id = JEH.je_header_id AND nvl(JEL.status,'U') = 'U' AND JEL.stat_amount is not null AND PI.ledger_id = JEH.ledger_id AND PI.code_combination_id = JEL.code_combination_id AND PI.actual_flag = JEH.actual_flag AND PI.period_name = JEL.period_name AND PI.currency_code = 'STAT' AND nvl(PI.budget_version_id, -1) = nvl(JEH.budget_version_id, -1) AND nvl(PI.encumbrance_type_id, -1) = nvl(JEH.encumbrance_type_id, -1) GROUP BY JEH.ledger_id, JEH.actual_flag, JEH.encumbrance_type_id, JEH.budget_version_id, JEL.period_name, JEL.code_combination_id) WHERE (PI.ledger_id, PI.actual_flag, nvl(PI.budget_version_id, -1), nvl(PI.encumbrance_type_id, -1), PI.currency_code, PI.period_name, PI.code_combination_id) IN (SELECT JEH2.ledger_id, JEH2.actual_flag, nvl(JEH2.budget_version_id, -1), nvl(JEH2.encumbrance_type_id, -1), 'STAT', JEH2.period_name, JEL2.code_combination_id FROM GL_JE_BATCHES JEB2, GL_JE_HEADERS JEH2, GL_JE_LINES JEL2 WHERE JEB2.status = 'I' AND JEB2.chart_of_accounts_id = 50225 AND JEB2.posting_run_id = 229329 AND JEB2.average_journal_flag = 'N' AND JEH2.je_batch_id = JEB2.je_batch_id AND JEL2.je_header_id = JEH2.je_header_id AND nvl(JEL2.status,'U') = 'U' AND JEL2.stat_amount is not null)

GL Data Purge

In order to Purge GL Data, Following steps are needed.

1. Close GL Period Permanently. Follow these steps to close GL Period Permanently. when you update status of a period to Permanently Closed, all other prior periods too get permanently closed. When you save the changes, form will submit concurrent program Periods - Permanently Close Period


2. Before purging, set storage parameter for GL Archive tables using form "GLXSTSPM". This form is available to responsibility General Ledger Super User. Navigation for this form is "Setup -> System -> Storage".


3. GL Data can be archived and Purged using form GLXSTPRG. This form is accessible to responsibility General Ledger Super User. Navigation for this form is "Setup -> System -> Purge". If you do not have access to responsibility General Ledger Super User then you may use this link to determine which other responsibility have access to that form.


4. In order to Purge GL data, it must be archived first. GL Data is purged from following six tables


1. GL_BALANCES
2. GL_JE_BATCHES
3. GL_JE_HEADERS
4. GL_JE_LINES
5. GL_IMPORT_REFERENCES
6. GL_JE_SEGMENT_VALUES


5. The GL data from above tables is archived into following tables


1. GL_ARCHIVE_BALANCES
2. GL_ARCHIVE_BATCHES
3. GL_ARCHIVE_HEADERS
4. GL_ARCHIVE_LINES
5. GL_ARCHIVE_REFERENCES
6. GL_ARCHIVE_SEGMENT_VALS


6. Data could be archived for a range of periods. Once data is archived, it could be exported for future reference


7. After archiving GL data for a period or range of periods, you can not archive GL data for another period. Before you could archive another period, You need to do the following


1. Purge GL data. If GL data is archived for multiple periods then purge could be performed one period at time


2. Export archived data for future reference. This data not be loaded back into GL tables. Oracle does not recommend it. You have to develop custom programs if you intend to review archived data. This step is optional


3. When all archived data is purged, Truncate/Delete archived data


8. GL_ARCHIVE_HISTORY: This table has information about archive and purge runs. This table is used for List of value for Archive and Purge "Periods To" in "Archive and Purge" form (GLXSTSPM)


9. When we purged GL data, Purge process was slow. We disabled few indexes on GL_BALANCES and GL_JE_LINES and purge performance was acceptable.

Difference in GL Interface and GL Interface History Structure

I was running Journal import and found that insert into GL_Interface_history is very slow. After completion of Journal import program, it moves records from GL_Interface table to GL_Interface_History table. I was looking for certain column in GL_INTERFACE_HISTORY but could not find it, so i wanted to compare structure of both tables. I did not know any better way, so i used following sql to see the differences between two tables. By ordering the result by 2, i get column that are not in GL_INTERFACE_HISTORY.

SELECT first_table.column_name "GL Interface Columns" , second_table.column_name "GL Interface History Columns" FROM (SELECT column_name FROM dba_tab_columns WHERE table_name = 'GL_INTERFACE' ) first_table , (SELECT column_name FROM dba_tab_columns WHERE table_name = 'GL_INTERFACE_HISTORY' ) second_table WHERE first_table.column_name = second_table.column_name(+) UNION SELECT first_table.column_name , second_table.column_name FROM (SELECT column_name FROM dba_tab_columns WHERE table_name = 'GL_INTERFACE' ) first_table , (SELECT column_name FROM dba_tab_columns WHERE table_name = 'GL_INTERFACE_HISTORY' ) second_table WHERE first_table.column_name(+) = second_table.column_name ORDER BY 2;