[20160608]perf定位問題.txt

[20160608]perf定位問題.txt

-- 很久沒看生產系統的awr報表,有時候偷懶,我直接pert top看看呼叫的函式.
-- 以前我提到過我們生產系統一般靠前的是kcbgtcr排在前面,這個主要是邏輯讀.

#  perf top -k /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/oracle
   PerfTop:   10002 irqs/sec  kernel:28.3%  exact:  0.0% [1000Hz cycles],  (all, 24 CPUs)
-----------------------------------------------------------------------------------------------------------------
             samples  pcnt function                   DSO
             _______ _____ __________________________ ___________________________________________________________

             2219.00 11.6% lnxmul                     /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/oracle
             2020.00 10.5% rworofprFastUnpackRow      /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/oracle
             1087.00  5.7% evaopn2                    /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/oracle
             1063.00  5.5% lnxsum                     /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/oracle
             1056.00  5.5% evamul                     /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/oracle
              810.00  4.2% srsget                     /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/oracle
              587.00  3.1% srsqb1tp                   /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/oracle
              576.00  3.0% sorgetqbf                  /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/oracle
              514.00  2.7% lnxrou                     /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/oracle
              505.00  2.6% kaf4reasrp1km              /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/oracle
              440.00  2.3% qesaFastAggNonDistSS       /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/oracle
              427.00  2.2% qersoFetch                 /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/oracle
              409.00  2.1% evarou                     /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/oracle
              407.00  2.1% srsnext                    /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/oracle
              404.00  2.1% lnxsni                     /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/oracle
              298.00  1.6% kaf4reasrp0km              /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/oracle
              262.00  1.4% kcbgtcr                    /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/oracle
              250.00  1.3% __intel_new_memcpy         /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/oracle
              177.00  0.9% perf_file_section__process /usr/bin/2.6.39-400.126.1.el5uek/perf

--再次看到自己很少見到的函式lnxmul.

SELECT *
  FROM (  SELECT sql_id, COUNT (*)
            FROM V$ACTIVE_SESSION_HISTORY
           WHERE sample_time >= SYSDATE - 5 / 1440
        GROUP BY sql_id
        ORDER BY 2 DESC)
 WHERE ROWNUM <= 2;

SQL_ID          COUNT(*)
------------- ----------
8u25kty2p5aus       2093
                      50

--很明顯問題集中在sql_id='8u25kty2p5aus'.

SYSTEM> select sql_text from v$sql where sql_id='8u25kty2p5aus';
SQL_TEXT
------------------------------------------------------------
SELECT NVL(SUM(FYJE), :"SYS_B_0")
         FROM (SELECT (ROUND(B.YPSL * B.CFTS * B.YPDJ,:"SYS_
B_1")) FYJE                                           FROM M
S_CF01 A, MS_CF02 B
 WHERE A.CFSB = B.CFSB
      AND (A.MZXH IS NULL OR A.MZXH = :"SYS_B_2")
                                 AND A.BRID = :BRID
                                   AND A.JZXH = :JZXH
                                     AND A.KFRQ >= TO_DATE(:
SFRQ,:"SYS_B_3")
AND A.ZFPB = :"SYS_B_4" AND (A.FSLX NOT IN (:"SYS_B_5",:"SYS
_B_6")) OR A.FSLX IS NULL)


--透過toad格式化sql語句如下:

SELECT NVL (SUM (FYJE), :"SYS_B_0")
  FROM (SELECT (ROUND (B.YPSL * B.CFTS * B.YPDJ, :"SYS_B_1")) FYJE
          FROM MS_CF01 A, MS_CF02 B
         WHERE        A.CFSB = B.CFSB
                  AND (A.MZXH IS NULL OR A.MZXH = :"SYS_B_2")
                  AND A.BRID = :BRID
                  AND A.JZXH = :JZXH
                  AND A.KFRQ >= TO_DATE ( :SFRQ, :"SYS_B_3")
                  AND A.ZFPB = :"SYS_B_4"
                  AND (A.FSLX NOT IN ( :"SYS_B_5", :"SYS_B_6"))
               OR A.FSLX IS NULL)

--昏!寫sql語句也太不嚴謹.看看後面的or ,執行計劃變成了笛卡爾積.變成了不可能完成的語句.

--應該寫成如下:

SELECT NVL (SUM (FYJE), :"SYS_B_0")
  FROM (SELECT (ROUND (B.YPSL * B.CFTS * B.YPDJ, :"SYS_B_1")) FYJE
          FROM MS_CF01 A, MS_CF02 B
         WHERE     A.CFSB = B.CFSB
               AND (A.MZXH IS NULL OR A.MZXH = :"SYS_B_2")
               AND A.BRID = :BRID
               AND A.JZXH = :JZXH
               AND A.KFRQ >= TO_DATE ( :SFRQ, :"SYS_B_3")
               AND A.ZFPB = :"SYS_B_4"
               AND (   (A.FSLX NOT IN ( :"SYS_B_5", :"SYS_B_6"))
                    OR A.FSLX IS NULL))

--寫錯其實很正常,問題是如何測試的,順便執行一次就能發現問題,失望!!!
--我題目有一些誇大了,實際上還是透過其他定位問題.不過透過perf瞭解整體狀態還是不錯的.