Here's a trivial example. I'm issuing 2 statements 1) a drop table and 2) creating a table of select * from v$mystat before each command. Then after I'm doing sql that shows me the changes from that point to now.
SQL> select count(1) from emp;
Table MY_STATS dropped.
Table MY_STATS created.
COUNT(1)
----------
14
NAME DELTA
---------------------------------------------------------------- ----------
logical read bytes from cache 1859584
session pga memory 1507328
session uga memory 1035504
KTFB alloc space (block) 65536
cell physical IO interconnect bytes 32768
physical write total bytes 24576
physical write bytes 24576
redo size 16200
.....
This is on the same connection you are executing on so whatever is being done will show in the stats as well. One thing still being worked on is the ability to clone and pass some context to another connect and have these pre and post things done on that connection keeping the main connection free of that noise.
The way to do this is the introduction of 2 new set commands. The names should be a clue as to which is which : set precommand and set postcommand. They can hold 1 or N commands to be run.
set precommand drop table my_stats; create table my_stats as select * from v$mystat;
set postcommand select s.name, ( m2.value - m.value ) delta from my_stats m, v$mystat m2, V$STATNAME s where m.STATISTIC# = m2.STATISTIC# and m.STATISTIC# = s.STATISTIC# and m.value != m2.value order by 2 desc;
select count(1) from user_objects where object_name like 'E%';
~
The current downside of this method is that it needs to be all on one line. So, if there's multiple commands to be run it's kinda ugly. Like in this precommand where I drop and create a table. The great thing about this is that the command to be run could be @mypre.sql
This allows me to better format the sql and make it much much easier to read and maintain.
└─>cat pre.sql
drop table my_stats;
create table my_stats as select * from v$mystat;
└─>cat post.sql
select s.name, ( m2.value - m.value ) delta
from my_stats m, v$mystat m2, V$STATNAME s
where m.STATISTIC# = m2.STATISTIC#
and m.STATISTIC# = s.STATISTIC#
and m.value != m2.value order by 2 desc;
Then the sql set command look just like expected.
set precommand @pre.sql
set postcommand @post.sql
Now if you want to mess with people throw something like this in their login.sql when they aren't looking
SQL> set postcommand select 'Better go tune that before Joel sees it' MOTD from dual;
Then on every command they will get that message.
SQL> select 1 from dual;
1
----------
1
MOTD
---------------------------------------
Better go tune that before Joel sees it
SQL>