Oracle-通过创建索引加快SQL执行效率
一、问题概述
需求：客户反馈dg库执行SQL比主库慢一备，其实和上一个cache的案例是一个客户，但是场景不同，本次使用了cache，但是效果还是不理想，客户不开心，废话不多说。
1) 客户执行一条sql,主库执行时间<<备库执行的时间；
2）上前观察dg cache的情况，发现SQL涉及的对象不再cache中，多次查询也无法加载到cache中；
3）观察为什么dg环境未进行cache,发现sql执行计划涉及的表全表扫描，event 直接路径读！
4）会话层面禁用直接路径读，多次查询，发现sql性能提升不明显，并且不能在dg关闭直接路径读！ 也就是说在cache中还是慢
5）测试库进行创建索引测试，确认可以优化SQL提升速度；
6）生产库创建索引，对比执行效率。
二、问题分析
1) 客户执行一条sql,主库执行时间<<备库执行的时间；
set linesize 500 set termout off alter session set statistics_level=all; alter session set current_schema=xx; --exec sql select /*+ gather_plan_statistics */ count(*) from xx; select * from table(dbms_xplan.display_cursor(null,null,'ADVANCED ALLSTATS LAST PEEKED_BINDS')); SELECT STATEMENT 00:01:18.26 dg备库耗时 1分18s 主库15s
相同执行计划！
2）上前观察dg cache的情况，发现SQL涉及的对象不再cache中，多次查询也无法加载到cache中；
SYS> select o.object_name,count(*) blocks from dba_objects o,v$bh bh where o.object_id=bh.objd and o.owner in ('xx') and o.object_name in('xx') group by o.object_name; OBJECT_NAME BLOCKS ------------------------------------ xx 1
3）观察为什么dg环境未进行cache,发现sql执行计划涉及的表全表扫描，event 直接路径读！
SQL> select INST_ID,sid,serial#,USERNAME,STATUS,MACHINE,SQL_ID,EVENT,(sysdate-LOGON_TIME)*86400 as "s",
LAST_CALL_ET from gv$session where status='ACTIVE' and username is not null; USERNAME SQL_ID EVENT ------------- -------------------- ---------- ----- xx cyqxx direct path read
> alter table xx.xx cache; * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-16000: database open for read-only access
4）会话层面禁用直接路径读，多次查询，发现sql性能提升不明显，并且不能在dg关闭直接路径读！ 也就是说在cache中还是慢
session 1>
alter session set events '10949 trace name context forever'; 多次执行sql 可以参考 https://www.eygle.com/archives/2012/05/oracle_11g_direct_path_read.html
SQL 执行效率从 1分18s 降低到了22s
但是无法实例级别关闭直接路径读， 这个方式目的是暂时禁用直接路径读，将sql涉及的表cache到内存中
OBJECT_NAME BLOCKS
------------------------------------
xx 497
session 2>
会话层面不禁用直接路径读，耗时24s !!! 直接路径读反而比走cache快2s！！！
想办法让sql 执行的对象cache到buffer cache中，SQL 执行效率从1分18s 降低到了 24s !!! 客户不满意，主库sql执行15s !!!
5）测试库进行创建索引测试，确认可以优化SQL提升速度；
select /*+ gather_plan_statistics */ 6个列, (SELECT SUM(MMI.column_1) FROM tab3 MMI WHERE MMI.column_4=MR.column_3) TOTALMONEY FROM tab1 mr left join (select column_3, replace(to_char(wm_concat(column_4)), ',', '/') column_4 FROM (select mr.column_3, mts.column_4 FROM tab2 mr, tab3 mi, tab4 mts where mr.column_3 = mi.column_4 and mts.column_5 = mi.column_6 and mts.column_7 = mi.column_8 group by column_3,column_4) group by column_3) rm on mr.column_3 = rm.column_3 where 1=1 ORDER BY MR.column_9 DESC ; select * from table(dbms_xplan.display_cursor(null,1,'ADVANCED ALLSTATS LAST PEEKED_BINDS')); Plan hash value: 3940027804 ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | | 28078 (100)| | 11024 |00:00:22.08 | 4601K| | | | | 1 | SORT AGGREGATE | | 11024 | 1 | 21 | | | | 11024 |00:00:18.04 | 4409K| | | | |* 2 | TABLE ACCESS FULL | tab3 | 11024 | 3 | 63 | | 115 (1)| 00:00:02 | 29420 |00:00:18.00 | 4409K| | | | | 3 | SORT ORDER BY | | 1 | 11024 | 22M| 28M| 28078 (1)| 00:05:37 | 11024 |00:00:22.08 | 4601K| 2320K| 704K| 2062K (0)| |* 4 | HASH JOIN OUTER | | 1 | 11024 | 22M| | 23127 (1)| 00:04:38 | 11024 |00:00:03.96 | 192K| 2498K| 1081K| 2668K (0)| | 5 | TABLE ACCESS FULL | tab2 | 1 | 11024 | 1388K| | 120 (1)| 00:00:02 | 11024 |00:00:00.01 | 421 | | | | | 6 | VIEW | | 1 | 11024 | 21M| | 23007 (1)| 00:04:37 | 11024 |00:00:03.89 | 191K| | | | | 7 | SORT GROUP BY | | 1 | 11024 | 495K| | 23007 (1)| 00:04:37 | 11024 |00:00:03.76 | 180K| 2037K| 712K| 1810K (0)| | 8 | VIEW | VM_NWVW_0 | 1 | 29420 | 1321K| | 23007 (1)| 00:04:37 | 15841 |00:00:01.93 | 48387 | | | | | 9 | HASH GROUP BY | | 1 | 29420 | 4395K| 4624K| 23007 (1)| 00:04:37 | 15841 |00:00:01.93 | 48387 | 3001K| 1501K| 2975K (0)| |* 10 | HASH JOIN | | 1 | 29420 | 4395K| | 22009 (1)| 00:04:25 | 29420 |00:00:01.90 | 48387 | 1741K| 1741K| 1966K (0)| | 11 | TABLE ACCESS FULL | tab2 | 1 | 11024 | 204K| | 119 (0)| 00:00:02 | 11024 |00:00:00.01 | 420 | | | | |* 12 | HASH JOIN | | 1 | 29420 | 3849K| 2392K| 21889 (1)| 00:04:23 | 29420 |00:00:01.87 | 47967 | 3944K| 1214K| 4635K (0)| | 13 | TABLE ACCESS FULL| tab3 | 1 | 29420 | 2039K| | 115 (1)| 00:00:02 | 29420 |00:00:00.01 | 401 | | | | | 14 | TABLE ACCESS FULL| tab4 | 1 | 2428K| 145M| | 13012 (1)| 00:02:37 | 2428K|00:00:01.11 | 47566 | | | | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- (SELECT SUM(MMI.column_1) FROM tab3 MMI WHERE MMI.column_4=MR.column_3) set timing on create index xx.xx on xx.tab3 (column_4,column_1) parallel 6 online;
alter index xx.xx parallel 1;
也就是说这个执行计划第二步骤，执行了11024次全表扫描，将这个步骤创建索引走索引，效果提升很明显了！
6）生产库创建索引，对比执行效率
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | | 28078 (100)| | 11024 |00:00:03.28 | 192K| 123 | | | | | 1 | SORT AGGREGATE | | 11024 | 1 | 21 | | | | 11024 |00:00:00.10 | 932 | 123 | | | | |* 2 | INDEX RANGE SCAN | ind | 11024 | 3 | 63 | | 2 (0)| 00:00:01 | 29420 |00:00:00.07 | 932 | 123 | | | | | 3 | SORT ORDER BY | | 1 | 11024 | 22M| 28M| 28078 (1)| 00:05:37 | 11024 |00:00:03.28 | 192K| 123 | 2320K| 704K| 2062K (0)| |* 4 | HASH JOIN OUTER | | 1 | 11024 | 22M| | 23127 (1)| 00:04:38 | 11024 |00:00:03.13 | 192K| 0 | 2498K| 1081K| 2673K (0)| | 5 | TABLE ACCESS FULL | tab2 | 1 | 11024 | 1388K| | 120 (1)| 00:00:02 | 11024 |00:00:00.01 | 420 | 0 | | | | | 6 | VIEW | | 1 | 11024 | 21M| | 23007 (1)| 00:04:37 | 11024 |00:00:03.08 | 191K| 0 | | | | | 7 | SORT GROUP BY | | 1 | 11024 | 495K| | 23007 (1)| 00:04:37 | 11024 |00:00:02.96 | 180K| 0 | 2037K| 712K| 1810K (0)| | 8 | VIEW | VM_NWVW_0 | 1 | 29420 | 1321K| | 23007 (1)| 00:04:37 | 15841 |00:00:01.26 | 48385 | 0 | | | | | 9 | HASH GROUP BY | | 1 | 29420 | 4395K| 4624K| 23007 (1)| 00:04:37 | 15841 |00:00:01.25 | 48385 | 0 | 3001K| 1501K| 2997K (0)| |* 10 | HASH JOIN | | 1 | 29420 | 4395K| | 22009 (1)| 00:04:25 | 29420 |00:00:01.23 | 48385 | 0 | 1741K| 1741K| 1964K (0)| | 11 | TABLE ACCESS FULL | tab2 | 1 | 11024 | 204K| | 119 (0)| 00:00:02 | 11024 |00:00:00.01 | 420 | 0 | | | | |* 12 | HASH JOIN | | 1 | 29420 | 3849K| 2392K| 21889 (1)| 00:04:23 | 29420 |00:00:01.20 | 47965 | 0 | 3944K| 1214K| 4632K (0)| | 13 | TABLE ACCESS FULL| tab3 | 1 | 29420 | 2039K| | 115 (1)| 00:00:02 | 29420 |00:00:00.01 | 400 | 0 | | | | | 14 | TABLE ACCESS FULL| tab4 | 1 | 2428K | 145M| | 13012 (1)| 00:02:37 | 2428K|00:00:00.51 | 47565 | 0 | | | | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
主库1s内，dg 3s； 比原来主库15s,dg 24s 降低了极大！
当然本例中，SQL存在很大的优化空间，本次只是使用索引，将sql执行消耗最大的问题处理了，SQL执行效率得到明显提升，满足客户需求。
