The below command can be used to create a SQL Tuning set
EXEC DBMS_SQLTUNE.create_sqlset(sqlset_name => 'SQL_Tuning_Set1');
BEGIN
DBMS_SQLTUNE.CREATE_SQLSET (
sqlset_name => 'SQL_Tuning_Set1',
description => 'SQL Tuning Set 1’);
END;
You can use UPDATE_SQLSET procedure to update the attributes of the SQL statements in the SQL tuning Sets
You can use below query to find the SQL Tuning sets owned by the user
SQL> SELECT NAME, STATEMENT_COUNT, DESCRIPTION FROM USER_SQLSET;
You can use DELETE_SQLSET procedure to deletes all the statements in SQL Tuning set
BEGIN
DBMS_SQLTUNE.DELETE_SQLSET ( sqlset_name => 'SQL_Tuning_Set1');
END;
/
You can use DROP_SQLSET procedure command the SQL Tuning set using below command
BEGIN
DBMS_SQLTUNE.DROP_SQLSET( sqlset_name => 'SQL_Tuning_Set1');
END;
/
You can also transfer the SQL tuning sets following steps create, pack, transfer, and unpack by using use below procedures. You can use datapump or export/import to export/import from source to destination database.
DBMS_SQLTUNE.create_stgtab_sqlset – create a stage
DBMS_SQLTUNE.pack_stgtab_sqlset – To pack SQL tuning sets
DBMS_SQLTUNE.unpack_stgtab_sqlset – To unpack SQL Tuning Sets
In Oracle 11g, further enhancements added to use SQL tuning sets with SQL Performance Analyzer. The DBMS_SQLPA package helps to build and compare two different version of the workload performance.
You can use CREATE_ANALYSIS_TASK to create an analysis task for SQL tuning set or for a single statement or single statement from the workload repository with range of snapshots
You can use below examples…
variable v_task VARCHAR2(64);
variable v_tset_task VARCHAR2(64);
-- SQL Text
EXEC :v_task := DBMS_SQLPA.CREATE_ANALYSIS_TASK(
sql_text => select dname, count(*) from dept, emp where dept.deptno = emp.deptno);
-- SQL ID
EXEC :v_task := DBMS_SQLPA.CREATE_ANALYSIS_TASK( SQL_ID => 'cv1d34ds5kdd4');
--Workload repository
exec :v_task := DBMS_SQLPA.CREATE_ANALYSIS_TASK( BEGIN_SNAP => 1, END_SNAP => 2,
SQL_ID => 'cv1d34ds5kdd4');
-- SQL Tuning Set
EXEC :v_tset_task := DBMS_SQLPA.CREATE_ANALYSIS_TASK( SQLSET_NAME => 'SQL_Tuning_Set1', order_by => 'BUFFER_GETS' );
EXEC DBMS_SQLPA.CANCEL_ANALYSIS_TASK(:v_task);
In Oracle 21c, Automatic SQL tuning automates the entire SQL tuning processes. The automated SQL Tuning sets (ASTS) is a system generated execution plan and performance metrics, it is useful for repairing SQL performance regression when using SQL Plan management.
This feature is introduced in 19c RU 19.7 onwards and available with AWR . You can use ASTS with SQL plan management to implement entire workflow without manual intervention.
You can run below query dba_sqlset_Statements to view statements in ASTS
SQL> SELECT SQL_TEXT FROM DBA_SQLSET_STATEMENTS WHERE SQLSET_NAME = 'SYS_AUTO_STS';
You can use below commands to enable/Disable ASTS
BEGIN
DBMS_AUTO_TASK_ADMIN.ENABLE(
CLIENT_NAME => 'ASTS CAPTURE TASK',
OPERATION => NULL,
WINDOW_NAME => NULL);
END;
/
BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE(
CLIENT_NAME => 'ASTS CAPTURE TASK',
OPERATION => NULL,
WINDOW_NAME => NULL);
END;
/
Thanks & Regards
http://oracleracexpert.com
ReplyDeleteUP Board 12th Question Paper 2023, Uttar Pradesh Board Intermediate Question Paper 2023, UP Board 12th Question Paper 2023 Those students who are waiting for the 12th class Question Paper 2023, the UP board will UP 12th Exam Pattern 2023announce it Question Paper 2023 in First week of June . Board Question Paper 2023 will be Downloading by name and Syllabus & Subject wise.