標簽：com class http blog div code img style java javascript string
oracle的optimizer會對一些sql語句進行查詢轉換，比如：
下面讲讲遇到的in list转化优化的案例:
create table test( col1 varchar2(12) col2 number ext varchar2(4000) ); create index test_ind on test(user_id, col2); create sequence seq_test cache 200;
第一步：准備一些測試數據（10個線程隨機的插入數據）：
#!/bin/sh for((i=1;i<=$1;i++)) do /home/oracle/insert.sh & done ####################################################### #!/bin/sh . /home/oracle/.bash_profile sqlplus -S /nolog<<EOF conn test/ali88 declare type arraylist is table of varchar2(20 byte); arr_user arraylist; ran number; begin arr_user := arraylist();arr_user.extend(3);arr_user(1):=‘xpchild001‘; arr_user(2):=‘xpchild002‘;arr_user(3):=‘xpchild003‘; ran :=dbms_random.value(1,3); while(1>0) loop insert into test(col1,col2,ext)values(arr_user(ran),seq_test.nextval,dbms_random.string(‘|‘, 300)); commit; DBMS_LOCK.SLEEP(0.05); end loop; end; / EOF
下面看這個語句的執行計劃：
delete from test t where col1 =:1 and col2 in ( :2 , :3 , :4 , :5 , :6 , :7 , :8 , :9 , :10 , :11 , :12 , :13 , :14 , :15 , :16 , :17 , :18 , :19 , :20 , :21); -------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 4 | 948 | 5 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| test | 4 | 948 | 5 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | test_ind | 4 | | 2 (0)| 00:00:01 | -------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("col1"=:1) filter("col2"=TO_NUMBER(:2) OR "col2"=TO_NUMBER(:3) OR ......... "col2"=TO_NUMBER(:21))
分析：對于test_ind(col1,col2)這樣的組合索引，oracle的優化器使用了access+filter的掃描方式，而對于熱點表，或者col1存在大量記錄的時候，
這樣的掃描會從col1找到最小的col2,順著leaf節點的鏈表，找到col2的最大值的區間裏，進行filter，看下autotrace後的結果：
Statistics ---------------------------------------------------------- 12389 consistent gets 20 rows
這裏掃描了col1=:1前導列的所有leaf塊，所以盡管只有20條記錄，卻有12389的邏輯讀。
下面對這個sql進行inlist的查詢轉換：
ops$admin@orcl>alter session set optimizer_index_caching=100; delete /*+ use_concat*/from test t where col1 =:1 and col2 in ( :2 , :3 , :4 , :5 , :6 , :7 , :8 , :9 , :10 , :11 , :12 , :13 , :14 , :15 , :16 , :17 , :18 , :19 , :20 , :21); --------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 4 | 948 | 3 (0)| 00:00:01 | | 1 | INLIST ITERATOR | | | | | | | 2 | TABLE ACCESS BY INDEX ROWID| test | 4 | 948 | 3 (0)| 00:00:01 | |* 3 | INDEX RANGE SCAN | test_ind | 4 | | 0 (0)| 00:00:01 | --------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("col1"=:1 AND ("col2"=TO_NUMBER(:2) OR "col2"=TO_NUMBER(:3) OR "col2"=TO_NUMBER(:4) OR "col2"=TO_NUMBER(:5) OR "col2"=TO_NUMBER(:6) OR ...... /*+ BEGIN_OUTLINE_DATA NUM_INDEX_KEYS(@"DEL$1" "T"@"DEL$1" "test_ind" 2) INDEX(@"DEL$1" "T"@"DEL$1" ("test"."col1" "test"."col2")) OUTLINE_LEAF(@"DEL$1") ALL_ROWS OPT_PARAM(‘optimizer_index_caching‘ 100) OPT_PARAM(‘_gby_hash_aggregation_enabled‘ ‘false‘) OPT_PARAM(‘_optim_peek_user_binds‘ ‘false‘) OPT_PARAM(‘_index_join_enabled‘ ‘false‘) OPT_PARAM(‘query_rewrite_enabled‘ ‘false‘) OPTIMIZER_FEATURES_ENABLE(‘10.2.0.4‘) IGNORE_OPTIM_EMBEDDED_HINTS END_OUTLINE_DATA */
注意：oracle的优化器会对in
列表的查询转换为or的查询，使用use_concat的hint提示，使oracle可以把or的操作转化为concatenate的union
all操作。
但是這裏如果想要轉化爲inlist的eterator操作，必須還要調整optimizer_index_caching，可以在system或者session級別，optimizer_index_caching的值的範圍
是0到100，表示的是百分比，這個參數影響oracle優化器在選擇index時的cost計算，這裏設置成100，表示掃描過的root，branch節點塊都cache在buffer裏，所以每次
iterate从root到branch再到leaf节点时，oracle优化器认为cost会比较小，倾向于使用inlist
iterator。
看一下autotrace後的結果：
Statistics ---------------------------------------------------------- 81 consistent gets 20 rows
即每条记录平均三个逻辑读，从root->branch->leaf->table block。
如果在生産環境中可以使用profile來固定上面的執行計劃，這樣不用更改任何的代碼邏輯:
declare v_hints sys.sqlprof_attr; sql_fulltext clob; begin select SQL_FULLTEXT into sql_fulltext from v$sqlarea where sql_id = ‘xxxxxx‘; v_hints := sys.sqlprof_attr(‘NUM_INDEX_KEYS(@"DEL$1" "T"@"DEL$1" "test_ind" 2)‘, ‘INDEX(@"DEL$1" "T"@"DEL$1" ("test"."col1"‘, ‘"test"."col2"))‘, ‘OUTLINE_LEAF(@"DEL$1")‘, ‘ALL_ROWS‘, ‘OPT_PARAM(‘‘optimizer_index_caching‘‘ 100)‘); dbms_sqltune.import_sql_profile(sql_fulltext, v_hints, ‘test‘, force_match => true); end; /
oracle查詢轉換_inlist轉換,布布扣,bubuko.com
標簽：com class http blog div code img style java javascript string
原文地址：http://www.cnblogs.com/xpchild/p/3694975.html
