诺西LTE指标提取SQL语句20160801

NOKIA LTE KPI

2016-07

Oracle

1. --LTE全网指标......................................................................... 2 2. --ERB失败查询 ..................................................................... 17 3. --RRC失败查询 ..................................................................... 17 4. –VOLTE指标查询 .................................................................. 18 5. –15分组粒度全网指标查询 .................................................... 26 6. --现网基础配置查询 ............................................................... 45 7. --邻区指标查询 ....................................................................... 47 8. --小区低噪查询 ....................................................................... 48

本文主要包括LTE优化工作中，从数据库提取现网KPI的SQL脚本，经验证，均可查询

1. --LTE全网指标

SELECT enb_cell ,sdate ,enb_id

,enb_id*256+cell_id cell_id ,bts_ip

,bts_version ,bts_name ,cel_name

,round(decode(sum(M8020C6-M8020C4),0,0, 100*sum(M8020C3)/sum(M8020C6-M8020C4)),2) CellAvail ,Round(sum(M8012C19)/1000,2)用户面PDCP上行数据量KB ,Round(sum(M8012C20)/1000,2)用户面PDCP下行数据量KB

,sum(M8013C5) RRC连接建立成功次数

,sum(M8013C17+M8013C18+M8013C19+M8013C20+ M8013C21) RRC连接建立请求次数 ,sum(M8006C1) ERAB建立成功数 ,sum(M8006C0) ERAB建立请求数

,sum(M8006C176+M8006C177+M8006C178+M8006C179+M8006C180+M8013C59+M8013C60) 无线掉线 ,sum(M8013C47) 正常请求释放上下文数 ,sum(M8001C320) 上下文建立成功次数 ,sum(M8001C321) 遗留上下文数

--,sum(M8013C13) eNB请求释放上下文_user --,sum(M8013C15) eNB请求释放上下文_RNL --,sum(M8013C16) eNB请求释放上下文_other

,sum(M8014C14) eNB间S1切换出请求次数 ,sum(M8014C19) eNB间S1切换出成功次数 ,sum(M8014C0) eNB间X2切换出请求次数 ,sum(M8014C7) eNB间X2切换出成功次数 ,sum(M8009C6) eNB内切换出请求次数 ,sum(M8009C7) eNB内切换出成功次数

,Round(decode((nvl(sum(M8013C47),0)+nvl(decode(sum(M8001C321),0,0,ceil(sum(M8001C320)/sum(M8001C321))),0)),0,0,

100*(nvl(sum(M8006C176),0)+nvl(sum(M8006C177),0)+nvl(sum(M8006C178),0)+nvl(sum(M8006C179),0)+nvl(sum(M8006C180),0)+nvl(sum(M8013C59),0)+nvl(sum(M8013C60),0))

/(nvl(sum(M8013C47),0)+nvl(decode(sum(M8001C321),0,0,ceil(sum(M8001C320)/sum(M8001C321))),0))),2) 无线掉线率_RL55

/*,sum(M8009C7 + M8014C7 + M8014C19)切换成功数 ,sum(M8009C6 + M8014C0 + M8014C14)切换数

,sum(M8008C2) 寻呼记录丢弃个数 ,sum(M8008C1) 寻呼记录接收个数

,avg(M8011C50/(15*60*1000)*1/(1+4))上行PRB占用平均数 ,avg(M8011C54/(15*60*1000)*4/(1+4))下行PRB占用平均数

,sum(M8009C6 + M8014C0 + M8014C14)-sum(M8009C7 + M8014C7 + M8014C19)切换失败次数

,sum(M8013C17+M8013C18+M8013C19+M8013C20+ M8013C21)-sum(M8013C5)RRC连接建立失败次数 ,sum(M8006C0)-sum(M8006C1)ERAB建立失败次数

,sum(M8013C13+M8006C176+M8006C177+M8006C178+M8006C179+M8006C180+M8013C16+M8016C11)-sum(M8013C13+M8016C11) 无线掉线次数

,sum(M8016C25+M8006C12 + M8006C13 + M8006C14)ERAB掉线次数

,Round(Decode(sum(M8013C17+M8013C18+M8013C19+M8013C20+M8013C21)*sum(M8006C0),0,0,100*sum(M8013C5)/sum(M8013C17+M8013C18+M8013C19+M8013C20+M8013C21)*sum(M8006C1)/sum(M8006C0)),2)无线接通率

,Round(Decode(sum(M8006C35+M8006C36+M8006C168+M8006C169+M8006C170),0,0,100*sum(M8013C15+M8013C16)/sum(M8006C35+M8006C36+M8006C168+M8006C169+M8006C170)),2) 无线掉线率

,Round(Decode(sum(M8009C6 + M8014C0 + M8014C14),0,0,100* sum(M8009C7 + M8014C7 + M8014C19) / sum(M8009C6 + M8014C0 + M8014C14)),2)切换成功率

--,Round(Decode(sum(M8006C1 + M8001C223),0,0,100 * sum(M8016C25+M8006C12 + M8006C13 + M8006C14) / sum(M8006C1 + M8001C223)),2) ERAB掉线率 ,sum(M8006C1) ERAB建立成功数 ,sum(M8006C0) ERAB建立请求数

--,Round(Decode(sum(M8006C0),0,0,100*sum(M8006C1)/sum(M8006C0)),2) ERAB建立成功率 --,Round(Decode(sum(M8013C17+M8013C18+M8013C19+M8013C20+ M8013C21),0,0,100*sum(M8013C5)/sum(M8013C17+M8013C18+M8013C19+M8013C20+ M8013C21)),2) RRC连接建立成功率 --,sum(M8009C7)/sum(M8009C6)eNB内切换成功率

--,sum(M8014C14+M8014C7)/sum(M8014C14+M8014C0)eNB间切换成功率

,sum(M8006C10+M8006C176+M8006C177+M8006C178+M8006C179+M8006C180+M8006C13+M8006C14+M8006C15)

eNB_req_rel_ERAB

,sum(M8006C15 + M8006C10) Norm_req_rel_eRAB ,sum(M8016C25) ERAB_hooutfail

,Round(avg(M8001C2),2) 小区用户面DL平均时延 ,sum(M8015C2+M8015C9) 切换入eRAB数 ,sum(M8001C223)遗留上下文数 --,avg(M8001C217) ULPUSCH

--,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

--,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

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.

,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

additional EPS bearer setup attempts for GBR DRBs of QCI4 characteristics.

,sum(nvl(EPS_BEARER_STP_COM_INI_QCI_2,0)) M8006C168 --This measurement provides the number of initial EPS bearer setup completions for GBR DRBs of QCI2 characteristics.

,sum(nvl(EPS_BEARER_STP_COM_INI_QCI_3,0)) M8006C169 --This measurement provides the number of initial EPS bearer setup completions for GBR DRBs of QCI3 characteristics.

,sum(nvl(EPS_BEARER_STP_COM_INI_QCI_4,0)) M8006C170 --This measurement provides the number of initial EPS bearer setup completions for GBR DRBs of QCI4 characteristics.

,sum(nvl(EPS_BEARER_STP_COM_ADD_QCI_2,0)) M8006C171 --This measurement provides the number of additional EPS bearer setup completions for GBR DRBs of QCI2 characteristics.

,sum(nvl(EPS_BEARER_STP_COM_ADD_QCI_3,0)) M8006C172 --This measurement provides the number of additional EPS bearer setup completions for GBR DRBs of QCI3 characteristics.

,sum(nvl(EPS_BEARER_STP_COM_ADD_QCI_4,0)) M8006C173 --This measurement provides the number of additional EPS bearer setup completions for GBR DRBs of QCI4 characteristics. ,sum(nvl(PRE_EMPT_GBR_BEARER,0)) M8006C174 --This measurement provides the number of GBR E-RABs (Guaranteed Bit Rate bearers, 3GPP TS 23.203) being released due to lack of radio resources. ,sum(nvl(PRE_EMPT_NON_GBR_BEARER,0)) M8006C175 --This measurement provides the number of non-GBR E-RABs (non-Guaranteed Bit Rate bearers, 3GPP TS 23.203) being released due to lack of radio resources.

,sum(nvl(ERAB_REL_ENB_ACT_QCI1,0)) M8006C176 --This measurement provides the number of released active E-RABs (i.e. when there was user data in the queue at the time of release) with QCI1 characteristics. The release is initiated by the eNB due to radio connectivity problems. ,sum(nvl(ERAB_REL_ENB_ACT_QCI2,0)) M8006C177 --This measurement provides the number of released active E-RABs (i.e. when there was user data in the queue at the time of release) with QCI2 characteristics. The release is initiated by the eNB due to radio connectivity problems. ,sum(nvl(ERAB_REL_ENB_ACT_QCI3,0)) M8006C178 --This measurement provides the number of released active E-RABs (i.e. when there was user data in the queue at the time of release) with QCI3 characteristics. The release is initiated by the eNB due to radio connectivity problems. ,sum(nvl(ERAB_REL_ENB_ACT_QCI4,0)) M8006C179 --This measurement provides the number of released active E-RABs (i.e. when there was user data in the queue at the time of release) with QCI4 characteristics. The release is initiated by the eNB due to radio connectivity problems. ,sum(nvl(ERAB_REL_ENB_ACT_NON_GBR,0)) M8006C180 --This measurement provides the number of released active E-RABs (i.e. when there was user data in the queue at the time of release) with non-GBR characteristics (QCI5..9). The release is initiated by the eNB due to radio connectivity problems. ,sum(nvl(ERAB_IN_SESSION_TIME_QCI1,0)) M8006C181 --This measurement provides the aggregated in-session activity time in seconds for all E-RABs with QCI1 characteristics. The E-RAB is said to be in session if any user data has been transferred in UL or DL direction within the last 100msec. ,sum(nvl(ERAB_IN_SESSION_TIME_QCI2,0)) M8006C182 --This measurement provides the aggregated in-session activity time in seconds for all E-RABs with QCI2 characteristics. The E-RAB is said to be in session if any user data has been transferred in UL or DL direction within the last 100msec.

,sum(nvl(ERAB_IN_SESSION_TIME_QCI3,0)) M8006C183 --This measurement provides the aggregated in-session activity time in seconds for all E-RABs with QCI3 characteristics. The E-RAB is said to be in session if any user data has been transferred in UL or DL direction within the last 100msec.

,sum(nvl(ERAB_IN_SESSION_TIME_QCI4,0)) M8006C184 --This measurement provides the aggregated in-session activity time in seconds for all E-RABs with QCI4 characteristics. The E-RAB is said to be in session if any user data has been transferred in UL or DL direction within the last 100msec.

,sum(nvl(ERAB_IN_SESSION_TIME_NON_GBR,0)) M8006C185 --This measurement provides the aggregated

in-session activity time in seconds for all E-RABs with non-GBR (QCI5..9) characteristics. The E-RAB is said to be in session if any user data has been transferred in UL or DL direction within the last 100msec. from

NOKLTE_PS_LEPSB_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 )M8006,( select

to_char(period_start_time,'yyyymmdd') sdate ,LNCEL_ID

,to_char(period_start_time,'yyyymmdd')||LNCEL_ID cel_key_id ,sum(nvl(REJ_RRC_CONN_RE_ESTAB,0)) M8008C0 --The number of rejected RRC Connection re-establishments.

,sum(nvl(RRC_PAGING_REQUESTS,0)) M8008C1 --The number of RRC paging requests (records). ,sum(nvl(DISC_RRC_PAGING,0)) M8008C2 --The number of discarded RRC paging requests (records). ,sum(nvl(RRC_PAGING_MESSAGES,0)) M8008C3 --The number of transmitted RRC paging messages. ,sum(nvl(RRC_CON_RE_ESTAB_ATT,0)) M8008C4 --The number of attempted RRC Connection Re-establishment procedures.

,sum(nvl(RRC_CON_RE_ESTAB_SUCC,0)) M8008C5 --The number of successful RRC Connection Re-establishment procedures.

,sum(nvl(RRC_CON_RE_ESTAB_ATT_HO_FAIL,0)) M8008C6 --The number of RRC Connection Re-establishment attempts per cause (Handover Failure). ,sum(nvl(RRC_CON_RE_ESTAB_SUCC_HO_FAIL,0)) M8008C7 --The number of successful RRC Connection Re-establishment procedures per cause (Handover Failure). ,sum(nvl(RRC_CON_RE_ESTAB_ATT_OTHER,0)) M8008C8 --The number of RRC Connection Re-establishment attempts per cause (Other failure). ,sum(nvl(RRC_CON_RE_ESTAB_SUCC_OTHER,0)) M8008C9 --The number of successful RRC Connection Re-establishment procedures per cause (Other Failure). ,sum(nvl(REPORT_CGI_REQ,0)) M8008C10 --This counter provides the total number of attempts to retrieve the CGI of a neighbor cell from UE. ,sum(nvl(SUCC_CGI_REPORTS,0)) M8008C11 --This counter provides the number of CGI measurement reports received from UE. from

NOKLTE_PS_LRRC_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 )M8008,( select

to_char(period_start_time,'yyyymmdd') sdate ,LNCEL_ID

,to_char(period_start_time,'yyyymmdd')||LNCEL_ID cel_key_id

,sum(nvl(TOT_NOT_START_HO_PREP,0)) M8009C0 --The number of not started Handover preparations. The RRM receives an RRC Measurement Report (UE -; eNB), but the RRM decides not to start a Handover preparation phase. No target cell list will be handed over to the mobility management (MM) unit.

,sum(nvl(TOT_HO_DECISION,0)) M8009C1 --The number of positive Handover decisions. In case of a positive Handover decision, the RRM transmits a target cell list to the mobility management (MM) unit. ,sum(nvl(INTRA_ENB_HO_PREP,0)) M8009C2 --The number of Intra-eNB Handover preparations. ,sum(nvl(FAIL_ENB_HO_PREP_AC,0)) M8009C3 --The number of failed Intra-eNB Handover preparations due to Admission Control. Includes failures to set up data forwarding in the target cell. ,sum(nvl(FAIL_ENB_HO_PREP_OTH,0)) M8009C5 --The number of failed Intra-eNB Handover preparations due to other reasons.

,sum(nvl(ATT_INTRA_ENB_HO,0)) M8009C6 --The number of Intra-eNB Handover attempts. ,sum(nvl(SUCC_INTRA_ENB_HO,0)) M8009C7 --The number of successful Intra-eNB Handover completions. ,sum(nvl(ENB_INTRA_HO_FAIL,0)) M8009C8 --The number of Intra-eNB Handover failures due to the guarding timer THOoverall.

,sum(nvl(ENB_HO_DROP_RLFAIL,0)) M8009C12 --The number of Intra-eNB Handover drops due to Radio Link Failure.

,sum(nvl(ENB_HO_DROP_OTHERFAIL,0)) M8009C13 --The number of Intra-eNB Handover drops due to other failures.

from

NOKLTE_PS_LIANBHO_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 )M8009,( select

to_char(period_start_time,'yyyymmdd') sdate ,LNCEL_ID

,to_char(period_start_time,'yyyymmdd')||LNCEL_ID cel_key_id

,avg(nvl(PRB_USED_PUSCH,0)) M8011C50--Total number of used PRB's for PUSCH scheduling over the measurement period.

,avg(nvl(PRB_USED_PDSCH,0)) M8011C54 --Total number of used PRB's for PDSCH scheduling over the measurement period. from

NOKLTE_PS_LCELLR_lncel_hour PMRAW where

to_char(period_start_time,'yyyymmdd'),LNCEL_ID,to_char(period_start_time,'yyyymmdd')||LNCEL_ID

) M8011 ,( select

to_char(period_start_time,'yyyymmdd') sdate ,LNCEL_ID

,to_char(period_start_time,'yyyymmdd')||LNCEL_ID cel_key_id ,sum(nvl(PDCP_SDU_VOL_UL,0)) M8012C19 --The measurement gives an indication of the eUu interface traffic load by reporting the total received PDCP SDU-related traffic volume. ,sum(nvl(PDCP_SDU_VOL_DL,0)) M8012C20 --The measurement gives an indication of the eUu interface traffic load by reporting the total transmitted PDCP SDU-related traffic volume. ,round(avg(decode(PDCP_DATA_RATE_MEAN_UL,0,NULL,PDCP_DATA_RATE_MEAN_UL)),2) M8012C23 --The measurement gives indication of the eUu interface traffic load by reporting Mean UL PDCP traffic throughput

,round(avg(decode(PDCP_DATA_RATE_MEAN_DL,0,NULL,PDCP_DATA_RATE_MEAN_DL)),2) M8012C26 --The measurement gives indication of the eUu interface traffic load by reporting Mean DL PDCP traffic throughput.

from

NOKLTE_PS_LCELLT_lncel_hour PMRAW where

to_char(period_start_time,'yyyymmdd'),LNCEL_ID,to_char(period_start_time,'yyyymmdd')||LNCEL_ID )M8012,( select

to_char(period_start_time,'yyyymmdd') sdate ,LNCEL_ID

,to_char(period_start_time,'yyyymmdd')||LNCEL_ID cel_key_id

,sum(nvl(SIGN_CONN_ESTAB_COMP,0)) M8013C5 --The number of Signaling Connection Establishment completions with the UE target to be in the ECM-CONNECTED state.

,sum(nvl(SIGN_EST_F_RRCCOMPL_MISSING,0)) M8013C6 --The number of Signaling Connection Establishment failures due to a missing RRC CONNECTION SETUP COMPLETE message. The UE has not reached the ECM-CONNECTED state.

,sum(nvl(SIGN_EST_F_RRCCOMPL_ERROR,0)) M8013C7 --The number of Signaling Connection Establishment failures due to the Erroneous or incomplete RRC CONNECTION SETUP COMPLETE message. The UE has not reached the ECM-CONNECTED state.

,sum(nvl(SIGN_CONN_ESTAB_FAIL_RRMRAC,0)) M8013C8 --The number of Signaling Connection Establishment failures due to Rejection by RRM RAC. The UE has not reached the ECM-CONNECTED state. ,sum(nvl(EPC_INIT_TO_IDLE_UE_NORM_REL,0)) M8013C9 --The number of EPC-initiated transitions to the ECM-IDLE state due to a Normal release by the UE .

,sum(nvl(EPC_INIT_TO_IDLE_DETACH,0)) M8013C10 --The number of EPC-initiated transitions to the ECM-IDLE state due to the Detach procedure by the UE or the MME .

,sum(nvl(EPC_INIT_TO_IDLE_RNL,0)) M8013C11 --The number of EPC initiated transitions to ECM-IDLE state due to Radio Network Layer cause. The UE-associated logical S1-connection is released. ,sum(nvl(EPC_INIT_TO_IDLE_OTHER,0)) M8013C12 --The number of EPC-initiated transitions to the ECM-IDLE state due to Other causes.

,sum(nvl(ENB_INIT_TO_IDLE_NORM_REL,0)) M8013C13 --The number of eNB-initiated transitions from the ECM-CONNECTED to ECM-IDLE state due to User Inactivity or Redirect. The UE-associated logical S1-connection is released.

,sum(nvl(ENB_INIT_TO_IDLE_RNL,0)) M8013C15 --The number of eNB initiated transitions from the ECM-CONNECTED to ECM-IDLE state when the Radio Connection to the UE is lost. The UE-associated logical S1-connection is released.

,sum(nvl(ENB_INIT_TO_IDLE_OTHER,0)) M8013C16 --The number of eNB-initiated transitions from the ECM-CONNECTED to ECM-IDLE state due to Other causes than User Inactivity, Redirect or Radio Connection Lost.

,sum(nvl(SIGN_CONN_ESTAB_ATT_MO_S,0)) M8013C17 --The number of Signaling Connection Establishment attempts for mobile originated signaling. From UE's point of view, the transition from ECM-IDLE to ECM-CONNECTED has started. ,sum(nvl(SIGN_CONN_ESTAB_ATT_MT,0)) M8013C18 --The number of Signaling Connection Establishment attempts for mobile terminated connections. From UE's point of view, the transition from ECM-IDLE to ECM-CONNECTED is started. ,sum(nvl(SIGN_CONN_ESTAB_ATT_MO_D,0)) M8013C19 --The number of Signaling Connection Establishment attempts for mobile originated data connections. From UE's point of view, the transition from ECM-IDLE to ECM-CONNECTED is started. ,sum(nvl(SIGN_CONN_ESTAB_ATT_OTHERS,0)) M8013C20 --The number of Signaling Connection Establishment attempts due to other reasons. From UE's point of view, the transition from ECM-IDLE to ECM-CONNECTED is started. ,sum(nvl(SIGN_CONN_ESTAB_ATT_EMG,0)) M8013C21 --Number of Signalling Connection Establishment attempts for emergency calls

,sum(nvl(SUBFRAME_DRX_ACTIVE_UE,0)) M8013C24 --The number of subframes, when UE is DRX Active. ,sum(nvl(SUBFRAME_DRX_SLEEP_UE,0)) M8013C25 --The number of subframes, when UE is DRX Sleep (i.e. not DRX Active).

,sum(nvl(SIGN_CONN_ESTAB_COMP_EMG,0)) M8013C26 --The number of Signalling Connection Establishment completions for emergency calls

,sum(nvl(SIGN_CONN_ESTAB_FAIL_RB_EMG,0)) M8013C27 --The number of Signalling Connection Establishment failures for emergency calls due to missing RB (Radio Bearer) resources

,sum(nvl(PRE_EMPT_UE_CONTEXT_NON_GBR,0)) M8013C28 --This measurement provides the number of UE contexts being released due to lack of radio resources. ,sum(nvl(UE_CTX_SETUP_SUCC,0)) M8013C47

,sum(nvl(UE_CTX_REL_ENB_NO_RADIO_RES,0)) M8013C59 --This counter provides the number of released UE contexts initiated by the eNB due to missing radio resources. ,sum(nvl(UE_CTX_REL_ENB_RNL_UNSPEC,0)) M8013C60 ,lnbts.co_object_instance enb_id ,lncel.co_object_instance cell_id ,lnbts.co_main_host bts_ip

,lnbts.co_ocv_sys_version bts_version ,Trim(lnbts.co_name) bts_name ,Trim(lncel.co_name) cel_name 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')

--- to_char(period_start_time,'yyyymmdd') >= to_char(SYSDATE-1,'yyyymmdd') -- and to_char(period_start_time,'yyyymmdd') <= to_char(SYSDATE-1,'yyyymmdd') AND PMRAW.LNCEL_ID=lncel.co_gid AND lnbts.co_oc_id=3129 AND lnbts.CO_STATE<>9 AND lncel.co_oc_id=3130 AND lncel.CO_STATE<>9 and PMRAW.lnbts_id=lnbts.co_gid --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

,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,( select

to_char(period_start_time,'yyyymmdd') sdate ,LNCEL_ID ,to_char(period_start_time,'yyyymmdd')||LNCEL_ID cel_key_id ,sum(nvl(INTER_ENB_HO_PREP,0)) M8014C0 --The number of Inter-eNB X2-based Handover preparations. The Mobility management (MM) receives a list with target cells from the RRM and decides to start an Inter-eNB X2-based Handover.

,sum(nvl(FAIL_ENB_HO_PREP_TIME,0)) M8014C2 --The number of failed Inter-eNB X2-based Handover preparations due to timer TX2RELOCprep.

,sum(nvl(FAIL_ENB_HO_PREP_AC,0)) M8014C3 --The number of failed Inter-eNB X2-based Handover preparations due to the target eNB's admission control reasons.

,sum(nvl(FAIL_ENB_HO_PREP_OTHER,0)) M8014C5 --The number of failed Inter-eNB X2-based Handover preparations due to the target eNB's other reasons.

,sum(nvl(ATT_INTER_ENB_HO,0)) M8014C6 --The number of Inter-eNB X2-based Handover attempts.

,sum(nvl(SUCC_INTER_ENB_HO,0)) M8014C7 --The number of successful Inter-eNB X2-based Handover completions.

,sum(nvl(INTER_ENB_HO_FAIL,0)) M8014C8 --Number of Inter eNB Handover failures due to expiration of guarding timer TX2RELOCoverall

,sum(nvl(INTER_ENB_S1_HO_PREP,0)) M8014C14 --The number of Inter eNB S1-based Handover preparations

,sum(nvl(INTER_S1_HO_PREP_FAIL_TIME,0)) M8014C15 --The number of failed Inter eNB S1-based Handover preparations due to the expiry of the guarding timer TS1RELOCprep.

,sum(nvl(INTER_S1_HO_PREP_FAIL_NORR,0)) M8014C16 --The number of failed Inter eNB S1-based Handover preparations with cause No Radio Resources Available in Target Cell.

,sum(nvl(INTER_S1_HO_PREP_FAIL_OTHER,0)) M8014C17 --The number of failed Inter eNB S1-based Handover preparations due to the reception of an S1AP: HANDOVER PREPARATION FAILURE message with a cause other than No Radio Resources Available in Target Cell.

,sum(nvl(INTER_ENB_S1_HO_ATT,0)) M8014C18 --The number of Inter eNB S1-based Handover attempts ,sum(nvl(INTER_ENB_S1_HO_SUCC,0)) M8014C19 --The number of successful Inter eNB S1-based Handover completions

,sum(nvl(INTER_ENB_S1_HO_FAIL,0)) M8014C20 --The number of Inter eNB S1-based Handover failures from

NOKLTE_PS_LIENBHO_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 )M8014, (

select

to_char(period_start_time,'yyyymmdd') sdatetime ,LNCEL_ID ,to_char(period_start_time,'yyyymmdd')||LNCEL_ID cel_key_id ,sum(nvl(INTRA_HO_SUCC_NB,0)) M8015C2 --The number of successful Intra-eNB Handover completions per neighbour cell relationship. ,sum(nvl(INTER_HO_SUCC_NB,0)) M8015C9 --The number of successful Inter eNB Handover completions per neighbour cell relationship

,SUM(NVL(INTER_HO_PREP_FAIL_OTH_NB,0)) M8015C5 --The number of failed Inter eNB Handover preparations per cause per neighbour cell relationship

,SUM(NVL(INTER_HO_PREP_FAIL_TIME_NB,0)) M8015C6 --The number of failed Inter eNB Handover preparations per neighbour cell relationship due to the expiration of the respective guarding timer.

,SUM(NVL(INTER_HO_PREP_FAIL_AC_NB,0)) M8015C7 --The number of failed Inter eNB Handover preparations per neighbour cell relationship due to failures in the HO preparation on the target side

,SUM(NVL(INTER_HO_ATT_NB,0)) M8015C8 --The number of Inter eNB Handover attempts per neighbour cell relationship

from

NOKLTE_PS_LNCELHO_lncel_hour PMRAW where

to_char(period_start_time,'yyyymmdd'),LNCEL_ID,to_char(period_start_time,'yyyymmdd')||LNCEL_ID )M8015,( select

to_char(period_start_time,'yyyymmdd') sdatetime ,LNCEL_ID

,to_char(period_start_time,'yyyymmdd')||LNCEL_ID cel_key_id ,sum(nvl(CSFB_REDIR_CR_ATT,0)) M8016C11 --The number of CS Fallback attempts with redirection via the RRC Connection Release

,sum(nvl(CSFB_REDIR_CR_CMODE_ATT,0)) M8016C12 --The number of CS Fallback attempts (UE in Connected Mode) with redirection via the RRC Connection Release ,sum(nvl(CSFB_REDIR_CR_EMERGENCY_ATT,0)) M8016C13 --The number of CS Fallback attempts for emergency call reason with redirection via the RRC Connection Release ,sum(nvl(ISYS_HO_PREP,0)) M8016C14 --Number of Inter System Handover preparations. ,sum(nvl(ISYS_HO_PREP_FAIL_TIM,0)) M8016C15 --Number of failed Inter System Handover preparations due to expiration of guarding timer.

,sum(nvl(ISYS_HO_ATT,0)) M8016C21 --Number of Inter System Handover attempts. ,sum(nvl(ISYS_HO_SUCC,0)) M8016C23 --Number of successful Inter System Handover completions. ,sum(nvl(ISYS_HO_FAIL,0)) M8016C25 --Number of failed Inter System Handover attempts. ,sum(nvl(NACC_TO_GSM_ATT,0)) M8016C26 --This measurement provides the number of NACC from LTE to GSM attempts

,sum(nvl(NACC_TO_GSM_SUCC,0)) M8016C27 --This measurement provides the number of successful NACC from LTE to GSM completions ,sum(nvl(NACC_TO_GSM_FAIL,0)) M8016C28 --This measurement provides the number of failed NACC from LTE to GSM.

,sum(nvl(ISYS_HO_UTRAN_SRVCC_ATT,0)) M8016C29 --This measurement provides the number of Inter System Handover attempts to UTRAN with SRVCC (Single Radio Voice Call Continuity, 3GPP TS 23.216).

,sum(nvl(ISYS_HO_UTRAN_SRVCC_SUCC,0)) M8016C30 --This measurement provides the number of successful Inter System Handover completions to UTRAN with SRVCC (Single Radio Voice Call Continuity, 3GPP TS 23.216).

,sum(nvl(ISYS_HO_UTRAN_SRVCC_FAIL,0)) M8016C31 --This measurement provides the number of failed Inter System Handover attempts to UTRAN with SRVCC (Single Radio Voice Call Continuity, 3GPP TS 23.216). ,sum(nvl(CSFB_PSHO_UTRAN_ATT,0)) M8016C32 --This measurement provides the number of CS Fallback attempts to UTRAN with PS Handover (Circuit Switched Fallback in Evolved Packet System, 3GPP TS 23.272). ,sum(nvl(ISYS_HO_GERAN_SRVCC_ATT,0)) M8016C33 --This measurement provides the number of Inter

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

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

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

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\

,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)\

,sum(M8006C206+M8006C215+decode(M8006C54,0,0,ceil(M8006C45/M8006C54))) \掉线率分母(QCI=1)\

,sum(M8006C177) \异常释放次数(QCI=2)\

,sum(M8006C207+M8006C216+decode(M8006C54,0,0,ceil(M8006C46/M8006C54))) \掉线率分母(QCI=2)\

,AVG(M8008C17) \连接建立时长\,AVG(M8007C14) \建立时长\

,avg(M8011C50)/17280000 \上行PRB利用率\,avg(M8011C54)/69120000 \下行PRB利用率\

FROM (

SELECT M8013.sdatetime

-- ,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

,EU0427,M8001C305,M8001C314,M8001C323,M8001C306,M8001C315,M8001C324,M8001C269,M8001C419,M8001C227

,V8006C1,V8006C2,V8006C3,V8006C4,V8006C5,V8006C6,V8006C7,V8006C8,V8006C9,V8006C10,V8006C11,V8006C12 ,EU0205,EU0507,EU0508,EU0509,EU0510,EU0515,EU0516 ,M8006C45,M8006C46,M8006C54,M8006C143,M8006C152,M8006C176,M8006C177,M8006C188,M8006C189,M8006C192,M8006C196,M8006C197,M8006C198,M8006C201,M8006C205 ,M8006C206,M8006C207,M8006C210,M8006C214,M8006C215,M8006C216,M8006C219,M8006C223

,M8007C14 ,M8008C17

,M8026C255,M8026C260,M8026C256,M8026C261 ,M8011C50,M8011C54 ,M8012C116,M8012C143

,M8013C5,M8013C17,M8013C18,M8013C19,M8013C21,M8013C26,M8013C31,M8013C34 ,M8016C34,M8016C33

FROM

( select

to_char(period_start_time,'yyyymmddhh24') sdatetime ,LNCEL_ID

,to_char(period_start_time,'yyyymmddhh24')||LNCEL_ID cel_key_id

,AVG(nvl(PDCP_RET_DL_DEL_MEAN_QCI_2,0)) as EU0427 ,sum(nvl(PDCP_SDU_UL_QCI_1,0)) as M8001C305 ,sum(nvl(PDCP_SDU_DL_QCI_1,0)) as M8001C314

,sum(nvl(PDCP_SDU_DISC_DL_QCI_1,0)) as M8001C323 ,sum(nvl(PDCP_SDU_UL_QCI_2,0)) as M8001C306 ,sum(nvl(PDCP_SDU_DL_QCI_2,0)) as M8001C315

,sum(nvl(PDCP_SDU_DISC_DL_QCI_2,0)) as M8001C324

,avg(nvl(PDCP_RET_DL_DEL_MEAN_QCI_1,0)) as M8001C269 ,sum(nvl(UE_DRB_UL_DATA_QCI_1,0)) as M8001C419 ,sum(nvl(UE_DRB_DL_DATA_QCI_1,0)) as M8001C227

FROM NOKLTE_PS_LCELLD_lncel_hour PMRAW where

---to_char(period_start_time,'yyyymmddHH24') >= to_char(SYSDATE-1,'yyyymmddHH24') period_start_time between to_date(&1,'yyyymmddhh24') and to_date(&2,'yyyymmddhh24') -- and to_char(period_start_time,'yyyymmddHH24') <= to_char(SYSDATE-1,'yyyymmddHH24') --AND PERIOD_DURATION=15 group by

to_char(period_start_time,'yyyymmddhh24'),LNCEL_ID,to_char(period_start_time,'yyyymmddhh24')||LNCEL_ID )M8001 ,(

select

to_char(period_start_time,'yyyymmddhh24') sdatetime ,LNCEL_ID ,to_char(period_start_time,'yyyymmddhh24')||LNCEL_ID cel_key_id

,SUM(nvl(ERAB_INI_SETUP_SUCC_QCI1,0)) V8006C1 ,sum(nvl(ERAB_ADD_SETUP_SUCC_QCI1,0)) V8006C2 ,SUM(nvl(ERAB_INI_SETUP_ATT_QCI1,0)) V8006C3 ,sum(nvl(ERAB_ADD_SETUP_ATT_QCI1,0)) V8006C4 ,SUM(nvl(ERAB_INI_SETUP_SUCC_QCI2,0)) V8006C5 ,sum(nvl(ERAB_ADD_SETUP_SUCC_QCI2,0)) V8006C6 ,SUM(nvl(ERAB_INI_SETUP_ATT_QCI2,0)) V8006C7 ,sum(nvl(ERAB_ADD_SETUP_ATT_QCI2,0)) V8006C8 ,SUM(nvl(ERAB_INI_SETUP_SUCC_QCI5,0)) V8006C9 ,sum(nvl(ERAB_ADD_SETUP_SUCC_QCI5,0)) V8006C10 ,SUM(nvl(ERAB_INI_SETUP_ATT_QCI5,0)) V8006C11 ,sum(nvl(ERAB_ADD_SETUP_ATT_QCI5,0)) V8006C12

,sum(nvl(SUM_SIMUL_ERAB_QCI_1,0)) as M8006C45 ,sum(nvl(SUM_SIMUL_ERAB_QCI_2,0)) as M8006C46 ,sum(nvl(DENOM_SUM_SIMUL_ERAB,0)) as M8006C54

,sum(nvl(ENB_EPS_BEAR_REL_REQ_O_QCI1,0)) as M8006C143 ,sum(nvl(ENB_EPS_BEAR_REL_REQ_T_QCI1,0)) as M8006C152 ,sum(nvl(ERAB_REL_ENB_ACT_QCI1,0)) as M8006C176 ,sum(nvl(ERAB_REL_ENB_ACT_QCI2,0)) as M8006C177 ,sum(nvl(ERAB_INI_SETUP_ATT_QCI1,0)) as M8006C188 ,sum(nvl(ERAB_INI_SETUP_ATT_QCI2,0)) as M8006C189 ,sum(nvl(ERAB_INI_SETUP_ATT_QCI5,0)) as M8006C192 ,sum(nvl(ERAB_INI_SETUP_ATT_QCI9,0)) as M8006C196 ,sum(nvl(ERAB_ADD_SETUP_ATT_QCI1,0)) as M8006C197 ,sum(nvl(ERAB_ADD_SETUP_ATT_QCI2,0)) as M8006C198 ,sum(nvl(ERAB_ADD_SETUP_ATT_QCI5,0)) as M8006C201 ,sum(nvl(ERAB_ADD_SETUP_ATT_QCI9,0)) as M8006C205 ,sum(nvl(ERAB_INI_SETUP_SUCC_QCI1,0)) as M8006C206 ,sum(nvl(ERAB_INI_SETUP_SUCC_QCI2,0)) as M8006C207 ,sum(nvl(ERAB_INI_SETUP_SUCC_QCI5,0)) as M8006C210 ,sum(nvl(ERAB_INI_SETUP_SUCC_QCI9,0)) as M8006C214 ,sum(nvl(ERAB_ADD_SETUP_SUCC_QCI1,0)) as M8006C215 ,sum(nvl(ERAB_ADD_SETUP_SUCC_QCI2,0)) as M8006C216 ,sum(nvl(ERAB_ADD_SETUP_SUCC_QCI5,0)) as M8006C219 ,sum(nvl(ERAB_ADD_SETUP_SUCC_QCI9,0)) as M8006C223

,decode(SUM(ERAB_INI_SETUP_ATT_QCI1+ERAB_ADD_SETUP_ATT_QCI1),0,0 ,SUM(ERAB_REL_ENB_ACT_QCI1+ENB_EPS_BEAR_REL_REQ_O_QCI1+ENB_EPS_BEAR_REL_REQ_T_QCI1)/SUM(ERAB_INI_SETUP_ATT_QCI1+ERAB_ADD_SETUP_ATT_QCI1)) as EU0205 from

NOKLTE_PS_LEPSB_lncel_hour PMRAW where

period_start_time between to_date(&1,'yyyymmddhh24') and to_date(&2,'yyyymmddhh24') ---- to_char(period_start_time,'yyyymmddHH24') >= to_char(SYSDATE-1,'yyyymmddHH24') -- and to_char(period_start_time,'yyyymmddHH24') <= to_char(SYSDATE-1,'yyyymmddHH24') --AND PERIOD_DURATION=15 group by

to_char(period_start_time,'yyyymmddhh24'),LNCEL_ID,to_char(period_start_time,'yyyymmddhh24')||LNCEL_ID )M8006,(

select

to_char(period_start_time,'yyyymmddhh24') sdatetime ,LNCEL_ID

,to_char(period_start_time,'yyyymmddhh24')||LNCEL_ID cel_key_id ,SUM(IP_TPUT_VOL_UL_QCI_1)/8/1000 as EU0507

,SUM(IP_TPUT_VOL_DL_QCI_1)/8/1000 as EU0508 ,SUM(IP_TPUT_VOL_UL_QCI_2)/8/1000 as EU0509 ,SUM(IP_TPUT_VOL_DL_QCI_2)/8/1000 as EU0510 ,SUM(IP_TPUT_VOL_UL_QCI_5)/8/1000 as EU0515 ,SUM(IP_TPUT_VOL_DL_QCI_5)/8/1000 as EU0516

,sum(nvl(PDCP_DATA_RATE_MEAN_UL_QCI_1,0)) as M8012C116 ,sum(nvl(PDCP_DATA_RATE_MEAN_DL_QCI_1,0)) as M8012C143 from

NOKLTE_PS_LCELLT_lncel_hour PMRAW where

period_start_time between to_date(&1,'yyyymmddhh24') and to_date(&2,'yyyymmddhh24') --- to_char(period_start_time,'yyyymmddHH24') >= to_char(SYSDATE-1,'yyyymmddHH24') -- and to_char(period_start_time,'yyyymmddHH24') <= to_char(SYSDATE-1,'yyyymmddHH24') --AND PERIOD_DURATION=15 group by

to_char(period_start_time,'yyyymmddhh24'),LNCEL_ID,to_char(period_start_time,'yyyymmddhh24')||LNCEL_ID )M8012 ,(

select

to_char(period_start_time,'yyyymmddhh24') sdatetime ,LNCEL_ID

,to_char(period_start_time,'yyyymmddhh24')||LNCEL_ID cel_key_id ,sum(nvl(SIGN_CONN_ESTAB_COMP,0)) as M8013C5 ,sum(nvl(SIGN_CONN_ESTAB_ATT_MO_S,0)) as M8013C17 ,sum(nvl(SIGN_CONN_ESTAB_ATT_MT,0)) as M8013C18 ,sum(nvl(SIGN_CONN_ESTAB_ATT_MO_D,0)) as M8013C19 ,sum(nvl(SIGN_CONN_ESTAB_ATT_EMG,0)) as M8013C21 ,sum(nvl(SIGN_CONN_ESTAB_COMP_EMG,0)) as M8013C26 ,sum(nvl(SIGN_CONN_ESTAB_ATT_HIPRIO,0)) as M8013C31 ,sum(nvl(SIGN_CONN_ESTAB_ATT_DEL_TOL,0)) as M8013C34 ,lnbts.co_object_instance enb_id ,lncel.co_object_instance cell_id ,ip.ipno_mpia_8 bts_ip

,lnbts.co_sys_version bts_version ,Trim(lnbts.co_name) bts_name ,Trim(lncel.co_name) cel_name from

NOKLTE_PS_LUEST_lncel_hour PMRAW,ctp_common_objects lnbts,ctp_common_objects lncel,c_lte_ipno ip where

period_start_time between to_date(&1,'yyyymmddhh24') and to_date(&2,'yyyymmddhh24')

--- to_char(period_start_time,'yyyymmddHH24') >= to_char(SYSDATE-1,'yyyymmddHH24')

-- and to_char(period_start_time,'yyyymmddHH24') <= to_char(SYSDATE-1,'yyyymmddHH24') AND PMRAW.LNCEL_ID=lncel.co_gid and PMRAW.lnbts_id=lnbts.co_gid

and ip.ipno_bts_id=lnbts.co_object_instance --AND PERIOD_DURATION=15 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,ip.ipno_mpia_8,lnbts.co_sys_version,Trim(lnbts.co_name),Trim(lncel.co_name) ) M8013 ,( select

to_char(period_start_time,'yyyymmddhh24') sdatetime ,LNCEL_ID

,to_char(period_start_time,'yyyymmddhh24')||LNCEL_ID cel_key_id ,sum(nvl(PDCP_SDU_LOSS_UL_QCI_1_FNA,0)) as M8026C255 ,sum(nvl(PDCP_SDU_LOSS_DL_QCI_1_FNA,0)) as M8026C260 ,sum(nvl(PDCP_SDU_LOSS_UL_QCI_2_FNA,0)) as M8026C256 ,sum(nvl(PDCP_SDU_LOSS_DL_QCI_2_FNA,0)) as M8026C261

from

NOKLTE_PS_LQOS_LNCEL_hour PMRAW where

select

to_char(period_start_time,'yyyymmddhh24') sdatetime ,LNCEL_ID

,to_char(period_start_time,'yyyymmddhh24')||LNCEL_ID cel_key_id ,sum(nvl(ISYS_HO_GERAN_SRVCC_SUCC,0)) as M8016C34 ,sum(nvl(ISYS_HO_GERAN_SRVCC_ATT,0)) as M8016C33

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,'yyyymmddHH24') >= to_char(SYSDATE-1,'yyyymmddHH24')

-- and to_char(period_start_time,'yyyymmddHH24') <= to_char(SYSDATE-1,'yyyymmddHH24') --AND PERIOD_DURATION=15 group by

to_char(period_start_time,'yyyymmddhh24'),LNCEL_ID,to_char(period_start_time,'yyyymmddhh24')||LNCEL_ID )M8016 ,(

select

to_char(period_start_time,'yyyymmddhh24') sdatetime ,LNCEL_ID

,to_char(period_start_time,'yyyymmddhh24')||LNCEL_ID cel_key_id ,sum(nvl(PRB_USED_PUSCH,0)) as M8011C50 ,sum(nvl(PRB_USED_PDSCH,0)) as M8011C54 from

NOKLTE_PS_LCELLR_lncel_hour PMRAW where

to_char(period_start_time,'yyyymmddhh24'),LNCEL_ID,to_char(period_start_time,'yyyymmddhh24')||LNCEL_ID )M8011 ,(

select

to_char(period_start_time,'yyyymmddhh24') sdatetime ,LNCEL_ID ,to_char(period_start_time,'yyyymmddhh24')||LNCEL_ID cel_key_id ,avg(nvl(ERAB_SETUP_TIME_MEAN,0)) as M8007C14

from

NOKLTE_PS_LRDB_MNC1_RAW where

period_start_time between to_date(&1,'yyyymmddhh24') and to_date(&2,'yyyymmddhh24') --- to_char(period_start_time,'yyyymmddHH24') >= to_char(SYSDATE-1,'yyyymmddHH24')

-- and to_char(period_start_time,'yyyymmddHH24') <= to_char(SYSDATE-1,'yyyymmddHH24') --AND PERIOD_DURATION=15 group by

to_char(period_start_time,'yyyymmddhh24'),LNCEL_ID,to_char(period_start_time,'yyyymmddhh24')||LNCEL_ID )M8007 ,(

select

to_char(period_start_time,'yyyymmddhh24') sdatetime ,LNCEL_ID

,to_char(period_start_time,'yyyymmddhh24')||LNCEL_ID cel_key_id ,avg(nvl(RRC_CON_STP_TIM_MEAN,0)) as M8008C17

from

NOKLTE_PS_LRRC_MNC1_RAW where

period_start_time between to_date(&1,'yyyymmddhh24') and to_date(&2,'yyyymmddhh24') --- to_char(period_start_time,'yyyymmddHH24') >= to_char(SYSDATE-1,'yyyymmddHH24')

-- and to_char(period_start_time,'yyyymmddHH24') <= to_char(SYSDATE-1,'yyyymmddHH24') --AND PERIOD_DURATION=15 group by

to_char(period_start_time,'yyyymmddhh24'),LNCEL_ID,to_char(period_start_time,'yyyymmddhh24')||LNCEL_ID )M8008

WHERE M8013.cel_key_id=m8001.cel_key_id(+) AND M8013.cel_key_id=m8006.cel_key_id(+) AND M8013.cel_key_id=m8007.cel_key_id(+) AND M8013.cel_key_id=m8008.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=m8016.cel_key_id(+) AND M8013.cel_key_id=m8026.cel_key_id(+) )

WHERE enb_id=618679 --enb_id between 620416 and 620608) --(bts.co_object_instance between '618496' and '619375')or(bts.co_object_instance between '620416' and '620608')

GROUP BY enb_cell,enb_id,cell_id,bts_ip,bts_version,bts_name,cel_name,sdatetime

5. –15分组粒度全网指标查询

SELECT

enb_cell ,sdatetime ,enb_id ,cell_id

,bts_ip

,bts_version ,bts_name ,cel_name

,sum(M8013C17+M8013C18+M8013C19+M8013C20+M8013C21+M8013C31+M8013C34) RRC连接建立请求次数

,sum(M8013C5) RRC连接建立成功次数 ,sum(M8006C0) ERAB建立请求数 ,sum(M8006C1) ERAB建立成功数 ,sum(M8013C15）无线掉线次数1 ,sum(M8013C16) 无线掉线次数2 ,sum(M8006C12) ERAB掉线次数1 ,sum(M8006C13) ERAB掉线次数2 ,sum(M8006C14) ERAB掉线次数3

,Round(Decode(sum(M8013C17+M8013C18+M8013C19+M8013C20+ M8013C21),0,0, 100*sum(M8013C5)/sum(M8013C17+M8013C18+M8013C19+M8013C20+ M8013C21)),2) RRC连接建立成功率 --集团考核指标,和nokia官方公式一致,LTE_5218a ,Round(Decode(sum(M8006C0),0,0,100*sum(M8006C1)/sum(M8006C0)),2) ERAB建立成功率

,Round(100*decode( sum(M8006C6 + M8006C7 + M8006C8 + M8006C9 + M8006C15 + M8006C10 +

M8006C12 + M8006C14 + M8006C13),0,null, sum(M8006C12 + M8006C14 + M8006C13) / sum(M8006C6 + M8006C7 + M8006C8 + M8006C9 + M8006C15 + M8006C10 + M8006C12 + M8006C14 + M8006C13)),2) ERAB掉线率 ,Round(Decode(sum(M8013C17+M8013C18+M8013C19+M8013C20+M8013C21)*sum(M8006C0),0,0,

100*(sum(M8013C5)/sum(M8013C17+M8013C18+M8013C19+M8013C20+M8013C21))*(sum(M8006C1)/sum(M8006C0))),2) 无线接通率 --集团考核指标,nokia官方没有这个公式,为中国区自己匹配,nokia最近似的标准公式是LTE_5060e：RRC成功率*initial_Erab成功率

,Round(Decode(sum(M8006C35+M8006C36+M8006C168+M8006C169+M8006C170),0,0,

100*sum(M8013C15+M8013C16)/sum(M8006C35+M8006C36+M8006C168+M8006C169+M8006C170)),2) 无线掉线率1 --集团考核指标,常用公式,nokia内部没有这个公式,分母不包括:last(roundup(M8001C318/M8001C319)),因为不合理

,Round(decode(sum(M8006C35+M8006C36+M8006C168+M8006C169+M8006C170+M8001C223),0,0,

100*sum(M8013C15+M8013C16)/sum(M8006C35+M8006C36+M8006C168+M8006C169+M8006C170+M8001C223)),2) 无线掉线率2 --集团考核指标,修正后的报集团公式,nokia官方没有这个公式,分母用M8001C223替代了last(roundup(M8001C318/M8001C319))

,Round(decode(sum(M8006C35+M8006C36+M8006C168+M8006C169+M8006C170+M8001C223),0,0, 100*sum(M8006C176+M8006C177+M8006C178+M8006C179+M8006C180+M8013C16)

/sum(M8006C35+M8006C36+M8006C168+M8006C169+M8006C170+M8001C223)),2) 无线掉线率3 ,Round(Decode(sum(M8009C6+M8014C6+M8014C18),0,0,

100*sum(M8009C7+M8014C7+M8014C19)/sum(M8009C6+M8014C6+M8014C18)),2) 切换成功率1 --集团考核指标,修正后的正确公式,修正了集团公式的分母,inter eNB S1用M8014C18替换M8014C14,nokia官方没有这个公式

,Round(Decode(sum(M8009C6+M8014C0+M8014C14),0,0,

100*sum(M8009C7+M8014C7+M8014C19)/sum(M8009C6+M8014C0+M8014C14)),2) 切换成功率2_集团公式 --集团考核指标,报给集团的公式,该公式被亿阳网管使用。该公式存在两个错误,X2中使用了错误的分母:M8014C0,该值为切换准备次数,S1中使用了错误的分母M8014C14,nokia官方没有这个公式

,Round(avg(M8001C199),1) RRC连接平均数 ,max(M8001C200) RRC连接最大数

,Round(Decode(sum(M8001C217),0,0,sum(M8011C50/(15*60*1000*1/(1+4)))/sum(M8001C217)*100),2) 上行业务PRB占用率

,Round(Decode(sum(M8001C216),0,0,max(M8011C54-438800)/(15*60*1000*4/(1+4))/sum(M8001C216)*100),2) 下行业务PRB占用率

FROM (

SELECT M8013.sdatetime

-- ,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,M8001C223,M8001C254,M8001C259,M8001C318,M8001C319,M8001C269,M8001C217,M8001C216,M8001C147,M8001C150,M8001C148,M8001C151 ,M8006C0,M8006C1,M8006C6,M8006C7,M8006C8,M8006C9,M8006C10,M8006C12,M8006C13,M8006C14,M8006C15,M8006C17,M8006C18,M8006C26,M8006C35,M8006C36,M8006C44,M8006C89,M8006C98

,M8006C45,M8006C46,M8006C47,M8006C48,M8006C49,M8006C50,M8006C51,M8006C52,M8006C53,M8006C54 ,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,M8011C37,M8011C24,M8011C50avg,M8011C54avg,M8011C47,M8011C51

,M8011C38,M8011C39,M8011C40,M8011C41,M8011C42

,M8012C19,M8012C20,M8012C92,M8012C94,M8012C96,M8012C98,M8012C100,M8012C102,M8012C104,M8012C106,M8012C108,M8012C118,M8012C120,M8012C122,M8012C124,M8012C126,M8012C128

,M8012C130,M8012C132,M8012C134

,M8013C5,M8013C9,M8013C10,M8013C11,M8013C12,M8013C13,M8013C15,M8013C16,M801

3C17,M8013C18,M8013C19,M8013C20,M8013C21,M8013C31,M8013C34

,M8014C0,M8014C6,M8014C7,M8014C8,M8014C14,M8014C18,M8014C19,M8014C20 ,M8015C2,M8015C9,M8015C8,M8015C5,M8015C6,M8015C7

,M8016C11,M8016C14,M8016C21,M8016C23,M8016C25,M8016C26,M8016C27,M8016C29,M8016C30,M8020C3,M8020C6,M8020C4 FROM ( select

to_char(period_start_time,'yyyymmddHH24mi') sdatetime ,LNCEL_ID

,to_char(period_start_time,'yyyymmddHH24mi')||LNCEL_ID cel_key_id ,sum(PDCP_SDU_UL) M8001C153 ,sum(PDCP_SDU_DL) M8001C154

,avg(nvl(RRC_CONN_UE_AVG,0)) M8001C199 --The average number of UEs in RRC_CONNECTED state over the measurement period. The average value is the arithmetical average of samples taken from the number of UEs in RRC_CONNECTED state.

,max(nvl(RRC_CONN_UE_MAX,0)) M8001C200 --The highest value for number of UEs in RRC_CONNECTED state over the measurement period.

,avg(nvl(CELL_LOAD_ACT_UE_AVG,0)) M8001C223 --The average number of active UE per cell during measurement period. A UE is termed active if at least a single non-GBR DRB has been successfully configured for it.

,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(SUM_RRC_CONN_UE) M8001C318 ,sum(DENOM_RRC_CONN_UE) M8001C319 ,avg(nvl(PDCP_RET_DL_DEL_MEAN_QCI_1,0)) M8001C269 ,avg(nvl(MEAN_PRB_AVAIL_PUSCH,0)) M8001C217 ,avg(nvl(MEAN_PRB_AVAIL_PDSCH,0)) M8001C216 ,avg(nvl(DL_UE_DATA_BUFF_AVG,0)) M8001C147 ,avg(nvl(UL_UE_DATA_BUFF_AVG,0)) M8001C150 ,max(nvl(DL_UE_DATA_BUFF_MAX,0)) M8001C148 ,max(nvl(UL_UE_DATA_BUFF_MAX,0)) M8001C151

FROM NOKLTE_PS_LCELLD_MNC1_RAW PMRAW where

---to_char(period_start_time,'yyyymmddHH24mi') >= to_char(SYSDATE-1,'yyyymmddHH24mi') period_start_time between to_date(&1,'yyyymmddHH24mi') and to_date(&2,'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')||LNC

EL_ID )M8001 ,(

select

to_char(period_start_time,'yyyymmddHH24mi') sdatetime ,LNCEL_ID

,to_char(period_start_time,'yyyymmddHH24mi')||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(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(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.

,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(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 additional EPS bearer setup attempts for GBR DRBs of QCI4 characteristics.

,sum(nvl(EPS_BEARER_STP_COM_INI_QCI_2,0)) M8006C168 --This measurement provides the number of initial EPS bearer setup completions for GBR DRBs of QCI2 characteristics.

,sum(nvl(EPS_BEARER_STP_COM_INI_QCI_3,0)) M8006C169 --This measurement provides the number of initial EPS bearer setup completions for GBR DRBs of QCI3 characteristics.

,sum(nvl(EPS_BEARER_STP_COM_INI_QCI_4,0)) M8006C170 --This measurement provides the number of initial EPS bearer setup completions for GBR DRBs of QCI4 characteristics.

,sum(nvl(EPS_BEARER_STP_COM_ADD_QCI_2,0)) M8006C171 --This measurement provides the number of additional EPS bearer setup completions for GBR DRBs of QCI2 characteristics.

,sum(nvl(EPS_BEARER_STP_COM_ADD_QCI_3,0)) M8006C172 --This measurement provides the number of additional EPS bearer setup completions for GBR DRBs of QCI3 characteristics. ,sum(nvl(EPS_BEARER_STP_COM_ADD_QCI_4,0)) M8006C173 --This measurement provides the number of additional EPS bearer setup completions for GBR DRBs of QCI4 characteristics. ,sum(nvl(PRE_EMPT_GBR_BEARER,0)) M8006C174 --This measurement provides the number of GBR E-RABs (Guaranteed Bit Rate bearers, 3GPP TS 23.203) being released due to lack of radio resources. ,sum(nvl(PRE_EMPT_NON_GBR_BEARER,0)) M8006C175 --This measurement provides the number of non-GBR E-RABs (non-Guaranteed Bit Rate bearers, 3GPP TS 23.203) being released due to lack of radio resources.

,sum(nvl(ERAB_IN_SESSION_TIME_QCI2,0)) M8006C182 --This measurement provides the aggregated in-session activity time in seconds for all E-RABs with QCI2 characteristics. The E-RAB is said to be in session if any user data has been transferred in UL or DL direction within the last 100msec.

any user data has been transferred in UL or DL direction within the last 100msec.

,sum(nvl(ERAB_IN_SESSION_TIME_NON_GBR,0)) M8006C185 --This measurement provides the aggregated in-session activity time in seconds for all E-RABs with non-GBR (QCI5..9) characteristics. The E-RAB is said to be in session if any user data has been transferred in UL or DL direction within the last 100msec. ,sum(nvl(SUM_SIMUL_ERAB_QCI_1,0)) M8006C45 ,sum(nvl(SUM_SIMUL_ERAB_QCI_2,0)) M8006C46 ,sum(nvl(SUM_SIMUL_ERAB_QCI_3,0)) M8006C47 ,sum(nvl(SUM_SIMUL_ERAB_QCI_4,0)) M8006C48 ,sum(nvl(SUM_SIMUL_ERAB_QCI_5,0)) M8006C49 ,sum(nvl(SUM_SIMUL_ERAB_QCI_6,0)) M8006C50 ,sum(nvl(SUM_SIMUL_ERAB_QCI_7,0)) M8006C51 ,sum(nvl(SUM_SIMUL_ERAB_QCI_8,0)) M8006C52 ,sum(nvl(SUM_SIMUL_ERAB_QCI_9,0)) M8006C53 ,sum(nvl(DENOM_SUM_SIMUL_ERAB,0)) M8006C54 from

NOKLTE_PS_LEPSB_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 )M8006,( select

to_char(period_start_time,'yyyymmddHH24mi') sdatetime ,LNCEL_ID ,to_char(period_start_time,'yyyymmddHH24mi')||LNCEL_ID cel_key_id ,sum(nvl(REJ_RRC_CONN_RE_ESTAB,0)) M8008C0 --The number of rejected RRC Connection re-establishments.

,sum(nvl(RRC_CON_RE_ESTAB_SUCC,0)) M8008C5 --The number of successful RRC Connection Re-establishment procedures.

,sum(nvl(RRC_CON_RE_ESTAB_ATT_HO_FAIL,0)) M8008C6 --The number of RRC Connection Re-establishment attempts per cause (Handover Failure).

,sum(nvl(RRC_CON_RE_ESTAB_SUCC_HO_FAIL,0)) M8008C7 --The number of successful RRC Connection Re-establishment procedures per cause (Handover Failure).

,sum(nvl(RRC_CON_RE_ESTAB_ATT_OTHER,0)) M8008C8 --The number of RRC Connection Re-establishment attempts per cause (Other failure).

,sum(nvl(RRC_CON_RE_ESTAB_SUCC_OTHER,0)) M8008C9 --The number of successful RRC Connection Re-establishment procedures per cause (Other Failure).

,sum(nvl(REPORT_CGI_REQ,0)) M8008C10 --This counter provides the total number of attempts to retrieve the CGI of a neighbor cell from UE.

,sum(nvl(SUCC_CGI_REPORTS,0)) M8008C11 --This counter provides the number of CGI measurement reports received from UE. from

NOKLTE_PS_LRRC_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 )M8008,( select

to_char(period_start_time,'yyyymmddHH24mi') sdatetime ,LNCEL_ID

,to_char(period_start_time,'yyyymmddHH24mi')||LNCEL_ID cel_key_id ,sum(nvl(TOT_NOT_START_HO_PREP,0)) M8009C0 --The number of not started Handover preparations. The RRM receives an RRC Measurement Report (UE -; eNB), but the RRM decides not to start a Handover preparation phase. No target cell list will be handed over to the mobility management (MM) unit. ,sum(nvl(TOT_HO_DECISION,0)) M8009C1 --The number of positive Handover decisions. In case of a positive Handover decision, the RRM transmits a target cell list to the mobility management (MM) unit. ,sum(nvl(INTRA_ENB_HO_PREP,0)) M8009C2 --The number of Intra-eNB Handover preparations. ,sum(nvl(FAIL_ENB_HO_PREP_AC,0)) M8009C3 --The number of failed Intra-eNB Handover preparations due to Admission Control. Includes failures to set up data forwarding in the target cell. ,sum(nvl(FAIL_ENB_HO_PREP_OTH,0)) M8009C5 --The number of failed Intra-eNB Handover preparations due to other reasons. ,sum(nvl(ATT_INTRA_ENB_HO,0)) M8009C6 --The number of Intra-eNB Handover attempts. ,sum(nvl(SUCC_INTRA_ENB_HO,0)) M8009C7 --The number of successful Intra-eNB Handover completions. ,sum(nvl(ENB_INTRA_HO_FAIL,0)) M8009C8 --The number of Intra-eNB Handover failures due to the guarding timer THOoverall.

,sum(nvl(ENB_HO_DROP_RLFAIL,0)) M8009C12 --The number of Intra-eNB Handover drops due to Radio Link Failure.

,sum(nvl(ENB_HO_DROP_OTHERFAIL,0)) M8009C13 --The number of Intra-eNB Handover drops due to other failures.

from

NOKLTE_PS_LIANBHO_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 )M8009,( select

to_char(period_start_time,'yyyymmddHH24mi') sdatetime ,LNCEL_ID

,to_char(period_start_time,'yyyymmddHH24mi')||LNCEL_ID cel_key_id ,sum(nvl(PRB_USED_PUSCH,0)) M8011C50--Total number of used PRB's for PUSCH scheduling over the measurement period.

,sum(nvl(PRB_USED_PDSCH,0)) M8011C54 --Total number of used PRB's for PDSCH scheduling over the measurement period.

,sum(nvl(UL_PRB_UTIL_TTI_LEVEL_1, 0)) M8011C12 --The Physical Resource Block (PRB) utilization per TTI. The utilization is defined by the rate of used PRB per TTI. Utilization in the range of <= 10% is updated to this counter.

,sum(nvl(UL_PRB_UTIL_TTI_LEVEL_2, 0)) M8011C13 --The Physical Resource Block (PRB) utilization per TTI. The utilization is defined by the rate of used PRB per TTI. Utilization in the range of 10% < PRBUTIL < = 20% is updated to this counter.

,sum(nvl(UL_PRB_UTIL_TTI_LEVEL_3, 0)) M8011C14 --The Physical Resource Block (PRB) utilization per TTI. The utilization is defined by the rate of used PRB per TTI. Utilization in the range of 20% < PRBUTIL < = 30% is updated to this counter.

,sum(nvl(UL_PRB_UTIL_TTI_LEVEL_4, 0)) M8011C15 --The Physical Resource Block (PRB) utilization per TTI. The utilization is defined by the rate of used PRB per TTI. Utilization in the range of 30% < PRBUTIL < = 40% is updated to this counter.

,sum(nvl(UL_PRB_UTIL_TTI_LEVEL_5, 0)) M8011C16 --The Physical Resource Block (PRB) utilization per TTI. The utilization is defined by the rate of used PRB per TTI. Utilization in the range of 40% < PRBUTIL < = 50% is updated to this counter. ,sum(nvl(UL_PRB_UTIL_TTI_LEVEL_6, 0)) M8011C17 --The Physical Resource Block (PRB) utilization per TTI. The utilization is defined by the rate of used PRB per TTI. Utilization in the range of 50% < PRBUTIL < = 60% is updated to this counter. ,sum(nvl(UL_PRB_UTIL_TTI_LEVEL_7, 0)) M8011C18 --The Physical Resource Block (PRB) utilization per TTI. The utilization is defined by the rate of used PRB per TTI. Utilization in the range of 60% < PRBUTIL < = 70% is updated to this counter. ,sum(nvl(UL_PRB_UTIL_TTI_LEVEL_8, 0)) M8011C19 --The Physical Resource Block (PRB) utilization per TTI. The utilization is defined by the rate of used PRB per TTI. Utilization in the range of 70% < PRBUTIL < = 80% is updated to this counter.

,sum(nvl(UL_PRB_UTIL_TTI_LEVEL_9, 0)) M8011C20 --The Physical Resource Block (PRB) utilization per TTI. The utilization is defined by the rate of used PRB per TTI. Utilization in the range of 80% < PRBUTIL < = 90% is updated to this counter.

,sum(nvl(UL_PRB_UTIL_TTI_LEVEL_10, 0)) M8011C21 --The Physical Resource Block (PRB) utilization per TTI. The utilization is defined by the rate of used PRB per TTI. Utilization in the range of 90% < PRBUTIL < = 100% is updated to this counter.

,min(nvl(UL_PRB_UTIL_TTI_MIN, 0)) M8011C22 --The minimum value of the UL Physical Resource Block (PRB) utilization per TTI. The utilization is defined by the rate of used PRB per TTI.

,max(nvl(UL_PRB_UTIL_TTI_MAX, 0)) M8011C23 --The maximum value of the UL Physical Resource Block (PRB) utilization per TTI. The utilization is defined by the rate of used PRB per TTI.

,avg(nvl(UL_PRB_UTIL_TTI_MEAN, 0)) M8011C24 --The mean value of the UL Physical Resource Block (PRB) use per TTI. The use is defined by the rate of used PRB per TTI.
The reported value is 10 times higher than the actual value (for example 5.4 is stored as 54).

,sum(nvl(DL_PRB_UTIL_TTI_LEVEL_1, 0)) M8011C25 --The Physical Resource Block (PRB) utilization per TTI. The utilization is defined by the rate of used PRB per TTI. Utilization in the range of <= 10% is updated to this counter.

,sum(nvl(DL_PRB_UTIL_TTI_LEVEL_2, 0)) M8011C26 --The Physical Resource Block (PRB) utilization per TTI. The utilization is defined by the rate of used PRB per TTI. Utilization in the range of 10% < PRBUTIL < = 20% is updated to this counter.

,sum(nvl(DL_PRB_UTIL_TTI_LEVEL_3, 0)) M8011C27 --The Physical Resource Block (PRB) utilization per TTI. The utilization is defined by the rate of used PRB per TTI. Utilization in the range of 20% < PRBUTIL < = 30% is updated to this counter.

,sum(nvl(DL_PRB_UTIL_TTI_LEVEL_4, 0)) M8011C28 --The Physical Resource Block (PRB) utilization per TTI. The utilisation is defined by the rate of used PRB per TTI. Utilization in the range of 30% < PRBUTIL < = 40% is updated to this counter.

,sum(nvl(DL_PRB_UTIL_TTI_LEVEL_5, 0)) M8011C29 --The Physical Resource Block (PRB) utilization per TTI. The utilization is defined by the rate of used PRB per TTI. Utilization in the range of 40% < PRBUTIL < = 50% is updated to this counter.

,sum(nvl(DL_PRB_UTIL_TTI_LEVEL_6, 0)) M8011C30 --The Physical Resource Block (PRB) utilization per TTI. The utilization is defined by the rate of used PRB per TTI. Utilization in the range of 50% < PRBUTIL < = 60% is updated to this counter.

,sum(nvl(DL_PRB_UTIL_TTI_LEVEL_7, 0)) M8011C31 --The Physical Resource Block (PRB) utilization per TTI. The utilization is defined by the rate of used PRB per TTI. Utilization in the range of 60% < PRBUTIL < = 70% is updated to this counter.

,sum(nvl(DL_PRB_UTIL_TTI_LEVEL_8, 0)) M8011C32 --The Physical Resource Block (PRB) utilization per TTI. The utilization is defined by the rate of used PRB per TTI. Utilization in the range of 70% < PRBUTIL < = 80% is updated to this counter. ,sum(nvl(DL_PRB_UTIL_TTI_LEVEL_9, 0)) M8011C33 --The Physical Resource Block (PRB) utilization per TTI. The utilization is defined by the rate of used PRB per TTI. Utilization in the range of 80% < PRBUTIL < = 90% is updated to this counter. ,sum(nvl(DL_PRB_UTIL_TTI_LEVEL_10, 0)) M8011C34 --The Physical Resource Block (PRB) utilization per TTI. The utilization is defined by the rate of used PRB per TTI. Utilization in the range of 90% < PRBUTIL < = 100% is updated to this counter. ,min(nvl(DL_PRB_UTIL_TTI_MIN, 0)) M8011C35 --The minimum value of the DL Physical Resource Block (PRB) utilization per TTI. The utilization is defined by the rate of used PRB per TTI. ,max(nvl(DL_PRB_UTIL_TTI_MAX, 0)) M8011C36 --The maximum value of the DL Physical Resource Block (PRB) utilization per TTI. The utilization is defined by the rate of used PRB per TTI.

,avg(nvl(DL_PRB_UTIL_TTI_MEAN, 0)) M8011C37 --The mean value of the DL Physical Resource Block (PRB) use per TTI. The use is defined by the rate of used PRB per TTI.
The reported value is 10 times higher than the actual value (for example 5.4 is stored as 54).

,sum(nvl(CCE_AVAIL_ACT_TTI, 0)) M8011C38 --Total number of CCEs available for active TTIs when at least one PDCCH is going to be scheduled.

,sum(nvl(AGG1_USED_PDCCH, 0)) M8011C39 --Total number of AGG1 used for PDCCH scheduling over the measurement period.

,sum(nvl(AGG2_USED_PDCCH, 0)) M8011C40 --Total number of AGG2 used for PDCCH scheduling over the measurement period.

,sum(nvl(AGG4_USED_PDCCH, 0)) M8011C41 --Total number of AGG4 used for PDCCH scheduling over the measurement period.

,sum(nvl(AGG8_USED_PDCCH, 0)) M8011C42 --Total number of AGG8 used for PDCCH scheduling over the measurement period.

,sum(nvl(AGG1_BLOCKED_PDCCH, 0)) M8011C43 --Total number of AGG1 blocked for PDCCH scheduling over the measurement period.

,sum(nvl(AGG2_BLOCKED_PDCCH, 0)) M8011C44 --Total number of AGG2 blocked for PDCCH scheduling over the measurement period.

,sum(nvl(AGG4_BLOCKED_PDCCH, 0)) M8011C45 --Total number of AGG4 blocked for PDCCH scheduling over the measurement period.

,sum(nvl(AGG8_BLOCKED_PDCCH, 0)) M8011C46 --The total number of AGG8 blocked for PDCCH scheduling over the measurement period.

,sum(nvl(PRB_USED_UL_TOTAL, 0)) M8011C47 --Total number of PRBs used for UL transmissions on PUSCH, PUCCH and PRACH over the measurement period is updated to this counter. A PRB covers the pair of resource blocks of two consecutive slots in a subframe.

,sum(nvl(PRB_USED_PUCCH, 0)) M8011C49 --Total number of PRBs used for PUCCH over the measurement period is updated to this counter. A PRB covers the pair of resource blocks of two consecutive slots in a subframe.

,avg(nvl(PRB_USED_PUSCH, 0)) M8011C50avg --Total number of PRBs used for UL transmissions on PUSCH over the measurement period is updated to this counter. A PRB covers the pair of resource blocks of two consecutive slots in a subframe.

,sum(nvl(PRB_USED_DL_TOTAL, 0)) M8011C51 --Total number of PRBs used for DL transmissions over the measurement period is updated to this counter. A PRB covers the pair of resource blocks of two consecutive slots in a subframe.

,avg(nvl(PRB_USED_PDSCH, 0)) M8011C54avg --Total number of PRBs used for PDSCH over the measurement period is updated to this counter. A PRB covers the pair of resource blocks of two consecutive slots in a subframe.

,avg(nvl(UE_PER_UL_TTI_AVG, 0)) M8011C55 --Average value of UEs served per TTI on uplink during a measurement period. Counter unit is 0.1 UE. For example, counter value 100 represents ten UEs. ,max(nvl(UE_PER_UL_TTI_MAX, 0)) M8011C56 --Maximum value of UEs served per TTI on uplink during a measurement period.

,avg(nvl(UE_PER_DL_TTI_AVG, 0)) M8011C57 --Average value of UEs served per TTI on downlink during a measurement period. Counter unit is 0.1 UE. For example, counter value 100 represents ten UEs. ,max(nvl(UE_PER_DL_TTI_MAX, 0)) M8011C58 --Maximum value of UEs served per TTI on downlink during a measurement period.

,sum(nvl(PDCCH_1_OFDM_SYMBOL, 0)) M8011C59 --This counter provides the number of subframes when only 1 OFDM symbol is allocated to the PDCCH (3GPP TS 36.211, 36.300).

,sum(nvl(PDCCH_2_OFDM_SYMBOLS, 0)) M8011C60 --This counter provides the number of subframes when 2 OFDM symbols are allocated to the PDCCH (3GPP TS 36.211, 36.300).

,sum(nvl(PDCCH_3_OFDM_SYMBOLS, 0)) M8011C61 --This counter provides the number of subframes when 3 OFDM symbols are allocated to the PDCCH (3GPP TS 36.211, 36.300).

,avg(nvl(TTI_BUNDLING_MODE_UE_AVG, 0)) M8011C62 --The measurement provides the average

number of UEs in TTI Bundling mode per sample.聽Each instance of a UE entering TTI Bundling Mode during the measurement interval is included in the average. Thus if, for example, a single UE is placed in TTI Bundling mode three times during the measurement interval, all of the three instances will be included in the average.
The reported value is 100 times higher than the actual value (for example 1.00 is stored as 100).

,sum(nvl(TTI_BUNDL_RETENTION_SHORT, 0)) M8011C64 --The measurement represents the number of UEs that are in TTI Bundling mode for only a short period of time, which is defined as less than t1 seconds, that is retention time in TTI Bundling mode < t1 sec.

,sum(nvl(TTI_BUNDL_RETENTION_MEDIUM, 0)) M8011C65 --The measurement represents the number of UEs that are in TTI Bundling mode for a medium period of time. This time period is defined by a lower bound of t1 seconds and an upper bound of t2 seconds, that is retention time in TTI Bundling mode: t1 <= x <= t2 sec. ,sum(nvl(TTI_BUNDL_RETENTION_LONG, 0)) M8011C66 --The measurement represents the number of UEs that are in TTI Bundling mode for a long period of time, which is defined as longer than t2 seconds, that is retention time in TTI Bundling mode > t2 sec.

,sum(nvl(CA_SCELL_CONFIG_ATT, 0)) M8011C67 --This counter provides the number of SCell configuration attempts.

,sum(nvl(CA_SCELL_CONFIG_SUCC, 0)) M8011C68 --This counter provides the number of successful SCell configuration attempts.

,sum(nvl(HIGH_CELL_LOAD_LB, 0)) M8011C69 --This counter indicates the occurrence that the cell is in the active Load Balancing state.
When the cell is in the active Load Balancing state, the Load Balancing algorithm tries to reduce the serving cell load by shifting cell traffic to neighbor cells (Load Balancing handover).
The maximum value of this counter depends on the length of the measurement period. If the measurement period is 15 minutes (that is 900 sec), the maximum value of this counter is 900 sec/1 sec = 900 (every 1 second the eNB checks whether the cell is in the active Load Balancing state).

from NOKLTE_PS_LCELLR_MNC1_RAW PMRAW where

to_char(period_start_time,'yyyymmddHH24mi'),LNCEL_ID,to_char(period_start_time,'yyyymmddHH24mi')||LNCEL_ID

) M8011 ,( select

to_char(period_start_time,'yyyymmddHH24mi') sdatetime ,LNCEL_ID

,to_char(period_start_time,'yyyymmddHH24mi')||LNCEL_ID cel_key_id

,sum(nvl(PDCP_SDU_VOL_UL,0)) M8012C19 --The measurement gives an indication of the eUu interface traffic load by reporting the total received PDCP SDU-related traffic volume.

,sum(nvl(PDCP_SDU_VOL_DL,0)) M8012C20 --The measurement gives an indication of the eUu interface traffic load by reporting the total transmitted PDCP SDU-related traffic volume. ,sum(nvl(IP_TPUT_TIME_UL_QCI_1,0)) M8012C92 ,sum(nvl(IP_TPUT_TIME_UL_QCI_2,0)) M8012C94 ,sum(nvl(IP_TPUT_TIME_UL_QCI_3,0)) M8012C96 ,sum(nvl(IP_TPUT_TIME_UL_QCI_4,0)) M8012C98 ,sum(nvl(IP_TPUT_TIME_UL_QCI_5,0)) M8012C100 ,sum(nvl(IP_TPUT_TIME_UL_QCI_6,0)) M8012C102 ,sum(nvl(IP_TPUT_TIME_UL_QCI_7,0)) M8012C104 ,sum(nvl(IP_TPUT_TIME_UL_QCI_8,0)) M8012C106 ,sum(nvl(IP_TPUT_TIME_UL_QCI_9,0)) M8012C108 ,sum(nvl(IP_TPUT_TIME_DL_QCI_1,0)) M8012C118 ,sum(nvl(IP_TPUT_TIME_DL_QCI_2,0)) M8012C120 ,sum(nvl(IP_TPUT_TIME_DL_QCI_3,0)) M8012C122 ,sum(nvl(IP_TPUT_TIME_DL_QCI_4,0)) M8012C124 ,sum(nvl(IP_TPUT_TIME_DL_QCI_5,0)) M8012C126 ,sum(nvl(IP_TPUT_TIME_DL_QCI_6,0)) M8012C128 ,sum(nvl(IP_TPUT_TIME_DL_QCI_7,0)) M8012C130 ,sum(nvl(IP_TPUT_TIME_DL_QCI_8,0)) M8012C132 ,sum(nvl(IP_TPUT_TIME_DL_QCI_9,0)) M8012C134 from

NOKLTE_PS_LCELLT_MNC1_RAW PMRAW where

to_char(period_start_time,'yyyymmddHH24mi'),LNCEL_ID,to_char(period_start_time,'yyyymmddHH24mi')||LNCEL_ID )M8012,( select

to_char(period_start_time,'yyyymmddHH24mi') sdatetime ,LNCEL_ID

,to_char(period_start_time,'yyyymmddHH24mi')||LNCEL_ID cel_key_id

,sum(nvl(SIGN_CONN_ESTAB_COMP,0)) M8013C5 --The number of Signaling Connection Establishment completions with the UE target to be in the ECM-CONNECTED state.

,sum(nvl(SIGN_EST_F_RRCCOMPL_MISSING,0)) M8013C6 --The number of Signaling Connection Establishment failures due to a missing RRC CONNECTION SETUP COMPLETE message. The UE has not

reached the ECM-CONNECTED state.

,sum(nvl(EPC_INIT_TO_IDLE_DETACH,0)) M8013C10 --The number of EPC-initiated transitions to the ECM-IDLE state due to the Detach procedure by the UE or the MME .

,sum(nvl(SIGN_CONN_ESTAB_COMP_EMG,0)) M8013C26 --The number of Signalling Connection Establishment completions for emergency calls

,sum(nvl(SIGN_CONN_ESTAB_FAIL_RB_EMG,0)) M8013C27 --The number of Signalling Connection Establishment failures for emergency calls due to missing RB (Radio Bearer) resources

,sum(nvl(PRE_EMPT_UE_CONTEXT_NON_GBR,0)) M8013C28 --This measurement provides the number of UE contexts being released due to lack of radio resources.

,sum(nvl(SIGN_CONN_ESTAB_ATT_HIPRIO,0)) M8013C31 ,sum(nvl(SIGN_CONN_ESTAB_ATT_DEL_TOL,0)) M8013C34 ,lnbts.co_object_instance enb_id ,lncel.co_object_instance cell_id ,ip.ipno_mpia_8 bts_ip

,lnbts.co_sys_version bts_version ,Trim(lnbts.co_name) bts_name ,Trim(lncel.co_name) cel_name from

NOKLTE_PS_LUEST_MNC1_RAW PMRAW,ctp_common_objects lnbts,ctp_common_objects lncel,c_lte_ipno ip 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 PMRAW.LNCEL_ID=lncel.co_gid and PMRAW.lnbts_id=lnbts.co_gid

and ip.ipno_bts_id=lnbts.co_object_instance --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

,lnbts.co_object_instance,lncel.co_object_instance,ip.ipno_mpia_8,lnbts.co_sys_version,Trim(lnbts.co_name),Trim(lncel.co_name) )M8013,( select

to_char(period_start_time,'yyyymmddHH24mi') sdatetime ,LNCEL_ID ,to_char(period_start_time,'yyyymmddHH24mi')||LNCEL_ID cel_key_id ,sum(nvl(INTER_ENB_HO_PREP,0)) M8014C0 --The number of Inter-eNB X2-based Handover preparations. The Mobility management (MM) receives a list with target cells from the RRM and decides to start an Inter-eNB X2-based Handover.

,sum(nvl(FAIL_ENB_HO_PREP_TIME,0)) M8014C2 --The number of failed Inter-eNB X2-based Handover preparations due to timer TX2RELOCprep. ,sum(nvl(FAIL_ENB_HO_PREP_AC,0)) M8014C3 --The number of failed Inter-eNB X2-based Handover preparations due to the target eNB's admission control reasons.

,sum(nvl(FAIL_ENB_HO_PREP_OTHER,0)) M8014C5 --The number of failed Inter-eNB X2-based Handover preparations due to the target eNB's other reasons.

,sum(nvl(ATT_INTER_ENB_HO,0)) M8014C6 --The number of Inter-eNB X2-based Handover attempts.

,sum(nvl(SUCC_INTER_ENB_HO,0)) M8014C7 --The number of successful Inter-eNB X2-based Handover completions.

,sum(nvl(INTER_ENB_HO_FAIL,0)) M8014C8 --Number of Inter eNB Handover failures due to expiration of guarding timer TX2RELOCoverall

,sum(nvl(INTER_ENB_S1_HO_PREP,0)) M8014C14 --The number of Inter eNB S1-based Handover

preparations

,sum(nvl(INTER_S1_HO_PREP_FAIL_TIME,0)) M8014C15 --The number of failed Inter eNB S1-based Handover preparations due to the expiry of the guarding timer TS1RELOCprep.

,sum(nvl(INTER_S1_HO_PREP_FAIL_NORR,0)) M8014C16 --The number of failed Inter eNB S1-based Handover preparations with cause No Radio Resources Available in Target Cell.

,sum(nvl(INTER_ENB_S1_HO_ATT,0)) M8014C18 --The number of Inter eNB S1-based Handover attempts

,sum(nvl(INTER_ENB_S1_HO_SUCC,0)) M8014C19 --The number of successful Inter eNB S1-based Handover completions

,sum(nvl(INTER_ENB_S1_HO_FAIL,0)) M8014C20 --The number of Inter eNB S1-based Handover failures from

NOKLTE_PS_LIENBHO_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 )M8014, (

select

to_char(period_start_time,'yyyymmddHH24mi') sdatetime ,LNCEL_ID ,to_char(period_start_time,'yyyymmddHH24mi')||LNCEL_ID cel_key_id ,sum(nvl(INTRA_HO_SUCC_NB,0)) M8015C2 --The number of successful Intra-eNB Handover completions per neighbour cell relationship. ,sum(nvl(INTER_HO_SUCC_NB,0)) M8015C9 --The number of successful Inter eNB Handover completions per neighbour cell relationship ,SUM(NVL(INTER_HO_PREP_FAIL_OTH_NB,0)) M8015C5 --The number of failed Inter eNB Handover preparations per cause per neighbour cell relationship ,SUM(NVL(INTER_HO_PREP_FAIL_TIME_NB,0)) M8015C6 --The number of failed Inter eNB Handover preparations per neighbour cell relationship due to the expiration of the respective guarding timer.

,SUM(NVL(INTER_HO_PREP_FAIL_AC_NB,0)) M8015C7 --The number of failed Inter eNB Handover preparations per neighbour cell relationship due to failures in the HO preparation on the target side

,SUM(NVL(INTER_HO_ATT_NB,0)) M8015C8 --The number of Inter eNB Handover attempts per neighbour cell relationship

from

NOKLTE_PS_LNCELHO_DMNC1_RAW PMRAW where

period_start_time between to_date(&1,'yyyymmddHH24mi') and to_date(&2,'yyyymmddHH24mi')

to_char(period_start_time,'yyyymmddHH24mi'),LNCEL_ID,to_char(period_start_time,'yyyymmddHH24mi')||LNCEL_ID )M8015,( select

to_char(period_start_time,'yyyymmddHH24mi') sdatetime ,LNCEL_ID

,to_char(period_start_time,'yyyymmddHH24mi')||LNCEL_ID cel_key_id ,sum(nvl(CSFB_REDIR_CR_ATT,0)) M8016C11 --The number of CS Fallback attempts with redirection via the RRC Connection Release

,sum(nvl(ISYS_HO_PREP_FAIL_AC,0)) M8016C16 --Number of failed Inter System Handover preparations due to admission control of target cell. ,sum(nvl(ISYS_HO_PREP_FAIL_OTH,0)) M8016C17 --Number of failed Inter System Handover preparations due to other reasons of target cell. ,sum(nvl(ISYS_HO_ATT,0)) M8016C21 --Number of Inter System Handover attempts. ,sum(nvl(ISYS_HO_SUCC,0)) M8016C23 --Number of successful Inter System Handover completions. ,sum(nvl(ISYS_HO_FAIL,0)) M8016C25 --Number of failed Inter System Handover attempts. ,sum(nvl(NACC_TO_GSM_ATT,0)) M8016C26 --This measurement provides the number of NACC from LTE to GSM attempts ,sum(nvl(NACC_TO_GSM_SUCC,0)) M8016C27 --This measurement provides the number of successful NACC from LTE to GSM completions ,sum(nvl(NACC_TO_GSM_FAIL,0)) M8016C28 --This measurement provides the number of failed NACC from LTE to GSM.

,sum(nvl(ISYS_HO_UTRAN_SRVCC_ATT,0)) M8016C29 --This measurement provides the number of Inter System Handover attempts to UTRAN with SRVCC (Single Radio Voice Call Continuity, 3GPP TS 23.216). ,sum(nvl(ISYS_HO_UTRAN_SRVCC_SUCC,0)) M8016C30 --This measurement provides the number of successful Inter System Handover completions to UTRAN with SRVCC (Single Radio Voice Call Continuity, 3GPP TS 23.216).

NOKLTE_PS_LISHO_MNC1_RAW PMRAW where

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

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

,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%'

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

Prefix Prefix

and b.co_parent_gid=c.co_gid and adjl.conf_id=1 and lncel.conf_id=1

and cel.co_parent_gid=c.co_gid and lncel.obj_gid=cel.co_gid and cel.co_oc_id=2881 and c.co_oc_id=2860 and a.co_state=0 and b.co_state=0 and c.co_state=0 and cel.co_state=0 --and d.co_oc_id=3130

-- and c.co_object_instance=235280

--- and ((c.co_object_instance between 233472 and 236031) or (c.co_object_instance between 241664 and 244735))

8. --小区低噪查询

select a.period_start_time

,bts.co_object_instance enodeb_id ,cel.co_object_instance ci_config ,cell.LNCEL_LCR_ID cell_id ,a.RSSI_PUSCH_AVG ,a.RSSI_PUCCH_MIN ,a.RSSI_PUCCH_MAX ,a.RSSI_PUSCH_MIN ,a.RSSI_PUSCH_MAX

from

Noklte_Ps_LPQUL_Mnc1_Raw a ,ctp_common_objects bts ,ctp_common_objects cel ,c_lte_lncel cell where

bts.co_oc_id=2860

and cel.co_parent_gid=bts.co_gid and a.LNCEL_ID=cel.co_gid and cell.obj_gid=cel.co_gid and cell.conf_id='1'

and a.period_start_time>=to_date(&start_date,'yyyymmddhh24') and a.period_start_time

620568, 620569, 620596, 620865, 620595 )

group by

a.period_start_time ,bts.co_object_instance ,cel.co_object_instance ,cell.LNCEL_LCR_ID ,a.RSSI_PUCCH_MIN ,a.RSSI_PUCCH_MAX ,a.RSSI_PUSCH_AVG ,a.RSSI_PUSCH_MIN ,a.RSSI_PUSCH_MAX

order by

a.period_start_time

诺西LTE指标提取SQL语句20160801

下载：诺西LTE指标提取SQL语句20160801.doc

最近浏览

最新搜索

站内搜索