\nSQL> create global temporary table DEMOGTT on commit preserve rows as select * from dba_objects;\nTable created.\nSQL> commit;\nCommit complete.\n<\/code><\/pre>\nThe point here is to use the Filter Predicate LIBrary that is shipped in every oracle installation, even non-Exadata ones, for simulation:<\/p>\n
\nSQL> alter session set \"_rdbms_internal_fplib_enabled\"=true cell_offload_plan_display=always \"_serial_direct_read\"=always;\nSession altered.\n<\/code><\/pre>\nI’ve also forced Serial Direct Read to be sure to do direct path reads.
\nThen I select from it with a highly selective predicate:<\/p>\n
\nSQL> set autotrace trace\nSQL> select object_id from DEMOGTT where object_name like 'X%';\n498 rows selected.\n \nExecution Plan\n----------------------------------------------------------\nPlan hash value: 962761541\n \n-------------------------------------------------------------------------------------\n| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |\n-------------------------------------------------------------------------------------\n| 0 | SELECT STATEMENT | | 1381 | 40049 | 459 (1)| 00:00:01 |\n|* 1 | TABLE ACCESS STORAGE FULL| DEMOGTT | 1381 | 40049 | 459 (1)| 00:00:01 |\n-------------------------------------------------------------------------------------\n \nPredicate Information (identified by operation id):\n---------------------------------------------------\n \n 1 - storage(\"OBJECT_NAME\" LIKE 'X%')\n filter(\"OBJECT_NAME\" LIKE 'X%')\n \nNote\n-----\n - Global temporary table session private statistics used\n \nStatistics\n----------------------------------------------------------\n 1 recursive calls\n 0 db block gets\n 1720 consistent gets\n 1684 physical reads\n 128 redo size\n 9983 bytes sent via SQL*Net to client\n 915 bytes received via SQL*Net from client\n 35 SQL*Net roundtrips to\/from client\n 0 sorts (memory)\n 0 sorts (disk)\n 498 rows processed\n \nSQL> set autotrace off\n<\/code><\/pre>\nThanks to cell_offload_plan_display=always I can see that the optimizer build a plan that can use predicate offloading (the ‘STORAGE’ full table scan).
\nAutotrace tells me that I’ve read 1684 blocks from storage. I check my session cell statistics.<\/p>\n
\nSQL> select name,value from v$mystat join v$statname using(statistic#) where name like 'cell%' and value>0 order by 1;\n \nNAME VALUE\n---------------------------------------------------------------- ----------\ncell IO uncompressed bytes 13795328\ncell blocks processed by cache layer 1684\ncell blocks processed by data layer 1684\ncell blocks processed by txn layer 1684\ncell physical IO interconnect bytes 27770880\ncell scans 2\ncell simulated physical IO bytes eligible for predicate offload 13795328\ncell simulated physical IO bytes returned by predicate offload 10552\n<\/code><\/pre>\nAll the 1684 physical reads were processed by the storage cell layers which means that offloading occurred.<\/p>\n
Conclusion<\/h3>\n
When you are used to it, it’s often easy to build a very small test case to validate any assumption. With this example you know that ‘direct path read temp’ are eligible to SmartScan.<\/p>\n","protected":false},"excerpt":{"rendered":"
By Franck Pachot . I wanted to check if Exadata predicate offloading can occur on Global Temporary Tables. Actually, I thought it did not and I was wrong. I was ready to post that as an hypothesis for https:\/\/community.oracle.com\/thread\/3903836 but, before any post to forums, I try to test what I say because I may […]<\/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":[229],"tags":[103,760,96],"type_dbi":[],"class_list":["post-7221","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","tag-exadata","tag-gtt","tag-oracle"],"acf":[],"yoast_head":"\n
GTT in Exadata are eligible to SmartScan - 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\/gtt-in-exadata-are-eligible-to-smartscan\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"GTT in Exadata are eligible to SmartScan\" \/>\n<meta property=\"og:description\" content=\"By Franck Pachot . I wanted to check if Exadata predicate offloading can occur on Global Temporary Tables. Actually, I thought it did not and I was wrong. I was ready to post that as an hypothesis for https:\/\/community.oracle.com\/thread\/3903836 but, before any post to forums, I try to test what I say because I may […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/gtt-in-exadata-are-eligible-to-smartscan\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2016-03-07T10:21:25+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=\"2 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\/gtt-in-exadata-are-eligible-to-smartscan\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/gtt-in-exadata-are-eligible-to-smartscan\/\"},\"author\":{\"name\":\"Oracle Team\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"headline\":\"GTT in Exadata are eligible to SmartScan\",\"datePublished\":\"2016-03-07T10:21:25+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/gtt-in-exadata-are-eligible-to-smartscan\/\"},\"wordCount\":251,\"commentCount\":0,\"keywords\":[\"Exadata\",\"GTT\",\"Oracle\"],\"articleSection\":[\"Database Administration & Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/gtt-in-exadata-are-eligible-to-smartscan\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/gtt-in-exadata-are-eligible-to-smartscan\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/gtt-in-exadata-are-eligible-to-smartscan\/\",\"name\":\"GTT in Exadata are eligible to SmartScan - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2016-03-07T10:21:25+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/gtt-in-exadata-are-eligible-to-smartscan\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/gtt-in-exadata-are-eligible-to-smartscan\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/gtt-in-exadata-are-eligible-to-smartscan\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"GTT in Exadata are eligible to SmartScan\"}]},{\"@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":"GTT in Exadata are eligible to SmartScan - 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\/gtt-in-exadata-are-eligible-to-smartscan\/","og_locale":"en_US","og_type":"article","og_title":"GTT in Exadata are eligible to SmartScan","og_description":"By Franck Pachot . I wanted to check if Exadata predicate offloading can occur on Global Temporary Tables. Actually, I thought it did not and I was wrong. I was ready to post that as an hypothesis for https:\/\/community.oracle.com\/thread\/3903836 but, before any post to forums, I try to test what I say because I may […]","og_url":"https:\/\/www.dbi-services.com\/blog\/gtt-in-exadata-are-eligible-to-smartscan\/","og_site_name":"dbi Blog","article_published_time":"2016-03-07T10:21:25+00:00","author":"Oracle Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Oracle Team","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/gtt-in-exadata-are-eligible-to-smartscan\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/gtt-in-exadata-are-eligible-to-smartscan\/"},"author":{"name":"Oracle Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"headline":"GTT in Exadata are eligible to SmartScan","datePublished":"2016-03-07T10:21:25+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/gtt-in-exadata-are-eligible-to-smartscan\/"},"wordCount":251,"commentCount":0,"keywords":["Exadata","GTT","Oracle"],"articleSection":["Database Administration & Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/gtt-in-exadata-are-eligible-to-smartscan\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/gtt-in-exadata-are-eligible-to-smartscan\/","url":"https:\/\/www.dbi-services.com\/blog\/gtt-in-exadata-are-eligible-to-smartscan\/","name":"GTT in Exadata are eligible to SmartScan - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2016-03-07T10:21:25+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/gtt-in-exadata-are-eligible-to-smartscan\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/gtt-in-exadata-are-eligible-to-smartscan\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/gtt-in-exadata-are-eligible-to-smartscan\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"GTT in Exadata are eligible to SmartScan"}]},{"@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\/7221","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=7221"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/7221\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=7221"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=7221"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=7221"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=7221"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}