Check if license is available. Since ASH is extra cost tool, it requires additional charge to purchase.

kIsH@x3z6zx9<^>show parameter control_man

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_management_pack_access       string      DIAGNOSTIC+TUNING

Check the record count in the ash views. If the database is up and running for many days, then record count should be quite high as per the retention.

Example: If 1000 snapshots are generated per day and retention for AWR is 7 days, then

Snapshot generation = 7 * 1000 = 7000 (Instance should be up and running on all these 7 days)

If instance is down, then ASH has no work to do and it is expected to be empty.

kIsH@x3z6zx9<^>select count(*) from v$active_session_history where sample_time like '%18-APR%';


kIsH@x3z6zx9<^>select count(*) from dba_hist_active_sess_history where sample_time like '%17-APR%';


kIsH@x3z6zx9<^>col mint for a20
kIsH@x3z6zx9<^>col maxt for a20
kIsH@x3z6zx9<^>select min(sample_time),max(sample_time) from dba_hist_active_sess_history;

MIN(SAMPLE_TIME)                                                            MAX(SAMPLE_TIME)
--------------------------------------------------------------------------- ---------------------------------------------------------------------------
02-MAR-22 AM                                                   18-APR-22 PM

Make sure that the user inputs are filled properly on the prompt.

There can be mistakes due to following reasons,

  • Fill wrong day or time
  • Fill wrong day or time format
  • Fill file format instead of time
  • Fill date instead of duration

Current Instance

   DB Id    DB Name      Inst Num Instance
----------- ------------ -------- ------------
 3942786352 X3Z6ZX9             1 x3z6zx9

Specify the Report Type
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
Enter value for report_type:

Type Specified:  html

Enter the begin time in the below pointed format

ASH Samples in this Workload Repository schema

Oldest ASH sample available:  09-Apr-22 19:08:26   [  12887 mins in the past]
Latest ASH sample available:  18-Apr-22 17:55:42   [      0 mins in the past]

Specify the timeframe to generate the ASH report
Enter begin time for report:

--    Valid input formats:
--      To specify absolute begin time:
--        [MM/DD[/YY]] HH24:MI[:SS]
--        Examples: 02/23/03 14:30:15  <-=========
--                  02/23 14:30:15
--                  14:30:15
--                  14:30
--      To specify relative begin time: (start with '-' sign)
--        -[HH24:]MI
--        Examples: -1:15  (SYSDATE - 1 Hr 15 Mins)
--                  -25    (SYSDATE - 25 Mins)

Defaults to -15 mins
Enter value for begin_time:

Check MMON logs for more understanding on the background

*** 2022-04-17 11:29:21.059
*** SESSION ID:(9.1) 2022-04-17 11:29:21.059
*** CLIENT ID:() 2022-04-17 11:29:21.059
*** SERVICE NAME:() 2022-04-17 11:29:21.059
*** MODULE NAME:() 2022-04-17 11:29:21.059
*** ACTION NAME:() 2022-04-17 11:29:21.059

minact-scn master-status: grec-scn:0x0000.00000000 gmin-scn:0x0000.00000000 gcalc-scn:0x0000.00000000

*** 2022-04-17 11:29:30.695
***ket_aba_spawn: unable to schedule MMON Slave, error 100

Check for any hidden parameters set which purges the snapshots faster

Leave a Reply

%d bloggers like this: