Statspack execution time

[Dilippatel]

My statspack snap execution takes 8 to 10 minutes, ( Sometimes 12 minutes !!!! ). Experts suggests to take snaps after each 15 minutes when the load is more on the database. But how effective would be the analysis using statspack report effective if snap takes 8 to 10 min.

Can you share everybody what time it takes to execute there statspack snap.

My Snap level is 5 (Default).
Database Oracle 8i 8.1.7.4 on Solaris 8 with 8 CPU and 8 GB RAM.

Thanks

[hrishy]

Hi Dilip

its okay 8-10minutes is also a good window.check out the statspack report contents and wheather its is maeningful for you..You need to run ur statspack during peak loads to make it meaningful

regards
Hrishy

[DaPi]

Hi hrishy, One of us has misunderstood this. I think he's saying 8-10 mins is the elapsed time to run the snaps NOT the interval between them!

It takes a few seconds run on my machine. I can only suggest you take the proc apart and try running it query by query till you find the slow one.

Alternatively grab sql & stats from the V$ views for parsing user = perfstat . . .

Is there any reason not to run ANALYZE on the perfstat schema?

[Dilippatel]

Thanks Dapi, Hrishi for help.

Dapi is correct, It's really elapsed time for taking snap. Really too long compared to Dapi's few seconds.

I analyzed my perfstat schema (which was not analyzed before )
Now it takes some 5 Min to execute the snap. Which also I think is too much.

Dapi, please tell me how to "take the proc apart and try running it query by query till you find the slow one."

Because I am executing by following way

SQL> execute statspack.snap;


Tuning of the performance tuning tool (Interesting isn't it ?)

[DaPi]

/RDBMS/Admin/statspack.sql is the code that creates the package. Easier to work with that than try to get the queries from DBA_SOURCE or wherever.