Database Reference
In-Depth Information
When we enable AUTOTRACE in SQL*Plus, SQL*Plus will perform the following actions when we execute
DML operations (
INSERT
,
UPDATE
,
DELETE
,
SELECT
, and
MERGE
):
1.
It will create a new session using the current connection, if the secondary session does not
already exist.
2.
It will ask this new session to query the
V$SESSTAT
view to remember the initial statistics
values for the session in which we will run the DML. This is very similar to the function the
watch_stat.sql
script performed for us in Chapter 4.
3.
It will run the DML operation in the original session.
4.
Upon completion of that DML statement, SQL*Plus will request the other session to query
V$SESSTAT
again and produce the report displayed previously showing the difference in
the statistics for the session that executed the DML.
If you turn off AUTOTRACE, SQL*Plus will terminate this additional session and you will no longer see it in
V$SESSION
. Why does SQL*Plus do this trick? The answer is fairly straightforward. SQL*Plus does it for the same
reason that we used a second SQL*Plus session in Chapter 4 to monitor memory and temporary space usage: if we
had used a single session to monitor memory usage, we would have been using memory to do the monitoring. By
observing the statistics in a single session, we necessarily would change those statistics. If SQL*Plus used a single
session to report on the number of I/Os performed, how many bytes were transferred over the network, and how
many sorts happened, then the queries used to find these details would be adding to the statistics themselves. They
could be sorting, performing I/O, transferring data over the network (one would assume they would), and so on.
Hence, we need to use another session to measure correctly.
So far, we've seen a connection with one or two sessions. Now we'd like to use SQL*Plus to see a connection
with no session. That one is pretty easy. In the same SQL*Plus window used in the previous example, simply type the
misleadingly named command,
DISCONNECT
:
EODA@ORA12CR1> disconnect
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Technically, that command should be called
DESTROY_ALL_SESSIONS
instead of
DISCONNECT
, since we haven't
really disconnected physically.
■
Note
the true disconnect in SQL*plus is “exit,” as you would have to exit to completely destroy the connection.
We have, however, closed all of our sessions. If we open another session using some other user account and
query (replacing
EODA
with your account name, of course):
$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Thu Mar 20 15:57:56 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SYS@ORA12CR1> select * from v$session where username = 'EODA';
no rows selected
