[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")
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');