oracle-developer/mystats

A variation on Jonathan Lewis's SNAP_MY_STATS package to report the resource consumption of a unit of work between two snapshots.

MYSTATS

1.0 Introduction

This repository contains two versions of the MyStats utility. This reports on the resource usage between two snapshots in an active database session. It is a combination of Jonathan Lewis's SNAP_MY_STATS package and my own re-factoring of Tom Kyte's runstats utility (also available via www.oracle-developer.net or github.com/oracle-developer/runstats). I've also added some functionality and flexibility around the statistics reporting section.

2.0 Versions

There are two versions provided.

2.1 mystats_pkg.sql

This creates via a single PL/SQL package named MYSTATS_PKG. This uses invoker rights and dynamic SQL to workaround the common issue whereby developers are not given explicit grants on the required V$ views but have V$ access via a role. See the comments in the package header for more details and usage instructions.

2.2 mystats.sql

This version is a standalone SQL*Plus script that runs MyStats from your SQLPATH without the need to create any database objects. This can be used if you are not able to create the PL/SQL package version of MyStats. See the comments in the script header for more details and usage instructions.

3.0 Version History

Version  Date            Description
-------- --------------- ----------------------------------------------
1.0      June 2007       Original version
1.1      January 2009    Added extended reporting options
2.0      October 2011    Re-design for standalone script version
2.01     November 2011   Bug-fix for numeric overflow
3.0      November 2015   Added extended snapshot and reporting options

4.0 Credits

1. Credit is given to Jonathan Lewis for his original idea of taking two snapshots to identify resource consumption.
2. Thanks to Martin Bach for suggesting (and providing a prototype) for extended snapshot and reporting options.

5.0 License

This project uses the MIT License. See https://github.com/oracle-developer/mystats/blob/master/LICENSE

Adrian Billington (c) www.oracle-developer.net

mystats's Issues

ORA-06502: PL/SQL: numeric or value error

Not sure if you already notice this, however I could not find it reported and also I’m unable to diagnostic / solve it for two days, so I’m reporting to you. Nothing really important, there is an easy workaround. Let me explain the issue I’m facing with the testcase in following:

$ sqlplus / as sysdba
Last Successful login time: Sat Jul 09 2016 06:56:31 -05:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
SQL> create user check identified by check default tablespace users temporary tablespace temp;
SQL> grant connect,dba to check;
SQL> conn check/check
SQL> @mystats start

PL/SQL procedure successfully completed.

SQL> @mystats stop

==========================================================================================
MyStats report : 09-JUL-2016 06:48:46
==========================================================================================


------------------------------------------------------------------------------------------
1. Summary Timings
------------------------------------------------------------------------------------------

Type    Statistic Name                                                               Value
------  ----------------------------------------------------------------  ----------------
TIMER   snapshot interval (seconds)                                                   7.07
TIMER   CPU time used (seconds)                                                       0.00


------------------------------------------------------------------------------------------
2. Statistics Report
------------------------------------------------------------------------------------------

Type    Statistic Name                                                               Value
------  ----------------------------------------------------------------  ----------------
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.XMLTYPE", line 272
ORA-06512: at line 1
ORA-06512: at line 135
ORA-06512: at line 203


SQL> @mystats start

PL/SQL procedure successfully completed.

SQL> @mystats stop
….
PL/SQL procedure successfully completed.

SQL> 

This is happening the first time we run the pair start/stop inside a new session without any argument (so I assume default s=a). Next time we run the script (start / stop again in same session) it works fine. I’ve tested in 11gR2 and 12c.

Using argument for start to avoid the issue:

sqlplus check/check
Last Successful login time: Sat Jul 09 2016 06:56:31 -05:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
SQL> @mystats start s=a

PL/SQL procedure successfully completed.

SQL> @mystats stop
…
==========================================================================================
End of report
==========================================================================================

PL/SQL procedure successfully completed.

SQL>

The issue is happening in procedure ms_report. Line 135 points to the for loop start. With no arguments for start, values for sqlplus variables v_ms_option and v_ms_threshold are 0 and the rest are null, exactly the same as second execution (that works). I’ve not been able to diagnose / solve it, so I’m reporting to you in case want to take a look.

Thanks in advance and best regards

Luis