利用sqlprofile最佳化goldengate中慢的sql語句，sql語句最佳化

利用sqlprofile最佳化goldengate中慢的sql語句，sql語句最佳化

HP unix中監控到一個進程佔用cpu較高，後來發現是gg中的語句執行計畫有問題，導致cpu偏高。由於gg中的語句不能更改，怎樣才能改變不能更改的語句的執行計畫呢，這裡可以採用sqlprofile來最佳化。[szggs1@szodsd01] $ top -hSystem: szodsd01                                      Wed Nov  5 10:17:16 2014Load averages: 0.42, 0.47, 0.491007 processes: 599 sleeping, 408 runningCpu states: (avg) LOAD   USER   NICE    SYS   IDLE  BLOCK  SWAIT   INTR   SSYS 0.42  19.8%   0.0%   5.2%  75.0%   0.0%   0.0%   0.0%   0.0%System Page Size: 4KbytesMemory: 40107992K (35984460K) real, 53292056K (48597616K) virtual, 12423080K free  Page# 1/44CPU TTY  PID USERNAME PRI NI   SIZE    RES STATE    TIME %WCPU  %CPU COMMAND       3   ?  1697 oracle   178 20 25460M  6420K run   1780:54 95.10 94.93 oracleszodsd1      11   ? 28561 oracle   178 20 25461M  7236K sleep   55:06  8.01  8.00 oracleszodsd1         SQL> @getsql_spidEnter value for ospid: 1697ADDR----------------C00000037A0351C8SID_SERIAL SQL_ID_NUM      P_NAME          P_VALUE         SQL_ID        USERNAME   PROGRAM         EVENT                STATUS   BLOCKING_SESSION---------- --------------- --------------- --------------- ------------- ---------- --------------- -------------------- -------- ----------------3152,32913 6khsffxsn05tg,0 driver id       1413697536      6khsffxsn05tg GGS        replicat@szodsd SQL*Net message from ACTIVE                           #bytes          1                                        01 (TNS V1-V3)   client                                           0SQL_FULLTEXT--------------------------------------------------------------------------------DELETE FROM "SPS_SZ"."TB_XX_XXXX_XXXX_XXX"  WHERE "LOG_ID" = :b0 AND "PLAN_ID" =:b1 AND "LOG_NO" = :b2 AND "PLAN_RESULT" = :b3 AND "STAFF_CODE" = :b4 AND "WORK_STAFF" = :b5 AND "OP_DATE" = :b6 AND "OP_TIME" = :b7 AND "LOG_NOTES" = :b8 AND "UNIT_ID" = :b9 AND ROWNUM = 1--上面由replicat和GGS可見是gg的語句造成的--查看gg中TB_XX_XXXX_XXXX_XXX對應的進程為RSZSPSESQL> @getggmapSession altered.Enter value for ttable: TB_XX_XXXX_XXXX_XXXEnter value for towner: SPS_SZSTATS                                    LOG_TIME            PNAME           SOURCE_OWNER         SOURCE_TABLENAME     TARGET_OWNER         TARGET_TABLENAME     GGS_OWNER---------------------------------------- ------------------- --------------- -------------------- -------------------- -------------------- -------------------- ------------stats RSZSPSE table *TB_XX_XXXX_XXXX_XXX 2014-11-05 00:00:18 RSZSPSE         SPS_SZ_INST          TB_XX_XXXX_XXXX_XXX  SPS_SZ               TB_XX_XXXX_XXXX_XXX   szggs11 row selected.SQL>--果然延遲了很長時間，6個鐘GGSCI (szodsd01) 2> info RSZSPSEREPLICAT   RSZSPSE   Last Started 2014-11-01 14:00   Status RUNNINGCheckpoint Lag       06:14:03 (updated 00:02:40 ago)Log Read Checkpoint  File /odsd/szggs01/ggdata/sz/rszspsa/dirdat/si248293                     2014-11-05 04:08:32.005523  RBA 1349428--查看執行計畫和outline資訊，後面用sqlprofile最佳化SQL> explain plan for  2  DELETE FROM "SPS_SZ"."TB_XX_XXXX_XXXX_XXX"  3   WHERE "LOG_ID" = :b0  4     AND "PLAN_ID" = :b1  5     AND "LOG_NO" = :b2  6     AND "PLAN_RESULT" = :b3  7     AND "STAFF_CODE" = :b4  8     AND "WORK_STAFF" = :b5  9     AND "OP_DATE" = :b6 10     AND "OP_TIME" = :b7 11     AND "LOG_NOTES" = :b8 12     AND "UNIT_ID" = :b9 13     AND ROWNUM = 1;Explained.SQL> @getplan'general,outline,starts'Enter value for plan type:outlinePLAN_TABLE_OUTPUT----------------------------------------------------------------------------------------------------------------Plan hash value: 692294925---------------------------------------------------------------------------------------------------------------| Id  | Operation                          | Name                     | Rows  | Bytes | Cost (%CPU)| Time     |---------------------------------------------------------------------------------------------------------------|   0 | DELETE STATEMENT                   |                          |     1 |   352 | 16760   (2)| 00:03:55 ||   1 |  DELETE                            | TB_XX_XXXX_XXXX_XXX      |       |       |            |       ||*  2 |   COUNT STOPKEY                    |                          |       |       |            |       ||*  3 |    TABLE ACCESS BY INDEX ROWID     | TB_XX_XXXX_XXXX_XXX      |     1 |   352 | 16760   (2)| 00:03:55 ||   4 |     BITMAP CONVERSION TO ROWIDS    |                          |       |       |            |       ||   5 |      BITMAP AND                    |                          |       |       |            |       ||   6 |       BITMAP CONVERSION FROM ROWIDS|                          |       |       |            |       ||*  7 |        INDEX RANGE SCAN            | IX_AA_AAA_AAAAAAA        |  1757K|       |    14  (79)| 00:00:01 ||   8 |       BITMAP CONVERSION FROM ROWIDS|                          |       |       |            |       ||*  9 |        INDEX RANGE SCAN            | IX_AA_AAA_AAAAAAA_PLANID |  1757K|       |    14  (79)| 00:00:01 ||  10 |       BITMAP CONVERSION FROM ROWIDS|                          |       |       |            |       ||* 11 |        INDEX RANGE SCAN            | IX_BB_BBB_BBBB_BBBBBB    |  1757K|       |  2787   (1)| 00:00:40 ||  12 |       BITMAP CONVERSION FROM ROWIDS|                          |       |       |            |       ||* 13 |        INDEX RANGE SCAN            | IX_CC_CCC_CCCC_CCCCCC    |  1757K|       | 13793   (1)| 00:03:14 |---------------------------------------------------------------------------------------------------------------Query Block Name / Object Alias (identified by operation id):-------------------------------------------------------------   1 - DEL$1   3 - DEL$1 / TB_XX_XXXX_XXXX_XXX@DEL$1Outline Data-------------  /*+      BEGIN_OUTLINE_DATA      BITMAP_TREE(@"DEL$1" "TB_XX_XXXX_XXXX_XXX"@"DEL$1" AND(("TB_XX_XXXX_XXXX_XXX"."LOG_ID")              ("TB_XX_XXXX_XXXX_XXX"."PLAN_ID") ("TB_XX_XXXX_XXXX_XXX"."OP_DATE")              ("TB_XX_XXXX_XXXX_XXX"."PLAN_RESULT")))      OUTLINE_LEAF(@"DEL$1")      ALL_ROWS      OPTIMIZER_FEATURES_ENABLE('10.2.0.4')      IGNORE_OPTIM_EMBEDDED_HINTS      END_OUTLINE_DATA  */Predicate Information (identified by operation id):---------------------------------------------------   2 - filter(ROWNUM=1)   3 - filter("STAFF_CODE"=:B4 AND "WORK_STAFF"=:B5 AND "OP_TIME"=:B7 AND "LOG_NOTES"=:B8 AND              "LOG_NO"=TO_NUMBER(:B2) AND "UNIT_ID"=TO_NUMBER(:B9))   7 - access("LOG_ID"=TO_NUMBER(:B0))   9 - access("PLAN_ID"=TO_NUMBER(:B1))  11 - access("OP_DATE"=:B6)  13 - access("PLAN_RESULT"=TO_NUMBER(:B3))Note-----   - dynamic sampling used for this statement--執行計畫中一大堆的BITMAP關鍵字，很容易誤以為這幾個索引是bitmap索引，其實是普通索引來的。--擷取表和索引的相關資訊OWNER                TABLE_NAME                     OBJECT_TYPE  STA LAST_ANALYZED-------------------- ------------------------------ ------------ --- -------------------SPS_SZ               TB_XX_XXXX_XXXX_XXX             TABLE--上表TB_XX_XXXX_XXXX_XXX統計資訊到期OWNER                SEGMENT_NAME                   SEGMENT_TYPE           Size(Mb)-------------------- ------------------------------ -------------------- ----------SPS_SZ               IX_CC_CCC_CCCC_CCCCCC          INDEX                  8434.625SPS_SZ               IX_AA_AAA_AAAAAAA              INDEX                 11176.625SPS_SZ               IX_AA_AAA_AAAAAAA_PLANID       INDEX                11484.4375SPS_SZ               IX_BB_BBB_BBBB_BBBBBB          INDEX                   13138.5SPS_SZ               TB_XX_XXXX_XXXX_XXX            TABLE                     38322--索引加起來都要比表大OWNER                INDEX_NAME                     TABLE_NAME                     PAR UNIQUENES DEGREE     INDEX_TYPE LEAF_BLOCKS     BLEVEL CLUSTERING_FACTOR          %-------------------- ------------------------------ ------------------------------ --- --------- ---------- ---------- ----------- ---------- ----------------- ----------SPS_SZ               IX_AA_AAA_AAAAAAA              TB_XX_XXXX_XXXX_XXX             NO  NONUNIQUE 1       NORMAL     284214          2         185996490        100SPS_SZ               IX_CC_CCC_CCCC_CCCCCC          TB_XX_XXXX_XXXX_XXX             NO  NONUNIQUE 1       NORMAL     206567          2           6963531 .000006899SPS_SZ               IX_BB_BBB_BBBB_BBBBBB          TB_XX_XXXX_XXXX_XXX             NO  NONUNIQUE 1       NORMAL     299483          2           6898441 .000049673SPS_SZ               IX_AA_AAA_AAAAAAA_PLANID       TB_XX_XXXX_XXXX_XXX             NO  NONUNIQUE 1       NORMAL     284214          2          31263628 19.8460594--由上可知，索引IX_AA_AAA_AAAAAAA的叢集因子100，distinct_keys/NUM_ROWS*100=100，選擇性非常好，類似於主鍵，基本返回1行。--其他3個索引非常垃圾，完全可以不用。如果走4個索引的話，體積都大於全表掃描了，而且INDEX RANGE SCAN 是單塊讀，全表是多塊讀，還不如走全表了。--由上面Outline Data資訊裡面得到如下資訊：BITMAP_TREE(@"DEL$1" "TB_XX_XXXX_XXXX_XXX"@"DEL$1" AND(("TB_XX_XXXX_XXXX_XXX"."LOG_ID")              ("TB_XX_XXXX_XXXX_XXX"."PLAN_ID") ("TB_XX_XXXX_XXXX_XXX"."OP_DATE")              ("TB_XX_XXXX_XXXX_XXX"."PLAN_RESULT")))--這裡走bitmap肯定是錯的，這裡可以走索引IX_AA_AAA_AAAAAAA，但如何得到走IX_AA_AAA_AAAAAAA的hint呢？SQL> explain plan for  2  DELETE /*+index(a,IX_AA_AAA_AAAAAAA)*/ FROM "SPS_SZ"."TB_XX_XXXX_XXXX_XXX" a  3   WHERE "LOG_ID" = :b0  4     AND "PLAN_ID" = :b1  5     AND "LOG_NO" = :b2  6     AND "PLAN_RESULT" = :b3  7     AND "STAFF_CODE" = :b4  8     AND "WORK_STAFF" = :b5  9     AND "OP_DATE" = :b6 10     AND "OP_TIME" = :b7 11     AND "LOG_NOTES" = :b8 12     AND "UNIT_ID" = :b9 13     AND ROWNUM = 1;Explained.SQL> @getplan'general,outline,starts'Enter value for plan type:outlinePLAN_TABLE_OUTPUT-------------------------------------------------------------------------------------------------------------------------Plan hash value: 2457304297----------------------------------------------------------------------------------------------------| Id  | Operation                     | Name               | Rows  | Bytes | Cost (%CPU)| Time     |----------------------------------------------------------------------------------------------------|   0 | DELETE STATEMENT              |                    |     1 |   352 |   744K  (1)| 02:53:43 ||   1 |  DELETE                       | TB_XX_XXXX_XXXX_XXX|       |       |            |          ||*  2 |   COUNT STOPKEY               |                    |       |       |            |          ||*  3 |    TABLE ACCESS BY INDEX ROWID| TB_XX_XXXX_XXXX_XXX|     1 |   352 |   744K  (1)| 02:53:43 ||*  4 |     INDEX RANGE SCAN          | IX_AA_AAA_AAAAAAA  |  1757K|       |    14  (79)| 00:00:01 |----------------------------------------------------------------------------------------------------Query Block Name / Object Alias (identified by operation id):-------------------------------------------------------------   1 - DEL$1   3 - DEL$1 / A@DEL$1   4 - DEL$1 / A@DEL$1Outline Data-------------  /*+      BEGIN_OUTLINE_DATA      INDEX_RS_ASC(@"DEL$1" "A"@"DEL$1" ("TB_XX_XXXX_XXXX_XXX"."LOG_ID"))            --要用INDEX_RS_ASC這個hint，把A替換成表名就是了      OUTLINE_LEAF(@"DEL$1")      ALL_ROWS      OPTIMIZER_FEATURES_ENABLE('10.2.0.4')      IGNORE_OPTIM_EMBEDDED_HINTS      END_OUTLINE_DATA  */Predicate Information (identified by operation id):---------------------------------------------------   2 - filter(ROWNUM=1)   3 - filter("STAFF_CODE"=:B4 AND "WORK_STAFF"=:B5 AND "OP_DATE"=:B6 AND "OP_TIME"=:B7 AND              "LOG_NOTES"=:B8 AND "PLAN_ID"=TO_NUMBER(:B1) AND "LOG_NO"=TO_NUMBER(:B2) AND              "PLAN_RESULT"=TO_NUMBER(:B3) AND "UNIT_ID"=TO_NUMBER(:B9))   4 - access("LOG_ID"=TO_NUMBER(:B0))Note-----   - dynamic sampling used for this statement        --使用profile              declare  v_hints sys.sqlprof_attr;begin  v_hints := sys.sqlprof_attr('INDEX_RS_ASC(@"DEL$1" "TB_XX_XXXX_XXXX_XXX"@"DEL$1" ("TB_XX_XXXX_XXXX_XXX"."LOG_ID"))');    dbms_sqltune.import_sql_profile('DELETE FROM "SPS_SZ"."TB_XX_XXXX_XXXX_XXX"  WHERE "LOG_ID" = :b0 AND "PLAN_ID" = :b1 AND "LOG_NO" = :b2 AND "PLAN_RESULT" = :b3 AND "STAFF_CODE" = :b4 AND "WORK_STAFF" = :b5 AND "OP_DATE" = :b6 AND "OP_TIME" = :b7 AND "LOG_NOTES" = :b8 AND "UNIT_ID" = :b9 AND ROWNUM = 1',                                   v_hints,                                  'SPS_SZ_TB_XX_XXXX_XXXX_XXX',                                   force_match => true);end;/--驗證執行計畫是否只走IX_AA_AAA_AAAAAAA：SQL> explain plan for  2  DELETE  FROM "SPS_SZ"."TB_XX_XXXX_XXXX_XXX"  3   WHERE "LOG_ID" = :b0  4     AND "PLAN_ID" = :b1  5     AND "LOG_NO" = :b2  6     AND "PLAN_RESULT" = :b3  7     AND "STAFF_CODE" = :b4  8     AND "WORK_STAFF" = :b5  9     AND "OP_DATE" = :b6 10     AND "OP_TIME" = :b7 11     AND "LOG_NOTES" = :b8 12     AND "UNIT_ID" = :b9 13     AND ROWNUM = 1;Explained.SQL> @getplan'general,outline,starts'Enter value for plan type:PLAN_TABLE_OUTPUT-------------------------------------------------------------------------------------------------------------------Plan hash value: 2457304297----------------------------------------------------------------------------------------------------| Id  | Operation                     | Name               | Rows  | Bytes | Cost (%CPU)| Time     |----------------------------------------------------------------------------------------------------|   0 | DELETE STATEMENT              |                    |     1 |   352 |   744K  (1)| 02:53:43 ||   1 |  DELETE                       | TB_XX_XXXX_XXXX_XXX |       |       |            |          ||*  2 |   COUNT STOPKEY               |                    |       |       |            |          ||*  3 |    TABLE ACCESS BY INDEX ROWID| TB_XX_XXXX_XXXX_XXX |     1 |   352 |   744K  (1)| 02:53:43 ||*  4 |     INDEX RANGE SCAN          | IX_AA_AAA_AAAAAAA  |  1757K|       |    14  (79)| 00:00:01 |----------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - filter(ROWNUM=1)   3 - filter("STAFF_CODE"=:B4 AND "WORK_STAFF"=:B5 AND "OP_DATE"=:B6 AND "OP_TIME"=:B7 AND              "LOG_NOTES"=:B8 AND "PLAN_ID"=TO_NUMBER(:B1) AND "LOG_NO"=TO_NUMBER(:B2) AND              "PLAN_RESULT"=TO_NUMBER(:B3) AND "UNIT_ID"=TO_NUMBER(:B9))   4 - access("LOG_ID"=TO_NUMBER(:B0))Note-----   - dynamic sampling used for this statement   - SQL profile "SPS_SZ_TB_XX_XXXX_XXXX_XXX" used for this statement   --執行計畫對了。雖然糾正了執行計畫，但畢竟索引IX_AA_AAA_AAAAAAA有11G，cpu還是有點偏高，掃描起來還是有點慢的，只有delete操作完了後才會降下來。但是現在效率上已經好很多了。--相關的視圖   SQL> select name,created from dba_sql_profiles order by created;NAME                           CREATED------------------------------ -------------------SPS_SZ_TB_XX_XXXX_XXXX_XXX      2014-11-05 10:49:585 rows selected.SQL> col attr_val for a80SQL> select name,attr_val from dba_sql_profiles a, sys.sqlprof$attr b  2   where a.signature = b.signature  3     and a.name = 'SPS_SZ_TB_XX_XXXX_XXXX_XXX';NAME                           ATTR_VAL------------------------------ --------------------------------------------------------------------------------SPS_SZ_TB_XX_XXXX_XXXX_XXX      INDEX_RS_ASC(@"DEL$1" "TB_XX_XXXX_XXXX_XXX"@"DEL$1" ("TB_XX_XXXX_XXXX_XXX"."LOG_ID                               "))1 row selected. --@指令碼@getsql_spidset lines 200 pages 200 long 100000col USERNAME for a10col PROGRAM for a15col EVENT for a20col sid_serial for a10col sql_id_num for a15col p_name for a15col p_value for a15col addr new_value addrcol sql_id new_value sql_idSELECT addr FROM gv$process c WHERE c.spid = &ospid;  select a.sid ||','|| a.SERIAL# sid_serial,a.SQL_ID ||','|| a.sql_child_number sql_id_num,P1TEXT||chr(10)||P2TEXT||chr(10)||P3TEXT p_name,a.p1||chr(10)||a.p2||chr(10)||a.p3 p_value,decode(a.SQL_ID,null,a.PREV_SQL_ID,a.SQL_ID) sql_id,a.USERNAME,a.PROGRAM,a.EVENT,a.STATUS,a.BLOCKING_SESSION from gv$session a  where a.PADDR = '&addr';select c.SQL_FULLTEXT from gv$sqlarea c where c.SQL_ID='&sql_id';cl col--@getplanset feedback offpro 'general,outline,starts'proacc type prompt 'Enter value for plan type:' default 'general'select * from table(dbms_xplan.display) where '&&type'='general';select * from table(dbms_xplan.display(null, null,'advanced -projection')) where '&&type'='outline';SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST')) where '&&type'='starts';set feedback onundef type

sql語句最佳化oracle中，這兩個sql語句，由於資料量非常的大有幾G查詢非常的慢幫忙最佳化

/** try this: */

-- 1)
select
c.characvalueid,
c.sequencekey,
l.resourceid
from tr_characvalue c
join tr_resourcecharac l on c.characvalueid = l.characvalueid
where 1=1
and (1=2
or exists (select 1
FROM tr_teammember
WHERE teamid = 20160
and resourceid = l.resourceid
)
or exists (select 1
FROM tr_resource
WHERE genresteamid = 20160
and resourceid = l.resourceid
)
)
order by c.characid, c.sequencekey

-- 2)
select u.resourceid, u.userfieldid
from tr_resourcevalue u
join tr_userfield p on u.userfieldid = p.userfieldid
where 1=1
and (1=2
or exists (select 1
FROM tr_teammember
WHERE teamid = 20160
and resourceid = u.resourceid
)
or exists (select 1
FROM tr_resource
WHERE genresteamid = 20160
and resourceid = u.resourceid
)
)
order by u.userfieldid
 
在SQL語句的where子句中對存在索引的列使用函數時，為何Oracle最佳化器會忽略掉這些索引？

where子句中對欄位進行函數操作，這將導致引擎放棄使用索引而進行全表掃描。
因為SQL只有在運行時才會解析局部變數，但最佳化程式不能將訪問計劃的選擇延遲到運行時；它必須在編譯時間進行選擇。然而，如果在編譯時間建立訪問計劃，變數的值還是未知的，因而無法作為索引選擇的輸入項。