Re: Scary 10gR2 query problem
- From: Ray Stell <stellr@xxxxxxxxxx>
- To: fairlie rego <fairlie_r@xxxxxxxxx>
- Date: Wed, 24 May 2006 12:05:30 -0400
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@xxxxxxxxxxxxxxxxxxxx> 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
>
> --
> //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.
--
--
//www.freelists.org/webpage/oracle-l
Other related posts:
