After upgrading database to 12c, SQL Navigator is freezing and locking up. This can happen in any module within the application. This is happening to multiple users. Issue can happen on one 12c database instance only as well; other 12c databases are fine.
A)
It was discovered that an option to frequently gather statistics and re-analyze explain plans had been turned on and was consuming resources. Once these were switched off, the database ran more consistently and developers stopped having SQL Nav 7.2 lock up.
One example of a slow operation is describing database objects. With SQL Nav 7.3 beta it took 74 secs, with SQL Developer it took 4 secs. Used SQL Tracker of SQL Navigator, but SQL Tracker showed up blank for SQL Developer.
B)
Another discovery is the problem is linked to plans that were automatically generated when the affected schemas were moved from Oracle 11g to 12c. In the conversion from 11g to 12c, there were some default options chosen that caused the problem. The environment was set to generate SQL plan baselines, auto-accept them, and then not subsequently update them, which resulted in the schemas using bad plans and caused the slowness issue. This apparently is a new option for 12c. The solution was to drop all the generated plans and start over.
