This quick post is mainly for myself… I will certainly use it for reference in the future.
Debugging problems due to adaptive dynamic sampling and in general adaptive features sometimes needs to get historical data about, e.g., parse time.
In order to get this information you may need to query the view DBA_HIST_SYS_TIME_MODEL (take care, it needs Diagnostic Pack license!)
You can use this query as an example.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 |
with h as ( select s.snap_id, s.BEGIN_INTERVAL_TIME, --s.END_INTERVAL_TIME, g.STAT_ID, g.stat_name, nvl( decode( greatest( VALUE, nvl(lag(VALUE) over (partition by s.dbid, s.instance_number, g.stat_name order by s.snap_id),0) ), VALUE, VALUE - lag(VALUE) over (partition by s.dbid, s.instance_number, g.stat_name order by s.snap_id ), VALUE ), 0 ) VALUE from DBA_HIST_SNAPSHOT s, DBA_HIST_SYS_TIME_MODEL g, v$instance i where s.SNAP_ID=g.SNAP_ID and s.BEGIN_INTERVAL_TIME >= trunc(to_timestamp(nvl('&startdate',to_char(sysdate,'YYYYMMDD')),'YYYYMMDD')) and s.BEGIN_INTERVAL_TIME < = trunc(to_timestamp(nvl('&enddate',to_char(sysdate,'YYYYMMDD')),'YYYYMMDD')+1) and s.instance_number=i.instance_number and s.instance_number=g.instance_number ) select p.begin_interval_time, p.value as "parse time elapsed", t.value as "DB time", round(p.value/t.value,2)*100 as "parse pct", par.value as opt_adapt_feat from h p, h t , dba_hist_parameter par where p.snap_id=t.snap_id and p.snap_id=par.snap_id and p.stat_name='parse time elapsed' and t.stat_name='DB time' and par.parameter_name='optimizer_adaptive_features' and t.value>0 order by p.begin_interval_time / |
In this specific example, it shows the “parse time elapsed”, the “DB time” and the percentage parse/dbtime, along with the value of the parameter “optimizer_adaptive_features“. You can use it to check if changing the parameters related to adaptive dynamic sampling improves or not the parse time.
The output will be something like this:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
BEGIN_INTERVAL_TIME parse time elapsed DB time parse pct OPT_ADAPT_FEAT -------------- ---------- ------------------ ----------- ---------- ---------- 23-OCT-15 03.00.36.569 AM 3235792 57030479 5.67 TRUE 23-OCT-15 03.30.38.712 AM 3438093 60262996 5.71 TRUE 23-OCT-15 04.00.40.709 AM 4622998 69813760 6.62 TRUE 23-OCT-15 04.30.42.776 AM 4590463 56441202 8.13 TRUE 23-OCT-15 05.00.44.735 AM 13772357 113741371 12.11 TRUE 23-OCT-15 05.30.46.722 AM 3448944 49807800 6.92 TRUE 23-OCT-15 06.00.48.664 AM 4792886 54235691 8.84 TRUE 23-OCT-15 06.30.50.713 AM 8527305 58775613 14.51 TRUE 23-OCT-15 07.00.52.667 AM 8518273 75248056 11.32 TRUE 23-OCT-15 07.30.54.622 AM 9800048 17381081 1.07 TRUE 23-OCT-15 08.00.56.609 AM 6986551 1629027583 .43 TRUE 23-OCT-15 08.30.58.568 AM 8414695 2493025822 .34 TRUE 23-OCT-15 09.00.00.457 AM 13648260 2412333113 .57 TRUE 23-OCT-15 09.30.02.384 AM 15186610 4635080356 .33 TRUE 23-OCT-15 10.00.04.298 AM 23465769 39080849 3.17 FALSE 23-OCT-15 10.30.06.421 AM 12152991 2654461964 .46 FALSE 23-OCT-15 11.00.08.444 AM 24901111 549936076 4.53 FALSE 23-OCT-15 11.30.10.485 AM 8080236 354568317 2.28 FALSE 23-OCT-15 12.00.12.453 PM 4291839 91028268 4.71 FALSE 23-OCT-15 12.30.14.430 PM 3675163 177312397 2.07 FALSE 23-OCT-15 01.00.16.468 PM 9184841 231138367 3.97 FALSE 23-OCT-15 01.30.18.438 PM 8132397 162607229 5 FALSE 23-OCT-15 02.00.20.707 PM 13375709 210251458 6.36 FALSE 23-OCT-15 02.30.23.740 PM 10116413 285114368 3.55 FALSE 23-OCT-15 03.00.25.699 PM 8067777 123864339 6.51 FALSE 23-OCT-15 03.30.27.641 PM 5787931 110621767 5.23 FALSE |
HTH
—
Ludo
Latest posts by Ludovico (see all)
- New views in Oracle Data Guard 23c - January 3, 2024
- New in Data Guard 21c and 23c: Automatic preparation of the primary - December 22, 2023
- Does FLASHBACK QUERY work across incarnations or after a Data Guard failover? - December 13, 2023
Pingback: Querying the dba_hist_sys_time_model to get historical data - Ludovico Caldara - Blogs - triBLOG