AWR

 ****** AWR ****** 


Run the following query to display the AWR settings. 

By default, the INTERVAL equals to 1 hour and the RETENTION equals to 8 days. 

 

col SNAP_INTERVAL format a20 

col RETENTION format a20 

col TOPNSQL format a10 

SELECT SNAP_INTERVAL, RETENTION, TOPNSQL FROM DBA_HIST_WR_CONTROL; 

 

SNAP_INTERVAL        RETENTION            TOPNSQL

-------------------- -------------------- ----------

+00000 01:00:00.0    +00008 00:00:00.0    DEFAULT

 

 

Modify the INTERVAL setting to 30 minutes. 

 

BEGIN 

 DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(INTERVAL => 30); 

END; 

/ 

 

PL/SQL procedure successfully completed.

 

SELECT SNAP_INTERVAL, RETENTION, TOPNSQL FROM DBA_HIST_WR_CONTROL;

 

SNAP_INTERVAL        RETENTION            TOPNSQL

-------------------- -------------------- ----------

+00000 00:30:00.0    +00008 00:00:00.0    DEFAULT

 

 

 

Execute the following query to display the existing AWR snapshots. 

 

col BEGIN_INTERVAL_TIME format a28 

col END_INTERVAL_TIME format a28 

SELECT SNAP_ID, BEGIN_INTERVAL_TIME, END_INTERVAL_TIME, SNAP_LEVEL 

  FROM DBA_HIST_SNAPSHOT 

ORDER BY SNAP_ID; 

 

   SNAP_ID BEGIN_INTERVAL_TIME          END_INTERVAL_TIME            SNAP_LEVEL

---------- ---------------------------- ---------------------------- ----------

         1 05-AUG-25 09.03.07.000 AM    05-AUG-25 11.57.37.042 PM             1

         2 06-AUG-25 08.47.07.000 AM    06-AUG-25 08.58.05.520 AM             1

         3 06-AUG-25 09.22.49.000 AM    06-AUG-25 10.00.48.420 AM             1

 

 

Manually create a heavyweight AWR snapshot. 

 

exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT(FLUSH_LEVEL=>'ALL') 

 

SELECT SNAP_ID, BEGIN_INTERVAL_TIME, END_INTERVAL_TIME, SNAP_LEVEL 

  FROM DBA_HIST_SNAPSHOT 

ORDER BY SNAP_ID DESC FETCH FIRST 1 ROWS ONLY; 

 

Delete the manually created snapshot. 

begin 

 DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE( 

    LOW_SNAP_ID  => &v_snapID, 

    HIGH_SNAP_ID => &v_snapID); 

end; 

/ 

 

 

Space consumed by AWR snapshots depend on the INTERVAL and RETENTION setting.

 

col OCCUPANT_NAME format a20 

col MOVE_PROCEDURE format a20 

SELECT OCCUPANT_NAME, SPACE_USAGE_KBYTES/1024 MB, MOVE_PROCEDURE 

FROM V$SYSAUX_OCCUPANTS 

WHERE OCCUPANT_NAME = 'SM/AWR'; 

 

Run the following script as sysdba to produce a report about the space consumed by the AWR 

snapshots. 

conn / as sysdba 

@ ?/rdbms/admin/awrinfo.sql 

 

 

Submit the following query to display the space consumed by the Optimizer backup statistics.

 

conn system/oracle 

SELECT OCCUPANT_NAME, SPACE_USAGE_KBYTES/1024 MB, MOVE_PROCEDURE 

FROM V$SYSAUX_OCCUPANTS 

WHERE OCCUPANT_NAME = 'SM/OPTSTAT'; 

 

Display the optimizer statistics retention period. 

By default, it is set to 31 days.

 

SELECT DBMS_STATS.GET_STATS_HISTORY_RETENTION FROM DUAL; 

 

Decrease the retention period of the optimizer statistics history to 7 days. 

exec DBMS_STATS.ALTER_STATS_HISTORY_RETENTION(7); 

 

SQL> exec DBMS_STATS.ALTER_STATS_HISTORY_RETENTION(7);

PL/SQL procedure successfully completed.

 

SQL> SELECT DBMS_STATS.GET_STATS_HISTORY_RETENTION FROM DUAL;

GET_STATS_HISTORY_RETENTION

---------------------------

                          7

Purge the optimizer statistics that do not comply with the new retention period. 

When you pass NULL to the PURGE_STATS procedure, the statistics that are older than the current 

retention period will be deleted. 

Note: do not run the procedure below in a production system if the size occupied by the 

optimizer statistics is in gigabytes. It may take very long time to finish. Delete the statistics in 

chunks instead. 

 

exec DBMS_STATS.PURGE_STATS(NULL)

 






 


Comments

Post a Comment

Popular posts from this blog

To change the data directory location for PostgreSQL after installation

 To change the data directory location for PostgreSQL after installation, you'll need to move the existing data to the new location and update PostgreSQL's configuration to use the new directory. Here’s a step-by-step guide to achieve that: 1. Stop PostgreSQL service : First, stop the PostgreSQL service to ensure no data is being accessed or written during the move. sudo systemctl stop postgresql-15 2. Create the new data directory : Create the new directory where you want to move your PostgreSQL data, and set the appropriate permissions for the postgres user. sudo mkdir -p /new/postgres/pg_data sudo chown postgres:postgres /new/postgres/pg_data sudo chmod 700 /new/postgres/pg_data 3. Move the existing data : Move your existing data from the old directory to the new directory. This step will ensure all your current databases and configurations are preserved. sudo rsync -av --progress /var/lib/pgsql/15/data/ /new/postgres/pg_data/ The rsync command helps preserve permission...
Read more

GIT

Image
GIT Download : https://git-scm.com/downloads Click the one above link to down load the Git software. Installation: Click on the machine OS supported, for windows click on the download for windows. Click on the OS bit version as per your PC or laptop supported, it will download. Checking the GIT version. GIT -HUB account Creation: Click on this link :   https://github.com/ Open Gmail account check mail from GitHub for code and enter the code.  GIT HUB  Account creation is completed :---- Account creation  END -----: Create a Repository:  (or) :----C reate Repository  END -----: GIT ARCHITECTURE: GIT configuration:      git config --global user.name "Sainath Kota"      git config --global user.email "sainath.kota@gmail.com" Remove an entry in global configuration:     git config --global --unset user.name      git config --global --uns...
Read more