103 lines
4.1 KiB
SQL
103 lines
4.1 KiB
SQL
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : SQL Profile Commands
|
|
* Parameters :
|
|
*
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -----------------------------------------
|
|
* 16-May-12 Vishal Gupta First cut
|
|
*/
|
|
|
|
-- Requires the ALTER ANY SQL PROFILE privilege.
|
|
|
|
-- Create a SQL Profile (Use following scripts)
|
|
@sql_profile_create.sql
|
|
@sql_profile_create_fromcursor.sql
|
|
|
|
-- DROP a SQL Profile
|
|
exec DBMS_SQLTUNE.DROP_SQL_PROFILE (name => 'PROF_6r0u9km82f9gt_3364382418', ignore => FALSE)
|
|
|
|
-- Enable/Disable SQL Profile.
|
|
exec DBMS_SQLTUNE.ALTER_SQL_PROFILE (name=> 'PROF_8zp316hbs0c45_3180070961', attribute_name => 'STATUS', value => 'DISABLED')
|
|
exec DBMS_SQLTUNE.ALTER_SQL_PROFILE (name=> 'PROF_8zp316hbs0c45_3180070961', attribute_name => 'STATUS', value =>'ENABLED')
|
|
|
|
-- Rename a SQL Profile
|
|
exec DBMS_SQLTUNE.ALTER_SQL_PROFILE (name=> 'PROF_8zp316hbs0c45_3180070961', attribute_name =>'NAME', value =>'PROF_ak6yf7a3h5h9n_3180070961')
|
|
|
|
-- Modify Description
|
|
exec DBMS_SQLTUNE.ALTER_SQL_PROFILE (name=> '<profilename>', attribute_name =>'DESCRIPTION', value =>'<desc>')
|
|
|
|
-- Force matching of SQL statements even if literal values are used.
|
|
-- Its similar to using cursor_sharing=force
|
|
exec DBMS_SQLTUNE.ALTER_SQL_PROFILE (name=> '<profilename>',attribute_name =>'FORCE_MATCHING', value =>'TRUE')
|
|
|
|
-- Change CATEGORY
|
|
exec DBMS_SQLTUNE.ALTER_SQL_PROFILE (name=> '<profilename>', attribute_name =>'CATEGORY', value =>'<newcategory>')
|
|
|
|
|
|
-- On Source Side
|
|
-- Pack, transfer and UnPack the SQL Profile from one database to another.
|
|
drop table TRAF_DBA_OWNER.VG_SQLPROF_STG;
|
|
exec DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF ( table_name => 'VG_SQLPROF_STG' , schema_name => 'TRAF_DBA_OWNER' , tablespace_name => NULL);
|
|
exec DBMS_SQLTUNE.PACK_STGTAB_SQLPROF ( profile_name => 'PROF_f5mx26ua5khzy_8022806', profile_category => 'DEFAULT', staging_table_name => 'VG_SQLPROF_STG' , staging_schema_owner => 'TRAF_DBA_OWNER');
|
|
exec DBMS_SQLTUNE.PACK_STGTAB_SQLPROF ( profile_name => '%', profile_category => '%', staging_table_name => 'VG_SQLPROF_STG' , staging_schema_owner => 'TRAF_DBA_OWNER');
|
|
expdp "'/ as sysdba'" tables=TRAF_DBA_OWNER.VG_SQLPROF_STG directory=DIR_DUMP dumpfile=TRAF_DBA_OWNER.VG_SQLPROF_STG.dmp logfile=TRAF_DBA_OWNER.VG_SQLPROF_STG.exp.log
|
|
|
|
-- On Target Side
|
|
impdp "'/ as sysdba'" TABLE_EXISTS_ACTION=replace tables=TRAF_DBA_OWNER.VG_SQLPROF_STG dumpfile=TRAF_DBA_OWNER.VG_SQLPROF_STG.dmp logfile=TRAF_DBA_OWNER.VG_SQLPROF_STG.imp.log
|
|
exec DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF ( profile_name => '%', profile_category => 'DEFAULT', staging_table_name => 'VG_SQLPROF_STG' , staging_schema_owner => 'TRAF_DBA_OWNER' , replace => false);
|
|
exec DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF ( profile_name => 'PROF_f5mx26ua5khzy_8022806', profile_category => '%', staging_table_name => 'VG_SQLPROF_STG' , staging_schema_owner => 'TRAF_DBA_OWNER' , replace => false);
|
|
|
|
|
|
-------------------------------
|
|
-- SQL Tuning Advisor
|
|
-------------------------------
|
|
DECLARE
|
|
my_task_name VARCHAR2(30);
|
|
my_sqltext CLOB;
|
|
BEGIN
|
|
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
|
|
sql_id => 'f5mx26ua5khzy',
|
|
plan_hash_value => null,
|
|
scope => 'COMPREHENSIVE',
|
|
time_limit => 300,
|
|
task_name => 'TUNINGTASK_f5mx26ua5khzy',
|
|
description => 'TUNINGTASK_f5mx26ua5khzy'
|
|
);
|
|
END;
|
|
/
|
|
|
|
--After you have created a tuning task, you need to execute the task and start the tuning process. For example:
|
|
BEGIN
|
|
DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'TUNINGTASK_f5mx26ua5khzy' );
|
|
END;
|
|
/
|
|
|
|
|
|
SET LONG 80000
|
|
SET LONGCHUNKSIZE 40000
|
|
set pages 10000
|
|
set lines 500
|
|
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'TUNINGTASK_f5mx26ua5khzy')
|
|
FROM DUAL;
|
|
|
|
|
|
Recommendation (estimated benefit: 91.32%)
|
|
------------------------------------------
|
|
- Consider accepting the recommended SQL profile to use parallel execution for this statement.
|
|
|
|
BEGIN
|
|
dbms_sqltune.accept_sql_profile(task_name =>'TUNINGTASK_f5mx26ua5khzy'
|
|
, task_owner => 'SYS'
|
|
, replace =>TRUE
|
|
, profile_type => DBMS_SQLTUNE.PX_PROFILE);
|
|
END;
|
|
/
|
|
|
|
|