SQL practical optimization

Transcript

2. Sql优化核心是什么？
3. create table test1(id,object_name,owner) as select object_id as id,object_name,owner from dba_objects; create table test2(id,object_type,status) as select object_id as id,status,temporary from dba_objects; select count(*) from test1 t1,test2 t2 where t1.id=t2.id and t1.owner='SCOTT'; create index idx_test1 on test1(id,owner); create index idx_test2 on test2(id); exec dbms_stats.gather_table_stats(user,'test1',cascade=>true,estimate_percent=>100); exec dbms_stats.gather_table_stats(user,'test2',cascade=>true,estimate_percent=>100);
5. CREATE INDEX IDX_TEST1 ON TEST1(OWNER,ID);
6. 适用于在单独查询返回记录很多，而组合查询之后返回记录很少的情况 选择过滤条件作为引导列 尽量把join列放在组合索引的最后面，即使join选择性很高 引导列的选择性越高越好 仅等值查询时，组合索引的顺序是不影响性能的
7. 在数据库优化过程中，索引的重要性是不言而喻的，但是在我们进行性能调整过程中， 一个索引是否能够被使用到，在索引创建之前是无法确定的，而创建索引又是一个代价 很高的操作，尤其是数据量很大的情况下，这时候我 们就可以考虑使用虚拟索引 特点： 无法执行alter index 不能创建和虚拟索引同名的实际索引 数据字典中查不到
8. create table test as select * from dba_objects; --创建虚拟索引，首先要将 _use_nosegment_indexes的隐含参数设置为true alter session set "_use_nosegment_indexes"=true; create index ix_test on test(object_id) nosegment; explain plan for select * from test where object_id=1; set linesize 1000 select * from table(dbms_xplan.display()); set autotrace traceonly select * from test where object_id=1;
9. --以下看的是真实执行计划，显 然是用不到索引。 alter session set statistics_level=all; select * from test where object_id=1; select * from table(dbms_xplan.display_cur sor(null,null,'allstats last'));
10. create table test1 ( a int, b varchar2(80) ); begin for i in 1 .. 100000 loop insert into test1(a,b) values (i, rpad(dbms_random.random,75,'*') ); end loop; end; alter table test1 add constraint test1_pk primary key(a); begin dbms_stats.gather_table_stats( user, 'TEST1', cascade=>true ); end; select /*+ index( test1 test1_pk ) */ * from test where a between 20000 and 40000; create table test2 as select a,b from test1 order by b; alter table test2 add constraint test2_pk primary key (a); begin dbms_stats.gather_table_stats( user, 'TEST2', cascade=>true ); end; select /*+ index( test2 test2_pk ) */ * from test where a between 20000 and 40000;
13. 当sql中出现内联视图或是通过create view语句创建的视图时，CBO会将视图进行展开， 进行等价改写，这个过程就叫视图合并
14. 是否发生了视图合并，如果没有发生视图合并，在执行计划中，一般我们都能看到view 关键字 当子查询或视图中有以下情况，那么视图是不会合并的 Union,union all,instersact,minus Avg,count,max,min,sum Rownum Connect by Group by distinct
15. 什么是谓词推入？ 当sql语句中包含不能合并的视图，并且视图有谓词过滤，那么Oracle CBO就会将where 过滤条件推入到视图中，这个就是谓词过滤。 谓词推入目的？ 谓词过滤注意就是让oracle尽可能早的过滤掉无用的数据，提升sql运行性能。
16. SQL> create or replace view v_emp as select /*+ no_merge */ empno,ename,job from emp where sal>3000; SQL> select * from v_emp where ename='KING';
17. SQL> create or replace view v_emp as select /*+ no_merge */ empno,ename,job from emp where sal>3000 and rownum>=1; SQL> select * from v_emp where ename='KING';
18. select employee_id,first_name,last_name, salary from employees a where salary=(select min(salary) from employees b where b.department_id=a.department_id);
19. with c as (select e.*,min(salary) over(partition by department_id) as min_salary from employees e) select employee_id,first_name,last_n ame,salary from c where c.salary=c.min_salary;
21. 根据nested loops原理，我们有以下优化技巧 驱动表的过滤条件要有索引 被驱动表的join字段要有索引 驱动表结果集要小
22. create table test1 as select * from dba_objects; create table test2 as select * from dba_objects; select /*+ gather_plan_statistics */ a.owner,count(*) from test1 a,test2 b where a.object_name=b.object_name group by a.owner; select * from table(dbms_xplan.display_cursor(null ,null,'ALLSTATS LAST'));
23. select /*+ gather_plan_statistics dynamic_sampling(a 10) dynamic_sampling(b 10) */ a.owner,count(*) from test1 a,test2 b where a.object_name=b.object_name group by a.owner; select * from table(dbms_xplan.display_curs or(null,null,'ALLSTATS LAST'));
24. select * from (select row_.*, rownum rownum_ from (select t.bookreviewid, t.msisdn, t.contentid, t.contenttype, t.portaltype, t.publishstatus, t.commentary, t.publishsdate, t.createtime, t.floorNum, t.istop, t.assessstatus, t.isprime, :"SYS_B_0" as createNick, nvl(opposenum, :"SYS_B_1") as opposenum, nvl(abetnum, :"SYS_B_2") as abetnum, t.replycontent, t.latestreplytime from us_publiccomment t where :"SYS_B_3" = :"SYS_B_4" and t.publishstatus in (:"SYS_B_5", :"SYS_B_6") and t.contenttype = :1 and t.contentid = :2 order by t.isTop desc, t.floornum desc) row_ where rownum <= :"SYS_B_7") where rownum_ >= :"SYS_B_8"
25. create index MREAD.IDX_US_PUBLIC_CON_SORT on MREAD.us_publiccomment(CONTENTI D,istop desc,floornum desc) tablespace TBS_MREAD_IDX parallel 8 online 重新搜集统计信息
26.  Sql语句中有rownum< , 只有语句中有rownum< 执行计划中才会有stopkey关键字  Order by 后面的字段必须建索引
27. 早上老综合库在9点24分的时候出现了ORA-600错误，错误内容如下： Tue May 12 09:24:52 2015 Errors in file /oracle/database/diag/rdbms/integ/integ3/trace/integ3_ora_910.trc (incident=755353): ORA-00600: 内部错误代码, 参数: [kokegPinLob1], [], [], [], [], [], [], [], [], [], [], [] Incident details in: /oracle/database/diag/rdbms/integ/integ3/incident/incdir_755353/integ3_ora_910_i755353.trc Tue May 12 09:25:27 2015 Errors in file /oracle/database/diag/rdbms/integ/integ3/trace/integ3_ora_2327.trc (incident=754858): ORA-00600: 内部错误代码, 参数: [kokegPinLob1], [], [], [], [], [], [], [], [], [], [], [] Incident details in: /oracle/database/diag/rdbms/integ/integ3/incident/incdir_754858/integ3_ora_2327_i754858.trc 通过跟踪后台日志发现是下列SQL引起的: ========= Dump for incident 755353 (ORA 600 [kokegPinLob1]) ========
28. *** 2015-05-12 09:24:52.923 dbkedDefDump(): Starting incident default dumps (flags=0x2, level=3, mask=0x0) ----- Current SQL Statement for this session (sql_id=5nz1w5b0f1dxy) ----- select * from (select r.*, ROWNUM rn from (select A.*,T.RESOURCE_NAME,C.RES_GRP_NAME, T.CATEGORY,ip.ip,B.TYPE,B.start_time,ROWNUM from SLAVE_ACCOUNT A, PRACCT_SLACCT_R B , APP_RESOURCE T, RESOURCE_GROUP C ,(select t.resource_id,wm_concat(t.ip) ip from resource_ip t group by t.resource_id) ip WHERE A.SLACCT_ID = B.SLACCT_ID and t.resource_id=ip.resource_id AND A.RESOURCE_ID = T.RESOURCE_ID AND T.RES_GRP_ID = C.RES_GRP_ID AND B.PRACCT_ID = :1 AND (B.CANCEL_TIME is null or B.CANCEL_TIME>SYSDATE ) and A.STATE != 2 ORDER BY A.SLACCT_ID ASC ) r where ROWNUM < :2) where rn >= :3
30. 该函数在Oracle官方的文档中是:function is used internally and for this reason it is UN-documented,11g listagg使用比wmsys.wm_concat效率高很 多,wmsys.wm_concat是undocument函数，有很多不确定因素，不建议使用,而且 12c已经删除了wmsys.wm_concat,建议11g库对wmsys.wm_concat最好修改为 listagg。
33. Sql优化的核心就是减少IO操作，这里的IO不单指物理IO 特殊执行计划和高级执行计划