Oracle SQL Profile

What is an Oracle SQL Profile

 

Oracle SQL profile is one of the features provided by Oracle at an extra cost that you can use to guide the decision path the optimizer uses when selecting the execution plans.It comes with the Tuning pack activation,as a output to SQL Tuning advisor and provides auxiliary information to the optimizer to better the execution plans.

In very simple terms Oracle SQL Profile help optimizer derive better execution plans.Its a database object that contains corrections and improvements to the statistics of a particular SQL statement.It has corrections and enhancements to statistics that enable the optimizer to craft a more efficient execution plan.

Please keep in mind that  Oracle SQL Profile needs

  1.  Enterprise Edition of the Database.
  2.  Extra License Required .
  3.  But the beauty is no changes to actual SQL is required.

 

We will consider the below for our example case study.

Source DB:PSFNI

Target DB:PSFNA

Real Life usage of Oracle SQL Profile

 

This morning i had to tune a query with runs as a PSQUERY in a particular database.For those who dont know PSQUERY,Its a PeopleSoft reporting technology ,pretty similar to select queries but get initiated from the WebServer to the Appserver towards the database and returns the results back in HTML/Excel format to the web user.At the backend they are just running select queries in the Oracle database.We opted to run SQL Tuning Advisor on it and it suggested a Oracle SQL Profile.Oracle SQL Profile just did the magic and brought down the execution of the query in seconds.

How do you get a Oracle SQL Profile

It comes in as an output to SQL Tuning Advisor which you can initiate via OEM or via command line for a particular query.Here i will showcase a command line method of generating a recommendation which throws a SQL Profile.

The SQL ID in this case was 43yuu61zrwczd

PL/SQL procedure successfully completed.
 

SQL> exec dbms_sqltune.execute_tuning_task(task_name=>'PeopleSoft_Tuning');

PL/SQL procedure successfully completed.

SQL>
SQL> set long 10000
SQL> set longchunksize 10000
SQL> set lines 132
SQL> set pages 200
SQL> select dbms_sqltune.report_tuning_task('PeopleSoft_Tuning') from dual;

DBMS_SQLTUNE.REPORT_TUNING_TASK('PEOPLESOFT_TUNING')
----------------------------------------------------
GENERAL INFORMATION SECTION
-----------------------------------------------------
Tuning Task Name   : PeopleSoft_Tuning
Tuning Task Owner  : SYSADM
Workload Type      : Single SQL Statement
Scope              : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status  : COMPLETED
Started at         : 06/18/2019 07:00:38
Completed at       : 06/18/2019 07:14:54

-------------------------------------------------------------------------------
FINDINGS SECTION (2 findings)
-------------------------------------------------------------------------------

1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
  A potentially better execution plan was found for this statement.

  Recommendation (estimated benefit: 95.67%)
  ------------------------------------------
  - Consider accepting the recommended SQL profile.
    execute dbms_sqltune.accept_sql_profile(task_name => 'PeopleSoft_Tuning',
            task_owner => 'SYSADM', replace => TRUE);

  Validation results
  ------------------
  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:             PARTIAL          COMPLETE
  Elapsed Time (s):           31.043873          1.747894      94.36 %
  CPU Time (s):               16.770137           .749378      95.53 %
  User I/O Time (s):            .543182           .268452      50.57 %
  Buffer Gets:                  2228244             96473      95.67 %
  Physical Read Requests:           565               157      72.21 %
  Physical Write Requests:            0                 0
  Physical Read Bytes:          4628480           2203648      52.38 %
  Physical Write Bytes:               0                 0
  Rows Processed:                   943              2944
  Fetches:                          943              2944
  Executions:                         0                 1


Apply the profile to the SQL 43yuu61zrwczd

SQL> execute dbms_sqltune.accept_sql_profile(task_name => 'PeopleSoft_Tuning',ta
sk_owner =>'SYSADM',replace => TRUE);

PL/SQL procedure successfully completed.

Oracle Recommendation for Oracle SQL Profile

 

Oracle SQL Profile is not a silver bullet for improving query performance.Please accept the profile only if the application sees a significant performance gain,otherwise disable/drop it.

Steps to migrate  Oracle SQL Profile 

 

After we determined the SQL profile was the solution in PSFNI we also transported the same to PSFNA

Source DB:PSFNI

Target DB:PSFNA

Problem.The PS Query is running fine in PSFNI with the SQL profile and we would like to apply the same profile to the query in PSFNA.

Step 1 :  Create a Staging Table in the source Database(PSFNI)

SQL>EXEC DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF (table_name => 'SQL_PROF_PSFNA');

Step 2 : Populate the Staging Table with the Oracle SQL Profile information

SQL>EXEC DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name => 'SQL_PROF_PSFNA',profile_name=>'SYS_SQLPROF_016b63cf2df50000');

Step 3 : Copy the Staging Table to the destination Database(Destination DB:PSFNA)

I used a DB link to do the same

SQL>create table SQL_PROF_PSFNA as select * from SQL_PROF_PSFNA@PSFNI;

Step 4 : Load the Contents of the Staging Table into the Destination Database(Destination DB:PSFNA)

SQL>EXEC DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(REPLACE => TRUE,staging_table_name => 'SQL_PROF_PSFNA');

 

Some important Oracle SQL Profile Commands.

 

1.Dropping a Oracle SQL Profile

SQL>exec dbms_sqltune.drop_sql_profile('SYS_SQLPROF_016b63cf2df50000');

2.Disabling a Oracle SQL Profile

begin

dbms_sqltune.alter_sql_profile(name=>'SYS_SQLPROF_016b63cf2df50001',attribute_name=>'STATUS',value=>'DISABLED');

end;

/

3.Displaying Oracle SQL Profile Information 

select name,type,status,sql_text from dba_sql_profiles;

4.Determine if a Query is using a Oracle SQL Profile

select * from v$sql where sql_profile is not null;

Conclusion

 

When you issue a SQL Statement,the query optimizer creates an execution plan that describes the combination of steps Oracle takes to retrieve or change the data.Oracle SQL profile is one of the features provided by Oracle that you can use to guide the decision path the optimizer uses when selecting the execution plans.Needless to say in this era of ML and AI,SQL Tuning Advisor is a great example of the Oracle RDBMS learning query patterns and throwing out intelligent solution in form of Oracle SQL Profile,all of this without human intervention.This feature is only going to get better and better with newer versions of Oracle.Think about it Oracle Autonomous is Auto Tuning and probably a lot of workloads are out there ,which are load and go and to which Oracle's advisor might just learn and tune at a click from the DBA.Sounds exciting isnt it?

Hope you guys like this article.Please provide your feedback.

Until then,

Happy Learning!!

  • Manjunath Maller
    • OCP 9i,10g,11g,12c
    • OCE RAC and DG 12c.
    • OCI(Oracle Cloud Infrastructure) Certified .
    • AWS enthuiast

 

You may explore our E-Learning Page to have an insight into our High Rated & Best Seller Courses on Udemy

DB Alchemist Academy

 

 

Share you comments

{{ errorMessage }}