Vishal Gupta's Blog

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;

 

 

 

About these ads

3 Responses to “Slow Statspack Snapshots”

  1. Sander said

    Clever colleague!

  2. It was Sander Westphal who identified that database had _optimizer_ignore_hints set to false. Good job Sander.

  3. Amit Rath said

    nice one …!!!!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: