[20180302]sql profile能減少分析時間嗎?
[20180302]sql profile能減少分析時間嗎?
--//連結http://www.itpub.net/thread-2097379-1-1.html的討論,測試看看sql profile能減少分析時間嗎?
--//要找到這樣的語句分析時間"很長",而且機器cpu效能還不能太好(這樣比較容易測試),我以前遇到一例,連結:
--//http://blog.itpub.net/267265/viewspace-1298186/
--//透過這個例子測試看看.
1.環境:
SCOTT@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
$ cat aa.txt
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;
SCOTT@book> alter system flush shared_pool;
System altered.
SCOTT@book> set timing on
SCOTT@book> @ aa.txt
CONSTRAINT_NAME OWNER R_OWNER TABLE_NAME COLUMN_NAME DELETE_RU
------------------------------ ------ ------- ---------- -------------------- ---------
FK_DEPTNO SCOTT SCOTT DEPT DEPTNO NO ACTION
Elapsed: 00:00:01.68
SCOTT@book> @ aa.txt
CONSTRAINT_NAME OWNER R_OWNER TABLE_NAME COLUMN_NAME DELETE_RU
------------------------------ ------ ------- ---------- -------------------- ---------
FK_DEPTNO SCOTT SCOTT DEPT DEPTNO NO ACTION
Elapsed: 00:00:00.01
--//你只要重新整理共享池,再次執行該語句就需要1.XX秒,在我們以前的舊伺服器更慢,說明該語句主要問題花分析上,參考連結:
--//http://blog.itpub.net/267265/viewspace-1298186/
2.使用sql profile穩定執行計劃:
--//確定sql_id=gmzkkrbp9s3zb.執行時間很長.sql profile如果分析效能很差的語句要特別注意這點.
SCOTT@book> @ &r/sp1 gmzkkrbp9s3zb
.....略.
SCOTT@book> execute dbms_sqltune.accept_sql_profile(task_name => 'tuning gmzkkrbp9s3zb', replace => TRUE, name=>'tuning gmzkkrbp9s3zb', FORCE_MATCH=>True)
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.71
SCOTT@book> alter system flush shared_pool;
System altered.
SCOTT@book> @ aa.txt
CONSTRAINT_NAME OWNER R_OWNER TABLE_NAME COLUMN_NAME DELETE_RU
--------------- ------ -------- ---------- -------------------- ---------
FK_DEPTNO SCOTT SCOTT DEPT DEPTNO NO ACTION
Elapsed: 00:00:01.93
--//可以發現第一次執行並沒有減少分析時間.不過sql profile可以透過交換來穩定執行計劃.先刪除sql_profle.
--//難道不是直接取出執行計劃來用嗎?噢明白了:
SCOTT@book> @ &r/spext.sql gmzkkrbp9s3zb
SCOTT@book> @ &r/spext.sql gmzkkrbp9s3zb
HINT NAME
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------
OPT_ESTIMATE(@"SEL$5F5C8B85", NLJ_INDEX_FILTER, "U"@"SEL$24", ("C"@"SEL$22", "U"@"SEL$23", "RC"@"SEL$22", "OC"@"SEL$22", "U"@"SEL$15", "O"@"SEL$15", "CD"@"SEL$14", "CC"@"SEL$14", "C"@"SEL$14", "U"@"SE tuning gmzkkrbp9s3zb
L$14", "C"@"SEL$2", "U"@"SEL$7", "O"@"SEL$7", "RO"@"SEL$2", "U"@"SEL$4", "U"@"SEL$3", "RC"@"SEL$2", "OC"@"SEL$2"), "I_USER2", SCALE_ROWS=0.5)
OPT_ESTIMATE(@"SEL$5F5C8B85", NLJ_INDEX_SCAN, "OC"@"SEL$22", ("U"@"SEL$23", "U"@"SEL$14"), "I_CON1", SCALE_ROWS=0.2234042553) tuning gmzkkrbp9s3zb
OPT_ESTIMATE(@"SEL$5F5C8B85", NLJ_INDEX_SCAN, "OC"@"SEL$22", ("U"@"SEL$23", "C"@"SEL$14", "U"@"SEL$14", "C"@"SEL$2", "U"@"SEL$7", "O"@"SEL$7", "U"@"SEL$4", "U"@"SEL$3", "RC"@"SEL$2", "OC"@"SEL$2"), "I tuning gmzkkrbp9s3zb
_CON1", SCALE_ROWS=0.09090909091)
--//裡面的提示並不是執行計劃,而是一些控制百分比例的引數.
SCOTT@book> execute dbms_sqltune.drop_sql_profile(name => 'tuning gmzkkrbp9s3zb');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.08
3.修改語句:
$ cat aa.txt
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@book> alter system flush shared_pool;
System altered.
SCOTT@book> set timing on
SCOTT@book> @ ab.txt
CONSTRAINT_NAME OWNER R_OWNER TABLE_NAME COLUMN_NAME DELETE_RU
--------------- ------ ------- ---------- -------------------- ---------
FK_DEPTNO SCOTT SCOTT DEPT DEPTNO NO ACTION
Elapsed: 00:00:00.20
--// sql_id = 19zn8y37y66cd.第一次執行就很快.
SCOTT@book> @ &r/spsw 19zn8y37y66cd 0 gmzkkrbp9s3zb 0 '' true
PL/SQL procedure successfully completed.
4.繼續測試:
SCOTT@book> alter system flush shared_pool;
System altered.
Elapsed: 00:00:00.12
SCOTT@book> @ aa.txt
CONSTRAINT_NAME OWNER R_OWNER TABLE_NAME COLUMN_NAME DELETE_RU
------------------------------ ------ ------- ---------- -------------------- ---------
FK_DEPTNO SCOTT SCOTT DEPT DEPTNO NO ACTION
Elapsed: 00:00:00.16
SCOTT@book> @ &r/dpc '' ''
....
Note
-----
- cpu costing is off (consider enabling it)
- SQL profile switch tuning gmzkkrbp9s3zb used for this statement
- 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
326 rows selected.
5.刪除sql_profile:
--//重新使用前面的執行sql profile:
SCOTT@book> execute dbms_sqltune.accept_sql_profile(task_name => 'tuning gmzkkrbp9s3zb', replace => TRUE, name=>'tuning gmzkkrbp9s3zb', FORCE_MATCH=>True)
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.71
SCOTT@book> @ aa.txt
CONSTRAINT_NAME OWNER R_OWNER TABLE_NAME COLUMN_NAME DELETE_RU
------------------------------ ------ ------- ---------- -------------------- ---------
FK_DEPTNO SCOTT SCOTT DEPT DEPTNO NO ACTION
--//抽取執行計劃:
SCOTT@book> @ &r/dpc '' outline
...
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.4')
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$5F5C8B85" "RO"@"SEL$2" 46)
OUTLINE_LEAF(@"SEL$26")
OUTLINE_LEAF(@"SEL$B2256D11")
PUSH_PRED(@"SEL$5F5C8B85" "RO"@"SEL$22" 19)
OUTLINE_LEAF(@"SEL$5F5C8B85")
OUTER_JOIN_TO_INNER(@"SEL$1AB1DE6C" "RC"@"SEL$2")
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$5F5C8B85")
OUTER_JOIN_TO_INNER(@"SEL$1AB1DE6C" "RC"@"SEL$2")
OUTLINE(@"SEL$25")
OUTLINE(@"SEL$1AB1DE6C")
MERGE(@"SEL$1FB6C052")
MERGE(@"SEL$261A5DF9")
MERGE(@"SEL$FF8A3B74")
OUTLINE(@"SEL$32")
OUTLINE(@"SEL$33")
OUTLINE(@"SEL$20")
OUTLINE(@"SEL$21")
OUTLINE(@"SEL$12")
OUTLINE(@"SEL$13")
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$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$5F5C8B85" "U"@"SEL$14" ("USER$"."NAME"))
FULL(@"SEL$5F5C8B85" "U"@"SEL$3")
INDEX_RS_ASC(@"SEL$5F5C8B85" "OC"@"SEL$2" ("CON$"."OWNER#" "CON$"."NAME"))
INDEX_RS_ASC(@"SEL$5F5C8B85" "C"@"SEL$14" ("CON$"."OWNER#" "CON$"."NAME"))
INDEX_RS_ASC(@"SEL$5F5C8B85" "C"@"SEL$2" ("CDEF$"."CON#"))
NO_ACCESS(@"SEL$5F5C8B85" "RO"@"SEL$2")
INDEX_RS_ASC(@"SEL$5F5C8B85" "O"@"SEL$7" ("OBJ$"."OBJ#" "OBJ$"."OWNER#" "OBJ$"."TYPE#"))
INDEX(@"SEL$5F5C8B85" "U"@"SEL$7" ("USER$"."USER#" "USER$"."TYPE#" "USER$"."SPARE1" "USER$"."SPARE2"))
INDEX_RS_ASC(@"SEL$5F5C8B85" "RC"@"SEL$2" ("CON$"."CON#"))
INDEX(@"SEL$5F5C8B85" "U"@"SEL$4" "I_USER#")
FULL(@"SEL$5F5C8B85" "U"@"SEL$23")
INDEX_RS_ASC(@"SEL$5F5C8B85" "OC"@"SEL$22" ("CON$"."OWNER#" "CON$"."NAME"))
INDEX_RS_ASC(@"SEL$5F5C8B85" "C"@"SEL$22" ("CDEF$"."CON#"))
NO_ACCESS(@"SEL$5F5C8B85" "RO"@"SEL$22")
INDEX_RS_ASC(@"SEL$5F5C8B85" "RC"@"SEL$22" ("CON$"."CON#"))
INDEX(@"SEL$5F5C8B85" "U"@"SEL$24" ("USER$"."USER#" "USER$"."TYPE#" "USER$"."SPARE1" "USER$"."SPARE2"))
INDEX_RS_ASC(@"SEL$5F5C8B85" "CD"@"SEL$14" ("CDEF$"."CON#"))
INDEX_RS_ASC(@"SEL$5F5C8B85" "CC"@"SEL$14" ("CCOL$"."CON#" "CCOL$"."COL#"))
INDEX_RS_ASC(@"SEL$5F5C8B85" "O"@"SEL$15" ("OBJ$"."OBJ#" "OBJ$"."OWNER#" "OBJ$"."TYPE#"))
INDEX(@"SEL$5F5C8B85" "U"@"SEL$15" ("USER$"."USER#" "USER$"."TYPE#" "USER$"."SPARE1" "USER$"."SPARE2"))
INDEX(@"SEL$5F5C8B85" "OI"@"SEL$2" ("OBJ$"."OBJ#" "OBJ$"."OWNER#" "OBJ$"."TYPE#"))
INDEX(@"SEL$5F5C8B85" "UI"@"SEL$2" ("USER$"."USER#" "USER$"."TYPE#" "USER$"."SPARE1" "USER$"."SPARE2"))
INDEX(@"SEL$5F5C8B85" "OI"@"SEL$22" ("OBJ$"."OBJ#" "OBJ$"."OWNER#" "OBJ$"."TYPE#"))
INDEX(@"SEL$5F5C8B85" "UI"@"SEL$22" ("USER$"."USER#" "USER$"."TYPE#" "USER$"."SPARE1" "USER$"."SPARE2"))
INDEX_RS_ASC(@"SEL$5F5C8B85" "O"@"SEL$27" ("OBJ$"."OBJ#" "OBJ$"."OWNER#" "OBJ$"."TYPE#"))
INDEX(@"SEL$5F5C8B85" "U"@"SEL$27" ("USER$"."USER#" "USER$"."TYPE#" "USER$"."SPARE1" "USER$"."SPARE2"))
INDEX_RS_ASC(@"SEL$5F5C8B85" "COL"@"SEL$14" ("COL$"."OBJ#" "COL$"."INTCOL#"))
CLUSTER(@"SEL$5F5C8B85" "AC"@"SEL$14")
LEADING(@"SEL$5F5C8B85" "U"@"SEL$14" "U"@"SEL$3" "OC"@"SEL$2" "C"@"SEL$14" "C"@"SEL$2" "RO"@"SEL$2" "O"@"SEL$7" "U"@"SEL$7" "RC"@"SEL$2" "U"@"SEL$4"
"U"@"SEL$23" "OC"@"SEL$22" "C"@"SEL$22" "RO"@"SEL$22" "RC"@"SEL$22" "U"@"SEL$24" "CD"@"SEL$14" "CC"@"SEL$14" "O"@"SEL$15" "U"@"SEL$15" "OI"@"SEL$2"
"UI"@"SEL$2" "OI"@"SEL$22" "UI"@"SEL$22" "O"@"SEL$27" "U"@"SEL$27" "COL"@"SEL$14" "AC"@"SEL$14")
USE_NL(@"SEL$5F5C8B85" "U"@"SEL$3")
USE_NL(@"SEL$5F5C8B85" "OC"@"SEL$2")
USE_NL(@"SEL$5F5C8B85" "C"@"SEL$14")
USE_NL(@"SEL$5F5C8B85" "C"@"SEL$2")
USE_NL(@"SEL$5F5C8B85" "RO"@"SEL$2")
USE_NL(@"SEL$5F5C8B85" "O"@"SEL$7")
USE_NL(@"SEL$5F5C8B85" "U"@"SEL$7")
USE_NL(@"SEL$5F5C8B85" "RC"@"SEL$2")
USE_NL(@"SEL$5F5C8B85" "U"@"SEL$4")
USE_HASH(@"SEL$5F5C8B85" "U"@"SEL$23")
USE_NL(@"SEL$5F5C8B85" "OC"@"SEL$22")
USE_NL(@"SEL$5F5C8B85" "C"@"SEL$22")
USE_NL(@"SEL$5F5C8B85" "RO"@"SEL$22")
USE_NL(@"SEL$5F5C8B85" "RC"@"SEL$22")
USE_NL(@"SEL$5F5C8B85" "U"@"SEL$24")
USE_NL(@"SEL$5F5C8B85" "CD"@"SEL$14")
USE_NL(@"SEL$5F5C8B85" "CC"@"SEL$14")
USE_NL(@"SEL$5F5C8B85" "O"@"SEL$15")
USE_NL(@"SEL$5F5C8B85" "U"@"SEL$15")
USE_NL(@"SEL$5F5C8B85" "OI"@"SEL$2")
USE_NL(@"SEL$5F5C8B85" "UI"@"SEL$2")
USE_NL(@"SEL$5F5C8B85" "OI"@"SEL$22")
USE_NL(@"SEL$5F5C8B85" "UI"@"SEL$22")
USE_NL(@"SEL$5F5C8B85" "O"@"SEL$27")
USE_NL(@"SEL$5F5C8B85" "U"@"SEL$27")
USE_NL(@"SEL$5F5C8B85" "COL"@"SEL$14")
USE_NL(@"SEL$5F5C8B85" "AC"@"SEL$14")
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_SS(@"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_SS(@"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_SS(@"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_SS(@"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_SS(@"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
*/
--//建立指令碼ac,包含上面的提示,太長指令碼不再貼出:
SCOTT@book> @ ac.txt
CONSTRAINT_NAME OWNER R_OWNER TABLE_NAME COLUMN_NAME DELETE_RU
--------------- ------ ------- ---------- -------------------- ---------
FK_DEPTNO SCOTT SCOTT DEPT DEPTNO NO ACTION
--//獲取sql_id=2x1yphzmr8s4g.
SCOTT@book> execute dbms_sqltune.drop_sql_profile(name => 'tuning gmzkkrbp9s3zb');
PL/SQL procedure successfully completed.
--//交換執行計劃.
SCOTT@book> @ &r/spsw 2x1yphzmr8s4g 0 gmzkkrbp9s3zb 0 '' true
PL/SQL procedure successfully completed.
SCOTT@book> alter system flush shared_pool;
System altered.
SCOTT@book> alter system flush shared_pool;
System altered.
SCOTT@book> set timing on
SCOTT@book> @ aa.txt
CONSTRAINT_NAME OWNER R_OWNER TABLE_NAME COLUMN_NAME DELETE_RU
--------------- ------ ------- ---------- -------------------- ---------
FK_DEPTNO SCOTT SCOTT DEPT DEPTNO NO ACTION
Elapsed: 00:00:00.23
--//可以發現執行很快第一次.
SCOTT@book> @ &r/spext gmzkkrbp9s3zb
HINT NAME
------------------------------------- ------------------------------
IGNORE_OPTIM_EMBEDDED_HINTS switch tuning gmzkkrbp9s3zb
OPTIMIZER_FEATURES_ENABLE('11.2.0.3') switch tuning gmzkkrbp9s3zb
DB_VERSION('11.2.0.4') switch tuning gmzkkrbp9s3zb
ALL_ROWS switch tuning gmzkkrbp9s3zb
OUTLINE_LEAF(@"SEL$9F331807") switch tuning gmzkkrbp9s3zb
...
--//記錄的就是真實的執行計劃.
--//不管怎樣,透過sql profile的某種方式可以減少分析時間的.不過如果使用分析,裡面記錄的是一些百分比,這樣不能跳過分析階段,如果儲存的是真實的執行計劃.
--//就可以減少分析.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2151428/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 探究 | App Startup真的能減少啟動耗時嗎APP
- tcp減少2msl的時間TCP
- 如何減少 Hyperf 框架的掃描時間框架
- 使用BatchNorm替代LayerNorm可以減少Vision Transformer訓練時間和推理時間BATORM
- Android低版本上APP首次啟動時間減少80%（二）AndroidAPP
- 減少熱備方法遷移資料庫的停機時間資料庫
- locust 執行一段時間，使用者數沒減少，但是 RPS 逐漸減少，最後沒有請求
- 請列舉出多種減少頁面載入時間的方法
- 縮短IIS應用池回收時間來實現減少IIS假死
- JDK11垃圾回收暫停時間比Java8減少60%JDKJava
- 移動spa商城優化記（二）--- 減少70%的打包等待時間優化
- MySQL實現當前資料表的所有時間都增加或減少指定的時間間隔(推薦)MySql
- 時間相減和時間轉換
- golang 時間加減Golang
- 恆訊科技科普：減少伺服器響應時間的10種方法伺服器
- 深圳眾創空間，減少不必要成本
- MySQL 減少InnoDB系統表空間的大小MySql
- Rust能實現碳減排嗎？ - theregisterRust
- Vue原理解析（九）：搞懂computed和watch原理，減少使用場景思考時間Vue
- 使用壓縮功能減少主機之間SCP時候的頻寬佔用
- SQL Profile（第三篇）SQL
- SQL Profile（第二篇）SQL
- SQL Profile（第四篇）SQL
- 合成資料: 利用開源技術節約資金、時間和減少碳排放
- 【XTTS】Oracle11g 使用XTTS增量跨平臺傳輸減少停機時間TTSOracle
- python定時爬蟲啟用時如何減少記憶體？Python爬蟲記憶體
- 手機丟失後的黃金時間，這麼做可以減少你的損失
- SQL Profile（第一篇）SQL
- laravel列印輸出完整sql,執行時間和explain分析LaravelSQLAI
- 日本研究稱中國10年間將碳排放減少35%
- Chrome外掛：奮鬥逼、卷王必備，用於減少摸魚時間和頻率Chrome
- 你能找到心儀的妹子嗎？- 時間複雜度進階時間複雜度
- 《12 分鐘》GI 前瞻：時間真的能改變一切嗎？
- 故障分析 | MySQL 相同 SQL 不同環境執行時間不一樣案例分析MySql
- 12c – 使用跨平臺增量備份來減少傳輸表空間的停機時間 (Doc ID 2102859.1)
- 12c – 使用跨平臺增量備份來減少傳輸表空間的停機時間 (文件 ID 2102859.1)
- Webpack + Vue，部署時減少包體積的幾種方法WebVue
- [20230110]sql profile run standby database.txtSQLDatabase