Re: DUAL revisited

...if you're going to query from an account other than SYS, then please SET AUTOTRACE TRACEONLY STATISTICS (instead of SET AUTOTRACE ON) so it doesn't try to do an EXPLAIN PLAN. You cannot grant SELECT on X$ tables to anyone other than SYS, which affects EXPLAIN PLAN also...

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Friday, August 02, 2002 11:15 AM

> 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
> --------------------------------------------------------------------
> 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).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Tim Gorman
  INET: Tim_at_SageLogix.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).