Re: How long should statspack.snap take to run?

Charles Hooper wrote:
> jon.fife_at_gmail.com wrote:
> > Hi all,
> >
> > I've been tossed into a vacant DBA role and I'm trying to pick up the
> > pieces the former DBA left in his wake. It doesn't seem that he had any
> > sort of history for performance baselines, so I've been playing around
> > with statspack. When I do the statspack.snap it takes around 15 minutes
> > to complete and I was just wondering if that was outrageous or typical.
> > It is a 9.2.0.4 server with roughly 150GB of data and 150 concurrent
> > users during the day doing OLTP. I've done some googling to see if the
> > time spent was sane, but didn't find much there. It seems some people
> > set up statspack to run every 30 minutes or so, which makes 15 minutes
> > seem high...
> >
> > Another thing to note: when I first did the snap I was getting the
> > following:
> > ORA-00001: unique constraint (PERFSTAT.STATS$SQL_SUMMARY_PK) violated
> >
> > I found a fix for that in Metalink (Note:267244.1) that had me disable
> > the primary key constraint on PERFSTAT.STATS$SQL_SUMMARY. Since that
> > invalidated the STATS$SQL_SUMMARY_PK index I created a new index named
> > $STATS$SQL_SUMMARY_PK on STATS$SQL_SUMMARY(SNAP_ID, DBID,
> > INSTANCE_NUMBER, HASH_VALUE, TEXT_SUBSET) and did a compute statistics
> > on it. I don't know if that would have a huge impact on the execution
> > time, since there are only 3700 rows in STATS$SQL_SUMMARY.
> >
> > Thanks for any advice,
> >
> > Jon
>
> Personal opinion, having read several performance tuning books, is that
> stats pack reports are of limited value, since their do not allow you
> to focus on a specific problem. The stats pack reports are either for
> too long of a duration, and thus dilute the existence of real
> performance problems, or are so short in duration and miss a
> performance problem when it occurs.
>
> Instead, pick a high value target. Activity A would cost my company N
> fewer dollars per month if it completed X seconds earlier. Once you
> have that information, perform a 10046 trace at level 8 (wait events)
> or level 12 (wait with bind variable values) against the application
> with the greatest potential benefit to the company. Examine the waits
> carefully - they will provide an indication of what can be adjusted in
> your system to improve performance.
>
> If you feel the need to look at full system performance first, try this
> to obtain a rough idea of what is happening in the system:
> CREATE TABLE
> SYSTEM_LOG
> AS
> SELECT
> 0 SID,
> TO_DATE(TO_CHAR(SYSDATE,'YYYYMMDD HH24:MI'),'YYYYMMDD HH24:MI')
> TIME_STAMP,
> EVENT,
> TOTAL_WAITS,
> TOTAL_TIMEOUTS,
> TIME_WAITED,
> AVERAGE_WAIT
> FROM
> V$SYSTEM_EVENT
> WHERE
> EVENT IN (
> 'buffer busy waits',
> 'db file sequential read',
> 'db file scattered read',
> 'direct path read',
> 'direct path write',
> 'enqueue',
> 'free buffer waits',
> 'latch free',
> 'log file parallel write',
> 'log file sync')
> UNION ALL
> SELECT
> SID,
> TO_DATE(TO_CHAR(SYSDATE,'YYYYMMDD HH24:MI'),'YYYYMMDD HH24:MI')
> TIME_STAMP,
> EVENT,
> TOTAL_WAITS,
> TOTAL_TIMEOUTS,
> TIME_WAITED,
> AVERAGE_WAIT
> FROM
> V$SESSION_EVENT
> WHERE
> EVENT IN (
> 'buffer busy waits',
> 'db file sequential read',
> 'db file scattered read',
> 'direct path read',
> 'direct path write',
> 'enqueue',
> 'free buffer waits',
> 'latch free',
> 'log file parallel write',
> 'log file sync');
>
> COMMIT;
>
> The above creates a logging table with baseline values for various
> performance statistics. When the performance in the system seems to be
> slower than expected, execute the following:
> INSERT INTO
> SYSTEM_LOG
> SELECT
> 0 SID,
> TO_DATE(TO_CHAR(SYSDATE,'YYYYMMDD HH24:MI'),'YYYYMMDD HH24:MI')
> TIME_STAMP,
> EVENT,
> TOTAL_WAITS,
> TOTAL_TIMEOUTS,
> TIME_WAITED,
> AVERAGE_WAIT
> FROM
> V$SYSTEM_EVENT
> WHERE
> EVENT IN (
> 'buffer busy waits',
> 'db file sequential read',
> 'db file scattered read',
> 'direct path read',
> 'direct path write',
> 'enqueue',
> 'free buffer waits',
> 'latch free',
> 'log file parallel write',
> 'log file sync')
> UNION ALL
> SELECT
> SID,
> TO_DATE(TO_CHAR(SYSDATE,'YYYYMMDD HH24:MI'),'YYYYMMDD HH24:MI')
> TIME_STAMP,
> EVENT,
> TOTAL_WAITS,
> TOTAL_TIMEOUTS,
> TIME_WAITED,
> AVERAGE_WAIT
> FROM
> V$SESSION_EVENT
> WHERE
> EVENT IN (
> 'buffer busy waits',
> 'db file sequential read',
> 'db file scattered read',
> 'direct path read',
> 'direct path write',
> 'enqueue',
> 'free buffer waits',
> 'latch free',
> 'log file parallel write',
> 'log file sync');
>
> Then run this SQL statement again once a minute for five to 10 minutes.
>
> Making sense of the captured data:
> SELECT
> SL2.SID,
> SL2.TIME_STAMP,
> SL2.EVENT,
> SL2.TOTAL_WAITS-NVL(SL1.TOTAL_WAITS,0) WAITS,
> SL2.TOTAL_TIMEOUTS-NVL(SL1.TOTAL_TIMEOUTS,0) TIME_OUTS,
> SL2.TIME_WAITED-NVL(SL1.TIME_WAITED,0) TIME_WAITED,
> SL2.AVERAGE_WAIT
> FROM
> SYSTEM_LOG SL1,
> SYSTEM_LOG SL2
> WHERE
> SL2.SID=SL1.SID(+)
> AND SL2.TIME_STAMP=SL1.TIME_STAMP(+) + (1/24/60)
> AND SL2.EVENT=SL1.EVENT(+)
> ORDER BY
> SID,
> SL2.TIME_STAMP,
> SL2.EVENT;
>
> For example, if the delta value of time waited for db file scattered
> read where SID=0 seems consistently high, that is an indication that
> full table scans are likely contributing to the system performance
> problems. You can then look at that value for the various sessions to
> determine which session is contributing the most to the performance
> problem. For there, you can enable a 10046 trace for the session to
> determine the SQL statements executed by the session, the wait events
> that result from the execution of the SQL statement, and the file and
> block number (or other information present with other types of waits)
> involved in the wait, which may be used to determine the table that is
> the greatest contributor. This may lead you to look at the statistics
> for the table and indexes on the table, where you may find that the
> statistics for the table and indexes are stale - statistics show that
> the table has 10 rows with an average size of 100 bytes per row, when
> there are actually 1,000,000 rows in the table.
>
> Charles Hooper
> PC Support Specialist
> K&M Machine-Fabricating, Inc.

Statspack can be a very useful took IF you work with it enough to understand what it shows you. While the default report has limited usefulness for finding specific causes of performance problems the report can identify if the database as a whole had a performance problem. Higher level settings can be used to help locate resource intensive SQL, which generally sits at the bottom of all performance problems.

Once you know you have a problem then you can use other tools to help you locate the true cause and solve the problem.

IMHO -- Mark D Powell -- Received on Fri Jul 14 2006 - 17:14:42 CDT