RE: Statspack setup for 2nd RAC instance ?...basic Qs

Vivek,

I'd continue to recommend the settings of "spparms.sql" for busy applications.  Yes, the definition of "busy" is completely subjective.  For "quiet" or low-intensity environments in terms of resource utilization, I'd recommend leaving the thresholds at their default and changing only the two following things:

exec statspack.modify_statspack_parameter( -               i_snap_level => 7, -  
            i_seg_itl_waits_th => 1)

The default SNAP_LEVEL of 5 does not capture V$SQL_PLAN (captured by level 6 and above) nor V$SEGSTAT (captured by level 7), both of which are really really useful.  Lowering the threshold for "ITL waits" from the default value (forget what it is) to 1 is sensible mainly because ITL waits are so difficult to diagnose yet so easy to fix (i.e. increase INITRANS), so I don't see the point of filtering out any of them.

Watch out when setting SNAP_LEVEL to 6 or 7 in 9.2.0.5 -- there is a bug on queries on V$SQL_PLAN that is easily detected by doing a "select count(*)" on that view (i.e. query hangs indefinitely, as does STATSPACK.SNAP when it queries the view).  So, before changing SNAP_LEVEL on that version, query V$SQL_PLAN first...

Thanks!

-Tim

On Wed, 7 Feb 2007 01:47:00 +0530, VIVEK_SHARMA wrote
> Thanks Tim as always for the support & explanation in such simple language.
>
> It worked. I simply executed “exec statspac.snap” from the 2nd instance & the 2nd started appearing thereafter in the sprepins.sql Output

>
> Would you still recommend the following parameters (as picked up from your site) for RAC with 10gR2 & advised by Barbara?

>
> exec statspack.modify_statspack_parameter( -

>          i_snap_level => 7, -
>          i_executions_th => 1000, -
>          i_parse_calls_th => 1000, -
>          i_disk_reads_th => 10000, -
>          i_buffer_gets_th => 100000, -
>          i_sharable_mem_th => 1048576, -
>          i_version_count_th => 20, -
>       i_seg_itl_waits_th => 1)
>
> NOTE – We aim to collect Statspack for Benchmark Runs on a Hybrid Banking Application product

>
> Cheers

>
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Tim Gorman
> Sent: Wednesday, February 07, 2007 12:48 AM
>

>

> Vivek,
>
> Yes, you did communicate the issue sufficiently.  What you have not done is

> understand Barb's response sufficiently.
>
> You only need to take at least two snapshots on an instance in order

> for "spreport.sql" to have something against which to report.  The STATSPACK
> repository is always installed initially capable of handling data from
> multiple OPS/RAC instances - there is no "installation" process for
> additional OPS/RAC instances.  Barb provided you with instructions on how to
> schedule DBMS_JOB jobs on the second instance -- once those snapshots
> happen, then "spreport.sql" will have something to report against.
>
> Hope this helps...

>
> -Tim

>

• CAUTION - Disclaimer *****************
  > This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely for the use of the addressee(s). If you are not the intended recipient, please notify the sender by e-mail and delete the original message. Further, you are not to copy, disclose, or distribute this e-mail or its contents to any other person and any such actions are unlawful. This e-mail may contain viruses. Infosys has taken every reasonable precaution to minimize this risk, but is not liable for any damage you may sustain as a result of any virus in this e-mail. You should carry out your own virus checks before opening the e-mail or attachment. Infosys reserves the right to monitor and review the content of all messages sent to or from this e-mail address. Messages sent to or from this e-mail address may be stored on the Infosys e-mail system.
  > ***INFOSYS******** End of Disclaimer ********INFOSYS***
  >

Tim Gorman
consultant - Evergreen Database Technologies, Inc.

website = http://www.evdbt.com 
email   = tim_at_evdbt.com 
mobile  = +1-303-885-4526 
fax     = +1-303-484-3608

--
http://www.freelists.org/webpage/oracle-l