Shulou(Shulou.com)06/01 Report--

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')

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.