(8.0.22)
https://gihyo.jp/dev/serial/01/mysql-road-construction-news/0085
-- スナップショット保存用テーブルと初期スナップショットの作成
drop table monitor.snapshot;
CALL sys.ps_setup_disable_thread(CONNECTION_ID());
CALL sys.statement_performance_analyzer('create_tmp', 'monitor._tmp_snapshot', NULL);
CALL sys.statement_performance_analyzer('snapshot', NULL, NULL);
CALL sys.statement_performance_analyzer('save', 'monitor._tmp_snapshot', NULL);
create table monitor.snapshot as select * from monitor._tmp_snapshot;
select count(*) from monitor._tmp_snapshot;
select count(*) from monitor.snapshot;
CALL sys.ps_setup_enable_thread(CONNECTION_ID());
--
mkdir awr_rpt
cd awr_rpt
CALL sys.ps_setup_disable_thread(CONNECTION_ID());
CALL sys.statement_performance_analyzer('snapshot', NULL, NULL);
CALL sys.statement_performance_analyzer('delta', 'monitor.snapshot', 'analysis');
CALL sys.statement_performance_analyzer('save', 'monitor.snapshot', NULL);
CALL sys.ps_setup_enable_thread(CONNECTION_ID());
mysql < /root/awr_rpt/awr_rpt.sql > /root/awr_rpt/awrrpt_`date '+%Y%m%d%H%M%S'`.txt
find /root/awr_rpt -name "awrrpt_*.txt" -mtime +3 | xargs -I{} rm -rf {}
exit 0
chmod +x awr_rpt.sh
crontab -e
5,15,25,35,45,55 * * * * /root/awr_rpt/awr_rpt.sh
(19c)
-- PDB単位のAWRスナップショット自動取得の有効化
https://blogs.oracle.com/otnjp/kusakabe-014-awr-report-pdb
show parameters awr_pdb_autoflush_enabled
alter system set awr_pdb_autoflush_enabled = true;
show parameters awr_pdb_autoflush_enabled
exec dbms_workload_repository.modify_snapshot_settings(interval=>10,retention=>44640);
select con_id,snap_interval,retention from awr_pdb_wr_control;
select dbms_workload_repository.create_snapshot() from dual;
select max(snap_id) from awr_pdb_snapshot;
--
mkdir awr_rpt
cd awr_rpt
column inst_num new_value inst_num noprint
column num_days new_value num_days noprint
column dbid new_value dbid noprint
column report_type new_value report_type noprint
column awr_location new_value awr_location noprint
column begin_snap new_value begin_snap noprint
column end_snap new_value end_snap noprint
column report_name new_value report_name noprint
select 1 inst_num from dual;
select 1 num_days from dual;
select 334982388 dbid from dual;
select 'text' report_type from dual;
select 'AWR_PDB' awr_location from dual;
select max(snap_id) end_snap from awr_pdb_snapshot;
select max(snap_id)-1 begin_snap from awr_pdb_snapshot;
select '/home/oracle/awr_rpt/awrrpt_' || &inst_num || '_' || &begin_snap || '_' || &end_snap || '.txt' report_name from dual;
exit
vim awr_rpt.sh
#!/bin/sh
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/19.0.0/dbhome_1
export PATH=$ORACLE_HOME/bin:${PATH}
export NLS_LANG=JAPANESE_JAPAN.AL32UTF8
export ORACLE_SID=orcl
sqlplus test/test@pdb1 @/home/oracle/awr_rpt/awr_rpt.sql
find /home/oracle/awr_rpt -name "awrrpt_*.txt" -mtime +3 | xargs -I{} rm -rf {}
exit 0
chmod +x awr_rpt.sh
crontab -e
5,15,25,35,45,55 * * * * /home/oracle/awr_rpt/awr_rpt.sh
(12)
http://pgstatsinfo.sourceforge.net/documents/statsinfo3.2/pg_statsinfo-ja.html#install
--pg_statsinfo
rpm -ivh pg_statsinfo-12.0-1.pg12.rhel7.x86_64.rpm
vim postgresql.conf
shared_preload_libraries = 'pg_statsinfo' # 事前ロードを行う
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # ログファイル名を指定する
pg_statsinfo.snapshot_interval = 10min # スナップショット自動取得
pg_statsinfo.enable_maintenance = 'on' # 自動メンテナンス設定
pg_statsinfo.maintenance_time = '00:02:00' # 自動メンテナンス実行時刻設定
pg_statsinfo.repository_keepday = 3 # スナップショットの保持期間設定
pg_statsinfo.repolog_keepday = 3 # 蓄積ログの保持期間設定
vim pg_hba.conf
# TYPE DATABASE USER CIDR-ADDRESS METHOD [for UNIX]
local all postgres trust
sudo systemctl restart postgresql-12
--
mkdir awr_rpt
cd awr_rpt
end_snap=`/usr/pgsql-12/bin/pg_statsinfo -l | tail -1 | awk '{print $1}'`
begin_snap=`expr ${end_snap} - 1`
report_name='/var/lib/pgsql/awr_rpt/awrrpt_'${begin_snap}'_'${end_snap}'.txt'
/usr/pgsql-12/bin/pg_statsinfo -r All -b ${begin_snap} -e ${end_snap} -d postgres -U postgres -o ${report_name}
find /var/lib/pgsql/awr_rpt -name "awrrpt_*.txt" -mtime +3 | xargs -I{} rm -rf {}
exit 0
chmod +x awr_rpt.sh
crontab -e
5,15,25,35,45,55 * * * * /var/lib/pgsql/awr_rpt/awr_rpt.sh
(2019)
mkdir awr_rpt
cd awr_rpt
notepad awr_rpt.sql
use test;
go
select st.text, qs.*
from sys.dm_exec_query_stats as qs
cross apply sys.dm_exec_sql_text(plan_handle) as st
;
notepad awr_rpt.bat
@echo off
set YYYYMMDD=%date:/=%
set HHMMSS=%time::=%
set HHMMSS=%HHMMSS:~0,6%
if "%HHMMSS:~0,1%" equ " " set HHMMSS=0%HHMMSS:~1%
sqlcmd -s, -i C:\awr_rpt\awr_rpt.sql > C:\awr_rpt\awrrpt_%YYYYMMDD%%HHMMSS%.txt 2>&1
FORFILES /P C:\awr_rpt /M awrrpt_*.txt /D +3 /C "cmd /c del @file"
exit /b 0
