发布时间 : 星期五 文章诺西LTE指标提取SQL语句20160801更新完毕开始阅读
--,avg(M8001C216) DLPDSCH
,round(100*avg(M8011C50/(15*60*1000)*1/(1+4))/100,2)上行业务信息PRB占用率--天平均 ,round(100*avg(M8011C54/(15*60*1000)*4/(1+4))/100,2)下行业务信息PRB占用率--天平均
--,round(100*avg(M8011C50avg+M8011C54avg)/(24*60*60*1000*100), 2) 无线利用率 --,round(100*avg(M8011C50avg)/(24*60*60*1000*100/5),2) 上行PRB平均利用率 --,round(100*avg(M8011C54avg)/(24*60*60*1000*100*4/5),2) 下行PRB平均利用率 ,round(avg(M8012C23),2) 上行PDCP平均速率 ,round(avg(M8012C26),2) 下行PDCP平均速率 ,Round(sum(M8001C223),2) 平均激活用户数 ,max(M8001C224) 最大激活用户数
--,Round(decode(sum(M8011C54),0,0,sum(M8012C20)*8/(sum(M8011C54))),2) 下行每PRB平均速率 --,Round(decode(sum(M8011C50),0,0,sum(M8012C19)*8/(sum(M8011C50))),2) 上行每PRB平均速率 */
FROM (
SELECT M8013.sdate
-- ,M8020.MRBTS_ID -- ,M8020.LNBTS_ID -- ,M8020.LNCEL_ID
,enb_id || '_' || cell_id enb_cell ,enb_id ,cell_id ,bts_ip
,bts_version ,bts_name ,cel_name
,m8001C199,M8001C153,M8001C154,M8001C200,M8001C217,M8001C216,M8001C223, M8001C224,M8001C254,M8001C259,M8001C2,M8001C320,M8001C321 ,M8006C0,M8006C1,M8006C6,M8006C7,M8006C8,M8006C9,M8006C10,M8006C12,M8006C13,M8006C14,M8006C15,M8006C17,M8006C18,M8006C26,M8006C35,M8006C36,M8006C44,M8006C89,M8006C98 ,M8006C107,M8006C116,M8006C125,M8006C134,M8006C143,M8006C152,M8006C161,M8006C162,M8006C163,M8006C164,M8006C165,M8006C166,M8006C167,M8006C168,M8006C169,M8006C170,M8006C171,M8006C172,M8006C173,M8006C176,M8006C177,M8006C178,M8006C179,M8006C180 ,M8008C0,m8008c1,m8008c2,M8008C4,M8008C5 ,M8009C2,M8009C6,M8009C7 ,M8011C50,M8011C54
,M8012C19,M8012C20,M8012C23,M8012C26
,M8013C5,M8013C9,M8013C10,M8013C11,M8013C12,M8013C13,M8013C15,M8013C16,M8013C17,M8013C18,M8013C19,M8013C20,M8013C21,M8013C47,M8013C59,M8013C60
,M8014C0,M8014C6,M8014C7,M8014C8,M8014C14,M8014C18,M8014C19,M8014C20 ,M8015C2,M8015C9,M8015C8,M8015C5,M8015C6,M8015C7
,M8016C11,M8016C14,M8016C21,M8016C23,M8016C25,M8016C26,M8016C27,M8016C29,M8016C30
4
--,M8018C0,M8018C1,
,M8020C3,M8020C6,M8020C4 FROM ( select
to_char(period_start_time,'yyyymmdd') sdate ,LNCEL_ID
,to_char(period_start_time,'yyyymmdd')||LNCEL_ID cel_key_id
,sum(PDCP_SDU_UL) M8001C153 --The number of received PDCP SDUs (uplink) per cell. ,sum(PDCP_SDU_DL) M8001C154 --The number of transmitted PDCP SDUs per cell. ,sum(nvl(RRC_CONN_UE_AVG,0)) M8001C199 --The average number of UEs in RRC_CONNECTED state over the measurement period.
,max(nvl(RRC_CONN_UE_MAX,0)) M8001C200 --The highest value for number of UEs in RRC_CONNECTED state over the measurement period.
,avg(MEAN_PRB_AVAIL_PUSCH) M8001C217 --This measurement provides the average number of PRBs on PUSCH available for dynamic scheduling.
,avg(MEAN_PRB_AVAIL_PDSCH) M8001C216 --This measurement provides the average number of PRBs on PDSCH available for dynamic scheduling. ,avg(nvl(CELL_LOAD_ACT_UE_AVG,0)) M8001C223 --The average number of active UE per cell during measurement period.
,max(nvl(CELL_LOAD_ACT_UE_MAX,0)) M8001C224 --The max number of active UE per cell during measurement period.
,sum(PDCP_SDU_LOSS_UL) M8001C254 ---Number of missing UL PDCP packets of a data bearer that are not delivered to higher layers. ,sum(PDCP_SDU_LOSS_DL) M8001C259 ---Number of DL PDCP SDUs that could not be successfully transmitted.
,sum(nvl(SUM_ACTIVE_UE,0)) M8001C320 --This measurement provides the sum of sampled values for measuring the number of simultaneously Active UEs. This counter divided by the denominator DENOM_ACTIVE_UE provides the average number of Active UEs per cell.
A UE is active if at least a single non-GBR DRB has been successfully configured for it. ,sum(nvl(DENOM_ACTIVE_UE,0)) M8001C321 --The number of samples taken for counter SUM_ACTIVE_UE used as a denominator for average calculation. ,avg(PDCP_SDU_DELAY_DL_DTCH_MEAN) M8001C2 --The mean retention period for a PDCP SDU (DL) inside the eNB.
FROM NOKLTE_PS_LCELLD_lncel_hour PMRAW where
---to_char(period_start_time,'yyyymmdd') >= to_char(SYSDATE-1,'yyyymmdd')
period_start_time between to_date(&1,'yyyymmddhh24') and to_date(&2,'yyyymmddhh24') -- 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 )M8001
5
,(
select
to_char(period_start_time,'yyyymmdd') sdatetime ,LNCEL_ID
,to_char(period_start_time,'yyyymmdd')||LNCEL_ID cel_key_id
,sum(nvl(EPS_BEARER_SETUP_ATTEMPTS,0)) M8006C0 --The number of EPS bearer setup attempts. Each bearer of the E-RAB to Be Setup List IE is counted.
,sum(nvl(EPS_BEARER_SETUP_COMPLETIONS,0)) M8006C1 --The number of EPS bearer setup completions. Each bearer of the E-RAB Setup List IE is counted.
,sum(nvl(EPS_BEARER_SETUP_FAIL_RNL,0)) M8006C2 --The number of EPS bearer setup failures due to Radio Network Layer. Each bearer of the E-RAB Failed to Setup List IE is counted.
,sum(nvl(EPS_BEARER_SETUP_FAIL_TRPORT,0)) M8006C3 --The number of EPS bearer setup failures due to Transport Layer. Each bearer of the E-RAB Failed to Setup List IE is counted. ,sum(nvl(EPS_BEARER_SETUP_FAIL_RESOUR,0)) M8006C4 --The number of EPS bearer setup failures due to Resource reasons. Each bearer of the E-RAB Failed to Setup List IE has to be counted. ,sum(nvl(EPS_BEARER_SETUP_FAIL_OTH,0)) M8006C5 --The number of EPS bearer setup failures due to Other reasons. Each bearer of the E-RAB Failed to Setup List IE is counted. ,sum(nvl(EPC_EPS_BEARER_REL_REQ_NORM,0)) M8006C6 --The number of released Data Radio Bearers due to normal release per call. Each bearer of the E-RAB To Be Released List IE has to be counted. In case of a UE context release request, all established EPS Bearers are counted. ,sum(nvl(EPC_EPS_BEARER_REL_REQ_DETACH,0)) M8006C7 --The number of EPC-initiated EPS Bearer Release requests due to the Detach procedure by the UE or MME (NAS cause). Each bearer of the E-RAB To Be Released List IE has to be counted. In case of a UE context release request, all established EPS Bearers are counted.
,sum(nvl(EPC_EPS_BEARER_REL_REQ_RNL,0)) M8006C8 --The number of EPC-initiated EPS Bearer Release requests due to the Radio Network Layer cause. Each bearer of the E-RAB to be Released List IE is counted.
,sum(nvl(EPC_EPS_BEARER_REL_REQ_OTH,0)) M8006C9 --The number of released Data-Radio Bearers due to Other Reasons. Each bearer of the E-RAB To Be Released List IE has to be counted. In case of a UE context release request, all established EPS Bearer are counted. ,sum(nvl(ENB_EPS_BEARER_REL_REQ_NORM,0)) M8006C10 --The number of eNB-initiated EPS Bearer Release requests due to the UE inactivity. In case of the UE context release request, all the established EPS Bearers are counted. ,sum(nvl(ENB_EPS_BEARER_REL_REQ_RNL,0)) M8006C12 --The number of E-RABs requested to be released in case a Radio Link Failure is detected by eNB. ,sum(nvl(ENB_EPS_BEARER_REL_REQ_OTH,0)) M8006C13 --The number of eNB-initiated EPS Bearer Release requests due to Other causes . In case of a UE context release request, all the established EPS Bearers are counted.
,sum(nvl(ENB_EPS_BEARER_REL_REQ_TNL,0)) M8006C14 --The number of eNB-initiated EPS Bearer Release requests due to Transport Layer Cause
,sum(nvl(ENB_EPSBEAR_REL_REQ_RNL_REDIR,0)) M8006C15 --The number of eNB-initiated EPS Bearer Release requests due Redirect (release due to RNL E-UTRAN generated reason or RNL Inter-RAT Redirection). ,sum(nvl(EPS_BEARER_SETUP_FAIL_HO,0)) M8006C16 --The number of EPS bearer setup failures due to Handover Pending reason. Each bearer of the E-RAB Failed to Setup List IE is counted.
,sum(nvl(EPS_BEARER_STP_ATT_INI_QCI_1,0)) M8006C17 --The number of initial EPS bearer setup attempts per QCI1. Each bearer of the E-RAB to Be Setup List IE is counted.
6
,sum(nvl(EPS_BEAR_STP_ATT_INI_NON_GBR,0)) M8006C18 --The number of initial EPS bearer setup attempts per non-GBR. Each bearer of the E-RAB to Be Setup List IE is counted.
,sum(nvl(EPS_BEARER_STP_ATT_ADD_QCI_1,0)) M8006C26 --The number of additional EPS bearer setup attempts per QCI1. Each bearer of the E-RAB to Be Setup List IE is counted.
,sum(nvl(EPS_BEARER_STP_COM_INI_QCI1,0)) M8006C35 --The number of initial EPS bearer setup completions per QCI1. Each bearer of the E-RAB Setup List IE is counted.
,sum(nvl(EPS_BEAR_STP_COM_INI_NON_GBR,0)) M8006C36 --The number of initial EPS bearer setup completions per non-GBR. Each bearer of the E-RAB Setup List IE is counted.
,sum(nvl(EPS_BEAR_SET_COM_ADDIT_QCI1,0)) M8006C44 --The number of additional EPS bearer setup completions for QCI1. Each bearer of the E-RAB Setup List IE is counted.
,sum(nvl(EPC_EPS_BEAR_REL_REQ_N_QCI1,0)) M8006C89 --The number of EPC-initiated EPS Bearer Release requests for QCI1 due to normal release by UE. Each bearer of the E-RAB to be Released List IE is counted.
,sum(nvl(EPC_EPS_BEAR_REL_REQ_D_QCI1,0)) M8006C98 --The number of EPC-initiated EPS Bearer Release requests for QCI1 due to the Detach procedure by the UE or the MME. Each bearer of the E-RAB to be Released List IE is counted.
,sum(nvl(EPC_EPS_BEAR_REL_REQ_R_QCI1,0)) M8006C107 --The number of EPC-initiated EPS Bearer Release requests for QCI1 due to the Radio Network Layer cause. Each bearer of the E-RAB to be Released List IE is counted.
,sum(nvl(EPC_EPS_BEAR_REL_REQ_O_QCI1,0)) M8006C116 --The number of EPC-initiated EPS Bearer Release requests for QCI1 due to Other causes. Each bearer of the E-RAB to be Released List IE is counted. ,sum(nvl(ENB_EPS_BEAR_REL_REQ_N_QCI1,0)) M8006C125 --The number of eNB-initiated EPS Bearer Release requests for QCI1 due to the Normal release. In case of a UE context release request, all the established EPS Bearers are counted.
,sum(nvl(ENB_EPS_BEAR_REL_REQ_R_QCI1,0)) M8006C134 --The number of eNB-initiated EPS Bearer Release requests for QCI1 due to Radio Network Layer cause. In case of a UE context release request, all the established EPS Bearers are counted. ,sum(nvl(ENB_EPS_BEAR_REL_REQ_O_QCI1,0)) M8006C143 --The number of eNB-initiated EPS Bearer Release requests for QCI1 due to Other causes . In case of a UE context release request, all the established EPS Bearers are counted. ,sum(nvl(ENB_EPS_BEAR_REL_REQ_T_QCI1,0)) M8006C152 --The number of eNB-initiated EPS Bearer Release requests for QCI1 due Transport Layer Cause - Transport Resource UnavailableCause. ,sum(nvl(ENB_EPS_BEAR_REL_REQ_RD_QCI1,0)) M8006C161 --The number of eNB-initiated EPS Bearer Release requests for QCI1 due Redirect (release due to RNL E-UTRAN generated reason or RNL Inter-RAT Redirection )
,sum(nvl(EPS_BEARER_STP_ATT_INI_QCI_2,0)) M8006C162 --This measurement provides the number of initial EPS bearer setup attempts for GBR DRBs of QCI2 characteristics. ,sum(nvl(EPS_BEARER_STP_ATT_INI_QCI_3,0)) M8006C163 --This measurement provides the number of initial EPS bearer setup attempts for GBR DRBs of QCI3 characteristics.
,sum(nvl(EPS_BEARER_STP_ATT_INI_QCI_4,0)) M8006C164 --This measurement provides the number of initial EPS bearer setup attempts for GBR DRBs of QCI4 characteristics.
,sum(nvl(EPS_BEARER_STP_ATT_ADD_QCI_2,0)) M8006C165 --This measurement provides the number of additional EPS bearer setup attempts for GBR DRBs of QCI2 characteristics.
,sum(nvl(EPS_BEARER_STP_ATT_ADD_QCI_3,0)) M8006C166 --This measurement provides the number of additional EPS bearer setup attempts for GBR DRBs of QCI3 characteristics.
,sum(nvl(EPS_BEARER_STP_ATT_ADD_QCI_4,0)) M8006C167 --This measurement provides the number of
7