{"id":4171,"date":"2014-11-29T19:59:01","date_gmt":"2014-11-29T18:59:01","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/when-oracle-resets-session-statistics\/"},"modified":"2014-11-29T19:59:01","modified_gmt":"2014-11-29T18:59:01","slug":"when-oracle-resets-session-statistics","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/when-oracle-resets-session-statistics\/","title":{"rendered":"When Oracle resets session statistics"},"content":{"rendered":"<h2>By Franck Pachot<\/h2>\n<p>.<br \/>\nDuring our <a href=\"https:\/\/www.dbi-services.com\/index.php\/products\/dbi-insite-workshops\/oracle-12c-new-features-workshop\">Oracle 12c New Features workshop<\/a> I had a very good question about whether the session statistics are reset or not when doing ALTER SESSION SET CONTAINER. My initial thought was that they were not reset because it&#8217;s the same session (same SID and SERIAL#). But when I&#8217;m not 100% sure about something, I test it. And once again, it proves that even the instructor can learn something new when giving a workshop, thanks to the great interaction with the participants.<\/p>\n<p>My test was very simple, querying the &#8216;logon&#8217; statistics after an ALTER SESSION SET CONTAINER and I came with the following tweet:<\/p>\n<blockquote lang=\"en\"><p>Quizz: How can I be connected and get 0 logons from V$MYSTAT ? <a href=\"http:\/\/t.co\/YZPQNU8FiH\">pic.twitter.com\/YZPQNU8FiH<\/a><\/p>\n<p>\u2014 Franck Pachot (@FranckPachot) <a href=\"https:\/\/twitter.com\/FranckPachot\/status\/537598847946854400\">November 26, 2014<\/a><\/p><\/blockquote>\n<p>Of course that needs more investigation. This is about session statistics. What about session events? and session time model?<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">SQL&gt; connect \/ as sysdba\nConnected.\n\nSQL&gt; select name,value from v$mystat join v$statname using(statistic#) where name like 'logon%';\n\nNAME                                     VALUE\n----------------------------------- ----------\nlogons cumulative                            1\nlogons current                               1\n<\/pre>\n<p>I&#8217;m connected to the root container. I generate a bit of activity (using dbms_system.wait_for_event for fake i\/o activity):<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">SQL&gt; exec for i in 1..1e5 loop dbms_system.wait_for_event('db file sequential read', 0 , 0 ); end loop;\nPL\/SQL procedure successfully completed.\n\nSQL&gt; exec for i in 1..100 loop dbms_system.wait_for_event('db file sequential read', 0 , 1 ); end loop;\nPL\/SQL procedure successfully completed.\n\nSQL&gt; declare s date:=sysdate; begin loop exit when sysdate&gt;s+60\/24\/60\/60; end loop; end;\n  2  \/\nPL\/SQL procedure successfully completed.\n<\/pre>\n<p>and here are my session statistics:<\/p>\n<p><strong>Time Model<\/strong> show 60 seconds of CPU and the 100 seconds of I\/O is in the DB time (time model values are in microseconds):<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">SQL&gt; select stat_name,value from v$sess_time_model where sid=sys_context('userenv','sid') and stat_name in ('DB time','DB CPU');\n\nSTAT_NAME                                VALUE\n----------------------------------- ----------\nDB time                              165084940\nDB CPU                                61119000\n<\/pre>\n<p><strong>Session Events<\/strong> shows those 100 seconds of I\/O and the longest call took 1 second (values are in centisecond when not with &#8216;MICRO&#8217;):<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">SQL&gt; select event,total_waits,time_waited_micro,max_wait from v$session_event where sid=sys_context('userenv','sid');\n\nEVENT                          TOTAL_WAITS TIME_WAITED_MICRO   MAX_WAIT\n------------------------------ ----------- ----------------- ----------\nDisk file operations I\/O                19              1407          0\ndb file sequential read             100100         104060605        101\nSQL*Net message to client               33               123          0\nSQL*Net message from client             32             13842          0\n<\/pre>\n<p><strong>Session Statistics<\/strong> where time is in centiseconds show 60 seconds of CPU and 100 + 60 of DB time:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">SQL&gt; select name,value from v$mystat join v$statname using(statistic#) where name = 'DB time' or name like 'CPU%' or name like 'SQL*Net%client';\n\nNAME                                     VALUE\n----------------------------------- ----------\nCPU used when call started                6118\nCPU used by this session                  6118\nDB time                                  16513\nuser I\/O wait time                       10406\nSQL*Net roundtrips to\/from client           33\n<\/pre>\n<p>Ok, so that is consistent. And this has to be consistent as we often compare information for those 3 sources.<\/p>\n<p>Let&#8217;s now change to another container with ALTER SESSION SET CONTAINER:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">SQL&gt; show con_id\n\nCON_ID\n------------------------------\n1\n\nSQL&gt; alter session set container=PDB1;\n\nSession altered.\n<\/pre>\n<p>and look at the same statistics now:<\/p>\n<pre>SQL&gt; select stat_name,value from v$sess_time_model where sid=sys_context('userenv','sid') and stat_name in ('DB time','DB CPU');\n\nSTAT_NAME                                VALUE\n----------------------------------- ----------\nDB time                              103907514\nDB CPU                                    2000\n<\/pre>\n<pre>SQL&gt; select event,total_waits,time_waited_micro,max_wait from v$session_event where sid=sys_context('userenv','sid');\n\nEVENT                          TOTAL_WAITS TIME_WAITED_MICRO   MAX_WAIT\n------------------------------ ----------- ----------------- ----------\nDisk file operations I\/O                25              2084          0\ndb file sequential read             100100         104060605        101\nSQL*Net message to client               42               180          0\nSQL*Net message from client             41             17774          0\n<\/pre>\n<pre>SQL&gt; select name,value from v$mystat join v$statname using(statistic#) where name = 'DB time' or name like 'CPU%' or name like 'SQL*Net%client';\n\nNAME                                     VALUE\n----------------------------------- ----------\nCPU used when call started                  11\nCPU used by this session                    11\nDB time                                      8\nuser I\/O wait time                           0\nSQL*Net roundtrips to\/from client            5<\/pre>\n<p>All the session statistics have been reset and now have low values. And only them: events and time model still show cumulative values from the begining of the session.<\/p>\n<p>So this unfortunately brings inconsistency. Here:<\/p>\n<ul>\n<li>session statistic DB time cannot be compared with time model<\/li>\n<li>SQL*Net roundtrips cannot be compared to the count of &#8216;SQL*net message to client&#8217; event<\/li>\n<li>CPU info from session statistics cannot be compared with the time model<\/li>\n<li>session statistic wait time cannot be compared with session event time<\/li>\n<\/ul>\n<p>and this how I can be connected and see 0 logons for my session:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">SQL&gt; select name,value from v$mystat join v$statname using(statistic#) where name like 'logon%';\n\nNAME                                     VALUE\n----------------------------------- ----------\nlogons cumulative                            0\nlogons current                               0\n<\/pre>\n<p>Now, as we are talking about resetting statistics, there is something else we can do. As you see above the session keeps track of the longest time for each wait event. Here above, the maximum I\/O time was 1 second. We can reset that if we want:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">SQL&gt; exec dbms_system.kcfrms;\nPL\/SQL procedure successfully completed.\n<\/pre>\n<p>we still have all cumulative values for the session, but the high water mark of wait event duration has been reset:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">SQL&gt; select event,total_waits,time_waited_micro,max_wait from v$session_event where sid=sys_context('userenv','sid');\n\nEVENT                          TOTAL_WAITS TIME_WAITED_MICRO   MAX_WAIT\n------------------------------ ----------- ----------------- ----------\nDisk file operations I\/O                29              2263          0\ndb file sequential read             100100         104060605          0\nSQL*Net message to client               49               209          0\n<\/pre>\n<p>Finally, I made other tests that I do not reproduce here:<br \/>\nIf you ALTER SESSION SET CONTAINER with your current container then statistics are not reset.<br \/>\nIf you ALTER SESSION SET CURRENT_SCHEMA the statistics are not reset.<br \/>\nSo I&#8217;ll stay with a reconnect when I want to reset all session statistics with wait events and time model as well.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>By Franck Pachot . During our Oracle 12c New Features workshop I had a very good question about whether the session statistics are reset or not when doing ALTER SESSION SET CONTAINER. My initial thought was that they were not reset because it&#8217;s the same session (same SID and SERIAL#). But when I&#8217;m not 100% [&hellip;]<\/p>\n","protected":false},"author":27,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[198,59],"tags":[320,96,209],"type_dbi":[],"class_list":["post-4171","post","type-post","status-publish","format-standard","hentry","category-database-management","category-oracle","tag-multitenant-database","tag-oracle","tag-oracle-12c"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v27.2 (Yoast SEO v27.2) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>When Oracle resets session statistics - dbi Blog<\/title>\n<meta name=\"description\" content=\"Good to know: Oracle resets some session statistics when you do change to another container.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/when-oracle-resets-session-statistics\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"When Oracle resets session statistics\" \/>\n<meta property=\"og:description\" content=\"Good to know: Oracle resets some session statistics when you do change to another container.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/when-oracle-resets-session-statistics\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2014-11-29T18:59:01+00:00\" \/>\n<meta name=\"author\" content=\"Oracle Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Oracle Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"5 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/when-oracle-resets-session-statistics\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/when-oracle-resets-session-statistics\/\"},\"author\":{\"name\":\"Oracle Team\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"headline\":\"When Oracle resets session statistics\",\"datePublished\":\"2014-11-29T18:59:01+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/when-oracle-resets-session-statistics\/\"},\"wordCount\":507,\"commentCount\":0,\"keywords\":[\"Multitenant database\",\"Oracle\",\"Oracle 12c\"],\"articleSection\":[\"Database management\",\"Oracle\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/when-oracle-resets-session-statistics\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/when-oracle-resets-session-statistics\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/when-oracle-resets-session-statistics\/\",\"name\":\"When Oracle resets session statistics - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2014-11-29T18:59:01+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"description\":\"Good to know: Oracle resets some session statistics when you do change to another container.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/when-oracle-resets-session-statistics\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/when-oracle-resets-session-statistics\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/when-oracle-resets-session-statistics\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"When Oracle resets session statistics\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\",\"name\":\"Oracle Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"caption\":\"Oracle Team\"},\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/oracle-team\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"When Oracle resets session statistics - dbi Blog","description":"Good to know: Oracle resets some session statistics when you do change to another container.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/when-oracle-resets-session-statistics\/","og_locale":"en_US","og_type":"article","og_title":"When Oracle resets session statistics","og_description":"Good to know: Oracle resets some session statistics when you do change to another container.","og_url":"https:\/\/www.dbi-services.com\/blog\/when-oracle-resets-session-statistics\/","og_site_name":"dbi Blog","article_published_time":"2014-11-29T18:59:01+00:00","author":"Oracle Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Oracle Team","Est. reading time":"5 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/when-oracle-resets-session-statistics\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/when-oracle-resets-session-statistics\/"},"author":{"name":"Oracle Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"headline":"When Oracle resets session statistics","datePublished":"2014-11-29T18:59:01+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/when-oracle-resets-session-statistics\/"},"wordCount":507,"commentCount":0,"keywords":["Multitenant database","Oracle","Oracle 12c"],"articleSection":["Database management","Oracle"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/when-oracle-resets-session-statistics\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/when-oracle-resets-session-statistics\/","url":"https:\/\/www.dbi-services.com\/blog\/when-oracle-resets-session-statistics\/","name":"When Oracle resets session statistics - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2014-11-29T18:59:01+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"description":"Good to know: Oracle resets some session statistics when you do change to another container.","breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/when-oracle-resets-session-statistics\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/when-oracle-resets-session-statistics\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/when-oracle-resets-session-statistics\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"When Oracle resets session statistics"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee","name":"Oracle Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","caption":"Oracle Team"},"url":"https:\/\/www.dbi-services.com\/blog\/author\/oracle-team\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/4171","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/27"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=4171"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/4171\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=4171"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=4171"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=4171"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=4171"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}