概述

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方面内容，感興趣的朋友可以關注下！

,

更多精彩资讯请关注tft每日頭條，我们将持续为您更新最新资讯!

查看全部