[20240311]sql_id=c8s65f1cuhcb1的优化.txt

来源：这里教程网时间：2026-03-03 19:39:53 作者：

[20240311]sql_id=c8s65f1cuhcb1的优化.txt 1.环境： SYS@127.0.0.1:9014/ywdb> @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 2.问题分析: SYS@127.0.0.1:9014/ywdb> @ ashtop sql_id 1=1 &day Total Distinct Distinct Seconds AAS %This SQL_ID FIRST_SEEN LAST_SEEN Execs Seen Tstamps --------- ------- ------- ------------- ------------------- ------------------- ---------- -------- 17243 .2 25% | 2024-03-07 10:59:56 2024-03-08 10:59:49 6 11988 16006 .2 23% | c8s65f1cuhcb1 2024-03-07 10:59:56 2024-03-08 10:59:54 16003 14604 1308 .0 2% | 327ms8chj9552 2024-03-07 11:00:03 2024-03-08 10:59:29 1308 1303 1275 .0 2% | 0x6u28dvh18us 2024-03-08 01:40:46 2024-03-08 02:05:22 1257 1275 1099 .0 2% | g7ytdh9mxt1s0 2024-03-07 11:01:10 2024-03-08 10:59:36 708 1087 1048 .0 2% | fs9p5prj0hu06 2024-03-08 01:19:49 2024-03-08 01:40:45 1048 1048 --//sql_id=c8s65f1cuhcb1,以前没有这么严重,估计与我分析系统表造成的问题. SYS@127.0.0.1:9014/ywdb> @ sql_id c8s65f1cuhcb1 --SQL_ID = c8s65f1cuhcb1 SELECT SYS.ALL_CONS_COLUMNS.COLUMN_NAME , SYS.ALL_CONSTRAINTS.CONSTRAINT_NAME FROM SYS.ALL_CONSTRAINTS , SYS.ALL_CONS_COLUMNS WHERE SYS.ALL_CONSTRAINTS.CONSTRAINT_TYPE = :"SYS_B_0" AND SYS.ALL_CONSTRAINTS.TABLE_NAME = :"SYS_B_1" AND SYS.ALL_CONSTRAINTS.OWNER = :"SYS_B_2" AND SYS.ALL_CONSTRAINTS.CONSTRAINT_NAME = SYS.ALL_CONS_COLUMNS.CONSTRAINT_NAME AND SYS.ALL_CONSTRAINTS.TABLE_NAME = SYS.ALL_CONS_COLUMNS.TABLE_NAME AND SYS.ALL_CONSTRAINTS.OWNER = SYS.ALL_CONS_COLUMNS.OWNER ORDER BY SYS.ALL_CONSTRAINTS.CONSTRAINT_NAME , SYS.ALL_CONS_COLUMNS.POSITION; --//这个程序递归调用执行。 --//只要开发使用PB，就会出现大量递归调用该语句的情况，以前有人问过我如何解决。涉及到数据字典如下： SYS@127.0.0.1:9014/ywdb> @ sqlt c8s65f1cuhcb1 OWNER TABLE_NAME OT -------------------- ------------------------------ -------------------------------------------------- SYS OBJ$ SYS.OBJ$ SYS OBJAUTH$ SYS.OBJAUTH$ SYS CCOL$ SYS.CCOL$ SYS CDEF$ SYS.CDEF$ SYS USER$ SYS.USER$ SYS COL$ SYS.COL$ SYS ATTRCOL$ SYS.ATTRCOL$ SYS CON$ SYS.CON$ 8 rows selected. --//产生的执行计划非常复杂!!我也尝试过涉及到的表自动建立建立直方图，但是还是没有效果!! --//首先尝试使用sql profile来看看效果。测试前如下： SYS@127.0.0.1:9014/ywdb> @ sqlhh c8s65f1cuhcb1 .1 time unit : millisecond BEGIN_INTERVAL_TIME INST_ID SQL_ID PLAN_HASH_VALUE EXECUTIONS ELA_MS_PER_EXEC CPU_MS_PER_EXEC ROWS_PER_EXEC LIOS_PER_EXEC BLKRD_PER_EXEC IOW_MS_PER_EXEC AVG_IOW_MS CLW_MS_PER_EXEC APW_MS_PER_EXEC CCW_MS_PER_EXEC ------------------- ------------ ------------- --------------- ---------- --------------- --------------- ------------- ------------- -------------- --------------- ----------- --------------- --------------- --------------- 2024-03-08 09:00:20 1 c8s65f1cuhcb1 2845309787 6181 101 100 1.0 62490 0 0 0.0 0 0 0 2024-03-08 09:00:20 2 c8s65f1cuhcb1 2845309787 10462 105 104 0.9 63465 0 0 0.0 0 0 0 2024-03-08 10:00:23 2 c8s65f1cuhcb1 2845309787 4031 107 105 1.0 64230 0 0 0.0 0 0 0 2024-03-08 10:00:23 1 c8s65f1cuhcb1 2845309787 2645 103 102 0.8 63091 0 0 0.0 0 0 0 2024-03-08 10:23:02 1 c8s65f1cuhcb1 2845309787 4354 101 100 0.8 62557 0 0 0.0 0 0 0 2024-03-08 10:23:02 2 c8s65f1cuhcb1 2845309787 6156 106 105 1.0 64238 0 0 0.0 0 0 0 --//原来的逻辑读有64XXX。使用sql profile后： SYS@127.0.0.1:9014/ywdb> @ d_buffer c8s65f1cuhcb1 10 2 EXECUTIONS1 BUFFER_GETS1 ELAPSED_TIME1 ROWS_PROCESSED1 每次buffer_gets 每次执行时间平均处理记录数 INST_ID --------------- --------------- --------------- --------------- --------------- --------------- --------------- --------------- 5453985 3379301090 557543625124 4786043 619.60219729244 102226.83508004 .87753138301627 2 ... sleep 10 , waiting .... EXECUTIONS2 BUFFER_GETS2 ELAPSED_TIME2 ROWS_PROCESSED2 每次buffer_gets 每次执行时间平均处理记录数 INST_ID --------------- --------------- --------------- --------------- --------------- --------------- --------------- --------------- 5454011 3379306127 557543658096 4786070 619.60016710637 102226.3537965 .87753215019185 2 执行次数总buffer_gets 总执行时间总处理记录数每次buffer_gets 每次执行时间平均处理记录数 --------------- --------------- --------------- --------------- --------------- --------------- --------------- 26 5037 32972 27 193.73076923077 1268.1538461538 1.0384615384615 --//逻辑读19X。 SYS@127.0.0.1:9014/ywdb> @ spext c8s65f1cuhcb1 HINT NAME DESCRIPTION LAST_MODIFIED ---------------------------------------------------------------------------------------------------- -------------------- ----------- ------------------------------ OPT_ESTIMATE(@"SEL$A422EF13", TABLE, "X$KZSPR"@"SEL$21", SCALE_ROWS=0.232524444) tuning c8s65f1cuhcb1 2024-03-08 11:02:49.000000 OPT_ESTIMATE(@"SEL$16", TABLE, "O2"@"SEL$16", SCALE_ROWS=0.1160240313) tuning c8s65f1cuhcb1 2024-03-08 11:02:49.000000 OPT_ESTIMATE(@"SEL$16", INDEX_FILTER, "O2"@"SEL$16", "I_OBJ2", SCALE_ROWS=0.1160240313) tuning c8s65f1cuhcb1 2024-03-08 11:02:49.000000 OPT_ESTIMATE(@"SEL$16", INDEX_FILTER, "O2"@"SEL$16", "I_OBJ5", SCALE_ROWS=0.1160240313) tuning c8s65f1cuhcb1 2024-03-08 11:02:49.000000 OPT_ESTIMATE(@"SEL$16", INDEX_SKIP_SCAN, "O2"@"SEL$16", "I_OBJ2", SCALE_ROWS=0.1160240313) tuning c8s65f1cuhcb1 2024-03-08 11:02:49.000000 OPT_ESTIMATE(@"SEL$16", INDEX_SKIP_SCAN, "O2"@"SEL$16", "I_OBJ1", SCALE_ROWS=0.1160240313) tuning c8s65f1cuhcb1 2024-03-08 11:02:49.000000 .. OPT_ESTIMATE(@"SEL$804EC250", INDEX_FILTER, "O"@"SEL$15", "I_OBJ2", SCALE_ROWS=33.03524588) tuning c8s65f1cuhcb1 2024-03-08 11:02:49.000000 OPT_ESTIMATE(@"SEL$804EC250", INDEX_FILTER, "O"@"SEL$7", "I_OBJ2", SCALE_ROWS=33.03524588) tuning c8s65f1cuhcb1 2024-03-08 11:02:49.000000 OPTIMIZER_FEATURES_ENABLE(default) tuning c8s65f1cuhcb1 2024-03-08 11:02:49.000000 45 rows selected. --//使用sql profile缺省记录的并不是执行计划，而且一些统计值,实际上效果并不是很好，平均的逻辑读19X。 --//偶然的一次我发现我们其中一台机器，运行效果很高，逻辑读很少，仅仅6X-7X上下。 --//我就记录下当时执行计划的outline，一直使用它为sql profile 交换来稳定执行计划。 --//但是每次查询笔记很不方便，我都是从另外的机器抽取outline，这次单独记录下来。 --//加入如下提示看看: /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('11.2.0.4') DB_VERSION('11.2.0.4') ALL_ROWS 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$804EC250" "RO"@"SEL$2" 30) OUTLINE_LEAF(@"SEL$804EC250") MERGE(@"SEL$1FB6C052") MERGE(@"SEL$FF8A3B74") 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$804EC250") MERGE(@"SEL$1FB6C052") MERGE(@"SEL$FF8A3B74") OUTLINE(@"SEL$1") OUTLINE(@"SEL$1FB6C052") MERGE(@"SEL$15") OUTLINE(@"SEL$FF8A3B74") MERGE(@"SEL$3") MERGE(@"SEL$4") MERGE(@"SEL$7") OUTLINE(@"SEL$20") OUTLINE(@"SEL$21") OUTLINE(@"SEL$12") OUTLINE(@"SEL$13") OUTLINE(@"SEL$14") OUTLINE(@"SEL$15") OUTLINE(@"SEL$2") OUTLINE(@"SEL$3") OUTLINE(@"SEL$4") OUTLINE(@"SEL$7") INDEX_RS_ASC(@"SEL$804EC250" "U"@"SEL$14" ("USER$"."NAME")) INDEX_SS(@"SEL$804EC250" "O"@"SEL$7" ("OBJ$"."OWNER#" "OBJ$"."NAME" "OBJ$"."NAMESPACE" "OBJ$"."REMOTEOWNER" "OBJ$"."LINKNAME" "OBJ$"."SUBNAME" "OBJ$"."TYPE#" "OBJ$"."SPARE3" "OBJ$"."OBJ#")) INDEX(@"SEL$804EC250" "U"@"SEL$7" ("USER$"."USER#" "USER$"."TYPE#" "USER$"."SPARE1" "USER$"."SPARE2")) INDEX(@"SEL$804EC250" "C"@"SEL$2" "I_COBJ#") INDEX_RS_ASC(@"SEL$804EC250" "OC"@"SEL$2" ("CON$"."CON#")) INDEX(@"SEL$804EC250" "U"@"SEL$3" "I_USER#") INDEX_RS_ASC(@"SEL$804EC250" "C"@"SEL$14" ("CON$"."OWNER#" "CON$"."NAME")) INDEX_RS_ASC(@"SEL$804EC250" "RC"@"SEL$2" ("CON$"."CON#")) INDEX_RS_ASC(@"SEL$804EC250" "CD"@"SEL$14" ("CDEF$"."CON#")) INDEX(@"SEL$804EC250" "OI"@"SEL$2" ("OBJ$"."OBJ#" "OBJ$"."OWNER#" "OBJ$"."TYPE#")) INDEX(@"SEL$804EC250" "U"@"SEL$4" ("USER$"."USER#" "USER$"."TYPE#" "USER$"."SPARE1" "USER$"."SPARE2")) INDEX(@"SEL$804EC250" "UI"@"SEL$2" ("USER$"."USER#" "USER$"."TYPE#" "USER$"."SPARE1" "USER$"."SPARE2")) NO_ACCESS(@"SEL$804EC250" "RO"@"SEL$2") INDEX_RS_ASC(@"SEL$804EC250" "CC"@"SEL$14" ("CCOL$"."CON#" "CCOL$"."COL#")) INDEX_RS_ASC(@"SEL$804EC250" "O"@"SEL$15" ("OBJ$"."OBJ#" "OBJ$"."OWNER#" "OBJ$"."TYPE#")) INDEX_RS_ASC(@"SEL$804EC250" "COL"@"SEL$14" ("COL$"."OBJ#" "COL$"."INTCOL#")) CLUSTER(@"SEL$804EC250" "AC"@"SEL$14") INDEX(@"SEL$804EC250" "U"@"SEL$15" ("USER$"."USER#" "USER$"."TYPE#" "USER$"."SPARE1" "USER$"."SPARE2")) LEADING(@"SEL$804EC250" "U"@"SEL$14" "O"@"SEL$7" "U"@"SEL$7" "C"@"SEL$2" "OC"@"SEL$2" "U"@"SEL$3" "C"@"SEL$14" "RC"@"SEL$2" "CD"@"SEL$14" "OI"@"SEL$2" "U"@"SEL$4" "UI"@"SEL$2" "RO"@"SEL$2" "CC"@"SEL$14" "O"@"SEL$15" "COL"@"SEL$14" "AC"@"SEL$14" "U"@"SEL$15") USE_NL(@"SEL$804EC250" "O"@"SEL$7") USE_HASH(@"SEL$804EC250" "U"@"SEL$7") USE_NL(@"SEL$804EC250" "C"@"SEL$2") USE_NL(@"SEL$804EC250" "OC"@"SEL$2") USE_NL(@"SEL$804EC250" "U"@"SEL$3") USE_NL(@"SEL$804EC250" "C"@"SEL$14") USE_NL(@"SEL$804EC250" "RC"@"SEL$2") USE_NL(@"SEL$804EC250" "CD"@"SEL$14") USE_NL(@"SEL$804EC250" "OI"@"SEL$2") USE_NL(@"SEL$804EC250" "U"@"SEL$4") USE_NL(@"SEL$804EC250" "UI"@"SEL$2") USE_NL(@"SEL$804EC250" "RO"@"SEL$2") USE_NL(@"SEL$804EC250" "CC"@"SEL$14") USE_NL(@"SEL$804EC250" "O"@"SEL$15") USE_NL(@"SEL$804EC250" "COL"@"SEL$14") USE_NL(@"SEL$804EC250" "AC"@"SEL$14") USE_NL(@"SEL$804EC250" "U"@"SEL$15") 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_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_HASH(@"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_HASH(@"SEL$28294604" "X$KZSRO"@"SEL$18") END_OUTLINE_DATA */ --//加入提示后执行，记下sql_id=a0amsx2fh77m6. SYS@127.0.0.1:9014/ywdb> @ spsw a0amsx2fh77m6 0 c8s65f1cuhcb1 0 '' true PL/SQL procedure successfully completed. ================================================================================================================================================= if drop or alter sql profile ,run : execute dbms_sqltune.drop_sql_profile(name => 'switch tuning c8s65f1cuhcb1') execute dbms_sqltune.alter_sql_profile(name => 'switch tuning c8s65f1cuhcb1',attribute_name=>'STATUS',value=>'DISABLED') ================================================================================================================================================= SYS@127.0.0.1:9014/ywdb> @ d_buffer c8s65f1cuhcb1 10 1 EXECUTIONS1 BUFFER_GETS1 ELAPSED_TIME1 ROWS_PROCESSED1 每次buffer_gets 每次执行时间平均处理记录数 INST_ID --------------- --------------- --------------- --------------- --------------- --------------- --------------- --------------- 218256 878579224 21866561264 195539 4025.4527893849 100187.67531706 .89591580529287 1 ... sleep 10 , waiting .... EXECUTIONS2 BUFFER_GETS2 ELAPSED_TIME2 ROWS_PROCESSED2 每次buffer_gets 每次执行时间平均处理记录数 INST_ID --------------- --------------- --------------- --------------- --------------- --------------- --------------- --------------- 218276 878580741 21866579006 195566 4025.0908986787 100178.57669189 .89595741171728 1 执行次数总buffer_gets 总执行时间总处理记录数每次buffer_gets 每次执行时间平均处理记录数 --------------- --------------- --------------- --------------- --------------- --------------- --------------- 20 1517 17742 27 75.85 887.1 1.35 --//逻辑读仅仅76次。 --//星期一上班观察结果如下： SYS@127.0.0.1:9014/ywdb> @ ashtop sql_id sql_id='c8s65f1cuhcb1' &day Total Distinct Distinct Seconds AAS %This SQL_ID FIRST_SEEN LAST_SEEN Execs Seen Tstamps --------- ------- ------- ------------- ------------------- ------------------- ---------- -------- 382 .0 100% | c8s65f1cuhcb1 2024-03-10 09:23:07 2024-03-11 09:15:46 353 344 --//大大的减少了执行时间。

编辑推荐：

返回列表

[20240311]sql_id=c8s65f1cuhcb1的优化.txt

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推