Home » SQL & PL/SQL » SQL & PL/SQL » Same query different results (10.2.0.3 on solaris)
Same query different results [message #375984] Mon, 15 December 2008 09:49 Go to next message
amitkr
Messages: 17
Registered: November 2007
Junior Member
Hi,

The 2 queries below are same except for the ordered hint(different plans). The one without the hint is showing incorrect result(returning only 1 row should be 22).

Can any one explain this strange behavior:

  1  select rc.cse_id
  2  from ak_case rc, ak_evt re,ak_cse uc,dtl_cse dc
  3  where rc.cse_id=dc.cse_id
  4  and  rc.cse_id=re.cse_id
  5  and rc.cse_id = uc.cse_id
  6* and uc.user_id=292
SQL> /

Elapsed: 00:00:00.17

Execution Plan
----------------------------------------------------------                                                                                                                                              
Plan hash value: 3024491689                                                                                                                                                                             
                                                                                                                                                                                                        
--------------------------------------------------------------------------------------------------------------------                                                                                    
| Id  | Operation                            | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |                                                                                    
--------------------------------------------------------------------------------------------------------------------                                                                                    
|   0 | SELECT STATEMENT                     |                             |     1 |    31 |    13  (24)| 00:00:01 |                                                                                    
|   1 |  TEMP TABLE TRANSFORMATION           |                             |       |       |            |          |                                                                                    
|   2 |   LOAD AS SELECT                     | SYS_TEMP_0FD9D670E_6A0D08CB |       |       |            |          |                                                                                    
|   3 |    NESTED LOOPS                      |                             |     1 |    38 |     3   (0)| 00:00:01 |                                                                                    
|   4 |     MERGE JOIN CARTESIAN             |                             |     1 |    32 |     2   (0)| 00:00:01 |                                                                                    
|*  5 |      INDEX RANGE SCAN                | PK_ak_cse                |     1 |    26 |     0   (0)| 00:00:01 |                                                                                    
|   6 |      BUFFER SORT                     |                             |  2218K|    12M|     2   (0)| 00:00:01 |                                                                                    
|*  7 |       INDEX RANGE SCAN               | IDX_RE_cse_id_DIAGNOSIS    |  2218K|    12M|     2   (0)| 00:00:01 |                                                                                    
|*  8 |     INDEX UNIQUE SCAN                | PK_ak_case                 |     1 |     6 |     1   (0)| 00:00:01 |                                                                                    
|   9 |   LOAD AS SELECT                     | SYS_TEMP_0FD9D670E_6A0D08CB |       |       |            |          |                                                                                    
|  10 |    NESTED LOOPS                      |                             |     1 |    32 |     1   (0)| 00:00:01 |                                                                                    
|* 11 |     INDEX RANGE SCAN                 | PK_ak_cse                |     1 |    26 |     0   (0)| 00:00:01 |                                                                                    
|* 12 |     INDEX UNIQUE SCAN                | PK_ak_case                 |     1 |     6 |     1   (0)| 00:00:01 |                                                                                    
|* 13 |   HASH JOIN                          |                             |     1 |    31 |     9  (34)| 00:00:01 |                                                                                    
|  14 |    TABLE ACCESS BY INDEX ROWID       | dtl_cse                    |       |       |     6  (34)| 00:00:01 |                                                                                    
|  15 |     BITMAP CONVERSION TO ROWIDS      |                             |       |       |            |          |                                                                                    
|  16 |      BITMAP AND                      |                             |       |       |            |          |                                                                                    
|  17 |       BITMAP MERGE                   |                             |       |       |            |          |                                                                                    
|  18 |        BITMAP KEY ITERATION          |                             |       |       |            |          |                                                                                    
|  19 |         TABLE ACCESS FULL            | SYS_TEMP_0FD9D670F_6A0D08CB |     1 |    13 |     2   (0)| 00:00:01 |                                                                                    
|  20 |         BITMAP CONVERSION FROM ROWIDS|                             |       |       |            |          |                                                                                    
|* 21 |          INDEX RANGE SCAN            | PK_dtl_cse                 |       |       |     2   (0)| 00:00:01 |                                                                                    
|  22 |       BITMAP MERGE                   |                             |       |       |            |          |                                                                                    
|  23 |        BITMAP KEY ITERATION          |                             |       |       |            |          |                                                                                    
|  24 |         TABLE ACCESS FULL            | SYS_TEMP_0FD9D670E_6A0D08CB |     1 |    13 |     2   (0)| 00:00:01 |                                                                                    
|  25 |         BITMAP CONVERSION FROM ROWIDS|                             |       |       |            |          |                                                                                    
|* 26 |          INDEX RANGE SCAN            | PK_dtl_cse                 |       |       |     2   (0)| 00:00:01 |                                                                                    
|  27 |    TABLE ACCESS FULL                 | SYS_TEMP_0FD9D670E_6A0D08CB |     1 |    25 |     2   (0)| 00:00:01 |                                                                                    
--------------------------------------------------------------------------------------------------------------------                                                                                    
                                                                                                                                                                                                        
Predicate Information (identified by operation id):                                                                                                                                                     
---------------------------------------------------                                                                                                                                                     
                                                                                                                                                                                                        
   5 - access("USER_ID"=292)                                                                                                                                                                            
   7 - access("cse_id"="cse_id")                                                                                                                                                                      
   8 - access("RC"."cse_id"="cse_id")                                                                                                                                                                 
       filter("RC"."cse_id"="cse_id")                                                                                                                                                                 
  11 - access("USER_ID"=292)                                                                                                                                                                            
  12 - access("RC"."cse_id"="cse_id")                                                                                                                                                                 
  13 - access("C2"="cse_id")                                                                                                                                                                           
  21 - access("cse_id"="C0")                                                                                                                                                                           
  26 - access("cse_id"="C0")                                                                                                                                                                           
                                                                                                                                                                                                        
Note                                                                                                                                                                                                    
-----                                                                                                                                                                                                   
   - star transformation used for this statement                                                                                                                                                        


Statistics
----------------------------------------------------------                                                                                                                                              
          4  recursive calls                                                                                                                                                                            
         16  db block gets                                                                                                                                                                              
        154  consistent gets                                                                                                                                                                            
          2  physical reads                                                                                                                                                                             
       1208  redo size                                                                                                                                                                                  
        209  bytes sent via SQL*Net to client                                                                                                                                                           
        240  bytes received via SQL*Net from client                                                                                                                                                     
          2  SQL*Net roundtrips to/from client                                                                                                                                                          
          1  sorts (memory)                                                                                                                                                                             
          0  sorts (disk)                                                                                                                                                                               
          1  rows processed                                                                                                                                                                             

SQL> select /*+ordered */rc.cse_id
  2  from ak_case rc, ak_evt re,ak_cse uc,dtl_cse dc
  3  where rc.cse_id=dc.cse_id
  4  and  rc.cse_id=re.cse_id
  5  and rc.cse_id = uc.cse_id
  6  and uc.user_id=292;

22 rows selected.

Elapsed: 00:00:04.09

Execution Plan
----------------------------------------------------------                                                                                                                                              
Plan hash value: 3607569475                                                                                                                                                                             
                                                                                                                                                                                                        
----------------------------------------------------------------------------------------------------------------------------                                                                            
| Id  | Operation                            | Name                        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |                                                                            
----------------------------------------------------------------------------------------------------------------------------                                                                            
|   0 | SELECT STATEMENT                     |                             |     1 |    31 |       | 11804   (3)| 00:02:22 |                                                                            
|   1 |  TEMP TABLE TRANSFORMATION           |                             |       |       |       |            |          |                                                                            
|   2 |   LOAD AS SELECT                     | SYS_TEMP_0FD9D6710_6A0D08CB |       |       |       |            |          |                                                                            
|*  3 |    HASH JOIN                         |                             |     1 |    38 |       |  4464   (3)| 00:00:54 |                                                                            
|*  4 |     INDEX RANGE SCAN                 | PK_ak_cse                |     1 |    26 |       |     0   (0)| 00:00:01 |                                                                            
|*  5 |     HASH JOIN                        |                             |  2218K|    25M|    17M|  4443   (3)| 00:00:54 |                                                                            
|   6 |      INDEX FAST FULL SCAN            | PK_ak_case                 |  1031K|  6043K|       |   542   (3)| 00:00:07 |                                                                            
|   7 |      INDEX FAST FULL SCAN            | IDX_RE_cse_id_ISPRIMARY    |  2218K|    12M|       |  1080   (3)| 00:00:13 |                                                                            
|   8 |   LOAD AS SELECT                     | SYS_TEMP_0FD9D6710_6A0D08CB |       |       |       |            |          |                                                                            
|*  9 |    HASH JOIN                         |                             |     1 |    32 |    17M|  1436   (2)| 00:00:18 |                                                                            
|  10 |     INDEX FAST FULL SCAN             | PK_ak_case                 |  1031K|  6043K|       |   542   (3)| 00:00:07 |                                                                            
|* 11 |     INDEX RANGE SCAN                 | PK_ak_cse                |     1 |    26 |       |     0   (0)| 00:00:01 |                                                                            
|* 12 |   HASH JOIN                          |                             |     1 |    31 |       |  5904   (3)| 00:01:11 |                                                                            
|  13 |    TABLE ACCESS BY INDEX ROWID       | dtl_cse                    |       |       |       |  5902   (3)| 00:01:11 |                                                                            
|  14 |     BITMAP CONVERSION TO ROWIDS      |                             |       |       |       |            |          |                                                                            
|  15 |      BITMAP AND                      |                             |       |       |       |            |          |                                                                            
|  16 |       BITMAP MERGE                   |                             |       |       |       |            |          |                                                                            
|  17 |        BITMAP KEY ITERATION          |                             |       |       |       |            |          |                                                                            
|  18 |         TABLE ACCESS FULL            | SYS_TEMP_0FD9D6711_6A0D08CB |     1 |    13 |       |     2   (0)| 00:00:01 |                                                                            
|  19 |         BITMAP CONVERSION FROM ROWIDS|                             |       |       |       |            |          |                                                                            
|* 20 |          INDEX RANGE SCAN            | PK_dtl_cse                 |       |       |       |     2   (0)| 00:00:01 |                                                                            
|  21 |       BITMAP MERGE                   |                             |       |       |       |            |          |                                                                            
|  22 |        BITMAP KEY ITERATION          |                             |       |       |       |            |          |                                                                            
|  23 |         TABLE ACCESS FULL            | SYS_TEMP_0FD9D6710_6A0D08CB |     1 |    13 |       |     2   (0)| 00:00:01 |                                                                            
|  24 |         BITMAP CONVERSION FROM ROWIDS|                             |       |       |       |            |          |                                                                            
|* 25 |          INDEX RANGE SCAN            | PK_dtl_cse                 |       |       |       |     2   (0)| 00:00:01 |                                                                            
|  26 |    TABLE ACCESS FULL                 | SYS_TEMP_0FD9D6710_6A0D08CB |     1 |    25 |       |     2   (0)| 00:00:01 |                                                                            
----------------------------------------------------------------------------------------------------------------------------                                                                            
                                                                                                                                                                                                        
Predicate Information (identified by operation id):                                                                                                                                                     
---------------------------------------------------                                                                                                                                                     
                                                                                                                                                                                                        
   3 - access("cse_id"="cse_id" AND "RC"."cse_id"="cse_id")                                                                                                                                         
   4 - access("USER_ID"=292)                                                                                                                                                                            
   5 - access("RC"."cse_id"="cse_id")                                                                                                                                                                 
   9 - access("RC"."cse_id"="cse_id")                                                                                                                                                                 
  11 - access("USER_ID"=292)                                                                                                                                                                            
  12 - access("C2"="cse_id")                                                                                                                                                                           
  20 - access("cse_id"="C0")                                                                                                                                                                           
  25 - access("cse_id"="C0")                                                                                                                                                                           
                                                                                                                                                                                                        
Note                                                                                                                                                                                                    
-----                                                                                                                                                                                                   
   - star transformation used for this statement                                                                                                                                                        


Statistics
----------------------------------------------------------                                                                                                                                              
          4  recursive calls                                                                                                                                                                            
         16  db block gets                                                                                                                                                                              
      11327  consistent gets                                                                                                                                                                            
          2  physical reads                                                                                                                                                                             
       1252  redo size                                                                                                                                                                                  
        424  bytes sent via SQL*Net to client                                                                                                                                                           
        247  bytes received via SQL*Net from client                                                                                                                                                     
          3  SQL*Net roundtrips to/from client                                                                                                                                                          
          0  sorts (memory)                                                                                                                                                                             
          0  sorts (disk)                                                                                                                                                                               
         22  rows processed                                                                                                                                                                             



Thanks
Amit
Re: Same query different results [message #375989 is a reply to message #375984] Mon, 15 December 2008 09:56 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
It is kind of documented behavior with use of this hint.
Are the statistics updated on the concerned tables/indexes?
Re: Same query different results [message #375990 is a reply to message #375989] Mon, 15 December 2008 09:58 Go to previous messageGo to next message
amitkr
Messages: 17
Registered: November 2007
Junior Member
If I don't use the hint the result is wrong.

Using the hint is giving me correct output and yes the stats are up to date.

Amit
Re: Same query different results [message #375991 is a reply to message #375984] Mon, 15 December 2008 10:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
10.2.0.3 introduced many errors leading to wrong results.
Upgrade to 10.2.0.4.

Regards
Michel
Re: Same query different results [message #375992 is a reply to message #375991] Mon, 15 December 2008 10:02 Go to previous messageGo to next message
amitkr
Messages: 17
Registered: November 2007
Junior Member
Thanks Michael,

It will be great if you could point me to any specific bug this query might be hitting ?

Thanks
Amit
Re: Same query different results [message #375993 is a reply to message #375992] Mon, 15 December 2008 10:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As I said there are many potential ones, check Metalink for your specific cases.

Regards
Michel
Re: Same query different results [message #375998 is a reply to message #375993] Mon, 15 December 2008 10:52 Go to previous messageGo to next message
amitkr
Messages: 17
Registered: November 2007
Junior Member
The query outputs correct results when I do

alter session set "_optimizer_sortmerge_join_enabled" = false

I searched metalink but cannot find any related bug

Thanks
Amit
Re: Same query different results [message #376005 is a reply to message #375998] Mon, 15 December 2008 11:24 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
So far you have posted only the explain plan (without any hints), then with hints and now changed the optimizer parameter.
But what you have not posted is what the actual query is getting executed (by turning on 10053 trace) and also goes without saying (10046 trace).

Regards

Raj

[Updated on: Mon, 15 December 2008 11:24]

Report message to a moderator

Re: Same query different results [message #376033 is a reply to message #375984] Mon, 15 December 2008 13:43 Go to previous messageGo to next message
coleing
Messages: 213
Registered: February 2008
Senior Member
Validate the structure of your indexes:-

http://www.dba-oracle.com/art_dbazine_idx_rebuild.htm

Re: Same query different results [message #376043 is a reply to message #376033] Mon, 15 December 2008 14:37 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why do you think rebuilding indexes will solve this problem?

Regards
Michel
Previous Topic: what select send user?
Next Topic: delete column data every 30 days
Goto Forum:
  


Current Time: Sat Dec 10 01:26:39 CST 2016

Total time taken to generate the page: 0.05538 seconds