To prepare a presentation about Multitenant Tuning I wanted to see the METHOD_OPT dbms_stats global preference of all my pluggable DBs. In this specific case I had 3 PBDs called pdb1, pdb2 and pdb3 in my CDB. For testing purposes I changed the global preference in pdb1 from its default ‘FOR ALL COLUMNS SIZE AUTO’ to ‘FOR ALL INDEXED COLUMNS SIZE AUTO’:<\/p>\n
c##cbleile_adm@orclcdb@PDB1> exec dbms_stats.set_global_prefs('METHOD_OPT','FOR ALL INDEXED COLUMNS SIZE AUTO');\nc##cbleile_adm@orclcdb@PDB1> select dbms_stats.get_prefs('METHOD_OPT') from dual;\n\nDBMS_STATS.GET_PREFS('METHOD_OPT')\n------------------------------------\nFOR ALL INDEXED COLUMNS SIZE AUTO\n<\/code><\/pre>\nAfterwards I ran my SQL with the containers clause from the root container:<\/p>\n
\nc##cbleile_adm@orclcdb@CDB$ROOT> select con_id, dbms_stats.get_prefs('METHOD_OPT') method_opt from containers(dual);\n\n CON_ID METHOD_OPT\n---------- --------------------------------\n 1 FOR ALL COLUMNS SIZE AUTO\n 3 FOR ALL COLUMNS SIZE AUTO\n 4 FOR ALL COLUMNS SIZE AUTO\n 5 FOR ALL COLUMNS SIZE AUTO\n\n4 rows selected.\n<\/code><\/pre>\nFor CON_ID 3 I expected to see “FOR ALL INDEXED COLUMNS SIZE AUTO”. What is wrong here?<\/p>\n
I actually got it to work with the following query:<\/p>\n
\nc##cbleile_adm@orclcdb@CDB$ROOT> select con_id, method_opt from containers(select dbms_stats.get_prefs('METHOD_OPT') method_opt from dual);\n\n CON_ID METHOD_OPT\n---------- ----------------------------------\n 1 FOR ALL COLUMNS SIZE AUTO\n 4 FOR ALL COLUMNS SIZE AUTO\n 5 FOR ALL COLUMNS SIZE AUTO\n 3 FOR ALL INDEXED COLUMNS SIZE AUTO\n\n4 rows selected.\n<\/code><\/pre>\nThat is interesting. First of all I didn’t know that you can actually use SELECT-statements in the containers clause (according the syntax diagram it has to be a table or a view-name only) and secondly the function dbms_stats.get_prefs in the first example has obviously been called in the root container after getting the data.<\/p>\n
I verified that last statement with a simple test by creating a function in all containers, which just returns the container id of the current container:<\/p>\n
\ncreate or replace function c##cbleile_adm.show_con_id return number\nas\nconid number;\nbegin\n select to_number(sys_context('USERENV', 'CON_ID')) into conid from sys.dual;\n return conid;\n end;\n\/\n<\/code><\/pre>\nAnd then the test:<\/p>\n
\nc##cbleile_adm@orclcdb@CDB$ROOT> select con_id, show_con_id from containers(dual);\n\n CON_ID SHOW_CON_ID\n---------- -----------\n 1 1\n 3 1\n 4 1\n 5 1\n\n4 rows selected.\n\nc##cbleile_adm@orclcdb@CDB$ROOT> select con_id, show_con_id from containers(select show_con_id from dual);\n\n CON_ID SHOW_CON_ID\n---------- -----------\n 4 4\n 1 1\n 3 3\n 5 5\n\n4 rows selected.\n<\/code><\/pre>\nThat proved that the function in the select-list of the first statement is actually called in the root container after getting the data from the PDBs.<\/p>\n
Summary:
\n– be careful when running the containers-clause in a select-statement with a function in the select-list. You may get unexpected results.
\n– the syntax with a select-statement in the containers clause is interesting.<\/p>\n
REMARK: Above tests have been performed with Oracle 19.6.<\/p>\n","protected":false},"excerpt":{"rendered":"
By Clemens Bleile To prepare a presentation about Multitenant Tuning I wanted to see the METHOD_OPT dbms_stats global preference of all my pluggable DBs. In this specific case I had 3 PBDs called pdb1, pdb2 and pdb3 in my CDB. For testing purposes I changed the global preference in pdb1 from its default ‘FOR ALL […]<\/p>\n","protected":false},"author":35,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[59],"tags":[762,64,96],"type_dbi":[],"class_list":["post-14252","post","type-post","status-publish","format-standard","hentry","category-oracle","tag-containers","tag-multitenant","tag-oracle"],"acf":[],"yoast_head":"\n
Functions in SQL with the Multitenant Containers Clause - 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\/functions-in-sql-with-the-multitenant-containers-clause\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Functions in SQL with the Multitenant Containers Clause\" \/>\n<meta property=\"og:description\" content=\"By Clemens Bleile To prepare a presentation about Multitenant Tuning I wanted to see the METHOD_OPT dbms_stats global preference of all my pluggable DBs. In this specific case I had 3 PBDs called pdb1, pdb2 and pdb3 in my CDB. For testing purposes I changed the global preference in pdb1 from its default ‘FOR ALL […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/functions-in-sql-with-the-multitenant-containers-clause\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2020-06-03T11:27:46+00:00\" \/>\n<meta name=\"author\" content=\"Clemens Bleile\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@ifgtxD2SrQ8r!YuXj\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Clemens Bleile\" \/>\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\/functions-in-sql-with-the-multitenant-containers-clause\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/functions-in-sql-with-the-multitenant-containers-clause\/\"},\"author\":{\"name\":\"Clemens Bleile\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/0ac04011f60f2e93c115358d0789c2da\"},\"headline\":\"Functions in SQL with the Multitenant Containers Clause\",\"datePublished\":\"2020-06-03T11:27:46+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/functions-in-sql-with-the-multitenant-containers-clause\/\"},\"wordCount\":270,\"commentCount\":2,\"keywords\":[\"Containers\",\"multitenant\",\"Oracle\"],\"articleSection\":[\"Oracle\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/functions-in-sql-with-the-multitenant-containers-clause\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/functions-in-sql-with-the-multitenant-containers-clause\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/functions-in-sql-with-the-multitenant-containers-clause\/\",\"name\":\"Functions in SQL with the Multitenant Containers Clause - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2020-06-03T11:27:46+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/0ac04011f60f2e93c115358d0789c2da\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/functions-in-sql-with-the-multitenant-containers-clause\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/functions-in-sql-with-the-multitenant-containers-clause\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/functions-in-sql-with-the-multitenant-containers-clause\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Functions in SQL with the Multitenant Containers Clause\"}]},{\"@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\/0ac04011f60f2e93c115358d0789c2da\",\"name\":\"Clemens Bleile\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/1f596609fc67cb28ed714e7bccc81ed4cd73b8582a8148a490c77daeb2fde21a?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/1f596609fc67cb28ed714e7bccc81ed4cd73b8582a8148a490c77daeb2fde21a?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/1f596609fc67cb28ed714e7bccc81ed4cd73b8582a8148a490c77daeb2fde21a?s=96&d=mm&r=g\",\"caption\":\"Clemens Bleile\"},\"description\":\"Clemens Bleile has more than 30 years of IT experience, thirteen in Oracle Support and fifteen in Oracle Consulting. He is specialized in Oracle Database Performance Tuning (SQL Tuning, DB Tuning) and developing an Oracle DB IT architecture (highly available, low-maintenance, cost efficient storage of data). He is an expert in problem analysis and resolution. Prior to joining dbi services, Clemens Bleile was Manager of the EMEA Database Performance team at the Oracle Global Customer Support Services. Clemens Bleile is Oracle Certified Professional 11g, 12c and Oracle Certified Expert for Performance Management and Tuning and holds a Master Degree, Business Information Systems from the Fachhochschule Furtwangen, Germany.\",\"sameAs\":[\"https:\/\/www.dbi-services.com\",\"https:\/\/x.com\/ifgtxD2SrQ8r!YuXj\"],\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/clemens-bleile\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Functions in SQL with the Multitenant Containers Clause - 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\/functions-in-sql-with-the-multitenant-containers-clause\/","og_locale":"en_US","og_type":"article","og_title":"Functions in SQL with the Multitenant Containers Clause","og_description":"By Clemens Bleile To prepare a presentation about Multitenant Tuning I wanted to see the METHOD_OPT dbms_stats global preference of all my pluggable DBs. In this specific case I had 3 PBDs called pdb1, pdb2 and pdb3 in my CDB. For testing purposes I changed the global preference in pdb1 from its default ‘FOR ALL […]","og_url":"https:\/\/www.dbi-services.com\/blog\/functions-in-sql-with-the-multitenant-containers-clause\/","og_site_name":"dbi Blog","article_published_time":"2020-06-03T11:27:46+00:00","author":"Clemens Bleile","twitter_card":"summary_large_image","twitter_creator":"@ifgtxD2SrQ8r!YuXj","twitter_misc":{"Written by":"Clemens Bleile","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/functions-in-sql-with-the-multitenant-containers-clause\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/functions-in-sql-with-the-multitenant-containers-clause\/"},"author":{"name":"Clemens Bleile","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/0ac04011f60f2e93c115358d0789c2da"},"headline":"Functions in SQL with the Multitenant Containers Clause","datePublished":"2020-06-03T11:27:46+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/functions-in-sql-with-the-multitenant-containers-clause\/"},"wordCount":270,"commentCount":2,"keywords":["Containers","multitenant","Oracle"],"articleSection":["Oracle"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/functions-in-sql-with-the-multitenant-containers-clause\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/functions-in-sql-with-the-multitenant-containers-clause\/","url":"https:\/\/www.dbi-services.com\/blog\/functions-in-sql-with-the-multitenant-containers-clause\/","name":"Functions in SQL with the Multitenant Containers Clause - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2020-06-03T11:27:46+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/0ac04011f60f2e93c115358d0789c2da"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/functions-in-sql-with-the-multitenant-containers-clause\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/functions-in-sql-with-the-multitenant-containers-clause\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/functions-in-sql-with-the-multitenant-containers-clause\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Functions in SQL with the Multitenant Containers Clause"}]},{"@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\/0ac04011f60f2e93c115358d0789c2da","name":"Clemens Bleile","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/1f596609fc67cb28ed714e7bccc81ed4cd73b8582a8148a490c77daeb2fde21a?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/1f596609fc67cb28ed714e7bccc81ed4cd73b8582a8148a490c77daeb2fde21a?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/1f596609fc67cb28ed714e7bccc81ed4cd73b8582a8148a490c77daeb2fde21a?s=96&d=mm&r=g","caption":"Clemens Bleile"},"description":"Clemens Bleile has more than 30 years of IT experience, thirteen in Oracle Support and fifteen in Oracle Consulting. He is specialized in Oracle Database Performance Tuning (SQL Tuning, DB Tuning) and developing an Oracle DB IT architecture (highly available, low-maintenance, cost efficient storage of data). He is an expert in problem analysis and resolution. Prior to joining dbi services, Clemens Bleile was Manager of the EMEA Database Performance team at the Oracle Global Customer Support Services. Clemens Bleile is Oracle Certified Professional 11g, 12c and Oracle Certified Expert for Performance Management and Tuning and holds a Master Degree, Business Information Systems from the Fachhochschule Furtwangen, Germany.","sameAs":["https:\/\/www.dbi-services.com","https:\/\/x.com\/ifgtxD2SrQ8r!YuXj"],"url":"https:\/\/www.dbi-services.com\/blog\/author\/clemens-bleile\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/14252","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\/35"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=14252"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/14252\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=14252"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=14252"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=14252"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=14252"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}