=====Mit Oracle SQL Profilen arbeiten und SQL Profile zwischen Datenbanken austauschen====


==== SQL Profile in der DB auswerten ====

Views:

  * dba_sql_profiles
  * sys.sqlobj$data
  * sys.sqlobj$


Scriptes:
  * Alle Profile           => http://orapowershell.codeplex.com/SourceControl/latest#sql/sql_profile.sql
  * Details für ein Profile => http://orapowershell.codeplex.com/SourceControl/latest#sql/sql_profile_details.sql


====Ein SQL Profile umbennenen ====

Wird mit den Tuning Advisor ein Profile erzeugt, wird diesen Profile ein generiert Name vergeben. 

Umbennen mit:
<code sql>
begin 
	dbms_sqltune.alter_sql_profile ( 
		  name => 'SYS_SQLPROF_0347a6bed3cc0008'
		, attribute_name => 'NAME'
		, value => 'GPI_BUG_1078' 
	);
end;
/
--- automatisch erzeugen mit:
column command format a90 heading "Command" WORD_WRAPPED
select 'begin '
	 ||chr(10)||'dbms_sqltune.alter_sql_profile('
	 ||chr(10)||'  name=>'''||pf.name||''''
	 ||chr(10)||' ,attribute_name=>''NAME'''
	 ||chr(10)||' ,value=>''BUG_7867_GUP_'||vs.sql_id||''');'
	 ||chr(10)||'end;'
	 ||chr(10)||'/' 
	 as command		 
  from gv$sql vs
     , dba_sql_profiles pf
where pf.name=vs.sql_profile		
  and sql_profile is not null
  and pf.sql_text like 'DELETE%'
/ 
</code>


==== SQL Profile zwischen Datenbanken austauschen ====

Ablauf:
	* Staging Tabelle erzeugen
	* Profile in die Tabelle kopieren
	* Exportieren aus der Quelle
	* Importieren in das Ziel
	* Aus der Staging Tabelle Profile "installieren"


===Staging Tabelle erzeugen===
<code sql>
begin
	dbms_sqltune.create_stgtab_sqlprof (
	   table_name => 'SQL_PROF_TRANSFERS'
	 , schema_name => 'SYSTEM');
end;
/
</code>

===Profile in die Tabelle kopieren===

Name vom Profil ermitteln und Profile in die Tabelle kopieren

<code sql>

select  name
     , category
	  , substr(sql_text,1,100) as sql_text	  
	  , to_char(last_modified,'dd.mm.RR hh24:mi')  as last_mod
	  , description
	  , type
	  , status
	  , force_matching	
  from dba_sql_profiles
order by last_modified,name
/


begin
	dbms_sqltune.pack_stgtab_sqlprof (
		  profile_name         => '<sql_profile_name>'
		, profile_category     => 'default'
		, staging_table_name   => 'SQL_PROF_TRANSFERS'
		, staging_schema_owner => 'SYSTEM'
	);
end;
/	


select count(*) from SQL_PROF_TRANSFERS;
  COUNT(*)
----------
        1

--- create Script:

column command format a90 heading "Command" WORD_WRAPPED
select 'begin '
	 ||chr(10)||'dbms_sqltune.pack_stgtab_sqlprof('
	 ||chr(10)||'   profile_name         => '''||pf.name||''''
	 ||chr(10)||' , profile_category     => ''DEFAULT'''
	 ||chr(10)||' , staging_table_name   => ''SQL_PROF_TRANSFERS'''
	 ||chr(10)||' , staging_schema_owner => ''SYSTEM'');'
	 ||chr(10)||'end;'
	 ||chr(10)||'/' 
	 as command		 
  from gv$sql vs
     , dba_sql_profiles pf
where pf.name=vs.sql_profile		
  and sql_profile is not null
  and pf.name like 'BUG_7867_GUP_%'
/
</code>


=== Exportieren aus der Quelle ===

Export Directory suchen oder anlegen


<code bash>
vi export_SQL_PROFILE_GPIDB_DB.dpctl
 
DIRECTORY=IMPDP_GPIDB_REPO 
LOGFILE=expdp_08-08-2014_16_40_GPIDB1.log
DUMPFILE=expdp_08-08-2014_16_40_GPIDB1.dmp 
REUSE_DUMPFILES=Y 
COMPRESSION=ALL 
TABLES=SYSTEM.SQL_PROF_TRANSFERS
JOB_NAME=EXPDP_SQL_PROFILE

expdp "'/ as sysdba'"„ parfile=export_SQL_PROFILE_GPIDB_DB.dpctl

</code>

Auf das Ziel übertragen 

=== Importieren  aus der Quelle ===



<code bash>
vi import_SQL_PROFILE_GPIDB_DB.dpctl
 
DIRECTORY=IMPDP_GPIDB_REPO 
LOGFILE=impdp_08-08-2014_16_51_DEVGPIDB1.log
DUMPFILE=expdp_08-08-2014_16_40_PRODGPIDB1.dmp
TABLES=SYSTEM.SQL_PROF_TRANSFERS
JOB_NAME=IMPDP_SQL_PROFILE


impdp "'/ as sysdba'"„ parfile=import_SQL_PROFILE_GPIDB_DB.dpctl

</code>


=== einlesen im Ziel===

check ob die Daten auch da sind:

<code sql>
select count(*) from SQL_PROF_TRANSFERS;
select OBJ_NAME from SQL_PROF_TRANSFERS;

</code>

<code sql>
begin
 DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF (
	  profile_name => '<sql_profile_name>'
	, profile_category => 'DEFAULT'
	, REPLACE => TRUE
	, staging_table_name => 'SQL_PROF_TRANSFERS'
	, staging_schema_owner => 'SYSTEM');
end;
/

--- create Script:

column command format a90 heading "Command" WORD_WRAPPED
select 'begin '
	 ||chr(10)||'dbms_sqltune.UNPACK_STGTAB_SQLPROF('
	 ||chr(10)||'   profile_name         => '''||OBJ_NAME||''''
	 ||chr(10)||' , profile_category     => ''DEFAULT'''
	 ||chr(10)||' , REPLACE => TRUE'
	 ||chr(10)||' , staging_table_name   => ''SQL_PROF_TRANSFERS'''
	 ||chr(10)||' , staging_schema_owner => ''SYSTEM'');'
	 ||chr(10)||'end;'
	 ||chr(10)||'/' 
		 as command		 
  from SQL_PROF_TRANSFERS
 where OBJ_NAME like 'BUG_7867_GUP_%'
/

</code>
==== Ein SQL Profile löschen ====

<code sql>
BEGIN
  DBMS_SQLTUNE.DROP_SQL_PROFILE(name => 'PROFILE NAME');
END;
/
</code>


==== SQL Profile Verwendung über die Category steuern====

Mit  dem init.ora Parameter **"SQLTUNE_CATEGORY = category_name"** kann gesteuert werden, welche Profile von der aktuellen Session bzw. der gesamten DB gesucht werden sollen.


<code bash>

-- Change the category of the profile so it will be used only by sessions with category set to TEST.

EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE ( 
  name            =>  'SYS_SQLPROF_0347a6bed3cc0008'
, attribute_name  =>  'CATEGORY'
, value           =>  'TEST')
 
</code>

==== SQL Profile manuell anlegen====

sieh auch http://kerryosborne.oracle-guy.com/2010/07/sqlt-coe_xfr_sql_profilesql/
==== Quellen ====

Oracle:
  * http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_sqltun.htm#ARPLS68380
  * http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_sqltun.htm#ARPLS68432


Netz:
  * http://intermediatesql.com/oracle/how-to-add-a-hint-to-oracle-query-without-touching-its-text/
  * http://www.allguru.net/database/create-oracle-sql-profile-tuning/
  * http://www.allguru.net/database/migrate-oracle-sql-profile/
  * http://oraxperts.com/node/108
  * http://www.ora-solutions.net/web/2008/10/19/renaming-sql-profiles-generated-with-oem/
  * http://kerryosborne.oracle-guy.com/2010/07/sqlt-coe_xfr_sql_profilesql/