Link sql_id and oracle stats

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

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

./ help
Usage:./ [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 :

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 :

./ 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…

This entry was posted in ToolKit and tagged , . Bookmark the permalink.

2 Responses to Link sql_id and oracle stats

  1. Chris says:

    Hi – you’ve done a great amount of research and study to assist your fellow DBAs
    (and/or other Exadata engineers – thank you for your excellent work!

    My question to you is ” where IS the script? I’ve read about it and seen output on this
    and other panels, but it does not appear to be in your directory of scripts. Could you be kind enough to share the current location?

    Thanks again!

Leave a Reply

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

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

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s