R12.1.3 Payables: Payment Manager performance issue after upgrade

Post upgrade to R12.1.3, there is an issue in Payables which you can find from MOS in the note "Performance Problem In Payment Manager (Payment Dashboard Page) After Database Upgrade" by way of document ID 2014319.1 where it details how several different actions can take up to a minute!  First you must gather stats, then set or remove several database parameters, and bounce to make sure it takes effect so you should check out this note to see if you need to review the parameters and then take action if so!

11.2.0.3: Gather stats job hangs due to parallel processing

While I did not actually post describing our issue (this post actually mentions the problem though) when we upgraded our DB to 11.2.0.3 on Exadata we ran into some serious issues as soon as we fired up all of our Gather Schema Statistics reports because of the parallelism that was enabled for the process.  Enter MOS note "Dbms_Stats.Gather_Table_Stats For a Table is Hanging With the Wait Event "PX Deq: Execute Reply"" via document ID 1946632.1 which sounds exactly the type of situation we had with our nodes conflicting with each other due to the parallel processing that was enabled!  Did we patch to fix the issue?  Nope, what we did was actually use the report parameter that gave us control over how many parallel processes would get spawned and then ran everything in a single Gather Schema Statistics report since we were on Exadata and had processing power to spare!

Purge Inactive Sessions and HWM expectations

Recently I ran the Purge Inactive Sessions concurrent program after it had not run for a while, and based on my Workflow purging I wanted to make sure we didn't purge out too much data or else it would be likely the system would hit High Water Mark contention when trying to query the ICX_SESSIONS table.  On the first day I purged out records to leave us above where I thought the HWM contention would occur, but I noticed even after a Gather Table Statistics concurrent program was run there was slight slow down on querying from ICX_SESSIONS but nothing in HWM enqueue yet.  Reporting the issue to the DBAs, they suggested that I continue purging out past what I felt was safe since this was in QA and they were prepared to do a shrink to restore the table(s) affected by this program.

On the next day I ran the Purge Inactive Sessions report again, and as expected, there is now a performance problem with ICX_SESSIONS which I thought was due to purging too many records since a simple SELECT was not returning even after 90 minutes.  Why is that bad performance?  Before purging, the same statement was returning in less than a minute so obviously my statement must have been affected by the HWM enqueue contention right?  Wrong!  Curiouser, and curiouser.  That wasn't what I expected at all.  Since the report was still running, I decided to let it continue deleting before doing anything else and then when it completed I ran my SELECT statement.  Guess what I found?  That statement no longer took 90 minutes, as it was completing in a minute or two, nor did it go into HWM contention even before another Gather Table Statistics was done.

Why did my query take so long to return on the second day?  My best guess is that the system was trying to calculate the number of results in the table, but was unable to do so while I was busy having the report deleting from the table so it was stuck until after the report was completed.  When I do this in another environment prior to going to PROD, I'll be able to look at this closer and hopefully I'll gain more insight as to what was going on!

OVC - Reclaiming space

In running our Gather Schema for ALL schemas some interesting results popped up, one of which was the error message of “ORA-00900: invalid SQL statement” for a few objects.  Researching what the items were by just running a select on the tables (OVCX_FINP_HLD_INVALIDS_0_0 and OVCX_FINP_HLD_PROFILES_0_0) I found that the items in question were actually tables in our system which were columns of HTML code which made up webpages when put together!!

Research on My Oracle Support finds this article "OVC Diagnostic Readme and Available Parameters" [ID 252733.1] which explains that these tables are artifacts of an older version of the Oracle Diagnostics, deprecated in lieu of the current Oracle Diagnostics bundle, and the tables were generated from the ovc.zip file which is still provided by Oracle via MOS Notes 297613.1 and 398834.1.  While we only have a few of these objects in our system, reclaiming the space by dropping the tables won't be a whole lot for us by percentage, every byte is crucial and multiplied by every environment adds up to what your total storage budget is.  Maybe you don't have any of these objects in your system laying around, but are you sure of it?  Might be worth your time to go looking, don't you think?

New Gather Schema Statistics errors

After moving to Exadata we were able to change some of our operational tasks, and one of those was to run our Gather Schema Statistics with the ALL parameter instead of chunking it out by our biggest schema names because of how fast it runs.  With that, comes finding errors we never saw before and one of which was "ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt" for these items:

Schema       Object
--------       -------

FII	FII_FIN_ITEM_HIERARCHIES
AP	JE_CH_PAYMENT_REF
	JE_FR_DAS_010
	JE_FR_DAS_010_NEW
	JG_ZZ_SYS_FORMATS_ALL_B
	JG_ZZ_SYS_FORMATS_ALL_TL
GL	JE_BE_LINE_TYPE_MAP
	JE_BE_LOGS
	JE_BE_VAT_REP_RULES
ALL	JE_BE_LINE_TYPE_MAP
	JE_BE_LOGS
	JE_BE_VAT_REP_RULES

Searching My Oracle Support I was able to find a direct hit with note "Gather Schema Statistics fails with Ora-20001 errors after 11G database upgrade [ID 781813.1]" that was able to walk me through the way to identify which items were an issue, and how they could be resolved.