ORACLE AWRレポート 取得

$ sqlplus / as sysdba
SQL> @?/rdbms/admin/awrrpt

Enter value for report_type: html

Enter value for num_days: 7

Specify the Begin and End Snapshot Ids

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Enter value for begin_snap: 69752

Begin Snapshot Id specified: 69752

Enter value for end_snap: 69910

End   Snapshot Id specified: 69910

Enter value for report_name: return

Using the report name awrrpt_2_69752_69910.html

【diffをとる場合】

SQL> @?/rdbms/admin/awrddrpt

期間1、期間2を入力

https://www.ashisuto.co.jp/db_blog/article/oracle_awrextr-sql.html

【AWRレポートがとれない。】

SQL> show parameter

control_management_pack_access     string  NONE

timed_statistics                                     boolean  TRUE

statistics_level                                             string  TYPICAL

_object_statistics                                     boolean TRUE

■ UNUSABLEががないか？

SQL> select index_name,partition_name, status from user_ind_partitions where 

status = 'UNUSABLE' order by index_name;

→ 全て、USABLE 

■_object_statistics TRUEで無かった場合。

sql> alter system set "_object_statistics"=TRUE scope=both;
System altered.
SQL> select DBID, INSTANCE_NUMBER, SNAP_ID, count(SQL_ID) from WRH$_SQLSTAT group by DBID, INSTANCE_NUMBER, SNAP_ID order by 2, 1, 3 ;

DBID INSTANCE_NUMBER SNAP_ID COUNT(SQL_ID)
---------- --------------- ---------- -------------
3531627254 1 70355 115
3531627254 1 70356 111
3531627254 1 70357 100
・
・
3531627254 1 70583 94
229 rows selected.

【解決】
SQL> ALTER SYSTEM SET CONTROL_MANAGEMENT_PACK_ACCESS='DIAGNOSTIC+TUNING' SCOPE=MEMORY SID=‘sidxxxx’;
SQL> show parameter control_management_pack_access
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_management_pack_access string DIAGNOSTIC+TUNING → Noneになっていた。

【複数のAWRレポートを一括取得する。】

$ su - oracle

$ mkdir -p AWR/Tools/logs

$ vi /home/oracle/AWR/Tools/get_awr_base.sql <<__EOF__

set head off

set feed off

col min_snap new_value min_snap

col max_snap new_value max_snap

select max(SNAP_ID) min_snap from dba_hist_snapshot where INSTANCE_NUMBER=(select INSTANCE_NUMBER from v$instance) and

END_INTERVAL_TIME <= to_timestamp('&1'||':59','yyyy/mm/dd_hh24:mi:ss') ;

select max(SNAP_ID) max_snap from dba_hist_snapshot where INSTANCE_NUMBER=(select INSTANCE_NUMBER from v$instance) and

END_INTERVAL_TIME <= to_timestamp('&2'||':59','yyyy/mm/dd_hh24:mi:ss') ;

spool /home/oracle/AWR/Tools/get_awr.sql

select

'define report_type=html' || chr(10) ||

'define num_days=1' || chr(10) ||

'define begin_snap=' || SNAP_ID || chr(10) ||

'define end_snap=' || (SNAP_ID + 1)|| chr(10) ||

'define report_name=/home/oracle/AWR/Tools/logs/awr_' || instance_name || '_' || to_char(END_INTERVAL_TIME,'yyyymmdd_hh24mi') || '.html' || chr(10) ||

'@?/rdbms/admin/awrrpt.sql' || chr(10) ||

'execute DBMS_LOCK.SLEEP(1)' ||chr(10)

from DBA_HIST_SNAPSHOT s,v$instance i

where SNAP_ID between &min_snap and &max_snap -1

and s.INSTANCE_NUMBER = i.INSTANCE_NUMBER

order by SNAP_ID;

spool off

@/home/oracle/AWR/Tools/get_awr.sql

__EOF__

SQL> @get_awr_base.sql 2023/2/17_10:00 2023/2/21_09:00

※ 参考：https://bismarc256.hateblo.jp/entry/2019/11/13/200000