[译] 与Oracle数据库19c和多租户一起使用Statspack

原创王语嫣 2022-06-30

387

原文地址：Statspack with Oracle Database 19c and Multitenant
原文作者：Jérôme Dubar

介绍

谈到 Oracle 数据库的性能分析，每个人都会想到 Diagnostic Pack。这是一个非常完整的工具，它带来的指标比 DBA 可以分析的要多得多。但是这个工具有一个很大的缺点：它不包含在标准版中，它是企业版的一个选项。如果您没有此工具，仍然可以使用旧的 Statspack 工具进行性能分析。Statspack 就是诊断包之父，它仍然免费提供所有数据库版本。您只需要正确设置它，即使您使用的是现代环境（我的意思是 19c 和多租户），它也有助于诊断性能问题。当然，与诊断包相比存在一些限制，例如纯文本报告、较少的指标和截断的 SQL 语句。

Statspack 能否与 19c 和多租户一起正常工作？

可以的，您只需要使用特定的设置过程。如果您使用多租户，则应在 PDB 级别部署 Statspack。并且作业必须使用 dbms_scheduler 进行配置，因为 19c 不再支持老式的 dbms_job。这是我在现代环境中部署 Statspack 时使用的过程。

设置程序

在配置 Statspack 之前，请确保您使用的是 Standard Edition，或者确保您没有在 Enterprise Edition 数据库上使用 Diagnostic Pack。这两种工具不应该一起工作，两者都绝对没有用。诊断包和最终调整包通过实例参数启用/禁用。

-- Check if DIAGNOSTIC and TUNING pack are disabled at the CDB level
SQL> conn / as sysdba
SQL> sho parameter control_mana

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
control_management_pack_access	     string	 NONE

-- Remove DIAGNOSTIC and TUNING packs if needed
alter system set control_management_pack_access='NONE' scope=spfile;
复制

从现在开始，如果您使用多租户，则在 PDB 上执行命令：

export ORACLE_PDB_SID=MYPDB;
sqlplus / as sysdba
复制

Statspack 应该使用自己的表空间，2/3GB 通常应该没问题。

-- Create a dedicated tablespace if needed
create tablespace PERFSTAT datafile size 300M autoextend on maxsize 3G;
复制

如果之前部署了 Statspack，您可以将其删除以进行全新安装：

-- Remove previously installed statspack if needed
@?/rdbms/admin/spdrop
复制

Statspack 设置将需要一个默认表空间（您刚刚创建的那个）、一个临时表空间和它的用户 PERFSTAT 的密码（提示）：

-- Create statspack's user and objects
define default_tablespace='perfstat'
define temporary_tablespace='temp'
@?/rdbms/admin/spcreate
复制

您应该配置增加的指标级别：

-- Increase level of metrics
exec STATSPACK.MODIFY_STATSPACK_PARAMETER (i_snap_level=>7,i_modify_parameter=>'true', i_instance_number=>null);
复制

Statspack 将需要 2 个作业，1 个用于快照（快照将实际性能指标刷新到专用表中），1 个用于快照清除（无需保留旧的性能指标）。

让我们允许用户 PERFSTAT 创建作业：

-- Allow PERFSTAT's user to create jobs
conn / as sysdba
grant create job to perfstat;
conn perfstat/perfstat
show user
复制

如果需要，让我们停止并删除现有的快照作业：

-- Drop existing snapshot job if needed
Exec dbms_scheduler.stop_job('SP_SNAP_JOB');
Exec dbms_scheduler.drop_job('SP_SNAP_JOB');
Exec dbms_scheduler.drop_schedule('SP_SNAP_SCHED');
Exec dbms_scheduler.drop_program('SP_SNAP_PROG');
复制

现在让我们创建快照调度组件。在此示例中，我的选择是每 15 分钟拍摄一张快照。我不会更频繁地推荐，并且不太频繁地喜欢每小时也可以。

-- Create snapshot job using scheduler - every fifteen minutes
exec dbms_scheduler.create_program(program_name => 'SP_SNAP_PROG', program_type => 'STORED_PROCEDURE', program_action => 'PERFSTAT.statspack.snap', number_of_arguments => 0, enabled => FALSE);
exec dbms_scheduler.enable(name => 'SP_SNAP_PROG');
exec dbms_scheduler.create_schedule (schedule_name => 'SP_SNAP_SCHED', repeat_interval => 'freq=hourly; byminute=0,15,30,45; bysecond=0',end_date => null, comments => 'Schedule for Statspack snaps');
exec dbms_scheduler.create_job (job_name => 'SP_SNAP_JOB', program_name => 'SP_SNAP_PROG', schedule_name => 'SP_SNAP_SCHED',  enabled => TRUE, auto_drop => FALSE, comments => 'Statspack Job for snaps');
复制

让我们检查一下这个作业的调度：

-- Check job and scheduling
set lines 140
col owner for a10
col job_name for a15
col program_name for a20
col first for a18
col next for a18
col last for a18
select OWNER, JOB_NAME, PROGRAM_NAME, to_char(START_DATE,'YYYY/MM/DD HH24:MI') "FIRST", to_char(NEXT_RUN_DATE,'YYYY/MM/DD HH24:MI') "NEXT", to_char(LAST_START_DATE,'YYYY/MM/DD HH24:MI') "LAST" from dba_scheduler_jobs where owner='PERFSTAT';


OWNER	   JOB_NAME	   PROGRAM_NAME 	FIRST		 NEXT		  LAST
---------- --------------- -------------------- ---------------- ---------------- ----------------
PERFSTAT   SP_SNAP_JOB	   SP_SNAP_PROG 	2021/12/24 14:45 2021/12/24 14:45
复制

如果需要，让我们放弃清除工作：

-- Drop existing purge job if needed
Exec dbms_scheduler.stop_job('SP_PURGE_JOB');
Exec dbms_scheduler.drop_job('SP_PURGE_JOB');
Exec dbms_scheduler.drop_schedule('SP_PURGE_SCHED');
Exec dbms_scheduler.drop_program('SP_PURGE_PROG');
复制

创建清除作业计划。清洗每周进行一次，在周六和周日的晚上进行。在此示例中，我为快照使用了 30 天的保留期。我建议保留 10 天到 2 个月。即使您不需要长期保留，在性能问题出现前几天/几周比较性能指标总是很不错的。

-- Create a procedure for the purge
create or replace procedure extended_purge(
num_days IN number
)
is
BEGIN
  statspack.purge(i_num_days => num_days, i_extended_purge => TRUE);
END extended_purge;
/

-- Test this procedure if needed
-- exec extended_purge(30);


-- Create snapshot job using scheduler - every Sunday at 0:20AM - keep 30 days of snapshots
exec dbms_scheduler.create_program(program_name => 'SP_PURGE_PROG', program_type => 'STORED_PROCEDURE', program_action => 'PERFSTAT.extended_purge', number_of_arguments => 1, enabled => FALSE);
exec DBMS_SCHEDULER.define_program_argument (program_name => 'SP_PURGE_PROG', argument_name => 'i_num_days', argument_position => 1, argument_type => 'NUMBER', default_value => 30);
exec dbms_scheduler.enable(name => 'SP_PURGE_PROG');
exec dbms_scheduler.create_schedule (schedule_name => 'SP_PURGE_SCHED', repeat_interval =>  'freq=weekly; byday=SUN; byhour=0; byminute=20',end_date => null, comments => 'Schedule for Statspack purge');
exec dbms_scheduler.create_job (job_name => 'SP_PURGE_JOB', program_name => 'SP_PURGE_PROG', schedule_name => 'SP_PURGE_SCHED',  enabled => TRUE, auto_drop => FALSE, comments => 'Statspack Job for purge');
复制

如果您使用的是 12cR1 等旧版本，则可能缺少某些事件，您应该添加它们（与 19c 无关）：

-- Insert missing idle events on 12cR1 only: have a look at this blog post
delete from STATS$IDLE_EVENT;
insert into STATS$IDLE_EVENT select name from V$EVENT_NAME where wait_class='Idle';
insert into STATS$IDLE_EVENT values('log file parallel write');
insert into STATS$IDLE_EVENT values('target log write size');
commit;
复制

现在是时候锁定 PERFSTAT 用户了，使用 Statspack 报告功能通常与 SYS 连接完成：

-- Lock the perfstat user
conn / as sysdba
alter user perfstat account lock;
复制

现在使用 SYS 让我们检查两个作业的调度：

-- Check jobs's scheduling
set lines 140
col owner for a10
col job_name for a15
col first for a18
col next for a18
col last for a18
col program_name for a20
select OWNER, JOB_NAME, PROGRAM_NAME, to_char(START_DATE,'YYYY/MM/DD HH24:MI') "FIRST", to_char(NEXT_RUN_DATE,'YYYY/MM/DD HH24:MI') "NEXT", to_char(LAST_START_DATE,'YYYY/MM/DD HH24:MI') "LAST"  from dba_scheduler_jobs where owner='PERFSTAT';

OWNER	   JOB_NAME	   PROGRAM_NAME 	FIRST		 NEXT		  LAST
---------- --------------- -------------------- ---------------- ---------------- ----------------
PERFSTAT   SP_SNAP_JOB	   SP_SNAP_PROG 	2021/12/24 14:45 2021/12/24 14:45
PERFSTAT   SP_PURGE_JOB    SP_PURGE_PROG	2021/12/25 00:20 2021/12/25 00:20
复制

稍后让我们再次检查这些性能指标的运行和失败计数、实际保留时间（可用快照的天数）和数据量：

-- Check later after several occurences
set lines 140
col owner for a10
col job_name for a15
col first for a18
col next for a18
col last for a18
col program_name for a20
select OWNER, JOB_NAME, PROGRAM_NAME, to_char(START_DATE,'YYYY/MM/DD HH24:MI') "FIRST", to_char(NEXT_RUN_DATE,'YYYY/MM/DD HH24:MI') "NEXT", to_char(LAST_START_DATE,'YYYY/MM/DD HH24:MI') "LAST", run_count, failure_count  from dba_scheduler_jobs where owner='PERFSTAT';

OWNER	   JOB_NAME	   PROGRAM_NAME 	FIRST		   NEXT 	      LAST		  RUN_COUNT FAILURE_COUNT
---------- --------------- -------------------- ------------------ ------------------ ------------------ ---------- -------------
PERFSTAT   SP_SNAP_JOB	   SP_SNAP_PROG 	2021/12/24 14:45   2022/01/02 17:15   2022/01/02 17:00		874		0
PERFSTAT   SP_PURGE_JOB    SP_PURGE_PROG	2022/01/02 00:20   2022/01/09 00:20   2022/01/02 00:20		  1		0


-- Check actual retention
SQL> select round(sysdate-min(snap_time)) "RETENTION" from stats$snapshot;

 RETENTION
----------
	 7

-- Check data volume
SQL> select round(sum(bytes)/1024/1024,1) "PERFSTAT MB" from dba_segments where owner = 'PERFSTAT';

PERFSTAT MB
-----------
      742.8



...


OWNER	   JOB_NAME	   PROGRAM_NAME 	FIRST		   NEXT 	      LAST		  RUN_COUNT FAILURE_COUNT
---------- --------------- -------------------- ------------------ ------------------ ------------------ ---------- -------------
PERFSTAT   SP_SNAP_JOB	   SP_SNAP_PROG 	2021/12/24 14:45   2022/01/04 19:15   2022/01/04 19:00	       1074		0
PERFSTAT   SP_PURGE_JOB    SP_PURGE_PROG	2022/01/02 00:20   2022/01/09 00:20   2022/01/02 00:20		  1		0


 RETENTION
----------
	 9


PERFSTAT MB
-----------
      940.8

...

OWNER	   JOB_NAME        PROGRAM_NAME         FIRST              NEXT               LAST                RUN_COUNT FAILURE_COUNT
---------- --------------- -------------------- ------------------ ------------------ ------------------ ---------- -------------
PERFSTAT   SP_SNAP_JOB     SP_SNAP_PROG         2021/12/24 14:45   2022/01/17 14:30   2022/01/17 14:15         2303             0
PERFSTAT   SP_PURGE_JOB    SP_PURGE_PROG        2022/01/02 00:20   2022/01/23 00:20   2022/01/16 00:20            3             0


 RETENTION
----------
	12


PERFSTAT MB
-----------
     1751.8

复制

预期数据量

Statspack 指标可以在每个数据库上使用数 GB，具体取决于快照的频率和保留时间。根据您的设置，您可以期待以下内容，每个快照大约为 1MB：

1 个月 – 1 个快照/小时：±750MB
1 个月 – 2 次快照/小时：±1500MB
1 个月 – 4 张快照/小时：±3000MB

oracle oracle数据库墨力翻译

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

介绍
Statspack 能否与 19c 和多租户一起正常工作？
设置程序
预期数据量

[译] 与Oracle数据库19c和多租户一起使用Statspack

介绍

Statspack 能否与 19c 和多租户一起正常工作？

设置程序

预期数据量

评论