Today I would like to show you, a very Oracle Database feature called Automatic Workload Repository (AWR) that allows a complete view of the database, as I / O-consuming queries, CPU, instance information, server , fragmentation, index usage, wait events, resource-intensive events, and a wider range of information about instance behavior. It is a complete database report and helps, and much, the Oracle DBA to investigate and find out possible problems.
Generally, Oracle installations that are licensed to use Oracle Enterprise Manager (OEM) generate this report in an automated manner, where the DBA defines a range and the report is generated within that range. At companies where I worked, this report is generated hourly. If there is a need to generate it for a period of one week, for example with hourly information to try to identify when some strange instance behavior started to occur, I would have to generate each one manually by repeating it 168 times .. LOL
But let's not do that right? To resolve this issue, I will make this script available to you:
Oracle Database - Generate AWR.
With it, you can specify the start and end day and time, and the script generates all files in that period and exports them to a directory of your choice. Before you can run this script, you will need to execute simple 2 commands in the database to create the directory in the database and grant us access to the directory you created:
CREATE OR REPLACE DIRECTORY awr_reports_dir AS '/tmp/awr';
GRANT READ, WRITE ON DIRECTORY AWR_REPORTS_DIR TO dirceu.resende;
Ready. Just run the script, enter the desired period and download the generated HTML files. Good analysis, DBAs!