优化

今天有个客户数据库负载比较高，查询发现sql用了 like %ABC% 这种写法，并且应用方面还无法改写成等值条件，下面进行测试。 （客户生产表的这个列唯一值比较高，并且表的列很多有三十多个，如果列比较少或者like %ABC%或取的值比较多的话下面的建议不适用。） 首先创建测试表 create table tt as select * from dba_objects; SQL> alter system flush buffer_cache; System altered. SQL> SELECT OBJECT_ID, OBJECT_TYPE, OBJECT_NAME FROM TT WHERE OBJECT_NAME LIKE '%ZC%'; 44000 JAVA CLASS oracle/net/resolver/EZConnectResolver 47203 JAVA CLASS oracle/jdbc/driver/TSTZCopyingBinder 47205 JAVA CLASS oracle/jdbc/driver/TSLTZCopyingBinder Execution Plan ---------------------------------------------------------- Plan hash value: 264906180 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 3664 | 178K| 395 (1)| 00:00:01 | |* 1 | TABLE ACCESS FULL| TT | 3664 | 178K| 395 (1)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("OBJECT_NAME" LIKE '%ZC%' AND "OBJECT_NAME" IS NOT NULL) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 1423 consistent gets 1419 physical reads 0 redo size 915 bytes sent via SQL*Net to client 448 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 3 rows processed 未作处理前的consistent gets 大约 1400 进行sql改写，并创建索引，测试性能。 SQL> create index t_name on TT(OBJECT_NAME); Index created. SQL> exec dbms_stats.gather_table_stats('ZC','TT',no_invalidate=>false); PL/SQL procedure successfully completed. SQL> alter system flush buffer_cache; System altered. SQL> SELECT OBJECT_ID, OBJECT_TYPE, OBJECT_NAME FROM TT WHERE OBJECT_NAME IN (SELECT OBJECT_NAME FROM TT WHERE OBJECT_NAME LIKE '%ZC%'); 47205 JAVA CLASS oracle/jdbc/driver/TSLTZCopyingBinder 47203 JAVA CLASS oracle/jdbc/driver/TSTZCopyingBinder 44000 JAVA CLASS oracle/net/resolver/EZConnectResolver Execution Plan ---------------------------------------------------------- Plan hash value: 1780297486 -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 3664 | 304K| 523 (1)| 00:00:01 | |* 1 | HASH JOIN RIGHT SEMI | | 3664 | 304K| 523 (1)| 00:00:01 | |* 2 | INDEX FAST FULL SCAN| T_NAME | 3664 | 125K| 128 (1)| 00:00:01 | |* 3 | TABLE ACCESS FULL | TT | 3664 | 178K| 395 (1)| 00:00:01 | -------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("OBJECT_NAME"="OBJECT_NAME") 2 - filter("OBJECT_NAME" LIKE '%ZC%' AND "OBJECT_NAME" IS NOT NULL) 3 - filter("OBJECT_NAME" LIKE '%ZC%' AND "OBJECT_NAME" IS NOT NULL) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 487 consistent gets 485 physical reads 0 redo size 915 bytes sent via SQL*Net to client 506 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 3 rows processed sql 改写后 大约490，还是有比较明显的效果的。 或者下面这种写法： select /*+no_merge(b) use_nl(t b)*/ t.OBJECT_ID, t.OBJECT_TYPE, t.OBJECT_NAME from TT t, (select rowid from TT a where a.OBJECT_NAME like '%ZC%') b where t.rowid = b.rowid; Elapsed: 00:00:00.04 Execution Plan ---------------------------------------------------------- Plan hash value: 3989423677 -------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 3664 | 264K| 3792 (1)| 00:00:01 | | 1 | NESTED LOOPS | | 3664 | 264K| 3792 (1)| 00:00:01 | | 2 | VIEW | | 3664 | 43968 | 128 (1)| 00:00:01 | |* 3 | INDEX FAST FULL SCAN | T_NAME | 3664 | 168K| 128 (1)| 00:00:01 | | 4 | TABLE ACCESS BY USER ROWID| TT | 1 | 62 | 1 (0)| 00:00:01 | -------------------------------------------------------------------------------------- Predicate Information (identified by operation id): -------------------------------------------------- 3 - filter("A"."OBJECT_NAME" LIKE '%ZC%' AND "A"."OBJECT_NAME" IS NOT NULL) Hint Report (identified by operation id / Query Block Name / Object Alias): Total hints for statement: 1 (U - Unused (1)) --------------------------------------------------------------------------- 1 - SEL$1 / B@SEL$1 U - use_nl(t b) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 479 consistent gets 0 physical reads 0 redo size 915 bytes sent via SQL*Net to client 537 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 3 rows processed consistent gets也比较低。 测试instr函数性能 SQL> alter system flush buffer_cache; SELECT OBJECT_ID, OBJECT_TYPE, OBJECT_NAME FROM TT WHERE instr(OBJECT_NAME, 'ZC') >0; System altered. Execution Plan ---------------------------------------------------------- Plan hash value: 264906180 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 3664 | 178K| 395 (1)| 00:00:01 | |* 1 | TABLE ACCESS FULL| TT | 3664 | 178K| 395 (1)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(INSTR("OBJECT_NAME",'ZC')>0) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 1423 consistent gets 1419 physical reads 0 redo size 915 bytes sent via SQL*Net to client 447 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 3 rows processed instr和like %ZC% 差距不大，但是如果带入的变量ZC是个定值，我们可以考虑创建基于函数的索引进行优化。 SQL> create index idx_tt2 on tt(instr(OBJECT_NAME,'ZC')); Index created. SQL> exec dbms_stats.gather_table_stats('ZC','TT',no_invalidate=>false); PL/SQL procedure successfully completed. SQL> set linesize 1000 SQL> SELECT /*+ index (TT IDX_TT2 )*/ OBJECT_ID, OBJECT_TYPE, OBJECT_NAME FROM TT WHERE INSTR(OBJECT_NAME, 'ZC') >0; Execution Plan ---------------------------------------------------------- Plan hash value: 1462875822 --------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 54953 | 2844K| 1167 (1)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| TT | 54953 | 2844K| 1167 (1)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IDX_TT2 | 54953 | | 101 (0)| 00:00:01 | --------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access(INSTR("OBJECT_NAME",'ZC')>0) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 5 consistent gets 0 physical reads 0 redo size 915 bytes sent via SQL*Net to client 474 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 3 rows processed consistent gets 下降特别明显。 SELECT /*+ index (TT IDX_TT2 )*/ OBJECT_ID, OBJECT_TYPE, OBJECT_NAME FROM TT WHERE INSTR(OBJECT_NAME, 'ZC') >0，测试的时候发现，这个sql不加hint 不会走索引，怀疑是clustering_factor的原因。 exec dbms_stats.SET_INDEX_STATS('ZC','IDX_TT2', clstfct=>10); SQL> SELECT OBJECT_ID,OBJECT_NAME,OBJECT_TYPE FROM TT WHERE instr(OBJECT_NAME, 'ZC') >0; Elapsed: 00:00:00.01 Execution Plan ---------------------------------------------------------- Plan hash value: 1462875822 --------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 54953 | 2844K| 110 (1)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| TT | 54953 | 2844K| 110 (1)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IDX_TT2 | 54953 | | 101 (0)| 00:00:01 | --------------------------------------------------------------------------------------- Predicate Information (identified by operation id): -------------------------------------------------- 2 - access(INSTR("OBJECT_NAME",'ZC')>0) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 5 consistent gets 0 physical reads 0 redo size 915 bytes sent via SQL*Net to client 446 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 3 rows processed