Create sql profile and move sql profile from one database to another (part 1)
SQL Profile is a collection of information stored in the data dictionary that enables the query optimizer to create an optimal execution plan for a SQL statement.The SQL profile contains corrections for poor optimizer estimates discovered during Automatic SQL Tuning. This information can improve optimizer cardinality and selectivity estimates, which in turn leads the optimizer to select better plans .
In this article , I will try to clearly demonstrate how to create a sql profile for a problematic query and also how we can transfer the same profile to other database( assuming other database is running the identical sql ) http://parwezexa.com/create-sql-profile-and-move-sql-profile-from-one-database-to-anotherpart-2/ and apply on the target database .
In this example , I am creating a table and an index on one column , but I will be forcing the optimizer not to use the index using hints . later I will create a profile and will accept the best recommended profile for the query.
- Create table,index,gather stat and run statement forcing optimizer to choose full table scan
SQL> create table test (id number );
Table created.
SQL> declare
2 begin
3 for i in 1 .. 10000 loop
4 insert into test values(i);
5 commit;
6 end loop;
7 end;
8 /
PL/SQL procedure successfully completed.
SQL> create index test_idx on test(id);
Index created.
SQL> exec dbms_stats.gather_table_stats('','TEST');
PL/SQL procedure successfully completed.
SQL> set autotrace on
Autotrace Enabled
Shows the execution plan as well as statistics of the statement.
SQL> select /*+ no_index(test test_idx) */ * from test where id=5;
ID
1
Explain Plan
PLAN_TABLE_OUTPUT
Plan hash value: 1357081020
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 1 | 4 | 7 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST | 1 | 4 | 7 (0)| 00:00:01 |
SQL> select * from dba_sql_profiles;
no rows selected
2. As no profile exists , lets create a profile for the above sql statement and check the profile recommendation to make this sql execution better .
a) Create tuning task
declare
my_task_name VARCHAR2(30);
my_sqltext CLOB;
begin
my_sqltext := 'select /*+ no_index(test test_idx) */ * from test where id=5';
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text => my_sqltext,
user_name => 'PARWEZ',
scope => 'COMPREHENSIVE',
time_limit => 60,
task_name => 'my_sql_tuning_task_parwez',
description => 'Task to tune a query on a specified table');
end;
/
b) Execute the created tuning task
begin
DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'my_sql_tuning_task_parwez');
end;
/
c) Check the task report and recommendation
set long 10000
set linesize 100
set heading off
set heading on
set heading on
sql>SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'my_sql_tuning_task_parwez') from DUAL;
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK_PARWEZ')
GENERAL INFORMATION SECTION
Tuning Task Name : my_sql_tuning_task_parwez
Tuning Task Owner : SYS
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status : COMPLETED
Started at : 10/19/2020 19:31:48
Completed at : 10/19/2020 19:31:48
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK_PARWEZ')
Schema Name : PARWEZ
Container Name: PDB1
SQL ID : 3dr5803s315wr
SQL Text : select /*+ no_index(test test_idx) */ * from test where id=5
FINDINGS SECTION (1 finding)
1- SQL Profile Finding (see explain plans section below)
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK_PARWEZ')
A potentially better execution plan was found for this statement.
Recommendation (estimated benefit: 90.9%)
Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile(task_name =>
'my_sql_tuning_task_parwez', task_owner => 'SYS', replace =>
TRUE);
Validation results
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK_PARWEZ')
The SQL profile was tested by executing both its plan and the original plan
and measuring their respective execution statistics. A plan may have been
only partially executed if the other could be run to completion in less time.
Original Plan With SQL Profile % Improved ------------- ---------------- ----------
Completion Status: COMPLETE COMPLETE
Elapsed Time (s): .000141 .000012 91.48 %
CPU Time (s): .000141 .000012 91.48 %
User I/O Time (s): 0 0
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK_PARWEZ')
Buffer Gets: 22 2 90.9 %
Physical Read Requests: 0 0
Physical Write Requests: 0 0
Physical Read Bytes: 0 0
Physical Write Bytes: 0 0
Rows Processed: 1 1
Fetches: 1 1
Executions: 1 1
Notes
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK_PARWEZ')
Statistics for the original plan were averaged over 10 executions.
Statistics for the SQL profile plan were averaged over 10 executions.
EXPLAIN PLANS SECTION
1- Original With Adjusted Cost
Plan hash value: 1357081020
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK_PARWEZ')
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 1 | 4 | 7 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST | 1 | 4 | 7 (0)| 00:00:01 |
Predicate Information (identified by operation id):
1 - filter("ID"=5)
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK_PARWEZ')
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 3 (U - Unused (3))
0 - STATEMENT
U - IGNORE_OPTIM_EMBEDDED_HINTS / hint overridden by another in parent query block
U - OPTIMIZER_FEATURES_ENABLE(default) / hint overridden by another in parent query block
1 - SEL$1 / TEST@SEL$1
U - no_index(test test_idx) / rejected by IGNORE_OPTIM_EMBEDDED_HINTS
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK_PARWEZ')
2- Using SQL Profile
Plan hash value: 2882402178
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 1 | 4 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| TEST_IDX | 1 | 4 | 1 (0)| 00:00:01 |
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK_PARWEZ')
3. So the above recommendation is asking to create a sql profile , lets create a sql profile
DECLARE my_sqlprofile_name VARCHAR2(30); begin my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE ( task_name => 'my_sql_tuning_task_parwez', name => 'my_sql_profile'); end; / PL/SQL procedure successfully completed. Check if the profile is created and accepted SQL> select NAME,SQL_TEXT,LAST_MODIFIED,STATUS from dba_sql_profiles ; NAME SQL_TEXT LAST_MODIFIED STATUS my_sql_profile select /*+ no_index(test test_idx) */ * from test where id=5 20-OCT-20 07.28.14.000000000 AM ENABLED
4. Now we can see the profile is created and accepted lets run the same query and check if it is using the sql profile or not
SQL> set autotrace on
Autotrace Enabled
Shows the execution plan as well as statistics of the statement.
SQL> select /*+ no_index(test test_idx) */ * from test where id=5;
ID
1
Explain Plan
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 1 | 4 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| TEST_IDX | 1 | 4 | 1 (0)| 00:00:01 |
SQL profile "my_sql_profile" used for this statement
So our profile is working perfectly
771 total views, 1 views today