statspack report analyzer

does a statspack report analyzer exist?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.