Avec le pack "Diagnostic" Oracle permet d'obtenir un rapport d'activité grâce à AWR (Automatic Workload Repository).
Un premier paramétrage consiste à définir un intervalle et une rétention (dans cet exemple : de 20mn et 30j - soit 43200 minutes). Pour cela des droits administrateur sont nécessaires :
sqlplus / as sysdbaSQL> begin 2 dbms_workload_repository.modify_snapshot_settings(RETENTION=>43200, INTERVAL=>20);
3 end;
4 /
PL/SQL procedure successfully completed.
SQL> select * from dba_hist_wr_control;
DBID,SNAP_INTERVAL,RETENTION,TOPNSQL,CON_ID,SRC_DBID,SRC_DBNAME
1260426948,+00000 00:20:00.0,+00030 00:00:00.0,DEFAULT,0,1260426948,hr9Pour que ce rapport AWR ne soit pas vide (c'est à dire plein de commentaire "No data exists for this section of the report") il est aussi nécessaire d'activer le pack et les statistiques :
SQL> ALTER SYSTEM set CONTROL_MANAGEMENT_PACK_ACCESS = 'DIAGNOSTIC+TUNING';
SQL> show parameter TIMED_STATISTICS NAME_COL_PLUS_SHOW_PARAM TYPE VALUE_COL_PLUS_SHOW_PARAM ------------------------ --------- --------------------------------------------- timed_statistics boolean TRUE
SQL> show parameter CONTROL_MANAGEMENT_PACK_ACCESS NAME_COL_PLUS_SHOW_PARAM TYPE VALUE_COL_PLUS_SHOW_PARAM ------------------------------- --------- --------------------------------------------- control_management_pack_access string DIAGNOSTIC+TUNING
Ensuite, après avoir exécuté le traitement, demander la production du rapport (toujours avec un rôle administrateur), ici en mode texte :
SQL> @$ORACLE_HOME/rdbms/admin/awrrpt.sql Specify the Report Type ~~~~~~~~~~~~~~~~~~~~~~~ AWR reports can be generated in the following formats. Please enter the name of the format at the prompt. Default value is 'html'. 'html' HTML format (default) 'text' Text format 'active-html' Includes Performance Hub active report Enter value for report_type: text ... Type Specified: text ... Current Instance ~~~~~~~~~~~~~~~~ DB Id DB Name Inst Num Instance Container Name -------------- -------------- -------------- -------------- -------------- 1260426948 HR9PPR 1 hr9ppr hr9ppr Instances in this Workload Repository schema ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ DB Id Inst Num DB Name Instance Host ------------ ---------- --------- ---------- ------ * 1260426948 1 HR9PPR hr9ppr svlnx001 Using 1260426948 for database Id Using 1 for instance number Specify the number of days of snapshots to choose from ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Entering the number of days (n) will result in the most recent (n) days of snapshots being listed. Pressing <return> without specifying a number lists all completed snapshots. Enter value for num_days: 1 Listing the last day's Completed Snapshots Instance DB Name Snap Id Snap Started Snap Level ------------ ------------ ---------- ------------------ ---------- hr9ppr HR9PPR 10108 11 Jul 2025 00:00 1 10109 11 Jul 2025 01:00 1 10110 11 Jul 2025 02:00 1 10111 11 Jul 2025 03:00 1 10112 11 Jul 2025 04:00 1 10113 11 Jul 2025 05:00 1 10114 11 Jul 2025 06:00 1 10115 11 Jul 2025 07:00 1 10116 11 Jul 2025 08:00 1 10117 11 Jul 2025 09:00 1 10118 11 Jul 2025 10:00 1 <- intervalle a 20mn 10119 11 Jul 2025 10:20 1 10120 11 Jul 2025 10:40 1 10121 11 Jul 2025 11:00 1 10122 11 Jul 2025 11:20 1 10123 11 Jul 2025 11:40 1 10124 11 Jul 2025 12:00 1 <- debut du batch 10125 11 Jul 2025 12:20 1 <- fin du batch 10126 11 Jul 2025 12:40 1 10127 11 Jul 2025 13:00 1 10128 11 Jul 2025 13:20 1 10129 11 Jul 2025 13:40 1 Specify the Begin and End Snapshot Ids ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Enter value for begin_snap: 10124 Begin Snapshot Id specified: 10124 Enter value for end_snap: 10125 End Snapshot Id specified: 10125
Specify the Report Name ~~~~~~~~~~~~~~~~~~~~~~~ The default report file name is awrrpt_1_10124_10125.txt. To use this name, press <return> to continue, otherwise enter an alternative. Enter value for report_name: awrrpt_20250711_1200-1220.txt
Le rapport est produit dans le répertoire local.
Dans le cas de mon traitement, le rapport indique :
Top 10 Foreground Events by Total Wait Time~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Total Wait Avg % DB Wait Event Waits Time (sec) Wait time Class ------------------------------ ----------- ---------- --------- ------ -------- log file switch (checkpoint in 713 841.8 1180.70ms 78.8 Configur DB CPU 189.8 17.8 log file switch completion 333 34.2 102.67ms 3.2 Configur direct path write temp 1,865 2 1.09ms .2 User I/O
...
Finding 2: Log File Switches Impact is .72 active sessions, 81.98% of total activity. -------------------------------------------------------- Log file switch operations were consuming significant database time while waiting for checkpoint completion. This problem can be caused by use of hot backup mode on tablespaces. DML to tablespaces in hot backup mode causes generation of additional redo. Recommendation 1: Database Configuration Estimated benefit is .72 active sessions, 81.98% of total activity. ------------------------------------------------------------------- Action Verify whether incremental shipping was used for standby databases. Recommendation 2: Database Configuration Estimated benefit is .72 active sessions, 81.98% of total activity. ------------------------------------------------------------------- Action Increase the size of the log files to 2048 M to hold at least 20 minutes of redo information....
Les fichiers redologs (3 fichiers de 50Mo) sont trop petits pour le volume de mises à jour à traiter. 80% du temps est consommé à les gérer (checkpoint et swith) !
