[20191106]隐式转换.txt

来源：这里教程网时间：2026-03-03 14:29:03 作者：

[20191106]隐式转换.txt --//生产系统出现性能问题,原来看的链接如下: http://blog.itpub.net/267265/viewspace-2638863/=>[20190320]关于使用smem查看内存使用的问题.txt --//我在使用smem测试内存使用时认为,对于OLTP系统在使用HugePages的前提下.每个会话平均消耗PSS不会过5M. --//看来这个前提要好加上一条里面不能存在大量不良sql语句,才可能达到每个会话平均消耗PSS不会过5M. --//上面的系统当时出现短暂的IO问题.我当时就隐约遇到这套系统以后会频繁出现问题.果然不出所料,6.7,8月频繁出现 --//运行缓慢,监听中断的情况. --//采用HugePages后的情况如下: # smem -tk -U oracle -P oraclepeis PID User Command Swap USS PSS RSS 28605 oracle oraclepeis (LOCAL=NO) 4.2M 4.0K 19.0K 3.5M 3956 oracle oraclepeis (LOCAL=NO) 3.4M 232.0K 286.0K 14.1M 4971 oracle oraclepeis (LOCAL=NO) 3.0M 1.7M 1.9M 22.0M 8198 oracle oraclepeis (LOCAL=NO) 3.0M 1.9M 2.0M 6.1M 6554 oracle oraclepeis (LOCAL=NO) 1.6M 2.0M 2.0M 15.9M 4245 oracle oraclepeis (LOCAL=NO) 1.6M 2.0M 2.0M 15.9M 3906 oracle oraclepeis (LOCAL=NO) 2.9M 2.5M 2.6M 23.5M 4130 oracle oraclepeis (LOCAL=NO) 2.0M 2.8M 2.9M 22.4M 6180 oracle oraclepeis (LOCAL=NO) 320.0K 3.3M 3.3M 17.2M 5481 oracle oraclepeis (LOCAL=NO) 212.0K 3.4M 3.5M 17.3M 4973 oracle oraclepeis (LOCAL=NO) 124.0K 3.5M 3.5M 17.4M 8385 oracle oraclepeis (LOCAL=NO) 1.3M 3.5M 3.6M 11.5M 17092 oracle oraclepeis (LOCAL=NO) 0 3.6M 3.6M 17.4M .. 16205 oracle oraclepeis (LOCAL=NO) 0 5.6M 5.8M 27.8M 16032 oracle oraclepeis (LOCAL=NO) 0 5.4M 5.9M 16.4M 15304 oracle oraclepeis (LOCAL=NO) 0 6.4M 6.9M 19.1M ------------------------------------------------------------------------------- 238 1 27.9M 970.0M 993.0M 4.8G --//PSS=993M,933/238 = 4.17M,swap才使用27.9M.当前系统相对空闲... --//并且还打了p6139856_10204_Linux-x86-64.zip补丁,避免监听中断. --//但是用户还是抱怨运行缓慢,我叫同事看awr报表看看sql语句问题,可惜一直没人看.今天正好看一下.实际上我现在登录服务器都明显 --//感觉反应迟钝.一些os命令第一次执行也会很慢. 1.环境: SYSTEM@192.168.22.222:1521/peis> @ ver1 PORT_STRING VERSION BANNER ------------------------------ -------------- ---------------------------------------------------------------- x86_64/Linux 2.4.xx 10.2.0.4.0 Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi 2.发现问题: SYSTEM@192.168.22.222:1521/peis> @ sql_id 5whcb1qwww6x1 SQL_ID SQLTEXT ------------- ------------------------------------------------------------------------------------------------------- 5whcb1qwww6x1 select count ( *) from pe_visit where pe_level ='二楼体检区' and pe_pre_date > :1 and pe_pre_date < :2 view dba_hist_sqltext SQL_ID SQLTEXT ------------- ------------------------------------------------------------------------------------------------------- 5whcb1qwww6x1 select count ( *) from pe_visit where pe_level ='二楼体检区' and pe_pre_date > :1 and pe_pre_date < :2 --//发现这条语句很简单,pe_pre_date的索引也存在. SYSTEM@192.168.22.222:1521/peis> @ dpc 5whcb1qwww6x1 '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID 5whcb1qwww6x1, child number 1 ------------------------------------- select count ( *) from pe_visit where pe_level ='二楼体检区' and pe_pre_date > :1 and pe_pre_date < :2 Plan hash value: 891755927 --------------------------------------------------------------------------------- | Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | --------------------------------------------------------------------------------- | 1 | SORT AGGREGATE | | 1 | 14 | | | |* 2 | FILTER | | | | | | |* 3 | TABLE ACCESS FULL| PE_VISIT | 1579 | 22106 | 13210 (1)| 00:02:39 | --------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 3 - SEL$1 / PE_VISIT@SEL$1 Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter(:1<:2) 3 - filter(("PE_LEVEL"='二楼体检区' AND INTERNAL_FUNCTION("PE_PRE_DATE")>:1 AND INTERNAL_FUNCTION("PE_PRE_DATE")<:2)) --//发现过滤条件很奇怪,怎么是INTERNAL_FUNCTION("PE_PRE_DATE")>:1,真心讲好久没做优化,一时没反应过来.实际代入参数是timestamp类型. SYSTEM@192.168.22.222:1521/peis> @ bind_cap 5whcb1qwww6x1 '' C200 --------------------------------------------------------------------------------------------------------------------------------------------- select count ( *) from pe_visit where pe_level ='二楼体检区' and pe_pre_date > :1 and pe_pre_date < :2 SQL_ID CHILD_NUMBER WAS NAME POSITION MAX_LENGTH LAST_CAPTURED DATATYPE_STRING VALUE_STRING ------------- ------------ --- ---------------------------------------- ---------- ---------- ------------------- --------------- ------------ 5whcb1qwww6x1 0 YES :1 1 11 2019-11-06 11:26:38 TIMESTAMP YES :2 2 11 2019-11-06 11:26:38 TIMESTAMP 1 YES :1 1 11 2019-11-06 15:42:06 TIMESTAMP YES :2 2 11 2019-11-06 15:42:06 TIMESTAMP --//噢VALUE_STRING没有显示,原来带入的参数类型是TIMESTAMP.导致出现隐式转换. --//如果类型是timestamp,相关的值也保存在value_anydata中，如何抽取查看链接: --//http://blog.itpub.net/267265/viewspace-713215/=>v$sql_bind_capture与timestamp类型的绑定变量的数值 SELECT SQL_ID ,NAME ,POSITION ,datatype_string ,max_length ,value_string ,DUMP (value_anydata) c90 ,ANYDATA.accesstimestamp (value_anydata) c30 FROM v$sql_bind_capture a WHERE a.value_string IS NULL AND a.value_anydata IS NOT NULL AND datatype_string = 'TIMESTAMP' AND sql_id = '5whcb1qwww6x1'; SQL_ID NAME POSITION DATATYPE_STRING MAX_LENGTH VALUE_STRING C90 C30 ------------- --------- -------- --------------- ---------- ------------ ---------------------------------------------------------------------------------- ------------------------------ 5whcb1qwww6x1 :1 1 TIMESTAMP 11 Typ=58 Len=21: 32,234,132,6,0,0,0,0,224,218,203,102,159,127,0,0,80,232,217,102,159 2019-11-12 00:00:00.000000000 :2 2 TIMESTAMP 11 Typ=58 Len=21: 32,234,132,6,0,0,0,0,224,218,203,102,159,127,0,0,80,232,217,102,159 2019-11-12 23:59:59.000000000 :1 1 TIMESTAMP 11 Typ=58 Len=21: 32,234,132,6,0,0,0,0,224,218,203,102,159,127,0,0,80,232,217,102,159 2019-11-07 00:00:00.000000000 :2 2 TIMESTAMP 11 Typ=58 Len=21: 32,234,132,6,0,0,0,0,224,218,203,102,159,127,0,0,80,232,217,102,159 2019-11-07 23:59:59.000000000 --//可以发现取值范围是一天的,而类型是timestamp类型.语句存在隐式转换.一般这样的语句不应该仅仅1条. select distinct sql_id,name from ( SELECT SQL_ID ,NAME ,POSITION ,datatype_string ,max_length ,value_string ,DUMP (value_anydata) c90 ,ANYDATA.accesstimestamp (value_anydata) c30 FROM v$sql_bind_capture a WHERE a.value_string IS NULL AND a.value_anydata IS NOT NULL AND datatype_string = 'TIMESTAMP'); SQL_ID NAME ------------- -------------------- 44zxp8pzcs49d :8 gfj04ktqsyzrp :ADT_BEGIN :ADT_END 44zxp8pzcs49d :20 5whcb1qwww6x1 :2 cbukx1bujy80k :1 dbumjx9u8334g :16 1v0rzurbdpywr :5 91z2qtwn4n267 :ADT_END_DATE 647znt9fgk0yu :20 8qndjkwt2gpcd :AD_REGISTER_DATE 45smuv2b4w44k :4 c3fxr45mrwymr :8 6hh7qt2c9xg75 :4 btgu31w205r5n :8 647znt9fgk0yu :8 91z2qtwn4n267 :ADT_BEGIN_DATE 5whcb1qwww6x1 :1 c3fxr45mrwymr :21 axxmmcxn819pr :5 97ywkkx99jbnc :1 btgu31w205r5n :19 0q0u3shrucuka :4 60dmvn8gazaxy :LDT_BEG :LDT_END 25 rows selected. --//昏有24条语句可能涉及这个问题.还好不是很多,我估计其中一位开发这样设计程序.不过我查询如下 SELECT DISTINCT sql_id, name FROM (SELECT SQL_ID ,NAME ,POSITION ,datatype_string ,max_length ,value_string ,DUMP (value_anydata) c90 ,ANYDATA.accesstimestamp (value_anydata) c30 FROM v$sql_bind_capture a WHERE datatype_string = 'DATE'); --//也就是43条.先不管它. 3.建立索引: CREATE INDEX PHYEXAM.I_PE_VISIT_PE_LEVEL_PE_PRE_D ON PHYEXAM.PE_VISIT (PE_LEVEL, PE_PRE_DATE) LOGGING TABLESPACE TSP_PE PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 BUFFER_POOL DEFAULT ) NOPARALLEL COMPRESS 1; --//查看执行计划: SYSTEM@192.168.22.222:1521/peis> @ dpc 5whcb1qwww6x1 '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID 5whcb1qwww6x1, child number 1 ------------------------------------- select count ( *) from pe_visit where pe_level ='二楼体检区' and pe_pre_date > :1 and pe_pre_date < :2 Plan hash value: 2272508911 -------------------------------------------------------------------------------------------------------- | Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | -------------------------------------------------------------------------------------------------------- | 1 | SORT AGGREGATE | | 1 | 15 | | | |* 2 | FILTER | | | | | | |* 3 | INDEX FAST FULL SCAN| I_PE_VISIT_PE_LEVEL_PE_PRE_D | 1640 | 24600 | 1041 (3)| 00:00:13 | -------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 3 - SEL$1 / PE_VISIT@SEL$1 Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter(:1<:2) 3 - filter(("PE_LEVEL"='二楼体检区' AND INTERNAL_FUNCTION("PE_PRE_DATE")>:1 AND INTERNAL_FUNCTION("PE_PRE_DATE")<:2)) --//检查发现PE_LEVEL建立有直方图.删除看看. SYSTEM@192.168.22.222:1521/peis> execute dbms_stats.delete_column_stats(ownname=>'PHYEXAM',tabname=>'PE_VISIT',colname=>'PE_LEVEL'); PL/SQL procedure successfully completed. 222:1521/peis> @ dpc 5whcb1qwww6x1 '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID 5whcb1qwww6x1, child number 0 ------------------------------------- select count ( *) from pe_visit where pe_level ='二楼体检区' and pe_pre_date > :1 and pe_pre_date < :2 Plan hash value: 3803594299 ---------------------------------------------------------------------------------------------------- | Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | ---------------------------------------------------------------------------------------------------- | 1 | SORT AGGREGATE | | 1 | 35 | | | |* 2 | FILTER | | | | | | |* 3 | INDEX RANGE SCAN| I_PE_VISIT_PE_LEVEL_PE_PRE_D | 43 | 1505 | 21 (0)| 00:00:01 | ---------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 3 - SEL$1 / PE_VISIT@SEL$1 Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter(:1<:2) 3 - access("PE_LEVEL"='二楼体检区') filter((INTERNAL_FUNCTION("PE_PRE_DATE")>:1 AND INTERNAL_FUNCTION("PE_PRE_DATE")<:2)) SYSTEM@192.168.22.222:1521/peis> @ deltabuffer.sql 5whcb1qwww6x1 100 EXECUTIONS1 BUFFER_GETS1 ELAPSED_TIME1 ROWS_PROCESSED1 ----------- ------------ ------------- --------------- 2 3396 424297 2 ... sleep 100 , waiting .... EXECUTIONS2 BUFFER_GETS2 ELAPSED_TIME2 ROWS_PROCESSED2 ----------- ------------ ------------- --------------- 6 10188 1206310 6 总buffer_gets 每次buffer_gets 执行次数总执行时间每次执行时间总处理记录数平均处理记录数 ------------- --------------- ---------- ---------- ------------ ------------ -------------- 6792 1698 4 782013 195503.25 4 1 --//现在每次逻辑读1698,1698*8192/1024/1024 = 13.26M. --//总之不论是 INDEX FAST FULL SCAN还是INDEX RANGE SCAN 扫描都是治标,治本就是代入参数类型修改为date类型. --//还有就是一种变态的优化模式通过schedule人为地修改条件pe_pre_date between trunc(sysdate)-20 and trunc(sysdate)-10的PE_LEVEL"='二楼体检去'. --//当然首先要设置pe_pre_date < trunc(sysdate)-20 的PE_LEVEL"='二楼体检去'.这样人为缩写索引扫描范围.当然这个操作必须与用户与开发协商. --//这样的修改是否可能要讨论确定. --//还有1种可能修改字段pe_pre_date的类型为timestamp，不知道这个是否可行。 BEGIN SYS.DBMS_STATS.LOCK_TABLE_STATS ( OwnName => 'PHYEXAM' ,TabName => 'PE_VISIT'); END; / 4.收尾: --//重新分析表,让执行计划选择INDEX FAST FULL SCAN. --//又是一例豆腐渣工程...

编辑推荐：

返回列表

[20191106]隐式转换.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：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推