概述
This project meant to provide useful scripts for DB maintance and management, to make work easier and interesting...
今天主要分享一个shell脚本,主要是为了统计最消耗CPU资源的SQL语句等..
一、环境准备
1、配置tnsnames.ora
保证别名和ORACLE_SID一致,后面脚本需要
# vim /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora===================================================================MDMDB = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST =xx.xx.65)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = MDMDB) ) )===================================================================
2、测试连接
二、初始化脚本settdb.sh
use script settdb.sh for DB login details registry
输出:
三、turning.sh
统计最近10分钟,最消耗CPU资源的SQL语句、最近30分钟,最消耗IO资源的会话、根据io消耗前十sql的会话id,查出操作系统号并组合杀进程语句
#!/bin/bashecho "========================================查询最近10分钟,最消耗CPU资源的SQL语句================================================="sqlplus -S $DB_CONN_STR@$SH_DB_SID < sysdate - 10 / (24 * 60) and session_type <> 'BACKGROUND' and session_state = 'ON CPU' group by ash.sql_id order by count(*) desc) where rownum <= 20;exitEOFecho "========================================查询最近30分钟,最消耗IO资源的会话================================================="sqlplus -S $DB_CONN_STR@$SH_DB_SID < sysdate - 30 / (24 * 60) and session_type <> 'BACKGROUND' and session_state = 'WAITING' and wait_class = 'User I/O' group by ash.session_id order by count(*) desc) where rownum <= 20;exitEOFecho "========================================根据io消耗前十sql的会话id,查出操作系统号并组合杀进程语句================================================="sqlplus -S $DB_CONN_STR@$SH_DB_SID < sysdate - 5 / (24 * 60) and session_type <> 'BACKGROUND' and session_state = 'WAITING' and wait_class = 'User I/O' group by ash.session_id, ash.session_serial# order by count(*) desc) where rownum <= 10;exitEOF
输出结果:
后面会分享更多devops和dba方面内容,感兴趣的朋友可以关注下!