CODEWITHSUNDEEP
oracle-databaseawrstatspack
Thilo
Thilo

Reputation: 262794

Customize Oracle AWR report

Oracle creates nicely HTML-formatted workload reports (AWR) that list things like instance statistics, wait events, the top ten heavy SQL statements (by number of executions, number of disk reads, and so on).

Are there (easy) ways to customize this report?

For example, I want the top 25 SQL, instead of the top ten.

Upvotes: 2

Views: 1850

Answers (2)

Dinesh vishe
Dinesh vishe

Reputation: 3598

you can do it from following.

Top-n SQL thresholds The topnsql is used to specify the number of SQL to collect at each AWR snapshot for each criteria like elapsed time, CPU time, parse calls, shareable memory, and version count. The topnsql is normally set to a small number like 10, because you only want to see the most current SQL statements. This SQL information is normally purged after a period of time, after which the SQL source code is no longer needed.

begin dbms_workload_repository.modify_snapshot_settings(

retention => 7200,

interval =>60 ,

topnsql =>10 ,

dbid => 123661118);

end; /

Upvotes: 1

dpbradley
dpbradley

Reputation: 11925

See the awr*.sql files in $ORACLE_HOME/rdbms/admin - they are the files that produce the reports. I've copied these files and tweaked them to alter the reports for my own use.

[Edit]

It occurred to me after my original answer that you might have only been interested in modifying the top-N parameter in this report - this is done via the DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS package to modify the snapshot collection parameter.

Upvotes: 4

Related Questions