Stap by Stap - 0003
Autor: Igor Fortunato
-- AWR --
---------
-- Quantidade de SNAP_ID e Tempo
SELECT MIN(SAMPLE_TIME),MAX(SAMPLE_TIME),count(snap_id)
FROM DBA_HIST_ACTIVE_SESS_HISTORY;
MIN(SAMPLE_TIME) MAX(SAMPLE_TIME) COUNT(SNAP_ID)
--------------------------- --------------------------- --------------
27/06/24 01:14:58,577000000 29/06/24 21:36:24,596000000 126
-- Informações de configuração dos SNAPSHOTS AWR
set lines 200
col SRC_DBNAME for a10
select *
from DBA_HIST_WR_CONTROL;
DBID SNAP_INTERVAL RETENTION TOPNSQL CON_ID SRC_DBID SRC_DBNAME
---------- ------------------- ------------------- ---------- ---------- ---------- ----------
280030230 +00 01:00:00.000000 +08 00:00:00.000000 DEFAULT 3 280030230 ORCLPDB
1684414743 +00 01:00:00.000000 +08 00:00:00.000000 DEFAULT 0 1684414743 CDB$ROOT
-- Informações BASELINE
COL baseline_name FOR A22
SELECT dbid, baseline_name, baseline_type, moving_window_size
FROM dba_hist_baseline
WHERE baseline_name = 'SYSTEM_MOVING_WINDOW';
DBID BASELINE_NAME BASELINE_TYPE MOVING_WINDOW_SIZE
---------- ---------------------- ------------- ------------------
1684414743 SYSTEM_MOVING_WINDOW MOVING_WINDOW 8
-- Alterar Intervalo pelo DBID
ALTER SESSION SET CONTAINER=CDB$ROOT
EXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(interval => 60, dbid=> 1684414743) ;
ALTER SESSION SET CONTAINER=CORCLPDB
EXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(interval => 60, dbid=> 280030230) ;
-- Consultar SNAP_ID com DBA_HIST_SNAPSHOT
SELECT instance_number,snap_id,startup_time
FROM dba_hist_snapshot
WHERE DBID = 280030230
--AND begin_interval_time >= TO_DATE('27/06/24 01:14:43','DD/MM/YY HH24:MI:SS')
--AND end_interval_time <= TO_DATE('27/06/24 01:14:43','DD/MM/YY HH24:MI:SS')
ORDER by SNAP_ID;
INSTANCE_NUMBER SNAP_ID STARTUP_TIME
--------------- ---------- ---------------------------
1 2 27/06/24 01:14:43,000000000
-- Consultar SNAP_ID com AWR_PDB_SNAPSHOT
SELECT instance_number,snap_id,startup_time
FROM awr_pdb_snapshot
WHERE DBID = 280030230
--AND begin_interval_time >= TO_DATE('27/06/24 01:14:43','DD/MM/YY HH24:MI:SS')
--AND end_interval_time <= TO_DATE('27/06/24 01:14:43','DD/MM/YY HH24:MI:SS')
ORDER by SNAP_ID;
INSTANCE_NUMBER SNAP_ID STARTUP_TIME
--------------- ---------- ---------------------------
1 2 27/06/24 01:14:43,000000000
-- Executar snapshots manual:
EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT;
-- Apagar SNAP_ID
EXEC DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE (low_snap_id =>1387 , high_snap_id => 1194);
-- Gerar relatório AWR
conn / as sysdba
@$ORACLE_HOME/rdbms/admin/awrrpt.sql
-------------------------------------------------------------------------------------
-- ORA-13541 "system moving window baseline size (%s) greater than retention (%s)" --
-------------------------------------------------------------------------------------
-- MODIFY_SNAPSHOT_SETTINGS
SELECT (60*1) as "1h",
(60*6) as "6h",
(60*12) as "12h",
(60*24) as "1 dia",
((60*12)*8) as "8 dias"
FROM DUAL;
1h 6h 12h 1 dia 8 dias
---------- ---------- ---------- ---------- ----------
60 360 720 1440 5760
-- Calcular WINDOW_SIZE em segundos
SELECT ((&window_size)/60/60)/24 AS window_size
FROM DUAL;
WINDOW_SIZE
-----------
14
A) Altere o tamanho da janela de base para um valor menor que a configuração de retenção do AWR: 1 Day
EXEC DBMS_WORKLOAD_REPOSITORY.modify_baseline_window_size(window_size =>1);
B) Altere a retenção do AWR para 7 Days (In Minutes) conforme abaixo:
EXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(retention=>10080);
C) Modifique a linha de base de volta para 7 dias:
EXEC DBMS_WORKLOAD_REPOSITORY.modify_baseline_window_size(window_size =>7);
-- Mais informações:
https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_WORKLOAD_REPOSITORY.html#GUID-6FFD52F0-BB67-44E0-A95F-A81E4125547D
0 Comentários