概述

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/bash
echo "========================================查询最近10分钟，最消耗CPU资源的SQL语句================================================="
sqlplus -S $DB_CONN_STR@$SH_DB_SID <<EOF 

set linesize 1000 pages 500
prompt CPU in 10m
set line 234
col sql_text for a70
select sql_id, cnt, pctload, substr(sql_text, 1, 70) sql_text
 from (select ash.sql_id,
 count(*) cnt,
 max(s.sql_text) sql_text,
 max(s.parsing_schema_name) parsing_schema_name,
 round(count(*) / sum(count(*)) over(), 2) pctload
 from v\$active_session_history ash, v\$sqlarea s
 where ash.sql_id = s.sql_id
 and sample_time > 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;
exit
EOF

echo "========================================查询最近30分钟，最消耗IO资源的会话================================================="
sqlplus -S $DB_CONN_STR@$SH_DB_SID <<EOF 
prompt IO in 30m
set line 234
col sql_text for a70
select session_id, cnt, substr(sql_text, 1, 70) sql_text
 from (select ash.session_id,
 count(*) cnt,
 max(s.sql_text) sql_text,
 max(s.parsing_schema_name) parsing_schema_name,
 round(count(*) / sum(count(*)) over(), 2) pctload
 from v\$active_session_history ash, v\$sqlarea s
 where ash.sql_id = s.sql_id(+)
 and sample_time > 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;
exit
EOF

echo "========================================根据io消耗前十sql的会话id，查出操作系统号并组合杀进程语句================================================="
sqlplus -S $DB_CONN_STR@$SH_DB_SID <<EOF 
prompt TOPSQL by IO
set line 234
col sql_text for a70
select session_id, session_serial#, cnt, substr(sql_text, 1, 70) sql_text
 from (select ash.session_id,
 ash.session_serial#,
 count(*) cnt,
 max(s.sql_text) sql_text,
 max(s.parsing_schema_name) parsing_schema_name,
 round(count(*) / sum(count(*)) over(), 2) pctload
 from v\$active_session_history ash, v\$sqlarea s
 where ash.sql_id = s.sql_id(+)
 and sample_time > 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;
exit
EOF

输出结果：

后面会分享更多devops和dba方面内容，感兴趣的朋友可以关注下！