Home » RDBMS Server » Server Administration » Same query produces different results
| Same query produces different results [message #372294] |
Wed, 31 January 2001 08:50  |
Kristen
Messages: 6
Registered: January 2001
|
Junior Member |
|
|
On my project, we are using Oracle 8.1.6. We are experiencing issues with
queries returning different results when run multiple times with the same data.
Our tables have been analyzed and our database is running with the Cost-based Optimizer.
We even tried adding a RULE hint to the query and it still produced different results.
All of the tables the queries pull from look fine, and one week we will have the problem
with one query and the next week it will be a different query. We have an identical
database running on 8.0.5 and the same queries work consistently on this database.
Has anyone ever seen anything like this?!?!?!
|
|
|
|
|
|
| Re: Same query produces different results [message #372297 is a reply to message #372295] |
Wed, 31 January 2001 15:46  |
Kristen
Messages: 6
Registered: January 2001
|
Junior Member |
|
|
Here's the query:
SELECT DECODE ( AL1.CURRENT_CHNL_ID, '04','04', '05', '05', '08', '08', 'Exit' ),
AL5.FMLY_PARENT_NAME,
AL5.FMLY_PARENT_DESC,
AL4.RPT_YR_QTR_WK,
AL4.RPT_WK_IN_QTR,
AL3.MIN_ORD_WK,
AL2.WEEK,
SUM ( AL2.PURCHASER_COUNT ),
SUM ( AL2.LEASE_PURCHASER_COUNT ),
SUM ( AL2.SYS_QTY ),
SUM ( AL2.LEASE_SYS_QTY ),
SUM ( AL2.COMB_REV_DISC_AMT ),
SUM ( AL2.LEASE_COMB_REV_DISC_AMT ),
SUM ( AL2.COMB_MARGIN_AMT ),
SUM ( AL2.LEASE_COMB_MARGIN_AMT )
FROM BSDD.CUSTOMER_BILL_TO_ADDRESS AL1,
BSDD.FP_WEEKLY_SUMMARY AL2,
BSDD.V_CUST_FIRST_ORD_WK AL3,
BSDD.V_REPORT_CALENDAR_WEEK AL4,
BSDD.V_FP_PLP AL5
WHERE ( AL3.CUST_NUM (+)= AL2.CUST_NUM
AND AL2.CUST_NUM = AL1.CUST_NUM (+)
AND AL5.FMLY_PARENT_NAME=AL2.FMLY_PARENT_NAME
AND AL4.FISC_WEEK_NUM=AL2.WEEK)
AND (AL5.PROD_LINE_PARENT_NAME='4IN')
GROUP BY DECODE ( AL1.CURRENT_CHNL_ID, '04','04', '05', '05', '08', '08', 'Exit' ), AL5.FMLY_PARENT_NAME,
AL5.FMLY_PARENT_DESC,
AL4.RPT_YR_QTR_WK,
AL4.RPT_WK_IN_QTR,
AL3.MIN_ORD_WK,
AL2.WEEK
We have tried different variations, like taking a field out of the SELECT statement, and sometimes it produces consistent results. For example, we took out FMLY_PARENT_DESC (which is one-to-one with FMLY_PARENT_NAME) and got the same row count 3 times. Our DBA even exported our 8.1.6 database to 8.0.5 and then we did not experience the problem. This is why we are thinking it is a bug or something. Thanks for any help you can provide.
|
|
|
|
| Re: Same query produces different results [message #372302 is a reply to message #372297] |
Wed, 31 January 2001 20:27 |
Andrew again...
Messages: 270
Registered: July 2000
|
Senior Member |
|
|
Interesting problem. I presume it is dynamic SQL - not compiled. The things which catch my eye are the 2 outer joins and their position in the where clause. I guess I would shift the outer joins down (but for no good reason which I could site). Also as you will be aware, the generation of fresh stats could change your execution plan which could cause the "bug" to manifest itself???
select table_name, num_rows, CHAIN_CNT, LAST_ANALYZED
from user_tables
where table_name in ('BSDD.CUSTOMER_BILL_TO_ADDRESS',
'BSDD.FP_WEEKLY_SUMMARY',
'BSDD.V_CUST_FIRST_ORD_WK',
'BSDD.V_REPORT_CALENDAR_WEEK',
'BSDD.V_FP_PLP')
I'd do an explain plan on the 2 different versions to see what happens?? Beware of trying to capture the plan in a trace file - it's only determined when you run TKPROD on the trace file! Maybe enabling autotrace in sqlplus is the easiest way.
|
|
|
|
| Re: Same query produces different results [message #372306 is a reply to message #372297] |
Thu, 01 February 2001 07:42 |
me
Messages: 66
Registered: August 2000
|
Member |
|
|
You did not state (or I missed it) what is returning different. The number of records for the grouped by clause or the summed values. If it is the summed values and the fields being summed are nullable fields, you will need to NVL the summed fields.
SUM ( AL2.PURCHASER_COUNT ),
SUM ( NVL(AL2.LEASE_PURCHASER_COUNT,0) ),
SUM ( NVL(AL2.SYS_QTY,0) ),
SUM ( NVL(AL2.LEASE_SYS_QTY,0) ),
SUM ( NVL(AL2.COMB_REV_DISC_AMT,0) ),
SUM ( NVL(AL2.LEASE_COMB_REV_DISC_AMT,0) ),
SUM ( NVL(AL2.COMB_MARGIN_AMT,0) ),
SUM ( NVL(AL2.LEASE_COMB_MARGIN_AMT,0) )
|
|
|
|
| Re: Same query produces different results [message #372307 is a reply to message #372297] |
Thu, 01 February 2001 09:20 |
Kristen
Messages: 6
Registered: January 2001
|
Junior Member |
|
|
The number of records for the grouped by clause are returning different. For example, I used the query:
SELECT COUNT(1) FROM
( my query);
When run 3 times in a row, I received different row counts. Plus, there are no null values in the summed fields.
Thanks for the help.
|
|
|
|
|
|
| Re: Same query produces different results [message #372369 is a reply to message #372297] |
Mon, 05 February 2001 17:18 |
Kristen
Messages: 6
Registered: January 2001
|
Junior Member |
|
|
Answers to both are 'No'. We found out that there is a bug in oracle 8.1.6 where the combination of group by and a function (like decode) can return wrong results on a parallel query. We had to apply a patch.
For anyone having the same problem - here is the info....
This is bug 1253654.
This problem was introduced in 8.1.5
Parallel query may return the wrong results if a GROUP BY clause
is used with a function rather than a column.
The wrong results may also occur if a parallel plan is run serially.
For Windows NT, looks like first fixed in patch set 81630. It is also fixed in 81613.)
|
|
|
|
| Re: Same query produces different results [message #372370 is a reply to message #372297] |
Mon, 05 February 2001 17:19 |
Kristen
Messages: 6
Registered: January 2001
|
Junior Member |
|
|
Answers to both are 'No'. We found out that there is a bug in oracle 8.1.6 where the combination of group by and a function (like decode) can return wrong results on a parallel query. We had to apply a patch.
For anyone having the same problem - here is the info....
This is bug 1253654.
This problem was introduced in 8.1.5
Parallel query may return the wrong results if a GROUP BY clause
is used with a function rather than a column.
The wrong results may also occur if a parallel plan is run serially.
For Windows NT, looks like first fixed in patch set 81630. It is also fixed in 81613.)
|
|
|
|
| Re: Same query produces different results [message #372371 is a reply to message #372297] |
Mon, 05 February 2001 17:21 |
Kristen
Messages: 6
Registered: January 2001
|
Junior Member |
|
|
Answers to both are 'No'. We found out that there is a bug in oracle 8.1.6 where the combination of group by and a function (like decode) can return wrong results on a parallel query. We had to apply a patch.
For anyone having the same problem - here is the info....
This is bug 1253654.
This problem was introduced in 8.1.5
Parallel query may return the wrong results if a GROUP BY clause
is used with a function rather than a column.
The wrong results may also occur if a parallel plan is run serially.
For Windows NT, looks like first fixed in patch set 81630. It is also fixed in 81613.)
|
|
|
|
Goto Forum:
Current Time: Wed May 15 14:03:00 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
