SQL Profile

By

 

Creating a SQL Profile in Oracle is a common method used to influence the optimizer to choose a better execution plan without changing the SQL text. A SQL Profile contains auxiliary information (like corrected cardinality estimates) to help the optimizer make better decisions.

✅ Steps to Create a SQL Profile in Oracle

There are three primary methods to create a SQL Profile:

Method 1: Using SQL Tuning Advisor (DBMS_SQLTUNE)

This is the most common and recommended way.

Step 1: Identify the SQL ID

You can find the SQL ID from AWR or V$SQL:

SELECT sql_id, sql_text

FROM v$sql

WHERE sql_text LIKE '%your_query_here%';

Step 2: Create a Tuning Task

BEGIN

  DBMS_SQLTUNE.create_tuning_task (

    sql_id       => 'abcd1234',     -- Your SQL_ID

    scope        => 'COMPREHENSIVE',

    time_limit   => 300,

    task_name    => 'tune_sql_1',

    description  => 'Tuning task for SQL_ID abcd1234');

END;

/

Step 3: Execute the Tuning Task

BEGIN

  DBMS_SQLTUNE.execute_tuning_task(task_name => 'tune_sql_1');

END;

/

Step 4: Review Recommendations

SELECT DBMS_SQLTUNE.report_tuning_task('tune_sql_1') FROM dual;

If it recommends a SQL Profile, you can accept it.

Step 5: Accept SQL Profile

BEGIN

  DBMS_SQLTUNE.accept_sql_profile (

    task_name => 'tune_sql_1',

    name      => 'sql_profile_abcd1234');

END;

/

🔹 The SQL Profile is now active and will influence the optimizer.

Method 2: Manual SQL Profile Creation (Using Hints)

If you know the right hints (from a better plan), you can create a SQL Profile manually.

Example:

DECLARE

  l_hints CLOB;

BEGIN

  l_hints := q'[

    BEGIN_OUTLINE_DATA

      INDEX(@"SEL$1" "EMP"@"SEL$1" ("EMP"."EMP_ID"))

    END_OUTLINE_DATA

  ]';

 

  DBMS_SQLTUNE.import_sql_profile(

    sql_text    => q'[SELECT * FROM emp WHERE emp_id = :1]',

    profile     => SQLPROF_ATTR(

                    name       => 'manual_profile_emp',

                    category   => 'DEFAULT',

                    description=> 'Manual Profile with INDEX hint',

                    sql_text   => q'[SELECT * FROM emp WHERE emp_id = :1]',

                    signature  => NULL,

                    type       => DBMS_SQLTUNE.SQL_PROFILE_TYPE_MANUAL,

                    hints      => l_hints));

END;

/

Method 3: Using coe_xfr_sql_profile.sql (Manual Fix Plan from Plan Baseline)

Oracle provides a script $ORACLE_HOME/rdbms/admin/coe_xfr_sql_profile.sql to create SQL Profile from a known plan.

Steps:

  1. Identify SQL_ID and PLAN_HASH_VALUE of the desired plan.
  2. Run the script:

@?/rdbms/admin/coe_xfr_sql_profile.sql

  1. It will prompt for SQL_ID and PLAN_HASH_VALUE.
  2. It generates another script coe_xfr_sql_profile_<SQL_ID>_<PLAN_HASH>.sql.
  3. Run the generated script to create the profile.

✅ Verify SQL Profile

SELECT name, status, sql_text

FROM dba_sql_profiles

WHERE name LIKE '%your_profile%';

🚫 Drop SQL Profile (if needed)

BEGIN

  DBMS_SQLTUNE.drop_sql_profile(name => 'sql_profile_abcd1234');

END;

/

Great! Here's a complete step-by-step example of identifying a slow query, using SQL Tuning Advisor to create a SQL Profile, and observing the impact on the execution plan.

🎯 Scenario

We have a query on the HR.EMPLOYEES table that is performing poorly. We'll tune it using a SQL Profile.

🔍 Step 1: Identify the Slow Query

Assume this query is running slow:

SELECT * FROM employees WHERE department_id = 50;

Let’s simulate it being slow by assuming the optimizer uses a full table scan.

Check the SQL in V$SQL:

SELECT sql_id, plan_hash_value, executions, elapsed_time, sql_text

FROM v$sql

WHERE sql_text LIKE 'SELECT * FROM employees WHERE department_id = 50%';

Suppose the SQL_ID returned is f7cb8gq1z7xmu.

🛠️ Step 2: Create Tuning Task

BEGIN

  DBMS_SQLTUNE.create_tuning_task (

    sql_id       => 'f7cb8gq1z7xmu',

    scope        => 'COMPREHENSIVE',

    time_limit   => 60,

    task_name    => 'tune_emp_query',

    description  => 'Tuning task for employees query');

END;

/

▶️ Step 3: Execute Tuning Task

BEGIN

  DBMS_SQLTUNE.execute_tuning_task(task_name => 'tune_emp_query');

END;

/

📄 Step 4: View Recommendations

SELECT DBMS_SQLTUNE.report_tuning_task('tune_emp_query') FROM dual;

You’ll see output like:

Recommendation (estimated benefit: 98%)

---------------------------------------

 - Consider accepting the recommended SQL profile.

 - It may enable the optimizer to choose a better plan.

 

SQL Profile Name: SYS_SQLPROF_0123456789abcdef

✅ Step 5: Accept the SQL Profile

BEGIN

  DBMS_SQLTUNE.accept_sql_profile (

    task_name => 'tune_emp_query',

    name      => 'emp_query_profile');

END;

/

Now Oracle will apply this SQL Profile the next time the query is executed.

🔁 Step 6: Re-run the Query and Check the Plan

SELECT * FROM employees WHERE department_id = 50;

Then check the new execution plan:

SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));

Compare the new plan with the previous one from V$SQL_PLAN:

SELECT * FROM v$sql_plan WHERE sql_id = 'f7cb8gq1z7xmu' ORDER BY id;

You should see that the new plan is more efficient (e.g., uses an index instead of a full table scan).

📌 Optional: Check SQL Profile is Applied

SELECT *

FROM dba_sql_profiles

WHERE name = 'emp_query_profile';

❌ Optional: Drop the SQL Profile

BEGIN

  DBMS_SQLTUNE.drop_sql_profile(name => 'emp_query_profile');

END;

/

🧠 Summary

Step

Action

1

Identify slow query via SQL ID

2

Create tuning task via DBMS_SQLTUNE

3

Execute and analyze recommendations

4

Accept SQL Profile if beneficial

5

Re-run query and compare execution plan

Here’s a complete example using Oracle's coe_xfr_sql_profile.sql script to manually create a SQL Profile for a specific plan. This method is often used to "fix" a known good plan when a query suddenly starts using a bad one.

🧭 Scenario

You observed that a query was running well with PLAN_HASH_VALUE = 1234567890, but now it uses a new slower plan. You want to fix the plan using the good one.

🧾 Step 1: Find SQL_ID and PLAN_HASH_VALUE

Get SQL_IDs and plans from AWR or V$SQL:

SELECT sql_id, plan_hash_value, executions, elapsed_time, sql_text

FROM dba_hist_sqlstat

WHERE sql_text LIKE 'SELECT * FROM employees WHERE department_id = 50%'

ORDER BY last_active_time DESC;

Assume:

  • SQL_ID = f7cb8gq1z7xmu
  • Good PLAN_HASH_VALUE = 1234567890

🧰 Step 2: Run coe_xfr_sql_profile.sql Script

This script is located at:

$ORACLE_HOME/rdbms/admin/coe_xfr_sql_profile.sql

On SQL*Plus:

@?/rdbms/admin/coe_xfr_sql_profile.sql

It will prompt:

Enter value for sql_id: f7cb8gq1z7xmu

Enter value for plan_hash_value: 1234567890

It generates a script like:

coe_xfr_sql_profile_f7cb8gq1z7xmu_1234567890.sql

📜 Step 3: Run the Generated Script

@coe_xfr_sql_profile_f7cb8gq1z7xmu_1234567890.sql

This script does the following:

  • Extracts outline hints from the desired plan
  • Creates a SQL Profile using those hints
  • Registers the profile with Oracle Optimizer

Example output inside the script:

BEGIN

  DBMS_SQLTUNE.import_sql_profile(

    sql_text    => :sql_text,

    profile     => sqlprof_attr(

                     name  => 'coe_f7cb8gq1z7xmu_1234567890',

                     category => 'DEFAULT'),

    replace     => TRUE);

END;

/

✅ Step 4: Verify Profile is in Use

SELECT name, sql_text

FROM dba_sql_profiles

WHERE name LIKE '%f7cb8gq1z7xmu%';

Check if it’s influencing the plan:

SELECT *

FROM table(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ADVANCED'));

❌ Optional: Drop the Profile

BEGIN

  DBMS_SQLTUNE.drop_sql_profile(name => 'coe_f7cb8gq1z7xmu_1234567890');

END;

/

📌 Summary: Why Use coe_xfr_sql_profile.sql?

✅ Benefit

🔍 Description

Fix plan regressions

Lock in a good plan manually

No code change needed

No hint injection or SQL rewrite

Reversible

Easily drop profile if not needed

Useful post-upgrade

Resolve performance issues after upgrades or stats changes

🧾 Sample Generated Script: coe_xfr_sql_profile_f7cb8gq1z7xmu_1234567890.sql

-- coe_xfr_sql_profile_f7cb8gq1z7xmu_1234567890.sql

-- SQL Profile generated by coe_xfr_sql_profile.sql

 

-- SQL_ID  : f7cb8gq1z7xmu

-- PLAN_HASH_VALUE : 1234567890

 

DEFINE sql_text =

'SELECT * FROM employees WHERE department_id = 50';

 

VARIABLE signature NUMBER;

VARIABLE profile_name VARCHAR2(30);

 

BEGIN

  :signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(:sql_text);

END;

/

 

-- Display the signature

PRINT signature;

 

-- Create SQL Profile using outline data from PLAN_HASH_VALUE 1234567890

BEGIN

  DBMS_SQLTUNE.import_sql_profile (

    sql_text    => :sql_text,

    profile     => sqlprof_attr(

                     name        => 'coe_f7cb8gq1z7xmu_1234567890',

                     category    => 'DEFAULT',

                     description => 'Manual profile using coe_xfr_sql_profile.sql',

                     signature   => :signature,

                     type        => DBMS_SQLTUNE.SQL_PROFILE_TYPE_MANUAL,

                     hints       =>

                       q'[

                         BEGIN_OUTLINE_DATA

                           USE_HASH(@"SEL$1" "EMPLOYEES"@"SEL$1")

                           FULL(@"SEL$1" "EMPLOYEES"@"SEL$1")

                           OUTLINE_LEAF(@"SEL$1")

                           ALL_ROWS

                           OPT_PARAM('_optimizer_cost_model' 'cpu')

                           DB_VERSION('19.0.0')

                         END_OUTLINE_DATA

                       ]'

                   ),

    replace     => TRUE

  );

END;

/

 

-- Confirm it's created

SELECT name, status FROM dba_sql_profiles

WHERE name = 'coe_f7cb8gq1z7xmu_1234567890';

🔍 Breakdown of Key Components

Part

Meaning

sql_text

Original query text to which the profile applies

signature

A unique identifier to match SQL text to the profile

hints

Outlines the optimizer directives from the desired plan

name

Name of the SQL Profile being created

DBMS_SQLTUNE.import_sql_profile

Registers the profile with the optimizer

🛑 Before Running the Script

Make sure:

  • You’re using the exact SQL text (spacing and casing matter!)
  • You have appropriate privileges (ADMINISTER SQL TUNING SET, etc.)

 


No comments:

Post a Comment