c<\/em> online statistics and GTT I mentioned the following:<\/p>\nA final note about those 12c changes in statistics gathering on GTT. In 11g the dbms_stats did a commit at the start. So if you did gather stats after the load, you had to set the GTT as ON COMMIT PRESERVE ROWS. Or you just vacuum what you\u2019ve loaded. That has changed in 12c. If you now choose to do a conventional insert followed by dbms_stats (having set private stats of course) then you don\u2019t need to set on commit preserve rows anymore.<\/em><\/p>\nToday, I realized that I’ve never explained exactly when dbms_stats.gather_table_stats commits the transaction or not. Because, of course, it depends. In summary: 12c<\/em> non-SYS owner GTT with private statistics.
\n<\/p>\nHere is an example. I connect as non-SYS user:<\/p>\n
\nSQL> connect demo\/demo@\/\/localhost\/pdb1\nConnected.\nSQL> show user\nUSER is \"DEMO\"\n<\/code><\/pre>\nI create a permanent table and a global temporary table:<\/p>\n
\nSQL> create table DEMO(text varchar2(20));\nTable created.\n \nSQL> create global temporary table DEMOGTT(text varchar2(20));\nTable created.\n<\/code><\/pre>\nIn the permanent table, I insert my row. The goal is to be sure that this insert is not commited and can be rolled back at the end:<\/p>\n
\nSQL> insert into DEMO values('Forget me, please!');\n1 row created.\n<\/code><\/pre>\nIn the global temporary table I insert one row. The goal is to be sure that the row remains until the end of my transaction (on commit delete rows):<\/p>\n
\nSQL> insert into DEMOGTT values('Preserve me, please!');\n1 row created.\n<\/code><\/pre>\nHere it is:<\/p>\n
\nSQL> select * from DEMO;\n \nTEXT\n--------------------\nForget me, please!\n \nSQL> select * from DEMOGTT;\n \nTEXT\n--------------------\nPreserve me, please!\n<\/code><\/pre>\nThen, I gather statistics on the GTT:<\/p>\n
\nSQL> exec dbms_stats.gather_table_stats(user,'DEMOGTT');\nPL\/SQL procedure successfully completed.\n<\/code><\/pre>\nI check that my rows in the GTT are still there, which is a proof that no commit happened:<\/p>\n
\nSQL> select * from DEMOGTT;\n \nTEXT\n--------------------\nPreserve me, please!\n<\/code><\/pre>\nAnd I check that, as no commit happened, I can rollback my previous insert on the permanent table:<\/p>\n
\nSQL> rollback;\nRollback complete.\n \nSQL> select * from DEMO;\nno rows selected\n<\/code><\/pre>\nThis is the new behavior in 12c<\/i>. The same in 11g<\/i> would have committed my transaction before and after the call to dbms_stats.<\/p>\nGTT only<\/h3>\n
Here is the same example when gathering the stats on the permanent table:<\/p>\n
SQL> show user\nUSER is \"DEMO\"\nSQL> exec dbms_stats.gather_table_stats(user,'DEMO');\nPL\/SQL procedure successfully completed.\n :\nSQL> select * from DEMOGTT;\nno rows selected\n :\nSQL> rollback;\nRollback complete.\n :\nSQL> select * from DEMO;\n :\nTEXT\n--------------------\nForget me, please!\n<\/code><\/pre>\nThe transaction was committed by dbms_stats here: no rows from GTT (on commit delete rows), and the insert in permanent table was commited before my rollback.<\/p>\n
Not for SYS<\/h3>\n
When connected as SYS:<\/p>\n
SQL> show user\nUSER is \"SYS\"\nSQL> exec dbms_stats.gather_table_stats(user,'DEMOGTT');\nPL\/SQL procedure successfully completed.\n \nSQL> select * from DEMOGTT;\nno rows selected\n \nSQL> rollback;\nRollback complete.\n \nSQL> select * from DEMO;\n \nTEXT\n--------------------\nForget me, please!\n<\/code><\/pre>\nThe transaction was committed by dbms_stats here: when the table is owned by SYS, dbms_stats commits.<\/p>\n
I mean, not for SYS owner<\/h3>\n
If I’m connected by SYS but gather stats on a non-SYS table, dbms_stats do not commit:<\/p>\n
\nSQL> show user\nUSER is \"SYS\"\nSQL> exec dbms_stats.gather_table_stats('DEMO','DEMOGTT');\nPL\/SQL procedure successfully completed.\n \nSQL> select * from DEMOGTT;\n \nTEXT\n--------------------\nPreserve me, please!\n \nSQL> rollback;\nRollback complete.\n \nSQL> select * from DEMO;\nno rows selected\n<\/code><\/pre>\nThe behaviour is not related to the user who runs dbms_stats, but the owner of the GTT.<\/p>\n
Private statistics only<\/h3>\n
The default in 12c<\/i> for GTT is private statistics, visible to session only. Trying the same with shared statistics (as in 11g<\/i>):<\/p>\nSQL> show user\nUSER is \"DEMO\"\n \nSQL> select dbms_stats.get_prefs(ownname=>user,tabname=>'DEMO_GTT',pname=>'GLOBAL_TEMP_TABLE_STATS') from dual;\n \nDBMS_STATS.GET_PREFS(OWNNAME=>USER,TABNAME=>'DEMO_GTT',PNAME=>'GLOBAL_TEMP_TABLE\n--------------------------------------------------------------------------------\nSESSION\n \nSQL> exec dbms_stats.set_table_prefs(user,'DEMO_GTT','GLOBAL_TEMP_TABLE_STATS','SHARED' );\nPL\/SQL procedure successfully completed.\n \nSQL> exec dbms_stats.gather_table_stats(user,'DEMOGTT');\nPL\/SQL procedure successfully completed.\n :\nSQL> select * from DEMOGTT;\nno rows selected\n :\nSQL> rollback;\nRollback complete.\n :\nSQL> select * from DEMO;\n :\nTEXT\n--------------------\nForget me, please!\n \nSQL> exec dbms_stats.set_table_prefs(user,'DEMO_GTT', 'GLOBAL_TEMP_TABLE_STATS',null);\nPL\/SQL procedure successfully completed.\n<\/code><\/pre>\nThe dbms_stats did commit my transaction here.<\/p>\n
So what?<\/h3>\n
Private session statistics for GTT is a great feature. Use it: gather statistics after filling the GTT. And don’t worry about on commit delete rows GTT (the default) because this statistic gathering do not commit the transation.<\/p>\n","protected":false},"excerpt":{"rendered":"
By Franck Pachot . In my UKOUG OracleScene article on 12c online statistics and GTT I mentioned the following: A final note about those 12c changes in statistics gathering on GTT. In 11g the dbms_stats did a commit at the start. So if you did gather stats after the load, you had to set the […]<\/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":[59],"tags":[760,209,255],"type_dbi":[],"class_list":["post-10454","post","type-post","status-publish","format-standard","hentry","category-oracle","tag-gtt","tag-oracle-12c","tag-statistics"],"acf":[],"yoast_head":"\n
12c dbms_stats.gather_table_stats on GTT do not commit - dbi Blog<\/title>\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\/12c-dbms_stats-gather_table_stats-on-gtt-do-not-commit\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"12c dbms_stats.gather_table_stats on GTT do not commit\" \/>\n<meta property=\"og:description\" content=\"By Franck Pachot . In my UKOUG OracleScene article on 12c online statistics and GTT I mentioned the following: A final note about those 12c changes in statistics gathering on GTT. In 11g the dbms_stats did a commit at the start. So if you did gather stats after the load, you had to set the […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/12c-dbms_stats-gather_table_stats-on-gtt-do-not-commit\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2017-09-05T17:21:23+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=\"4 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\/12c-dbms_stats-gather_table_stats-on-gtt-do-not-commit\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/12c-dbms_stats-gather_table_stats-on-gtt-do-not-commit\/\"},\"author\":{\"name\":\"Oracle Team\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"headline\":\"12c dbms_stats.gather_table_stats on GTT do not commit\",\"datePublished\":\"2017-09-05T17:21:23+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/12c-dbms_stats-gather_table_stats-on-gtt-do-not-commit\/\"},\"wordCount\":479,\"commentCount\":2,\"keywords\":[\"GTT\",\"Oracle 12c\",\"Statistics\"],\"articleSection\":[\"Oracle\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/12c-dbms_stats-gather_table_stats-on-gtt-do-not-commit\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/12c-dbms_stats-gather_table_stats-on-gtt-do-not-commit\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/12c-dbms_stats-gather_table_stats-on-gtt-do-not-commit\/\",\"name\":\"12c dbms_stats.gather_table_stats on GTT do not commit - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2017-09-05T17:21:23+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/12c-dbms_stats-gather_table_stats-on-gtt-do-not-commit\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/12c-dbms_stats-gather_table_stats-on-gtt-do-not-commit\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/12c-dbms_stats-gather_table_stats-on-gtt-do-not-commit\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"12c dbms_stats.gather_table_stats on GTT do not commit\"}]},{\"@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":"12c dbms_stats.gather_table_stats on GTT do not commit - dbi Blog","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\/12c-dbms_stats-gather_table_stats-on-gtt-do-not-commit\/","og_locale":"en_US","og_type":"article","og_title":"12c dbms_stats.gather_table_stats on GTT do not commit","og_description":"By Franck Pachot . In my UKOUG OracleScene article on 12c online statistics and GTT I mentioned the following: A final note about those 12c changes in statistics gathering on GTT. In 11g the dbms_stats did a commit at the start. So if you did gather stats after the load, you had to set the […]","og_url":"https:\/\/www.dbi-services.com\/blog\/12c-dbms_stats-gather_table_stats-on-gtt-do-not-commit\/","og_site_name":"dbi Blog","article_published_time":"2017-09-05T17:21:23+00:00","author":"Oracle Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Oracle Team","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/12c-dbms_stats-gather_table_stats-on-gtt-do-not-commit\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/12c-dbms_stats-gather_table_stats-on-gtt-do-not-commit\/"},"author":{"name":"Oracle Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"headline":"12c dbms_stats.gather_table_stats on GTT do not commit","datePublished":"2017-09-05T17:21:23+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/12c-dbms_stats-gather_table_stats-on-gtt-do-not-commit\/"},"wordCount":479,"commentCount":2,"keywords":["GTT","Oracle 12c","Statistics"],"articleSection":["Oracle"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/12c-dbms_stats-gather_table_stats-on-gtt-do-not-commit\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/12c-dbms_stats-gather_table_stats-on-gtt-do-not-commit\/","url":"https:\/\/www.dbi-services.com\/blog\/12c-dbms_stats-gather_table_stats-on-gtt-do-not-commit\/","name":"12c dbms_stats.gather_table_stats on GTT do not commit - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2017-09-05T17:21:23+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/12c-dbms_stats-gather_table_stats-on-gtt-do-not-commit\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/12c-dbms_stats-gather_table_stats-on-gtt-do-not-commit\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/12c-dbms_stats-gather_table_stats-on-gtt-do-not-commit\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"12c dbms_stats.gather_table_stats on GTT do not commit"}]},{"@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\/10454","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=10454"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/10454\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=10454"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=10454"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=10454"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=10454"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}