Speed Up PL/SQL Developer on Oracle 12c by Disabling Dynamic Sampling

A developer using PL/SQL Developer against an Oracle 12.1.0.2 test database noticed extremely slow field auto‑completion (5 seconds after typing a dot) and a 2‑second pause for any single‑table query, which hampered debugging and coding.

The initial suspicion was that PL/SQL Developer was querying system tables and that the data dictionary and fixed‑object statistics had not been collected, leading to sluggish internal view access.

To address this, the following PL/SQL procedures were executed to gather the missing statistics: exec dbms_stats.gather_dictionary_stats; Result: PL/SQL procedure completed successfully (elapsed time 00:02:34.50). exec dbms_stats.gather_fixed_objects_stats; Result: PL/SQL procedure completed successfully (elapsed time 00:03:56.13).

After gathering the stats, the shared pool was flushed: alter system flush shared_pool; However, the operations remained slow. An SQL trace was run, revealing that during query composition Oracle automatically performed dynamic sampling, which generated high‑I/O statements and caused the noticeable lag.

Relevant trace screenshots are shown below:

To stop the costly dynamic sampling, the optimizer parameter was set to zero:

alter system set optimizer_dynamic_sampling=0 scope=both;

After applying this change, most operations showed noticeable performance improvement, though not a complete fix. Restarting the database finally eliminated the lag, and PL/SQL Developer became responsive again.