Oracle Scratchpad

July 4, 2013

12c Statspack Hack

Filed under: 12c,Oracle,Statspack — Jonathan Lewis @ 10:57 am BST Jul 4,2013

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).

Option 1 – completely valid

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.

Option 2 – slightly suspect

You could edit the install and report scripts to change explicit references to the perfstat schemaname to be c##perfstat instead and then install it on cdb$root.

Option 3 – definitely naughty [updated – see comments, especially #3]

There is a hidden parameter which defines the “common user prefix” (_common_user_prefix) as “c##”. You could set this to null in your parameter file, bounce the database, install statspack in cdb$root, remove the parameter from the parameter file, and bounce the database again.

Warning – if you do this you will have to set the parameter to true if you ever want to drop the schema, so make sure you’ve documented what you did and what you will have to do.

There is an easier, naughty option (see comments).  Edit the spcusr.sql script to delete the call to set “container=current”, and execute the “alter session” statement from the spcreate.sql script that sets “_oracle_script” to true before running spcreate.sql (Alternatively, move the “alter session” statement to a point in the file before the call to @@spcusr.sql).

Option 4 – production strategy

Wait for Oracle to modify the statspack install procedure to make a cdb$root install legally possible. (I suspect there was an “official” hack at some point given that “alter session” line in the spcreate.sql script.)


  1. Hi Jonathan,

    I could successfully install statspack while setting “_oracle_script” parameter to true on session level like all the catalog scripts (catproc.sql, initjvm.sql, etc.) also do.
    I think the result is similar to your option 3.

    Cheers Mathias

    Comment by Mathias Zarick — July 4, 2013 @ 3:01 pm BST Jul 4,2013 | Reply

    • Mathias,

      Thanks for the note. Infact that’s the parameter that gets set in the spcreate.sql script – and it didn’t work for me. You didn’t do this on an earlier beta release, by any chance ?

      Comment by Jonathan Lewis — July 4, 2013 @ 7:20 pm BST Jul 4,2013 | Reply

      • Hi Jonathan,

        I did not test before but right now I tested this on beta 2 and it worked without a problem. If the parameter is set to true you are able to create common users without the prefix. Note that in the spcreate script the parameter is set lately after the create user part. Funny thing is that also the comment in spcreate “– set this parameter for creating common objects in consolidated database” comes up too late.

        Cheers Mathias

        Comment by Mathias Zarick — July 5, 2013 @ 8:10 am BST Jul 5,2013 | Reply

        • Mathias,

          The really funny thing is that having seen the comment about bypassing the “common object” problem, I didn’t notice that the “alter session” statement appeared AFTER the spcusr.sql script to create the user. I did actually try setting that parameter it before calling spcreate.sql and got an error message on the ‘create user’ statement so assumed it didn’t have the effect I’d hoped.

          Given your comments, I’ve looked at the spcusr.sql script and see that the problem is actually the “container = current” that appears in the create user statement (does this appear in the beta 2 scripts ?)

          I’ve update the note accordingly.

          Comment by Jonathan Lewis — July 5, 2013 @ 8:41 am BST Jul 5,2013

        • Jonathan,

          now I really wonder :-) I have no “container = current” clause in the spcusr.sql script. Neither in Production, nor in beta 2. I use Linux x86_64.

          sizes and dates from production:
          -rw-r–r– 1 oracle oinstall 1175 Sep 23 2012 spcreate.sql
          -rw-r–r– 1 oracle oinstall 15561 Apr 13 22:23 spcusr.sql
          md5’s also from production:
          7ef8f7c9dd1d50a9807b69b66815c680 spcusr.sql
          7743d6c11b99653948572c2ab0fbcb84 spcreate.sql

          Cheers Mathias

          Comment by Mathias Zarick — July 5, 2013 @ 10:01 am BST Jul 5,2013

        • Mathias,

          I think I was still looking at a copy of the Beta 3 code (can’t confirm that since I’ve deleted it, though). The GA code doesn’t have a “container” line.

          Comment by Jonathan Lewis — July 10, 2013 @ 8:16 pm BST Jul 10,2013

  2. you could do it this way, too:
    add the following to the spcreate script or create a copy:
    define perfstat_password=aaaaaa
    define default_tablespace=sysaux
    define temporary_tablespace=temp

    … and then execute the script with the script:
    perl $ORACLE_HOME/rdbms/admin/ -b mylog -d $ORACLE_HOME/rdbms/admin/ -l /home/oracle -e spcreate.sql


    Comment by danielwestermann — July 11, 2013 @ 7:43 am BST Jul 11,2013 | Reply

    • Daniel,

      Thanks for the suggestion. I think that’s one for people who are very familiar with perl.

      (Standard warning for others: make sure you understand what the code does when you follow a suggestion from the Internet)

      Comment by Jonathan Lewis — July 24, 2013 @ 12:43 pm BST Jul 24,2013 | Reply

  3. Jonathan,

    Finally found some time to return to this issue. Here is what I did, which matches up to what Mathias has done.

    First I checked the spcuser.sql for the “container=current” phase in the create user perfstat statement; it was not there.
    Second logged in is as sys and was in the root container CDB$ROOT.
    Third issued the ‘alter session set “_oracle_script” = TRUE;’
    Fourth ran spcreate.sql and it completed successfully.
    Fifth connected as perfstat/ and ran spauto.sql.

    Letting the job run for awhile, I found the snapshots were being taken and I was able to generate statspack reports.

    In the end it seems the ‘alter session set “_oracle_script” = TRUE’ statement should also be in the spcuser.sql script or called in the spcreate.sql before the call to spcuser.sql in the spcreate.sql.

    Comment by William Gaynor — July 21, 2013 @ 1:14 am BST Jul 21,2013 | Reply

  4. I’ve just spent a few minutess looking at the perfstat question again, investigating it from the direction of the dynamic performance views. Just for reference:

    1. some of the views (e.g. v$sql) when queried from cdb$root show rows for every value of con_id (i.e. they are detail views across the whole instance); for the pluggable database these views show only the rows for their individual con_id. The con_id of cdb$root shows up as 1.
    2. some of the views (e.g. v$sysstat) when queried from cdb$root show rows only for con_id = 0 – which indicates a sum over all the pluggable databases plus the container database. These views show the con_id of the pluggable db when queried from a pluggable db, and only show values relevant to that db. I haven’t checked yet if there is a simple way to see the values for just the cdb$root.
    3. some of the view (e.g. v$waitstat) when queried from cdb$root show rows only for con_id = 1. I have not yet checked if theses are really the values for just cdb$root, or whether they are sums across all the databases. (Just checked for v$waitstat specifically, and it’s showing values only for cdb$root).

    These are details that need to be checked to make sure we can interpret statspack collections are reports correctly. One thought, though, is that the AWR report is not self-consistent.

    Comment by Jonathan Lewis — November 13, 2013 @ 7:22 am GMT Nov 13,2013 | Reply

  5. […] although Oracle still deliver Statspack with their recent DB releases (yes, even in 12c it’s not dead!), there are few tools that support it. But wait – Oracle SQL Developer […]

    Pingback by Visualizing Statspack Performance Data in SQL Developer | Oraculix (en) — March 12, 2015 @ 5:27 pm GMT Mar 12,2015 | Reply

  6. […] In this situation, one of the tools available to get AWR-like information is the old Statspack (which is actually the antecessor of AWR). Yes, it still works in 11g and 12c. […]

    Pingback by Use Statspack in 11g/12c (and show SQL_ID in the Report) | EDUARDO CLARO — June 28, 2017 @ 1:35 am BST Jun 28,2017 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by

%d bloggers like this: