ORACLE小小说之武朝迷案

huanhuanlove · 发表于 2010-3-16 16:19

果然是牛贴不是技术发现楼主的文笔很好哈哈。。。。。。。

rollingpig · 发表于 2010-3-16 16:22

SELECT PERSON
FROM
TACHE WHERE FLOW_ID = :B1 AND STATE <> 'F'
这个SQL可能没有适当的index.
每次执行返回一行，但是每次执行500 logical read.

wabjtam123 · 发表于 2010-3-16 16:24

原帖由 rollingpig 于 2010-3-16 16:11 发表
最后的SQL应该是这样子
Select b.*,Pkp_Flow_Function.Getflowcurstaffname(b.Flow_Id) Flow_Operator
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

ROLLINGPIG大师，我咋没把这个附件发上去啊，呵呵，现在我补上去《终极解决.txt》了，是这么写的，我把ROWNUM分页象征性的写了一下，虽然没实际用途！

[ 本帖最后由 wabjtam123 于 2010-3-16 16:27 编辑 ]

wabjtam123 · 发表于 2010-3-16 16:33

原帖由 rollingpig 于 2010-3-16 16:22 发表
SELECT PERSON
FROM
TACHE WHERE FLOW_ID = :B1 AND STATE 'F'
这个SQL可能没有适当的index.
每次执行返回一行，但是每次执行500 logical read.

ROLLINGPIG大师，确实如此！
只有一个主键，就是flow_id，没用到索引，估计是认为有回表，代价会比较大？

如果建如下索引，一定最快，因为索引可以回答问题
create index idx_union on tache (flow_id,person,state)

这样建索引后，尝试一跑，代价从100降低为2了，假如投入实战收效估计相当可观，就是不了解业务对这张表的更新操作到什么程度，讨论一下再考虑是否真如此修改投放补丁。
谢谢！

[ 本帖最后由 wabjtam123 于 2010-3-16 16:37 编辑 ]