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.

  1. 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 

 316 total views,  4 views today

Leave a Reply

Your email address will not be published. Required fields are marked *