Friday, March 04, 2016

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?

No comments:

Post a Comment