Seeing different statistics when connecting as internal

I run Oracle 8i Enterprise Edition Release 8.1.7.4.1 on Windows 2003 Server. I use the SQL*Plus Autotrace facility and see different results in statistics if I connect with sys user credentials and with internal credentials. The details are the following:

1. Connecting as sys 

 SQL> conn sys/<sys_pwd>
 Connected.
 SQL> set autotrace on
 SQL> select count(*) from dual;
 
   COUNT(*)
 ----------
          1
 
 1 row selected.
 
 
 Execution Plan
 ----------------------------------------------------------
    0      SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=1 Card=1)
    1    0   SORT (AGGREGATE)
    2    1     TABLE ACCESS (FULL) OF 'DUAL' (Cost=1 Card=1)
  
 Statistics
 ----------------------------------------------------------
           0  recursive calls
           4  db block gets
           1  consistent gets
           0  physical reads
           0  redo size
         367  bytes sent via SQL*Net to client
         425  bytes received via SQL*Net from client
           2  SQL*Net roundtrips to/from client
           0  sorts (memory)
           0  sorts (disk)
           1  rows processed
 
 SQL>
2. Connecting as internal 
 
 SQL> conn internal/<internal_pwd>
 Connected.
 SQL> set autotrace on
 SQL> select count(*) from dual;
 
   COUNT(*)
 ----------
          1
 
 1 row selected.
 
 
 Execution Plan
 ----------------------------------------------------------
    0      SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=1 Card=1)
    1    0   SORT (AGGREGATE)
    2    1     TABLE ACCESS (FULL) OF 'DUAL' (Cost=1 Card=1)
   
 Statistics
 ----------------------------------------------------------
           0  recursive calls
           0  db block gets
           0  consistent gets
           0  physical reads
           0  redo size
           0  bytes sent via SQL*Net to client
           0  bytes received via SQL*Net from client
           0  SQL*Net roundtrips to/from client
           0  sorts (memory)
           0  sorts (disk)
           1  rows processed
 
 SQL>
In that way, in case I connect as sys, I can see all statistics. If I connect as internal, I see no statistics but rows processed. What is a reason that could be?

    Requires Free Membership to View

    Login

I honestly can't account for this one, but I'd ask in return if this behavior is consistent between calls (i.e., run the query more than once when logged in each way). I'd also ask if the behavior has any practical effect on the run time of your applications. If not, it's probably not worth investigating further. I'm not able to do any research on this one myself, as I do not have an Oracle8i database available.

This was first published in June 2005

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top