Slow Statspack Snapshots

For quite some time we had been experiencing slow statspack snapshots, taking about 300sec. In a worst case scenario it took 7 hours. My colleague was investigating it, it turned out that on this particular database “_optimizer_ignore_hints” was set to true. So it was ignoring all the optimization put in by Oracle in statspack snapshot code.

Environment

OS – Linux

Database – 10.2.0.3

Disable Optimizer Hints

SQL> set timing on

SQL> alter session set “_optimizer_ignore_hints” = true;

Session altered.

Elapsed: 00:00:00.03

SQL> exec statspack.snap

PL/SQL procedure successfully completed.

Elapsed: 00:00:20.73

Enable Optimizer Hints

SQL> alter session set “_optimizer_ignore_hints” = false;

Session altered.

Elapsed: 00:00:00.01

SQL> exec statspack.snap

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.75

SQL> exec statspack.snap

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.32

Nobody knows why this parameter was set at instance level. DBA who was handling these databases has left. We will follow up with the vendor, if there is no specific reason to disable optimizer hints then _optimizer_ignore_hints hidden parameter will removed from init.ora. For the time being statspack snapshot dbms job has been altered to include following statements.

alter session set “_optimizer_ignore_hints” = false;

statspack.snap;