管理Managing SQL Profiles

Implement a recommended SQL profile.

"Implementing a SQL Profile" describes this task.

實施sql profile

Obtain information about SQL profiles stored in the database.

"Listing SQL Profiles" describes this task.

獲取資料庫中sql  profile 存放的資訊 

Optionally, modify the implemented SQL profile.

"Altering a SQL Profile" describes this task.

對存在的sqlprofile 進行修改

Drop the implemented SQL profile when it is no longer needed.

"Dropping a SQL Profile" describes this task.

刪除一個不再需要的sql profile

The SQL Tuning Advisor task STA_SPECIFIC_EMP_TASK includes a recommendation to create a SQL profile.

The name of the SQL profile is my_sql_profile.

The PL/SQL block accepts a profile that uses parallel execution (profile_type).

The profile uses force matching.

Connect SQL*Plus to the database with the appropriate privileges, and then execute the ACCEPT_SQL_PROFILE function.

For example, execute the following PL/SQL:

DECLARE
  my_sqlprofile_name VARCHAR2(30);
BEGIN
  my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE ( 
    task_name    => 'STA_SPECIFIC_EMP_TASK'
,   name         => 'my_sql_profile'
,   profile_type => DBMS_SQLTUNE.PX_PROFILE
,   force_match  => true 
);
END;
/

						

							22.3 Listing SQL Profiles
						

						

							The data dictionary view DBA_SQL_PROFILES stores SQL profiles persistently in the database. The statistics are in an Oracle internal format, so you cannot query profiles directly. However, you can list profiles.
						

						

							To list SQL profiles: 
						

						

							
							

								        - 
								

									Connect SQL*Plus to the database with the appropriate privileges, and then query the DBA_SQL_PROFILES view.
								

								

									For example, execute the following query:
								

COLUMN category FORMAT a10
COLUMN sql_text FORMAT a20

SELECT NAME, SQL_TEXT, CATEGORY, STATUS
FROM   DBA_SQL_PROFILES;

								

									Sample output appears below:
								
NAME                           SQL_TEXT             CATEGORY   STATUS
------------------------------ -------------------- ---------- --------
SYS_SQLPROF_01285f6d18eb0000   select promo_name, c DEFAULT    ENABLED
                               ount(*) c from promo
                               tions p, sales s whe
                               re s.promo_id = p.pr
                               omo_id and p.promo_c
                               ategory = 'internet'
                                group by p.promo_na
                               me order by c desc


								

									22.4 Altering a SQL Profile
								

								

									You can alter attributes of an existing SQL profile using the attribute_name parameter of the ALTER_SQL_PROFILE procedure.
								

								

									The CATEGORY attribute determines which sessions can apply a profile. View the CATEGORY attribute by queryingDBA_SQL_PROFILES.CATEGORY. By default, all profiles are in the DEFAULT category, which means that all sessions in which theSQLTUNE_CATEGORY initialization parameter is set to DEFAULT can use the profile.
								

								

									By altering the category of a SQL profile, you determine which sessions are affected by profile creation. For example, by setting the category toDEV, only sessions in which the SQLTUNE_CATEGORY initialization parameter is set to DEV can use the profile. 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 profile in a restricted environment before making it available to other sessions.
								

								

									The example in this section assumes that you want to change the category of the SQL profile so it is used only by sessions with the SQL profile category set to TEST, run the SQL statement, and then change the profile category back to DEFAULT.
								

								

									To alter a SQL profile: 
								

								

									
									

										            1. 
										

											Connect SQL*Plus to the database with the appropriate privileges, and then use the ALTER_SQL_PROFILE procedure to set theattribute_name.
										

										

											For example, execute the following code to set the attribute CATEGORY to TEST:
										

VARIABLE pname my_sql_profile
BEGIN DBMS_SQLTUNE.ALTER_SQL_PROFILE ( 
   name            =>  :pname
,  attribute_name  =>  'CATEGORY'
,  value           =>  'TEST'      
);
END;

									
									

										            2. 
										

											Change the initialization parameter setting in the current database session.
										
										

											For example, execute the following SQL:
										
ALTER SESSION SET SQLTUNE_CATEGORY = 'TEST';

									
									

										            3. 
										

											Test the profiled SQL statement.
										
									
									

										            4. 
										

											Use the ALTER_SQL_PROFILE procedure to set the attribute_name.
										
										

											For example, execute the following code to set the attribute CATEGORY to DEFAULT:
										
VARIABLE pname my_sql_profile
BEGIN 
  DBMS_SQLTUNE.ALTER_SQL_PROFILE ( 
     name            =>  :pname
,    attribute_name  =>  'CATEGORY'
,    value           =>  'DEFAULT'   
);
END;
									
								
								

									22.5 Dropping a SQL Profile
								
								

									You can drop a SQL profile with the DROP_SQL_PROFILE procedure.
								
								

									Assumptions
								
								

									This section assumes the following:
								
								

									

										            - 
										

											You want to drop my_sql_profile.
										
									
									

										            - 
										

											You want to ignore errors raised if the name does not exist.
										
									
								
								

									To drop a SQL profile: 
								
								

									

										            - 
										

											Connect SQL*Plus to the database with the appropriate privileges, call the DBMS_SQLTUNE.DROP_SQL_PROFILE procedure.
										
										

											The following example drops the profile named my_sql_profile:
										
BEGIN
  DBMS_SQLTUNE.DROP_SQL_PROFILE ( 
    name => 'my_sql_profile' 
);
END;
/
										

											22.6 Transporting a SQL Profile
										

										

											You can transport SQL profiles. This operation involves exporting the SQL profile from the SYS schema in one database to a staging table, and then importing the SQL profile from the staging table into another database. You can transport a SQL profile to any Oracle database created in the same release or later.
										

										

											Table 22-1 shows the main procedures and functions for managing SQL profiles.
										

										

											
											

												Table 22-1 APIs for Transporting SQL Profiles
											

											

												
												
													
													

														
														

															Procedure or Function
														

														

															Description
														

													

												

												

													
													

														
														

															
															

																CREATE_STGTAB_SQLPROF
															

														

														

															
															

																Creates the staging table used for copying SQL profiles from one system to another.
															

														

													

													

														
														

															
															

																PACK_STGTAB_SQLPROF
															

														

														

															
															

																Moves profile data out of the SYS schema into the staging table.
															

														

													

													

														
														

															
															

																UNPACK_STGTAB_SQLPROF
															

														

														

															
															

																Uses the profile data stored in the staging table to create profiles on this system.
															

														

													

												

											

										

										

											The following graphic shows the basic workflow of transporting SQL profiles: