Oracle-L: RE: Win2k/8.1.7.3/Optimizer Weirdness

From: Gogala, Mladen <MGogala_at_oxhp.com>
Date: Tue, 19 Nov 2002 11:21:11 -0800
Message-ID: <F001.00507053.20021119112111@fatcity.com>

This is 9.2 and Solaris:

SQL*Plus: Release 9.2.0.1.0 - Production on Tue Nov 19 14:16:21 2002

SQL> connect rman817 as sysdba
Enter password:
Connected.

SQL>
SQL>
SQL> select /*+ RULE */ file_name from dba_temp_files;

no rows selected

SQL> ed
SP2-0110: Cannot create save file "afiedt.buf"

SQL> select file_name from dba_temp_files;

FILE_NAME

/d02/oradata/RMAN/temp01.dbf

SQL> On the other hand, this works like a charm.

SQL> alter session set optimizer_mode=rule;

Session altered.

SQL> select file_name from dba_temp_files;

FILE_NAME

/d02/oradata/RMAN/temp01.dbf

SQL> That means that "RULE" hint can cause a query to return incorrect results. It is obviously a bug. I'll try it on 9.2.0.2 tonight and see what happens. The bug might be fixed. I successfully reproduced the behavior on my 8i databases.

> -----Original Message-----
> From: Clark, Tommy R [mailto:TOMMY.R.CLARK_at_saic.com]
> Sent: Tuesday, November 19, 2002 1:50 PM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: Win2k/8.1.7.3/Optimizer Weirdness
>
>
> I was able to duplicate this on my 9.0.1.1.1 database running
> on Win2K.
>
> -----Original Message-----
> Sent: Tuesday, November 19, 2002 1:17 PM
> To: Multiple recipients of list ORACLE-L
>
>
> Hi Listers:
>
> Has anyone ever seen something like this before? Can somebody
> explain it to me?
>
> Thanks,
> Mike
>
> SQL>
> SQL> select file_name from dba_temp_files;
>
> File Name
> -----------------------------------------------------
> E:\ORANT\ORADATA\ECRMP\TEMP01.DBF
>
> 1 row selected.
>
> SQL> select /*+ RULE */ file_name from dba_temp_files;
>
> no rows selected
>
> SQL>
>
> ---
> ==============================================================
> =============
> Michael P. Vergara
> Oracle DBA
> Guidant Corporation
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Vergara, Michael (TEM)
> INET: mvergara_at_guidant.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> 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: Clark, Tommy R
> INET: TOMMY.R.CLARK_at_saic.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> 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: Gogala, Mladen
  INET: MGogala_at_oxhp.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
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).

Received on Tue Nov 19 2002 - 13:21:11 CST