1. Home
  2. Support
Article - CS234097

Tuning queries with SQL Tuning Advisor and SQL Profiles

Modified: 30-May-2016   


Applies To

  • Windchill PDMLink 9.1 F000

Description

Tuning queries with SQL Tuning Advisor (STA) and SQL Profiles in ORACLE

Note: You need to have the Diagnostic and Tuning Pack enabled for your Oracle system.
  • Login as DBA user:
$ sqlplus "/ as sysdba"
  • Run this sql to see what sqls are getting executed.
col program format a20
set lines 180
col sql_text format a50
select b.sid,b.status,b.last_call_et,b.program,c.sql_id,c.sql_text from v$session b,v$sqlarea c where b.sql_id=c.sql_id;
  • Run SQL Tuning Advisor for the sql_id of interest.
SQL> spool spool_file_name.log
SQL> @?/rdbms/admin/sqltrpt
SQL> spool off

This accepts awr snapshots info and sql_id which needs to be analysed.

Browse through the spool file, you will find a "FINDINGS SECTION".
Under this you might see recommendations given by STA. A typical recommendation can be like this.
1- SQL Profile Finding (see explain plans section below)
——————————————————–
A potentially better execution plan was found for this statement.

Recommendation (estimated benefit: 99.91%)
——————————————
– Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_4711',replace => TRUE);

This means that it has found a better execution plan for the current sql than the one being used for it. This execution plan is represented in terms of an SQL profile.
  • For making the new profile default for the current sql, execute the accept_qsl_profile procedure as suggested by STA:
SQL> execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_4711',replace => TRUE);

Once we execute the above, the suggested profile will be registered in DBA_SQL_PROFILES table under the category DEFAULT with a system generated NAME such as 'SYS_SQLPROF_014a343154084000' (some random name!).
  • If you want the profile to be registered with a custom name use:
SQL> execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_4711',name => 'CUSTOM_PROF_MyQuery');
  • We can move these profiles from one database to another database as below. This is called packing and unpacking sql profiles. First Create the staging table which will hold sql profiles to be moved over.
SQL> exec DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF (table_name=>'SQL_STG_TAB',schema_name=>'SYS');

This will create a table SQL_STG_TAB, which will be able to hold the required sql profiles, the structure of this table is exactly same as that of DBA_SQL_PROFILES table.
  • To pack a custom profile to stage table:
SQL> EXEC DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name => 'SQL_STG_TAB',profile_name=>’CUSTOM_PROF_MyQuery′);
  • To Pack all profiles under DEFAULT category to stage table,
SQL> EXEC DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name => 'SQL_STG_TAB');
  • If you want to pack particular sql profiles and you don't know the names of the profiles you can query dba_sql_profiles tab.
SQL> select name from DBA_SQL_PROFILES where SQL_TEXT like '%SELECT%EMPLOYEE%NAME%';

NAME
——————————
SYS_SQLPROF_0815

SQL> exec DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name => 'SQL_STG_TAB',profile_name=>'SYS_SQLPROF_0815');
  • Export stage tab using exp.
$ exp "/ as sysdba" tables=SQL_STG_TAB file=SQL_STG_TAB.dmp
  • Import the stage tab using imp
$ imp "/ as sysdba" file=SQL_STG_TAB.dmp full=y
  • Unpack the profiles at the target database,
SQL> EXEC DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(replace => TRUE,staging_table_name => 'SQL_STG_TAB');
This is a printer-friendly version of Article 234097 and may be out of date. For the latest version click CS234097