灵活好用的sql monitoring 脚本 part2

最新推荐文章于 2024-08-09 21:25:57 发布

DB架构

最新推荐文章于 2024-08-09 21:25:57 发布

阅读量111

点赞数

分类专栏： DBA 文章标签：数据库 oracle dba

本文链接：https://blog.csdn.net/u011868279/article/details/126169890

版权

DBA 专栏收录该内容

183 篇文章 12 订阅

订阅专栏

这些Oracle数据库脚本用于监控和分析SQL语句、资源使用、缓存命中率、NLS设置以及调用堆栈。它们包括显示当前运行进程的SQL、资源消耗最大的SQL、指定地址的SQL文本、特定SID的SQL文本以及数据库的缓存命中率。此外，还展示了非索引外键列表和NLS参数详细信息。

摘要由CSDN通过智能技术生成

1.Script:sql_area.sql

-- Description : Displays the SQL statements for currently running processes.

[oracle@MaxwellDBA 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
[oracle@MaxwellDBA monitoring]$

SQL> @/home/oracle/oracledba/monitoring/sql_area.sql

       SID Status   Process                  Schema Name                                                                                                                 OS User
---------- -------- ------------------------ -------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------
SQL Text
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Program
------------------------------------------------
         1 ACTIVE   108602                   SYS                                                                                                                         oracle

oracle@MaxwellDBA (PMON)

       238 ACTIVE   108604                   SYS                                                                                                                         

SQL>

2.Script:top_sql.sql

-- Description : Displays a list of SQL statements that are using the most resources.

[oracle@MaxwellDBA 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
[oracle@MaxwellDBA monitoring]$

3.Script: sql_text.sql

-- Description : Displays the SQL statement held at the specified address.

[oracle@MaxwellDBA 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
[oracle@MaxwellDBA monitoring]$

4.Script: sql_text_by_sid.sql

-- Description : Displays the SQL statement held at the specified address.

[oracle@MaxwellDBA 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
[oracle@MaxwellDBA monitoring]$

5.Script: cache_hit_ratio.sql

-- Description : Displays cache hit ratio for the database.

[oracle@MaxwellDBA 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;
[oracle@MaxwellDBA 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.

[oracle@MaxwellDBA 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;
[oracle@MaxwellDBA 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.

[oracle@MaxwellDBA 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;
/
[oracle@MaxwellDBA 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.

[oracle@MaxwellDBA 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
[oracle@MaxwellDBA monitoring]$