发布时间 : 星期四 文章诺西LTE指标提取SQL语句20160801更新完毕开始阅读
System Handover attempts to GERAN with SRVCC (Single Radio Voice Call Continuity, 3GPP TS 23.216).
,sum(nvl(ISYS_HO_GERAN_SRVCC_SUCC,0)) M8016C34 --This measurement provides the number of successful Inter System Handover completions to GERAN with SRVCC (Single Radio Voice Call Continuity, 3GPP TS 23.216).
,sum(nvl(ISYS_HO_GERAN_SRVCC_FAIL,0)) M8016C35 --This measurement provides the number of failed Inter System Handover attempts to GERAN with SRVCC (Single Radio Voice Call Continuity, 3GPP TS 23.216). from
NOKLTE_PS_LISHO_lncel_hour PMRAW where
period_start_time between to_date(&1,'yyyymmddHH24') and to_date(&2,'yyyymmddHH24') --- to_char(period_start_time,'yyyymmdd') >= to_char(SYSDATE-1,'yyyymmdd') -- and to_char(period_start_time,'yyyymmdd') <= to_char(SYSDATE-1,'yyyymmdd') --AND PERIOD_DURATION=15 group by
to_char(period_start_time,'yyyymmdd'),LNCEL_ID,to_char(period_start_time,'yyyymmdd')||LNCEL_ID )M8016,( select
to_char(period_start_time,'yyyymmdd') sdatetime -- ,MRBTS_ID ,LNBTS_ID ,LNCEL_ID
,to_char(period_start_time,'yyyymmdd')||LNCEL_ID cel_key_id ,sum(nvl(CHNG_TO_CELL_AVAIL,0)) M8020C0 --Number of cell state changes to cell is available ,sum(nvl(CHNG_TO_CELL_PLAN_UNAVAIL,0)) M8020C1 --Number of cell state changes to cell is planned unavailable
,sum(nvl(CHNG_TO_CELL_UNPLAN_UNAVAIL,0)) M8020C2 --Number of cell state changes to cell is unplanned unavailable
,sum(nvl(SAMPLES_CELL_AVAIL,0)) M8020C3 --The number of samples when the cell is available ,sum(nvl(SAMPLES_CELL_PLAN_UNAVAIL,0)) M8020C4 --The number of samples when the cell is planned unavailable ,sum(nvl(SAMPLES_CELL_UNPLAN_UNAVAIL,0)) M8020C5 --The number of samples when the cell is unplanned unavailable ,sum(nvl(DENOM_CELL_AVAIL,0)) M8020C6 --The number of samples when cell availability is checked. This counter is used as a denominator for the cell availability calculation from
NOKLTE_PS_LCELAV_LNCEL_HOUR PMRAW where
period_start_time between to_date(&1,'yyyymmddHH24') and to_date(&2,'yyyymmddHH24') --AND PERIOD_DURATION=15 group by
to_char(period_start_time,'yyyymmdd'),MRBTS_ID,LNBTS_ID,LNCEL_ID,to_char(period_start_time,'yyyymmdd')||LNCEL_ID )M8020
16
WHERE M8013.cel_key_id=m8001.cel_key_id(+) AND M8013.cel_key_id=m8006.cel_key_id(+) AND M8013.cel_key_id=m8008.cel_key_id(+) AND M8013.cel_key_id=m8009.cel_key_id(+) AND M8013.cel_key_id=m8011.cel_key_id(+) AND M8013.cel_key_id=m8012.cel_key_id(+) AND M8013.cel_key_id=m8014.cel_key_id(+) AND M8013.cel_key_id=m8015.cel_key_id(+) AND M8013.cel_key_id=m8016.cel_key_id(+) -- AND M8013.cel_key_id=m8018.cel_key_id(+) AND M8013.cel_key_id=m8020.cel_key_id(+) )
--WHERE enb_id between 618935 and 618935
GROUP BY enb_cell,enb_id,cell_id,bts_ip,bts_version,bts_name,cel_name,sdate ORDER BY enb_cell,sdate;
2. --ERB失败查询
SELECT
sdate,enb_id,'460-00-' ||enb_id|| '-' || cell_id CGI ,sum(M8006C1) ERAB建立成功数,sum(M8006C0) ERAB建立请求数,sum(M8006C0-M8006C1) ERAB_FAIL FROM (select
to_char(period_start_time,'yyyymmddhh24') sdate ,lnbts.co_object_instance enb_id ,lncel.co_object_instance cell_id ,LNCEL_ID ,to_char(period_start_time,'yyyymmddhh24')||LNCEL_ID cel_key_id ,sum(nvl(EPS_BEARER_SETUP_COMPLETIONS,0)) M8006C1 ,sum(nvl(EPS_BEARER_SETUP_ATTEMPTS,0)) M8006C0 from NOKLTE_PS_LEPSB_lncel_hour PMRAW,utp_common_objects lnbts,utp_common_objects lncel where period_start_time between to_date(&1,'yyyymmddHH24') and to_date(&2,'yyyymmddHH24') AND PMRAW.LNCEL_ID=lncel.co_gid and PMRAW.lnbts_id=lnbts.co_gid group by to_char(period_start_time,'yyyymmddhh24'),MRBTS_ID,LNBTS_ID,LNCEL_ID,to_char(period_start_time,'yyyymmddhh24')||LNCEL_ID ,lnbts.co_object_instance,lncel.co_object_instance,lnbts.co_main_host,lnbts.co_ocv_sys_version,Trim(lnbts.co_name),Trim(lncel.co_name) ) M8006 GROUP BY sdate,enb_id,cell_id order by ERAB_FAIL DESC
3. --RRC失败查询
SELECT
17
sdate,enb_id,'460-00-' ||enb_id|| '-' || cell_id CGI ,sum(M8013C5) RRC连接建立成功次数,sum(M8013C17+M8013C18+M8013C19+M8013C20+ M8013C21) RRC连接建立请求次数,sum((M8013C17+M8013C18+M8013C19+M8013C20+ M8013C21)-M8013C5) RRC_CON_FAIL FROM (select
to_char(period_start_time,'yyyymmddhh24') sdate ,lnbts.co_object_instance enb_id ,lncel.co_object_instance cell_id ,LNCEL_ID
,to_char(period_start_time,'yyyymmddhh24')||LNCEL_ID cel_key_id ,sum(nvl(SIGN_CONN_ESTAB_COMP,0)) M8013C5 ,sum(nvl(SIGN_CONN_ESTAB_ATT_MO_S,0)) M8013C17 ,sum(nvl(SIGN_CONN_ESTAB_ATT_MT,0)) M8013C18 ,sum(nvl(SIGN_CONN_ESTAB_ATT_MO_D,0)) M8013C19 ,sum(nvl(SIGN_CONN_ESTAB_ATT_OTHERS,0)) M8013C20 ,sum(nvl(SIGN_CONN_ESTAB_ATT_EMG,0)) M8013C21 from NOKLTE_PS_LUEST_lncel_hour PMRAW,utp_common_objects lnbts,utp_common_objects lncel where period_start_time between to_date(&1,'yyyymmddHH24') and to_date(&2,'yyyymmddHH24') AND PMRAW.LNCEL_ID=lncel.co_gid and PMRAW.lnbts_id=lnbts.co_gid group by to_char(period_start_time,'yyyymmddhh24'),MRBTS_ID,LNBTS_ID,LNCEL_ID,to_char(period_start_time,'yyyymmddhh24')||LNCEL_ID ,lnbts.co_object_instance,lncel.co_object_instance,lnbts.co_main_host,lnbts.co_ocv_sys_version,Trim(lnbts.co_name),Trim(lncel.co_name) ) M8013 GROUP BY sdate,enb_id,cell_id order by RRC_CON_FAIL DESC
4. –VOLTE指标查询 SELECT enb_cell ,sdatetime ,enb_id ,cell_id ,bts_ip
,bts_version ,bts_name ,cel_name
,avg(EU0427) as \下行平均时延_QCI2\
,round(decode(SUM(V8006C3+V8006C4),0,0,SUM(V8006C1+V8006C2)/SUM(V8006C3+V8006C4)),4) as \建立成功率QCI1\
,round(decode(SUM(V8006C7+V8006C8),0,0,SUM(V8006C5+V8006C6)/SUM(V8006C7+V8006C8)),4) as \建立成功率QCI2\
,round(decode(SUM(V8006C11+V8006C12),0,0,SUM(V8006C9+V8006C10)/SUM(V8006C11+V8006C12)),4) as \建立成功率QCI5\
,sum(EU0507) as \上行字节数QCI1\
18
,sum(EU0508) as \下行字节数QCI1\ ,sum(EU0509) as \上行字节数QCI2\ ,sum(EU0510) as \下行字节数QCI2\ ,sum(EU0515) as \上行字节数QCI5\ ,sum(EU0516) as \下行字节数QCI5\,sum(EU0205) as \掉线率QCI1\
,round(decode(sum(M8001C305),0,0,sum(M8026C255)/sum(M8001C305)),4) as \上行丢包率_QCI1\,round(decode(sum(M8001C314+M8026C260),0,0,sum(M8026C260)/sum(M8001C314+M8026C260)),4) as \下行丢包率_QCI1\
,round(decode(sum(M8001C314+M8026C260),0,0,sum(M8001C323)/sum(M8001C314+M8026C260)),4) as \下行弃包率_QCI1\
,round(decode(sum(M8001C306),0,0,sum(M8026C256)/sum(M8001C306)),4) as \上行丢包率_QCI2\,round(decode(sum(M8001C315+M8026C261),0,0,sum(M8026C261)/sum(M8001C315+M8026C261)),4) as \下行丢包率_QCI2\
,round(decode(sum(M8001C315+M8026C261),0,0,sum(M8001C324)/sum(M8001C315+M8026C261)),4) as \下行弃包率_QCI2\
,avg(M8001C269) as \下行平均时延\,sum(M8016C34) as \切换成功次数\,sum(M8016C33) as \切换请求次数\
,sum(M8012C116)*15*60/8 as \语音上行流量\,sum(M8012C143)*15*60/8 as \语音下行流量\
,sum(M8001C419/100) as \语音UL话务量\,sum(M8001C227/100) as \语音DL话务量\
,sum(M8026C260) as \丢失的语音包数(下行)\
,sum(M8001C314+M8026C260) as \发送的语音包数(下行)\,sum(M8026C261) \丢失的视频包数(下行)\
,sum(M8001C315+M8026C261) \发送的视频包数(下行)\
,sum(M8013C17+M8013C18+M8013C19+M8013C21+decode(M8013C31,null,0,M8013C31)+decode(M8013C34,null,0,M8013C34)) \连接建立请求次数\ ,sum(M8013C5) \连接建立成功次数\
,sum(M8006C206+M8006C215) \建立成功次数(QCI=1)\,sum(M8006C188+M8006C197) \建立请求次数(QCI=1)\,sum(M8006C207+M8006C216) \建立成功次数(QCI=2)\ ,sum(M8006C189+M8006C198) \建立请求次数(QCI=2)\,sum(M8006C210+M8006C219) \建立成功次数(QCI=5)\,sum(M8006C192+M8006C201) \建立请求次数(QCI=5)\,sum(M8006C214+M8006C223) \建立成功次数(QCI=9)\,sum(M8006C196+M8006C205) \建立请求次数(QCI=9)\
,sum(M8006C176+M8006C143+M8006C152) \异常释放次数(QCI=1)\
19