Oracle-L: RE: DUAL revisited

From: Kathy Duret <KathyD_at_belkin.com>
Date: Fri, 02 Aug 2002 09:15:56 -0800
Message-ID: <F001.004AA1AF.20020802091556@fatcity.com>

I followed the instructions but I get the error below. I do not get the same error when I select from dual. And yes I did to the public grant. And other clues? DB 8.1.7.3

Thanks Kathy

Execution Plan                                                                  
----------------------------------------------------------                      
ERROR:                                                                          
ORA-01039: insufficient privileges on underlying objects of the view            
                                                                                
                                                                                
SP2-0612: Error generating AUTOTRACE EXPLAIN report                             
                                                                                
Statistics                                                                      
----------------------------------------------------------                      
          0  recursive calls                                                    
          0  db block gets                                                      
          0  consistent gets                                                    
          0  physical reads                                                     
          0  redo size                                                          
        371  bytes sent via SQL*Net to client                                   
        426  bytes received via SQL*Net from client                             
          2  SQL*Net roundtrips to/from client                                  
          0  sorts (memory)                                                     
          0  sorts (disk)                                                       
          1  rows processed

-----Original Message-----
Sent: Friday, August 02, 2002 8:13 AM
To: Multiple recipients of list ORACLE-L

I've been reading the thread on DUAL vs X$DUAL, read the referenced article and tried some testing on our system. I do a simple select from DUAL and I get the same statistics as were in the article, but sometimes I also get 1 memory sort. Does anyone know why this would be? And if this is true, that really makes me want to change over to X$DUAL. Also, what is the consensus about creating a view for DUAL that references X$DUAL? I'm running the Oracle Application and cannot change all of their code to use something other than DUAL, and there is a lot of it.

Here are the statistics on the select. Notice the difference between the first vs repeat of the same seelct.

Connected to:
Oracle8i Enterprise Edition Release 8.1.7.1.1 - Production With the Partitioning option
JServer Release 8.1.7.1.1 - Production

SQL> set autotrace traceonly
SQL> select 'y' from dual;

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1) 1 0 TABLE ACCESS (FULL) OF 'DUAL' (Cost=2 Card=1)

Statistics

          0  recursive calls
          4  db block gets
          1  consistent gets
          0  physical reads
          0  redo size
        361  bytes sent via SQL*Net to client
        425  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> / Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1) 1 0 TABLE ACCESS (FULL) OF 'DUAL' (Cost=2 Card=1)

Statistics

          0  recursive calls
          4  db block gets
          1  consistent gets
          0  physical reads
          0  redo size
        361  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> Thanks for your input.

John Zoltak
North American Mfg Co
4455 East 71st Street
Cleveland Ohio 44105

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: John Zoltak
INET: JohnZoltak_at_namfg.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).

Confidential
This e-mail and any files transmitted with it are the property of Belkin Components and/or its affiliates, are confidential, and are intended solely for the use of the individual or entity to whom this e-mail is addressed. If you are not one of the named recipients or otherwise have reason to believe that you have received this e-mail in error, please notify the sender and delete this message immediately from your computer. Any other use, retention, dissemination, forwarding, printing or copying of this e-mail is strictly prohibited.
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Kathy Duret
INET: KathyD_at_belkin.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------