{"id":4298,"date":"2025-06-18T22:22:09","date_gmt":"2025-06-18T22:22:09","guid":{"rendered":"https:\/\/w3buddy.com\/?post_type=cposts&#038;p=4298"},"modified":"2025-06-18T22:22:09","modified_gmt":"2025-06-18T22:22:09","slug":"create-a-sql-plan-baseline-in-oracle","status":"publish","type":"cposts","link":"https:\/\/w3buddy.com\/blog\/notes\/performance-tuning\/create-a-sql-plan-baseline-in-oracle\/","title":{"rendered":"Create a SQL Plan Baseline in Oracle"},"content":{"rendered":"\n<h2 class=\"wp-block-heading\">What is a SQL Plan Baseline and When Should You Use It?<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">A <strong>SQL Plan Baseline<\/strong> ensures Oracle sticks to a known, stable execution plan\u2014helping prevent performance regressions when the optimizer generates new plans.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>\ud83d\udccc When to Create a Baseline:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>You\u2019ve found a <strong>reliable and fast plan<\/strong>, especially for critical or high-load queries.<\/li>\n\n\n\n<li>The optimizer starts using <strong>unpredictable plans<\/strong> after changes like:\n<ul class=\"wp-block-list\">\n<li>Database upgrades<\/li>\n\n\n\n<li>Statistics gathering<\/li>\n\n\n\n<li>Schema modifications<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li>You need <strong>consistent performance<\/strong> across environments (Prod, QA, Dev).<\/li>\n\n\n\n<li>You&#8217;re planning a <strong>version or platform upgrade<\/strong> and want to lock in proven plans ahead of time.<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>\ud83d\udcdd Recommendation:<\/strong><br>Create a baseline <em>before<\/em> any major system change\u2014<strong>if<\/strong> the current plan works well. It\u2019s a smart way to avoid unexpected slowdowns later.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><mark style=\"background-color:#fff300\" class=\"has-inline-color\">Steps to Create a SQL Plan Baseline<\/mark><\/h2>\n\n\n\n<h2 class=\"wp-block-heading\">Step 1: Connect as SYSTEM<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">You need to be logged in as a privileged user (e.g., <code>SYSTEM<\/code>) to create and manage baselines.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code><code>SQL> SHOW USER;\nUSER is \"SYSTEM\"<\/code><\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Step 2: Identify Available Plans for the SQL_ID<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">This helps you see all execution plans used recently for a query, so you can pick the most efficient one.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SET LINESIZE 150\nSET PAGESIZE 50\nCOL sql_id FORMAT A15\nCOL plan_hash_value FORMAT 9999999999\nCOL last_snap FORMAT 99999\n--Get last snapshot ID for each plan of a given SQL_ID\nSELECT \n    sql_id, \n    plan_hash_value, \n    MAX(snap_id) AS last_snap\nFROM \n    dba_hist_sqlstat\nWHERE \n    sql_id = 'f14nzf4gyq4y4'  -- \ud83d\udd04 Replace with your target SQL_ID\nGROUP BY \n    sql_id, \n    plan_hash_value\nORDER BY \n    last_snap DESC;\n<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">Sample Output :<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code><code>SQL_ID         PLAN_HASH_VALUE    LAST_SNAP\n-------------  -----------------  ----------\nf14nzf4gyq4y4  2881287423         21662\nf14nzf4gyq4y4  1734569820         21660<\/code><\/code><\/pre>\n\n\n\n<blockquote class=\"wp-block-quote is-layout-flow wp-block-quote-is-layout-flow\">\n<p class=\"wp-block-paragraph\"><strong>Note:<\/strong> Each <code>plan_hash_value<\/code> represents a different execution plan. In later steps, you\u2019ll pick the one with best performance (<em>lowest elapsed time per execution<\/em>).<\/p>\n<\/blockquote>\n\n\n\n<h2 class=\"wp-block-heading\">Step 3: Review Plan Performance (Day-wise)<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">This gives you plan-level performance history using AWR data. You can see elapsed time, CPU usage, and buffer reads for each plan used over time.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code><code>CLEAR COLUMNS BREAKS COMPUTES\nSET LINESIZE 200\nSET PAGESIZE 1000\nCOL sql_id FOR A15 HEAD \"SQL|ID\"\nCOL s_time FOR A12 HEAD \"Snap|Time\"\nCOL executions FOR 999999999 HEAD \"Execs\"\nCOL elapsed_t FOR 99999999.99 HEAD \"Elapsed|Secs\"\nCOL cpu_t FOR 99999999.99 HEAD \"CPU|Secs\"\nCOL user_io FOR 99999999.99 HEAD \"User I\/O|Secs\"\nCOL buffer_gets FOR 999999999 HEAD \"Buffer|Gets\"\nCOL disk_r FOR 999999999 HEAD \"Disk|Reads\"\nCOL rows_processed FOR 999999999 HEAD \"Rows|Processed\"\nCOL sec_per_exec FOR 99999999.9999 HEAD \"Seconds|per Exec\"\nCOL plan_hash_value FOR A15 HEAD \"Plan|Hash\"\nBREAK ON sql_id ON plan_hash_value SKIP 1\nACCEPT 1_sqlid PROMPT 'Enter SQL ID: '\nACCEPT 1_days DEFAULT 42 PROMPT 'Enter days in the past (default 42): '\nSELECT \n    sql_id,\n    TO_CHAR(plan_hash_value) plan_hash_value,\n    TO_CHAR(begin_interval_time, 'DD-MON-YYYY') s_time,\n    SUM(executions_delta) executions,\n    ROUND(SUM(elapsed_time_delta) \/ 1e6, 2) elapsed_t,\n    ROUND(SUM(cpu_time_delta) \/ 1e6, 2) cpu_t,\n    ROUND(SUM(iowait_delta) \/ 1e6, 2) user_io,\n    ROUND(SUM(buffer_gets_delta)) buffer_gets,\n    ROUND(SUM(disk_reads_delta)) disk_r,\n    ROUND(SUM(rows_processed_delta)) rows_processed,\n    DECODE(SUM(executions_delta), 0, ROUND(SUM(elapsed_time_delta) \/ 1e6, 2),\n        ROUND(SUM(elapsed_time_delta)\/1e6\/SUM(executions_delta), 4)) sec_per_exec\nFROM \n    dba_hist_sqlstat a, dba_hist_snapshot b\nWHERE \n    a.snap_id = b.snap_id\n    AND a.instance_number = b.instance_number\n    AND a.sql_id = '&amp;&amp;1_sqlid'\n    AND TRUNC(begin_interval_time) >= TRUNC(SYSDATE - &amp;&amp;1_days)\nGROUP BY \n    sql_id, plan_hash_value, TO_CHAR(begin_interval_time, 'DD-MON-YYYY')\nORDER BY \n    TO_DATE(TO_CHAR(begin_interval_time, 'DD-MON-YYYY'), 'DD-MON-YYYY'),\n    plan_hash_value;<\/code><\/code><\/pre>\n\n\n\n<blockquote class=\"wp-block-quote is-layout-flow wp-block-quote-is-layout-flow\">\n<p class=\"wp-block-paragraph\"><strong>Note:<\/strong> This helps you choose the best plan for baseline creation. Focus on plans with consistently low <code>sec_per_exec<\/code>.<\/p>\n<\/blockquote>\n\n\n\n<h2 class=\"wp-block-heading\">Step\u202f4: Create and Load a SQL Tuning Set (STS)<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">An STS is a container for SQL statements and their performance data\u2014useful for tuning tasks. Here\u2019s how to decide what to do in different scenarios:<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">4.1 Check if an STS Already Exists<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code><code>SELECT owner, name\nFROM dba_sqlset\nWHERE owner = 'SYSTEM';<\/code><\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Note:<\/strong> If you already have an STS (e.g., <code>STS_SYSTEM_3<\/code>) with the SQL ID you want to baseline, you can <strong>reuse it<\/strong>\u2014no need to recreate.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">4.2 Drop the STS (Optional)<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>BEGIN\n  DBMS_SQLTUNE.DROP_SQLSET('STS_SYSTEM_3');  -- \ud83d\udd04 Replace if needed\nEND;\n\/<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>When to drop:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>If you&#8217;re sure the STS is old or you want a fresh start.<\/li>\n\n\n\n<li>Avoid dropping if it&#8217;s shared or part of scheduled tasks\u2014dropping an active STS can cause <code>ORA-13757<\/code> errors <\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">4.3 Create a New SQL Tuning Set<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>BEGIN\n  DBMS_SQLTUNE.CREATE_SQLSET(\n    sqlset_name  => 'STS_SYSTEM_3',  -- \ud83d\udd04 Choose a meaningful name\n    sqlset_owner => 'SYSTEM'         -- \ud83d\udd04 Typically your user\/schema\n  );\nEND;\n\/<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Why create new:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>If you are confused and not sure about existing SQL Tuning Set like can be dropped or not then it is always good to create new one for you to avoid any issue.<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">4.4 Load the plan into the tuning set<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>DECLARE\n  cur SYS_REFCURSOR;\nBEGIN\n  OPEN cur FOR\n    SELECT VALUE(a)\n    FROM TABLE(\n      DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(\n        begin_snap     => 21661,  -- \ud83d\udd04 Start snapshot ID\n        end_snap       => 21663,  -- \ud83d\udd04 End snapshot ID\n        basic_filter   => 'sql_id = ''f14nzf4gyq4y4''',  -- \ud83d\udd04 Target SQL_ID\n        attribute_list => 'ALL'\n      )\n    ) a;\n\n  DBMS_SQLTUNE.LOAD_SQLSET(\n    sqlset_name     => 'STS_SYSTEM_3',  -- \ud83d\udd04 Your SQL Tuning Set name\n    populate_cursor => cur\n  );\nEND;\n\/<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Step 5: Create the SQL Plan Baseline<\/h2>\n\n\n\n<pre class=\"wp-block-code\"><code>DECLARE\n  custom_plan PLS_INTEGER;\nBEGIN\n  custom_plan := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(\n    sqlset_name   => 'STS_SYSTEM_3',                -- \ud83d\udd04 Name of the SQL Tuning Set\n    basic_filter  => 'plan_hash_value = ''2881287423'''  -- \ud83d\udd04 Target Plan Hash Value\n  );\nEND;\n\/<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Step 6: Verify Created Baseline<\/h2>\n\n\n\n<pre class=\"wp-block-code\"><code>SET LINESIZE 180\nSET PAGESIZE 100\nSET UNDERLINE '-'\nCOLUMN signature      FORMAT A20   HEADING \"Signature\"\nCOLUMN sql_handle     FORMAT A30   HEADING \"SQL Handle\"\nCOLUMN plan_name      FORMAT A35   HEADING \"Plan Name\"\nCOLUMN created        FORMAT A20   HEADING \"Created (DT)\"\nCOLUMN enabled        FORMAT A6    HEADING \"Enabled\"\nCOLUMN accepted       FORMAT A8    HEADING \"Accepted\"\nCOLUMN fixed          FORMAT A5    HEADING \"Fixed\"\nCOLUMN reproduced     FORMAT A10   HEADING \"Reproduced\"\nWITH tmp AS (\n  SELECT exact_matching_signature signature\n  FROM v$sql\n  WHERE sql_id = 'f14nzf4gyq4y4' -- \ud83d\udd04 Replace with your SQL_ID\n)\nSELECT\n  TO_CHAR(a.signature)  AS signature,\n  sql_handle,\n  plan_name,\n  TO_CHAR(created, 'DD-MON-YYYY HH24:MI') AS created,\n  enabled,\n  accepted,\n  fixed,\n  reproduced\nFROM\n  dba_sql_plan_baselines a, tmp\nWHERE\n  a.signature = tmp.signature\nORDER BY\n  created;<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Step 7: Rename the Baseline (Optional but Recommended)<\/h2>\n\n\n\n<pre class=\"wp-block-code\"><code>DECLARE\n  custom_plan PLS_INTEGER;\nBEGIN\n  custom_plan := DBMS_SPM.ALTER_SQL_PLAN_BASELINE(\n    sql_handle      => 'SQL_abcdefgh1234567',          -- \ud83d\udd04 Replace with actual SQL handle\n    plan_name       => 'SQL_PLAN_abcdefgh1234567',     -- \ud83d\udd04 Replace with current baseline plan name\n    attribute_name  => 'PLAN_NAME',\n    attribute_value => 'SQL_PLAN_MANUAL_f14nzf4gyq4y4' -- \ud83d\udd04 Replace with new custom plan name\n  );\nEND;\n\/<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Step 8: Fix the Baseline (Make it Always Used \u2014 Optional)<\/h2>\n\n\n\n<pre class=\"wp-block-code\"><code>DECLARE\n  custom_plan PLS_INTEGER;\nBEGIN\n  custom_plan := DBMS_SPM.ALTER_SQL_PLAN_BASELINE(\n    sql_handle      => 'SQL_abcdefgh1234567',            -- \ud83d\udd04 Replace with actual SQL handle\n    plan_name       => 'SQL_PLAN_MANUAL_f14nzf4gyq4y4',  -- \ud83d\udd04 Replace with plan name to be fixed\n    attribute_name  => 'FIXED',\n    attribute_value => 'YES'\n  );\nEND;\n\/<\/code><\/pre>\n\n\n\n<blockquote class=\"wp-block-quote is-layout-flow wp-block-quote-is-layout-flow\">\n<p class=\"wp-block-paragraph\">\u26a0\ufe0f <strong>Note:<\/strong> Only fix the plan if you\u2019re sure it&#8217;s consistently optimal. Fixing prevents Oracle from evolving to better plans automatically.<\/p>\n<\/blockquote>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><mark style=\"background-color:#fff300\" class=\"has-inline-color\">Reference: Additional DBA Scripts (Not Part of Main Steps)<\/mark><\/h2>\n\n\n\n<blockquote class=\"wp-block-quote is-layout-flow wp-block-quote-is-layout-flow\">\n<p class=\"wp-block-paragraph\">These scripts are for <strong>DBA reference and post-baseline maintenance<\/strong>. They&#8217;re not part of the core baseline creation steps but are extremely helpful when you need to clean up, manage, or monitor baselines later.<\/p>\n<\/blockquote>\n\n\n\n<h3 class=\"wp-block-heading\">Drop a SQL Plan Baseline<\/h3>\n\n\n\n<blockquote class=\"wp-block-quote is-layout-flow wp-block-quote-is-layout-flow\">\n<p class=\"wp-block-paragraph\"><strong>When to Use:<\/strong> Use this if a baseline is no longer needed or created by mistake. This completely removes it from the system.<\/p>\n<\/blockquote>\n\n\n\n<pre class=\"wp-block-code\"><code>DECLARE\n  result PLS_INTEGER;\nBEGIN\n  result := DBMS_SPM.DROP_SQL_PLAN_BASELINE(\n    sql_handle => 'SQL_abcdefgh1234567',           -- \ud83d\udd04 Replace with actual SQL_HANDLE (from DBA_SQL_PLAN_BASELINES)\n    plan_name  => 'SQL_PLAN_MANUAL_f14nzf4gyq4y4'  -- \ud83d\udd04 Replace with actual PLAN_NAME to drop\n  );\n  DBMS_OUTPUT.PUT_LINE('Baseline dropped. Result = ' || result);\nEND;\n\/<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">Unfix a SQL Plan Baseline<\/h3>\n\n\n\n<blockquote class=\"wp-block-quote is-layout-flow wp-block-quote-is-layout-flow\">\n<p class=\"wp-block-paragraph\"><strong>When to Use:<\/strong> If a baseline is fixed and you want Oracle to consider better plans in the future, unfix it with this.<\/p>\n<\/blockquote>\n\n\n\n<pre class=\"wp-block-code\"><code>DECLARE\n  result PLS_INTEGER;\nBEGIN\n  result := DBMS_SPM.ALTER_SQL_PLAN_BASELINE(\n    sql_handle      => 'SQL_abcdefgh1234567',           -- \ud83d\udd04 Replace with actual SQL_HANDLE\n    plan_name       => 'SQL_PLAN_MANUAL_f14nzf4gyq4y4', -- \ud83d\udd04 Replace with actual PLAN_NAME\n    attribute_name  => 'FIXED',\n    attribute_value => 'NO'                             -- 'NO' means plan is no longer fixed (Oracle can evolve to better ones)\n  );\n  DBMS_OUTPUT.PUT_LINE('Baseline unfixed. Result = ' || result);\nEND;\n\/<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">View All Baselines for a SQL ID<\/h3>\n\n\n\n<blockquote class=\"wp-block-quote is-layout-flow wp-block-quote-is-layout-flow\">\n<p class=\"wp-block-paragraph\"><strong>When to Use:<\/strong> Use this to see all plan baselines associated with a SQL_ID \u2014 including their status (enabled, fixed, accepted).<\/p>\n<\/blockquote>\n\n\n\n<pre class=\"wp-block-code\"><code>SET LINESIZE 150\nSET PAGESIZE 100\nSET UNDERLINE '-'\nCOLUMN sql_handle   FORMAT A30   HEADING 'SQL Handle'\nCOLUMN plan_name    FORMAT A35   HEADING 'Plan Name'\nCOLUMN enabled      FORMAT A7    HEADING 'Enabled'\nCOLUMN accepted     FORMAT A8    HEADING 'Accepted'\nCOLUMN fixed        FORMAT A6    HEADING 'Fixed'\nCOLUMN reproduced   FORMAT A10   HEADING 'Reproduced'\nCOLUMN created      FORMAT A20   HEADING 'Created (DT)'\nSELECT \n  sql_handle, \n  plan_name, \n  enabled, \n  accepted, \n  fixed, \n  reproduced,\n  TO_CHAR(created, 'DD-MON-YYYY HH24:MI') AS created\nFROM \n  dba_sql_plan_baselines\nWHERE \n  signature IN (\n    SELECT exact_matching_signature \n    FROM v$sql \n    WHERE sql_id = 'f14nzf4gyq4y4'  -- \ud83d\udd04 Replace with your SQL_ID\n  );<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">Notes:<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li><code>enabled<\/code>: Indicates whether the plan is available for use.<\/li>\n\n\n\n<li><code>accepted<\/code>: Shows if the plan was accepted into the baseline.<\/li>\n\n\n\n<li><code>fixed<\/code>: Tells whether Oracle is forced to use this plan.<\/li>\n\n\n\n<li><code>reproduced<\/code>: If <code>NO<\/code>, it means the plan hasn&#8217;t yet been reproduced in the current system.<\/li>\n\n\n\n<li><code>created<\/code>: Useful to track how old or new the baseline is.<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">Clean Up SQL Tuning Set (STS)<\/h3>\n\n\n\n<blockquote class=\"wp-block-quote is-layout-flow wp-block-quote-is-layout-flow\">\n<p class=\"wp-block-paragraph\"><strong>When to Use:<\/strong> If you created a SQL Tuning Set temporarily (e.g., to load a plan), you can safely drop it afterward.<\/p>\n<\/blockquote>\n\n\n\n<pre class=\"wp-block-code\"><code><code>BEGIN\n  DBMS_SQLTUNE.DROP_SQLSET('STS_SYSTEM_3');  -- Replace with your STS name\nEND;\n\/<\/code><\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">Rename a SQL Plan Baseline (Optional)<\/h3>\n\n\n\n<blockquote class=\"wp-block-quote is-layout-flow wp-block-quote-is-layout-flow\">\n<p class=\"wp-block-paragraph\"><strong>When to Use:<\/strong> Useful for giving the plan a clearer, meaningful name (especially helpful when you have many baselines).<\/p>\n<\/blockquote>\n\n\n\n<pre class=\"wp-block-code\"><code>DECLARE\n  result PLS_INTEGER;\nBEGIN\n  result := DBMS_SPM.ALTER_SQL_PLAN_BASELINE(\n    sql_handle      => 'SQL_abcdefgh1234567',          -- \ud83d\udd04 Replace with actual SQL handle\n    plan_name       => 'SQL_PLAN_abcdefgh1234567',     -- \ud83d\udd04 Replace with current\/old plan name\n    attribute_name  => 'PLAN_NAME',\n    attribute_value => 'SQL_PLAN_MANUAL_f14nzf4gyq4y4' -- \ud83d\udd04 Replace with desired new plan name\n  );\nEND;\n\/<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">View Baseline Usage Statistics<\/h3>\n\n\n\n<blockquote class=\"wp-block-quote is-layout-flow wp-block-quote-is-layout-flow\">\n<p class=\"wp-block-paragraph\"><strong>When to Use:<\/strong> Check how often a baseline is used, whether it\u2019s enabled\/fixed, and when it was last executed.<\/p>\n<\/blockquote>\n\n\n\n<pre class=\"wp-block-code\"><code>SET LINESIZE 150\nSET PAGESIZE 50\nCOLUMN sql_handle     FORMAT A25  HEADING 'SQL Handle'\nCOLUMN plan_name      FORMAT A35  HEADING 'Plan Name'\nCOLUMN executions     FORMAT 999999 HEADING 'Executions'\nCOLUMN last_executed  FORMAT A20  HEADING 'Last Executed'\nCOLUMN enabled        FORMAT A7   HEADING 'Enabled'\nCOLUMN accepted       FORMAT A8   HEADING 'Accepted'\nCOLUMN fixed          FORMAT A5   HEADING 'Fixed'\nCOLUMN created        FORMAT A20  HEADING 'Created'\nSELECT \n  sql_handle,\n  plan_name,\n  executions,\n  TO_CHAR(last_executed, 'DD-MON-YYYY HH24:MI') AS last_executed,\n  enabled,\n  accepted,\n  fixed,\n  TO_CHAR(created, 'DD-MON-YYYY HH24:MI') AS created\nFROM \n  dba_sql_plan_baselines\nWHERE \n  sql_handle = 'SQL_abcdefgh1234567';  -- \ud83d\udd04 Replace with actual SQL handle<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">Notes:<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Use this to <strong>monitor plan usage<\/strong>, especially after implementing a baseline.<\/li>\n\n\n\n<li>If <code>executions<\/code> is 0, the plan hasn&#8217;t been used yet.<\/li>\n\n\n\n<li><code>last_executed<\/code> shows the most recent usage timestamp.<\/li>\n\n\n\n<li>Replace <code>'SQL_abcdefgh1234567'<\/code> with the <strong>actual <code>sql_handle<\/code><\/strong> from your environment (e.g., from <code>DBA_SQL_PLAN_BASELINES<\/code> or <code>DBA_HIST_SQLSTAT<\/code> joins).<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">Evolve Baselines Automatically (Accept Better Plans)<\/h3>\n\n\n\n<blockquote class=\"wp-block-quote is-layout-flow wp-block-quote-is-layout-flow\">\n<p class=\"wp-block-paragraph\"><strong>When to Use:<\/strong> Use this to let Oracle evaluate unaccepted plans and accept them if they perform better.<\/p>\n<\/blockquote>\n\n\n\n<pre class=\"wp-block-code\"><code>SET SERVEROUTPUT ON SIZE UNLIMITED\nDECLARE\n  report CLOB;\nBEGIN\n  report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(\n    sql_handle => 'SQL_abcdefgh1234567',  -- \ud83d\udd04 Replace with your actual SQL handle\n    plan_name  => NULL,                   -- NULL = evolve all unaccepted plans for this handle\n    time_limit => 60,                     -- Time limit (in seconds) for plan evolution\n    verify     => 'YES',                  -- Compare plans based on performance\n    commit     => 'YES'                   -- Automatically accept better-performing plans\n  );\n  -- Print evolution report\n  DBMS_OUTPUT.PUT_LINE(report);\nEND;\n\/<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">Notes:<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Replace <code>'SQL_abcdefgh1234567'<\/code><\/strong> with your actual SQL handle (from <code>DBA_SQL_PLAN_BASELINES<\/code>).<\/li>\n\n\n\n<li>If <code>plan_name<\/code> is <code>NULL<\/code>, all unaccepted plans under that <code>sql_handle<\/code> will be evaluated.<\/li>\n\n\n\n<li>Useful after <strong>loading plans<\/strong> from AWR or SQL tuning set to determine if they should be accepted.<\/li>\n\n\n\n<li><code>verify => 'YES'<\/code> ensures Oracle <strong>compares plan performance<\/strong> before accepting.<\/li>\n\n\n\n<li><code>commit => 'YES'<\/code> applies accepted plans automatically \u2014 set to <code>'NO'<\/code> if you want to <strong>review manually first<\/strong>.<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">Fix a Plan (Make It Always Preferred)<\/h3>\n\n\n\n<blockquote class=\"wp-block-quote is-layout-flow wp-block-quote-is-layout-flow\">\n<p class=\"wp-block-paragraph\"><strong>When to Use:<\/strong> After verifying that a plan is consistently good, you can fix it so Oracle always uses it.<\/p>\n<\/blockquote>\n\n\n\n<pre class=\"wp-block-code\"><code>DECLARE\n  result PLS_INTEGER;\nBEGIN\n  result := DBMS_SPM.ALTER_SQL_PLAN_BASELINE(\n    sql_handle      => 'SQL_abcdefgh1234567',           -- \ud83d\udd04 Replace with actual SQL handle\n    plan_name       => 'SQL_PLAN_MANUAL_f14nzf4gyq4y4', -- \ud83d\udd04 Replace with actual plan name\n    attribute_name  => 'FIXED',\n    attribute_value => 'YES'\n  );\nEND;\n\/<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">Notes:<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>\ud83d\udd04 Replace placeholders<\/strong>:\n<ul class=\"wp-block-list\">\n<li><code>'SQL_abcdefgh1234567'<\/code>: Get from <code>DBA_SQL_PLAN_BASELINES.SQL_HANDLE<\/code><\/li>\n\n\n\n<li><code>'SQL_PLAN_MANUAL_f14nzf4gyq4y4'<\/code>: Use the actual plan name you want to fix<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li>Use <strong><code>FIXED => 'YES'<\/code><\/strong> <strong>only<\/strong> after you&#8217;re confident the plan is stable and optimal.<\/li>\n\n\n\n<li>A fixed plan can prevent Oracle from adapting to better plans unless you evolve or unfix it later.<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">Add a Description (Good Practice for Documentation)<\/h3>\n\n\n\n<blockquote class=\"wp-block-quote is-layout-flow wp-block-quote-is-layout-flow\">\n<p class=\"wp-block-paragraph\"><strong>When to Use:<\/strong> Optional, but helps others understand why a baseline was created or modified.<\/p>\n<\/blockquote>\n\n\n\n<pre class=\"wp-block-code\"><code>DECLARE\n  result PLS_INTEGER;\nBEGIN\n  result := DBMS_SPM.ALTER_SQL_PLAN_BASELINE(\n    sql_handle      => 'SQL_abcdefgh1234567',               -- \ud83d\udd04 Replace with actual SQL handle\n    plan_name       => 'SQL_PLAN_MANUAL_f14nzf4gyq4y4',     -- \ud83d\udd04 Replace with actual plan name\n    attribute_name  => 'DESCRIPTION',\n    attribute_value => 'Manual baseline for report query - 18-JUN-2025'  -- \ud83d\udd04 Customize description as needed\n  );\nEND;\n\/<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"<p>What is a SQL Plan Baseline and When Should You Use It? A SQL Plan Baseline ensures Oracle sticks to a known, stable execution plan\u2014helping prevent performance regressions when the optimizer generates new plans. \ud83d\udccc When to Create a Baseline: \ud83d\udcdd Recommendation:Create a baseline before any major system change\u2014if the current plan works well. It\u2019s [&hellip;]<\/p>\n","protected":false},"template":"","meta":{"googlesitekit_rrm_CAowu461DA:productID":""},"categories":[952,992],"class_list":["post-4298","cposts","type-cposts","status-publish","hentry","category-notes","category-performance-tuning"],"_links":{"self":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/cposts\/4298","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/cposts"}],"about":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/types\/cposts"}],"wp:attachment":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/media?parent=4298"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/categories?post=4298"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}