WEB应用访问缓慢的问题定位

WEB应用访问缓慢的问题定位

欢迎关注博主公众号「java大师」, 专注于分享Java领域干货文章, 关注回复「资源」, 免费领取全网最热的Java架构师学习PDF, 转载请注明出处 http://www.javaman.cn/jszw/web-slow

1、如果页面能正常访问，但是每个页面的打开速度都很慢，处理方式如下：

1）首先查看应用服务器和数据库服务器的cpu和内存使用情况是不是很高

windows服务器通过任务管理器查看

windows截图-1.png

linux服务器通过top命令查看内存和cpu使用率：主要看1、2、3这三个指标有没有异常，内存和cpu有没有超过95%

linux截图-2.png

详细参数参见下了文档

Linux查看CPU和内存使用情况：https://www.cnblogs.com/mengchunchen/p/9669704.html

2）如果两个服务器的内存都不高，通过下面语句找到oracle等待时长最多的sql带入plsql进行验证

<pre class="md-fences md-end-block md-fences-with-lineno ty-contain-cm modeLoaded" spellcheck="false" lang="sql" cid="n13" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: var(--monospace); font-size: 0.9em; display: block; break-inside: avoid; text-align: left; white-space: normal; background-image: inherit; background-position: inherit; background-size: inherit; background-repeat: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; margin-bottom: 15px; margin-top: 15px; padding-top: 8px; padding-bottom: 6px; padding-left: 0px; width: inherit; color: rgb(51, 51, 51); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;"> SELECT ASH.USER_ID,
U.USERNAME,
S.SQL_TEXT,
SUM(ASH.WAIT_TIME + ASH.TIME_WAITED) TTL_WAIT_TIME
FROM VSQLAREA S, DBA_USERS U
WHERE ASH.SAMPLE_TIME BETWEEN SYSDATE - 15 / (24 * 60) AND SYSDATE
AND ASH.SQL_ID = S.SQL_ID
AND ASH.USER_ID = U.USER_ID
GROUP BY ASH.USER_ID, S.SQL_TEXT, U.USERNAME
ORDER BY TTL_WAIT_TIME</pre>

plsql验证

将sql放入plsql窗口，按【F5】进入执行计划页面

oracle执行计划分析-4.png

cost为耗费时长，执行顺序是按照缩进从右往左，从上到下执行，上图执行顺序为table access full--> table access full-->hash join-->all rows。如果一个列表查询的sql执行的cost耗费越大，结合oracle等待时长的sql，可以断定该sql存在问题!!!

将定位的sql提交给研发进行进一步的分析！！

目前发现数据库表中的部分索引建立以后失效的情况，经分析定位为：随着表的增长，where条件出来的数据太多，大于15%，使得索引失效,目前无法提前发现，只能通过上面的语句定位到执行缓慢的语句。

附录----执行计划详解：https://www.cnblogs.com/Dreamer-1/p/6076440.html

3）如果没有找到等待过长的sql语句，需要进一步排查应用服务器到oracle数据库主机的连接是否顺畅，有没有存在延迟!!

3.1）oracle的alert的日志文件超过4G，会导致连接报错

可以通过下列语句找到alert.log的目录

<pre class="md-fences md-end-block md-fences-with-lineno ty-contain-cm modeLoaded" spellcheck="false" lang="sql" cid="n27" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: var(--monospace); font-size: 0.9em; display: block; break-inside: avoid; text-align: left; white-space: normal; background-image: inherit; background-position: inherit; background-size: inherit; background-repeat: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; margin-bottom: 15px; margin-top: 15px; padding-top: 8px; padding-bottom: 6px; padding-left: 0px; width: inherit; color: rgb(51, 51, 51); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;"> select value from v$diag_info where name ='Diag Trace';</pre>

[图片上传失败...(image-28f6a4-1625715500143)]

找到该日志，然后备份删除

[图片上传失败...(image-d134d2-1625715500143)]

3.2）如果日志文件没有超过4G或者3.1步骤处理完仍然没有解决数据库连接超时或者慢的问题

需要执行：tnsping IP:端口/实例名称

<pre class="md-fences md-end-block md-fences-with-lineno ty-contain-cm modeLoaded" spellcheck="false" lang="" cid="n33" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: var(--monospace); font-size: 0.9em; display: block; break-inside: avoid; text-align: left; white-space: normal; background-image: inherit; background-position: inherit; background-size: inherit; background-repeat: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; margin-bottom: 15px; margin-top: 15px; padding-top: 8px; padding-bottom: 6px; padding-left: 0px; width: inherit; color: rgb(51, 51, 51); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;"> C:\Users\fangr>tnsping localhost:1521/orcl
TNS Ping Utility for 64-bit Windows: Version 11.2.0.1.0 - Production on 03-7月 -2021 17:08:45

Copyright (c) 1997, 2010, Oracle. All rights reserved.

已使用的参数文件:
D:\app1\fangr\product\11.2.0\dbhome_1\network\admin\sqlnet.ora

已使用 EZCONNECT 适配器来解析别名
尝试连接 (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=orcl))(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521)))
OK (0 毫秒)</pre>

执行结果如下图：如果超过1000毫秒，则连接可能存在问题！！需要协调ping一下是否丢包，或者协调客户定位下网络问题

oracle-tnsping-6.png

扩展：AWR分析报告进行数据库的分析

觉得数据库慢的时候也可以通过AWR分析报告，对oracle进行性能分析找到一些oracle可能存在的潜在问题

对数据库缓慢时间段范围内生成awr报告！！

AWR报告生成方法：https://www.cnblogs.com/liyasong/p/oracle_report1.html

AWR报告详细解读：https://blog.csdn.net/u011334954/article/details/103953103?utm_medium=distribute.pc_relevant.none-task-blog-baidujs_baidulandingword-0&spm=1001.2101.3001.4242

该报告重点关注如下内容：

1）正常情况下DBCPU应该排在首位，%DB time每个耗时不能超过70%，如果超过就要针对处理！

awr报告分析7-1.png

2）根据SQL ordered by Elapsed Time，找到耗时比较长的sql，针对sql进行分析

awr报告分析7-2.png

3）查看SGA和PGA的内存使用是否过高，正常情况下不得超过主机内存的80%

awr-分析报告3

2、如果页面能正常访问，添加记录或者更新网页卡住不动：

网页访问和列表加载正常，此刻很有可能存在oracle锁表的情况，通过下面的sql执行，找到被锁的表

<pre class="md-fences md-end-block md-fences-with-lineno ty-contain-cm modeLoaded" spellcheck="false" lang="sql" cid="n53" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: var(--monospace); font-size: 0.9em; display: block; break-inside: avoid; text-align: left; white-space: normal; background-image: inherit; background-position: inherit; background-size: inherit; background-repeat: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; margin-bottom: 15px; margin-top: 15px; padding-top: 8px; padding-bottom: 6px; padding-left: 0px; width: inherit; color: rgb(51, 51, 51); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;"> select sess.sid,
sess.serial#,
lo.oracle_username,
lo.os_user_name,
ao.object_name,
lo.locked_mode
from vsession sess
where ao.object_id = lo.object_id
and lo.session_id = sess.sid;</pre>

找到被锁的表

oracle锁表7-4.png

通过下面sql定位到锁表的sql语句

<pre class="md-fences md-end-block md-fences-with-lineno ty-contain-cm modeLoaded" spellcheck="false" lang="sql" cid="n57" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: var(--monospace); font-size: 0.9em; display: block; break-inside: avoid; text-align: left; white-space: normal; background-image: inherit; background-position: inherit; background-size: inherit; background-repeat: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; margin-bottom: 15px; margin-top: 15px; padding-top: 8px; padding-bottom: 6px; padding-left: 0px; width: inherit; color: rgb(51, 51, 51); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;"> SELECT A.USERNAME,
A.MACHINE,
A.PROGRAM,
A.SID,
A.SERIAL#,
A.STATUS,
C.PIECE,
C.SQL_TEXT
FROM VSQLTEXT C
WHERE A.SID IN (SELECT DISTINCT T2.SID
FROM VSESSION T2
WHERE T1.SESSION_ID = T2.SID)
AND A.SQL_ADDRESS = C.ADDRESS(+)
ORDER BY C.PIECE;</pre>

将找到的sql提交给研发，判断该sql语句是人为锁定的还是程序未提交导致的！

[图片上传失败...(image-800f18-1625715500143)]

如果需要，可以通过下面的sql进行解锁

<pre class="md-fences md-end-block md-fences-with-lineno ty-contain-cm modeLoaded" spellcheck="false" lang="sql" cid="n61" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: var(--monospace); font-size: 0.9em; display: block; break-inside: avoid; text-align: left; white-space: normal; background-image: inherit; background-position: inherit; background-size: inherit; background-repeat: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; margin-bottom: 15px; margin-top: 15px; padding-top: 8px; padding-bottom: 6px; padding-left: 0px; width: inherit; color: rgb(51, 51, 51); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;"> alter system kill session '68,51'; --分别为SID和SERIAL#号</pre>

3、如果排除oracle和sql的问题，那么进一步排查应用服务器的问题

1）调整程序连接的最大线程数和排队请求数

tomcat：将maxThreads="800" acceptCount="1000"调整为800和500代表允许最大线程数为800，可以同时处理800个请求，如果超 过800个线程，接受排队500个请求!

<pre class="md-fences md-end-block md-fences-with-lineno ty-contain-cm modeLoaded" spellcheck="false" lang="xml" cid="n67" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: var(--monospace); font-size: 0.9em; display: block; break-inside: avoid; text-align: left; white-space: normal; background-image: inherit; background-position: inherit; background-size: inherit; background-repeat: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; margin-bottom: 15px; margin-top: 15px; padding-top: 8px; padding-bottom: 6px; padding-left: 0px; width: inherit; color: rgb(51, 51, 51); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;"> <Connector port="8080" protocol="HTTP/1.1" connectionTimeout="20000" redirectPort="8443" maxThreads="800" acceptCount="500"/></pre>

weblogic：

<pre class="md-fences md-end-block md-fences-with-lineno ty-contain-cm modeLoaded" spellcheck="false" lang="" cid="n69" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: var(--monospace); font-size: 0.9em; display: block; break-inside: avoid; text-align: left; white-space: normal; background-image: inherit; background-position: inherit; background-size: inherit; background-repeat: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; margin-bottom: 15px; margin-top: 15px; padding-top: 8px; padding-bottom: 6px; padding-left: 0px; width: inherit; color: rgb(51, 51, 51); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;"> 进入WebLogic控制台——左边窗口的mydomain——servers——点击myserver——Configuration——General——点击Advanced Options的Show链接——最下面的Configure Execute Queues 链接——weblogic.kernel.Default链接——Thread Count参数。的却是隐藏的比较深。修改Thread Count的值，然后重启WebLogic就可以生效了。</pre>

2）根据服务器的内存大小，调整tomcat的内存，最大不超过应用服务器内存的60%

tomcat：

<pre class="md-fences mock-cm md-end-block md-fences-with-lineno" spellcheck="false" lang="shell" cid="n72" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: var(--monospace); font-size: 0.9em; display: block; break-inside: avoid; text-align: left; white-space: pre-wrap; background-image: inherit; background-position: inherit; background-size: inherit; background-repeat: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; margin-bottom: 15px; margin-top: 15px; padding-top: 8px; padding-bottom: 6px; padding-left: 8px; width: inherit; color: rgb(51, 51, 51); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">在“echo"UsingCATALINA_BASE: $CATALINA_BASE"”上面加入以下行：
JAVA_OPTS="-server -Xms800m -Xmx800m -XX:PermSize=256m -XX:MaxPermSize=512m -XX:MaxNewSize=512m"</pre>

<pre class="md-fences mock-cm md-end-block md-fences-with-lineno" spellcheck="false" lang="" cid="n73" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: var(--monospace); font-size: 0.9em; display: block; break-inside: avoid; text-align: left; white-space: pre-wrap; background-image: inherit; background-position: inherit; background-size: inherit; background-repeat: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; margin-bottom: 15px; margin-top: 15px; padding-top: 8px; padding-bottom: 6px; padding-left: 8px; width: inherit; color: rgb(51, 51, 51); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">weblogic：</pre>

<pre class="md-fences mock-cm md-end-block md-fences-with-lineno" spellcheck="false" lang="" cid="n74" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: var(--monospace); font-size: 0.9em; display: block; break-inside: avoid; text-align: left; white-space: pre-wrap; background-image: inherit; background-position: inherit; background-size: inherit; background-repeat: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; margin-bottom: 15px; margin-top: 15px; padding-top: 8px; padding-bottom: 6px; padding-left: 8px; width: inherit; color: rgb(51, 51, 51); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">直接修改user_projects\domains\base_domain\bin下的startWebLogic.cmd文件

在它下面添加一下代码

set JAVA_OPTIONS=-Xms256m –Xmx256m；</pre>

3）根据tomcat的catalina.log的具体报错反馈给开发进行查询，需要根据的实际报错

4）服务器重启大法