Q

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?

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

Dig deeper on Oracle database performance problems and tuning

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close