I've published a few month ago an article in the UKOUG OracleScene magazine on Improving Statspack Experience: quick setup script, changing settings, filling Idle Events,etc. In the article, I used dbms_job to schedule the snapshots, because I have this script for years and never took the time to do it with dbms_scheduler. Thanks to Nicolas Jardot here is the equivalent script using dbms_scheduler.
The idea is to have a script to run on each instance (when in RAC) in order to have a job calling statspack.snap and statspack.purge on each instance.
The following is the text of an article I published in the UKOUG magazine several years ago (2010), but I came across it recently while writing up some notes for a presentation and thought it would be worth repeating here.
The title of this piece came from a presentation by Cary Millsap and captures an important point about trouble-shooting as a very memorable aphorism. Your solution to a problem may look good for you right now but is it a solution that will still be appropriate when the database has grown in volume and has more users.
I was actually prompted to write this article by a question on the OTN database forum that demonstrated the need for the basic combination of problem solving and forward planning. Someone had a problem with a fairly sudden change in performance of his system from November to December, and he had some samples from trace files and Statspack of a particular query that demonstrated the problem.
It's amazing how easy it is to interpret a number incorrectly until the point comes where you have to look at it closely – and then you realise that there was a lot more to the number than your initial casual assumption, and you would have realised it all along if you'd ever needed to think about it before.
Starting from a comment on an old statspack/AWR page, with a near-simultaneous thread appearing on OTN, (do read both) here's a quick summary of getting statspack onto 12c with containers. (For non-container databases it's a standard install).
At present, you have to install the perfstat account on a pluggable database if you want to do it legally. On the plus side this means you could install it once then clone, unplug, and re-plug it elsewhere – though you might have to play around each time enabling a new statspack job.