[20221128]dg数据库优化问题.txt

来源：这里教程网时间：2026-03-03 18:12:31 作者：

[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来纠正这个错误,有时候根本不可行.这样确实可能导致生产系统性能出 --//现抖动.

编辑推荐：

返回列表

[20221128]dg数据库优化问题.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：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推