当前位置：网站首页>Flexible and easy-to-use sql monitoring script part2
Flexible and easy-to-use sql monitoring script part2
2022-08-05 14:32:00 【Dreamer DBA】
1.Script:sql_area.sql
-- Description : Displays the SQL statements for currently running processes.
[[email protected] monitoring]$ cat sql_area.sql
-- -----------------------------------------------------------------------------------
-- File Name : sql_area.sql
-- Author : Maxwell
-- Description : Displays the SQL statements for currently running processes.
-- Requirements : Access to the V$ views.
-- Call Syntax : @sql_area
-- Last Modified: 05/08/2022
-- -----------------------------------------------------------------------------------
SET LINESIZE 500
SET PAGESIZE 1000
SET FEEDBACK OFF
SELECT s.sid,
s.status "Status",
p.spid "Process",
s.schemaname "Schema Name",
s.osuser "OS User",
Substr(a.sql_text,1,120) "SQL Text",
s.program "Program"
FROM v$session s,
v$sqlarea a,
v$process p
WHERE s.sql_hash_value = a.hash_value(+)
AND s.sql_address = a.address(+)
AND s.paddr = p.addr;
SET PAGESIZE 14
SET FEEDBACK ON
[[email protected] monitoring]$SQL> @/home/oracle/oracledba/monitoring/sql_area.sql
SID Status Process Schema Name OS User
---------- -------- ------------------------ -------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------
SQL Text
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Program
------------------------------------------------
1 ACTIVE 108602 SYS oracle
[email protected] (PMON)
238 ACTIVE 108604 SYS
SQL> 2.Script:top_sql.sql
-- Description : Displays a list of SQL statements that are using the most resources.
[[email protected] monitoring]$ cat top_sql.sql
-- -----------------------------------------------------------------------------------
-- File Name : top_sql.sql
-- Author : Maxwell
-- Description : Displays a list of SQL statements that are using the most resources.
-- Comments : The address column can be use as a parameter with SQL_Text.sql to display the full statement.
-- Requirements : Access to the V$ views.
-- Call Syntax : @top_sql (number)
-- Last Modified: 05/08/2022
-- -----------------------------------------------------------------------------------
SET LINESIZE 500
SET PAGESIZE 1000
SET VERIFY OFF
SELECT *
FROM (SELECT Substr(a.sql_text,1,50) sql_text,
Trunc(a.disk_reads/Decode(a.executions,0,1,a.executions)) reads_execution,
a.buffer_gets,
a.disk_reads,
a.executions,
a.sorts,
a.address
FROM v$sqlarea a
ORDER BY 2 DESC)
WHERE rownum <= &&1;
SET PAGESIZE 14
[[email protected] monitoring]$ 3.Script: sql_text.sql
-- Description : Displays the SQL statement held at the specified address.
[[email protected] monitoring]$ cat sql_text.sql
-- -----------------------------------------------------------------------------------
-- File Name : sql_text.sql
-- Author : Maxwell
-- Description : Displays the SQL statement held at the specified address.
-- Comments : The address can be found using v$session or Top_SQL.sql.
-- Requirements : Access to the V$ views.
-- Call Syntax : @sql_text (address)
-- Last Modified: 05/08/2022
-- -----------------------------------------------------------------------------------
SET LINESIZE 500
SET PAGESIZE 1000
SET FEEDBACK OFF
SET VERIFY OFF
SELECT a.sql_text
FROM v$sql_text_with_newlines a
WHERE a.address = UPPER('&&1')
ORDER BY a.piece;
PROMPT
SET PAGESIZE 14
SET FEEDBACK ON
[[email protected] monitoring]$ 4.Script: sql_text_by_sid.sql
-- Description : Displays the SQL statement held at the specified address.
[[email protected] monitoring]$ cat sql_text_by_sid.sql
-- -----------------------------------------------------------------------------------
-- File Name : sql_text_by_sid.sql
-- Author : Maxwell
-- Description : Displays the SQL statement held for a specific SID.
-- Comments : The SID can be found by running session.sql or top_session.sql.
-- Requirements : Access to the V$ views.
-- Call Syntax : @sql_text_by_sid (sid)
-- Last Modified: 05/08/2022
-- -----------------------------------------------------------------------------------
SET LINESIZE 500
SET PAGESIZE 1000
SET VERIFY OFF
SELECT a.sql_text
FROM v$sqltext a,
v$session b
WHERE a.address = b.sql_address
AND a.hash_value = b.sql_hash_value
AND b.sid = &1
ORDER BY a.piece;
PROMPT
SET PAGESIZE 14
[[email protected] monitoring]$ 5.Script: cache_hit_ratio.sql
-- Description : Displays cache hit ratio for the database.
[[email protected] monitoring]$ cat cache_hit_ratio.sql
-- File Name : cache_hit_ratio.sql
-- Author : Maxwell
-- Description : Displays cache hit ratio for the database.
-- Comments : The minimum figure of 89% is often quoted, but depending on the type of system this may not be possible.
-- Requirements : Access to the v$ views.
-- Call Syntax : @cache_hit_ratio
-- Last Modified: 05/08/2022
-- -----------------------------------------------------------------------------------
PROMPT
PROMPT Hit ratio should exceed 89%
SELECT Sum(Decode(a.name, 'consistent gets', a.value, 0)) "Consistent Gets",
Sum(Decode(a.name, 'db block gets', a.value, 0)) "DB Block Gets",
Sum(Decode(a.name, 'physical reads', a.value, 0)) "Physical Reads",
Round(((Sum(Decode(a.name, 'consistent gets', a.value, 0)) +
Sum(Decode(a.name, 'db block gets', a.value, 0)) -
Sum(Decode(a.name, 'physical reads', a.value, 0)) )/
(Sum(Decode(a.name, 'consistent gets', a.value, 0)) +
Sum(Decode(a.name, 'db block gets', a.value, 0))))
*100,2) "Hit Ratio %"
FROM v$sysstat a;
[[email protected] monitoring]$ SQL> @/home/oracle/oracledba/monitoring/cache_hit_ratio.sql
Hit ratio should exceed 89%
Consistent Gets DB Block Gets Physical Reads Hit Ratio %
--------------- ------------- -------------- -----------
383152247 45839249 2495285 99.42
1 row selected.
SQL> 6.Script: nls_params.sql
-- Description : Displays National Language Suppport (NLS) information.
[[email protected] monitoring]$ cat nls_params.sql
-- -----------------------------------------------------------------------------------
-- File Name : /monitoring/nls_params.sql
-- Author : Maxwell
-- Description : Displays National Language Suppport (NLS) information.
-- Requirements :
-- Call Syntax : @nls_params
-- Last Modified: 05-AUG-2022
-- -----------------------------------------------------------------------------------
SET LINESIZE 100
COLUMN parameter FORMAT A45
COLUMN value FORMAT A45
PROMPT *** Database parameters ***
SELECT * FROM nls_database_parameters ORDER BY 1;
PROMPT *** Instance parameters ***
SELECT * FROM nls_instance_parameters ORDER BY 1;
PROMPT *** Session parameters ***
SELECT * FROM nls_session_parameters ORDER BY 1;
[[email protected] monitoring]$ SQL> @/home/oracle/oracledba/monitoring/nls_params.sql
*** Database parameters ***
PARAMETER VALUE
--------------------------------------------- ---------------------------------------------
NLS_CALENDAR GREGORIAN
NLS_CHARACTERSET AL32UTF8
NLS_COMP BINARY
NLS_CURRENCY $
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_DUAL_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_LANGUAGE AMERICAN
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CHARACTERSET AL16UTF16
PARAMETER VALUE
--------------------------------------------- ---------------------------------------------
NLS_NCHAR_CONV_EXCP FALSE
NLS_NUMERIC_CHARACTERS .,
NLS_RDBMS_VERSION 19.0.0.0.0
NLS_SORT BINARY
NLS_TERRITORY AMERICA
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
20 rows selected.
*** Instance parameters ***
PARAMETER VALUE
--------------------------------------------- ---------------------------------------------
NLS_CALENDAR
NLS_COMP BINARY
NLS_CURRENCY
NLS_DATE_FORMAT
NLS_DATE_LANGUAGE
NLS_DUAL_CURRENCY
NLS_ISO_CURRENCY
NLS_LANGUAGE AMERICAN
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
NLS_NUMERIC_CHARACTERS
PARAMETER VALUE
--------------------------------------------- ---------------------------------------------
NLS_SORT
NLS_TERRITORY AMERICA
NLS_TIMESTAMP_FORMAT
NLS_TIMESTAMP_TZ_FORMAT
NLS_TIME_FORMAT
NLS_TIME_TZ_FORMAT
17 rows selected.
*** Session parameters ***
PARAMETER VALUE
--------------------------------------------- ---------------------------------------------
NLS_CALENDAR GREGORIAN
NLS_COMP BINARY
NLS_CURRENCY $
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_DUAL_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_LANGUAGE AMERICAN
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
NLS_NUMERIC_CHARACTERS .,
PARAMETER VALUE
--------------------------------------------- ---------------------------------------------
NLS_SORT BINARY
NLS_TERRITORY AMERICA
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
17 rows selected.
SQL> 7.Script: call_stack.sql
-- Description : Displays the current call stack.
[[email protected] monitoring]$ cat call_stack.sql
-- -----------------------------------------------------------------------------------
-- File Name : /monitoring/call_stack.sql
-- Author : Maxwell
-- Description : Displays the current call stack.
-- Requirements : Access to DBMS_UTILITY.
-- Call Syntax : @call_stack
-- Last Modified: 05-AUG-2022
-- -----------------------------------------------------------------------------------
SET SERVEROUTPUT ON
DECLARE
v_stack VARCHAR2(2000);
BEGIN
v_stack := Dbms_Utility.Format_Call_Stack;
Dbms_Output.Put_Line(v_stack);
END;
/
[[email protected] monitoring]$ SQL> @/home/oracle/oracledba/monitoring/call_stack.sql
----- PL/SQL Call Stack -----
object line object
handle number name
0x7d5abdf8
4 anonymous block
PL/SQL procedure successfully completed.
SQL> 8.Script: non_indexed_fks.sql
-- Description : Displays a list of non-indexes FKs.
[[email protected] monitoring]$ cat non_indexed_fks.sql
-- -----------------------------------------------------------------------------------
-- File Name : /monitoring/non_indexed_fks.sql
-- Author : Maxwell
-- Description : Displays a list of non-indexes FKs.
-- Requirements : Access to the ALL views.
-- Call Syntax : @non_indexed_fks
-- Last Modified: 15/07/2000
-- -----------------------------------------------------------------------------------
SET SERVEROUTPUT ON
SET PAGESIZE 1000
SET LINESIZE 255
SET FEEDBACK OFF
SELECT t.table_name,
c.constraint_name,
c.table_name table2,
acc.column_name
FROM all_constraints t,
all_constraints c,
all_cons_columns acc
WHERE c.r_constraint_name = t.constraint_name
AND c.table_name = acc.table_name
AND c.constraint_name = acc.constraint_name
AND NOT EXISTS (SELECT '1'
FROM all_ind_columns aid
WHERE aid.table_name = acc.table_name
AND aid.column_name = acc.column_name)
ORDER BY c.table_name;
PROMPT
SET FEEDBACK ON
SET PAGESIZE 18
[[email protected] monitoring]$ 边栏推荐
- day14·私有化属性
- C# employee attendance management system source code attendance salary management system source code
- Redis - Talking about master-slave synchronization
- 20款短视频自媒体必备工具，让你的运营效率翻倍
- day6·动态导入模块
- 14, the regular expression - section after class exercises and answers
- npm install时卡在sill idealTree buildDeps
- ソイラ / 索伊拉
- Deficiency needs attention
- 理财产品是只能看到本金看不到收益吗？
猜你喜欢
恶访、黑产猖獗，做安全“守门人”| 创新场景50
背后的力量 | 开启智能化教学新体验 华云数据助力天长市工业学校打造新型IT实训室
内存问题难定位，那是因为你没用ASAN
shell实现加密压缩文件自动解压
基于STM32单片机设计指纹考勤机+上位机管理
为什么你做自媒体赚不到钱？今天就教你几招
【虚拟机数据恢复】Hyper-V虚拟化文件丢失，虚拟化服务器不可用的数据恢复案例
01.Gameplay Architecture ECS简介
LeetCode高频题69. x 的平方根，二分法搞定，非常简单
The power behind | Open up a new experience of intelligent teaching Huayun Data helps Tianchang Industrial School to build a new IT training room
随机推荐
Shell realizes automatic decompression of encrypted compressed files
二维码期货开户安全可靠吗？期货开户免费是真的吗？
An in-depth long article discusses the simplification and speedup of JOIN operations
一篇笔记爆不爆，话题占了爆文的绝大部分，这篇文章教你
Deficiency needs attention
Oracle数据迁移实用入门
NLP paper reading | Parametric machine translation meets comparative learning: I want both efficiency and performance!
深度卷积神经网络是什么,卷积神经网络结构设计
重视客户争取最大期货开户优惠
OpenHarmony像素单位(eTS)
双因子与多因子身份验证有什么区别？
day11· 对象方法、静态方法、类方法
OneNote 教程，如何在 OneNote 中插入表格并设置其格式？
cookie、session、tooken
期货开户公司的选择和作用
day9·类和对象
DevEco Studio Configuration: Custom Header Code Comments
Unity camera walkthrough script
day13·魔术方法__ call__与__del__
使用 Redis 源码编译发布 Windows 版 Redis For Windows 发行包