Introduction to DBM_SQLTUNE usage
How to use DBMS_SQLTUNE
Required permissions
Grant advisor to user
Grant select_catalog_role to user;-essential for management through OEM
Grant execute on dbms_sqltune to user
The method to get the bound variable
There are two situations at this time, one is the SQL that exists in v$sql, the other is SQL which does not exist in v$sql.
Convert the corresponding binding variable value in v$sql
Follow SQL_ID to get the value of bind_data in v$sql
Select position, value_string
From table (dbms_sqltune.extract_binds ('beda0a200500521ffd700'))
If it does not exist in the v$sql, you can use this method to get the value of the binding variable
Select dbms_sqltune.extract_bind (bind_data,1) .value_string | |'-'| |
Dbms_sqltune.extract_bind (bind_data, 2) .value_string | |'-'| |
Dbms_sqltune.extract_bind (bind_data, 3) .value_string | |'-'| |
Dbms_sqltune.extract_bind (bind_data, 4) .value_string | |'-'| |
Dbms_sqltune.extract_bind (bind_data, 5) .value_string | |'-'| |
Dbms_sqltune.extract_bind (bind_data, 6) value_string
From sys.wrh$_sqlstat
Where sql_id = '1nrufx6sw8sd'
1. Create tuning task 1.1 create via sql_text (bind variable SQL)
DECLARE
My_task_name VARCHAR2 (30)
My_sqltext CLOB
BEGIN
My_sqltext: = 'select * from emp where ename=:name and DEPTNO=: deptno'
My_task_name: = DBMS_SQLTUNE.CREATE_TUNING_TASK (
Sql_text = > my_sqltext
Bind_list = > sql_binds (anydata.convertvarchar2 (10), anydata.convertnumber (2))
User_name = > 'TEST'
Scope = > 'COMPREHENSIVE'
Time_limit = > 60
Task_name = > 'test_sql_tuning'
Description = > 'Task to tune a query on emp')
END
/
1.2 Optimization of unbound variable SQL
Declare
My_task_name VARCHAR2 (30)
My_sqltext CLOB
BEGIN
My_sqltext: = 'select * fromorabpel.cube_scope'
My_task_name: = DBMS_SQLTUNE.CREATE_TUNING_TASK (sql_text = > my_sqltext
User_name = > 'orabpel'
Scope = > 'COMPREHENSIVE'
Time_limit = > 60,-- optimize the time limit for 60s
Task_name = > 'wxw_sql_tuning_task'
Description = > 'tune thebad sql')
Dbms_sqltune.Execute_tuning_task (task_name = > 'TEST_sql_tuning_task')
END
1.3 optimize through SQL_ID
This situation is often used for abnormal SQL optimization in v$sql.
DECLARE
My_task_name VARCHAR2 (40)
BEGIN
My_task_name: = DBMS_SQLTUNE.create_tuning_task (
Sql_id = > '1hudpukz651tt'
Plan_hash_value = > NULL
Scope = > dbms_sqltune.SCOPE_COMPREHENSIVE
Time_limit = > dbms_sqltune.TIME_LIMIT_DEFAULT
Task_name = > 'test_sql_tuning_1hudpukz651tt'
Description = > '1hudpukz651tt')
DBMS_SQLTUNE.EXECUTE_TUNING_TASK (task_name = > 'test_sql_tuning_1hudpukz651tt')
Dbms_output.put_line (my_task_name)
END
1.4 optimize by specifying Snap_id in AWR
Declare
My_task_name varchar2 (30)
Begin
My_task_name: = dbms_sqltune.create_tuning_task (
Begin_snap = > 24365
End_snap = > 24366
Sql_id = > 'd40kghyfbg8sj'
Plan_hash_value = > null
Scope = > 'comprehensive'
Time_limit = > 60
Task_name = > 'wxw_sql_tuning_task'
Description = > 'tune thebad sql'
);
Dbms_sqltune.execute_tuning_task (task_name = > 'wxw_sql_tuning_task')
End
2. Perform tuning tasks
-execute
Execdbms_sqltune.execute_tuning_task ('TEST_tuning_task')
3. Check the implementation
-checek
SELECT status FROM USER_ADVISOR_TASKS WHERElower (task_name) = 'test_tuning_task'
Select * from user_advisor_tasks
-report
SET LONG 999999
Set serveroutput on size 999999
SET LINESIZE 1000
SELECTDBMS_SQLTUNE.REPORT_TUNING_TASK ('TEST_tuning_task') FROM DUAL
4. Delete tuning tasks
-drop
Execdbms_sqltune.drop_tuning_task ('TEST_tuning_task')
