ORACLE小小说之武朝迷案.doc

ORACLE小小说之武朝迷案 梁敬彬1、武朝迷案 最近网管组遇到了一个困扰很长时间的棘手问题，就是如下执行频率极高的语句（用于权限管理），在生产环境中运行比较缓慢，执行需要200秒以上，急需优化，最好是控制在10秒以内。（测试环境服务器也是如此状况） 据同事另外反映两点疑惑：1、生产环境及测试服务器环境运行时快时慢 2、模拟的本机笔记本环境居然跑的比生产快几十倍！Select * From (Select Rownum Sid, a.* From (Select a.Ne_Alarm_List_Id Id, a.Flow_Id, /*流程ID*/ a.Oprt_State Oprtstate, /*操作状态*/ a.Alarm_State Alarmstate, /*告警状态*/ a.Alarm_Level Alarmlevel, /*告警级别*/ a.Perf_Msg_Id, /*性能消息ID*/ a.Alarm_Class Alarmclass, /*告警类别*/ To_Char(a.Generate_Time, yyyy-mm-dd) Generatedate, /*产生日期*/ To_Char(a.Last_Generate_Time, yyyy-mm-dd) Lastdate, /*最后产生日期*/ B1.List_Label Alarm_Type, C1.Ne_Name, d.Kpi_Name, a.Kpi_Value, Decode(C1.Ne_Flag, 6, e.Region_Name, C2.Ne_Name) Datasource, | Decode(a.Alarm_Level, 1, 严重, 2, 重要, 3, 一般, 未知) | Alarm_Level, To_Char(a.Generate_Time, yyyy-mm-dd hh24:mi:ss) Generate_Time, To_Char(a.Last_Generate_Time, yyyy-mm-dd hh24:mi:ss) Last_Generate_Time, Pkp_Flow_Function.Getflowcurstaffname(a.Flow_Id) Flow_Operator, a.Alarm_Times, Decode(a.Oprt_State, 20, To_Char(a.Confirm_Time, yyyy-mm-dd hh24:mi:ss), 25, To_Char(a.Suspend_Time, yyyy-mm-dd hh24:mi:ss), 30, To_Char(a.Clear_Time, yyyy-mm-dd hh24:mi:ss), 40, To_Char(a.Delete_Time, yyyy-mm-dd hh24:mi:ss) Executetim, B2.List_Label Alarm_State, Decode(a.Flow_Id, , 未派单, 已派单) Work_State /*自定义列项*/ From Ne_Alarm_List a, (Select * From Tp_Domain_Listvalues Where Domain_Code = DOMAIN_NE_ALARM_TYPE) B1, (Select * From Tp_Domain_Listvalues Where Domain_Code = DOMAIN_ALARM_STATE) B2, (Select * From Tp_Domain_Listvalues Where Domain_Code = DOMAIN_DR_ID_FLAG) B3, (Select * From Tp_Domain_Listvalues Where Domain_Code = DOMAIN_ALARM_OPRT_STATE) B4, Net_Element C1, Net_Element C2, Kpi_Code_List d, Manage_Region e, Kpi_Mapping_Cfg f, Ne_Trans_Alarm Nta, (Select t.Primary_Id $PRIMARY_ID, Sum(t.Has_Read) $HAS_READ From Tree_Privilege t Where t.Tree_Cfg_Name = NET_ELEMENT And t.Assign_Object In (STAFF_3, ORG_2, STATION_22) Group By t.Primary_Id) $PRI_VIEW Where Nvl(a.Config_Ne_Id, a.Ne_Id) =$PRI_VIEW.$PRIMARY_ID(+) And Nvl($PRI_VIEW.$HAS_READ, 0) 0 And B1.List_Value = a.Alarm_Type And a.Ne_Id = C1.Ne_Id And B2.List_Value = a.Alarm_State And B3.List_Value = a.Dr_Id | And B4.List_Value = a.Oprt_State And a.Config_Ne_Id = C2.Ne_Id(+) And a.Kpi_Id = d.Kpi_Id And a.Kpi_Id = f.Kpi_Id(+) And Nvl(a.Alarm_Region_Origin, -1) = To_Char(e.Region_Id(+) And a.Ne_Alarm_List_Id = Nta.Ne_Alarm_List_Id(+) And (Select Path | / From Net_Element Where Ne_Id = a.Ne_Id) Not Like (Select Path | /% From Net_Element Where Ne_Type_Id = 30 And Ne_Flag = 6 And State = 0SA) /*非业务系统*/ And To_Number(a.Dr_Id) = 0 Order By B2.Sort_Id, B4.Sort_Id, a.Alarm_Type, a.Ne_Id, Nvl(a.Last_Send_Time, a.Create_Time) Desc) a) b Where b.Sid = 1 And b.Sid 和 ) 一分析，发现执行计划是有少许差异，但是逻辑读却有天壤之别，笔记本上788910 consistent gets 而服务器上是37494576 consistent gets 。递归调用对比更是离谱 ：笔记本为 0 recursive calls而服务器为1305552 recursive calls 。 究竟是什么原因导致如此大的差异呢？执行计划虽有差异，但是却并不感觉特别离谱，限入沉思中。3、无所适从3.1统计信息不全？ 查询后发现系统确有收集统计信息，心中略有不甘，为防止收集不准确，继续做全收集如下（无分区表，所以不用 dbms_stats包了） analyze table Ne_Alarm_List compute statistics for table for all indexes for all indexed columns; analyze table Tp_Domain_Listvalues compute statistics for table for all indexes for all indexed columns; analyze table Net_Element compute statistics for table for all indexes for all indexed columns; analyze table Kpi_Code_List compute statistics for table for all indexes for all indexed columns; analyze table Manage_Region compute statistics for table for all indexes for all indexed columns; analyze table Kpi_Mapping_Cfg compute statistics for table for all indexes for all indexed columns; analyze table Ne_Trans_Alarm compute statistics for table for all indexes for all indexed columns; analyze table Tree_Privilege compute statistics for table for all indexes for all indexed columns; 收集完毕，发现执行依旧缓慢，执行计划有变，但是罗极读和递归依旧大到惊人！3.2 物理分布OR碎片？ 于是检查如下： UNIX服务器上表和索引的情况 select table_name,num_rows,blocks from user_tables where lower(table_name) in (ne_alarm_list ,tp_domain_listvalues ,net_element ,kpi_code_list ,manage_region ,kpi_mapping_cfg ,ne_trans_alarm ,tree_privilege) order by table_name;TABLE_NAME NUM_ROWS BLOCKS- - -KPI_CODE_LIST 64018 1378KPI_MAPPING_CFG 4063 20MANAGE_REGION 237 5NET_ELEMENT 103485 1882NE_ALARM_LIST 138278 15197NE_TRANS_ALARM 0 5TP_DOMAIN_LISTVALUES 1121 13TREE_PRIVILEGE 2443 4780 select table_name,index_name,blevel,leaf_blocks from user_indexes where lower(table_name) in (ne_alarm_list ,tp_domain_listvalues ,net_element ,kpi_code_list ,manage_region ,kpi_mapping_cfg ,ne_trans_alarm ,tree_privilege) order by table_name;TABLE_NAME INDEX_NAME BLEVEL LEAF_BLOCKS- - - -KPI_CODE_LIST PK_KPI_CODE_LIST 1 170KPI_MAPPING_CFG PK_KPI_MAPPING_CFG 1 19MANAGE_REGION PK_MANAGE_REGION 0 1NET_ELEMENT IDX_NE_ELEM_PARENT_NE_ID 1 241NET_ELEMENT IDX_NE_ELEM_NE_NAME 2 367NET_ELEMENT IDX_STATE_PATH 2 768NET_ELEMENT PK_NET_ELEMENT 1 369NET_ELEMENT IDX_NE_ELEM_STATE 1 234NET_ELEMENT IDX_NE_PATH 2 656NET_ELEMENT IDX_NEID_NETYPEID 1 317NET_ELEMENT IDX_NE_ELEM_NET_TYPE_ID 1 237NE_ALARM_LIST PK_NE_ALARM_LIST 1 338NE_TRANS_ALARM PK_NE_TRANS_ALARM 0 0TREE_PRIVILEGE PK_TREE_PRIVILEGE 2 505914 rows selected 笔记本上表和索引的情况 select table_name,num_rows,blocks from user_tables where lower(table_name) in (ne_alarm_list ,tp_domain_listvalues ,net_element ,kpi_code_list ,manage_region ,kpi_mapping_cfg ,ne_trans_alarm ,tree_privilege) order by table_name;TABLE_NAME NUM_ROWS BLOCKS- - -KPI_CODE_LIST 64018 1378KPI_MAPPING_CFG 4063 20MANAGE_REGION 237 5NET_ELEMENT 103485 1440NE_ALARM_LIST 138278 12901NE_TRANS_ALARM 0 0TP_DOMAIN_LISTVALUES 1121 13TREE_PRIVILEGE 2443 21SQL select table_name,index_name,blevel,leaf_blocks from user_indexes where lower(table_name) in (ne_alarm_list ,tp_domain_listvalues ,net_element ,kpi_code_list ,manage_region ,kpi_mapping_cfg ,ne_trans_alarm ,tree_privilege) order by table_name;TABLE_NAME INDEX_NAME BLEVEL LEAF_BLOCKS- - - -KPI_CODE_LIST PK_KPI_CODE_LIST 1 230KPI_MAPPING_CFG PK_KPI_MAPPING_CFG 1 16MANAGE_REGION PK_MANAGE_REGION 0 1MANAGE_REGION IDX_REGION_141 0 1NET_ELEMENT PK_NET_ELEMENT 1 332NET_ELEMENT IDX_NE_ELEM_PARENT_NE_ID 1 365NE_ALARM_LIST IDX_CREATE_TIME 2 544NE_ALARM_LIST IDX_ALARM_LIST_NE_ID 1 435NE_ALARM_LIST IDX_ALARM_NE_CONFIG_ID 1 339NE_ALARM_LIST PK_NE_ALARM_LIST 1 425NE_ALARM_LIST IDX_ALARM_LIST_FLOW_ID 1 377NE_TRANS_ALARM IDX_NE_TRAN_LIST_ID 0 0NE_TRANS_ALARM PK_NE_TRANS_ALARM 0 0TREE_PRIVILEGE PK_TREE_PRIVILEGE 1 13 心头一喜，好像看出TREE_PRIVILEGE 的表和索引的统计信息明显有问题，块是4780对21，叶子的高度是2对1 ，差异还算明显啊！ 于是操刀如下，从数据字典中捞出如下两批语句，分别执行，做好了表重组和索引重建两大工作 select table_name,index_name,blevel,leaf_blocks,alter table |table_name| move;,alter index |index_name| rebuild; from user_indexes where lower(table_name) in (ne_alarm_list ,tp_domain_listvalues ,net_element ,kpi_code_list ,manage_region ,kpi_mapping_cfg ,ne_trans_alarm ,tree_privilege) order by table_name; 操作完以后刚才的4780对21和2对1的情况不见了（具体就不贴出了），变得非常接近了，我感觉问题好像已经搞定了。 欢天喜地一跑脚本，天，依旧奇慢无比，再一看autotrace 跟踪的结果，心凉了半截，执行计划又略为小变动了一下，但是巨大惊人的逻辑读和递归调用形同鬼魅，如影随形，挥之不去！ 3.3 版本，BUG? 为什么会有这么多逻辑读，为什么我的笔记本没有这么多逻辑读？我们的环境可是一摸一样啊，并且在别的同事的机器上也是跑的和我笔记本一样顺畅，百思不得其解。 会不会是版本问题，BUG? 跳起身来，进行了以下查询 服务器版本为 SQL show parameter featNAME TYPE VALUE- - -optimizer_features_enable string 10.2.0.4 我笔记本版本为 SQL show parameter featNAME TYPE VALUE- - -optimizer_features_enable string 10.2.0.1 和我笔记本一样OK的别人环境数据库版本为 SQL show parameter featNAME TYPE VALUE- - -optimizer_features_enable string 10.2.0.3眼睛一亮，看来版本问题啊，版本不一样，怪不得莫名奇妙，心跳加速，心里寻思：离谱真的源于BUG? 猜测不如动手，咋确认呢？来狠的，直接把服务器的版本降下来 SQL alter system set optimizer_features_enable=10.2.0.3; System altered. 继续执行服务器环境SQL语句，发现没效果，重启数据库，依然如故！3.4 分页调优？ 大家对分页调优写法都有了解，本案例中由于如下子查询语句和整个结果集关联，并有 And Nvl($PRI_VIEW.$HAS_READ, 0) 0的条件，导致无法将整个结果集先分页再和子查询关联，因为这样只取ROWNUM范围15行的值参与关联，结果就错了！ (Select t.Primary_Id $PRIMARY_ID, Sum(t.Has_Read) $HAS_READ From Tree_Privilege t Where t.Tree_Cfg_Name = NET_ELEMENT And t.Assign_Object In (STAFF_3, ORG_2, STATION_22) Group By t.Primary_Id) $PRI_VIEW 所以本案例中，分页调优难以凑效！ 唉，长叹一声，无所适从！4、峰回路转 为什么会有那么多次的逻辑读和递归？看来看执行计划还是太表面了，难以回答ORACLE内部在做啥小动作，不查出问题所在，不将谜团大白于天下，誓不罢休！ 试试10046trace吧，看看整个执行开始到结束，到底在执行什么，等待什么？玩啥猫腻！ 分别做了两次10046TRACE，具体见附件10046trace_notebook.txt和10046trace_service.txt 仔细观察，终于有重大发现了，笔记本中环境10046trace_notebook.txt SELECT PERSON FROM TACHE WHERE FLOW_ID = :B1 AND STATE Fcall count cpu elapsed disk query current rows- - - - - - - -Parse 1 0.00 0.00 0 0 0 0Execute 65249 2.26 1.90 0 0 0 0Fetch 65249 1.23 1.30 0 65243 0 0- - - - - - - -total 130499 3.50 3.20 0 65243 0 0 这里怎么跑出一个这样的语句，Eeecute6万多次最终获取0行 而10046trace_service.txt中类似的地方确实如下，rows显示为65331和前者为0！有所发现。SELECT PERSON FROM TACHE WHERE FLOW_ID = :B1 AND STATE Fcall count cpu elapsed disk query current rows- - - - - - - -Parse 1 0.00 0.00 0 0 0 0Execute 65339 0.78 0.75 0 0 0 0Fetch 130670 148.79 145.17 0 32714110 0 65331- - - - - - - -total 196010 149.58 145.93 0 32714110 0 65331 继续分析发现 10046trace_service.txt还有如下调用，而10046trace_notebook.txt根本找不到如下两处调用！SELECT PKP_FLOW_FUNCTION.GETSTAFFNAMEBYID(LTRIM(RTRIM(TO_CHAR(:B1 ) FROM DUALcall count cpu elapsed disk query current rows- - - - - - - -Parse 1 0.00 0.00 0 0 0 0Execute 522102 7.43 6.78 0 0 0 0Fetch 522102 42.59 42.17 0 0 0 522102- - - - - - - -total 1044205 50.03 48.96 0 0 0 522102SELECT NAME FROM STAFF_INFO WHERE STAFF_ID = :B1 call count cpu elapsed disk query current rows- - - - - - - -Parse 1 0.00 0.00 0 0 0 0Execute 522102 8.51 7.78 0 0 0 0Fetch 522102 21.35 20.21 0 4110939 0 456771- - - - - - - -total 1044205 29.86 27.99 0 4110939 0 456771仔细观察，还有新发现， 10046trace_service.txt还有如下递归调用 统计信息，而10046trace_notebook.txt根本找不到！RECURSIVE STATEMENTSOVERALL TOTALS FOR ALL RECURSIVE STATEMENTScall count cpu elapsed disk query current rows- - - - - - - -Parse 11 0.00 0.01 0 0 0 0Execute 110955