Oracle Database Commands and Practical Examples

Monday 11 January 2021

AWR - Delete AWR Snapshots ID

AWR - Delete AWR Snapshots ID

Query to display the existing AWR snapshots period

SQL> col BEGIN_INTERVAL_TIME format a28

SQL> col END_INTERVAL_TIME format a28

SQL> SELECT SNAP_ID, BEGIN_INTERVAL_TIME, END_INTERVAL_TIME, SNAP_LEVEL

2 FROM DBA_HIST_SNAPSHOT

3 ORDER BY SNAP_ID;

SNAP_ID BEGIN_INTERVAL_TIME END_INTERVAL_TIME SNAP_LEVEL

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

237 03-JAN-21 11.11.04.000 AM 03-JAN-21 11.21.29.133 AM 1

238 03-JAN-21 11.21.29.133 AM 03-JAN-21 12.30.52.205 PM 1

239 03-JAN-21 12.30.52.205 PM 03-JAN-21 01.30.06.140 PM 1

240 03-JAN-21 01.30.06.140 PM 03-JAN-21 02.30.41.034 PM 1

241 03-JAN-21 02.30.41.034 PM 03-JAN-21 04.43.52.074 PM 1

242 03-JAN-21 04.43.52.074 PM 04-JAN-21 10.53.30.874 AM 1

243 04-JAN-21 10.53.30.874 AM 04-JAN-21 01.21.24.107 PM 1

244 04-JAN-21 01.21.24.107 PM 04-JAN-21 06.52.48.561 PM 1

245 04-JAN-21 06.52.48.561 PM 04-JAN-21 07.30.44.011 PM 1

246 04-JAN-21 07.30.44.011 PM 04-JAN-21 08.30.48.957 PM 1

247 04-JAN-21 08.30.48.957 PM 04-JAN-21 09.30.35.943 PM 1

SNAP_ID BEGIN_INTERVAL_TIME END_INTERVAL_TIME SNAP_LEVEL

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

248 04-JAN-21 09.30.35.943 PM 04-JAN-21 10.41.13.096 PM 1

249 04-JAN-21 10.41.13.096 PM 04-JAN-21 11.30.08.754 PM 1

250 05-JAN-21 08.52.50.000 PM 05-JAN-21 09.03.53.714 PM 1

251 05-JAN-21 09.03.53.714 PM 05-JAN-21 11.03.33.863 PM 1

252 06-JAN-21 04.29.38.000 PM 06-JAN-21 04.39.51.949 PM 1

253 06-JAN-21 04.39.51.949 PM 06-JAN-21 05.30.14.405 PM 1

254 06-JAN-21 06.40.07.000 PM 06-JAN-21 06.51.12.663 PM 1

255 06-JAN-21 06.51.12.663 PM 06-JAN-21 07.30.42.067 PM 1

256 07-JAN-21 01.40.05.000 PM 07-JAN-21 01.50.44.257 PM 1

257 07-JAN-21 01.50.44.257 PM 07-JAN-21 02.30.02.151 PM 1

258 07-JAN-21 02.30.02.151 PM 07-JAN-21 03.30.09.955 PM 1

SNAP_ID BEGIN_INTERVAL_TIME END_INTERVAL_TIME SNAP_LEVEL

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

259 07-JAN-21 03.30.09.955 PM 07-JAN-21 04.30.15.553 PM 1

260 07-JAN-21 04.30.15.553 PM 07-JAN-21 05.30.20.993 PM 1

261 07-JAN-21 05.30.20.993 PM 07-JAN-21 06.30.25.701 PM 1

262 07-JAN-21 06.30.25.701 PM 07-JAN-21 07.30.31.904 PM 1

263 07-JAN-21 07.30.31.904 PM 07-JAN-21 08.30.38.215 PM 1

264 07-JAN-21 08.30.38.215 PM 07-JAN-21 10.48.52.857 PM 1

265 07-JAN-21 10.48.52.857 PM 07-JAN-21 11.30.55.648 PM 1

266 07-JAN-21 11.30.55.648 PM 08-JAN-21 11.17.14.791 AM 1

267 08-JAN-21 11.17.14.791 AM 08-JAN-21 12.30.30.901 PM 1

268 08-JAN-21 12.30.30.901 PM 08-JAN-21 03.13.41.727 PM 1

269 08-JAN-21 03.13.41.727 PM 08-JAN-21 04.30.46.203 PM 1

SNAP_ID BEGIN_INTERVAL_TIME END_INTERVAL_TIME SNAP_LEVEL

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

270 08-JAN-21 04.30.46.203 PM 08-JAN-21 05.30.52.773 PM 1

271 08-JAN-21 05.30.52.773 PM 08-JAN-21 06.30.55.994 PM 1

272 08-JAN-21 06.30.55.994 PM 08-JAN-21 07.30.03.886 PM 1

273 08-JAN-21 07.30.03.886 PM 09-JAN-21 04.45.43.649 PM 1

274 09-JAN-21 04.45.43.649 PM 09-JAN-21 05.30.38.500 PM 1

275 09-JAN-21 05.30.38.500 PM 09-JAN-21 06.30.53.204 PM 1

276 11-JAN-21 01.27.38.000 PM 11-JAN-21 01.38.49.671 PM 1

277 11-JAN-21 01.38.49.671 PM 11-JAN-21 02.30.59.139 PM 1

278 11-JAN-21 02.30.59.139 PM 11-JAN-21 03.30.47.602 PM 1

279 11-JAN-21 03.30.47.602 PM 11-JAN-21 04.30.52.254 PM 1

43 rows selected.

Flush all AWR snapshots and check snapshots period details.

If FLUSH_LEVEL is not specified, it defaults to TYPICAL for a non-CDB database.

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

PL/SQL procedure successfully completed.

SQL> SELECT SNAP_ID, BEGIN_INTERVAL_TIME, END_INTERVAL_TIME, SNAP_LEVEL

2 FROM DBA_HIST_SNAPSHOT

3 ORDER BY SNAP_ID DESC FETCH FIRST 1 ROWS ONLY;

SNAP_ID BEGIN_INTERVAL_TIME END_INTERVAL_TIME SNAP_LEVEL

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

279 11-JAN-21 03.30.47.602 PM 11-JAN-21 04.30.52.254 PM 1

Delete based on SNAP ID

SQL> DEFINE v_snapID = &Enter_SnapID

Enter value for enter_snapid: 279

SQL> begin

2 DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(

3 LOW_SNAP_ID => &v_snapID,

4 HIGH_SNAP_ID => &v_snapID);

5 end;

6 /

old 3: LOW_SNAP_ID => &v_snapID,

new 3: LOW_SNAP_ID => 279,

old 4: HIGH_SNAP_ID => &v_snapID);

new 4: HIGH_SNAP_ID => 279);

PL/SQL procedure successfully completed.

AWR - Script execution's For non and RAC Database AWR report

Script execution's For non and RAC Database AWR report

Example Below for 2 node RAC .

SQL> @d

NAME INSTANCE_NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE CURRENT_SCN FAILOVER_SCN

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

SMRRAC smrrac1 smrrac READ WRITE PRIMARY YES 0

Execute statement example awrgrpti.sql

SQL> @?/rdbms/admin/awrgrpti.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:

Type Specified: html

Instances in this Workload Repository schema

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

DB Id INSTT_NUM DB Name INSTT_NAME Host

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

* 4255065810 2 SMRRAC smrrac2 s01

* 4255065810 1 SMRRAC smrrac1 s02

Enter value for dbid: 4255065810

Header Details:

AWR - Set AWR Retention period from 1hr to 30 minutes

Enabling and Disabling AWR Snapshots Capture

enabled when STATISTICS_LEVEL = TYPICAL or ALL

disabled when STATISTICS_LEVEL = BASIC

Initial settings: RETENTION = 8 days, INTERVAL = 60 minutes and 31 days

Execute command to Check for snapshot intervals period:

SQL> SELECT SNAP_INTERVAL, RETENTION FROM DBA_HIST_WR_CONTROL;

SNAP_INTERVAL

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

RETENTION

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

+00000 01:00:00.0

+00008 00:00:00.0

+40150 00:01:00.0

+00008 00:00:00.0

Execute statement to set AWR STATISTICS_LEVEL to ALL to change interval time.

C:\Users\varun>sqlplus sys/system123@pdb as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Jan 11 16:48:31 2021

Version 19.3.0.0.0

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

SQL> show parameter STATISTICS_LEVEL

NAME TYPE VALUE

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

client_statistics_level string TYPICAL

statistics_level string TYPICAL

SQL> ALTER SYSTEM SET STATISTICS_LEVEL=ALL SCOPE=BOTH;

SQL> col SNAP_INTERVAL format a20

SQL> col RETENTION format a20

SQL> col TOPNSQL format a10

SQL> SELECT SNAP_INTERVAL, RETENTION, TOPNSQL FROM DBA_HIST_WR_CONTROL;

SNAP_INTERVAL RETENTION TOPNSQL

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

+40150 00:01:00.0 +00008 00:00:00.0 DEFAULT

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

Change snapshot interval from 1 hour to 30 minute

SQL> BEGIN

2 DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(INTERVAL => 30);

3 END;

4 /

PL/SQL procedure successfully completed.

Statement to check Snapshot interval period

SQL> col SNAP_INTERVAL format a20

SQL> col RETENTION format a20

SQL> col TOPNSQL format a10

SQL> 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

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

HOME

Monday 11 January 2021

AWR - Delete AWR Snapshots ID

AWR - Script execution's For non and RAC Database AWR report

AWR - Set AWR Retention period from 1hr to 30 minutes