[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 rownum<=1;
BANNER
--------------------------------------------------------------------------------
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")
MERGE(@"SEL$1FB6C052")
MERGE(@"SEL$261A5DF9")
MERGE(@"SEL$FF8A3B74")
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#"
"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")
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'[
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
]',
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")',
'MERGE(@"SEL$1FB6C052")',
'MERGE(@"SEL$261A5DF9")',
'MERGE(@"SEL$FF8A3B74")',
'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#" "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
FROM SYS.all_constraints f, SYS.all_cons_columns, SYS.all_constraints p
WHERE f.owner = 'HR'
AND f.table_name = 'EMPLOYEES'
AND f.constraint_type = 'R'
AND SYS.all_cons_columns.constraint_name = f.constraint_name
AND SYS.all_cons_columns.table_name = 'EMPLOYEES'
AND SYS.all_cons_columns.owner = 'HR'
AND p.owner = f.r_owner
AND p.constraint_name = f.r_constraint_name
ORDER BY f.constraint_name, SYS.all_cons_columns.POSITION;
--如果不需要sql profile，刪除命令如下：
exec DBMS_SQLTUNE.drop_sql_profile(name=>'profile_laji');
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 rownum<=1;
BANNER
--------------------------------------------------------------------------------
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")
MERGE(@"SEL$1FB6C052")
MERGE(@"SEL$261A5DF9")
MERGE(@"SEL$FF8A3B74")
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#"
"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")
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'[
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
]',
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")',
'MERGE(@"SEL$1FB6C052")',
'MERGE(@"SEL$261A5DF9")',
'MERGE(@"SEL$FF8A3B74")',
'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#" "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
FROM SYS.all_constraints f, SYS.all_cons_columns, SYS.all_constraints p
WHERE f.owner = 'HR'
AND f.table_name = 'EMPLOYEES'
AND f.constraint_type = 'R'
AND SYS.all_cons_columns.constraint_name = f.constraint_name
AND SYS.all_cons_columns.table_name = 'EMPLOYEES'
AND SYS.all_cons_columns.owner = 'HR'
AND p.owner = f.r_owner
AND p.constraint_name = f.r_constraint_name
ORDER BY f.constraint_name, SYS.all_cons_columns.POSITION;
--如果不需要sql profile，刪除命令如下：
exec DBMS_SQLTUNE.drop_sql_profile(name=>'profile_laji');
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-752117/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 一條SQL語句的執行計劃變化探究SQL
- 清除SQL語句的執行計劃SQL
- 一條sql語句的執行過程SQL
- 通過分析SQL語句的執行計劃優化SQL語句SQL優化
- 一條update SQL語句是如何執行的SQL
- 一條SQL更新語句是如何執行的SQL
- 一條SQL更新語句是如何執行的？SQL
- 一條更新的SQL語句是如何執行的？SQL
- 根據SQL Id獲得SQL語句的執行計劃SQL
- 一條 SQL 查詢語句是如何執行的？SQL
- 一條SQL語句在MySQL中如何執行的MySql
- MyBatis 一次執行多條SQL語句MyBatisSQL
- 一條SQL語句的執行計劃變化探究（r10筆記第9天）SQL筆記
- 獲得目標SQL語句執行計劃的方法SQL
- 通過鎖定表的統計資訊來穩定sql的執行計劃SQL
- 通過分析SQL語句的執行計劃優化SQL 二SQL優化
- 一條sql語句在mysql中是如何執行的MySql
- 一條 SQL 語句在 MySQL 中是如何執行的？MySql
- 執行計劃不穩定的原因分析
- 通過分析SQL語句的執行計劃優化SQL(總結)SQL優化
- 一條更新語句的執行流程
- sql最佳化：使用儲存提綱穩定sql執行計劃SQL
- MySQL日誌（一條sql更新語句是如何執行的）MySql
- 獲取oracle sql語句詳細些執行計劃OracleSQL
- spark sql語句效能最佳化及執行計劃SparkSQL
- 執行一條sql語句都經歷了什麼？SQL
- 執行一條 SQL 語句，期間發生了什麼？SQL
- 一條簡單的sql語句執行15天的原因分析SQL
- MySQL cron定時執行SQL語句MySql
- 透過分析SQL語句的執行計劃最佳化SQL(總結)SQL
- 獲取oracle正在處於等待狀態的sql語句的執行計劃的語句OracleSQL
- 一條查詢語句的執行流程
- MySQL系列之一條SQL查詢語句的執行過程MySql
- 解決: 執行計劃變了,一條語句要跑20小時候.
- [轉]透過分析SQL語句的執行計劃最佳化SQL(總結)SQL
- 使用dbms_xplan包來獲得sql語句的執行計劃SQL
- mysql sql語句執行超時設定MySql
- 一文讀懂一條 SQL 查詢語句是如何執行的SQL