Vishal Gupta's Blog

Archive for the ‘Statspack’ Category

Slow Statspack Snapshots

Posted by Vishal Gupta on Oct 10, 2008

 

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;

 

 

 

Posted in Oracle, Performance, Statspack | 3 Comments »

 
%d bloggers like this: