title: Upgraded db from 11.2.0.3.0 to 12.1.0.2.0. Slow Journal Import
body: <p>We upgraded our database from 11.2.0.3.0 to 12.1.0.2.0 on PROD on weekend of 18/19 June 2016.</p>
  
  <pre><code>SELECT * FROM v$version;
  </code></pre>
  
  <p><strong>Version info:</strong></p>
  
  <pre><code>Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
  PL/SQL Release 12.1.0.2.0 - Production
  CORE    12.1.0.2.0    Production
  TNS for Solaris: Version 12.1.0.2.0 - Production
  NLSRTL Version 12.1.0.2.0 - Production
  </code></pre>
  
  <p>We are running Oracle Release 12.1.3</p>
  
  <p>We also applied CPU patches and others (13942692, 18813637, 12404574, 15969020, 18835102, 21612876, 17889841, 12598630, 18991480, 22465286, 22614470, 16289505, 18039691, 9833058,, 16958896, 22378313).</p>
  
  <p>During the testing we hit an issue with slow performance of the "<strong>Journal Import</strong>" GLLEZL job.</p>
  
  <p>We find that when the Journal Import runs slowly (e.g. for a 19,000 line Web ADI journal the "Web ADI - Journal Import" job takes 2.5 hours compared to a couple of minutes on the Database 11 version).</p>
  
  <p>For other jobs, e.g. when we load data into the GL_INTERFACE and run a standard "Program - Journal Import", e.g. a 120,000 line journal, the job never completes.</p>
  
  <p>I raised a Service Request and we got a quick fix, which is to cancel the long-running job, and then run this command:</p>
  
  <pre><code>exec fnd_stats.gather_table_stats('GL','GL_INTERFACE',100)  
  </code></pre>
  
  <p>Then when we re-run the Journal Import it runs quickly.</p>
  
  <p>The strange thing is that we can run that command, and re-run Jnl Import, and the issue is fixed for that instance.</p>
  
  <p>The next time we run a big job, it gets stuck again.</p>
  
  <p>We have reviewed this Note:</p>
  
  <p><strong>R12: Improving Performance of General Ledger and Journal Import (Doc ID 858725.1)</strong></p>
  
  <p>We also had another fix from Oracle, which was to:</p>
  
  <ol>
  <li><p>Cancel an unwanted Index on Segment1 of our Accounting Flexfield</p></li>
  <li><p>Run "Program - Optimizer" with both parameters set to Yes</p></li>
  <li><p>Run "Gather Schema Statistics" for GL Schema with 25 as Estimate Percent value</p></li>
  </ol>
  
  <p>But that has not fixed the problem - we still need to run the manual fix each time.</p>
  
  <p>We also now schedule "Program - Optimizer" with both parameters set to Yes on a daily basis, and a full Gather Schema Stats runs weekly at 25 for all Schemas.</p>
  
  <p>Oracle are still trying to work this one out, but I thought I'd ask here in case anyone else has had similar issues.</p>