when query performance is poor and you need to understand the optimizer’s choice of an execution plan. Listed next are features you can use to guide the decision path the optimizer uses when selecting an execution plan:
• Initialization parameters
• Statistics
• Hints
• SQL profiles
• SQL plan management (plan baselines)
• Adaptive SQL plan management (12c)
• Adaptive query optimization (12c)
how the optimizer chooses between the low-cost plan and a plan baseline, consider the general steps taken when formulating an execution plan:
1. The optimizer first considers initialization parameters, hints, and SQL profiles when
choosing the lowest-cost plan. If using Oracle Database 12c, the optimizer will also factor
in adaptive query optimization features.
2. Regardless of the plan arrived at in step 1, if a plan baseline exists for the query, the optimizer
will choose the lowest-cost accepted plan from the plan baseline. Additionally, the optimizer
will give preference to accepted plans that have a fixed state in the plan baseline.
3. If the accepted plans in the plan baseline are not reproducible (say an index has been dropped that all of the plan baseline plans depend on), then the optimizer chooses the lowest-cost plan generated in step 1. Lowest cost in this situation means using the least amount of database resources such as CPU, I/O, and memory.
4. If a plan baseline exists for a query, and if the low-cost plan (from step 1) has a lower cost than the plan from the plan baseline, then the low-cost plan is automatically added to the plan history for the query in an unaccepted state. You can choose to move plans from the plan history into the plan baseline so that the optimizer will consider them when choosing an execution plan. This provides you the flexibility to use better plans as they become available (evolving the plan).
5. If using a plan baseline for a query, in Oracle 12c adaptive SQL plan management is enabled by default. In this mode, if a significantly more cost-efficient plan is generated by the optimizer, then the execution plan will automatically be added to the plan baseline. Features such as initialization parameters and hints don’t require an extra license and are available with all editions of the Oracle database. Other features such as SQL profiles require an extra license and ship only with the.
1.SQL PROFILE
SQL profile is associated with a SQL statement and contains adjustments to statistics that help the optimizer generate a more efficient execution plan. The SQL Tuning Advisor recommends and generates the code required to create and accept a SQL profile
Note Since a SQL profile is associated with a specific SQL statement (and not a user), there are no ALL- or USER-level views associated with SQL profiles. Having said that, keep in mind that it is possible for two SQL statements to have the same signature, yet be based on tables that exist in separate schemas.
CREATE PROFILE statement.
1.1 How To Creating and Accepting a SQL Profile
1.2 How To Determining if a Query is Using a SQL Profile
1.3 How To Automatically Accepting SQL Profiles
1.4 How To Displaying SQL Profile Information
1.5 How To Selectively Testing a SQL Profile
1.6 How To Transporting a SQL Profile to a Different Database
1.7 How To Disabling a SQL Profile
1.8 How to Enable a SQL Profile
1.9 How To Dropping a SQL Profile
1.1 How To Creating and Accepting a SQL Profile
You have a poorly performing query, and you want to get advice from the SQL Tuning Advisor. If the SQL Tuning Advisor recommends a SQL profile, then you want to accept the SQL profile (for the poorly performing query).
Run the SQL Tuning Advisor for the problem query. Keep in mind that the SQL Tuning Advisor may or may not recommend a SQL profile as a solution for performance issues. To run the SQL Tuning Advisor manually, perform the following steps:
Step 1: Use DBMS_SQLTUNE to Create a Tuning Task
The first step is to create a tuning task that is associated with the problem SQL statement. In the following code, the SQL text is hard-coded as input to the tune_sql variable:
DECLARE
tune_sql CLOB;
tune_task VARCHAR2(30);
BEGIN
tune_sql := 'select count(*) from emp';
tune_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text => tune_sql
,user_name => 'MV_MAINT'
,scope => 'COMPREHENSIVE'
,time_limit => 60
,task_name => 'TUNE1'
,description => 'Calling SQL Tuning Advisor for one statement'
);
END;
/
The prior code is placed in a file named sqltune.sql and executed as follows:
SQL> @sqltune.sql
Step 2: Execute the Tuning Task
This step runs the SQL Tuning Advisor to generate advice regarding any queries associated with the tuning task (created in step 1):
SQL> exec dbms_sqltune.execute_tuning_task(task_name=>'TUNE1');
Step 3: Run Tuning Advice Report
Now use DBMS_SQLTUNE to extract any tuning advice generated in step 2:
set long 10000
set longchunksize 10000
set lines 132
set pages 200
select dbms_sqltune.report_tuning_task('TUNE1') from dual;
For this example, the SQL Tuning Advisor recommends creating a SQL profile. Here is a snippet from the output that contains the recommendation and the code required to create the SQL profile:
Recommendation (estimated benefit: 86.11%)
------------------------------------------
- Consider accepting the recommended SQL profile to use parallel execution
for this statement.
execute dbms_sqltune.accept_sql_profile(task_name => 'TUNE1', task_owner
=> 'SYS', replace => TRUE, profile_type =>
DBMS_SQLTUNE.PX_PROFILE);
-------------------------------------------
Executing this query parallel with DOP 8 will improve its response time
86.11% over the original plan. However, there is some cost in enabling
parallel execution...
Step 4: Create and Accept the SQL Profile
To create the SQL profile, you need to run the code recommended by the SQL Tuning Advisor (from step 3)—for
example:
begin
-- This is the code from the SQL Tuning Advisor
dbms_sqltune.accept_sql_profile(
task_name => 'TUNE1',
task_owner => 'SYS',
replace => TRUE,
profile_type => DBMS_SQLTUNE.PX_PROFILE);
--
end;
/
When the prior code is run, it creates and enables the SQL profile. Now whenever the associated SQL query is executed, the SQL profile will be considered by the optimizer when formulating an execution plan.
If you need to later drop the tuning task, you can use the DBMS_SQLTUNE.DROP_TUNING_TASK procedure. Here’s an example of dropping the tuning task:
SQL> exec DBMS_SQLTUNE.DROP_TUNING_TASK(task_name=>'TUNE1');
The only Oracle-supported method for creating a SQL profile is to run the SQL Tuning Advisor and, if recommended, create a SQL profile using the Tuning Advisor’s output. In other words, the SQL Tuning Advisor determines if a SQL profile will help and, if so, generates the code required to create a SQL profile for a given query. Keep in mind that a SQL profile is not a silver bullet for improving query performance. It may be that the SQL Tuning Advisor recommends a SQL profile for a query and it does nothing to improve performance. We recommend that you observe the performance characteristics of the query before and after the SQL profile has been created.
If there is a significant performance gain, then keep the SQL profile in place, otherwise drop it (see Recipe 12-8 for details on how to drop a SQL profile). The “Solution” section detailed how to manually run the SQL Tuning Advisor. Keep in mind that with Oracle Database 11g and higher, this tuning task job automatically runs on a regularly scheduled basis. The automatic tuning task will oftentimes recommend the application of a SQL profile for a poorly performing query. See Chapter 11 for details on automatic SQL tuning features. You can review the output of the automatic tuning job via this query:
SQL> SELECT DBMS_AUTO_SQLTUNE.REPORT_AUTO_TUNING_TASK FROM DUAL;
We recommend that you review the output of the automatic tuning job on a regular basis. If a SQL profile is
recommended then consider creating it with the code provided in the output from the tuning task.
1.2 How To Determining if a Query is Using a SQL Profile
You’ve created a SQL profile for a query and wonder if the SQL profile is being used by the optimizer when the query executes.
select sql_id, child_number, sql_profile from v$sql where sql_profile is not null;
To view historical high resource-consuming SQL statements that have used a SQL profile,
select sql_id, sql_profile from dba_hist_sqlstat where sql_profile is not null;
RENAMING A SQL PROFILE
When you create and accept a SQL profile (see Recipe 12-1), the name is usually consists of a system-generated non-meaningful name. If you want to rename a SQL profile, use the DBMS_SQLTUNE package and the ALTER_SQL_PROFILE procedure.
For example:
BEGIN dbms_sqltune.alter_sql_profile ( NAME => 'SYS_SQLPROF_013f199ac5990000',
attribute_name =>
'NAME', VALUE => 'ORDER_ACCEPT_QUERY_SP');
END;
/
This allows you to provide a descriptive name for the SQL profile. You can verify the name change by querying DBA_SQL_PROFILES.
1.3 How To Automatically Accepting SQL Profiles
You realize that the Automatic SQL Tuning job runs on a daily basis (in Oracle Database 11g or higher). You determine that the automatic tuning job generates reasonable SQL profiles for problematic queries and now want to enable the automatic acceptance of SQL profiles generated by the automatic tuning job.
Use the DBMS_AUTO_SQLTUNE.SET_AUTO_TUNING_TASK_PARAMETER procedure to enable the automatic acceptance of SQL profiles recommended by the Automatic SQL Tuning task—
for example:
BEGIN
DBMS_AUTO_SQLTUNE.SET_AUTO_TUNING_TASK_PARAMETER(
parameter => 'ACCEPT_SQL_PROFILES', value => 'TRUE');
END;
/
If you want to disable the automatic acceptance of SQL profiles, then do so as follows (using the FALSE
parameter):
BEGIN
DBMS_AUTO_SQLTUNE.SET_AUTO_TUNING_TASK_PARAMETER(
parameter => 'ACCEPT_SQL_PROFILES', value => 'FALSE');
END;
/
In Oracle Database 11g or higher, an automatically configured job runs the SQL Tuning Advisor on a periodic basis(determined by a configured maintenance window). This job identifies high resource-consuming SQL statements from performance metrics contained in the AWR. When the automatic tuning job runs, it will occasionally recommend that a SQL profile be implemented for a poorly performing SQL statement. If you have configured the automatic acceptance via DBMS_AUTO_SQLTUNE.SET_AUTO_TUNING_TASK_PARAMETER, and if the SQL Tuning
Advisor deems the query’s performance will significantly improve with the SQL Profile, then it will be automatically implemented. You can report on the details of the automatic tuning task configuration via this
query:
SELECT
parameter_name
,parameter_value
FROM dba_advisor_parameters
WHERE task_name = 'SYS_AUTO_SQL_TUNING_TASK'
AND parameter_name
IN ('ACCEPT_SQL_PROFILES',
'MAX_SQL_PROFILES_PER_EXEC',
'MAX_AUTO_SQL_PROFILES',
'EXECUTION_DAYS_TO_EXPIRE');
PARAMETER_NAME PARAMETER_VALUE
------------------------- --------------------
EXECUTION_DAYS_TO_EXPIRE 30
ACCEPT_SQL_PROFILES TRUE
MAX_SQL_PROFILES_PER_EXEC 20
MAX_AUTO_SQL_PROFILES 10000
1.4 How To Displaying SQL Profile Information
You have created and accepted several SQL profiles and now want to view information related to these database objects.
DBA_SQL_PROFILES view to display information about SQL profiles. Here’s an example that selects the most interesting columns:
SQL> select name, type, status, sql_text from dba_sql_profiles;
Here is a snippet of the output:
NAME TYPE STATUS SQL_TEXT
------------------------------ ------- -------- ------------------------------
SYS_SQLPROF_012eda58a1be0001 MANUAL ENABLED SELECT ecm_snapshot_id AS id...
SYS_SQLPROF_012ea20305980000 MANUAL ENABLED SELECT * FROM inv_maint...
SYS_SQLPROF_012edf0316930003 MANUAL ENABLED SELECT /* + parallel(mgmt_db_f...
For this database, there are several manually enabled SQL profiles (as shown in the prior output).
Recall that a SQL profile contains improvements to existing statistics. The DBA_SQL_PROFILES view is the best source for viewing the SQL profile name, attributes, and associated SQL text. To view the internal SQL profile hint-related information, you can additionally query theDBMSHSXP_SQL_PROFILE_ATTR view. The prior output gives you an indication of the types of hints within a SQL profile. This information is used by the optimizer to better estimate the cardinality of each execution step. This data allows the optimizer to make better decisions when generating an execution plan.
for example:
SELECT
a.name
,b.comp_data
FROM dba_sql_profiles a
,dbmshsxp_sql_profile_attr b
WHERE a.name = b.profile_name;
Here is some sample output:
SYS_SQLPROF_0130520c90dc0002
<outline_data><hint><![CDATA[OPT_ESTIMATE(@"SEL$2",
NLJ_INDEX_SCAN, "FS"@"SEL$2", ("MAP"@"SEL$2"), "DB_FEAT_OPT_112_SUM_MV_IDX3",
SCALE_ROWS=0.3369001041)]]></h
You can also view this internal SQL profile information by querying the SQLOBJ$ and SQLOBJ$DATA views. The data in these views is in XML format, and therefore you must format the output with Oracle XML functions when querying—
for example:
SELECT
extractvalue(value(a), '.') sqlprofile_hints
FROM sqlobj$ o
,sqlobj$data d
,table(xmlsequence(extract(xmltype(d.comp_data),'/outline_data/hint'))) a
WHERE o.name = '&profile_name'
AND o. plan_id = d.plan_id
AND o.signature = d.signature
AND o.category = d.category
AND o.obj_type = d.obj_type;
Here is a small sample of the output:
OPT_ESTIMATE(@"SEL$EF0E05FC", INDEX_SCAN, "MGMT_TARGETS"@"SEL$4",
"MIDX3", SCALE_ROWS=50.68489486)
OPT_ESTIMATE(@"SEL$EF0E05FC", NLJ_INDEX_FILTER,
"MGMT_ECM_GEN_SNAPSHOT"@"SEL$3", ("MGMT_TARGETS"@"SEL$4"),
"IDX$$_1197C0001", SCALE_ROWS=0.4308705)
Again, these profile statistics don’t force the optimizer to use a certain execution plan. Rather, these statistics
provide the optimizer with information that enables it to construct a more efficient execution plan.
1.5 How To Selectively Testing a SQL Profile
You have a SQL profile that the SQL Tuning Advisor has recommended you enable for a SQL query. You want to test to see if the SQL profile helps performance but want to restrict the SQL profile being used to one session before enabling the profile for all sessions in the database. The idea being that you want to first verify the performance impact before allowing the optimizer to consider the SQL profile for all sessions running the given SQL query.
When you create a SQL profile, its category is assigned a value of DEFAULT (the category can be viewed by querying DBA_SQL_PROFILES). Also, when you start a SQL*Plus session by default the initialization parameter of SQTUNE_CATEOGRY has a value of DEFAULT. When you execute a SQL statement, the optimizer will check to see if there are any SQL profiles for the query, and also it will check to see if the category of the SQL profile matches the session’s (or system level) initialization parameter setting for SQLTUNE_CATEGORY. If those values match, then the optimizer will use the SQL profile as input when generating the execution plan.
Unless manually modified, the SQL profile category (value of DEFAULT) will always match the SQLTUNE_CATEGORY (value of DEFAULT). This means that if you alter a SQL profile’s category to something other than DEFAULT, then the optimizer will not consider using a SQL profile for query unless the session running the query has its SQLTUNE_CATEGORY modified to match the category of the SQL profile.
For example, say you modify the SQL profile to have a category of TEST1:
BEGIN
DBMS_SQLTUNE.ALTER_SQL_PROFILE(
name => 'SYS_SQLPROF_012eda58a1be0001',
attribute_name => 'CATEGORY',
value => 'TEST1');
END;
/
Now the optimizer will only consider using the SQL profile for sessions executing the SQL query that have the initialization parameter of SQLTUNE_CATEGORY set to TEST1. You can set SQLTUNE_CATEGORY for a session as follows:
SQL> alter session set sqltune_category=TEST1;
You can verify the session setting of the SQLTUNE_CATEGORY via this statement:
SQL> show parameter sqltune_category
And you can verify the category of a SQL profile via this query:
SQL> select name, category from dba_sql_profiles;
1.6 How To Transporting a SQL Profile to a Different Database
You have a test database and want to extract all of the SQL profiles from the test database and move them to a production database. Listed next are the steps involved with transporting a SQL profile from one database to another:
1. Create a staging table.
2. Populate the staging table.
3. Move the table from the source database to the destination database (use Data Pump or a database link).
4. On the destination database, extract information from the staging table to populate the data dictionary with SQL profile information.
These steps are detailed in the following subsections.
Step 1: Create a Staging Table
Use the DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF procedure to create the staging table. This example creates a table named PROF_STAGE owned by the MV_MAINT user:
BEGIN
dbms_sqltune.create_stgtab_sqlprof(
table_name => 'PROF_STAGE',
schema_name => 'MV_MAINT' );
END;
/
Step 2: Populate the Staging Table
Use the DBMS_SQLTUNE.PACK_STGTAB_SQLPROF procedure to populate the table created in step 1 with SQL profile information. This example populates the table with information regarding a specific SQL profile:
BEGIN
dbms_sqltune.pack_stgtab_sqlprof(
profile_name => 'SYS_SQLPROF_012edf84806e0004',
staging_table_name => 'PROF_STAGE',
staging_schema_owner => 'MV_MAINT' );
END;
Note : The PROFILE_NAME parameter can include wildcard characters. For example, if you want to transport all SQL profiles in a database, you can use ‘%’ for the PROFILE_NAME.
Step 3: Copy the Staging Table to the Destination Database
You can copy the table from one database to the other via Data Pump or by using a database link. This example creates a database link in the destination database and then copies the table from the source database:
create database link source_db
connect to mv_maint
identified by foo
using 'source_db';
Once the database link has been created, the table can be copied directly from the source with the CREATE
TABLE...AS SELECT statement:
SQL> create table PROF_STAGE as select * from PROF_STAGE@source_db;
Step 4: Load the Contents of the Staging Table into the Destination Database
Now in the destination database, unpack the table to load profile information into the database:
BEGIN
DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(
replace => TRUE,
staging_table_name => 'PROF_STAGE');
END;
/
If no profile name is specified, the default is the % wildcard character (meaning all profiles in the table will be
loaded into the destination database).
How It Works It’s fairly easy to copy SQL profiles from one database to another. You simply have to create a special table to hold the profile information, then populate the table, copy the table to the destination database, and lastly unpack the table’s contents. Table 12-3 describes all of the parameters for the profile packing procedure.
1.7 How To Disabling a SQL Profile
You created a SQL profile for a query (see Recipe 12-1) and sometime later you added an index to one of the tables used by the query and subsequently noticed that the SQL_PROFILE column of V$SQL is not populated (see Recipe 12-2) when the query is executing. You therefore want to disable the SQL profile and verify that there is no performance decline.
Solution
First verify the name of the SQL profile that you want to disable:
SQL> select name, status from dba_sql_profiles;
Here’s a partial snippet of the output:
NAME STATUS
------------------------------ --------
SYS_SQLPROF_013f199ac5990000 ENABLED
Now use the DBMS_SQLTUNE.ALTER_SQL_PROFILE procedure to modify the status of the profile to DISABLED:
BEGIN
DBMS_SQLTUNE.ALTER_SQL_PROFILE(
name => 'SYS_SQLPROF_013f199ac5990000',
attribute_name => 'STATUS',
value => 'DISABLED');
END;
/
1.8 How to Enable a SQL Profile
Disabling a SQL profile is fairly easy. You may want to do this because you have determined the SQL profile is no longer being used by the optimizer for a query. Or you may want to ascertain the impact of a SQL profile by disabling it, running the query, re-enabling it, and running the query. You can enable a disabled SQL profile as shown:
BEGIN
DBMS_SQLTUNE.ALTER_SQL_PROFILE(
name => 'SYS_SQLPROF_013f199ac5990000',
attribute_name => 'STATUS',
value => 'ENABLED');
END;
/
You can also disable a SQL profile from Enterprise Manager. From the Performance tab click on the SQL link, then click on SQL Plan Control.
1.9 How To Dropping a SQL Profile
You have determined that a SQL profile is no longer being used by a SQL query (see Recipe 12-2) and want to drop it.
Execute the DBMS_SQLTUNE.DROP_SQL_PROFILE procedure to drop a SQL profile. Pass in the name of the SQL profile you want to drop—
for example:
SQL> exec dbms_sqltune.drop_sql_profile('SYS_SQLPROF_012eda58a1be0001');
It’s fairly easy to drop a SQL profile. You might want to do this if you’re cleaning up a database or if you want to remove profiles from a testing environment. If you’re unsure of the SQL profile name, you can query DBA_SQL_PROFILES for more information.
If you want to drop all profiles in a database, you can use PL/SQL to loop through all profiles and drop them:
declare
cursor c1 is select name from dba_sql_profiles;
begin
for r1 in c1 loop
dbms_sqltune.drop_sql_profile(r1.name);
end loop;
end;
/
