发布时间 : 星期四 文章诺西LTE指标提取SQL语句20160801更新完毕开始阅读
,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_MNC1_RAW PMRAW where
period_start_time between to_date(&1,'yyyymmddHH24mi') and to_date(&2,'yyyymmddHH24mi') --- to_char(period_start_time,'yyyymmddHH24mi') >= to_char(SYSDATE-1,'yyyymmddHH24mi') -- and to_char(period_start_time,'yyyymmddHH24mi') <= to_char(SYSDATE-1,'yyyymmddHH24mi') --AND PERIOD_DURATION=15 group by
to_char(period_start_time,'yyyymmddHH24mi'),LNCEL_ID,to_char(period_start_time,'yyyymmddHH24mi')||LNCEL_ID )M8016, (
select
to_char(period_start_time,'yyyymmddHH24mi') sdatetime -- ,MRBTS_ID ,LNBTS_ID ,LNCEL_ID
,to_char(period_start_time,'yyyymmddHH24mi')||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_MNC1_RAW PMRAW where
period_start_time between to_date(&1,'yyyymmddHH24mi') and to_date(&2,'yyyymmddHH24mi') --AND PERIOD_DURATION=15 group by
to_char(period_start_time,'yyyymmddHH24mi'),MRBTS_ID,LNBTS_ID,LNCEL_ID,to_char(period_start_time,'yyyymmddHH24mi')||LNCEL_ID )M8020
44
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=m8020.cel_key_id(+) )
WHERE --enb_id in(203166,203188,203255,203250,203252,202861,203028,203165,203187,203251,203218,203029,201999,202360)
--enb_id in(&ENB) and
(enb_id between '111111' and '999999') or (enb_id between '11111' and '99999') or (enb_id between '1111' and '9999') or (enb_id between '111' and '999') or (enb_id between '11' and '99') or (enb_id between '1' and '9')
GROUP BY enb_cell,enb_id,cell_id,bts_ip,bts_version,bts_name,cel_name,sdatetime ORDER BY enb_cell,sdatetime;
6. --现网基础配置查询
select
distinct lncel.LNCEL_EUTRA_CEL_ID CellID ,bts.co_object_instance eNB_ID ,bts.co_name eNodeB_name ,LNCEL.LNCEL_LCR_ID Local_Cell_ID ,cel.co_main_host ipadd ,cel.co_sys_version version
,LNCEL.LNCEL_TAC TAC ,LNCEL.LNCEL_EARFCN EARFCN -- ,lncel.u U值
-- PDCCH 最大符号数
,LNCEL.LNCEL_PHY_CELL_ID PCI
,LNCEL.LNCEL_ROOT_SEQ_INDEX RACH_Root_Sequence ,LNCEL.LNCEL_P_RACH_CS PRACH_cyclic_shift ,lncel.LNCEL_PRCI_152 prachconfindex ,lncel.lncel_ul_rs_cs UL_RS_CS
,lncel.LNCEL_GAPS_80 groupassignmentforpusch
45
,LNCEL.LNCEL_TDD_FRAME_CONF TDDFRAME ,LNCEL.LNCEL_TSSC_296 SPEFRAME
--Prach前导格式
,LNCEL.LNCEL_P_MAX pmax
,lncel.lncel_a_3_offs A3_OFFSET ,lncel.lncel_act_drx ACT_DRX ,lncel.lncel_thld_1 thld_1
,lncel.lncel_thld_2_ifreq thld_2_ifreq ,lncel.lncel_thld_2_a thld_2_a
,lncel.lncel_th_2_tdscdma th_2_tdscdma ,lncel.lncel_thld_3 thld_3
,lncel.lncel_thld_3_a thld_3_a ,lncel.lncel_thld_4 thld_4
,lncel.lncel_thresh_srv_l thresh_srv_l
,lncel.lncel_max_num_act_drb max_num_act_drb ,lncel.lncel_max_num_act_ue max_num_act_ue ,lncel.lncel_max_num_rrc max_num_rrc ,lncel.LNCEL_DL_MIMO_MODE dlmimo
,cel.co_object_instance||'_'||LNCEL.LNCEL_LCR_ID eNBid_Loccel from
-- ctp_common_objects mrb ctp_common_objects bts ,ctp_common_objects cel ,c_lte_lnbts lnbts ,c_lte_lncel lncel -- ,OBJECTS O where
lncel.obj_gid=cel.co_gid and lnbts.obj_gid=bts.co_gid and cel.co_parent_gid=bts.co_gid -- and bts.co_parent_gid=mrb.co_gid -- and bts.co_gid=o.int_id
-- and mrb.co_oc_id=2859 and bts.co_oc_id=2860 and cel.co_oc_id=2881 and lncel.conf_id=1 and cel.co_state=0
--and length(mrb.co_object_instance)=6 --and mrb.co_object_instance like '5%'
46
7. --邻区指标查询
select
c.co_object_instance s_enb_id ,c.co_name s_enb_name
,cel.co_object_instance s_lncel ,lncel.lncel_lcr_id s_lcr_id ,lncel.Lncel_Earfcn s_Earfcn -- ,adjl.conf_id conf_id
-- ,adjl.last_modified last_modified --,adjl.LNADJL_AECI_18
,decode(adjl.LNADJL_CP_CONF_DL,0,'normal',1,'extended') \Cyclic Configuration\
,decode(adjl.LNADJL_CP_CONF_UL,0,'normal',1,'extended') \Cyclic Configuration\
--,adjl.LNADJL_DL_TRM_BW
,adjl.LNADJL_ECGI_ADJ_ENB_ID adj_enb_id ,adjl.LNADJL_ECGI_LCR_ID adj_LCRl_id ,adjl.LNADJL_F_DL_EARFCN adj_earfcn -- ,adjl.LNADJL_F_EARFCN_TD --,adjl.LNADJL_F_UL_EARFCN --,adjl.LNADJL_LN_ADLP_INF ,adjl.LNADJL_MCC adj_mcc ,adjl.LNADJL_MNC adj_mnc ,adjl.LNADJL_TAC adj_tac ,adjl.LNADJL_PHY_CELL_ID adj_pci
,adjl.LNADJL_SPC_SUB_CONF_TD adj_spc_sub_conf ,adjl.LNADJL_UL_DL_CONF_TD adj_ul_dl_conf ,decode(adjl.LNADJL_SRC_DATA,0,'OM',1,'UE',2,'X2') LNADJL_SRC_DATA
,decode(adjl.LNADJL_TRM_BW_TD,0,'1.4m',1,'3m',2,'5m',3,'10m',4,'15m',5,'20m') BW_TDD -- ,adjl.LNADJL_UL_TRM_BW ,decode(adjl.LNADJL_VAL_DATA,0,'false',1,'true') \ from
c_lte_lnadjl adjl
,ctp_common_objects a --adjl 3362
,ctp_common_objects b --adj bts\邻站“ 3346 ,ctp_common_objects c --lnbts 3129 ,ctp_common_objects cel ,c_lte_lncel lncel where
adjl.obj_gid=a.co_gid
and a.co_parent_gid=b.co_gid
47
Prefix Prefix