Oracle诊断性能问题时常用脚本工具.pdf

Oracle 性能诊断工具 2021年04月 YUNHE ENMO (BEIJING) TECHNOLOGY CO.,LTD 云和恩墨 成就所托 Agenda • 监听日志分析 • DB性能问题 • 进行时 • 过去时 • 系统级/SQL级 T0 T 1 T2 • 数据库巡检 • trace format工具 4 Listener • Slow • Network (ping\traceroute) • Logfile location filesystem busy (iostat\sar -d) • Listener busy (top\ps) • Logon storm • QUEUESIZE(netstat -ano) • Rate Limit • Multiple LIST ENERS • Connect pool • Analyze listener log • Shell (see /archives/2499.html ) • ADRCI • ELK 5 Listener demo Logon storm 6 DB • AWR、AWRDD • AWR Snapshots and Reports from Oracle Multitentant Database(CDB, PDB) (Doc ID 2295998.1) • ASH\ DASH • V$session No-idle (ase.sql) • Tanel Poder’s Sql Tools (snapper \ashtop \ash_wait_chains\ latchprof … ) • $ORACLE_ PAT H $SQLPAT H • LOGS • Oratop • OS (Top[as] \ ps) 7 DB demo 8 DB demo 9 Session 1, TOP Event/ TOP SQL (v$session[_wait]\v$session_event\ ASH) 2, V$SESSTAT Statistics 3, Dump trace (sql trace\processtate\short_stack) 10 Session 11 SQL • Execution plan • Explain PLAN • DBMS_XPLAN @xawr @xbi @xi • Sq