Scribd
Upload
35 views

Master SQL PROFILE for Oracle DBA

Uploaded by

muhammathrinas722
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
35 views

Master SQL PROFILE for Oracle DBA

Uploaded by

muhammathrinas722
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 7

SQL Profile: Detailed Explanation, Advantages, Use Case, and

Workaround

1. What is an SQL Profile?

An SQL Profile in Oracle is a set of auxiliary information collected by the SQL Tuning
Advisor to improve the execution plan of a SQL statement without changing the code.
Unlike optimizer hints or plan baselines, it influences the query optimizer's decision-
making process without hard-coding the plan.

Advantages of SQL Profiles

• Non-intrusive: Does not require SQL code changes.


• Dynamic adjustments: Helps adapt execution plans as data and statistics evolve.
• Improved performance: Reduces the execution time of poorly performing queries.
• Temporary Fix: Serves as a quick performance improvement mechanism until the
root cause of the issue is resolved.
• Granularity: Targets specific SQL queries rather than applying global changes to
the database.

Use Cases for SQL Profiles

1. Performance Bottlenecks: To improve poorly performing SQL queries identified in


AWR, ADDM, or ASH reports.
2. Complex Joins: For queries with complex joins where the optimizer struggles to
find the best execution plan.
3. Legacy Applications: For queries in applications where you cannot modify SQL
statements directly.
4. Dynamic Plans: Helps when execution plans vary significantly due to incorrect or
incomplete statistics.

Scenario-Based Example: Using SQL Profile

Problem: A query performing a full table scan instead of using an index due to outdated
statistics.
Steps to Address Using SQL Profile:

1. Identify the SQL ID of the problematic query from AWR or ASH reports.
2. Run the SQL Tuning Advisor for the SQL ID using DBMS_SQLTUNE.
3. Accept the recommended SQL Profile generated by the advisor.

Example Steps:

sql

-- 1. Identify SQL ID (example: 'abcd1234xyz')


SELECT sql_text, sql_id FROM v$sql WHERE sql_text LIKE '%YOUR_QUERY%';

-- 2. Run SQL Tuning Advisor


DECLARE
l_sql_tune_task VARCHAR2(100);
BEGIN
l_sql_tune_task := DBMS_SQLTUNE.create_tuning_task(
sql_id => 'abcd1234xyz',
scope => 'COMPREHENSIVE',
time_limit => 60,
task_name => 'sql_tuning_task1',
description => 'Tuning task for SQL ID abcd1234xyz');
DBMS_SQLTUNE.execute_tuning_task(task_name => l_sql_tune_task);
END;
/

-- 3. View Recommendations
SELECT DBMS_SQLTUNE.report_tuning_task('sql_tuning_task1') FROM DUAL;

-- 4. Accept the SQL Profile


BEGIN
DBMS_SQLTUNE.accept_sql_profile(
task_name => 'sql_tuning_task1',
name => 'sql_profile_abcd1234xyz');
END;
/

How to Pin SQL Profile (Prebuilt Script Example)

Pinning an SQL Profile ensures that a specific plan remains consistent for a given query.
Steps to Pin the SQL Profile:

1. Extract the baseline or profile details.


2. Convert the plan into a SQL Profile.
3. Use scripts to force the optimizer to choose the desired plan.
sql

-- 1. Query the SQL Profile Details


SELECT profile, sql_text, category FROM dba_sql_profiles;

-- 2. Forcing the SQL Profile with SQL Plan Management (SPM)


DECLARE
l_plans PLS_INTEGER;
BEGIN
l_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(
sqlset_name => 'your_sqlset',
sql_id => 'abcd1234xyz',
plan_hash_value => '1234567890');
END;
/

-- 3. Alter to Pin the Plan


EXEC DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
sql_handle => 'SQL_123456789abcdef',
plan_name => 'your_plan_name',
attribute_name => 'enabled',
attribute_value => 'YES');

End-to-End Steps to Resolve Performance Issues in Oracle 19c Database

1. Understand the Problem:


a. Collect SQL query execution plans using EXPLAIN PLAN or DBMS_XPLAN.
b. Gather performance data using AWR/ASH/ADDM reports.
2. Analyze Execution Plans:
a. Identify high-cost operations like full table scans or large sorts.
b. Check whether indexes are being used effectively.
3. SQL Tuning Advisor:
a. Use SQL Tuning Advisor to identify and recommend fixes such as SQL
Profiles or statistics collection.
4. Gather/Refresh Statistics:
a. Ensure table and index statistics are up to date using:
sql

EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA', 'TABLE_NAME');

5. Create/Apply Indexes:
a. If recommended, create indexes to improve query performance.
6. Use SQL Plan Baselines:
a. If the execution plan keeps changing, create and use SQL Plan Baselines.
7. Create SQL Profiles:
a. Follow the steps mentioned above to apply SQL Profiles for specific queries.
8. Monitor Results:
a. After applying fixes, monitor the query performance again using AWR
snapshots or SQL tracing.

Workaround When SQL Profiles Do Not Work

• Use SQL Plan Management (SPM) to create baselines.


• Rewrite the query to simplify joins, subqueries, or other complex constructs.
• Use optimizer hints to enforce specific execution paths.
• Rebuild objects (indexes, partitions) if they are highly fragmented.

Questions Related to SQL Profiles

1. What is an SQL Profile, and how does it differ from SQL Plan Baselines?
2. Explain a real-time scenario where you used an SQL Profile to resolve a
performance issue.
3. Can SQL Profiles force the optimizer to use a specific plan? Why or why not?
4. How do you handle SQL statements with fluctuating performance without modifying
the code?
5. What tools and views in Oracle help identify poorly performing SQL queries?
6. Describe how to monitor the impact of an SQL Profile once applied.
7. What are the challenges of using SQL Profiles in a production environment?

Real-Time Notes for a Senior DBA

• Always back up SQL Tuning Sets (STS) and profiles before modifying them.
• Use DBMS_SQLDIAG to troubleshoot when SQL Profiles are ignored.
• Continuously monitor the database after applying fixes to ensure no regression
occurs.
• Regularly review AWR/ASH reports to identify new bottlenecks.
*************************************************************************************

To create and pin a SQL profile for optimizing execution plans in Oracle 19c, you can follow
these steps using the coe_xfr_sql_profile.sql script. This script is part of Oracle's SQLT
package, often used for transferring and enforcing optimal execution plans between
environments or within the same database.

Step-by-Step Process:

1. Identify the SQL ID and Plan Hash Value (PHV):


a. Locate the problematic query's SQL ID using the v$sql or v$sqlarea views.
b. Use DBA_HIST_SQLSTAT or DBMS_XPLAN.DISPLAY_AWR to identify the Plan
Hash Value associated with the optimal plan.
sql

SELECT sql_id, plan_hash_value


FROM v$sql
WHERE sql_text LIKE '%your_query%';

2. Run the coe_xfr_sql_profile.sql Script:


a. Download the coe_xfr_sql_profile.sql script from Oracle Support or find it in
your SQLT package.
b. Execute the script in SQL*Plus as SYSDBA. Provide the SQL ID and the Plan
Hash Value when prompted.
bash

sqlplus / as sysdba
SQL> @coe_xfr_sql_profile.sql

The script will create another SQL file, named something like
coe_xfr_sql_profile_<SQL_ID>_<PHV>.sql.

3. Modify the Generated Script (Optional):


a. Open the generated script file and set force_match => TRUE if you want to
apply the profile to similar SQLs with different literals (useful for queries with
bind variables).
4. Apply the SQL Profile on the Problematic Database:
a. Run the generated script in the target database to create the SQL profile.
bash
sqlplus / as sysdba
SQL> @coe_xfr_sql_profile_<SQL_ID>_<PHV>.sql

5. Verify the SQL Profile:


a. Check if the profile has been created and if it is being used.
sql

SELECT name, status


FROM dba_sql_profiles;

SELECT sql_id, sql_profile


FROM v$sql
WHERE sql_id = 'your_sql_id';

Use Cases:

• Fix execution plan changes caused by bind variable peeking.


• Force a stable plan for queries that behave unpredictably under changing
conditions.
• Transfer an optimal plan from a test environment to production.

Advantages:

• Non-invasive and reversible compared to stored outlines or hints.


• Can be applied dynamically without restarting the database.

Key Considerations:

• Always test the changes in a non-production environment.


• Monitor the query's performance after applying the profile to ensure desired results.

To use the coe_xfr_sql_profile.sql script for managing SQL Profiles and pinning the
best execution plan, the following My Oracle Support (MOS) documents will guide you with
the steps and details:

1. Document 1955195.1 - Encouraging the CBO to Use a Better Execution Plan


Using the COE_XFR_SQL_PROFILE Script

2. This document explains how to use the coe_xfr_sql_profile.sql script to


generate a SQL Profile based on an optimal execution plan. It guides you on
leveraging the script to enforce a preferred plan when there are performance
issues. This requires identifying the SQL_ID and PLAN_HASH_VALUE from memory or
AWR and applying the generated profile to stabilize the desired execution plan.
Access this document.

3. Document 1400903.1 - Using COE_LOAD_SQL_PROFILE for Custom Profiles

This document covers how coe_xfr_sql_profile.sql works alongside other SQLT


scripts, explaining licensing requirements and best practices for creating and applying SQL
Profiles. It also provides information on how to modify and use the generated profile across
different systems. Learn more here.

4. Document 215187.1 - SQLT (SQLTXPLAIN) Tool

This is the foundational document for downloading and setting up SQLT, which includes
the coe_xfr_sql_profile.sql script. It provides step-by-step instructions to use the
SQLT tool for diagnosing SQL performance issues and managing profiles. Explore this here.

Steps Summary:

1. Download SQLT: Refer to Document 215187.1 to get the SQLT tool and locate the
coe_xfr_sql_profile.sql script in the /sqlt/utl directory.
2. Identify SQL ID and Plan Hash Value: Obtain these from the shared pool or AWR
using views like V$SQL or DBA_HIST_SQL_PLAN.
3. Run the Script: Execute coe_xfr_sql_profile.sql to extract the profile. This
will generate a custom script
(coe_xfr_sql_profile_<SQL_ID>_<PLAN_HASH_VALUE>.sql).
4. Apply the Profile: Run the generated script on the target database to enforce the
desired plan.
5. Verify: Use views such as DBA_SQL_PROFILES to confirm the application and
monitor performance improvements.

Licensing Note: Using the SQLT tool and the related scripts requires an Oracle Tuning Pack
license if you rely on the Diagnostic Pack or AWR data.

These documents provide further clarity and specific examples to ensure you can resolve
performance issues effectively.

You might also like