872123, 10:54 PM Document 271196.1 ‘Copytight(€) 2023, Orade Al rights reserved, Orade Confidential Automatic SQL Tuning and SQL Profiles (Doc ID 271196.1) In this Document Purpose ‘Ask Questions, Get Hel Questions and Answers What is Automatic SQL Tuning? What is a SOL Profile? How can the scone of the SO. Profile be controlled? ‘Towhat statements can a SOL Profle be apnlied? How can SOL Profiles be managed? Using Enterprise Manas And Share Your Experiences With This Article i Accepting a SQL Profile Altering a SOL Profile Dropping a SQL Profile Example How do I produce a report of every tuning set? Discuss SQL Profiles References Oracle Database - Enterprise Edition - Version 10.1.0.2 and later Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later Oracle Cloud Infrastructure - Database Service - Version N/A and later Oracle Database Cloud Exadata Service - Version N/A and later Oracle Database Cloud Schema Service - Version N/A and later Information in this document applies to any platform. NOTE: In the images and/or the document content below, the user information and data used represents fictitious data from the Oracle sample schema(s) or Public Documentation delivered with an Oracle database product. Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner. ‘SQL Profiles was a feature introduced in 10g and managed through the DBMS_SQLTUNE package or through Oracle Enterprise Manager as part of the Automatic SQL Tuning process. ‘This FAQ answers basic questions on using SQL Profiles and provides an example using DBMS_SQLTUNE package. Ask Questions, Get Help, And Share Your Experiences With This Article Would you like to explore this topic further with other Oracle Customers, Oracle Employees, and Industry Experts? lick here to join the discussion where you can ask questions, get help from others, and share your experiences with this specific article. Discover discussions about other articles and helpful subjects by clicking here to access the main My Oracle Support Community page for SQL Performance. hitps:iIsupportoracle.comfepmosifaces/DacumentDisplay?_adtct-state=19czqhlw00_1518id=271196.1 n872123, 10:54 PM Document 271196.1 What is Automatic SQL Tuning? ‘The query optimizer can sometimes produce inaccurate estimates about an attribute of a statement due to lack of information leading to poor execution plans. Traditionally, users have had to correct this problem by manually adding hints to the application code to guide the optimizer into making correct decisions. For packaged applications, changing application code is not an option and the only alternative available is to log a bug with the application vendor and wait for afix, ‘Automatic SQL Tuning deals with this problem with its SQL Profiling capability, The Automatic Tuning Optimizer creates a profile of the SQL statement called a SQL Profile, consisting of auxiliary statistics specific to that statement, The query ‘optimizer under normal mode makes estimates about cardinality, selectivity, and cost that can sometimes be off by a significant amount resulting in poor execution plans. SQL Profile addresses this problem by collecting additional information using sampling and partial execution techniques to verify and, if necessary, adjust these estimates. During SQL Profiling, the Automatic Tuning Optimizer also uses execution history information of the SQL statement to appropriately set optimizer parameter settings, such as changing the OPTIMIZER_MODE initialization parameter setting from ALL_ROWS to FIRST_ROWS for that SQL statement. ‘The output of this type of analysis is a recommendation to accept the SQL Profile. ‘An SQL Profile, once accepted, is stored persistently in the data dictionary. An SQL Profile is specific to a particular query. If accepted, the optimizer under normal mode uses the information in the SQL Profile in conjunction with regular database statistics when generating an execution plan, The availabilty of the additional information makes it possible to produce well-tuned plans for corresponding SQL statement without requiring any change to the application code. ‘The following documentation provides more information about the SQL Tuning Advisor: Oracle® Database 2 Day DBA 12c Release 1 (12.1) 17643-13 10 Monitoring and Tuning the Database About the SQL Tuning Advisor What is a SQL Profile? 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. Ths information can improve optimizer cardinality and selectivity estimates, which in turn leads the ‘optimizer to Select better plans. ‘The SQL profile does not contain information about individual execution plans. Rather, the optimizer has the following ‘sources of information when choosing plans: + The environment, which contains the database configuration, bind variable values, optimizer statistics, data set, and soon + The supplemental statistics in the SQL profile Itis important to note that the SQL. Profile does not freeze the execution pian of a SQL statement, as done by stored ‘outlines. As tables grow of indexes are created or dropped, the execution plan can change with the same SQL Profile. The information stored in it continues to be relevant even as the data distribution or access path of the corresponding statement change. However, over a long period of time, its content can become outdated and would have to be regenerated. This can be done by running Automatic SQL Tuning again on the same statement to regenerate the SQL Profile. How can the scope of the SQL Profile be controlled? ‘The scope of a SQL Profile can be controlled by the CATEGORY profile attribute. This attribute determines which user sessions can apply the profile, You can view the CATEGORY attribute for a SQL Profile in CATEGORY column of the hitps:iIsupportoracle.comfepmosifaces/DacumentDisplay?_adtct-state=19czqhlw00_1518id=271196.1 on872123, 10:54 PM Document 271196.1 DBA_SQL_PROFILES view. aclect category name from aba_sql_profiles; By default, all profiles are created in the DEFAULT category. This means that all user sessions where the ‘SQLTUNE_CATEGORY initialization parameter is set to DEFAULT can use the profile. By altering the category of a SQL profile, you can determine which sessions are affected by the creation of a profile. For example, by setting the category of a SQL Profile to DEV, only those users sessions where the SQLTUNE_CATEGORY initialization parameter is set to DEV can use the profile. All other sessions do not have access to the SQL Profile and execution plans for SQL statements are not impacted by the SQL profile. This technique enables you to test a SQL Profile ina restricted environment before making it available to other user sessions. ‘To what statements can a SQL Profile be applied? SELECT statements UPDATE statements INSERT statements (only with a SELECT clause) DELETE statements CREATE TABLE statements (only with the AS SELECT clause) MERGE statements (the update or insert operations) How can SQL Profiles be managed? ‘SQL Profiles can be handled by Oracle Enterprise Manager as part of the Automatic SQL Tuning process or manually by Using DBMS_SQLTUNE package. Using Enterprise Manager 1, On the Performance page, click Top Activity. ‘The Top Activity page appears. 2. Under Top SQL, click the SQL ID link of the SQL statement that is using a SQL profile. ‘The SQL Details page appears. 3. Click the Plan Control tab. A list of SQL profiles is displayed under SQL Profiles and Outlines. 4, Select the SQL profile you want to manage. Do one of the following: © To enable a SQL profile that is disabled, click Disable/Enable. © To disable a SQL profile that is enabled, click Disable/Enable. © To remove a SQL profile, click Delete. 5. A confirmation page appears. Click Yes to continue, or No to cancel the action. Using DBMS_SQLTUNE package. To use the SQL Profiles APIs, you need the CREATE ANY SQl_PROFILE, DROP ANY SQL_PROFILE, and ALTER ANY ‘SQL_PROFILE system privileges. Accepting a SQL Profile Use the DBMS_SQLTUNE,ACCEPT_SQL_PROFILE procedure to accept a SQL Profile recommended by the SQL Tuning Advisor. This creates and stores a SQL Profile in the database. iE ACCEPT_SOL_PRO: y_sql_tuning task", > ‘my sql profile! hitps:iIsupportoracle.comfepmosifaces/DacumentDisplay?_adtct-state=19czqhlw00_1518id=271196.1872123, 10:54 PM Document 271196.1 my_sql_tuning_task is the name of the SQL tuning task. You can view information about a SQL Profile in the DBA_SQL_PROFILES view. Altering a SQL Profile ‘ter the STATUS, NAME, DESCRIPTION, and CATEGORY attributes of an existing SQL Profile with the ALTER_SQL_PROFILE procedure ribute_nane => ‘STAT In this example, my_sql_profile is the name of the SQL Profile that you want to alter ‘The status attribute is changed to disabled which means the SQL Profile will not be used during SQL compilation. Dropping a SQL Profile A SQL Profile can be dropped using the DROP_SQL_PROFILE procedure. Example SESSION 1 Create table, populate, create index and gather statistics Execute query with no_index hint Full Table Scan used sgi> exeate table test (n umber 7 PL/SQ2 proved sly compiere: SQL> create index test sdk on test (n} her _cable_s hitpsiIsupportoracle.comfepmosifaces/DacumentDisplay?_adtct-state=19czqhlw00_1518id=271196.1813/23, 10:54PM Document 271196.1 ft no_index (test Lest idk) */ + from Lest where acts SESSION 2 -- SYS Create and execute tuning task and run report tuning task. ‘Accept recommended SQL Profile ry task name VARCHAR (20) begin Ty _agilext = tawlect /*4 no index (test Lest ak) */ * from test where nel": Ey_task-name 1~ DBMS SOLTUNE.CREATE TUNING TASK fine limit => 60, Ensk_name => ‘my sai tuning task 2", aescziption =» ‘Tagk to tund a query on specified table’); ond? PL/SGL procedure essfelly comp: begin DEMS. SOLTCNE. EXRCUTE_TUKING_TASK{ task name ~ ena: PL/SQL procedure 8 *ny_sal_tuning_task_2*h; esefclly com set ong 10009, Set Longehunksize 1000 set linesize 100 fet heading 0: [BCT DBMS_SQLTUNE REPORT TUNING_TASK( ‘my_sqi_tuning_task_2") fron DUAL: GenRAL INFORMATION SECTION Tuning Task Nave: wy_sal_tuning task 2 Tuning Task Owner = SYS orkload Type Single sou staterent Seape conmacrensive Tine Lieit(seconds): 62 Corpietion status: COMLETED Started at 09/26/2012 12:36:48 Corpleted at (9/24/2012 12:36:49, scorr Sgn TD: dengcsgosovu SQ Text: Select /*+ no_index(test test_Idx) */ ¢ from test where not FINDINGS SECTION (2 Finding) 41+ SQL Profile Finding (see explain plans section below) hitpsiIsupportoracle.comfepmosifaces/DacumentDisplay?_adct-state=19czqhiw00_1518id=271196.1,1819723, 10:54PM Document 271196.1 2+ SQL Profite Finding (6 ‘A potentiaiiy better execution plan was found for this statement feconrendation (estinated benefit: 98.95%) Consider accepting the reconrended SQ profile. fxecute dbs. sqitune accept sal_prortle(eask sone “ry Sa_tuning £3512", fasx_owier =) "SYS", replace => TRUE); ‘validation results The SQL profile was tested by executing both ts plan and the original plan and neasuring their respective execution statistics, A plan may have been ‘only partially executed {f the other could be rin Co completion in Less tine original Plan wlth SQl Profile x Iyproves Corpletion status: comics cowuere Elapsed Te (2) seoieed onal 67.02% GPU Fine (=): 22 90 User 2/0 Tine (3): ° turer Gets 2 Physical Read Requests: Physical write Requests Physical Read Bytes Physical weste Bytes ows Processed: Executions: 90.9% 4. Statistics for the original plan were averaged over 18 executions, 2. Statistics for the SQL profile plan were averages over 10 execct ions 4+ onsginal kith Adjusted Cost Plan nash value: 247508104 [14 [operation [vane | Rows | aytes | cost GOPU)] Tine | [e [seer stare |) a Ta [es | "taace access rut] test | 1] 1a (@)] sacearst | & (1 eecearet | Predicate Information (identi¢ied ty operation 14) 1s Fatterewreay 2. Using su Profile Plan hash value: 2616057887 [24 | operation Thane | aows | aytes | cost (Bbv)| Time | SELECT STATEREAT | 1 (| earee:e: e1 rl al i [a [ano nance scan] testzox | 1] | 1) aeteo:e: | Predicate Information (identi¢ied by operation 1d) a access("W"=1) DECLARE, ny_sqlprofile_nane VARCHAR? (30) begin ny_sqlprefile_name := DBMS_SQLTUNE.ACCEPT_SOL_PROFILE ( task_name => Tay _sql_cuning task 2", nae => 'my_sql_profile'); end: 7 PL/SQL procedure successfully completed. hitpsIsupportoracle.comfepmosifaces/DacumentDisplay?_adct-state=19czqhlw00_1518id=271196.1,872123, 10:54 PM Document 271196.1 SESSION 1 -- Run query again Even with no_index hint , index is used Note: In Explain Plan we see "SQL profile "my_sql_profile” used for this statement. SQL> set autetrace on SQL> select /*+ no_index(test test_idx) */ * fro execution Plan Plan hash value: 1616057887 aT operation Thane Waows 1 ayeen | Cone GCR)T Tine | e1 V1 NTT Ga eoreos0s | 1 | IMoEK RANGE scan] TesT_tox | 1] 41 1 (@)/ 88:00:01 | Predicate Information (identi¢ied SQL profile *ny_sql_profile” used for this s How do I produce a report of every tuning set? Lect "SELECT did, d.owner , d.description , d.created , d.tast_modified , d.statement_count, (DBMS_SQLTUNE.select_sqiset ("""||name||""")) 28, dba_sqlset d WHERE dname=""" | |name| 113 FROM dba_sqiset ORDER BY d. last_modified vBsC Discuss SQL Profiles Still have questions? Consider posting a discussion in the Database Tuning Community. NOTE:262687.1 - Using the DBMS_SQLTUNE Package to Run the SQL Tuning Advisor NOTE:1253696.1 - How To Use SQL Profiles for Queries Using Different Literals Using the Force_Match Parameter of DBMS_SQUTUNE.ACCEPT_SQlL_PROFILE NOTE:457531.1 - How to Move SQL Profiles from One Database to Another (Including to Higher Versions) Didnt find what you are looking for? hitps:iIsupportoracle.comfepmosifaces/DacumentDisplay?_adtct-state=19czqhlw00_1518id=271196.1 7