As you probably already know, the Session Snapper has been designed to be a very easy-to-use performance tool. It is especially useful in database environments where there are no decent performance tools pre-installed and available.
Snapper doesn’t require any setup, all you need is to log on to the database using sqlplus and download snapper.sql script to your computer.
Well, actually the second part is not required, as Oracle sqlplus allows you to run scripts from http and ftp locations!
C:>sqlplus "sys/oracle@ora92 as sysdba" SQL*Plus: Release 10.2.0.3.0 - Production on Thu Aug 30 23:00:10 2007 Copyright (c) 1982, 2006, Oracle. All Rights Reserved. Connected to: Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.8.0 - Production SQL> @https://github.com/tanelpoder/tpt-oracle/blob/master/i.sql Tanel's sqlplus http test... https://github.com/tanelpoder/tpt-oracle/blob/master/i.sql USER SYSDATE ------------------------------ --------- SYS 30-AUG-07 NAME --------- ORA92 INSTANCE_NAME HOST_NAME ---------------- ---------------------------------------------------------------- ora92 WINDOWS01 You should be *very* sure that noone can change the scripts on the server without your knowing!!! SQL>
Cool stuff or what? :)
Let’s see how this relates to everyday DBA life…
Probably everyone who has managed tens or hundreds of Oracle databases, knows this situation: You need to get something done fast, but for whatever environmental reasons you don’t have access to your normal work environment, PC, GUI tools and scripts on your hard-disk. And you just don’t have your scripts available on every one of those servers. Or you do some the scripts there, but they were copied there 4 years ago and are outdated.
This normally means that you will log on to the server, fire up sqlplus and spend most of your time on repeatedly typing in long commands for doing very simple operations. Finding list of active users or tablespace level free space breakdown can be done in a second using a script or mouseclick, however it may take long time if you need to manually type in a SQL statement for that (on a crappy terminal!)
So, one solution would be copying your scripts to the server you’re working on – if you have the time and ability to do that.
Another option would be keeping a corporate script repository on a webserver and access the required scripts using @http callouts from sqlplus.
There’s one technical issue though. Unlike regular directories, the http and ftp locations will not work in SQLPATH variable (which is the automatic sql search path variable). So you can’t just type @df and have sqlplus automatically look it up from your webpage. So you would manually need to type the full HTTP URL of your script every time you want to execute it.
Four years ago I proposed a solution for running the remote scripts through a wrapper @r script, in an Oracle-L post (time flies fast, huh?)
That solution was somewhat clumsy though as it required the wrapper script still to be downloaded and stored on local disk and the parameters to script executed needed to be embedded in double quotes etc..
There’s another much simpler solution though – just using a sqlplus variable for holding the remote path:
SQL> define remote=https://github.com/tanelpoder/tpt-oracle/blob/master SQL> SQL> @&remote/snapper out 1 1 "select sid from v$mystat where rownum = 1" -- Session Snapper v1.03 by Tanel Poder ( ) -------------------------------------------------------------------------------------------------------------------------------------------- -- SID, SNAPSHOT START , SECONDS , TYPE, STATISTIC , DELTA, D/SEC, HDELTA, HD/SEC -------------------------------------------------------------------------------------------------------------------------------------------- DATA, 10, 20070830 23:02:36, 1, STAT, opened cursors cumulative , 5, 5, 5 5 DATA, 10, 20070830 23:02:36, 1, STAT, opened cursors current , 1, 1, 1 1 DATA, 10, 20070830 23:02:36, 1, STAT, recursive calls , 136, 136, 136 136 DATA, 10, 20070830 23:02:36, 1, STAT, recursive cpu usage , 8, 8, 8 8 DATA, 10, 20070830 23:02:36, 1, STAT, session logical reads , 71, 71, 71 71 DATA, 10, 20070830 23:02:36, 1, STAT, session uga memory , 65464, 65464, 65.46k 65.46k DATA, 10, 20070830 23:02:36, 1, STAT, session uga memory max , 130928, 130928, 130.93k 130.93k DATA, 10, 20070830 23:02:36, 1, STAT, session pga memory , 16604, 16604, 16.6k 16.6k DATA, 10, 20070830 23:02:36, 1, STAT, session pga memory max , 278748, 278748, 278.75k 278.75k DATA, 10, 20070830 23:02:36, 1, STAT, consistent gets , 71, 71, 71 71 DATA, 10, 20070830 23:02:36, 1, STAT, physical reads , 1, 1, 1 1 DATA, 10, 20070830 23:02:36, 1, STAT, free buffer requested , 1, 1, 1 1 DATA, 10, 20070830 23:02:36, 1, STAT, consistent gets - examination , 27, 27, 27 27 DATA, 10, 20070830 23:02:36, 1, STAT, calls to get snapshot scn: kcmgss , 13, 13, 13 13 DATA, 10, 20070830 23:02:36, 1, STAT, no work - consistent read gets , 44, 44, 44 44 DATA, 10, 20070830 23:02:36, 1, STAT, table fetch by rowid , 33, 33, 33 33 DATA, 10, 20070830 23:02:36, 1, STAT, rows fetched via callback , 7, 7, 7 7 DATA, 10, 20070830 23:02:36, 1, STAT, index fetch by key , 7, 7, 7 7 DATA, 10, 20070830 23:02:36, 1, STAT, index scans kdiixs1 , 11, 11, 11 11 DATA, 10, 20070830 23:02:36, 1, STAT, cursor authentications , 1, 1, 1 1 DATA, 10, 20070830 23:02:36, 1, STAT, buffer is pinned count , 14, 14, 14 14 DATA, 10, 20070830 23:02:36, 1, STAT, buffer is not pinned count , 68, 68, 68 68 DATA, 10, 20070830 23:02:36, 1, STAT, workarea executions - optimal , 30, 30, 30 30 DATA, 10, 20070830 23:02:36, 1, STAT, parse count (total) , 12, 12, 12 12 DATA, 10, 20070830 23:02:36, 1, STAT, execute count , 13, 13, 13 13 DATA, 10, 20070830 23:02:36, 1, STAT, sorts (memory) , 15, 15, 15 15 DATA, 10, 20070830 23:02:36, 1, STAT, sorts (rows) , 2056, 2056, 2.06k 2.06k DATA, 10, 20070830 23:02:36, 1, WAIT, db file sequential read , 29628, 29628, 29.63ms 29.63ms -- End of snap 1 PL/SQL procedure successfully completed. SQL>
So, you define the remote location into a variable once in sqlplus session and can run your scripts with much less typing effort. Obviously you can make the remote variable name shorter to type even less:
SQL> def r=https://github.com/tanelpoder/tpt-oracle/blob/master SQL> @&r/df TABLESPACE_NAME TotalMB UsedMB FreeMB % Used Ext Used ------------------------------ ---------- ---------- ---------- ------ --- ---------------------- EXAMPLE 121 121 0 100% YES |####################| INDX 25 1 24 4% YES |# | SYSTEM 390 286 104 74% YES |############### | TEMP 41 41 0 100% YES |####################| TOOLS 92 92 0 100% YES |####################| UNDOTBS1 200 5 195 3% YES |# | USERS 25 1 24 4% YES |# | X 30 1 29 4% YES |# | 8 rows selected.
NB! The other BIG concern with this approach is that you need to be VERY sure that noone can change those remote scripts without your knowing!
This essentially means that do not try to run any scripts directly from internet, including my site.
