Ask the Expert

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

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: