Re: Scary 10gR2 query problem

From: Ray Stell <stellr_at_cns.vt.edu>
Date: Wed, 24 May 2006 12:05:30 -0400
Message-ID: <20060524160530.GC1746@cns.vt.edu>

SQL> select count(*) from oddity;

COUNT(*)

67311

SQL> create table very_odd as select * from oddity;

Table created.

SQL> select count(*) from very_odd;

COUNT(*)

67311

SQL> select * from v$version;

BANNER

Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Prod PL/SQL Release 10.2.0.2.0 - Production
CORE 10.2.0.2.0 Production
TNS for Linux: Version 10.2.0.2.0 - Production NLSRTL Version 10.2.0.2.0 - Production

$ opatch lsinventory

Installed Top-level Products (3):

Oracle Database 10g                                                  10.2.0.1.0
Oracle Database 10g Products                                         10.2.0.1.0
Oracle Database 10g Release 2 Patch Set 1                            10.2.0.2.0

There are 3 products installed in this Oracle Home.

Interim patches (1) :

Patch 5079037 : applied on Mon May 22 11:28:08 EDT 2006

Created on 28 Apr 2006, 01:20:34 hrs PST8PDT Bugs fixed:
5079037, 5079038

On Wed, May 24, 2006 at 06:28:34AM -0700, fairlie rego wrote:
> Hi Ian,
>
> Assuming the problem is with the optimizer do you get correct results if you
> set optimizer_features_enable to 10.1.0 at the session level?
>
> Regards,
> Fairlie
> "Ian Cary (C)" <Ian.Cary_at_ordnancesurvey.co.uk> wrote:
>
> As there seem to a few people using 10.2 (10.2.0.2 on Solaris 9 in my
> case) I thought I'd share a scary issue with you all.
>
> Odd in the example below is a reasonably complex view that has been
> working fine in 10.1 for quite a while.
>
> MP409SH2> select count(*) from odd;
>
> COUNT(*)
> ----------------
> 67311
>
> Elapsed: 00:01:15.56
> MP409SH2> create table very_odd as select * from odd;
>
> Table created.
>
> Elapsed: 00:07:34.00
> MP409SH2> select count(*) from very_odd;
>
> COUNT(*)
> ----------------
> 1371016
>
> Elapsed: 00:00:01.84
>
> This is a 100% genuine case where the select statement returns the wrong
> (substantially) number of rows. In 10.1 the view returns the correct
> 67311 rows.
> There doesn't seem to be any pattern or reason for the error and I only
> noticed it because I had the 10.1 results to hand.
> I'll be raising a tar as soon as Metalink sorts itself out but I thought
> I'd share this as I'm not sure I can 100% trust the results of any
> select statement on 10.2
>
> Cheers,
>
> Ian
> .
>
>
> This email is only intended for the person to whom it is addressed and may contain confidential information. If you have received this email in error, please notify the sender and delete this email which must not be copied, distributed or disclosed to any other person.
>
> Unless stated otherwise, the contents of this email are personal to the writer and do not represent the official view of Ordnance Survey. Nor can any contract be formed on Ordnance Survey's behalf via email. We reserve the right to monitor emails and attachments without prior notice.
>
> Thank you for your cooperation.
>
> Ordnance Survey
> Romsey Road
> Southampton SO16 4GU
> Tel: 023 8079 2000
> http://www.ordnancesurvey.co.uk
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
>
>
> Fairlie Rego
> Senior Oracle Consultant
> Optus Telecommunications
> www.optus.com.au
>
>
>
>
>
>
>
>
> ---------------------------------
> How low will we go? Check out Yahoo! Messenger?s low PC-to-Phone call rates.

-- 
--
http://www.freelists.org/webpage/oracle-l

Received on Wed May 24 2006 - 11:05:30 CDT