[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.
--//又是一例豆腐渣工程...