[20240309]利用FORCE_MATCHING_SIGNATURE字段定位有问题的sql语句.txt
[20240309]利用FORCE_MATCHING_SIGNATURE字段定位有问题的sql语句.txt
--//前天遇到一个问题,使用tpt ash_index_helperz.sql查询时,我发现有一个表ATF_YPXX存在全表扫描.
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> @ash/ash_index_helperz % PORTAL_HIS.ATF_YPXX &day 1 0 "plan_card<=10 and TABLE_ROWS>=100 "
-- Santa's Little (Index) Helper BETA v0.5 - by Tanel Poder ( https://tanelpoder.com )
@ ash/ash_index_helper <sql_id|%> <owner.table_name|owner.%|%.table_name|%> <fromtime> <totime> <1|0|''> <1|0|''> <<filters>
PREDICATES SECONDS AAS CPU WAIT Accessed_Table Plan_Operation PLAN_CARD TABLE_ROWS FILTER_PCT SQL_EXECS ELA_SEC/EXEC SQL_ID MODULE1
----------------------------------------------------------- ------- ------- ----- ----- -------------------- -------------------------------------------------- ------------ ------------ ------------ ------------ ------------- --------------------
[F:] ("A"."OCC_TIME"='2024-03-07 13:00:00' AND "A"."P_ID"= 1 .0 100% 0% ATF_YPXX TABLE ACCESS FULL [PORTAL_HIS.ATF_YPXX] 1 3024446 .00003306391 1 0.811 dxvrdhd7yf5g7 python.exe
[F:] ("A"."OCC_TIME"='2024-03-07 07:00:00' AND "A"."P_ID"= 1 .0 100% 0% ATF_YPXX TABLE ACCESS FULL [PORTAL_HIS.ATF_YPXX] 1 3024446 .00003306391 1 0.841 44w85gabjjxdb python.exe
[F:] ("A"."OCC_TIME"='2024-03-07 13:00:00' AND "A"."P_ID"= 1 .0 100% 0% ATF_YPXX TABLE ACCESS FULL [PORTAL_HIS.ATF_YPXX] 1 3024446 .00003306391 1 0.924 d57pxua28f2gp python.exe
[F:] ("A"."OCC_TIME"='2024-03-07 13:00:00' AND "A"."P_ID"= 1 .0 100% 0% ATF_YPXX TABLE ACCESS FULL [PORTAL_HIS.ATF_YPXX] 1 3024446 .00003306391 1 0.896 dab5cwfj8hm3u python.exe
[F:] ("A"."OCC_TIME"='2024-03-07 13:00:00' AND "A"."P_ID"= 1 .0 100% 0% ATF_YPXX TABLE ACCESS FULL [PORTAL_HIS.ATF_YPXX] 1 3024446 .00003306391 1 0.940 g20cxsz8gr32j python.exe
[F:] ("A"."OCC_TIME"='2024-03-07 13:00:00' AND "A"."P_ID"= 1 .0 100% 0% ATF_YPXX TABLE ACCESS FULL [PORTAL_HIS.ATF_YPXX] 1 3024446 .00003306391 1 0.955 79mmmqpkjtzu3 python.exe
[F:] ("A"."OCC_TIME"='2024-03-07 13:00:00' AND "A"."P_ID"= 1 .0 100% 0% ATF_YPXX TABLE ACCESS FULL [PORTAL_HIS.ATF_YPXX] 1 3024446 .00003306391 1 0.897 58tx6x83wukph python.exe
7 rows selected.
SYS@127.0.0.1:9014/ywdb> @ sql_id 58tx6x83wukph
--SQL_ID = 58tx6x83wukph
select b.ysyzbh yzbxh, a.p_id, a.occ_time, b.lsbz, b.tzsj
from atf_ypxx a, zy_bqyz b
where a.yzxh = b.jlxh
and a.drug_code in ('12916F2011','15173F1531')
and a.p_id = '60286060'
and a.occ_time = '2024-03-07 13:00:00'
and (tzsj is null or
to_date(a.occ_time, 'yyyy-mm-dd hh24:mi:ss') < b.tzsj)
;
--//语句非常类似!!没有使用绑定变量，执行次数仅仅1次,每次大约0.8XX秒,表ATF_YPXX有1G,因为执行次数少导致awr报表无法发现。并
--//且tpt ashtop一般情况下很难发现,感觉最近一段时间修改或者增加代码导致.
--//优化很简单,建立p_id,occ_time的复合索引.
--//这样引出一个问题,这类语句没有使用绑定变量,仅仅执行1次,查看v$active_session_history视图存在force_matching_signature字
--//段,但是没有EXACT_MATCHING_SIGNATURE字段,如何通过tpt ashtop.sql脚本收集这些仅仅执行几次,FORCE_MATCHING_SIGNATURE相同的sql语句.
--//看了一下感觉使用ashtop.sql脚本有点难度,首先必须先知道force_matching_signature的值.
--//自己写一个脚本先收集force_matching_signature相同的信息:
$ cat fms.sql
-- Copyright 2023 lfree. All rights reserved.
-- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms and conditions.
--------------------------------------------------------------------------------
--
-- File name: fms.sql v1.0
-- Purpose: Query gv$active_session_history Force_Matching_Signature the same, sql_id different of information
-- Author: lfree
--
-- Usage:
-- @ fms <count(*)_number> <display_record_number> <fromtime> <totime>
-- <count(*)_number>=2>
--
-- Example:
-- @ fms 5 30 &day
--
-- Other:
-- This script uses only the in-memory GV$ACTIVE_SESSION_HISTORY, use
-- @dfms.sql for accessiong the DBA_HIST_ACTIVE_SESS_HISTORY archive
--
--------------------------------------------------------------------------------
WITH a1 AS (SELECT sql_id,force_matching_signature, count(*) cnt1
FROM gv$active_session_history
WHERE force_matching_signature <> 0 AND sample_time BETWEEN &3 AND &4
GROUP BY sql_id, force_matching_signature ) ,
a2 AS (SELECT force_matching_signature, count(*) cnt2,sum(cnt1) total_seconds
FROM a1
GROUP BY force_matching_signature
HAVING count(*) >= &&1
ORDER BY 3 desc)
SELECT force_matching_signature, cnt2,total_seconds
FROM a2
WHERE rownum <= &2;
$ cat dfms.sql
-- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms and conditions.
--------------------------------------------------------------------------------
--
-- File name: dfms.sql v1.0
-- Purpose: Query dba_hist_active_sess_history Force_Matching_Signature the same, sql_id different of information
-- Author: lfree
--
-- Usage:
-- @ idfms <count(*)_number> <display_record_number> <fromtime> <totime>
-- <count(*)__number>=2>
--
-- Example:
-- @ fms 5 30 &day
--
-- Other:
-- This script uses only the DBA_HIST_ACTIVE_SESS_HISTORY
-- @ fms.sql for accessiong the in-memory GV$ACTIVE_SESSION_HISTORY
--
--------------------------------------------------------------------------------
WITH a1 AS (SELECT sql_id,force_matching_signature, count(*) cnt1
FROM dba_hist_active_sess_history
WHERE force_matching_signature <> 0 AND sample_time BETWEEN &3 AND &4 and dbid = (SELECT d.dbid FROM v$database d)
GROUP BY sql_id,force_matching_signature ) ,
a2 AS (SELECT force_matching_signature, count(*) cnt2,sum(cnt1)*10 total_seconds
FROM a1
GROUP BY force_matching_signature
HAVING count(*) >= &&1
ORDER BY 3 desc)
SELECT force_matching_signature, cnt2,total_seconds
FROM a2
WHERE rownum <= &2;
3.测试看看：
SYS@127.0.0.1:9014/ywdb> @ fms.sql 2 10 &day
FORCE_MATCHING_SIGNATURE CNT2 TOTAL_SECONDS
------------------------ ---------- -------------
12120531484079936454 1301 1994
8680396602758190538 999 1241
4330757809746298095 3 1134
13026426528625086727 569 921
6490285911540139840 334 450
2672114946588399948 3 404
7809763007758332172 191 244
8968795636694904691 123 189
3375334192428958452 4 184
15582081887848190096 94 156
10 rows selected.
--//注:有一些CNT2很小,可能是非常频繁执行sql语句,比如FORCE_MATCHING_SIGNATURE=4330757809746298095
--//这些语句的FORCE_MATCHING_SIGNATURE相同,在应用中频繁执行,看到TOTAL_SECONDS会很高.往往不需要关注.
SYS@127.0.0.1:9014/ywdb> select distinct sql_text from v$sql where FORCE_MATCHING_SIGNATURE=4330757809746298095;
SQL_TEXT
------------------------------------------------------------
select count ( 0 ) from BSOFT_TEST_CONNECT
select count ( :"SYS_B_0" ) from BSOFT_TEST_CONNECT
select count ( 1 ) from BSOFT_TEST_CONNECT
--//应用用来判断是否连上的情况.
SYS@127.0.0.1:9014/ywdb> @ ashtop sql_id FORCE_MATCHING_SIGNATURE=12120531484079936454 &day
Total Distinct Distinct
Seconds AAS %This SQL_ID FIRST_SEEN LAST_SEEN Execs Seen Tstamps
--------- ------- ------- ------------- ------------------- ------------------- ---------- --------
7 .0 0% | 2633ab5rbtsmx 2024-03-07 10:05:34 2024-03-07 22:47:10 1 7
7 .0 0% | at0rwvy9jukny 2024-03-07 09:52:01 2024-03-08 07:40:26 1 7
7 .0 0% | ata0cjw5hcfdn 2024-03-07 09:53:51 2024-03-07 23:15:56 7 7
7 .0 0% | bn7nur60fmsz2 2024-03-07 10:14:28 2024-03-07 21:08:27 2 7
7 .0 0% | cksbjtxp82by5 2024-03-07 09:27:36 2024-03-07 21:39:25 1 7
6 .0 0% | 1xfaz7bpmy0f0 2024-03-07 13:18:50 2024-03-08 08:14:52 1 6
5 .0 0% | 0nrxtxrw7ykjs 2024-03-07 19:49:57 2024-03-08 05:58:05 1 5
5 .0 0% | 0sjpmdjzpxgud 2024-03-07 11:22:31 2024-03-07 17:26:41 1 5
5 .0 0% | 1dc6yungr22zs 2024-03-07 09:31:13 2024-03-07 16:10:20 1 5
5 .0 0% | 39cyjtzh0un8x 2024-03-07 10:43:19 2024-03-08 06:00:46 1 5
5 .0 0% | 3bbj5akntxg0m 2024-03-07 17:22:05 2024-03-08 06:14:45 1 5
5 .0 0% | 5rbyatk1uw381 2024-03-07 15:14:36 2024-03-08 06:35:41 2 5
5 .0 0% | 6938fq89qgd7b 2024-03-07 15:11:20 2024-03-08 06:51:03 1 5
5 .0 0% | 78t8bh12vv8nx 2024-03-07 11:03:15 2024-03-07 21:01:50 1 5
5 .0 0% | 7pkt6t3m2s6b1 2024-03-07 10:44:56 2024-03-07 16:09:03 1 5
5 .0 0% | 7rsxz4cdz48qx 2024-03-07 09:51:41 2024-03-07 23:06:23 1 5
5 .0 0% | a1tq4x1hcr5qu 2024-03-07 10:46:30 2024-03-07 21:54:04 1 5
5 .0 0% | dg3m3vp9h71v8 2024-03-07 09:48:42 2024-03-07 23:48:12 1 5
5 .0 0% | dgx7a0v4xs620 2024-03-07 18:49:02 2024-03-08 07:04:52 1 5
5 .0 0% | fkqm6f2xya397 2024-03-07 14:34:10 2024-03-07 19:26:46 1 5
4 .0 0% | 024aphnyr2hak 2024-03-07 10:21:14 2024-03-07 12:26:26 1 4
4 .0 0% | 1375jt8m2p337 2024-03-07 09:48:03 2024-03-07 17:26:55 1 4
4 .0 0% | 16m5afwdwbrru 2024-03-07 13:00:40 2024-03-07 22:06:30 1 4
4 .0 0% | 1c2rm7gjfdbz8 2024-03-07 16:07:45 2024-03-07 17:49:45 1 4
4 .0 0% | 1ysg3227zcsx2 2024-03-07 18:04:30 2024-03-08 07:36:52 1 4
4 .0 0% | 2u0gjfhvnqgsm 2024-03-07 14:14:55 2024-03-08 06:39:59 1 4
4 .0 0% | 38ynt00vyy87u 2024-03-07 09:49:08 2024-03-07 21:22:35 1 4
4 .0 0% | 3rbwyx6v97hcs 2024-03-07 13:07:45 2024-03-08 02:07:57 1 4
4 .0 0% | 4bqzqpjgt4cxn 2024-03-07 13:17:13 2024-03-07 20:26:05 1 4
4 .0 0% | 5712rtxxt04hn 2024-03-07 15:26:34 2024-03-07 19:20:36 1 4
30 rows selected.
--//注:查看前面ashtop查询1天的输出,FORCE_MATCHING_SIGNATURE=12120531484079936454的记录的CNT2=1301,相当于对应出现至少1301
--//条类似的sql语句(可能有一些awr没有抓取到),造成没有必要的硬分析.前面相关ATF_YPXX表的语句已经看不到(注:已经建立了相关索
--//引)
--//顺便看几条sql语句看看,一些已经不再共享池了.
SYS@127.0.0.1:9014/ywdb> @ sql_id 2633ab5rbtsmx
--SQL_ID = 2633ab5rbtsmx
SELECT a.ksdm
, a.jgid
, a.ksmc
, b.act
FROM ms_ghks a
, (
SELECT count(1) as act, ksdm, jgid
FROM (
SELECT MS_YYHY.HYID
, to_char(MS_YYHY.GZRQ,'yyyymmdd') GZRQ, MS_YYHY.YYSD, MS_GHKS.KSMC, MS_YYHY.KSDM, GY_YGDM.YGXM,
MS_YYHY.YSDM, MS_YYHY.YYBZ, MS_MZGHF.GHF, MS_MZGHF.ZLF, MS_GHKS.JGID
FROM MS_YYHY
, MS_GHKS
, GY_YGDM
, MS_YSKS
, MS_MZGHF
WHERE MS_YYHY.KSDM = MS_GHKS.KSDM
AND MS_YYHY.YSDM = GY_YGDM.YGDM
AND MS_YSKS.Mzks = MS_YYHY.KSDM
AND MS_YSKS.YSDM = GY_YGDM.YGDM
AND MS_YSKS.FYXH = MS_MZGHF.FYXH
AND MS_YYHY.TGBZ = 0
AND MS_YYHY.YYBZ = 0
AND MS_YYHY.NWBZ = 1
AND MS_YYHY.GZRQ = to_date('20240309','yyyymmdd')
AND MS_YYHY.YSDM = '2431'
AND MS_GHKS.KSDM in (20, 107, 4100))
group by ksdm
, jgid) b
WHERE a.ksdm = b.ksdm
AND a.jgid = b.jgid
AND a.ksdm in (20, 107, 4100)
AND a.ksdm <> 20
order by a.jgid;
SYS@127.0.0.1:9014/ywdb> @ sql_id at0rwvy9jukny
--SQL_ID = at0rwvy9jukny
SELECT a.ksdm
, a.jgid
, a.ksmc
, b.act
FROM ms_ghks a
, (
SELECT count(1) as act, ksdm, jgid
FROM (
SELECT MS_YYHY.HYID
, to_char(MS_YYHY.GZRQ,'yyyymmdd') GZRQ, MS_YYHY.YYSD, MS_GHKS.KSMC, MS_YYHY.KSDM, GY_YGDM.YGXM,
MS_YYHY.YSDM, MS_YYHY.YYBZ, MS_MZGHF.GHF, MS_MZGHF.ZLF, MS_GHKS.JGID
FROM MS_YYHY
, MS_GHKS
, GY_YGDM
, MS_YSKS
, MS_MZGHF
WHERE MS_YYHY.KSDM = MS_GHKS.KSDM
AND MS_YYHY.YSDM = GY_YGDM.YGDM
AND MS_YSKS.Mzks = MS_YYHY.KSDM
AND MS_YSKS.YSDM = GY_YGDM.YGDM
AND MS_YSKS.FYXH = MS_MZGHF.FYXH
AND MS_YYHY.TGBZ = 0
AND MS_YYHY.YYBZ = 0
AND MS_YYHY.NWBZ = 1
AND MS_YYHY.GZRQ = to_date('20240312','yyyymmdd')
AND MS_YYHY.YSDM = '761'
AND MS_GHKS.KSDM in (02, 142, 4096))
group by ksdm
, jgid) b
WHERE a.ksdm = b.ksdm
AND a.jgid = b.jgid
AND a.ksdm in (02, 142, 4096)
AND a.ksdm <> 02
order by a.jgid;
--//这两条语句基本一样,差别在于GZRQ，ksdm等信息不同,当然不一定存在优化的需求。像这类语句因为没有使用绑定变量，每个的执行
--//次数不是很多，导致许多情况下遗漏了优化的工作。
--//注:这些sql语句已经不再共享池,并且DBA_HIST_SQLTEXT也没有记录,dba_hist相关视图会漏调这些信息.
--//仔细看就可以发现开发很不好的编写sql风格,注意最内层.他是先写出最内层的语句然后在根据需要写出对应sql语句的,而且使用的
--//拼接代码,MS_GHKS.KSMC里层就有,根本无需要再次连接表ms_ghks a.
--//自己改写如下:
SELECT ms_ghks.ksdm,ms_ghks.jgid,ms_ghks.ksmc,count(1) AS act
FROM ms_yyhy, ms_ghks, gy_ygdm, ms_ysks, ms_mzghf
WHERE ms_yyhy.ksdm = ms_ghks.ksdm
AND ms_yyhy.ysdm = gy_ygdm.ygdm
AND ms_ysks.mzks = ms_yyhy.ksdm
AND ms_ysks.ysdm = gy_ygdm.ygdm
AND ms_ysks.fyxh = ms_mzghf.fyxh
AND ms_yyhy.tgbz = 0
AND ms_yyhy.yybz = 0
AND ms_yyhy.nwbz = 1
AND ms_yyhy.gzrq = to_date('20240312','yyyymmdd')
AND ms_yyhy.ysdm = '761'
AND ms_ghks.ksdm IN (02, 142, 4096)
AND ms_ghks.ksdm <> 02
GROUP BY ms_ghks.ksdm,ms_ghks.jgid,ms_ghks.ksmc
ORDER BY ms_ghks.jgid
--//许多表连接不知道是否多余!!
SYS@127.0.0.1:9014/ywdb> select count(*) from ms_yyhy where gzrq = to_date('20240312','yyyymmdd');
COUNT(*)
------------
1515
SYS@127.0.0.1:9014/ywdb> select count(*) from ms_yyhy where gzrq = to_date('20240312','yyyymmdd') and ysdm = '761';
COUNT(*)
------------
0
--//问题在于回表记录太多。
--//拿sqlh.sql脚本修改一下，建立sqlf.sql脚本
--//sqlh.sql脚本原始tpt的来源awr_sqlstats_per_exec.sql
$ ls -li tpt/awr/ | grep 3967980
3967980 -rw-r--r-- 2 oracle oinstall 3543 2024-03-11 10:28:54 awr_sqlstats_per_exec.sql
3967980 -rw-r--r-- 2 oracle oinstall 3543 2024-03-11 10:28:54 sqlh.sql
$ diff sqlh.sql sqlf.sql
60c60
< AND sql_id = '&1'
---
> AND FORCE_MATCHING_SIGNATURE = '&1'
--//建立sqlff.sql脚本
$ cat sqlff.sql
prompt
prompt time unit : millisecond
prompt
@awr/sqlf &1 % sysdate-&&2 sysdate
--//只要建立gzrq,ysdm的复合索引,就可以大大的减少逻辑读.
SYS@127.0.0.1:9014/ywdb> @ sqlff 12120531484079936454 .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-11 09:00:29 1 0cfqyzc2z73gc 1882478372 1 56 55 0.0 76 0 0 0.0 0 0 0
2024-03-11 09:00:29 1 1bssptnxvqdfq 1882478372 2 1 1 0.0 15 0 0 0.0 0 0 0
2024-03-11 09:00:29 2 1rm2ju80dfu38 1882478372 2 66 64 0.0 77 0 0 0.0 0 0 0
2024-03-11 09:00:29 1 4m37zx7agdrn6 1882478372 3 1 1 0.0 15 0 0 0.0 0 0 0
2024-03-11 09:00:29 1 5rn23fkq7chh2 1882478372 3 44 43 0.0 80 0 0 0.0 0 0 0
2024-03-11 09:00:29 1 642hkqu9hq5df 1882478372 2 39 34 0.0 44 0 0 0.0 0 0 0
2024-03-11 09:00:29 1 6f76ha6rqwpgz 1882478372 3 44 42 0.0 54 0 0 0.0 0 0 0
2024-03-11 09:00:29 1 6wffk45u6m88f 1882478372 3 67 66 0.0 75 0 0 0.0 0 0 0
2024-03-11 09:00:29 1 76mvnz67169y2 1882478372 1 56 54 0.0 78 0 0 0.0 0 0 0
2024-03-11 09:00:29 1 8x7gu978vsw4w 1882478372 1 100 92 0.0 76 0 0 0.0 0 0 0
2024-03-11 09:00:29 1 abnvpwudu7czc 1882478372 2 41 40 0.0 46 0 0 0.0 0 0 0
2024-03-11 09:00:29 2 anq94ugchz254 1882478372 4 66 65 0.0 77 0 0 0.0 0 0 0
2024-03-11 09:00:29 1 ata0cjw5hcfdn 1882478372 322 1 0 0.0 19 0 0 0.0 0 0 0
2024-03-11 09:00:29 2 ata0cjw5hcfdn 1882478372 276 1 1 0.0 20 0 0 0.0 0 0 0
2024-03-11 09:00:29 1 aya87adsgwtws 1882478372 4 56 53 0.0 62 0 0 0.0 0 0 0
2024-03-11 09:00:29 2 c4jvyf9dhc1y8 1882478372 3 26 25 0.0 36 0 0 0.0 0 0 0
2024-03-11 09:00:29 1 c7s8w9jr95srx 1882478372 3 66 64 0.0 77 0 0 0.0 0 0 0
2024-03-11 09:00:29 1 ctxhvbu26pt5u 1882478372 3 63 60 0.0 75 0 0 0.0 0 0 0
2024-03-11 09:00:29 1 djcx68hgy4pzq 1882478372 4 72 67 0.0 77 0 0 0.0 0 0 0
2024-03-11 09:00:29 1 fqscmr0bq8tzm 1882478372 1 58 56 0.0 77 0 0 0.0 0 0 0
20 rows selected.
--//dba_hist_sqlstat 在0.1天就记录这么多(真是的情况是许多没有记录)。如果查询1天就很多了，并且有点奇怪的是
--//ROWS_PER_EXEC=0,到底什么情况,索引存在回表，但是逻辑读并不是很高7X。
SYS@127.0.0.1:9014/ywdb> select sum(executions) from gv$sqlarea where FORCE_MATCHING_SIGNATURE=12120531484079936454;
SUM(EXECUTIONS)
---------------
391015
SYS@127.0.0.1:9014/ywdb> host sleep 60
SYS@127.0.0.1:9014/ywdb> select sum(executions) from gv$sqlarea where FORCE_MATCHING_SIGNATURE=12120531484079936454;
SUM(EXECUTIONS)
---------------
391033
SYS@127.0.0.1:9014/ywdb> host sleep 60
SYS@127.0.0.1:9014/ywdb> select sum(executions) from gv$sqlarea where FORCE_MATCHING_SIGNATURE=12120531484079936454;
SUM(EXECUTIONS)
---------------
391074
--//1分钟内有XX次的执行,不理解!!
--//建立如下索引:
CREATE INDEX PORTAL_HIS.i_MS_YYHY_GZRQ_YSDM ON PORTAL_HIS.MS_YYHY
(GZRQ, YSDM)
LOGGING
STORAGE (
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
)
NOPARALLEL
COMPRESS 1
COMPUTE STATISTICS
/
--//将另外的GZRQ索引先INVISIBLE观察看看一段时间，我想通过这个例子说明没有绑定变量结果有可能漏掉需要优化的sql语句。
--//我将上面提到的FORCE_MATCHING_SIGNATURE的sql语句看了一遍，基本都是上面类似的sql语句。
--//这些sql语句不做优化问题也不大，但是执行频率很高,没有合理的使用绑定变量导致在优化时出现遗漏的情况。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/267265/viewspace-3008675/ ，如需转载，请注明出处，否则将追究法律责任。
最新文章
- [20261102]光标跟踪事件9.txt
- [20261102]光标跟踪事件8.txt
- [20261102]为什么出现光标出现TOP_LEVEL_RPI_CURSOR不共享.txt
- [20261102]改变pdb名字.txt
- [20261018]光标跟踪事件3.txt
- [20261016]光标跟踪事件2.txt
- [20261015]模拟kksfbc child completion等待事件2.txt
- [20261013]光标跟踪事件.txt
- [20261008]验证Shared Memory Segments key以及Semaphore Arrays Key(11g).txt
- [20261008]gdb引入结构体是否可行.txt
