[20221128]dg数据库优化问题.txt
--//前几天我才知道,开发设置的一些查询在dg上运行,据说这些查询很慢.我想看看这些是什么语句.在主库自然无法查询.
--//不过实际上这些sql语句在备库的共享池内存里还是存在,可以使用tpt 的ashtop查询获得,通过测试说明问题.
--//顺便提一下我觉得备库查询应该是一些报表之类的语句,而不是要求实时性很高的sql语句,开发或者同事不应该轻率地把这些语句移
--//到备库执行,不能仅仅因为慢而转移到备库,而是要定位问题在那里,目前备库的磁盘IO性能很差.
1.环境:
SYS@192.168.100.237:1521/orcldg> @ pr
==============================
PORT_STRING : x86_64/Linux 2.4.xx
VERSION : 19.0.0.0.0
BANNER : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
BANNER_FULL : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
BANNER_LEGACY : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
CON_ID : 0
PL/SQL procedure successfully completed.
2.分析:
SYS@192.168.100.237:1521/orcldg> @ ashtop sql_id 1=1 &100day
Total Distinct Distinct
Seconds AAS %This SQL_ID FIRST_SEEN LAST_SEEN Execs Seen Tstamps
--------- ------- ------- ------------- ------------------- ------------------- ---------- --------
22078 .0 28% | 2022-11-27 11:35:46 2022-11-28 09:47:11 1 10864
15154 .0 19% | 623b841u978k2 2022-11-27 11:35:53 2022-11-28 09:47:05 9677 12895
13389 .0 17% | cscm97g90y7rx 2022-11-27 17:30:08 2022-11-28 08:36:04 19 2271
11619 .0 15% | a91d0rd8qvu21 2022-11-27 11:35:43 2022-11-28 09:46:54 216 6974
10233 .0 13% | bu48z014njcg4 2022-11-27 11:36:06 2022-11-28 09:46:09 9268 4554
1468 .0 2% | 67b206yz6h2p5 2022-11-28 08:44:44 2022-11-28 09:03:27 4 1124
1341 .0 2% | 91zy2kh7pzs0j 2022-11-28 09:23:56 2022-11-28 09:36:12 2 737
...
--//sql_id= cscm97g90y7rx ,67b206yz6h2p5,91zy2kh7pzs0j 执行次数很少,耗时很多.
--//仅仅分析其中一条cscm97g90y7rx.
3.继续:
SYS@192.168.100.237:1521/orcldg> @ tpt/sqlid cscm97g90y7rx %
CH# PLAN_HASH PARSES H_PARSES EXECUTIONS FETCHES ROWS_PROCESSED ROWS_PER_FETCH CPU_SEC_EXEC ELA_SEC_EXEC LIOS_PER_EXEC PIOS_PER_EXEC TOTAL_CPU_SEC TOTAL_ELA_SEC TOTAL_IOWAIT_SEC TOTAL_LIOS TOTAL_PIOS SORTS USERS_EXECUTING LAST_ACTIVE_TIME PARENT_HANDLE OBJECT_HANDLE
----- ---------- ---------- ---------- ---------- ---------- -------------- -------------- ------------ ------------ ------------- ------------- ------------- ------------- ---------------- ---------- ---------- ---------- --------------- ------------------- ---------------- ----------------
0 1711169267 18 5 15 19 110 5.78947368 75.482 886.633 16648216 237484 1132.235 13299.49 12237.5192 249723237 3562262 0 0 2022-11-28 08:32:05 000000008191E680 000000008191CF28
1 1711169267 1 1 1 1 0 0 1.433 1.440 701770 0 1.433 1.44 0 701770 0 0 0 2022-11-28 08:21:22 000000008191E680 000000007EE88B90
2 884840938 0 1 1 1 0 0 .029 .060 140 0 .029 .06 0 140 0 2 0 2022-11-28 08:21:22 000000008191E680 0000000070288CF8
3 884840938 1 1 2 2 0 0 .013 .013 136 0 .026 .027 0 272 0 4 0 2022-11-28 08:31:05 000000008191E680 000000007E3A2D50
4 884840938 66 1 67 71 178 2.50704225 .001 .004 102 0 .075 .277 .193281 6818 14 134 0 2022-11-28 09:39:15 000000008191E680 0000000085A93518
--//实际上仅仅child_number=0,1的时间有点长.逻辑IO有点大.
SYS@192.168.100.237:1521/orcldg> @ dpcx cscm97g90y7rx '' 0
-------------------------------------
SQL_ID cscm97g90y7rx, child number 0
-------------------------------------
...
--//sql语句太长,省略....
..
Plan hash value: 1711169267
---------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
---------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 21 (100)| |
| 1 | UNION-ALL | | | | | |
|* 2 | FILTER | | | | | |
| 3 | NESTED LOOPS | | 1 | 103 | 10 (0)| 00:00:01 |
| 4 | NESTED LOOPS | | 10 | 103 | 10 (0)| 00:00:01 |
|* 5 | TABLE ACCESS BY INDEX ROWID BATCHED| LIS_TEST | 1 | 73 | 5 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | I_LIS_TEST_AUDIT_TIME | 2 | | 3 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | PK_LIS_RESULT | 10 | | 3 (0)| 00:00:01 |
| 8 | TABLE ACCESS BY INDEX ROWID | LIS_RESULT | 10 | 300 | 5 (0)| 00:00:01 |
|* 9 | FILTER | | | | | |
| 10 | NESTED LOOPS OUTER | | 1 | 163 | 11 (0)| 00:00:01 |
| 11 | NESTED LOOPS | | 1 | 117 | 7 (0)| 00:00:01 |
|* 12 | TABLE ACCESS BY INDEX ROWID BATCHED| LIS_TEST | 1 | 76 | 5 (0)| 00:00:01 |
|* 13 | INDEX RANGE SCAN | I_LIS_TEST_AUDIT_TIME | 2 | | 3 (0)| 00:00:01 |
|* 14 | TABLE ACCESS BY INDEX ROWID BATCHED| MICRO_RESULT_GERM | 1 | 41 | 2 (0)| 00:00:01 |
|* 15 | INDEX RANGE SCAN | IX_MICRO_RESULT_GERM_TEST_ID | 1 | | 1 (0)| 00:00:01 |
|* 16 | TABLE ACCESS BY INDEX ROWID BATCHED | MICRO_RESULT_DRUG_SENS | 20 | 920 | 4 (0)| 00:00:01 |
|* 17 | INDEX RANGE SCAN | IX_MICRO_RESULT_DRU_1650258890 | 21 | | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
1 - :1 (CHAR(30), CSID=852): '60518644'
2 - :2 (CHAR(30), CSID=852): '431122198608110031'
3 - (CHAR(30), CSID=852): '2022/11/13'
4 - (CHAR(30), CSID=852): '2022-11-29'
5 - :1 (CHAR(30), CSID=852, Primary=1)
6 - :2 (CHAR(30), CSID=852, Primary=2)
7 - (CHAR(30), CSID=852, Primary=3)
8 - (CHAR(30), CSID=852, Primary=4)
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(TO_DATE(:STR_TO,'yyyy-mm-dd')>=TO_DATE(:STR_DTFROM,'yyyy-mm-dd'))
5 - filter(("T"."REPORT_TYPE"=1 AND ("T"."IDENTITY_ID"=SYS_OP_C2C(:STR_SFZH) OR
"T"."PAT_ID"=SYS_OP_C2C(:STR_MZHM))))
6 - access("T"."AUDIT_TIME">=TO_DATE(:STR_DTFROM,'yyyy-mm-dd') AND
"T"."AUDIT_TIME"<=TO_DATE(:STR_TO,'yyyy-mm-dd'))
7 - access("T"."ID"="R"."ID")
9 - filter(TO_DATE(:STR_TO,'yyyy-mm-dd')>=TO_DATE(:STR_DTFROM,'yyyy-mm-dd'))
12 - filter(("T"."REPORT_TYPE"=1 AND ("T"."IDENTITY_ID"=SYS_OP_C2C(:STR_SFZH) OR
"T"."PAT_ID"=SYS_OP_C2C(:STR_MZHM)) AND "T"."STATE">=90))
13 - access("T"."AUDIT_TIME">=TO_DATE(:STR_DTFROM,'yyyy-mm-dd') AND
"T"."AUDIT_TIME"<=TO_DATE(:STR_TO,'yyyy-mm-dd'))
14 - filter("G"."IS_DELETED"=0)
15 - access("T"."ID"="G"."TEST_ID")
16 - filter("S"."IS_DELETED"=0)
17 - access("G"."ID"="S"."GERM_RESULT_ID")
Note
-----
- this is an adaptive plan
- 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
--//注意看id=5,6,12,13就很容易定位了,实际上用错了索引导致的问题,T.IDENTITY_ID,T.PAT_ID索引都存在,出现SYS_OP_C2C函数是因
--//为表字段定义是nvarchar2类型,而带入的参数是char类型导致的问题,发生在绑定变量那边,不存在隐式转换问题.而执行计划选择
--//I_LIS_TEST_AUDIT_TIME日期类索引,查询范围2022/11/13,2022-11-29(操作员奇葩,竟然使用两种格式)有点大,导致大量的逻辑读.
--//实际上oracle 的acs 纠正这个执行计划后面的child_number =2,3,4执行都很快.只要使用sql_profile文档该执行计划就ok了.
4.再继续:
--//67b206yz6h2p5 ,91zy2kh7pzs0j:
SYS@192.168.100.237:1521/orcldg> @ bind_cap 67b206yz6h2p5 ''
C200
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
select TO_CHAR(a.test_date,'YYYY-MM') 日期,OFFICE_NAME 检验组,item.id 项目ID,item.combine_name 项目名称, count(distinct a.barcode) 项目数量,count(b.ITEM_ID) 结果数量,item.fee 单价,count(distinct a.ba
rcode)*item.fee 应收总金额 from lis_test a, lis_result b,com_order_item item where a.id = b.id and b.order_item_id=item.id(+) and test_date between :StartDate and :EndDate and a.inst_id in (:Instid
s1) and a.office_id in (:Officeids1) group by TO_CHAR(a.test_date,'YYYY-MM'),OFFICE_NAME,item.id,item.combine_name,item.fee
SQL_ID CHILD_NUMBER WAS NAME POSITION MAX_LENGTH LAST_CAPTURED DATATYPE_STRING VALUE_STRING C30
------------- ------------ --- ----------- -------- ---------- ------------------- --------------- ------------ ------------------------------
67b206yz6h2p5 0 YES :STARTDATE 1 11 2022-11-28 09:00:44 TIMESTAMP 2022-10-01 00:00:00.000000000
YES :ENDDATE 2 11 2022-11-28 09:00:44 TIMESTAMP 2022-10-30 00:00:00.000000000
YES :INSTIDS1 3 22 2022-11-28 09:00:44 NUMBER 2890
YES :OFFICEIDS1 4 22 2022-11-28 09:00:44 NUMBER 241
SYS@192.168.100.237:1521/orcldg> @ bind_cap 91zy2kh7pzs0j ''
C200
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
select TO_CHAR(a.test_date,'YYYY') 日期,inst_name 检验仪器,item.id 项目ID,item.combine_name 项目名称, count(distinct a.barcode) 项目数量,count(b.ITEM_ID) 结果数量,item.fee 单价,count(distinct a.barco
de)*item.fee 应收总金额 from lis_test a, lis_result b,com_order_item item where a.id = b.id and b.order_item_id=item.id(+) and test_date between :StartDate and :EndDate and a.inst_id in (:Instids1,
:Instids2) and a.office_id in (:Officeids1) group by TO_CHAR(a.test_date,'YYYY'),inst_name,item.id,item.combine_name,item.fee
SQL_ID CHILD_NUMBER WAS NAME POSITION MAX_LENGTH LAST_CAPTURED DATATYPE_STRING VALUE_STRING C30
------------- ------------ --- ------------ -------- ---------- ------------------- --------------- ------------ ------------------------------
91zy2kh7pzs0j 0 YES :STARTDATE 1 11 2022-11-28 09:23:56 TIMESTAMP 2022-01-01 00:00:00.000000000
YES :ENDDATE 2 11 2022-11-28 09:23:56 TIMESTAMP 2022-10-31 00:00:00.000000000
YES :INSTIDS1 3 22 2022-11-28 09:23:56 NUMBER 1816
YES :INSTIDS2 4 22 2022-11-28 09:23:56 NUMBER 1553
YES :OFFICEIDS1 5 22 2022-11-28 09:23:56 NUMBER 241
--//这两个查询类似,不过日期范围很大,慢很正常.这样的查询才比较适合在备库完成.
5.看看623b841u978k2,a91d0rd8qvu21,bu48z014njcg4:
--//这些与cscm97g90y7rx类似,
SYS@192.168.100.237:1521/orcldg> @ bind_cap 623b841u978k2 ''
SQL_ID CHILD_NUMBER WAS NAME POSITION MAX_LENGTH LAST_CAPTURED DATATYPE_STRING VALUE_STRING
------------- ------------ --- --------------------- ---------- ------------------- --------------- ------------------
623b841u978k2 5 YES :STR_MZHM 1 32 2022-11-28 10:07:19 CHAR(32) 02202927
YES :STR_SFZH 2 128 2022-11-28 10:07:19 CHAR(128) 45262419830628281X
YES :STR_DTFROM 3 32 2022-11-28 10:07:19 CHAR(32) 2022-11-28
YES :STR_DTTO 4 32 2022-11-28 10:07:19 CHAR(32) 2022-11-29
7 YES :STR_MZHM 1 32 2022-11-28 09:57:29 CHAR(32) 90970466
YES :STR_SFZH 2 128 2022-11-28 09:57:29 CHAR(128) 45010319580511201X
YES :STR_DTFROM 3 32 2022-11-28 09:57:29 CHAR(32) 2000-01-01
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
YES :STR_DTTO 4 32 2022-11-28 09:57:29 CHAR(32) 2022-11-29
10 YES :STR_MZHM 1 32 2022-11-28 10:05:59 CHAR(32) 91209141
YES :STR_SFZH 2 128 2022-11-28 10:05:59 CHAR(128) 452730194812250516
YES :STR_DTFROM 3 32 2022-11-28 10:05:59 CHAR(32) 2022-08-30
YES :STR_DTTO 4 32 2022-11-28 10:05:59 CHAR(32) 2022-11-29
12 rows selected.
SYS@192.168.100.237:1521/orcldg> @ bind_cap a91d0rd8qvu21 ''
SQL_ID CHILD_NUMBER WAS NAME POSITION MAX_LENGTH LAST_CAPTURED DATATYPE_STRING VALUE_STRING C30
------------- ------------ --- ----------- -------- ---------- ------------------- --------------- ------------ ------------------------------
a91d0rd8qvu21 1 YES :STATE 1 22 2022-11-28 10:04:32 NUMBER 50
YES :TENANT_ID 2 22 2022-11-28 10:04:32 NUMBER 1
YES :STARTDATE 3 11 2022-11-28 10:04:32 TIMESTAMP 2022-11-25 00:00:00.000000000
YES :ENDDATE 4 11 2022-11-28 10:04:32 TIMESTAMP 2022-11-29 00:00:00.000000000
YES :PAT_ID 5 32 2022-11-28 10:04:32 VARCHAR2(32) 91225574
SYS@192.168.100.237:1521/orcldg> @ bind_cap bu48z014njcg4 ''
SQL_ID CHILD_NUMBER WAS NAME POSITION MAX_LENGTH LAST_CAPTURED DATATYPE_STRING VALUE_STRING
------------- ------------ --- ------------ -------- ---------- ------------------- --------------- -----------------
bu48z014njcg4 0 YES :STR_MZHM 1 32 2022-11-28 10:04:39 CHAR(32) 90544724
YES :STR_SFZH 2 128 2022-11-28 10:04:39 CHAR(128) 452425194007051226
YES :STR_DTFROM 3 32 2022-11-28 10:04:39 CHAR(32) 2022-11-26
YES :STR_DTTO 4 32 2022-11-28 10:04:39 CHAR(32) 2022-11-29
--//也都是选择了错误的索引,执行计划不再贴出.这样的情况就是对应表越来越大,要存在10%的变化才会启用分析表.导致oracle认为该
--//日期范围的记录很少,导致选择日期索引作为access条件.
--//还可以做一个猜测,开始可能操作员查询的范围很小,也就是当天的日期,这样由于表"一段"时间内没有分析过,导致oracle优化器认为
--//这段日期范围内记录很小,优先选择日期索引作为access条件,而没有选择门诊号码,身份证号,pad_id的索引作为access条件.还有就
--//是一些操作员偷懒.输入一个2000-01-01作为开始查询日子(看下划线),这样再走日期索引,逻辑读就很大.慢就很自然了.
--//出现这样的情况,快速解决的方法就是分析表.也使用sql profile稳定执行计划就ok了.不过我执行sql profile遇到一些问题另外写
--//blog说明问题.
--//这类问题我感觉oracle应该找到更好的解决方法,不能通过acs来纠正这个错误,有时候根本不可行.这样确实可能导致生产系统性能出
--//现抖动.
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/267265/viewspace-2926307/ ，如需转载，请注明出处，否则将追究法律责任。
最新文章
- [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
