{"id":217,"date":"2024-12-16T06:53:53","date_gmt":"2024-12-16T06:53:53","guid":{"rendered":"https:\/\/w3buddy.com\/?p=217"},"modified":"2026-01-15T13:12:18","modified_gmt":"2026-01-15T07:42:18","slug":"how-to-create-an-sql-baseline-for-a-specific-sql_id","status":"publish","type":"post","link":"https:\/\/w3buddy.com\/blog\/how-to-create-an-sql-baseline-for-a-specific-sql_id\/","title":{"rendered":"How to Create an SQL Baseline for a Specific SQL_ID"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">Learn how to create an SQL baseline for a specific SQL_ID to stabilize execution plans and ensure consistent query performance in Oracle databases. This guide walks you through the necessary steps to create, manage, and use SQL baselines for optimal query optimization and improved database performance.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">1) Check Existing SQL Plan Baselines<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">To verify if any SQL plan baselines already exist:<\/p>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">SELECT * FROM dba_sql_plan_baselines;<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">2) Create a SQL Tuning Set<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">A <strong>SQL Tuning Set (STS)<\/strong> is a database object that contains SQL statements along with their execution statistics and context, which could include a user-defined priority. It can be populated from sources like AWR, the shared SQL area, or custom SQL queries.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">An STS includes:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>SQL statements<\/li>\n\n\n\n<li>Execution context (e.g., user schema, application module name, action, bind values, cursor compilation environment)<\/li>\n\n\n\n<li>Execution statistics (e.g., elapsed time, CPU time, buffer gets, disk reads, rows processed, optimizer cost)<\/li>\n\n\n\n<li>Execution plans and row source statistics (optional)<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">To create an STS:<\/p>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">BEGIN\n  DBMS_SQLTUNE.CREATE_SQLSET(\n    SQLSET_NAME => 'atfz4c63at1k1_STS01',\n    DESCRIPTION => 'Test SQL Tuning Set'\n  );\nEND;\n\/<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">This will create the SQL tuning set successfully.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">3) Verify SQL Monitoring is Enabled for the SQL Statement<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Check if SQL monitoring is enabled for the specific SQL statement:<\/p>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">SELECT DBMS_SQLTUNE.report_sql_monitor(\n    type => 'TEXT', \n    report_level => 'ALL', \n    SQL_ID => 'atfz4c63at1k1'\n) AS REPORT \nFROM DUAL;<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">4) Locate the AWR Snapshot Needed to Populate the SQL Tuning Set<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">To find the relevant AWR snapshots:<\/p>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">SELECT snap_id, begin_interval_time, end_interval_time \nFROM dba_hist_snapshot \nORDER BY end_interval_time DESC;<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">5) Populate the SQL Tuning Set<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">There are two options for populating the SQL tuning set:<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>A) From AWR:<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">DECLARE\n  CUR SYS_REFCURSOR;\nBEGIN\n  OPEN CUR FOR\n    SELECT VALUE(P) \n    FROM TABLE(DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(\n      BEGIN_SNAP => 1939, \n      END_SNAP => 1940, \n      BASIC_FILTER => 'sql_id = ''atfz4c63at1k1''',\n      ATTRIBUTE_LIST => 'ALL'\n    )) p;\n  DBMS_SQLTUNE.LOAD_SQLSET(\n    SQLSET_NAME => 'atfz4c63at1k1_STS01', \n    POPULATE_CURSOR => CUR\n  );\n  CLOSE CUR;\nEND;\n\/<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>B) From Shared SQL Area:<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">DECLARE\n  CUR SYS_REFCURSOR;\nBEGIN\n  OPEN CUR FOR\n    SELECT VALUE(P) \n    FROM TABLE(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('sql_id = ''atfz4c63at1k1''')) p;\n  DBMS_SQLTUNE.LOAD_SQLSET(\n    SQLSET_NAME => 'atfz4c63at1k1_STS01', \n    POPULATE_CURSOR => CUR\n  );\n  CLOSE CUR;\nEND;\n\/<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">6) Verify Contents of the SQL Tuning Set<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">To confirm the contents of your SQL tuning set, run:<\/p>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">SELECT * \nFROM TABLE(DBMS_SQLTUNE.SELECT_SQLSET(SQLSET_NAME => 'atfz4c63at1k1_STS01'));<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">7) Load the Desired Plan as a SQL Plan Baseline<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">To load the plan from the SQL tuning set into a SQL plan baseline:<\/p>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">DECLARE\n  MY_PLANS PLS_INTEGER;\nBEGIN\n  MY_PLANS := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(\n    SQLSET_NAME => 'atfz4c63at1k1_STS01', \n    BASIC_FILTER => 'PLAN_HASH_VALUE = ''3811430562'''\n  );\nEND;\n\/<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">8) Verify SQL Plan Baseline Creation<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">To verify that the SQL plan baseline was successfully created:<\/p>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">SELECT * FROM dba_sql_plan_baselines;<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">This guide provides all the necessary steps for creating an SQL baseline for a specific <code>SQL_ID<\/code> and ensuring plan stabilization.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Learn how to create an SQL baseline for a specific SQL_ID to stabilize execution plans and ensure consistent query performance in Oracle databases. This guide walks you through the necessary steps to create, manage, and use SQL baselines for optimal query optimization and improved database performance. 1) Check Existing SQL Plan Baselines To verify if [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"googlesitekit_rrm_CAowu461DA:productID":"","footnotes":""},"categories":[1225],"tags":[],"class_list":["post-217","post","type-post","status-publish","format-standard","hentry","category-database"],"_links":{"self":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/217","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/comments?post=217"}],"version-history":[{"count":2,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/217\/revisions"}],"predecessor-version":[{"id":1458,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/217\/revisions\/1458"}],"wp:attachment":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/media?parent=217"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/categories?post=217"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/tags?post=217"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}