{"id":7251,"date":"2021-06-25T09:33:47","date_gmt":"2021-06-25T09:33:47","guid":{"rendered":"https:\/\/rax-stg-wp-aws.com\/blogs\/oracle-sql-profile-and-baselines\/"},"modified":"2026-11-03T19:28:19","modified_gmt":"2026-11-03T19:28:19","slug":"oracle-sql-profile-and-baselines","status":"publish","type":"blogs","link":"https:\/\/www.rackspace.com\/en-gb\/blogs\/oracle-sql-profile-and-baselines\/","title":{"rendered":"Oracle SQL profile and baselines"},"content":{"rendered":"<p>\nThis post highlights the differences between SQL profile and baselines in Oracle; and explains how it works when tuning a query.<\/p>\n<p><strong>Optimizer, profile, and baseline<\/strong><\/p>\n<p>At a high level, these three elements work together as follows:<\/p>\n<ul>\n<li>The query optimizer uses information like system statistics, bind variables,&nbsp;and compilations to get the best plan for query execution. However, sometimes input defects lead to a sub-optimal plan.<\/li>\n<li>SQL profiles contain auxiliary information that mitigates this issue. They minimize these mistakes and help the optimizer select the best plan.<\/li>\n<li>SQL plan baselines for SQL statements consist of a set of accepted plans. After parsing the statement, the optimizer selects the best plan from the set of accepted plans. If the cost optimizer finds another good plan, it adds the new plan to the plan history. However, the optimizer doesn&#8217;t use the new plan until&nbsp;it verifies that it will perform better than the currently accepted plan.<\/li>\n<\/ul>\n<p>Think of it this way: SQL profiles give information to the optimizer to help select the best plan but don&#8217;t force the optimizer to select any specific plan. SQL plan baselines limit the optimizer plan selection to a set of accepted plans. If you want to consider cost-based plans, include them in the accepted baseline&nbsp;set of plans.<\/p>\n<p>Use SQL profiles if you want the optimizer to use a least-cost plan and need to use the latest statistics. Use baselines when you want to use one of a specific set of plans. If SQL plan baselines can&#8217;t get the best plan from the accepted set, use the SQL profile instead.<\/p>\n<p><strong>SQL Plan Management<\/strong><\/p>\n<p>SQL Plan Management (SPM) has the following components:<\/p>\n<ul>\n<li>Plan capture<\/li>\n<li>Plan selection<\/li>\n<li>Plan evolution<\/li>\n<\/ul>\n<p><strong>SPM plan capture<\/strong><\/p>\n<p>When you execute any statement, the system hard parses it and generates a cost plan according to the available SQL profile. After it selects a cost-based plan, it compares the plans available in the SQL plan baselines. If a cost-based generated plan matches one of the accepted plans, then you can use that plan. If the plan does not match, the system adds it to the plan baselines as an<br \/>\nunaccepted plan.<\/p>\n<p><strong>SPM plan selection<\/strong><\/p>\n<p>When you execute the SQL statement with a baseline plan, it opts for the best&nbsp;plan for that SQL. The optimizer uses the same process. The available SQL profile also affects the estimated cost for each plan and selects a plan accordingly.<\/p>\n<p><strong>SPM plan evolution<\/strong><\/p>\n<p>The last component of SPM is the evolution of unaccepted plans, which tests the unaccepted plans against the accepted plans. This process assesses the best plan considering the time taken by a query and the CPU resources required. It accepts the best plan according to the cost of the query. If an SQL profile is available, then it affects the estimated cost.<\/p>\n<p><strong>Profile versus baseline comparison<\/strong><\/p>\n<p>See the difference between SQL Profile and SPM Baseline according to CDN blogs <a href=\"https:\/\/www.cnblogs.com\/princessd8251\/articles\/3637461.html\">here<\/a>.<\/p>\n<p><img decoding=\"async\" src=\"\/wp-content\/uploads\/2026\/blog_article_images\/26334-body-Table1.png\" alt=\"SPM table 1\" \/><\/p>\n<p><strong>Architecture<\/strong><\/p>\n<p>The following image shows SQL plan baseline architecture:&nbsp;<\/p>\n<p><img decoding=\"async\" src=\"\/wp-content\/uploads\/2026\/blog_article_images\/26334-body-Picture1.png\" alt=\"SPM architechture 1\" \/><\/p>\n<p><a href=\"https:\/\/ittutorial.org\/sql-plan-management-using-sql-plan-baselines-in-oracle-oracle-database-performance-tuning-tutorial-14\">Architecture<\/a><\/p>\n<p class=\"text-align-center\">&nbsp;<\/p>\n<p><strong>Load SQL plan baselines<\/strong><\/p>\n<p><img decoding=\"async\" src=\"\/wp-content\/uploads\/2026\/blog_article_images\/26334-body-Picture2.png\" alt=\"SPM Image 2\" \/><\/p>\n<p><a href=\"https:\/\/ittutorial.org\/sql-plan-management-using-sql-plan-baselines-in-oracle-oracle-database-performance-tuning-tutorial-14\/\">Loading SQL plan baselines<\/a>&nbsp;<\/p>\n<p>With the first method, you can set the <strong>OPTIMIZER\\_CAPTURE\\_SQL\\_PLAN\\_BASELINES&nbsp;<\/strong>initialization parameter to `TRUE` to use automatic plan capture. This initialization parameter is `FALSE` by default, so set it to `TRUE` as shown in<br \/>\nthe following example:<\/p>\n<p><img decoding=\"async\" src=\"\/wp-content\/uploads\/2026\/blog_article_images\/26334-body-Code1.png\" alt=\"Baselines Code 1\" \/><\/p>\n<p>With the second method, you can use the **DBMS\\_SPM** package to enable manually manage SQL plan baselines. Load the plans from an SQL tuning set as shown in the following example:<\/p>\n<p><img decoding=\"async\" src=\"\/wp-content\/uploads\/2026\/blog_article_images\/26334-body-Code2.png\" alt=\"SPM Code 2 \" \/><\/p>\n<p class=\"text-align-center\">&nbsp;<\/p>\n<p><strong>Load the SQL plan baseline manually<\/strong><\/p>\n<p>Use the following command to load the plan baseline manually:<\/p>\n<blockquote><p>\n<img decoding=\"async\" src=\"\/wp-content\/uploads\/2026\/blog_article_images\/26334-body-Code3.png\" alt=\"SPM Code 3\" \/>\n<\/p><\/blockquote>\n<p><strong>Check the SQL plan baseline usage<\/strong><\/p>\n<p>After loading the SQL plan baseline, you need to execute SQL to see if the optimizer is using the SQL plan baseline. You can query the SQL plan baselinesby using SQL_TEXT and the plan name as follows:<\/p>\n<p class=\"text-align-center\">&nbsp;<\/p>\n<p><img decoding=\"async\" src=\"\/wp-content\/uploads\/2026\/blog_article_images\/26334-body-Code4.png\" alt=\"SPM Code 4\" \/><\/p>\n<p><strong>Display SQL plan baselines<\/strong><br \/>\nRun the following query to display SQL plan baselines:<\/p>\n<p><img decoding=\"async\" src=\"\/wp-content\/uploads\/2026\/blog_article_images\/26334-body-Code5.png\" alt=\"SPM Code 5\" \/><\/p>\n<p><strong>Drop the SQL plan baseline<\/strong><\/p>\n<p>To drop the SQL plan baseline, run the following query to check which SQL plan the optimizer is using:<\/p>\n<p><img decoding=\"async\" src=\"\/wp-content\/uploads\/2026\/blog_article_images\/26334-body-Code6.png\" alt=\"SPM Pic 6\" \/><\/p>\n<p>Then, after you get the plan in use, run the following command to drop the baseline:&nbsp;<\/p>\n<p><img decoding=\"async\" src=\"\/wp-content\/uploads\/2026\/blog_article_images\/26334-body-Code7.png\" alt=\"SPM Code 7\" \/><\/p>\n<p><strong>Oracle SQL profile&nbsp;<\/strong><\/p>\n<p>The SQL tunning advisor, which you trigger through Oracle Enterprise Manager (OEM) or by using a command-line query, can generate the SQL profile for an SQL statement. This profile includes additional information about the statement.<\/p>\n<p><strong>Example<\/strong><\/p>\n<p>In this example, you first run SQL tuning advisor against the sql_id and then perform operations on the SQL profile:<\/p>\n<p>1. &nbsp;<strong>Run SQL tuning advisor<\/strong><\/p>\n<p>Run the following SQL tuning advisor code for sql_id, 6dkrnbx1zdwy38:<\/p>\n<p><img decoding=\"async\" src=\"\/wp-content\/uploads\/2026\/blog_article_images\/26334-body-Code8a.png\" alt=\"SPM Code 8 a \" \/><\/p>\n<p><img decoding=\"async\" src=\"\/wp-content\/uploads\/2026\/blog_article_images\/26334-body-Code8b.png\" alt=\"SPM Code 8 b\" \/><\/p>\n<p>Run the following **DBMS\\_SQLTUNE.report\\_tuning\\_task** for recommendation<\/p>\n<p>&nbsp;<\/p>\n<p><img decoding=\"async\" src=\"\/wp-content\/uploads\/2026\/blog_article_images\/26334-body-Code9.png\" alt=\"SPM Code 9 pic\" \/><\/p>\n<p><strong>Accept the sql_profile&nbsp;<\/strong><\/p>\n<p>Run the following code to accept the sql_profile:<\/p>\n<p><img decoding=\"async\" src=\"\/wp-content\/uploads\/2026\/blog_article_images\/26334-body-Code10.png\" alt=\"SPM Code 10\" \/><\/p>\n<p><strong>&nbsp;3. Check the name of the sql_profile<\/strong><\/p>\n<p>Use the following query to check the sql_profile name:<\/p>\n<p><img decoding=\"async\" src=\"\/wp-content\/uploads\/2026\/blog_article_images\/26334-body-Code11.png\" alt=\"SPM Code 11\" \/><\/p>\n<p><strong>4. &nbsp;Disable the sql_profile&nbsp;<\/strong><\/p>\n<p>Run the following code to disable the sql_profile:<\/p>\n<p><img decoding=\"async\" src=\"\/wp-content\/uploads\/2026\/blog_article_images\/26334-body-Code12.png\" alt=\"SPM Code 12\" \/><\/p>\n<p>To enable it, change the value from `DISABLED` to `ENABLED`.&nbsp;<\/p>\n<p><strong>5. Drop the sql_profile<\/strong><\/p>\n<p>Run the following code to drop the sql_profile:<\/p>\n<p><img decoding=\"async\" src=\"\/wp-content\/uploads\/2026\/blog_article_images\/26334-body-Code13.png\" alt=\"SPM Code 13\" \/><\/p>\n<p><strong>Conclusion&nbsp;<\/strong><\/p>\n<p>When you execute any SQL statement, the optimizer creates an execution plan to parse the query, retrieve the data from the hard disk, and place it in memory. The SQL profile and baselines guide the optimizer to choose the least costly plan in terms of time and CPU cost. &nbsp;A good SQL plan efficiently runs a query and provides the desired results faster.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Oracle SQL profile and baselines<\/p>\n","protected":false},"author":2,"featured_media":7253,"menu_order":0,"comment_status":"open","ping_status":"open","template":"","format":"standard","meta":{"content-type":"","nid":"26334","content_type":"blog","created_by":"10130","authored_on":"2023-12-04T10:33:47+00:00","field_article_industries":"","tag_category":"Technical","field_blog_author_text":"by Rackspace Technology Staff","seo_description":"Understand the differences between SQL profiles and baselines in Oracle. Learn how they interact with the optimizer and aid in effective query tuning.\r\n","field_featured_image":"","insights_image":"\/wp-content\/uploads\/2026\/blog_technical_images\/26334-insights_image-oracle-technical-blog-1-insights.jpg","field_partners":"","rxt_platforms":"Oracle","product":"","publisheddate":"2021-06-25T09:33:47","rxt_solutions":"Data Management, Data Modernization, Data Strategy & Governance, Next-Gen Data Platforms","opengraph_tag":"","x_tag":"","teaser_flag":"Technical Blog","seo_titile":"Oracle SQL Profiles and Baselines | Rackspace Technology","field_topics":"","field_slug":"oracle-sql-profile-and-baselines","footnotes":""},"categories":[178],"tags":[204],"class_list":["post-7251","blogs","type-blogs","status-publish","format-standard","has-post-thumbnail","hentry","category-blog","tag-technical"],"_links":{"self":[{"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/blogs\/7251","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/blogs"}],"about":[{"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/types\/blogs"}],"author":[{"embeddable":true,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/comments?post=7251"}],"version-history":[{"count":2,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/blogs\/7251\/revisions"}],"predecessor-version":[{"id":9261,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/blogs\/7251\/revisions\/9261"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/media\/7253"}],"wp:attachment":[{"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/media?parent=7251"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/categories?post=7251"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/tags?post=7251"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}