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