[20120104]穩定一條sql語句的執行計劃.txt

[20120104]穩定一條sql語句的執行計劃.txt

http://www.itpub.net/thread-1495845-1-1.html

http://space.itpub.net/267265/viewspace-723066

ORACLE8I更新11G R2後，查詢系統視圖特别慢

我的測試版本:

SQL> select * from v$version where rownumBANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

我修改了sql語句，執行如下:

SELECT    f.constraint_name, f.owner, f.r_owner, p.table_name, SYS.all_cons_columns.column_name, f.delete_rule

    FROM SYS.all_constraints f, SYS.all_cons_columns, SYS.all_constraints p

   WHERE f.owner = 'SCOTT'

     AND f.table_name = 'EMP'

     AND f.constraint_type = 'R'

     AND SYS.all_cons_columns.constraint_name = f.constraint_name

     AND SYS.all_cons_columns.table_name = 'EMP'

     AND SYS.all_cons_columns.owner = 'SCOTT'

     AND p.owner = f.r_owner

     AND p.constraint_name = f.r_constraint_name

ORDER BY f.constraint_name, SYS.all_cons_columns.POSITION;

11GR2下加入Edition-Based Redefinition，導緻執行計劃變的異常複雜。而一些PB程式莫名奇妙要執行這些程式之外的sql語句，導緻性

能不穩定，一些sql語句邏輯讀異常高。

我的測試很奇怪，就是第3次執行後突然變快了。仔細檢視發現使用了11G的新特性cardinality feedback，執行計劃發生了改變。這是這個原因導緻

執行計劃第3次發生改變。我現在的目的是穩定執行計劃。

SQL> select * from table(dbms_xplan.display_cursor(NVL('&1',NULL),1,'ALLSTATS LAST PEEKED_BINDS outline cost'));

--太長，僅僅記錄outline的輸出。主要使用outline參數。

  /*+

      BEGIN_OUTLINE_DATA

      IGNORE_OPTIM_EMBEDDED_HINTS

      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')

      DB_VERSION('11.2.0.1')

      ALL_ROWS

      OUTLINE_LEAF(@"SEL$9F331807")

      UNNEST(@"SEL$30")

      OUTLINE_LEAF(@"SEL$DD46E77B")

      MERGE(@"SEL$9CF1E98E")

      OUTLINE_LEAF(@"SEL$28")

      OUTLINE_LEAF(@"SEL$28294604")

      UNNEST(@"SEL$18")

      OUTLINE_LEAF(@"SEL$A422EF13")

      MERGE(@"SEL$5EC70623")

      OUTLINE_LEAF(@"SEL$16")

      OUTLINE_LEAF(@"SEL$F6521A81")

      UNNEST(@"SEL$10")

      OUTLINE_LEAF(@"SEL$5ED1C707")

      MERGE(@"SEL$61262C81")

      OUTLINE_LEAF(@"SEL$8")

      OUTLINE_LEAF(@"SEL$6")

      OUTLINE_LEAF(@"SEL$9384AC1D")

      PUSH_PRED(@"SEL$94B70B9B" "RO"@"SEL$2" 52)

      OUTLINE_LEAF(@"SEL$26")

      OUTLINE_LEAF(@"SEL$B2256D11")

      PUSH_PRED(@"SEL$94B70B9B" "RO"@"SEL$22" 21)

      OUTLINE_LEAF(@"SEL$94B70B9B")

      MERGE(@"SEL$1FB6C052")

      MERGE(@"SEL$261A5DF9")

      MERGE(@"SEL$FF8A3B74")

      OUTLINE(@"SEL$29")

      OUTLINE(@"SEL$30")

      OUTLINE(@"SEL$31")

      OUTLINE(@"SEL$9CF1E98E")

      MERGE(@"SEL$33")

      OUTLINE(@"SEL$17")

      OUTLINE(@"SEL$18")

      OUTLINE(@"SEL$19")

      OUTLINE(@"SEL$5EC70623")

      MERGE(@"SEL$21")

      OUTLINE(@"SEL$9")

      OUTLINE(@"SEL$10")

      OUTLINE(@"SEL$11")

      OUTLINE(@"SEL$61262C81")

      MERGE(@"SEL$13")

      OUTLINE(@"SEL$5")

      OUTLINE(@"SEL$94B70B9B")

      OUTLINE(@"SEL$25")

      OUTLINE(@"SEL$6E71C6F6")

      OUTER_JOIN_TO_INNER(@"SEL$1")

      OUTLINE(@"SEL$1FB6C052")

      MERGE(@"SEL$15")

      OUTLINE(@"SEL$261A5DF9")

      MERGE(@"SEL$23")

      MERGE(@"SEL$24")

      MERGE(@"SEL$27")

      OUTLINE(@"SEL$FF8A3B74")

      MERGE(@"SEL$3")

      MERGE(@"SEL$4")

      MERGE(@"SEL$7")

      OUTLINE(@"SEL$32")

      OUTLINE(@"SEL$33")

      OUTLINE(@"SEL$20")

      OUTLINE(@"SEL$21")

      OUTLINE(@"SEL$12")

      OUTLINE(@"SEL$13")

      OUTLINE(@"SEL$1")

      OUTLINE(@"SEL$14")

      OUTLINE(@"SEL$15")

      OUTLINE(@"SEL$22")

      OUTLINE(@"SEL$23")

      OUTLINE(@"SEL$24")

      OUTLINE(@"SEL$27")

      OUTLINE(@"SEL$2")

      OUTLINE(@"SEL$3")

      OUTLINE(@"SEL$4")

      OUTLINE(@"SEL$7")

      INDEX_RS_ASC(@"SEL$94B70B9B" "U"@"SEL$14" ("USER$"."NAME"))

      FULL(@"SEL$94B70B9B" "U"@"SEL$3")

      FULL(@"SEL$94B70B9B" "OC"@"SEL$2")

      FULL(@"SEL$94B70B9B" "C"@"SEL$2")

      FULL(@"SEL$94B70B9B" "RC"@"SEL$2")

      INDEX_RS_ASC(@"SEL$94B70B9B" "C"@"SEL$14" ("CON$"."OWNER#" "CON$"."NAME"))

      INDEX_RS_ASC(@"SEL$94B70B9B" "O"@"SEL$7" ("OBJ$"."OBJ#" "OBJ$"."OWNER#" "OBJ$"."TYPE#"))

      INDEX(@"SEL$94B70B9B" "U"@"SEL$7" ("USER$"."USER#" "USER$"."TYPE#" "USER$"."SPARE1" "USER$"."SPARE2"))

      INDEX_RS_ASC(@"SEL$94B70B9B" "CD"@"SEL$14" ("CDEF$"."CON#"))

      INDEX(@"SEL$94B70B9B" "OI"@"SEL$2" ("OBJ$"."OBJ#" "OBJ$"."OWNER#" "OBJ$"."TYPE#"))

      INDEX_RS_ASC(@"SEL$94B70B9B" "CC"@"SEL$14" ("CCOL$"."CON#" "CCOL$"."COL#"))

      INDEX_RS_ASC(@"SEL$94B70B9B" "COL"@"SEL$14" ("COL$"."OBJ#" "COL$"."INTCOL#"))

      CLUSTER(@"SEL$94B70B9B" "AC"@"SEL$14")

      INDEX(@"SEL$94B70B9B" "UI"@"SEL$2" ("USER$"."USER#" "USER$"."TYPE#" "USER$"."SPARE1" "USER$"."SPARE2"))

      INDEX(@"SEL$94B70B9B" "U"@"SEL$4" "I_USER#")

      INDEX_RS_ASC(@"SEL$94B70B9B" "O"@"SEL$15" ("OBJ$"."OBJ#" "OBJ$"."OWNER#" "OBJ$"."TYPE#"))

      INDEX(@"SEL$94B70B9B" "U"@"SEL$15" ("USER$"."USER#" "USER$"."TYPE#" "USER$"."SPARE1" "USER$"."SPARE2"))

      FULL(@"SEL$94B70B9B" "U"@"SEL$23")

      INDEX_RS_ASC(@"SEL$94B70B9B" "OC"@"SEL$22" ("CON$"."OWNER#" "CON$"."NAME"))

      INDEX_RS_ASC(@"SEL$94B70B9B" "C"@"SEL$22" ("CDEF$"."CON#"))

      INDEX_RS_ASC(@"SEL$94B70B9B" "RC"@"SEL$22" ("CON$"."CON#"))

      INDEX(@"SEL$94B70B9B" "OI"@"SEL$22" ("OBJ$"."OBJ#" "OBJ$"."OWNER#" "OBJ$"."TYPE#"))

      INDEX(@"SEL$94B70B9B" "UI"@"SEL$22" ("USER$"."USER#" "USER$"."TYPE#" "USER$"."SPARE1" "USER$"."SPARE2"))

      INDEX_RS_ASC(@"SEL$94B70B9B" "O"@"SEL$27" ("OBJ$"."OBJ#" "OBJ$"."OWNER#" "OBJ$"."TYPE#"))

      INDEX(@"SEL$94B70B9B" "U"@"SEL$24" ("USER$"."USER#" "USER$"."TYPE#" "USER$"."SPARE1" "USER$"."SPARE2"))

      INDEX(@"SEL$94B70B9B" "U"@"SEL$27" ("USER$"."USER#" "USER$"."TYPE#" "USER$"."SPARE1" "USER$"."SPARE2"))

      NO_ACCESS(@"SEL$94B70B9B" "RO"@"SEL$22")

      NO_ACCESS(@"SEL$94B70B9B" "RO"@"SEL$2")

      LEADING(@"SEL$94B70B9B" "U"@"SEL$14" "U"@"SEL$3" "OC"@"SEL$2" "C"@"SEL$2" "RC"@"SEL$2" "C"@"SEL$14" "O"@"SEL$7" "U"@"SEL$7"

              "CD"@"SEL$14" "OI"@"SEL$2" "CC"@"SEL$14" "COL"@"SEL$14" "AC"@"SEL$14" "UI"@"SEL$2" "U"@"SEL$4" "O"@"SEL$15" "U"@"SEL$15"

              "U"@"SEL$23" "OC"@"SEL$22" "C"@"SEL$22" "RC"@"SEL$22" "OI"@"SEL$22" "UI"@"SEL$22" "O"@"SEL$27" "U"@"SEL$24" "U"@"SEL$27"

              "RO"@"SEL$22" "RO"@"SEL$2")

      USE_NL(@"SEL$94B70B9B" "U"@"SEL$3")

      USE_HASH(@"SEL$94B70B9B" "OC"@"SEL$2")

      USE_HASH(@"SEL$94B70B9B" "C"@"SEL$2")

      USE_HASH(@"SEL$94B70B9B" "RC"@"SEL$2")

      USE_NL(@"SEL$94B70B9B" "C"@"SEL$14")

      USE_NL(@"SEL$94B70B9B" "O"@"SEL$7")

      USE_NL(@"SEL$94B70B9B" "U"@"SEL$7")

      USE_NL(@"SEL$94B70B9B" "CD"@"SEL$14")

      USE_NL(@"SEL$94B70B9B" "OI"@"SEL$2")

      USE_NL(@"SEL$94B70B9B" "CC"@"SEL$14")

      USE_NL(@"SEL$94B70B9B" "COL"@"SEL$14")

      USE_NL(@"SEL$94B70B9B" "AC"@"SEL$14")

      USE_NL(@"SEL$94B70B9B" "UI"@"SEL$2")

      USE_NL(@"SEL$94B70B9B" "U"@"SEL$4")

      USE_NL(@"SEL$94B70B9B" "O"@"SEL$15")

      USE_NL(@"SEL$94B70B9B" "U"@"SEL$15")

      USE_HASH(@"SEL$94B70B9B" "U"@"SEL$23")

      USE_NL(@"SEL$94B70B9B" "OC"@"SEL$22")

      USE_NL(@"SEL$94B70B9B" "C"@"SEL$22")

      USE_NL(@"SEL$94B70B9B" "RC"@"SEL$22")

      USE_NL(@"SEL$94B70B9B" "OI"@"SEL$22")

      USE_NL(@"SEL$94B70B9B" "UI"@"SEL$22")

      USE_NL(@"SEL$94B70B9B" "O"@"SEL$27")

      USE_NL(@"SEL$94B70B9B" "U"@"SEL$24")

      USE_NL(@"SEL$94B70B9B" "U"@"SEL$27")

      USE_NL(@"SEL$94B70B9B" "RO"@"SEL$22")

      USE_NL(@"SEL$94B70B9B" "RO"@"SEL$2")

      SWAP_JOIN_INPUTS(@"SEL$94B70B9B" "C"@"SEL$2")

      INDEX(@"SEL$9384AC1D" "O"@"SEL$5" ("OBJ$"."OBJ#" "OBJ$"."OWNER#" "OBJ$"."TYPE#"))

      INDEX(@"SEL$9384AC1D" "U"@"SEL$5" ("USER$"."USER#" "USER$"."TYPE#" "USER$"."SPARE1" "USER$"."SPARE2"))

      LEADING(@"SEL$9384AC1D" "O"@"SEL$5" "U"@"SEL$5")

      USE_NL(@"SEL$9384AC1D" "U"@"SEL$5")

      INDEX(@"SEL$B2256D11" "O"@"SEL$25" ("OBJ$"."OBJ#" "OBJ$"."OWNER#" "OBJ$"."TYPE#"))

      INDEX(@"SEL$B2256D11" "U"@"SEL$25" ("USER$"."USER#" "USER$"."TYPE#" "USER$"."SPARE1" "USER$"."SPARE2"))

      LEADING(@"SEL$B2256D11" "O"@"SEL$25" "U"@"SEL$25")

      USE_NL(@"SEL$B2256D11" "U"@"SEL$25")

      INDEX(@"SEL$26" "U2"@"SEL$26" ("USER$"."USER#" "USER$"."TYPE#" "USER$"."SPARE1" "USER$"."SPARE2"))

      INDEX(@"SEL$26" "O2"@"SEL$26" ("OBJ$"."DATAOBJ#" "OBJ$"."TYPE#" "OBJ$"."OWNER#"))

      LEADING(@"SEL$26" "U2"@"SEL$26" "O2"@"SEL$26")

      USE_NL(@"SEL$26" "O2"@"SEL$26")

      INDEX(@"SEL$6" "U2"@"SEL$6" ("USER$"."USER#" "USER$"."TYPE#" "USER$"."SPARE1" "USER$"."SPARE2"))

      INDEX(@"SEL$6" "O2"@"SEL$6" ("OBJ$"."DATAOBJ#" "OBJ$"."TYPE#" "OBJ$"."OWNER#"))

      LEADING(@"SEL$6" "U2"@"SEL$6" "O2"@"SEL$6")

      USE_NL(@"SEL$6" "O2"@"SEL$6")

      INDEX(@"SEL$8" "U2"@"SEL$8" ("USER$"."USER#" "USER$"."TYPE#" "USER$"."SPARE1" "USER$"."SPARE2"))

      INDEX(@"SEL$8" "O2"@"SEL$8" ("OBJ$"."DATAOBJ#" "OBJ$"."TYPE#" "OBJ$"."OWNER#"))

      LEADING(@"SEL$8" "U2"@"SEL$8" "O2"@"SEL$8")

      USE_NL(@"SEL$8" "O2"@"SEL$8")

      FULL(@"SEL$5ED1C707" "X$KZSPR"@"SEL$13")

      INDEX(@"SEL$F6521A81" "OBJAUTH$"@"SEL$9" ("OBJAUTH$"."OBJ#" "OBJAUTH$"."GRANTOR#" "OBJAUTH$"."GRANTEE#" "OBJAUTH$"."PRIVILEGE#"

              "OBJAUTH$"."COL#"))

      FULL(@"SEL$F6521A81" "X$KZSRO"@"SEL$10")

      LEADING(@"SEL$F6521A81" "OBJAUTH$"@"SEL$9" "X$KZSRO"@"SEL$10")

      USE_HASH(@"SEL$F6521A81" "X$KZSRO"@"SEL$10")

      PX_JOIN_FILTER(@"SEL$F6521A81" "X$KZSRO"@"SEL$10")

      INDEX(@"SEL$16" "U2"@"SEL$16" ("USER$"."USER#" "USER$"."TYPE#" "USER$"."SPARE1" "USER$"."SPARE2"))

      INDEX(@"SEL$16" "O2"@"SEL$16" ("OBJ$"."DATAOBJ#" "OBJ$"."TYPE#" "OBJ$"."OWNER#"))

      LEADING(@"SEL$16" "U2"@"SEL$16" "O2"@"SEL$16")

      USE_NL(@"SEL$16" "O2"@"SEL$16")

      FULL(@"SEL$A422EF13" "X$KZSPR"@"SEL$21")

      INDEX(@"SEL$28294604" "OBJAUTH$"@"SEL$17" ("OBJAUTH$"."OBJ#" "OBJAUTH$"."GRANTOR#" "OBJAUTH$"."GRANTEE#" "OBJAUTH$"."PRIVILEGE#"

      FULL(@"SEL$28294604" "X$KZSRO"@"SEL$18")

      LEADING(@"SEL$28294604" "OBJAUTH$"@"SEL$17" "X$KZSRO"@"SEL$18")

      USE_HASH(@"SEL$28294604" "X$KZSRO"@"SEL$18")

      PX_JOIN_FILTER(@"SEL$28294604" "X$KZSRO"@"SEL$18")

      INDEX(@"SEL$28" "U2"@"SEL$28" ("USER$"."USER#" "USER$"."TYPE#" "USER$"."SPARE1" "USER$"."SPARE2"))

      INDEX(@"SEL$28" "O2"@"SEL$28" ("OBJ$"."DATAOBJ#" "OBJ$"."TYPE#" "OBJ$"."OWNER#"))

      LEADING(@"SEL$28" "U2"@"SEL$28" "O2"@"SEL$28")

      USE_NL(@"SEL$28" "O2"@"SEL$28")

      FULL(@"SEL$DD46E77B" "X$KZSPR"@"SEL$33")

      INDEX(@"SEL$9F331807" "OBJAUTH$"@"SEL$29" ("OBJAUTH$"."OBJ#" "OBJAUTH$"."GRANTOR#" "OBJAUTH$"."GRANTEE#" "OBJAUTH$"."PRIVILEGE#"

      FULL(@"SEL$9F331807" "X$KZSRO"@"SEL$30")

      LEADING(@"SEL$9F331807" "OBJAUTH$"@"SEL$29" "X$KZSRO"@"SEL$30")

      USE_HASH(@"SEL$9F331807" "X$KZSRO"@"SEL$30")

      PX_JOIN_FILTER(@"SEL$9F331807" "X$KZSRO"@"SEL$30")

      END_OUTLINE_DATA

  */

--編輯腳本如下：(注:主要是編輯sqlprof_attr中的内容，使用vim很容易完成。另外設定 force_match => TRUE，這樣其他相似的sql語

句也可以使用此執行計劃)

begin

dbms_sqltune.import_sql_profile(

   name => 'profile_laji',

   description => 'SQL profile created manually',

--   category => 'TEST',

   sql_text => q'[

ORDER BY f.constraint_name, SYS.all_cons_columns.POSITION

   ]',

   profile => sqlprof_attr(

     'ALL_ROWS',

     'OUTLINE_LEAF(@"SEL$9F331807")',

     'UNNEST(@"SEL$30")',

     'OUTLINE_LEAF(@"SEL$DD46E77B")',

     'MERGE(@"SEL$9CF1E98E")',

     'OUTLINE_LEAF(@"SEL$28")',

     'OUTLINE_LEAF(@"SEL$28294604")',

     'UNNEST(@"SEL$18")',

     'OUTLINE_LEAF(@"SEL$A422EF13")',

     'MERGE(@"SEL$5EC70623")',

     'OUTLINE_LEAF(@"SEL$16")',

     'OUTLINE_LEAF(@"SEL$F6521A81")',

     'UNNEST(@"SEL$10")',

     'OUTLINE_LEAF(@"SEL$5ED1C707")',

     'MERGE(@"SEL$61262C81")',

     'OUTLINE_LEAF(@"SEL$8")',

     'OUTLINE_LEAF(@"SEL$6")',

     'OUTLINE_LEAF(@"SEL$9384AC1D")',

     'PUSH_PRED(@"SEL$94B70B9B" "RO"@"SEL$2" 52)',

     'OUTLINE_LEAF(@"SEL$26")',

     'OUTLINE_LEAF(@"SEL$B2256D11")',

     'PUSH_PRED(@"SEL$94B70B9B" "RO"@"SEL$22" 21)',

     'OUTLINE_LEAF(@"SEL$94B70B9B")',

     'MERGE(@"SEL$1FB6C052")',

     'MERGE(@"SEL$261A5DF9")',

     'MERGE(@"SEL$FF8A3B74")',

     'OUTLINE(@"SEL$29")',

     'OUTLINE(@"SEL$30")',

     'OUTLINE(@"SEL$31")',

     'OUTLINE(@"SEL$9CF1E98E")',

     'MERGE(@"SEL$33")',

     'OUTLINE(@"SEL$17")',

     'OUTLINE(@"SEL$18")',

     'OUTLINE(@"SEL$19")',

     'OUTLINE(@"SEL$5EC70623")',

     'MERGE(@"SEL$21")',

     'OUTLINE(@"SEL$9")',

     'OUTLINE(@"SEL$10")',

     'OUTLINE(@"SEL$11")',

     'OUTLINE(@"SEL$61262C81")',

     'MERGE(@"SEL$13")',

     'OUTLINE(@"SEL$5")',

     'OUTLINE(@"SEL$94B70B9B")',

     'OUTLINE(@"SEL$25")',

     'OUTLINE(@"SEL$6E71C6F6")',

     'OUTER_JOIN_TO_INNER(@"SEL$1")',

     'OUTLINE(@"SEL$1FB6C052")',

     'MERGE(@"SEL$15")',

     'OUTLINE(@"SEL$261A5DF9")',

     'MERGE(@"SEL$23")',

     'MERGE(@"SEL$24")',

     'MERGE(@"SEL$27")',

     'OUTLINE(@"SEL$FF8A3B74")',

     'MERGE(@"SEL$3")',

     'MERGE(@"SEL$4")',

     'MERGE(@"SEL$7")',

     'OUTLINE(@"SEL$32")',

     'OUTLINE(@"SEL$33")',

     'OUTLINE(@"SEL$20")',

     'OUTLINE(@"SEL$21")',

     'OUTLINE(@"SEL$12")',

     'OUTLINE(@"SEL$13")',

     'OUTLINE(@"SEL$1")',

     'OUTLINE(@"SEL$14")',

     'OUTLINE(@"SEL$15")',

     'OUTLINE(@"SEL$22")',

     'OUTLINE(@"SEL$23")',

     'OUTLINE(@"SEL$24")',

     'OUTLINE(@"SEL$27")',

     'OUTLINE(@"SEL$2")',

     'OUTLINE(@"SEL$3")',

     'OUTLINE(@"SEL$4")',

     'OUTLINE(@"SEL$7")',

     'INDEX_RS_ASC(@"SEL$94B70B9B" "U"@"SEL$14" ("USER$"."NAME"))',

     'FULL(@"SEL$94B70B9B" "U"@"SEL$3")',

     'FULL(@"SEL$94B70B9B" "OC"@"SEL$2")',

     'FULL(@"SEL$94B70B9B" "C"@"SEL$2")',

     'FULL(@"SEL$94B70B9B" "RC"@"SEL$2")',

     'INDEX_RS_ASC(@"SEL$94B70B9B" "C"@"SEL$14" ("CON$"."OWNER#" "CON$"."NAME"))',

     'INDEX_RS_ASC(@"SEL$94B70B9B" "O"@"SEL$7" ("OBJ$"."OBJ#" "OBJ$"."OWNER#" "OBJ$"."TYPE#"))',

     'INDEX(@"SEL$94B70B9B" "U"@"SEL$7" ("USER$"."USER#" "USER$"."TYPE#" "USER$"."SPARE1" "USER$"."SPARE2"))',

     'INDEX_RS_ASC(@"SEL$94B70B9B" "CD"@"SEL$14" ("CDEF$"."CON#"))',

     'INDEX(@"SEL$94B70B9B" "OI"@"SEL$2" ("OBJ$"."OBJ#" "OBJ$"."OWNER#" "OBJ$"."TYPE#"))',

     'INDEX_RS_ASC(@"SEL$94B70B9B" "CC"@"SEL$14" ("CCOL$"."CON#" "CCOL$"."COL#"))',

     'INDEX_RS_ASC(@"SEL$94B70B9B" "COL"@"SEL$14" ("COL$"."OBJ#" "COL$"."INTCOL#"))',

     'CLUSTER(@"SEL$94B70B9B" "AC"@"SEL$14")',

     'INDEX(@"SEL$94B70B9B" "UI"@"SEL$2" ("USER$"."USER#" "USER$"."TYPE#" "USER$"."SPARE1" "USER$"."SPARE2"))',

     'INDEX(@"SEL$94B70B9B" "U"@"SEL$4" "I_USER#")',

     'INDEX_RS_ASC(@"SEL$94B70B9B" "O"@"SEL$15" ("OBJ$"."OBJ#" "OBJ$"."OWNER#" "OBJ$"."TYPE#"))',

     'INDEX(@"SEL$94B70B9B" "U"@"SEL$15" ("USER$"."USER#" "USER$"."TYPE#" "USER$"."SPARE1" "USER$"."SPARE2"))',

     'FULL(@"SEL$94B70B9B" "U"@"SEL$23")',

     'INDEX_RS_ASC(@"SEL$94B70B9B" "OC"@"SEL$22" ("CON$"."OWNER#" "CON$"."NAME"))',

     'INDEX_RS_ASC(@"SEL$94B70B9B" "C"@"SEL$22" ("CDEF$"."CON#"))',

     'INDEX_RS_ASC(@"SEL$94B70B9B" "RC"@"SEL$22" ("CON$"."CON#"))',

     'INDEX(@"SEL$94B70B9B" "OI"@"SEL$22" ("OBJ$"."OBJ#" "OBJ$"."OWNER#" "OBJ$"."TYPE#"))',

     'INDEX(@"SEL$94B70B9B" "UI"@"SEL$22" ("USER$"."USER#" "USER$"."TYPE#" "USER$"."SPARE1" "USER$"."SPARE2"))',

     'INDEX_RS_ASC(@"SEL$94B70B9B" "O"@"SEL$27" ("OBJ$"."OBJ#" "OBJ$"."OWNER#" "OBJ$"."TYPE#"))',

     'INDEX(@"SEL$94B70B9B" "U"@"SEL$24" ("USER$"."USER#" "USER$"."TYPE#" "USER$"."SPARE1" "USER$"."SPARE2"))',

     'INDEX(@"SEL$94B70B9B" "U"@"SEL$27" ("USER$"."USER#" "USER$"."TYPE#" "USER$"."SPARE1" "USER$"."SPARE2"))',

     'NO_ACCESS(@"SEL$94B70B9B" "RO"@"SEL$22")',

     'NO_ACCESS(@"SEL$94B70B9B" "RO"@"SEL$2")',

     'LEADING(@"SEL$94B70B9B" "U"@"SEL$14" "U"@"SEL$3" "OC"@"SEL$2" "C"@"SEL$2" "RC"@"SEL$2" "C"@"SEL$14" "O"@"SEL$7" "U"@"SEL$7"

              "RO"@"SEL$22" "RO"@"SEL$2")',

     'USE_NL(@"SEL$94B70B9B" "U"@"SEL$3")',

     'USE_HASH(@"SEL$94B70B9B" "OC"@"SEL$2")',

     'USE_HASH(@"SEL$94B70B9B" "C"@"SEL$2")',

     'USE_HASH(@"SEL$94B70B9B" "RC"@"SEL$2")',

     'USE_NL(@"SEL$94B70B9B" "C"@"SEL$14")',

     'USE_NL(@"SEL$94B70B9B" "O"@"SEL$7")',

     'USE_NL(@"SEL$94B70B9B" "U"@"SEL$7")',

     'USE_NL(@"SEL$94B70B9B" "CD"@"SEL$14")',

     'USE_NL(@"SEL$94B70B9B" "OI"@"SEL$2")',

     'USE_NL(@"SEL$94B70B9B" "CC"@"SEL$14")',

     'USE_NL(@"SEL$94B70B9B" "COL"@"SEL$14")',

     'USE_NL(@"SEL$94B70B9B" "AC"@"SEL$14")',

     'USE_NL(@"SEL$94B70B9B" "UI"@"SEL$2")',

     'USE_NL(@"SEL$94B70B9B" "U"@"SEL$4")',

     'USE_NL(@"SEL$94B70B9B" "O"@"SEL$15")',

     'USE_NL(@"SEL$94B70B9B" "U"@"SEL$15")',

     'USE_HASH(@"SEL$94B70B9B" "U"@"SEL$23")',

     'USE_NL(@"SEL$94B70B9B" "OC"@"SEL$22")',

     'USE_NL(@"SEL$94B70B9B" "C"@"SEL$22")',

     'USE_NL(@"SEL$94B70B9B" "RC"@"SEL$22")',

     'USE_NL(@"SEL$94B70B9B" "OI"@"SEL$22")',

     'USE_NL(@"SEL$94B70B9B" "UI"@"SEL$22")',

     'USE_NL(@"SEL$94B70B9B" "O"@"SEL$27")',

     'USE_NL(@"SEL$94B70B9B" "U"@"SEL$24")',

     'USE_NL(@"SEL$94B70B9B" "U"@"SEL$27")',

     'USE_NL(@"SEL$94B70B9B" "RO"@"SEL$22")',

     'USE_NL(@"SEL$94B70B9B" "RO"@"SEL$2")',

     'SWAP_JOIN_INPUTS(@"SEL$94B70B9B" "C"@"SEL$2")',

     'INDEX(@"SEL$9384AC1D" "O"@"SEL$5" ("OBJ$"."OBJ#" "OBJ$"."OWNER#" "OBJ$"."TYPE#"))',

     'INDEX(@"SEL$9384AC1D" "U"@"SEL$5" ("USER$"."USER#" "USER$"."TYPE#" "USER$"."SPARE1" "USER$"."SPARE2"))',

     'LEADING(@"SEL$9384AC1D" "O"@"SEL$5" "U"@"SEL$5")',

     'USE_NL(@"SEL$9384AC1D" "U"@"SEL$5")',

     'INDEX(@"SEL$B2256D11" "O"@"SEL$25" ("OBJ$"."OBJ#" "OBJ$"."OWNER#" "OBJ$"."TYPE#"))',

     'INDEX(@"SEL$B2256D11" "U"@"SEL$25" ("USER$"."USER#" "USER$"."TYPE#" "USER$"."SPARE1" "USER$"."SPARE2"))',

     'LEADING(@"SEL$B2256D11" "O"@"SEL$25" "U"@"SEL$25")',

     'USE_NL(@"SEL$B2256D11" "U"@"SEL$25")',

     'INDEX(@"SEL$26" "U2"@"SEL$26" ("USER$"."USER#" "USER$"."TYPE#" "USER$"."SPARE1" "USER$"."SPARE2"))',

     'INDEX(@"SEL$26" "O2"@"SEL$26" ("OBJ$"."DATAOBJ#" "OBJ$"."TYPE#" "OBJ$"."OWNER#"))',

     'LEADING(@"SEL$26" "U2"@"SEL$26" "O2"@"SEL$26")',

     'USE_NL(@"SEL$26" "O2"@"SEL$26")',

     'INDEX(@"SEL$6" "U2"@"SEL$6" ("USER$"."USER#" "USER$"."TYPE#" "USER$"."SPARE1" "USER$"."SPARE2"))',

     'INDEX(@"SEL$6" "O2"@"SEL$6" ("OBJ$"."DATAOBJ#" "OBJ$"."TYPE#" "OBJ$"."OWNER#"))',

     'LEADING(@"SEL$6" "U2"@"SEL$6" "O2"@"SEL$6")',

     'USE_NL(@"SEL$6" "O2"@"SEL$6")',

     'INDEX(@"SEL$8" "U2"@"SEL$8" ("USER$"."USER#" "USER$"."TYPE#" "USER$"."SPARE1" "USER$"."SPARE2"))',

     'INDEX(@"SEL$8" "O2"@"SEL$8" ("OBJ$"."DATAOBJ#" "OBJ$"."TYPE#" "OBJ$"."OWNER#"))',

     'LEADING(@"SEL$8" "U2"@"SEL$8" "O2"@"SEL$8")',

     'USE_NL(@"SEL$8" "O2"@"SEL$8")',

     'FULL(@"SEL$5ED1C707" "X$KZSPR"@"SEL$13")',

     'INDEX(@"SEL$F6521A81" "OBJAUTH$"@"SEL$9" ("OBJAUTH$"."OBJ#" "OBJAUTH$"."GRANTOR#" "OBJAUTH$"."GRANTEE#" "OBJAUTH$"."PRIVILEGE#" "OBJAUTH$"."COL#"))',

     'FULL(@"SEL$F6521A81" "X$KZSRO"@"SEL$10")',

     'LEADING(@"SEL$F6521A81" "OBJAUTH$"@"SEL$9" "X$KZSRO"@"SEL$10")',

     'USE_HASH(@"SEL$F6521A81" "X$KZSRO"@"SEL$10")',

     'PX_JOIN_FILTER(@"SEL$F6521A81" "X$KZSRO"@"SEL$10")',

     'INDEX(@"SEL$16" "U2"@"SEL$16" ("USER$"."USER#" "USER$"."TYPE#" "USER$"."SPARE1" "USER$"."SPARE2"))',

     'INDEX(@"SEL$16" "O2"@"SEL$16" ("OBJ$"."DATAOBJ#" "OBJ$"."TYPE#" "OBJ$"."OWNER#"))',

     'LEADING(@"SEL$16" "U2"@"SEL$16" "O2"@"SEL$16")',

     'USE_NL(@"SEL$16" "O2"@"SEL$16")',

     'FULL(@"SEL$A422EF13" "X$KZSPR"@"SEL$21")',

     'INDEX(@"SEL$28294604" "OBJAUTH$"@"SEL$17" ("OBJAUTH$"."OBJ#" "OBJAUTH$"."GRANTOR#" "OBJAUTH$"."GRANTEE#" "OBJAUTH$"."PRIVILEGE#" "OBJAUTH$"."COL#"))',

     'FULL(@"SEL$28294604" "X$KZSRO"@"SEL$18")',

     'LEADING(@"SEL$28294604" "OBJAUTH$"@"SEL$17" "X$KZSRO"@"SEL$18")',

     'USE_HASH(@"SEL$28294604" "X$KZSRO"@"SEL$18")',

     'PX_JOIN_FILTER(@"SEL$28294604" "X$KZSRO"@"SEL$18")',

     'INDEX(@"SEL$28" "U2"@"SEL$28" ("USER$"."USER#" "USER$"."TYPE#" "USER$"."SPARE1" "USER$"."SPARE2"))',

     'INDEX(@"SEL$28" "O2"@"SEL$28" ("OBJ$"."DATAOBJ#" "OBJ$"."TYPE#" "OBJ$"."OWNER#"))',

     'LEADING(@"SEL$28" "U2"@"SEL$28" "O2"@"SEL$28")',

     'USE_NL(@"SEL$28" "O2"@"SEL$28")',

     'FULL(@"SEL$DD46E77B" "X$KZSPR"@"SEL$33")',

     'INDEX(@"SEL$9F331807" "OBJAUTH$"@"SEL$29" ("OBJAUTH$"."OBJ#" "OBJAUTH$"."GRANTOR#" "OBJAUTH$"."GRANTEE#" "OBJAUTH$"."PRIVILEGE#" "OBJAUTH$"."COL#"))',

     'FULL(@"SEL$9F331807" "X$KZSRO"@"SEL$30")',

     'LEADING(@"SEL$9F331807" "OBJAUTH$"@"SEL$29" "X$KZSRO"@"SEL$30")',

     'USE_HASH(@"SEL$9F331807" "X$KZSRO"@"SEL$30")',

     'PX_JOIN_FILTER(@"SEL$9F331807" "X$KZSRO"@"SEL$30")'

   ),

   replace => FALSE,

   force_match => TRUE

);

end;

/

使用force_match => TRUE,這樣其他相似的SQL語句也有效。

select    f.constraint_name, f.owner, f.r_owner, p.table_name, SYS.all_cons_columns.column_name, f.delete_rule

   WHERE f.owner = 'HR'

     AND f.table_name = 'EMPLOYEES'

     AND SYS.all_cons_columns.table_name = 'EMPLOYEES'

     AND SYS.all_cons_columns.owner = 'HR'

--如果不需要sql profile，删除指令如下：

exec DBMS_SQLTUNE.drop_sql_profile(name=>'profile_laji');