{"id":1228,"date":"2015-10-23T22:33:57","date_gmt":"2015-10-23T20:33:57","guid":{"rendered":"http:\/\/www.ludovicocaldara.net\/dba\/?p=1228"},"modified":"2020-08-18T16:29:47","modified_gmt":"2020-08-18T14:29:47","slug":"querying-the-dba_hist_sys_time_model-to-get-historical-data","status":"publish","type":"post","link":"https:\/\/www.ludovicocaldara.net\/dba\/querying-the-dba_hist_sys_time_model-to-get-historical-data\/","title":{"rendered":"Querying the dba_hist_sys_time_model to get historical data"},"content":{"rendered":"

This quick post is mainly\u00a0for myself… I will certainly use it for reference in the future.<\/p>\n

Debugging problems due to adaptive dynamic sampling<\/strong> and in general adaptive features<\/strong> sometimes needs to get historical data about, e.g., parse time<\/strong>.<\/p>\n

In order to get this information you may need to query the view DBA_HIST_SYS_TIME_MODEL<\/strong> (take care, it needs Diagnostic Pack license!)<\/p>\n

You can use this query as an example.<\/p>\n

with h as (\r\nselect s.snap_id, s.BEGIN_INTERVAL_TIME,\r\n        --s.END_INTERVAL_TIME,\r\n        g.STAT_ID,\r\n        g.stat_name,\r\n        nvl(\r\n          decode(\r\n            greatest(\r\n              VALUE,\r\n              nvl(lag(VALUE) over (partition by s.dbid, s.instance_number, g.stat_name order by s.snap_id),0)\r\n             ),\r\n            VALUE,\r\n            VALUE - lag(VALUE)\r\n               over (partition by s.dbid,\r\n                                    s.instance_number,\r\n                                    g.stat_name\r\n                    order by s.snap_id\r\n                ),\r\n            VALUE\r\n           ),\r\n           0\r\n        ) VALUE\r\nfrom DBA_HIST_SNAPSHOT s,\r\n    DBA_HIST_SYS_TIME_MODEL g,\r\n    v$instance i\r\nwhere s.SNAP_ID=g.SNAP_ID\r\nand s.BEGIN_INTERVAL_TIME >=\r\n    trunc(to_timestamp(nvl('&startdate',to_char(sysdate,'YYYYMMDD')),'YYYYMMDD'))\r\nand s.BEGIN_INTERVAL_TIME < =\r\n    trunc(to_timestamp(nvl('&enddate',to_char(sysdate,'YYYYMMDD')),'YYYYMMDD')+1)\r\nand s.instance_number=i.instance_number\r\nand s.instance_number=g.instance_number\r\n)\r\nselect p.begin_interval_time, p.value as \"parse time elapsed\", t.value as \"DB time\",\r\nround(p.value\/t.value,2)*100 as \"parse pct\", par.value as opt_adapt_feat\r\nfrom h p, h t , dba_hist_parameter par\r\nwhere p.snap_id=t.snap_id\r\nand p.snap_id=par.snap_id\r\nand p.stat_name='parse time elapsed'\r\nand t.stat_name='DB time'\r\nand par.parameter_name='optimizer_adaptive_features'\r\nand t.value>0\r\norder by p.begin_interval_time\r\n\/<\/pre>\n
 <\/p>\n
In this specific example, it shows the “parse time elapsed”<\/strong>, the “DB time” and the percentage<\/strong> parse\/dbtime, along with the value of the parameter “optimizer_adaptive_features<\/strong>“. You can use it to check if changing the parameters related to adaptive dynamic sampling improves or not the parse time.<\/p>\n
The output will be something like this:<\/p>\n
BEGIN_INTERVAL_TIME    \t  parse time elapsed     DB time  parse pct OPT_ADAPT_FEAT\r\n-------------- ---------- ------------------ ----------- ---------- ----------\r\n23-OCT-15 03.00.36.569 AM       3235792   \t57030479      \t5.67 TRUE\r\n23-OCT-15 03.30.38.712 AM       3438093   \t60262996       \t5.71 TRUE\r\n23-OCT-15 04.00.40.709 AM       4622998   \t69813760       \t6.62 TRUE\r\n23-OCT-15 04.30.42.776 AM       4590463   \t56441202       \t8.13 TRUE\r\n23-OCT-15 05.00.44.735 AM      13772357        113741371      \t12.11 TRUE\r\n23-OCT-15 05.30.46.722 AM       3448944   \t49807800       \t6.92 TRUE\r\n23-OCT-15 06.00.48.664 AM       4792886   \t54235691       \t8.84 TRUE\r\n23-OCT-15 06.30.50.713 AM       8527305   \t58775613      \t14.51 TRUE\r\n23-OCT-15 07.00.52.667 AM       8518273   \t75248056      \t11.32 TRUE\r\n23-OCT-15 07.30.54.622 AM       9800048  \t17381081       1.07 TRUE\r\n23-OCT-15 08.00.56.609 AM       6986551       1629027583      .43 TRUE\r\n23-OCT-15 08.30.58.568 AM       8414695       2493025822      .34 TRUE\r\n23-OCT-15 09.00.00.457 AM      13648260       2412333113      .57 TRUE\r\n23-OCT-15 09.30.02.384 AM      15186610       4635080356      .33 TRUE\r\n23-OCT-15 10.00.04.298 AM      23465769  \t39080849       3.17 FALSE\r\n23-OCT-15 10.30.06.421 AM      12152991       2654461964      .46 FALSE\r\n23-OCT-15 11.00.08.444 AM      24901111        549936076       4.53 FALSE\r\n23-OCT-15 11.30.10.485 AM       8080236        354568317       2.28 FALSE\r\n23-OCT-15 12.00.12.453 PM       4291839   \t91028268       \t4.71 FALSE\r\n23-OCT-15 12.30.14.430 PM       3675163        177312397       2.07 FALSE\r\n23-OCT-15 01.00.16.468 PM       9184841        231138367       3.97 FALSE\r\n23-OCT-15 01.30.18.438 PM       8132397        162607229       5 FALSE\r\n23-OCT-15 02.00.20.707 PM      13375709        210251458       6.36 FALSE\r\n23-OCT-15 02.30.23.740 PM      10116413        285114368       3.55 FALSE\r\n23-OCT-15 03.00.25.699 PM       8067777        123864339       6.51 FALSE\r\n23-OCT-15 03.30.27.641 PM       5787931        110621767       5.23 FALSE\r\n<\/pre>\n
HTH<\/p>\n
—<\/p>\n
Ludo<\/p>\n","protected":false},"excerpt":{"rendered":"
This quick post is mainly\u00a0for 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 … Continue reading →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[326,3,52,6,132],"tags":[],"class_list":["post-1228","post","type-post","status-publish","format-standard","hentry","category-oracle","category-oracledb","category-12c","category-perf","category-triblog"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/posts\/1228","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/comments?post=1228"}],"version-history":[{"count":3,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/posts\/1228\/revisions"}],"predecessor-version":[{"id":1231,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/posts\/1228\/revisions\/1231"}],"wp:attachment":[{"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/media?parent=1228"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/categories?post=1228"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/tags?post=1228"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}