发布时间 : 星期四 文章诺西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
1
本文主要包括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))
2
/(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
3