[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
--//大大的减少了执行时间。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/267265/viewspace-3008682/ ，如需转载，请注明出处，否则将追究法律责任。
最新文章
- [20261008]验证Shared Memory Segments key以及Semaphore Arrays Key(11g).txt
- [20261008]gdb引入结构体是否可行.txt
- [20261008]bpftrace定义结构体例子.txt
- [20261007]关于oracle sskgph2函数.txt
- [20261007]oracle Semaphore Arrays Key值的冲突.txt
- [20261004]oracle sskgph2函数等价c以及python脚本.txt
- [20261004]bpftrace查看oracle函数调用堆栈问题.txt
- [20261002]验证sskgphpjw函数参与运算的字符串是否存在规律.txt
- [20261002]oracle Semaphore Arrays Key值的计算2.txt
- [20261001]oracle Semaphore Arrays Key值的计算.txt
