在近期进行十一数据库巡检的时候，某省的同事发现在数据库的alertlog中有关于ora-600的报错：
|
1 2 3 4 5 6 7 |
Sun Sep 21 19:20:52 2008 Errors in file /oracle/app/oracle/admin/hbmisc/udump/hbmisc_ora_13668.trc: ORA-00600: internal error code, arguments: [20022], [2], [], [], [], [], [], [] Sun Sep 21 19:21:15 2008 Errors in file /oracle/app/oracle/admin/hbmisc/udump/hbmisc_ora_13072.trc: ORA-00600: internal error code, arguments: [20022], [2], [], [], [], [], [], [] Sun Sep 21 19:38:09 2008 |
进一步查看了trace文件，发现是一个sql有关：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
*** SESSION ID:(172.1955) 2008-09-21 19:20:52.752 *** 2008-09-21 19:20:52.752 ksedmp: internal or fatal error ORA-00600: internal error code, arguments: [20022], [2], [], [], [], [], [], [] Current SQL statement for this session: select count(*) into :b0 from (select icpcode ,icpservid ,a.subsid subsid1 ,to_char(startdate,'YYYYMMDDHH24MISS') startdatetime ,to_char(enddate,'YYYYMMDDHH24MISS') enddatetime ,tab_a.status status1 ,b.subsid subsid2 ,decode(nvl(d.status,'A'),'A',0,2) status2 ,tab_a.visitcount visitcount from tab_a ,tab_b a ,icp ,service ,tab_b b ,tab_c d where (((((((nvl(tab_a.chargemid,tab_a.mid)=a.mid and tab_a.icpid=icp.icpid) and tab_a.servid=service.servid) and tab_a.status<>'C') and (tab_a.mid=:b1 or tab_a.chargemid=:b1)) and tab_a.mid=b.mid) and tab_a.mid=d.mid(+)) and tab_a.servid=d.servid(+)) union select icpcode ,icpservid ,a.subsid subsid1 ,to_char(startdate,'YYYYMMDDHH24MISS') startdatetime ,to_char(startdate,'YYYYMMDDHH24MISS') enddatetime ,'A' status1 ,a.subsid subsid2 ,1 status2 ,tab_a_freeuse.visitcount visitcount from tab_a_freeuse ,tab_b a ,icp ,service where (((tab_a_freeuse.icpid=icp.icpid and tab_a_freeuse.servid=service.servid) and tab_a_freeuse.mid=:b1) and tab_a_freeuse.mid=a.mid)) ----- Call Stack Trace ----- calling call entry argument values in hex location type point (? means dubious value) -------------------- -------- -------------------- ---------------------------- ksedmp()+184 ? ksedst() 40000000017F5B37 ? 000000032 ? 800003FAC0006570 ? 000000000 ? …… |
为什么一个select的sql会造成600的报错？上metalink后找到了答案（Note:378117.1）：
|
1 2 3 4 |
Cause ----------------------------- When _b_tree_bitmap_plans hidden parameter is set to TRUE, the optimizer is allowed to produce bitmap plans for normal b*tree indexes even if no bitmap indexes set. |
ok，我们进一步检查_b_tree_bitmap_plans这个参数的设置，并且检查该sql的执行计划：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 |
--参数设置： SQL> l 1 select 2 x.ksppinm name, 3 y.ksppstvl value, 4 y.ksppstdf isdefault, 5 decode(bitand(y.ksppstvf,7),1,'MODIFIED',4,'SYSTEM_MOD','FALSE') ismod, 6 decode(bitand(y.ksppstvf,2),2,'TRUE','FALSE') isadj 7 from 8 sys.x$ksppi x, 9 sys.x$ksppcv y 10 where 11 x.inst_id = userenv('Instance') and 12 y.inst_id = userenv('Instance') and 13 x.indx = y.indx and 14 x.ksppinm like '%_&par%' 15 order by 16* translate(x.ksppinm, ' _', ' ') SQL> / Enter value for par: bitmap old 14: x.ksppinm like '%_&par%' new 14: x.ksppinm like '%_bitmap%' NAME VALUE ISDEFAULT ISMOD ISADJ ------------------------------ ------------------------- --------- ---------- ----- _bitmap_or_improvement_enabled TRUE TRUE FALSE FALSE _b_tree_bitmap_plans TRUE TRUE FALSE FALSE create_bitmap_area_size 8388608 TRUE FALSE FALSE --执行计划： SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------ --------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | Pstart| Pstop | --------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 400 | | | | 1 | SORT AGGREGATE | | 1 | | | | | | 2 | VIEW | | 632 | | 400 | | | | 3 | SORT UNIQUE | | 632 | 89674 | 400 | | | | 4 | UNION-ALL | | | | | | | | 5 | NESTED LOOPS | | 227 | 44719 | 365 | | | | 6 | HASH JOIN | | 40 | 7080 | 325 | | | | 7 | NESTED LOOPS | | 40 | 6360 | 318 | | | | 8 | NESTED LOOPS OUTER | | 40 | 5280 | 238 | | | | 9 | NESTED LOOPS | | 40 | 4080 | 118 | | | | 10 | PARTITION HASH ALL | | | | | 1 | 4 | | 11 | TABLE ACCESS BY LOCAL INDEX ROWID| tab_a | 40 | 3000 | 37 | 1 | 4 | | 12 | BITMAP CONVERSION TO ROWIDS | | | | | | | | 13 | BITMAP OR | | | | | | | | 14 | BITMAP CONVERSION FROM ROWIDS | | | | | | | | 15 | INDEX RANGE SCAN | IDX_tab_a_MID | | | 9 | 1 | 4 | | 16 | BITMAP CONVERSION FROM ROWIDS | | | | | | | | 17 | INDEX RANGE SCAN | IDX_tab_a_CHARGEMID | | | 9 | 1 | 4 | | 18 | PARTITION HASH ITERATOR | | | | | KEY | KEY | | 19 | TABLE ACCESS BY LOCAL INDEX ROWID| tab_b | 1 | 27 | 2 | KEY | KEY | | 20 | INDEX UNIQUE SCAN | PK_tab_b_MID | 1 | | 1 | KEY | KEY | | 21 | PARTITION HASH ALL | | | | | 1 | 4 | | 22 | TABLE ACCESS BY LOCAL INDEX ROWID | tab_c | 1 | 30 | 3 | 1 | 4 | | 23 | INDEX RANGE SCAN | PK_tab_c | 1 | | 2 | 1 | 4 | | 24 | PARTITION HASH ITERATOR | | | | | KEY | KEY | | 25 | TABLE ACCESS BY LOCAL INDEX ROWID | tab_b | 1 | 27 | 2 | KEY | KEY | | 26 | INDEX UNIQUE SCAN | PK_tab_b_MID | 1 | | 1 | KEY | KEY | | 27 | TABLE ACCESS FULL | ICP | 1022 | 18396 | 6 | | | | 28 | VIEW | SERVICE | 6 | 120 | 1 | | | | 29 | UNION-ALL PARTITION | | | | | | | | 30 | TABLE ACCESS BY INDEX ROWID | GLOBAL_SERVICE | 1 | 21 | 2 | | | | 31 | INDEX UNIQUE SCAN | PK_GLOBALSERVICE_SERVID | 1 | | 1 | | | | 32 | TABLE ACCESS BY INDEX ROWID | LOCAL_SERVICE | 1 | 20 | 2 | | | | 33 | INDEX UNIQUE SCAN | PK_LOCALSERVICE_SERVID | 1 | | 1 | | | | 34 | NESTED LOOPS | | 405 | 44955 | 6 | | | | 35 | NESTED LOOPS | | 1 | 91 | 5 | | | | 36 | NESTED LOOPS | | 1 | 73 | 4 | | | | 37 | PARTITION HASH SINGLE | | | | | KEY | KEY | | 38 | TABLE ACCESS BY LOCAL INDEX ROWID | tab_b | 1 | 27 | 3 | KEY | KEY | | 39 | INDEX UNIQUE SCAN | PK_tab_b_MID | 1 | | 2 | KEY | KEY | | 40 | TABLE ACCESS BY INDEX ROWID | tab_a_FREEUSE | 1 | 46 | 1 | | | | 41 | INDEX RANGE SCAN | PK_tab_aFREEUSE | 1 | | 1 | | | | 42 | TABLE ACCESS BY INDEX ROWID | ICP | 1 | 18 | 1 | | | | 43 | INDEX UNIQUE SCAN | PK_ICP | 1 | | | | | | 44 | VIEW | SERVICE | 493 | 9860 | 1 | | | | 45 | UNION-ALL PARTITION | | | | | | | | 46 | TABLE ACCESS BY INDEX ROWID | GLOBAL_SERVICE | 1 | 21 | 2 | | | | 47 | INDEX UNIQUE SCAN | PK_GLOBALSERVICE_SERVID | 1 | | 1 | | | | 48 | TABLE ACCESS BY INDEX ROWID | LOCAL_SERVICE | 1 | 20 | 2 | | | | 49 | INDEX UNIQUE SCAN | PK_LOCALSERVICE_SERVID | 1 | | 1 | | | --------------------------------------------------------------------------------------------------------------------------- Note: cpu costing is off, PLAN_TABLE' is old version 57 rows selected. |
根据metalink的指导，我们在数据库中确实把_b_tree_bitmap_plans 设置为true了，不过，很不幸的是，这个值的默认值就是true，也就是说我们没改过这个值。
而且我们从sql执行计划看，在执行计划的第13、14行左右，显示sql确实进行了BITMAP CONVERSION，走了bitmap索引。
如何解决这个问题呢？metalink上介绍说：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
Solution 1. for this step make sure you don't already have an object called <h$parameter>, if you do name the object below something else Create or replace view H$PARAMETER as select a.ksppinm NAME, a.ksppdesc DESCRIPTION, b.ksppstvl SESSION_VALUE, c.ksppstvl SYSTEM_VALUE from x$ksppi a, x$ksppcv b, x$ksppsv c where a.indx = b.indx and a.indx = c.indx; 2. select * from h$parameter where name like '%_tree%'; --> only 1 row should be returned and this info is needed 3. drop view H$PARAMETER; --> you can keep this view if you want but it won't be supported 4. if point (2) will show the settings TRUE for SYSTEM and SESSION level, you need to arrange downtime on the database and set in the initSID/spfile file the following parameter: _b_tree_bitmap_plans=false |
汗，竟然让人改隐含参数。
改隐含参数要重启库，这就比较麻烦了。况且，改隐含参数真的有效吗？我在session级试了一下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 |
SQL> alter session set "_b_tree_bitmap_plans"=false; Session altered. Elapsed: 00:00:00.00 SQL> SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost | Pstart| Pstop | ------------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | | 159K| | | | 1 | SORT AGGREGATE | | 1 | | | | | | 2 | VIEW | | 632 | | 159K| | | | 3 | SORT UNIQUE | | 632 | 89674 | 159K| | | | 4 | UNION-ALL | | | | | | | | 5 | NESTED LOOPS | | 227 | 44719 | 159K| | | | 6 | HASH JOIN | | 40 | 7080 | 159K| | | | 7 | NESTED LOOPS OUTER | | 40 | 6360 | 159K| | | | 8 | NESTED LOOPS | | 40 | 5160 | 159K| | | | 9 | NESTED LOOPS | | 40 | 4080 | 159K| | | | 10 | PARTITION HASH ALL | | | | | 1 | 4 | | 11 | TABLE ACCESS FULL | TAB_A | 40 | 3000 | 159K| 1 | 4 | | 12 | PARTITION HASH ITERATOR | | | | | KEY | KEY | | 13 | TABLE ACCESS BY LOCAL INDEX ROWID| TAB_B | 1 | 27 | 2 | KEY | KEY | | 14 | INDEX UNIQUE SCAN | PK_TAB_B_MID | 1 | | 1 | KEY | KEY | | 15 | PARTITION HASH ITERATOR | | | | | KEY | KEY | | 16 | TABLE ACCESS BY LOCAL INDEX ROWID | TAB_B | 1 | 27 | 2 | KEY | KEY | | 17 | INDEX UNIQUE SCAN | PK_TAB_B_MID | 1 | | 1 | KEY | KEY | | 18 | PARTITION HASH ALL | | | | | 1 | 4 | | 19 | TABLE ACCESS BY LOCAL INDEX ROWID | TAB_C | 1 | 30 | 3 | 1 | 4 | | 20 | INDEX RANGE SCAN | PK_TAB_C | 1 | | 2 | 1 | 4 | | 21 | TABLE ACCESS FULL | ICP | 1022 | 18396 | 6 | | | | 22 | VIEW | SERVICE | 6 | 120 | 1 | | | | 23 | UNION-ALL PARTITION | | | | | | | | 24 | TABLE ACCESS BY INDEX ROWID | GLOBAL_SERVICE | 1 | 21 | 2 | | | | 25 | INDEX UNIQUE SCAN | PK_GLOBALSERVICE_SERVID | 1 | | 1 | | | | 26 | TABLE ACCESS BY INDEX ROWID | LOCAL_SERVICE | 1 | 20 | 2 | | | | 27 | INDEX UNIQUE SCAN | PK_LOCALSERVICE_SERVID | 1 | | 1 | | | | 28 | NESTED LOOPS | | 405 | 44955 | 6 | | | | 29 | NESTED LOOPS | | 1 | 91 | 5 | | | | 30 | NESTED LOOPS | | 1 | 73 | 4 | | | | 31 | TABLE ACCESS BY LOCAL INDEX ROWID | TAB_B | 1 | 27 | 3 | 4 | 4 | | 32 | INDEX UNIQUE SCAN | PK_TAB_B_MID | 1 | | 2 | 4 | 4 | | 33 | TABLE ACCESS BY INDEX ROWID | TAB_A_FREEUSE | 1 | 46 | 1 | | | | 34 | INDEX RANGE SCAN | PK_TAB_AFREEUSE | 1 | | 1 | | | | 35 | TABLE ACCESS BY INDEX ROWID | ICP | 1 | 18 | 1 | | | | 36 | INDEX UNIQUE SCAN | PK_ICP | 1 | | | | | | 37 | VIEW | SERVICE | 493 | 9860 | 1 | | | | 38 | UNION-ALL PARTITION | | | | | | | | 39 | TABLE ACCESS BY INDEX ROWID | GLOBAL_SERVICE | 1 | 21 | 2 | | | | 40 | INDEX UNIQUE SCAN | PK_GLOBALSERVICE_SERVID | 1 | | 1 | | | | 41 | TABLE ACCESS BY INDEX ROWID | LOCAL_SERVICE | 1 | 20 | 2 | | | | 42 | INDEX UNIQUE SCAN | PK_LOCALSERVICE_SERVID | 1 | | 1 | | | ------------------------------------------------------------------------------------------------------------------------ Note: cpu costing is off, PLAN_TABLE' is old version 50 rows selected. Elapsed: 00:00:00.86 |
在这边我们确实看到了执行计划发生了改变，但是很惨的是，在执行计划的第11行，tab_a变成了全表扫描，这个是个千万级数据的大表，这个sql原来执行只需2秒，而现在却变成了半小时还没出结果。
因此，对于这个问题，我们只能暂时的先放一下了，不能修改隐含参数；即使修改了隐含参数，sql的执行效果变的很差！！修改代码又需要开发修改核心的应用，也是不太现实。不幸中的万幸是，600的报错只是出现一次。在这里，我们暂时先放过这个问题。只能当作oracle临时出现的bug来处理。