[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秒,這樣將是一場"災難"!!!
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/30345407/viewspace-2142509/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Mysql DDL出現長時間等待MDL問題分析MySql
- 小問題-用分析函式求平均時間段的統計數.txt函式
- [20240202]windows時間同步問題.txtWindows
- c++ CopyFile函式響應時間過長問題。C++函式
- Oracle 11G RAC 本地時間和通過listener連線時間不相同的問題Oracle
- PostgreSQL問題分析1：時間線不一致SQL
- RAC的時間同步問題
- Unix 字串時間戳問題字串時間戳
- ORACLE 11g RAC時鐘同步問題Oracle
- 手機網站長時間按住頁面出現閃退問題網站
- 【原創】RedHat Linux啟動時間過長的問題解決RedhatLinux
- 關於時間 PHP 處理包遇到的問題時間序列化差值問題PHP
- java 讀取.txt檔案時，注意的問題Java
- Shutdown immediate命令長時間等待分析一例
- Job長時間執行不能退出的分析
- 32 位 PHP 時間戳問題PHP時間戳
- 有關時間同步的問題
- Laravel MongoDB 時間區間查詢的問題LaravelMongoDB
- c++ 獲取檔案建立時間、修改時間、訪問時間、檔案內容長度C++
- MongoDB常見問題解答：時間與時區MongoDB
- iOS鍵盤彈出時動畫時長失效問題iOS動畫
- system表空間不足的問題分析
- 時區問題導致時間相差8個小時
- 馬雲：我淡出阿里 只是時間問題阿里
- shell 計算時間差的問題
- 一些長時間GC停頓問題的排查及解決辦法GC
- VNPY2 中凌晨0點時間戳的處理問題，和夜盤資料時間戳分析時間戳
- 解決MongoDB儲存時間時差的問題MongoDB
- 倒數計時快取時間問題總結快取
- db2 前滾最小恢復時間和時間戳問題DB2時間戳
- 頻繁GC (Allocation Failure)及young gc時間過長分析GCAI
- job 執行時間比排程間隔時間長
- 【問題解決】使用YYYY-MM-dd時間轉換問題
- 時間管理類APP分析:拇指時間APP
- Apache HttpClient 沒有設定time out導致應用長時間阻塞的問題ApacheHTTPclient
- system表空間不足的問題分析(二)
- LINUX 解決時間同步問題（NTP）Linux
- oracle 時間欄位自動更新問題Oracle