statspack report analyzer

does a statspack report analyzer exist?
Who is Participating?
Doug WaltonDatabase AdministratorCommented:
I've something similar to this in the past, but I'm not authorized to release the code for it publicly yet.  It shouldn't take too much work to setup something similar.  Here's the overall procedure of how we did it:

  1. We had some automation that would run the same exact commands and hit the same pages against our test environment and that was the only thing running during a set hour every week.
  2. We then would take the statspack report from that hour and save its values into a database.
  3. Then we compared this new statspack results to the results from the last known "good" build.
  4. Any queries that had a significant change in buffered gets, executions, time elapsed, etc. got flagged had to be explained or optimized by a DBA or a developer.

The main thing is having a solid benchmark that doesn't change constantly you can run against the database for an hour.  Slightwv is definitely right about some queries being "bad" on one database and fine on another.  Most of our queries that looked "bad" were actually fine, that's why we compared to a known "good" state and only looked for large changes between releases.

Our license of Oracle didn't let us use AWR and we didn't find any statspack analyzers online at the time we could use, so that's why we made our own.  The hardest part was parsing the statspack data into another database.  It's pretty straightforward, it just takes some time.
slightwv (䄆 Netminder) Commented:
I'm guessing you are looking for something that says "you have a problem HERE" so you don't have to go through a lot of data?

The problem you have is that databases normally don't behave the same when it comes to what is "normal".  A "bad" value in a report for one database may be perfectly OK in another database.

It has been years since I looked at the output from spreport but I remember them being pretty nice as far as areas to focus on.  Is there specific area you are having problems trying to figure out?

As far as the analyzer you asked for:
Don Burleson used to have one but it doesn't seem to exist any more.  Statspack is more or less the old way.  If an analyzer exists it would likely be to run against AWR reports.

Here's Burleson's old link:
Geert GOracle dbaCommented:
tuning mainly comes down to finding the most resource intensive statements and "fixing" them
the resources are disk reads and buffer gets

statspack, and other tools, get this from v$sql
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

bkreynolds48Author Commented:
will try to figure out what data to pull

Doug if you have any more info please let me know
Mark GeerlingsDatabase AdministratorCommented:
All recent versions of the Oracle database (at least since version 10) also include a copy of OEM (Oracle Enterprise Manager) that provides a GUI view into current and some historical SQL statements.  That makes it easy to see which SQL statements are the slowest.  OEM also includes a "SQL Tuning Advisor" that can give you recommendations on how to improve poorly-performing SQL statements or queries.
Doug WaltonDatabase AdministratorCommented:
Here are some of the queries I used, I think they're based off something I read on Burleson's site, but I can't find the page anymore.

--getting dbid and instance number
select distinct dbid, instance_number from stats$database_instance where db_name='$DB';

--getting the snapshot IDs for start/end (we did 9-10am, seconds always vary so that's why there's the %)
select s.snap_id snap_id, to_char(snap_time, 'YYYY-MM-DD HH24:MI:SS') snapdate
  from stats\$snapshot s, stats$database_instance di
  where s.dbid              = $dbid
    and di.dbid             = $dbid
    and s.instance_number   = $instnum
    and di.instance_number  = $instnum
    and di.dbid             = s.dbid
    and di.instance_number  = s.instance_number
    and di.startup_time     = s.startup_time
    and (to_char(s.snap_time, 'YYYY-MM-DD HH24:MI:SS') like '$today 09:00:%'
      or to_char(s.snap_time, 'YYYY-MM-DD HH24:MI:SS') like '$today 10:00:%')
  order by snap_id asc;

--getting the actual data from the snapshots and the text of the queries, limited to the top 50
--this is specific to buffered gets, but there's several others you can order by if you switch those out or include them all at once
select /*+ orderd use_nl (topn st) */ st.SQL_ID, delta_buffer_gets, delta_executions, nvl(topn.module, 'null'), dbms_lob.substr(st.sql_fulltext,3500,1)
    from ( select *
               from ( select *
                        from perfstat.stats$temp_sqlstats
                       order by delta_buffer_gets desc
              where rownum <= 50
           ) topn
         , v$sqlarea st
    where st.old_hash_value(+) = topn.old_hash_value
    order by topn.delta_buffer_gets desc, topn.old_hash_value

Open in new window

This was also done about 4-5 years ago for oracle 11g.  I assume it still works, but we haven't been using it in-house for a while.

Hope this helps!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.