Shulou(Shulou.com)05/31 Report--

Editor to share with you how to use the SQL tuning toolkit DBMS_SQLTUNE. I hope you will get something after reading this article. Let's discuss it together.

Oracle provides the optimization recommendation feature package DBMS_SQLTUNE, which can help us analyze SQL and provide optimization recommendations.

Original implementation plan

Alter session set statistics_level=all

Set serveroutput off

Select * from test.emp where ename='SCOTT' and DEPTNO=20

SELECT * FROM table (dbms_xplan.display_cursor (NULL,NULL,'runstats_last'))

PLAN_TABLE_OUTPUT

-

SQL_ID 8k1gbrapm7zpd, child number 0

-

Select * from test.emp where ename='SCOTT' and DEPTNO=20

Plan hash value: 3956160932

| | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |

| | 0 | SELECT STATEMENT | | 1 | 1 | 00001 | 00.01 | 4 |

| | * 1 | TABLE ACCESS FULL | EMP | 1 | 1 | 1 | 00Rank 00.01 | 4 |

-

Predicate Information (identified by operation id):

1-filter (("ENAME" = 'SCOTT' AND "DEPTNO" = 20)) then optimize the SQL with DBMS_SQLTUNE

-1. Give users ADVISOR permissions

Grant ADVISOR to test

-- 2. Create a sql tuning task

Conn test/test

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

/

Parameter description:

Bind_list: multiple binding variables are separated by', 'commas. Parameter values must be written according to the type of column corresponding to the bound variable.

For example, if the emp.ename type is VARCHAR2 (10), it should be written as

Bind_list = > sql_binds (anydata.convertvarchar2 (10))

Time_limit: maximum execution time, default is 60.

Scope:

LIMITED, it takes about 1 second to optimize the SQL statement, but there is no SQL Profiling analysis.

COMPREHENSIVE, for comprehensive analysis, including SQL Profiling analysis; takes longer than LIMITED.

/ * add begin*/ on 2014-4-8

* * you can also use sql_id to create sql tunning tasks, which is much more convenient than using sql_text

FUNCTION CREATE_TUNING_TASK RETURNS VARCHAR2

Argument Name Type In/Out Default?

SQL_ID VARCHAR2 IN

PLAN_HASH_VALUE NUMBER IN DEFAULT

SCOPE VARCHAR2 IN DEFAULT

TIME_LIMIT NUMBER IN DEFAULT

TASK_NAME VARCHAR2 IN DEFAULT

DESCRIPTION VARCHAR2 IN DEFAULT

DECLARE

My_task_name VARCHAR2 (30)

BEGIN

My_task_name: = DBMS_SQLTUNE.CREATE_TUNING_TASK (

SQL_ID = > 'ddw7j6yfnw0vz'

Scope = > 'COMPREHENSIVE'

Time_limit = > 60

Task_name = > 'tunning_task_ddw7j6yfnw0vz'

Description = > 'Task to tune a query on ddw7j6yfnw0vz')

END

/

/ * add end*/ on 2014-4-8

-3. View the task name SELECT TASK_NAME

FROM DBA_ADVISOR_LOG

WHERE OWNER = 'TEST'

TASK_NAME

-

Test_sql_tuning

-- 4. Perform sql tuning tasks

BEGIN

DBMS_SQLTUNE.EXECUTE_TUNING_TASK (task_name = > 'test_sql_tuning')

END

/

-5. View sql tunning task status

SELECT status

FROM USER_ADVISOR_TASKS

WHERE task_name = 'test_sql_tuning'

STATUS

-

COMPLETED

-6. Show sql tunning results

SET LONG 10000

SET LONGCHUNKSIZE 1000

SET LINESIZE 100

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK ('test_sql_tuning')

FROM DUAL

DBMS_SQLTUNE.REPORT_TUNING_TASK ('TEST_SQL_TUNING')

-

GENERAL INFORMATION SECTION

Tuning Task Name: test_sql_tuning

Tuning Task Owner: TEST

Workload Type: Single SQL Statement

Scope: COMPREHENSIVE

Time Limit (seconds): 60

Completion Status: COMPLETED

Started at: 04/01/2014 16:45:16

Completed at: 04/01/2014 16:45:17

DBMS_SQLTUNE.REPORT_TUNING_TASK ('TEST_SQL_TUNING')

-

Schema Name: TEST

SQL ID: 95fv6dbj64d0f

SQL Text: select * from emp where ename=: name and DEPTNO=: deptno

FINDINGS SECTION (2 findings)

1-Statistics Finding

-

DBMS_SQLTUNE.REPORT_TUNING_TASK ('TEST_SQL_TUNING')

-

Table "TEST". "EMP" was not analyzed.

Recommendation

-

-Consider collecting optimizer statistics for this table.

Execute dbms_stats.gather_table_stats (ownname = > 'TEST', tabname = >

'EMP', estimate_percent = > DBMS_STATS.AUTO_SAMPLE_SIZE

Method_opt = > 'FOR ALL COLUMNS SIZE AUTO')

Rationale

-

DBMS_SQLTUNE.REPORT_TUNING_TASK ('TEST_SQL_TUNING')

-

The optimizer requires up-to-date statistics for the table in order to

Select a good execution plan.

2-Index Finding (see explain plans section below)

The execution plan of this statement can be improved by creating one or more

Indices.

Recommendation (estimated benefit: 66.67%)

-

-Consider running the Access Advisor to improve the physical schema designDBMS_SQLTUNE.REPORT_TUNING_TASK ('TEST_SQL_TUNING')

-

Or creating the recommended index.

Create index TEST.IDX$$_00D80001 on TEST.EMP ("ENAME", "DEPTNO")

Rationale

-

Creating the recommended indices significantly improves the execution plan

Of this statement. However, it might be preferable to run "Access Advisor"

Using a representative SQL workload as opposed to a single statement. This

Will allow to get comprehensive index recommendations which takes into

Account index maintenance overhead and additional space consumption.

DBMS_SQLTUNE.REPORT_TUNING_TASK ('TEST_SQL_TUNING')

-

EXPLAIN PLANS SECTION

1-Original

-

Plan hash value: 3956160932

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

DBMS_SQLTUNE.REPORT_TUNING_TASK ('TEST_SQL_TUNING')

-

| | 0 | SELECT STATEMENT | | 1 | 87 | 3 (0) | 00:00:01 |

| | * 1 | TABLE ACCESS FULL | EMP | 1 | 87 | 3 (0) | 00:00:01 |

Predicate Information (identified by operation id):

1-filter ("ENAME" =: NAME AND "DEPTNO" =: DEPTNO)

2-Using New Indices

-

DBMS_SQLTUNE.REPORT_TUNING_TASK ('TEST_SQL_TUNING')

-

Plan hash value: 2106247215

-

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

-

| | 0 | SELECT STATEMENT | | 1 | 87 | 1 (0) | 00:00:01 |

| | 1 | TABLE ACCESS BY INDEX ROWID | EMP | 1 | 87 | 1 (0) | 00:00:01 |

| | * 2 | INDEX RANGE SCAN | IDX$$_00D80001 | 1 | | 1 (0) | 00:00:01 |

-

Predicate Information (identified by operation id):

DBMS_SQLTUNE.REPORT_TUNING_TASK ('TEST_SQL_TUNING')

-

2-access ("ENAME" =: NAME AND "DEPTNO" =: DEPTNO)

-Summary of the recommended report:

Collect statistics for the EMP table

Execute dbms_stats.gather_table_stats (ownname = > 'TEST', tabname = >' EMP', estimate_percent = > DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt = > 'FOR ALL COLUMNS SIZE AUTO')

Create an index

Create index TEST.IDX$$_00D80001 on TEST.EMP ("ENAME", "DEPTNO"); execution plan after optimization

-

| | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |

-

| | 0 | SELECT STATEMENT | | 1 | 1 | 000.00 SELECT STATEMENT 00.01 | 3 |

| | 1 | TABLE ACCESS BY INDEX ROWID | EMP | 1 | 1 | 1 | 00lv 00.01 | 3 |

| | * 2 | INDEX RANGE SCAN | IDX$$_00D80001 | 1 | 1 | 1 | 00Rank 00.01 | 2 |

-

Predicate Information (identified by operation id):

2-access ("ENAME" = 'SCOTT' AND "DEPTNO" = 20)-- 7. Delete the sql tunning task after completion

EXEC DBMS_SQLTUNE.DROP_TUNING_TASK ('test_sql_tuning')

-8. Other

-- after the sql tunning task is created, you can also modify the parameters

BEGIN

DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER (

Task_name = > 'test_sql_tuning'

Parameter = > 'TIME_LIMIT', value = > 300)

END

/

-- check the progress of SQL Tuning Advisor (task has been implemented for a long time)

Col opname for a20

Col ADVISOR_NAME for a20

SELECT SID,SERIAL#,USERNAME,OPNAME,ADVISOR_NAME,TARGET_DESC,START_TIME SOFAR, TOTALWORK

FROM V$ADVISOR_PROGRESS

WHERE USERNAME = 'TEST'; or above arranged according to oracle doc

After reading this article, I believe you have a certain understanding of "how to use the SQL tuning toolkit DBMS_SQLTUNE". If you want to know more about it, you are welcome to follow the industry information channel. Thank you for reading!

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.