Overhead of Statspack at Level 7 in 10gR2 with RAC
- From: "VIVEK_SHARMA" <VIVEK_SHARMA@xxxxxxxxxxx>
- To: <tim@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 23 Feb 2007 19:48:16 +0530
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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***
Other related posts:
