[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了解整体状态还是不错的.
[20160608]perf定位问题.txt
2021-11-30 15:12:13