[20191106]隱式轉換.txt

lfree發表於2019-11-08

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

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/267265/viewspace-2663172/,如需轉載,請註明出處,否則將追究法律責任。

相關文章