[20141014]11G长时间分析问题.txt

[20141014]11G长时间分析问题.txt

http://www.itpub.net/thread-1495845-1-1.html
http://space.itpub.net/267265/viewspace-723066
http://blog.itpub.net/267265/viewspace-752117/

--我以前看到这条sql语句,我做了一些简单修改.

1. 问题提出:
SCOTT@test> @ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.3.0     Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
--我上次测试是11.2.0.1

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;

--为了测试方便,我建立文件a.sql,便于多次执行.第1次执行很慢,我的测试机器开始2次需要将近4秒.第3次看执行计划使用cardinality
--feedback,快了许多.(好像第2次就已经使用,但是执行很慢).

--编辑脚本如下：(注:主要是编辑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;
/

--如果不需要sql profile，删除命令如下：
-- exec DBMS_SQLTUNE.drop_sql_profile(name=>'profile_laji');

--但是我刷新共享池后,再次执行:

SCOTT@test> set timing on
SCOTT@test> alter system flush SHARED_POOL;
System altered.
Elapsed: 00:00:00.15

SCOTT@test> @a
CONSTRAINT_NAME                OWNER  R_OWNER   TABLE_NAME COLUMN_NAME          DELETE_RU
------------------------------ ------ --------- ---------- -------------------- ---------
FK_DEPTNO                      SCOTT  SCOTT     DEPT       DEPTNO               NO ACTION

Elapsed: 00:00:01.05
SCOTT@test> @a
CONSTRAINT_NAME                OWNER  R_OWNER   TABLE_NAME COLUMN_NAME          DELETE_RU
------------------------------ ------ --------- ---------- -------------------- ---------
FK_DEPTNO                      SCOTT  SCOTT     DEPT       DEPTNO               NO ACTION
Elapsed: 00:00:00.12

--注意看第1次执行依旧需要1秒以上,排除了其他因素,应该是分析时间"太长".占了将近1秒.先删除这条语句的sql profile.
SCOTT@test> exec DBMS_SQLTUNE.drop_sql_profile(name=>'profile_laji');
PL/SQL procedure successfully completed.

2.做一次10046跟踪看看:
SCOTT@test> exec DBMS_SQLTUNE.drop_sql_profile(name=>'profile_laji');
PL/SQL procedure successfully completed.

SCOTT@test> alter system flush SHARED_POOL;
System altered.

SCOTT@test> @10046on 12
old   1: alter session set events '10046 trace name context forever, level &1'
new   1: alter session set events '10046 trace name context forever, level 12'
Session altered.

SCOTT@test> set timing on
SCOTT@test> @a

CONSTRAINT_NAME                OWNER  R_OWNER   TABLE_NAME COLUMN_NAME          DELETE_RU
------------------------------ ------ --------- ---------- -------------------- ---------
FK_DEPTNO                      SCOTT  SCOTT     DEPT       DEPTNO               NO ACTION

Elapsed: 00:00:03.82
SCOTT@test> @a
CONSTRAINT_NAME                OWNER  R_OWNER   TABLE_NAME COLUMN_NAME          DELETE_RU
------------------------------ ------ --------- ---------- -------------------- ---------
FK_DEPTNO                      SCOTT  SCOTT     DEPT       DEPTNO               NO ACTION

Elapsed: 00:00:03.78
SCOTT@test> @a
CONSTRAINT_NAME                OWNER  R_OWNER   TABLE_NAME COLUMN_NAME          DELETE_RU
------------------------------ ------ --------- ---------- -------------------- ---------
FK_DEPTNO                      SCOTT  SCOTT     DEPT       DEPTNO               NO ACTION

Elapsed: 00:00:00.02
SCOTT@test> @10046off
Session altered.
Elapsed: 00:00:00.00

--可以tkprof报表,摘要如下:
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

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      3.75       3.75          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.01       0.01          0        329          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      3.76       3.76          0        329          0           1

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

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      3.75       3.75          0          0          0           0
Execute      2      0.00       0.00          0          0          0           0
Fetch        4      0.03       0.03          0        766          0           2
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        8      3.78       3.79          0        766          0           2

--通过报表可以发现第1次执行时间主要花在Parse上,需要3.75秒.而第2,3次结合前面计算时间的输出看,可以推断第2次执行依旧花在parse
--上.仅仅第3次执行因为cardinality feedback的执行计划已经在第2次生产(也就是执行计划已经在共享池里面),以后在执行就比较快了.

--实际上可以从上面的3次执行还可以推断,cardinality feedback的执行并不是很快,因为第1,2次主要时间浪费在parse上.

3.关闭_optimizer_use_feedback看看.
SCOTT@test> @hide _optimizer_use_feedback
old  10:  and lower(a.ksppinm) like lower('%&1%')
new  10:  and lower(a.ksppinm) like lower('%_optimizer_use_feedback%')
NAME                      DESCRIPTION             DEFAULT_VALUE          SESSION_VALUE          SYSTEM_VALUE
------------------------- ----------------------- ---------------------- ---------------------- ----------------------
_optimizer_use_feedback   optimizer use feedback  TRUE                   TRUE                   TRUE
Elapsed: 00:00:00.02

SCOTT@test> alter session set "_optimizer_use_feedback"=false;
Session altered.

SCOTT@test> alter system flush SHARED_POOL;
System altered.

SCOTT@test> set timing on
SCOTT@test> @a
CONSTRAINT_NAME                OWNER  R_OWNER    TABLE_NAME COLUMN_NAME          DELETE_RU
------------------------------ ------ ---------- ---------- -------------------- ---------
FK_DEPTNO                      SCOTT  SCOTT      DEPT       DEPTNO               NO ACTION

Elapsed: 00:00:03.78
SCOTT@test> @a
CONSTRAINT_NAME                OWNER  R_OWNER    TABLE_NAME COLUMN_NAME          DELETE_RU
------------------------------ ------ ---------- ---------- -------------------- ---------
FK_DEPTNO                      SCOTT  SCOTT      DEPT       DEPTNO               NO ACTION

Elapsed: 00:00:00.02
SCOTT@test> @a
CONSTRAINT_NAME                OWNER  R_OWNER    TABLE_NAME COLUMN_NAME          DELETE_RU
------------------------------ ------ ---------- ---------- -------------------- ---------
FK_DEPTNO                      SCOTT  SCOTT      DEPT       DEPTNO               NO ACTION
Elapsed: 00:00:00.01

--可以发现第2次执行就很快,再次证明是分析时间"太长".

SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
SQL_ID  gm1mvmk81qta6, child number 0
-------------------------------------
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

Plan hash value: 4203805911
...

Note
-----
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level

--可以看到并没有cardinality feedback方面的信息.而第1次执行3.78,第2,3次执行就很快了,说明以前的判断出现问题,主要浪费的时间
--在分析(parse)上.

4.如果看执行计划,特别复杂,连接N多表.一定是执行计划判断连接顺序时耗费太多的资源.加上11GR2下加入Edition-Based
Redefinition,导致更加复杂对比10g.再来看看修改optimizer_features_enable参数的情况:

--执行前退出,避免一些参数的影响,并且刷新共享池.

scott@test> alter system flush shared_pool;
System altered.

SCOTT@test> set timing on
SCOTT@test> alter session set optimizer_features_enable='9.2.0.8';
Session altered.

Elapsed: 00:00:00.01
SCOTT@test> @a

CONSTRAINT_NAME                OWNER  R_OWNER  TABLE_NAME COLUMN_NAME          DELETE_RU
------------------------------ ------ -------- ---------- -------------------- ---------
FK_DEPTNO                      SCOTT  SCOTT    DEPT       DEPTNO               NO ACTION

Elapsed: 00:00:00.75
SCOTT@test> @a
CONSTRAINT_NAME                OWNER  R_OWNER  TABLE_NAME COLUMN_NAME          DELETE_RU
------------------------------ ------ -------- ---------- -------------------- ---------
FK_DEPTNO                      SCOTT  SCOTT    DEPT       DEPTNO               NO ACTION

Elapsed: 00:00:00.01
SCOTT@test> @a
CONSTRAINT_NAME                OWNER  R_OWNER  TABLE_NAME COLUMN_NAME          DELETE_RU
------------------------------ ------ -------- ---------- -------------------- ---------
FK_DEPTNO                      SCOTT  SCOTT    DEPT       DEPTNO               NO ACTION
Elapsed: 00:00:00.02
-- 执行0.75秒,相对前面快了许多.

SCOTT@test> alter session set optimizer_features_enable='11119.0.0';
ERROR:
ORA-00096: invalid value 11119.0.0 for parameter optimizer_features_enable, must be from among 11.2.0.3.1, 11.2.0.3, 11.2.0.2, 11.2.0.1, 11.1.0.7, 11.1.0.6, 10.2.0.5, 10.2.0.4, 10.2.0.3, 10.2.0.2, 10.2.0.1, 10.1.0.5, 10.1.0.4, 10.1.0.3, 10.1.0,
9.2.0.8, 9.2.0, 9.0.1, 9.0.0, 8.1.7, 8.1.6, 8.1.5, 8.1.4, 8.1.3, 8.1.0, 8.0.7, 8.0.6, 8.0.5, 8.0.4, 8.0.3, 8.0.0

--我做了optimizer_features_enable等于其他的测试:
alter system flush shared_pool;alter session set optimizer_features_enable='11.2.0.3.1';@a;
alter system flush shared_pool;alter session set optimizer_features_enable='11.2.0.3';@a;
alter system flush shared_pool;alter session set optimizer_features_enable='11.2.0.2';@a;
alter system flush shared_pool;alter session set optimizer_features_enable='11.2.0.1';@a;
alter system flush shared_pool;alter session set optimizer_features_enable='11.1.0.7';@a;
alter system flush shared_pool;alter session set optimizer_features_enable='11.1.0.6';@a;
alter system flush shared_pool;alter session set optimizer_features_enable='10.2.0.5';@a;
alter system flush shared_pool;alter session set optimizer_features_enable='10.2.0.4';@a;
alter system flush shared_pool;alter session set optimizer_features_enable='10.2.0.3';@a;
alter system flush shared_pool;alter session set optimizer_features_enable='10.2.0.2';@a;
alter system flush shared_pool;alter session set optimizer_features_enable='10.2.0.1';@a;
alter system flush shared_pool;alter session set optimizer_features_enable='10.1.0.5';@a;
alter system flush shared_pool;alter session set optimizer_features_enable='10.1.0.4';@a;
alter system flush shared_pool;alter session set optimizer_features_enable='10.1.0.3';@a;
alter system flush shared_pool;alter session set optimizer_features_enable='10.1.0';@a;
alter system flush shared_pool;alter session set optimizer_features_enable='9.2.0.8';@a;
alter system flush shared_pool;alter session set optimizer_features_enable='9.2.0';@a;
alter system flush shared_pool;alter session set optimizer_features_enable='9.0.1';@a;
alter system flush shared_pool;alter session set optimizer_features_enable='9.0.0';@a;
alter system flush shared_pool;alter session set optimizer_features_enable='8.1.7';@a;
alter system flush shared_pool;alter session set optimizer_features_enable='8.1.6';@a;
alter system flush shared_pool;alter session set optimizer_features_enable='8.1.5';@a;
alter system flush shared_pool;alter session set optimizer_features_enable='8.1.4';@a;
alter system flush shared_pool;alter session set optimizer_features_enable='8.1.3';@a;
alter system flush shared_pool;alter session set optimizer_features_enable='8.1.0';@a;
alter system flush shared_pool;alter session set optimizer_features_enable='8.0.7';@a;
alter system flush shared_pool;alter session set optimizer_features_enable='8.0.6';@a;
alter system flush shared_pool;alter session set optimizer_features_enable='8.0.5';@a;
alter system flush shared_pool;alter session set optimizer_features_enable='8.0.4';@a;
alter system flush shared_pool;alter session set optimizer_features_enable='8.0.3';@a;
alter system flush shared_pool;alter session set optimizer_features_enable='8.0.0';@a;

--在使用vim替换 :%s/;/;
/g.就可以了.

11.2.0.3.1  ===>  Elapsed: 00:00:03.81
11.2.0.3  ===>    Elapsed: 00:00:03.78
11.2.0.2  ===>    Elapsed: 00:00:03.85
11.2.0.1  ===>    Elapsed: 00:00:05.78
11.1.0.7  ===>    Elapsed: 00:00:06.40
11.1.0.6  ===>    Elapsed: 00:00:06.42
10.2.0.5  ===>    Elapsed: 00:00:05.79
10.2.0.4  ===>    Elapsed: 00:00:05.76
10.2.0.3  ===>    Elapsed: 00:00:05.38
10.2.0.2  ===>    Elapsed: 00:00:05.38
10.2.0.1  ===>    Elapsed: 00:00:05.11
10.1.0.5  ===>    Elapsed: 00:00:03.46
10.1.0.4  ===>    Elapsed: 00:00:03.47
10.1.0.3  ===>    Elapsed: 00:00:03.48
10.1.0  ===>      Elapsed: 00:00:03.47
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
9.2.0.8  ===>     Elapsed: 00:00:00.73
9.2.0  ===>       Elapsed: 00:00:00.73
9.0.1  ===>       Elapsed: 00:00:00.26
9.0.0  ===>       Elapsed: 00:00:00.25
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
8.1.7  ===>       Elapsed: 00:00:03.26
8.1.6  ===>       Elapsed: 00:00:03.27
8.1.5  ===>       Elapsed: 00:00:03.09
8.1.4  ===>       Elapsed: 00:00:03.10
8.1.3  ===>       Elapsed: 00:00:03.11
8.1.0  ===>       Elapsed: 00:00:03.19
8.0.7  ===>       Elapsed: 00:00:16.19
8.0.6  ===>       Elapsed: 00:00:16.44
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
8.0.5  ===>       Elapsed: 00:00:00.28
8.0.4  ===>       Elapsed: 00:00:00.26
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
8.0.3  ===>       Elapsed: 00:00:03.11
8.0.0  ===>       Elapsed: 00:00:03.12

--可以发现执行最快的最快的是设置optimizer_features_enable=9.X, 8.0.4,8.0.5.

5.很明显我不能使用optimizer_features_enable提示.好像使用提示无效(另外写1篇blog).仅仅修改session有效.

alter system flush shared_pool;
alter session set optimizer_features_enable='9.0.0';
@a;

@dpc '' advanced

--仅仅抽取outline部分:
Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('9.0.0')
      DB_VERSION('11.2.0.3')
      OPT_PARAM('_optimizer_push_pred_cost_based' 'true')
      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$693A5C0E")
      OLD_PUSH_PRED(@"SEL$1AB1DE6C" "RO"@"SEL$2" ("OBJ$"."OBJ#"))
      OUTLINE_LEAF(@"SEL$26")
      OUTLINE_LEAF(@"SEL$FB557CB0")
      OLD_PUSH_PRED(@"SEL$1AB1DE6C" "RO"@"SEL$22" ("OBJ$"."OBJ#"))
      OUTLINE_LEAF(@"SEL$1AB1DE6C")
      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$1AB1DE6C")
      MERGE(@"SEL$1FB6C052")
      MERGE(@"SEL$261A5DF9")
      MERGE(@"SEL$FF8A3B74")
      OUTLINE(@"SEL$25")
      OUTLINE(@"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$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$1AB1DE6C" "U"@"SEL$14" ("USER$"."NAME"))
      INDEX_FFS(@"SEL$1AB1DE6C" "O"@"SEL$7" ("OBJ$"."OWNER#" "OBJ$"."NAME" "OBJ$"."NAMESPACE" "OBJ$"."REMOTEOWNER" "OBJ$"."LINKNAME"
              "OBJ$"."SUBNAME" "OBJ$"."TYPE#" "OBJ$"."SPARE3" "OBJ$"."OBJ#"))
      INDEX(@"SEL$1AB1DE6C" "U"@"SEL$7" "I_USER#")
      INDEX(@"SEL$1AB1DE6C" "C"@"SEL$2" "I_COBJ#")
      INDEX_RS_ASC(@"SEL$1AB1DE6C" "OC"@"SEL$2" ("CON$"."CON#"))
      INDEX(@"SEL$1AB1DE6C" "U"@"SEL$3" "I_USER#")
      INDEX_RS_ASC(@"SEL$1AB1DE6C" "RC"@"SEL$2" ("CON$"."CON#"))
      INDEX(@"SEL$1AB1DE6C" "U"@"SEL$4" "I_USER#")
      INDEX_RS_ASC(@"SEL$1AB1DE6C" "C"@"SEL$14" ("CON$"."OWNER#" "CON$"."NAME"))
      INDEX_RS_ASC(@"SEL$1AB1DE6C" "CD"@"SEL$14" ("CDEF$"."CON#"))
      FULL(@"SEL$1AB1DE6C" "OC"@"SEL$22")
      INDEX(@"SEL$1AB1DE6C" "U"@"SEL$23" "I_USER#")
      INDEX_RS_ASC(@"SEL$1AB1DE6C" "C"@"SEL$22" ("CDEF$"."CON#"))
      INDEX_RS_ASC(@"SEL$1AB1DE6C" "O"@"SEL$27" ("OBJ$"."OBJ#" "OBJ$"."OWNER#" "OBJ$"."TYPE#"))
      INDEX(@"SEL$1AB1DE6C" "U"@"SEL$27" "I_USER#")
      INDEX_RS_ASC(@"SEL$1AB1DE6C" "RC"@"SEL$22" ("CON$"."CON#"))
      INDEX(@"SEL$1AB1DE6C" "U"@"SEL$24" "I_USER#")
      INDEX_RS_ASC(@"SEL$1AB1DE6C" "CC"@"SEL$14" ("CCOL$"."CON#" "CCOL$"."COL#"))
      INDEX_RS_ASC(@"SEL$1AB1DE6C" "O"@"SEL$15" ("OBJ$"."OBJ#" "OBJ$"."OWNER#" "OBJ$"."TYPE#"))
      INDEX(@"SEL$1AB1DE6C" "U"@"SEL$15" "I_USER#")
      NO_ACCESS(@"SEL$1AB1DE6C" "RO"@"SEL$2")
      INDEX(@"SEL$1AB1DE6C" "OI"@"SEL$2" ("OBJ$"."OBJ#" "OBJ$"."OWNER#" "OBJ$"."TYPE#"))
      INDEX(@"SEL$1AB1DE6C" "UI"@"SEL$2" "I_USER#")
      NO_ACCESS(@"SEL$1AB1DE6C" "RO"@"SEL$22")
      INDEX(@"SEL$1AB1DE6C" "OI"@"SEL$22" ("OBJ$"."OBJ#" "OBJ$"."OWNER#" "OBJ$"."TYPE#"))
      INDEX(@"SEL$1AB1DE6C" "UI"@"SEL$22" "I_USER#")
      INDEX_RS_ASC(@"SEL$1AB1DE6C" "COL"@"SEL$14" ("COL$"."OBJ#" "COL$"."INTCOL#"))
      CLUSTER(@"SEL$1AB1DE6C" "AC"@"SEL$14")
      LEADING(@"SEL$1AB1DE6C" "U"@"SEL$14" "O"@"SEL$7" "U"@"SEL$7" "C"@"SEL$2" "OC"@"SEL$2" "U"@"SEL$3" "RC"@"SEL$2" "U"@"SEL$4"
              "C"@"SEL$14" "CD"@"SEL$14" "OC"@"SEL$22" "U"@"SEL$23" "C"@"SEL$22" "O"@"SEL$27" "U"@"SEL$27" "RC"@"SEL$22" "U"@"SEL$24"
              "CC"@"SEL$14" "O"@"SEL$15" "U"@"SEL$15" "RO"@"SEL$2" "OI"@"SEL$2" "UI"@"SEL$2" "RO"@"SEL$22" "OI"@"SEL$22" "UI"@"SEL$22"
              "COL"@"SEL$14" "AC"@"SEL$14")
      USE_NL(@"SEL$1AB1DE6C" "O"@"SEL$7")
      USE_NL(@"SEL$1AB1DE6C" "U"@"SEL$7")
      USE_NL(@"SEL$1AB1DE6C" "C"@"SEL$2")
      USE_NL(@"SEL$1AB1DE6C" "OC"@"SEL$2")
      USE_NL(@"SEL$1AB1DE6C" "U"@"SEL$3")
      USE_NL(@"SEL$1AB1DE6C" "RC"@"SEL$2")
      USE_NL(@"SEL$1AB1DE6C" "U"@"SEL$4")
      USE_NL(@"SEL$1AB1DE6C" "C"@"SEL$14")
      USE_NL(@"SEL$1AB1DE6C" "CD"@"SEL$14")
      USE_HASH(@"SEL$1AB1DE6C" "OC"@"SEL$22")
      USE_NL(@"SEL$1AB1DE6C" "U"@"SEL$23")
      USE_NL(@"SEL$1AB1DE6C" "C"@"SEL$22")
      USE_NL(@"SEL$1AB1DE6C" "O"@"SEL$27")
      USE_NL(@"SEL$1AB1DE6C" "U"@"SEL$27")
      USE_NL(@"SEL$1AB1DE6C" "RC"@"SEL$22")
      USE_NL(@"SEL$1AB1DE6C" "U"@"SEL$24")
      USE_NL(@"SEL$1AB1DE6C" "CC"@"SEL$14")
      USE_NL(@"SEL$1AB1DE6C" "O"@"SEL$15")
      USE_NL(@"SEL$1AB1DE6C" "U"@"SEL$15")
      USE_NL(@"SEL$1AB1DE6C" "RO"@"SEL$2")
      USE_NL(@"SEL$1AB1DE6C" "OI"@"SEL$2")
      USE_NL(@"SEL$1AB1DE6C" "UI"@"SEL$2")
      USE_NL(@"SEL$1AB1DE6C" "RO"@"SEL$22")
      USE_NL(@"SEL$1AB1DE6C" "OI"@"SEL$22")
      USE_NL(@"SEL$1AB1DE6C" "UI"@"SEL$22")
      USE_NL(@"SEL$1AB1DE6C" "COL"@"SEL$14")
      USE_NL(@"SEL$1AB1DE6C" "AC"@"SEL$14")
      INDEX(@"SEL$693A5C0E" "O"@"SEL$5" ("OBJ$"."OBJ#" "OBJ$"."OWNER#" "OBJ$"."TYPE#"))
      INDEX(@"SEL$693A5C0E" "U"@"SEL$5" "I_USER#")
      LEADING(@"SEL$693A5C0E" "O"@"SEL$5" "U"@"SEL$5")
      USE_NL(@"SEL$693A5C0E" "U"@"SEL$5")
      INDEX(@"SEL$FB557CB0" "O"@"SEL$25" ("OBJ$"."OBJ#" "OBJ$"."OWNER#" "OBJ$"."TYPE#"))
      INDEX(@"SEL$FB557CB0" "U"@"SEL$25" "I_USER#")
      LEADING(@"SEL$FB557CB0" "O"@"SEL$25" "U"@"SEL$25")
      USE_NL(@"SEL$FB557CB0" "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_NL(@"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_NL(@"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_NL(@"SEL$9F331807" "X$KZSRO"@"SEL$30")
      END_OUTLINE_DATA
  */

--抽取与OPTIMIZER_FEATURES_ENABLE='9.0.0'相关hint.

$ cat c.sql
SELECT   /*+       OPTIMIZER_FEATURES_ENABLE('9.0.0')
      OPT_PARAM('_optimizer_push_pred_cost_based' 'true')
      OLD_PUSH_PRED(@"SEL$1AB1DE6C" "RO"@"SEL$2" ("OBJ$"."OBJ#"))
      OLD_PUSH_PRED(@"SEL$1AB1DE6C" "RO"@"SEL$22" ("OBJ$"."OBJ#"))
*/  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 ;

SCOTT@test> alter system flush SHARED_POOL;
System altered.

Elapsed: 00:00:00.16
SCOTT@test> set timing on
SCOTT@test> @c
CONSTRAINT_NAME                OWNER  R_OWNER  TABLE_NAME COLUMN_NAME          DELETE_RU
------------------------------ ------ -------- ---------- -------------------- ---------
FK_DEPTNO                      SCOTT  SCOTT    DEPT       DEPTNO               NO ACTION

Elapsed: 00:00:00.27
CONSTRAINT_NAME                OWNER  R_OWNER  TABLE_NAME COLUMN_NAME          DELETE_RU
------------------------------ ------ -------- ---------- -------------------- ---------
FK_DEPTNO                      SCOTT  SCOTT    DEPT       DEPTNO               NO ACTION

Elapsed: 00:00:00.02

--看来11G加入许多新特性,这些特性在导致多表连接等选择连接顺序上,分析消耗太大,这给一些升级用户提一个醒,如果你的执行计划特
--别复杂,对表连接很多,视图很多的情况下,要注意分析时间导致的问题.特别在你应用没有很好绑定的情况下,这个问题会变得更加严重
--切记切记!!!

6.再看看其他方面的因素:
--我记得我以前学习oracle 8i的时候,修改optimizer_max_permutations参数,可以减少分析时间.
--现在已经修改为隐含参数.

SCOTT@test> @hide _OPTIMIZER_MAX_PERMUTATIONS
old  10:  and lower(a.ksppinm) like lower('%&1%')
new  10:  and lower(a.ksppinm) like lower('%_OPTIMIZER_MAX_PERMUTATIONS%')
NAME                                     DESCRIPTION                                                        DEFAULT_VALUE          SESSION_VALUE          SYSTEM_VALUE
---------------------------------------- ------------------------------------------------------------------ ---------------------- ---------------------- ----------------------
_optimizer_max_permutations              optimizer maximum join permutations per query block                TRUE                   2000                   2000

Elapsed: 00:00:00.04

--我的测试效果不明显.放弃!

7.总结:
--升级11g要注意,实际上任何升级都要注意,从上面的测试看,11G升级对于复杂sql语句可能分析时间会变长,
--如果你程序没有很好的使用绑定变量,设想上面的语句如果大量执行,PB写的代码就是这样,每次都是大量分析消耗
--3秒,这样将是一场"灾难"!!!

时间： 2024-09-05 23:43:10

[20141014]11G长时间分析问题.txt的相关文章

固态硬盘寿命有多长?SSD能使用多长时间?

随着固态硬盘的广泛应用,很多电脑用户都比较担心固态硬盘寿命太短,一些用户认为其基于闪存的技术前提下,很难达到机械硬盘的读写次数,从而导致淘汰.SSD固态硬盘价格较贵,如果使用寿命比普通硬盘短很多的话,那性价比将大打折扣,那么究竟固态硬盘寿命有多长?SSD能使用多长时间? 问题分析解答: 其实在硬盘的制造过程中,技术支持方面已经做的比较完善了.虽然说,使用寿命问题是SSD硬盘不可避免的缺陷,但已普通消费者的使用需求来看,远没有上文观点中的夸张,并且在一些特制的硬盘上,写入寿命可以达到100万到50

jmeter 退出线程-jmeter长时间无法关闭测试线程

问题描述 jmeter长时间无法关闭测试线程 jmeter启动运行脚本后,点击stop按钮,弹框提示正在推出测试线程.(长时间未退出) 解决方案 是不是写的脚本有问题.要多分析一下啊

请大家帮讨论一下要开发一个万网那样的平台需要多长时间

问题描述 我想问一下如果开发一个中国万网那样的站点需要多长时间(纯自己一个人开发,不down)有域名部分=虚拟主机部分=企业邮箱部分在加上新闻系统什么的我想知道这写需要多长的开发周期因为我是技术公司要我自己开发,想请各位给分析一下开发周期谢谢(纯自己一个人开发,不down) 解决方案 解决方案二: 1-3个月不等吧~解决方案三: 美工也自己搞.那估计很耗时间解决方案四: 看个人能力了吧解决方案五: 6个月.解决方案六: mark解决方案七: 该回复于2008-06-16 09:03:55被版主删

SQLServer中的执行计划缓存由于长时间缓存对性能造成的干扰

原文:SQLServer中的执行计划缓存由于长时间缓存对性能造成的干扰   本文出处:http://www.cnblogs.com/wy123/p/7190785.html  (保留出处并非什么原创作品权利,本人拙作还远远达不到,仅仅是为了链接到原文,因为后续对可能存在的一些错误进行修正或补充,无他)   先抛出一个性能问题,前几天遇到一个生产环境性能极其低下的存储过程,开发人员根据具体的业务逻辑和返回的数据量,猜测到这个存储过程的执行应该不会有这么慢.当时意识到可能是执行计划缓存的问题,因为当

圆桌讨论：区块链现在处于什么阶段？应用的爆发需要多长时间？| CCF-GAIR 2017

雷锋网AI金融评论报道,2017年7月7日至9日,全球人工智能与机器人峰会CCF-GAIR大会在深圳大中华喜来登酒店举行.本次由CCF中国计算机学会主办.雷锋网与香港中文大学(深圳)承办的大会聚集了全球30多位顶级院士.近300家AI明星AI企业 ,参会人数规模高达3000人,无愧国内顶级阵容. 在8日下午的金融科技专场,区块链环节也迎来了一个圆桌讨论,该环节由阿博茨科技CEO杨永智主持,北航数字社会与区块链实验室主任.天德科技首席科学家蔡维德教授,趣链科技CEO李伟博士,中国银联区块链负责人周

任务管理器-MFC程序，程序在运行中一切良好，如果长时间不进行程序操作，程序就卡死了

问题描述 MFC程序,程序在运行中一切良好,如果长时间不进行程序操作,程序就卡死了 写的MFC程序,程序在运行中一切良好,如果长时间不进行程序操作,程序就卡死了,最小化到托盘后,界面也显示不出来了,不知道是怎么回事?还有通过任务管理器看内存情况,内存也没有 明显的增加,也不占CPU,有谁遇到过这样的事情呢?帮帮忙了,谢谢了! 解决方案 可能存在隐藏较深的.或随机发生.或者在一定条件下发生的指针操作越界. 内存也没有 明显的增加,是不是说明内存在不操作下还是有增加,特别是 GDI 等.这样,有可能

第十六章——处理锁、阻塞和死锁（1）——确定长时间运行的事务

原文:第十六章--处理锁.阻塞和死锁(1)--确定长时间运行的事务 前言: 事务是OLTP系统中的主要部分.它管理数据一致性和数据并发问题,当多个资源同时被读取或者修改相同数据时,SQLServer会通过锁定机制来确保数据库中的数据总是处于一个有效状态.在SQLServer中,锁管理器是负责实现这些锁机制.SQLServer对于不同的资源类型提供不同的锁类型,如数据库.文件.对象.表.区.页和键. 当你使用事务时,依然会遇到由事务引起的问题,这些通常是由于锁.阻塞和死锁引起的. 本系列将讲解这三

绿叶蔬菜在烹调时不宜长时间地焖煮

一.把绿叶蔬菜长时间地焖煮着吃 绿叶蔬菜在烹调时不宜长时间地焖煮.不然,绿叶蔬菜中的硝酸盐将会转变成亚硝酸盐,容易使宝宝食物中毒. 速冻蔬菜类大多已经被涮过,不必煮得时间过长,不然就会烂掉,丧失很多营养.[page] 二.胡萝卜与萝卜混合做成泥酱 不要把胡萝卜与萝卜一起磨成泥酱.因为,胡萝卜中含有能够破坏维生素C的酵素,会把萝卜中的维生素C完全破坏掉.[page] 三.香菇洗得太干净或用水浸泡 香菇中含有麦角淄醇,在接受阳光照射后会转变为维生素D.但如果在吃前过度清洗或用水浸泡,就会损失很多营养

传漾王跃：站外推广这一步也是我们花很长时间积累的东西

和讯科技消息 3月22日,由艾瑞咨询集团主办的第七届艾瑞年度峰会今日在京举行.本届艾瑞年会历时2天,第一天诸多行业领袖参与高峰论坛,进行交流探讨.次日3月23日,行业精英现场多维度地剖析行业动态.分享成功经验及独到见解.传漾科技创始人兼技术副总裁王跃发表主题演讲. "站外推广这一步也是我们花很长时间积累的东西.比如说通过系统我们能够把它的每一个渠道的访客跟各个系统进行很精确的数据细分",王跃表示. 王跃同时认为,从精细化运营到数据化的驱动销量,打通整个电商运营环节的各个数据孤岛一定能够