[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的某种方式可以减少分析时间的.不过如果使用分析,里面记录的是一些百分比,这样不能跳过分析阶段,如果保存的是真实的执行计划.
--//就可以减少分析.