ASH REPORT SHOWS “NO DATA EXISTS”
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%';
COUNT(*)
----------
191
kIsH@x3z6zx9<^>select count(*) from dba_hist_active_sess_history where sample_time like '%17-APR%';
COUNT(*)
----------
1452
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 09.35.41.818 AM 18-APR-22 05.38.09.605 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
kIsH@x3z6zx9<^>@?/rdbms/admin/ashrpt
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