oracle10g中非标准sql的groupby问题

kylefree

遇到一个奇怪问题，在aix5309+oracle10.2.0.5的环境中，执行select * from RECUPERATE_FEE r,SHOPACCOUNT s where r.shopmultiappid = s.shopmultiappid  group by s.shopno,s.accountname,s.multiappid报错ORA-00979，但执行select count(*) from (select * from RECUPERATE_FEE r,SHOPACCOUNT s where r.shopmultiappid = s.shopmultiappid  group by s.shopno,s.accountname,s.multiappid);却没报错。
在aix6.1+oracle11.0.2.0环境中，同样的sql语句，均报了ORA-00979的错误。执行计划、统计分析、表字段信息、表条目数均在附件txt中，求助大神解惑=。=！
难道是上周五快钱运维总监张晨光讲的那种视图bug。。。

kylefree

SQL> select count(*) from RECUPERATE_FEE r,SHOPACCOUNT s where r.shopmultiappid = s.shopmultiappid  group by s.shopno,s.accountname,s.multiappid;

no rows selected

SQL>
SQL> explain plan for
  2  select count(*) from RECUPERATE_FEE r,SHOPACCOUNT s where r.shopmultiappid = s.shopmultiappid  group by s.shopno,s.accountname,s.multiappid;

Explained.

SQL> set linesize 300
SQL> select * from table(DBMS_XPLAN.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1831850831

------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                 |     1 |    96 |     5  (20)| 00:00:01 |
|   1 |  HASH GROUP BY               |                 |     1 |    96 |     5  (20)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| SHOPACCOUNT     |     1 |    78 |     2   (0)| 00:00:01 |
|   3 |    NESTED LOOPS              |                 |     1 |    96 |     4   (0)| 00:00:01 |
|   4 |     TABLE ACCESS FULL        | RECUPERATE_FEE  |     1 |    18 |     2   (0)| 00:00:01 |
|*  5 |     INDEX RANGE SCAN         | UK1_SHOPACCOUNT |     1 |       |     2   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("R"."SHOPMULTIAPPID"="S"."SHOPMULTIAPPID")

17 rows selected.

kylefree

在11.2.0.2中，执行下述语句情况：
SQL> show parameter opt

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
filesystemio_options                 string      asynch
object_cache_optimal_size            integer     102400
optimizer_capture_sql_plan_baselines boolean     FALSE
optimizer_dynamic_sampling           integer     2
optimizer_features_enable            string      11.2.0.2
optimizer_index_caching              integer     0
optimizer_index_cost_adj             integer     100
optimizer_mode                       string      ALL_ROWS
optimizer_secure_view_merging        boolean     TRUE
optimizer_use_invisible_indexes      boolean     FALSE
optimizer_use_pending_statistics     boolean     FALSE

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_use_sql_plan_baselines     boolean     TRUE
plsql_optimize_level                 integer     2

SQL> alter session set optimizer_features_enable = '10.2.0.5';

Session altered.

SQL>
SQL>
SQL> explain plan for
  2  select count(*) from RECUPERATE_FEE r,SHOPACCOUNT s where r.shopmultiappid = s.shopmultiappid  group by s.shopno,s.accountname,s.multiappid;

Explained.

SQL> select * from table(DBMS_XPLAN.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1831850831

------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                 |     1 |   111 |     7  (15)| 00:00:01 |
|   1 |  HASH GROUP BY               |                 |     1 |   111 |     7  (15)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| SHOPACCOUNT     |     1 |    78 |     2   (0)| 00:00:01 |
|   3 |    NESTED LOOPS              |                 |     1 |   111 |     6   (0)| 00:00:01 |
|   4 |     TABLE ACCESS FULL        | RECUPERATE_FEE  |     1 |    33 |     4   (0)| 00:00:01 |
|*  5 |     INDEX RANGE SCAN         | UK1_SHOPACCOUNT |     1 |       |     1   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("R"."SHOPMULTIAPPID"="S"."SHOPMULTIAPPID")

17 rows selected.

SQL> explain plan for
  2  select count(*) from (select * from RECUPERATE_FEE r,SHOPACCOUNT s where r.shopmultiappid = s.shopmultiappid  group by s.shopno,s.accountname,s.multiappid);
select count(*) from (select * from RECUPERATE_FEE r,SHOPACCOUNT s where r.shopmultiappid = s.shopmultiappid  group by s.shopno,s.accountname,s.multiappid)
                             *
ERROR at line 2:
ORA-00979: not a GROUP BY expression

Maclean Liu(刘相兵

ORA-00979: not a GROUP BY expression 相关的bug有很多


请提供一个11.2下 触发该问题的errorstack，做法


oradebug setmypid;
oradebug unlimit;

oradebug event 979 trace name errorstack level 3;

RUN YOUR STATEMENT WITH 979 error in 11.2

oradebug tracefile_name


上传生成的TRACE文件

anbob

两表是否含同名column?

kylefree

两张表在10g和11g中的字段是一致的，刚对比过，也是同构dmp导出导入的。唯一不同的是11g中RECUPERATE_FEE表记录数是1，目前10g中RECUPERATE_FEE表记录数是0.
11.2中运行sql的errorstack已上传附件

Maclean Liu(刘相兵

11.2.0.2.0+ AIX

1. ----- Error Stack Dump -----
   
2. ORA-00979: not a GROUP BY expression
   
3. ----- Current SQL Statement for this session (sql_id=b72jp8zhnyad2) -----
   
4. select count(*) from (select * from settle.RECUPERATE_FEE r,settle.SHOPACCOUNT s where r.shopmultiappid = s.shopmultiappid  group by s.shopno,s.accountname,s.multiappid)
   
5. 
   
6. ----- Call Stack Trace -----
   
7. calling              call     entry                argument values in hex      
   
8. location             type     point                (? means dubious value)     
   
9. -------------------- -------- -------------------- ----------------------------
   
10. skdstdst()+40        bl       107c8d9c0            109FD6B98 ? 109FD6B98 ?
    
11.                                                    000000001 ? 000000003 ?
    
12.                                                    000000000 ? 000002004 ?
    
13.                                                    000000001 ? 000000000 ?
    
14. ksedst1()+104        call     skdstdst()           1106C80E0 ? 109FD7674 ?
    
15.                                                    109FD6BC8 ? FFFFFFFFFFEB700 ?
    
16.                                                    FFFFFFFFFFEB4E0 ?
    
17.                                                    FFFFFFFFFFEB700 ? 1001D1724 ?
    
18.                                                    700000007 ?
    
19. ksedst()+40          call     ksedst1()            109FD7668 ? 7000000000282 ?
    
20.                                                    109FD763C ? B000000000000 ?
    
21.                                                    109FD6BC8 ? 000000000 ?
    
22.                                                    100000000 ? 1689C8CBC4FC2 ?
    
23. dbkedDefDump()+2828  call     ksedst()             110106E88 ? 0000000AC ?
    
24.                                                    110106E88 ? 000000000 ?
    
25.                                                    000000002 ? 1106D39A0 ?
    
26.                                                    000000000 ? 000000000 ?
    
27. ksedmp()+76          call     dbkedDefDump()       300000000 ? 000000000 ?
    
28.                                                    10A7CE538 ? 1109F38C0 ?
    
29.                                                    10A7CE538 ? 1106C80E0 ?
    
30.                                                    000000000 ? FFFFFFFFFFEBB20 ?
    
31. dbkdaKsdActDriver()  call     ksedmp()             7000005783FD308 ?
    
32. +2104                                              700000602845808 ?
    
33.                                                    7000006028457B8 ?
    
34.                                                    7000005B29DBD88 ? 0000003E8 ?
    
35.                                                    000000000 ? 110936A80 ?
    
36.                                                    000000001 ?
    
37. dbgdaExecuteAction(  call     dbkdaKsdActDriver()  1106C80E0 ? 000000002 ?
    
38. )+976                                              110978DE0 ? 000000000 ?
    
39.                                                    110106E88 ? 000000002 ?
    
40.                                                    7000005DAEEC930 ? 000000015 ?
    
41. dbgdaRunAction()+13  call     dbgdaExecuteAction(  1106C80E0 ? 10A7CD540 ?
    
42. 00                            )                    110106E88 ? FFFFFFFFFFECF28 ?
    
43.                                                    1FFFF1E40 ?
    
44.                                                    2422204800000015 ?
    
45.                                                    10142AF40 ? 0000000AC ?
    
46. dbgdRunActions()+84  call     dbgdaRunAction()     FFFFFFFFFFEE170 ? 000000002 ?
    
47.                                                    FFFFFFFFFFEE190 ? 000000000 ?
    
48.                                                    000000000 ? 000000001 ?
    
49.                                                    000000002 ?
    
50.                                                    FFFFFFFFFFFFFFFC ?
    
51. dbgdProcessEventAct  call     dbgdRunActions()     000000002 ? 0000000AC ?
    
52. ions()+568                                         000000000 ? 110975B98 ?
    
53.                                                    1109749A8 ? 110975B98 ?
    
54.                                                    1109327C8 ? 110932868 ?

复制代码

Maclean Liu(刘相兵

ORA-00979+ dbkdaKsdActDriver + subquery =>

ODM FINDING:
Hdr: 13503320 11.2.0.3 RDBMS 11.2.0.3 VIRTUAL_COLUMN PRODID-5 PORTID-226 10277722
Abstract: ORA-979 WITH SCALAR SUBQUERY

*** 12/14/11 03:53 pm ***
----

PROBLEM:
--------
Customer has a query that fails on ORA-979 that used to work ok in 10.2.0.4
SQL is

select project_id,
             respondent_date,
             - (trunc(sysdate) - trunc(respondent_date)) lag_days,
             p_count,
             e_count
        from (select m.project_id,
                     trunc(m.respondent_date) respondent_date,
                     (select count(*)
                        from respondent_cases rc
                       where rc.eldorado_project_id = m.project_id
                         and trunc(rc.respondent_date) =
                             trunc(m.respondent_date)
                         and trunc(rc.respondent_date) > trunc(sysdate) - 7)
p_count,
                     count(m.respondent_id) e_count
                from motherlode m, p_admin.projects pr
               where m.project_id = pr.project_id
                 and pr.status = 'ACTIVE'
                    --remove
                 and trunc(m.respondent_date) > sysdate - 7
               group by m.project_id, trunc(m.respondent_date)) a
       where p_count < e_count;

This is the same identical SQL fixed in bug 7439134

DIAGNOSTIC ANALYSIS:
--------------------
SQL used to work fine in 10.2.0.3 and returned ORA-979 in 10.2.0.4 so bug
7439134 was created to fix the issue on this SQL

Customer has patch 7439134 applied in 10.2.0.4 (fix in included in 11.2) but
the SQL now fails again in 11.2.0.3
The SQL fails for all OFE > 10.2.0.5

From the stack trace the SQL undergoes GBP but preventing it does not solve
the issue as well as preventing CBQT

WORKAROUND:
-----------
OFE=10.2.0.5, not acceptable for the customer

RELATED BUGS:
-------------
7439134

REPRODUCIBILITY:
----------------
11.2.0.3

TEST CASE:
----------
Uploaded, unzip TC.zip and run setup.sql
The TC returns few errors on the locked stats on the stattab, safe to ignore

STACK TRACE:
------------
     skdstdst <- ksedst1 <- ksedst <- dbkedDefDump <- ksedmp
        <- dbkdaKsdActDriver <- dbgdaExecuteAction <- dbgdaRunAction <-
dbgdRunActions <- dbgdProcessEventAct
         <- ions <- dbgdChkEventKgErr <- dbkdChkEventRdbmsEr <- ksfpec <-
dbgePostErrorKGE
          <- 1176 <- dbkePostKGE_kgsf <- kgeade <- kgeselv <- kgesecl0
           <- qcuErroer <- qcuErroep <- erroep <- qecgoc <- qecsel
            <- qecpqbcheck <- qecdrv <- kkqcttcalo <- kkqctdrvGBP <-
kkqgbpTravChkTran
             <- qksqbApplyToQbcLoc <- qksqbApplyToQbcLoc <- qksqbApplyToQbc
<- kkqctdrvTD <- kkqgbpdrv
              <- kkqdrv <- kkqctdrvIT <- apadrv <- opitca <- kksFullTypeCheck
               <- rpiswu2 <- kksLoadChild <- kxsGetRuntimeLock <- kksfbc <-
kkspsc0
                <- kksParseCursor <- opiosq0 <- kpooprx <- kpoal8 <- opiodr
                 <- ttcpip <- opitsk <- opiino <- opiodr <- opidrv
                  <- sou2o <- opimai_real <- ssthrdmain <- main <- start





尝试设置以下参数

alter session set "_complex_view_merging"=FALSE;
alter session set "_replace_virtual_columns"=false;

并在11.2中重试以上语句

kylefree

设置了这两个参数后，还是报错，操作步骤和trc已上传附件
我有一点不明白，如果单拿select count(*) from 后的语句select * from settle.RECUPERATE_FEE r,settle.SHOPACCOUNT s where r.shopmultiappid = s.shopmultiappid  group by s.shopno,s.accountname,s.multiappid来执行，10.2.0.5和11.2.0.2都是通不过报979错误，这个是由于不符合sql标准，select字段没全在groupby后列出，报错是正常预期的。
但是加上select count(*) from 后，10.2.0.5里面是能通过语法验证并返回结果，11.2.0.2报979错误，是否这两个版本对这句语句的优化处理逻辑已经不一样了，应该是以10g里面的执行情况作为正确的情况的还是11g里面的执行情况是正确的呢。

wwfbxs

是不是可以改变写法来实现这个功能？

kylefree

最佳的是应该改变这种写法。但是因为是一次硬件替换升级，数据库版本升级，原库里有这些sql，数量不明。所以要改变这种写法需要一条条找到，这个是正在做，担心的到了迁移那天sql找不全，有遗漏，这样业务操作就会出现错误，所以保险起见，想看看有无办法在11g里实现和10g里同样的情况，这样一旦出现错误也有临时快速的弥补措施。

wwfbxs

在版本升级后，应该安排详细测试，要不然到时候压力太大。
alter system set "_complex_view_merging"=false ;
修改后重启下数据库看看呢？
看看这个参数还行，我记得以前好像10.2.0.4有bug的。

[ 本帖最后由 wwfbxs 于 2012-7-25 13:05 编辑 ]

kylefree

这个参数修改了试过。但是不行。郁闷呐