 |
 |
|||
| ||||
 |  | |||
|
|
|
|||||||||||||||||||||||||
Abstract 
QUERY AGAINST GV$SESSION | ||||||||||||||||||||||||||
| | |||||||||||||||||||||||||
|
|
|
|
|
Resolution History
09-NOV-04 16:46:31 GMT Can you easily recover from, bypass or work around the problem? = YES Does your system or application continue normally after the problem occurs? = YES Are the standard features of the system or application still available; is the loss of service minor? = YES ### Detailed Problem Statement: ### Running this query shows unexpected results: with qw as (select inst_id from gv$session), y as ( select unique inst_id from qw union all select unique inst_id from qw ) select * from y INST_ID --------- 2 2 Should this return the same as: select unique inst_id from gv$session union all select unique inst_id from gv$session INST_ID ---------- 1 2 1 2 ### What were you trying to accomplish when the problem occurred? ### query using a 'with clause' ### The step-by-step actions which preceded the problem: ### listed above ### Has this ever worked? ### No ### Can you reproduce the problem at will? ### Yes ### Reproducible on other platforms, systems, environments? ### Unknown ### Known recent changes to your computer environment: ### n/a Contact me via : Telephone -> 09-NOV-04 17:00:15 GMT ACTION: ======= Hello Brian, Your Service Request (TAR) has been received and we are reviewing it. We will update you as soon as possible. Thank you, Marc Status: ====== @wip 09-NOV-04 17:01:20 GMT Email Update button has been pressed: Sending email 09-NOV-04 17:53:59 GMT Called Brain :- ================ he requested me call back after an hour. @CUS 09-NOV-04 20:42:40 GMT Hi Brain, I test this query query inhouse on NON RAC database JServer Release 9.2.0.4.0 - Production SQL> with qw as (select inst_id from gv$session), y as ( select unique inst_id from qw union all select unique inst_id from qw ) select * from y / INST_ID ---------- 1 1 SQL> select unique inst_id from gv$session union all select unique inst_id from gv$session INST_ID; INST_ID ---------- 1 1 SQL> are you running RAC database ? Thanks Ashish OSS @CUS 09-NOV-04 20:42:44 GMT Email Update button has been pressed: Sending email 09-NOV-04 20:47:44 GMT New info : yes. 10-NOV-04 20:27:11 GMT UPDATE ======= I tested the same queries on 10G in a RAC environment, the following are the results SQL> with qw as (select inst_id from gv$session), y as ( select unique inst_id from qw 2 3 4 5 union all 6 select unique inst_id 7 from qw 8 ) 9 select * from y / 10 INST_ID ---------- 1 2 1 2 SQL> SQL> select unique inst_id from gv$session union all select unique inst_id from gv$session INST_ID; INST_ID ---------- 1 2 Can you apply the 9.2.0.5 or 9.2.0.6 patchset and see if it still happens because I te sted on 9.2.0.5 I still didnt get the same results though. Both queries return different results. 10-NOV-04 20:49:52 GMT Email Update button has been pressed: Sending email. 10-NOV-04 20:56:37 GMT New info : Applying patches on production instances it not something that can be done with out extensive testing on test instances. This is a lengthily process. At the moment, there is no way we can upgrade. We are using the ‘with’ clause in many places. Are there problems using the ‘with’ in 9.2.0.4? If so, please let me know. 10-NOV-04 22:04:45 GMT UPDATE ======== I couldnt find any known bugs /issues with the "WITH" clause on 9.2.0.4 10-NOV-04 22:04:49 GMT Email Update button has been pressed: Sending email. 10-NOV-04 22:08:45 GMT New info : Is this a new bug? 11-NOV-04 20:43:32 GMT Inorder to file a new bug, our development wants us to test on the latest patchset. So I cannot file a new bug on 9.2.0.4 Can you atleast apply the 9.2.0.5 patchset and test it on your test machine and see if you get the same results 11-NOV-04 20:43:39 GMT Email Update button has been pressed: Sending email. 11-NOV-04 21:22:35 GMT New info : I'm sorry, our test machines are being used. 12-NOV-04 19:43:22 GMT I can only file a bug on the latest version. So please apply 9.2.0.5 patchset on your test machine and test this. If you still reproduce it, then I can file a new bug Also, I could not fiel any known issues or bugs with the WITH clause. 12-NOV-04 19:43:25 GMT Email Update button has been pressed: Sending email 15-NOV-04 20:07:17 GMT New info : It is quite unreasonable to request that we upgrade. Version 9.2.0.4 is a supported version. Can you forward this tar to someone who can help? 15-NOV-04 22:05:59 GMT UPDATE ======== ACtually, I tried this 9.2.0.5 Here are the results on 9.2 node 2 [L9202@stlnx4]/home/lsupport> sqlplus "/ as sysdba" SQL*Plus: Release 9.2.0.5.0 - Production on Tue Nov 16 17:01:13 2004 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to: Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production With the Partitioning, Real Application Clusters, OLAP and Oracle Data Mining tions JServer Release 9.2.0.5.0 - Production SQL> with qw as (select inst_id from gv$session), 2 y as ( 3 select unique inst_id 4 from qw 5 union all 6 select unique inst_id 7 from qw 8 ) 9 select * from y ; INST_ID ---------- 2 2 SQL> select unique inst_id 2 from gv$session 3 union all 4 select unique inst_id 5 from gv$session; INST_ID ---------- 1 2 1 2 node 1 [L9201@stlnx3]/home/lsupport> sqlplus "/ as sysdba" SQL*Plus: Release 9.2.0.5.0 - Production on Tue Nov 16 16:03:04 2004 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to: Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production With the Partitioning, Real Application Clusters, OLAP and Oracle Data Minin tions JServer Release 9.2.0.5.0 - Production SQL> with qw as (select inst_id from gv$session), 2 y as ( 3 select unique inst_id 4 from qw 5 union all 6 select unique inst_id 7 from qw 8 ) 9 select * from y ; INST_ID ---------- 1 1 SQL> SQL> select unique inst_id 2 from gv$session 3 union all 4 select unique inst_id 5 from gv$session; INST_ID ---------- 1 2 1 2 SQL> This looks similar to the results you got. I filed a bug4014652 with this. 16-NOV-04 22:48:16 GMT Associated bug 4014652 has been updated and is still at status: 16. 17-NOV-04 00:49:22 GMT Associated bug 4014652 has been updated and is still at status: 16. 17-NOV-04 19:20:33 GMT Associated bug 4014652 has been updated and is still at status: 16. 17-NOV-04 19:51:01 GMT Associated bug 4014652 has been updated and is still at status: 16. 17-NOV-04 21:20:37 GMT Associated bug 4014652 has been updated and is still at status: 16. 18-NOV-04 02:51:12 GMT Associated bug 4014652 has been updated and is still at status: 11. 19-NOV-04 18:55:22 GMT Associated bug 4014652 has been updated and is still at status: 11. 30-NOV-04 14:31:18 GMT New info : Any updates? 06-DEC-04 22:56:03 GMT New info : What's going on? I need to know what can and can not be used here. Is the 'with' an issue? I use the 'with' clause a lot. 07-DEC-04 18:20:54 GMT I filed a bug for our case and it is still with development, they are working on the problem . There are no workarounds present. Will let you know as soon as I hear from development 07-DEC-04 18:20:59 GMT Email Update button has been pressed: Sending email. 08-DEC-04 20:36:51 GMT Associated bug 4014652 has been updated and is still at status: 11. 08-DEC-04 21:06:07 GMT Associated bug 4014652 has been updated and has changed status to 92. 08-DEC-04 21:32:39 GMT The bug that was filed was closed as not a bug here is the update from the bug this is not a bug but a feature. 1. GV$ queries are not built to return correct results 2. the way a GV$ queries is built may influence its results because transient data are accessed thru it. In the with clause case we execute a single GV$ query which access and materialize the result in the temp table using only one session. In the second case, we have 2 GV$ queries running concurrently union all with two underlying slave group Closing as not a bug 08-DEC-04 21:32:44 GMT Email Update button has been pressed: Sending email. 10-DEC-04 15:51:00 GMT New info : It's beyond me why this query returns correct results: OLS2> with qw as (select inst_id from gv$session) 2 select unique inst_id from qw 3 / INST_ID ---------- 1 2 while this one doesn't: OLS2> with qw as (select inst_id from gv$session), 2 y as (select unique inst_id 3 from qw 4 union all 5 select unique inst_id 6 from qw 7 ) 8 select * from y 9 / INST_ID ---------- 2 2 According to set theory there is something wrong. 10-DEC-04 18:36:24 GMT Good afternoon Madhavi was correct, When you are dealing with :- From Madhavi's update. In the with clause case we execute a single GV$ query which access and materialize the result in the temp table using only *****one session*****. In the second case, we have *****2 GV$ queries running concurrently***** union al l with two underlying slave group This is the big difference. Best regards Mark 10-DEC-04 18:36:32 GMT Email Update button has been pressed: Sending email. 10-DEC-04 19:48:03 GMT New info : I don't fully understand the nature of gv$ tables. When I create a test table and run the queries the results are consistent. SQL> with qw as (select col from test_table) 2 select unique col from qw COL ---------- 1 2 SQL> with qw as (select col from test_table), 2 y as (select unique col 3 from qw 4 union all 5 select unique col 6 from qw 7 ) 8 select * from y SQL> / COL ---------- 1 2 1 2 Could you point me the direction of some documentation? 10-DEC-04 23:27:27 GMT UPDATE ACTION: ============= Hi , I have been asked to communicate to you that I will be out of the office on vacation until Monday, 20-DEC-2004, at 10:30 AM EDT US. If you require immediate assistance for this issue and need the TAR to be reassigned to an available support engineer, please update the TAR via MetaLi nk or phone 1-800-223-1711 (or your local support number) to request that the TA R be reassigned. Otherwise, no update on your part is necessary at this time and I will follow-up with you when I return on 20-DEC-2004. Thank you. Mark Brewer-Tillotson ORACLE Support High Availability Team. RMSC Colorado USA. GMT-7 13-DEC-04 15:15:26 GMT UPDATE ======= The v$tables are very volatile and hence the results comparing the table we create to this table is not correct. What is happening in your case i s the query with the with clause is selecting once from the gv$ and then materia lize the result into the temp table. While query # 2 is running 2 separate queri es. Thanks, Anil STATUS ======= @CUS 15-DEC-04 14:34:04 GMT New info : Thank you for your help. I ran the query against a 10g 3 node rac and the results are not consistent with the 9iR2. For some reason, the 10g RAC gv$ view sees all the nodes. 1 with qw as (select inst_id from gv$session), 2 y as ( select unique inst_id from qw 3 union all 4 select unique inst_id from qw 5 ) 6* select * from y SQL> / INST_ID ---------- 1 2 3 1 2 3 16-DEC-04 19:40:44 GMT . CTC Update (Published): ====================== Hello Brian, Thank you for the update. The owning support engineer, MBREWERT.US (Mark), has gone off shift for the evening and is not currently available; however, they will have the opport unity to review and progress the issue during their next shift, which commences at 10:30AM EST (8:30AM MST) US on Monday, 20-DEC-2004. In Addition, ANAIR.US (A nil), is unavailable until 8:30AM EST US on Friday, 17-DEC-2004. In the mean time, if you feel that this is a critical down production issue for which you requ ire immediate assistance from an available support engineer, please advise us of this by specifically indicating this in a TAR update or by phoning your local s upport number to advise call response of your need for attention; otherwise, no update is required on your part at this time and MBREWERT.US will follow-up with you during their next shift. Thank you. Eric Morris High Availability Cluster Technical Coordinator - the Americas STATUS ======= @CTS -- Customer to be Served . 04-JAN-05 03:10:21 GMT Good evening I have tonight reproduced your results. indeed in version 9.2 the wrong results were being reported yet in 10g (10.1.0.2 tested) the correct result s of all nodes are being reported. However this is not completely the case. What Anil was stating above is true. What is now happening in the 10g release is th at the data is being materialized in a more accurate method and hence it appears that we have a wrong results case when in reality it is just be represented mor e accurately. Best regards Mark 04-JAN-05 03:10:29 GMT Email Update button has been pressed: Sending email. 04-JAN-05 03:37:30 GMT New info : I have no idea how 1 sql statement can return 2 different results and they both be deemed as accurate. Like I said before it’s irrational and not consistent with basic set theory. E. F. Codd would be ashamed. 04-JAN-05 04:02:15 GMT Good evening Brian, <<< out call >>> Voicemail , no message left. Hi Brian, the problem is here that we are looking at both static data and volotile data in th e same query and comparing them. This breaks the base set theory where you can only look at static data. Now 10g has obviously realised this and is resolving the query in a way that would correct the issue. I have come across over the past 10 years several episodes like this of how you can get "different" results fro m the same query yet they are both in their own way correct. Depending on chang ing factors in how the query is resolved. I will say though that this is the first one in 5.5 years that has caught my attention like this in a version release change. Best regards Mark 04-JAN-05 04:02:28 GMT Email Update button has been pressed: Sending email. 19-JAN-05 00:00:31 GMT TAR passed SCL review date. Setting to HCL. | ||
| | |