SQL优化(ORACLE)数据库优化技巧

目 录 1 oracle 如何得到一个很大的表 3 2 loop insert 实例 5 3 autotrace 验证索引的性能到底有多大 6 4 EXPLAIN 验证 SQL是否走索引 8 5 结合 autotrace 创建并验证函数索引 8 6 sql trace 分析工具 TKPROF详细讲解 11 7 V SQL 视图详解加几个实例 15 8 autotrace 验证压缩表性能 17 9 AUTOTRACE 验证消除子查询后的性能 20 10 基于成本的优化 CBO 23 11 如何统计数据库数据 26 12 oracle 如何统计操作系统数据 28 13 parallel t 4 在大表查询性能的体现 29 14 CRAS和 create insert 的性能测试 30 15 增加字段时指定 default 值和先增加再 update 哪个性能好 31 16 分区表实例讲解 33 17 分区索引实例讲解 39 18 分区表管理实例 以及常见的错误 40 1 oracle 如何得到一个很大的表 在 Oracle 默认的数据库中最大的系统表非 dba objects 莫属了 我们可以利用 CTAS 这个表来创建一个很大的表 很快就可以得到几千万的数据量 对测试 提供了很好的环境 还有一种就是笛卡尔积这个表 得到的数据惊人 SQL create table awen ob bak as select from dba objects 表已创建 SQL select count from awen ob bak COUNT 50429 SQL insert into awen ob bak select from dba objects 已创建 50428 行 SQL insert into awen ob bak select from dba objects 已创建 50428 行 SQL insert into awen ob bak select from dba objects 已创建 50428 行 SQL insert into awen ob bak select from dba objects 已创建 50428 行 SQL insert into awen ob bak select from dba objects 已创建 50428 行 SQL insert into awen ob bak select from dba objects 已创建 50428 行 SQL 已创建 50428 行 SQL 已创建 50428 行 SQL 已创建 50428 行 SQL 已创建 50428 行 SQL 已创建 50428 行 SQL insert into awen ob bak select from awen ob bak 已创建 605137 行 SQL insert into awen ob bak select from awen ob bak 已创建 1210274 行 SQL insert into awen ob bak select from awen ob bak 已创建 2420548 行 SQL insert into awen ob bak select from awen ob bak 已创建 4841096 行 SQL 已创建 9682192 行 SQL select count from awen ob bak COUNT 19364384 还有一种就是笛卡尔积 这种可以翻倍增长 还有下面用 PL SQL也可以实现 2 loop insert 实例 下面是一个简单的 loop insert 的实例 SQL desc over com 名称 是否为空 类型 ID NUMBER 9 SQL select from over com ID 1 1 1 1 1 1 1 1 1 1 已选择 10 行 SQL begin 2 for i in 1 1000000 3 loop 4 insert into over com values i 5 end loop 6 end 7 PL SQL 过程已成功完成 SQL select count from over com COUNT 1000010 3 autotrace 验证索引的性能到底有多大 下面是通过例子来验证索引的性能到底有多大 一个只要 2 秒 一个要 6 分 22 秒 看到下面的例子 大家就会意识到索引的重要性了 前面一个是建索引的 后面 的是没有索引的 先对几个指标解释一下 recursive calls 递归调用的次数 db block gets 读数据块的数量 consistent gets 总的逻辑 I O physical reads 物理 I O redo size 重做数量 bytes sent via SQL Net to client bytes received via SQL Net from client SQL Net roundtrips to from client sorts memory 内存排序统计 sorts disk 磁盘排序统计 rows processed 被检索的行数 SQL create index ob index on awen ob bak owner 索引已创建 SQL set autotrace traceonly SQL show parameter sql trace SQL show parameter sql SQL select count from awen ob bak where owner awen 执行计划 Plan hash value 3442547862 Id Operation Name Rows Bytes Cost CPU Time 0 SELECT STATEMENT 1 6 1115 4 00 00 02 1 SORT AGGREGATE 1 6 2 INDEX RANGE SCAN OB INDEX 463K 2717K 1115 4 00 00 02 Predicate Information identified by operation id 2 access OWNER awen 统计信息 189 recursive calls 0 db block gets 27 consistent gets 2 physical reads 0 redo size 407 bytes sent via SQL Net to client 385 bytes received via SQL Net from client 2 SQL Net roundtrips to from client 5 sorts memory 0 sorts disk 1 rows processed SQL select count from awen ob bak2 where owner awen 执行计划 Plan hash value 2227305659 Id Operation Name Rows Bytes Cost CPU Time 0 SELECT STATEMENT 1 6 314K 1 00 06 22 1 SORT AGGREGATE 1 6 2 TABLE ACCESS FULL OB BAK2 463K 2717K 314K 1 00 06 22 Predicate Information identified by operation id 2 filter OWNER awen 统计信息 1 recursive calls 递归调用的次数 0 db block gets 读数据块的数量 265997 consistent gets 总的逻辑 I O 256004 physical reads 物理 I O 0 redo size 重做数量 407 bytes sent via SQL Net to client 385 bytes received via SQL Net from client 2 SQL Net roundtrips to from client 0 sorts memory 内存排序统计 0 sorts disk 磁盘排序统计 1 rows processed 被检索的行数 4 EXPLAIN 验证 SQL 是否走索引 下面的例子可以查看 sql 语句是否走了索引 SQL col id for 999 SQL col operation for a20 SQL col options for a20 SQL col object name for a20 SQL create index ob index on awen ob bak owner 索引已创建 SQL explain plan for select count from ob bak where owner awen 已解释 SQL select id operation options object name position from plan table ID OPERATION OPTIONS OBJECT NAME POSITION 0 SELECT STATEMENT 1115 1 SORT AGGREGATE 1 2 INDEX RANGE SCAN OB INDEX 1 SQL explain plan for select count from ob bak2 where owner awen 已解释 SQL select id operation options object name position from plan table ID OPERATION OPTIONS OBJECT NAME POSI TION 0 SELECT STATEMENT 314798 1 SORT AGGREGATE 1 2 TABLE ACCESS FULL OB BAK2 1 5 结合 autotrace 创建并验证函数索引 下面的列子 介绍了怎么创建函数索引 怎么看 sql 是否走了这个函数索引 以及索引对 sql 性能的提高 SQL select ename job mgr deptno from scott emp where sal 12 30000 已选择 9 行 执行计划 Plan hash value 3956160932 Id Operation Name Rows Bytes Cost CPU Time 0 SELECT STATEMENT 1 25 9 0 00 00 01 1 TABLE ACCESS FULL EMP 1 25 9 0 00 00 01 Predicate Information identified by operation id 1 filter SAL 12 create index scott index 1 on scott emp sal 索引已创建 SQL select ename job mgr deptno from scott emp where sal 12 30000 已选择 9 行 执行计划 Plan hash value 3956160932 Id Operation Name Rows Bytes Cost CPU Time 0 SELECT STATEMENT 1 25 9 0 00 00 01 1 TABLE ACCESS FULL EMP 1 25 9 0 00 00 01 Predicate Information identified by operation id 1 filter SAL 12 create index scott index 2 on scott emp sal 12 索引已创建 SQL select ename job mgr deptno from scott emp where sal 12 30000 已选择 9 行 执行计划 Plan hash value 3497670227 Id Operation Name Rows Bytes Cost CP U Time 0 SELECT STATEMENT 1 25 2 0 00 00 01 1 TABLE ACCESS BY INDEX ROWID EMP 1 25 2 0 00 00 01 2 INDEX RANGE SCAN SCOTT INDEX 2 1 1 0 00 00 01 Predicate Information identified by operation id 2 access SAL 12 alter system set sql trace true 系统已更改 SQL set autotrace traceonly SQL select count from awen ob bak 执行计划 Plan hash value 3333797230 Id Operation Name Rows Cost CPU Time 0 SELECT STATEMENT 1 313K 1 00 06 20 1 SORT AGGREGATE 1 2 TABLE ACCESS FULL OB BAK 19M 313K 1 00 06 20 统计信息 1 recursive calls 0 db block gets 265131 consistent gets 260828 physical reads 0 redo size 411 bytes sent via SQL Net to client 385 bytes received via SQL Net from client 2 SQL Net roundtrips to from client 0 sorts memory 0 sorts disk 1 rows processed SQL select count from awen ob bak2 执行计划 Plan hash value 2227305659 Id Operation Name Rows Cost CPU Time 0 SELECT STATEMENT 1 314K 1 00 06 21 1 SORT AGGREGATE 1 2 TABLE ACCESS FULL OB BAK2 19M 314K 1 00 06 21 统计信息 185 recursive calls 0 db block gets 266016 consistent gets 259202 physical reads 0 redo size 411 bytes sent via SQL Net to client 385 bytes received via SQL Net from client 2 SQL Net roundtrips to from client 4 sorts memory 0 sorts disk 1 rows processed SQL set autotrace off SQL exit 从 Oracle Database 10g Enterprise Edition Release 10 2 0 1 0 Production With the Partitioning OLAP and Data Mining options 断开 F oracle product 10 2 0 admin orcl udump tkprof orcl ora 864 trc orcl222 txt TKPROF Release 10 2 0 1 0 Production on 星期二 2 月 21 17 04 50 2012 Copyright c 1982 2005 Oracle All rights reserved 打开 orcl222 txt 文件之后 select count from awen ob bak call count cpu elapsed disk query current row s Parse 1 0 00 0 00 0 0 0 0 分析 Execute 1 0 00 0 00 0 0 0 0 执行 Fetch 2 4 78 206 63 260828 265131 0 1 获取 total 4 4 78 206 63 260828 265131 0 1 总共 过程 次数 执行用掉的时间 读数目 缓冲区读取数目 Misses in library cache during parse 1 Optimizer mode ALL ROWS Parsing user id SYS Rows Row Source Operation 1 SORT AGGREGATE cr 265131 pr 260828 pw 0 time 206638048 us 19364384 TABLE ACCESS FULL OB BAK cr 265131 pr 260828 pw 0 time 174313356 us select count from awen ob bak2 call count cpu elapsed disk query current row s Parse 1 0 00 0 16 0 0 0 0 Execute 1 0 00 0 00 0 0 0 0 Fetch 2 4 51 150 27 259202 265997 0 1 total 4 4 51 150 44 259202 265997 0 1 Misses in library cache during parse 1 Optimizer mode ALL ROWS Parsing user id SYS Rows Row Source Operation 1 SORT AGGREGATE cr 265997 pr 259202 pw 0 time 150277965 us 19364384 TABLE ACCESS FULL OB BAK2 cr 265997 pr 259202 pw 0 time 116186323 u s Trace file orcl ora 864 trc Trace file compatibility 10 01 00 Sort options default 12 sessions in tracefile 148 user SQL statements in trace file 2836 internal SQL statements in trace file 453 SQL statements in trace file 71 unique SQL statements in trace file 4976 lines in trace file 509 elapsed seconds in trace file 7 V SQL 视图详解加几个实例 几个例子 1 查询消费磁盘 I O 最多的 sql 语句 SQL select sql text executions disk reads from v sql where disk reads 400 order by disk reads desc SQL col sql text for a50 SQL SQL TEXT EXECUTIONS DISK READS select count from awen ob bak 1 229051 select count from ob bak 1 229018 select count from ob com 1 87005 select count from awen ob com 1 86766 SELECT OPT DYN SAMP ALL ROWS IGNORE WHER 2 501 E CLAUSE NO PARALLEL SAMPLESUB opt param paralle l execution enabled false NO PARALLEL INDEX S AMPLESUB NO SQL TUNE NVL SUM C1 0 NVL SUM C 2 0 FROM SELECT NO PARALLEL OB COM FULL OB COM NO PARALLEL INDEX OB COM 1 AS C1 1 AS C2 FROM AWEN OB COM SAMPLE BLOCK 0 07208 3 1 SEED 1 OB COM SAMPLESUB 2 查询逻辑读取数最多的 sql 语句 SQL select sql text buffer gets parse calls from v sql where buffer gets 10000 order by buffer gets desc SQL TEXT BUFFER GETS PARSE CALLS begin for i in 1 1000000 loop insert into over 1122409 1 com values i end loop end INSERT INTO OVER COM VALUES B1 1122406 1 select count from awen ob bak 265131 1 select count from ob bak 265131 1 select count from ob com 87049 1 select count from awen ob com 87047 1 DECLARE job BINARY INTEGER job next date DATE 31077 160 mydate broken BOOLEAN FALSE BEGIN EMD M AINTENANCE EXECUTE EM DBMS JOB PROCS mydate next date IF broken THEN b 1 ELSE b 0 END IF END select file block from seg where type 3 and 15399 261 ts 1 3 消耗 CPU 最多的 SQL SQL select sql text cpu time buffer gets from v sql where cpu time 500000 order by cpu time desc SQL TEXT CPU TIME BUFFER GETS begin for i in 1 1000000 loop insert into over 23426954 1122409 com values i end loop end INSERT INTO OVER COM VALUES B1 14556875 1122406 select count from awen ob bak 3359889 265131 select count from ob bak 3250529 265131 select count from ob com 1640647 87049 insert into wrh sga target advice snap id dbi 1437772 95 d instance number SGA SIZE SGA SIZE FACTOR ESTD DB TIME ESTD PHYSICAL READS select sna p id dbid instance number SGA SIZE SGA SI ZE FACTOR ESTD DB TIME ESTD PHYSICAL READS from v sga target advice select count from awen ob com 1375021 87047 DECLARE job BINARY INTEGER job next date DATE 632426 31317 mydate broken BOOLEAN FALSE BEGIN EMD M AINTENANCE EXECUTE EM DBMS JOB PROCS mydate next date IF broken THEN b 1 ELSE b 0 END IF END V SQL 视图字段的示意 SQL TEXT SQL 文本的前 1000 个字符 SHARABLE MEM 占用的共享内存大小 单位 byte PERSISTENT MEM 生命期内的固定内存大小 单位 byte RUNTIME MEM 执行期内的固定内存大小 SORTS 完成的排序数 LOADED VERSIONS 显示上下文堆是否载入 1 是 0 否 OPEN VERSIONS 显示子游标是否被锁 1 是 0 否 USERS OPENING 执行语句的用户数 FETCHES SQL 语句的 fetch 数 EXECUTIONS 自它被载入缓存库后的执行次数 USERS EXECUTING 执行语句的用户数 LOADS 对象被载入过的次数 FIRST LOAD TIME 初次载入时间 INVALIDATIONS 无效的次数 PARSE CALLS 解析调用次数 DISK READS 读磁盘次数 BUFFER GETS 读缓存区次数 ROWS PROCESSED 解析 SQL 语句返回的总列数 COMMAND TYPE 命令类型代号 OPTIMIZER MODE SQL 语句的优化器模型 OPTIMIZER COST 优化器给出的本次查询成本 PARSING USER ID 第一个解析的用户 ID PARSING SCHEMA ID 第一个解析的计划 ID KEPT VERSIONS 指出是否当前子游标被使用 DBMS SHARED POOL 包标记为常驻内存 ADDRESS 当前游标父句柄地址 TYPE CHK HEAP 当前堆类型检查说明 HASH VALUE 缓存库中父语句的 Hash 值 PLAN HASH VALUE 数值表示的执行计划 CHILD NUMBER 子游标数量 MODULE 在第一次解析这条语句是通过调用 DBMS APPLICATION INFO SET MODULE 设置的模块 名称 ACTION 在第一次解析这条语句是通过调用 DBMS APPLICATION INFO SET ACTION 设置的动作名 称 SERIALIZABLE ABORTS 事务未能序列化次数 OUTLINE CATEGORY 如果 outline 在解释 cursor 期间被应用 那么本列将显示出 outline 各类 否则 本列为空 CPU TIME 解析 执行 取得等 CPU 使用时间 单位 毫秒 ELAPSED TIME 解析 执行 取得等消耗时间 单位 毫秒 OUTLINE SID outline session 标识 CHILD ADDRESS 子游标地址 SQLTYPE 指出当前语句使用的 SQL 语言版本 REMOTE 指出是否游标是一个远程映象 Y N OBJECT STATUS 对象状态 VALID or INVALID IS OBSOLETE 当子游标的数量太多的时候 指出游标是否被废弃 Y N 8 autotrace 验证压缩表性能 SQL create table ob com compress as select from ob bak 表已创建 SQL select count from ob com COUNT 19364384 SQL select count from ob bak COUNT 19364384 SQL set autotrace traceonly SQL select count from awen over com 执行计划 Plan hash value 3164419484 Id Operation Name Rows Cost CPU Time 0 SELECT STATEMENT 1 9 0 00 00 01 1 SORT AGGREGATE 1 2 TABLE ACCESS FULL OVER COM 10 9 0 00 00 01 Note dynamic sampling used for this statement 统计信息 5 recursive calls 0 db block gets 15 consistent gets 0 physical reads 0 redo size 408 bytes sent via SQL Net to client 385 bytes received via SQL Net from client 2 SQL Net roundtrips to from client 0 sorts memory 0 sorts disk 1 rows processed SQL select count from awen after com 执行计划 Plan hash value 4088644374 Id Operation Name Rows Cost CPU Time 0 SELECT STATEMENT 1 7 0 00 00 01 1 SORT AGGREGATE 1 2 TABLE ACCESS FULL AFTER COM 10 7 0 00 00 01 Note dynamic sampling used for this statement 统计信息 5 recursive calls 0 db block gets 7 consistent gets 0 physical reads 0 redo size 408 bytes sent via SQL Net to client 385 bytes received via SQL Net from client 2 SQL Net roundtrips to from client 0 sorts memory 0 sorts disk 1 rows processed SQL select count from awen ob bak 执行计划 Plan hash value 3333797230 Id Operation Name Rows Cost CPU Time 0 SELECT STATEMENT 1 313K 1 00 06 20 1 SORT AGGREGATE 1 2 TABLE ACCESS FULL OB BAK 19M 313K 1 00 06 20 统计信息 1 recursive calls 0 db block gets 265131 consistent gets 229051 physical reads 0 redo size 411 bytes sent via SQL Net to client 385 bytes received via SQL Net from client 2 SQL Net roundtrips to from client 0 sorts memory 0 sorts disk 1 rows processed 压缩表一般使用于数据仓库 专注于存储 当要更新的时候 存储空间会爆增 9 AUTOTRACE 验证消除子查询后的性能 SQL set autotrace traceonly SQL select from scott emp e1 where e1 sal select avg sal from scott emp e 2 where e2 deptno e1 deptno 已选择 6 行 执行计划 Plan hash value 3796349128 Id Operation Name Rows Bytes Cost CP U Time 0 SELECT STATEMENT 1 63 12 9 00 00 01 1 TABLE ACCESS BY INDEX ROWID EMP 1 37 2 0 00 00 01 2 NESTED LOOPS 1 63 12 9 00 00 01 3 VIEW VW SQ 1 3 78 10 1 0 00 00 01 4 HASH GROUP BY 3 21 10 1 0 00 00 01 5 TABLE ACCESS FULL EMP 14 98 9 0 00 00 01 6 INDEX RANGE SCAN SCOTT INDEX 1 1 1 0 00 00 01 Predicate Information identified by operation id 1 filter DEPTNO E1 DEPTNO 6 access E1 SAL VW COL 1 统计信息 256 recursive calls 0 db block gets 57 consistent gets 8 physical reads 0 redo size 1033 bytes sent via SQL Net to client 385 bytes received via SQL Net from client 2 SQL Net roundtrips to from client 2 sorts memory 0 sorts disk 6 rows processed SQL ed 已写入 file afiedt buf 1 select from scott emp e1 select e2 deptno deptno 2 avg e2 sal avg sal from scott emp e2 group by deptno dept avg sal 3 where e1 deptno dept avg sal deptno 4 and e1 sal dept avg sal avg sal SQL 已选择 6 行 执行计划 Plan hash value 3051581259 Id Operation Name Rows Bytes Cost CP U Time 0 SELECT STATEMENT 1 63 12 9 00 00 01 1 TABLE ACCESS BY INDEX ROWID EMP 1 37 2 0 00 00 01 2 NESTED LOOPS 1 63 12 9 00 00 01 3 VIEW 3 78 10 1 0 00 00 01 4 HASH GROUP BY 3 21 10 1 0 00 00 01 5 TABLE ACCESS FULL EMP 14 98 9 0 00 00 01 6 INDEX RANGE SCAN SCOTT INDEX 1 1 1 0 00 00 01 Predicate Information identified by operation id 1 filter E1 DEPTNO DEPT AVG SAL DEPTNO 6 access E1 SAL DEPT AVG SAL AVG SAL 统计信息 1 recursive calls 0 db block gets 12 consistent gets 0 physical reads 0 redo size 1208 bytes sent via SQL Net to client 385 bytes received via SQL Net from client 2 SQL Net roundtrips to from client 0 sorts memory 0 sorts disk 6 rows processed 10 基于成本的优化 CBO 实例级 CBO 的 3 种优化方式 1 all rows 最大吞吐量 默认方式 2 first rows n 优先输出查询的前几行 3 first rows 为上面的老版本 尽快输出 查看默认方式 SQL show parameter optimizer mode NAME TYPE VALUE optimizer mode string ALL ROWS SQL set autotrace traceonly SQL alter system set optimizer mode first rows 100 scope both 系统已更改 SQL select from awen ob bak2 where rownum 100 已选择 99 行 执行计划 Plan hash value 1013120519 Id Operation Name Rows Bytes Cost CPU Time 0 SELECT STATEMENT 99 17523 817 100 00 00 10 1 COUNT STOPKEY 2 TABLE ACCESS FULL OB BAK2 18M 3079M 817 100 00 00 10 Predicate Information identified by operation id 1 filter ROWNUM alter system set optimizer mode all rows scope both 系统已更改 SQL select from awen ob bak2 where rownum 100 已选择 99 行 执行计划 Plan hash value 1013120519 Id Operation Name Rows Bytes Cost CPU Time 0 SELECT STATEMENT 99 17523 817 100 00 00 10 1 COUNT STOPKEY 2 TABLE ACCESS FULL OB BAK2 18M 3079M 817 100 00 00 10 Predicate Information identified by operation id 1 filter ROWNUM alter session set optimizer mode all rows SQL 语句中使用 hiti 提示 SQL select first rows 10000 from awen ob bak where rownum 100000 已选择 99999 行 执行计划 Plan hash value 1477604399 Id Operation Name Rows Bytes Cost CPU Time 0 SELECT STATEMENT 99999 8398K 310 3 00 00 04 1 COUNT STOPKEY 2 TABLE ACCESS FULL OB BAK 99999 8398K 310 3 00 00 04 Predicate Information identified by operation id 1 filter ROWNUM select job name state owner from dba scheduler jobs JOB NAME STATE OWNER PURGE LOG SCHEDULED SYS FGR AUTOPURGE JOB DISABLED SYS GATHER STATS JOB SCHEDULED SYS AUTO SPACE ADVISOR JOB SCHEDULED SYS RLM EVTCLEANUP SCHEDULED EXFSYS RLM SCHDNEGACTION SCHEDULED EXFSYS GATHER STATS JOB 的 state 为 SCHEDULED 表示正在被系统调用 SQL select last analyzed table name owner num rows sample size from dba tables where owner SCOTT LAST ANALYZED TABLE NAME OWNER NUM ROWS SAMPLE SIZE DEPT SCOTT EMP SCOTT BONUS SCOTT SALGRADE SCOTT COL SCOTT A SCOTT WWW SCOTT 但是不知道为什么没有查到数据 手工统计数据 使用 dbms stats 包来实现数据收集 gather database statistics 为全库中的表统计数据 gather schema statistics 为某个模式统计数据 gather table statistics 为某个特定的表统计数据 gather index statistics 为某个索引表统计数据 SQL execute dbms stats gather schema stats ownname awen PL SQL 过程已成功完成 SQL alter session set nls date format yyyy mm dd hh24 mi ss 会话已更改 SQL col owner for a10 SQL col table name for a10 SQL select last analyzed table name owner num rows sample size from dba tables where owner AWEN LAST ANALYZED TABLE NAME OWNER NUM ROWS SAMPLE SIZE 2012 02 21 11 00 07 A1 AWEN 6 6 2012 02 21 11 00 07 A2 AWEN 8 8 2012 02 21 11 00 07 A3 AWEN 8 8 2012 02 21 11 03 16 UP TEMP AWEN 256 256 2012 02 21 11 03 16 UP TEST AWEN 256 256 2012 02 21 11 02 05 OB BAK AWEN 19391099 593353 2012 02 21 11 03 16 OB BAK2 AWEN 19382334 53850 2012 02 21 11 03 16 TEMP UP AWEN 160 160 NUM ROWS 行数 SAMPLE SIZE 统计的采样行数 手工收集整个数据库的统计数据 SQL begin 2 dbms stats gather database stats estimate percent null 3 end 4 PL SQL 过程已成功完成 SQL SQL SQL SQL select last analyzed table name owner num rows sample size from dba tables where owner HR LAST ANALYZED TABLE NAME OWNER