How many times working on a performance issue, finding the Top SQL and Top Waits event has not been enough to understand what’s going on ?

Sometimes you have to diagnose more in depth thanks to the Oracle stats reported into the V$SESSTAT view.

But how to quickly answer :

- Which sql is linked to this stat ?

- Which stats is linked to this sql ?

To answer those questions I wrote a perl script (sqlidstat.pl) :

  • This perl script takes snapshots from the GV$SESSION and  GV$SESSTAT views.
  • As the GV$SESSTAT view is a cumulative one (values are gathered since the session started up), to extract real-time information the script takes a snapshot each second (default interval) and computes the differences with the previous snapshot.
  • You can choose the number of snapshots to display and the time to wait between snapshots.
  • You can choose to filter on sql_id, sid and on a particular stat (by default no filter is applied).
  • This script is oracle RAC aware : you can work on all the instances, a subset or the local one.
  • You have to set oraenv on one instance of the database you want to diagnose first.
  • The script has been tested on Linux, Solaris and Windows.

Usage Examples:

The help : 

./sqlidstat.pl help
Usage:./sqlidstat.pl [Interval [Count]] [inst] [top=] [sql_id=] [sid=] [name="statname"]
Default Interval : 1 second.
Default Count : Unlimited

Parameter                  Comment                      Default
---------                  -------                      -------
INST=                      ALL - Show all Instance(s)   ALL
CURRENT                    Show Current Instance
INSTANCE_NAME,...          choose Instance(s) to display
              << Instances are only displayed in a RAC DB >>
TOP=                        Number of rows to display   10
SQL_ID=                     ALL - Show all sql_id       ALL
SID=                        ALL - Show all SID          ALL
NAME=                       ALL - Show all STATNAME     ALL

Launch it on all instances :

./sqlidstat.pl
12:17:11 INST_NAME SQL_ID        NAME                                VALUE
12:17:11 BDTO_1    ff9853fmqsbdu table scan rows gotten              52562
12:17:11 BDTO_2    756yagxj5mmzn session uga memory max              65512
12:17:11 BDTO_2    756yagxj5mmzn session pga memory max              65536
12:17:11 BDTO_1    ff9853fmqsbdu session pga memory                  524288
12:17:11 BDTO_1    ff9853fmqsbdu file io wait time                   634055
12:17:11 BDTO_1    ff9853fmqsbdu session uga memory                  982264
12:17:11 BDTO_1    ff9853fmqsbdu physical read bytes                 8404992
12:17:11 BDTO_1    ff9853fmqsbdu cell physical IO interconnect bytes 8404992
12:17:11 BDTO_1    ff9853fmqsbdu physical read total bytes           8404992
12:17:11 BDTO_1    ff9853fmqsbdu logical read bytes from cache       23945216

Launch it on one instance and a particular stat :

./sqlidstat.pl inst=BDTO_1 name='logical read bytes from cache'
12:27:16 INST_NAME SQL_ID        NAME                          VALUE
12:27:16 BDTO_1    ff9853fmqsbdu logical read bytes from cache 227860480

You could also use the well known snapper script as Gwen Shapira who used it on a real practical case in this post.

I will follow that post with others posts and perl scripts I use to collect real time information based on wait events, latchs, sga stats…