Overhead of Statspack at Level 7 in 10gR2 with RAC

From: VIVEK_SHARMA <VIVEK_SHARMA_at_infosys.com>
Date: Fri, 23 Feb 2007 19:48:16 +0530
Message-ID: <BBD944BCAC3AB4499DFBAFB1D8AF3020029DC77D@BLRKECMSG11.ad.infosys.com>

Hi Tim, Folks

What would be the Overhead of taking statspack snapshots at Level 7 (using the following 2 Statspack parameter) Sets for a RAC 10gR2 setup?

Is it ill-advised to collect Statspack Snapshots while taking final readings of Performance Benchmark Runs (OLTP & Batch nature of Transactions) as it may affect the Transaction per second (TPS) Results?

Statspack at default Level of 5 seemingly has negligible Overhead.

SQL Trace seemingly has a 25% Overhead approx.

Cheers

Vivek

---

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Tim Gorman Sent: Wednesday, February 07, 2007 2:20 AM 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:

PARAMETER SET 1 for "Quiet" applications

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

	PARAMETER SET 2 for "Busy" applications

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)

>

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.

...

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

• 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*** -- http://www.freelists.org/webpage/oracle-l

Received on Fri Feb 23 2007 - 08:18:16 CST