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;
Vishal Gupta's Blog 