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

Using SQL profile with SQL Tuning Advisor

SQL Tuning Advisor has become very easy to use on 11GR2, I very much like to give some very complex SQL statements to SQL Tuning Advisor to tune, almost every time I am not disappointed, usually after tuning, SQL Tuning Advisor will give you some suggestions, such as suggesting that you create an index or collect statistics, or suggest you to accept SQL Profile and give the performance improvement after accepting SQL Profile. This section will give an example to demonstrate how to use SQL Profile with SQL Tuning Advisor. First, we need to build the test table that we need to use:

SQL > CREATE TABLE test

2 AS

3 SELECT ROWNUM id

4 DBMS_RANDOM.STRING ('Aguilar, 12) name

5 DECODE (MOD (ROWNUM, 500), 0, 'Inactive',' Active') status

6 FROM all_objects a,dba_objects b

7 WHERE ROWNUM create index t_ind on t (status)

Index created.

SQL > begin

2 dbms_stats.gather_table_stats (ownname = > 'test'

3 tabname = > 'test'

4 no_invalidate = > FALSE

5 estimate_percent = > 100

6 force = > true

7 degree = > 5

8 method_opt = > 'for all columns size 1'

9 cascade = > true)

10 end

11 /

SQL > select status,count (*) from test group by status

STATUS COUNT (*)

--

Active 49900

Inactive 100

The above code does the following:

L creates a test table, test, with a total of 50000 records.

The field status-on the l table has two unique values: Active and Inactive, which have data skew.

L column status has 49900 values of Active, accounting for the vast majority of the records in the table, while the number of records of Inactive is very few, only 100.

The l status field has an index and analyzes the statistics of the table, but the status field does not collect the histogram.

Let's query the value of status for Inactive. Since the value of status for Inactive is very few, the performance of index scanning is better, but due to the lack of histogram on the column, the execution plan takes a full table scan:

SQL > select count (name) from test where status='Inactive'

COUNT (NAME)

-

one hundred

SQL > select * from table (dbms_xplan.display_cursor)

PLAN_TABLE_OUTPUT

SQL_ID c37q7z5qjnwwf, child number 0

-

Select count (name) from test where status='Inactive'

Plan hash value: 1950795681

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

| | 0 | SELECT STATEMENT | 51 (100) | | |

| | 1 | SORT AGGREGATE | | 1 | 21 | |

| | * 2 | TABLE ACCESS FULL | TEST | 25000 | 512K | 51 (2) | 00:00:01 |

The cardinality returned by the predicate-filtered full table scan shown in the above execution plan is 25000, because of the lack of histogram, so the optimizer simply calculates the cardinality by cardinality = total records of the table / number of unique values in the status field = 5000comp2q25000. Let's analyze the SQL through SQL Tuning Advisor to see if the optimizer can recognize that this is an inefficient execution plan and can give us some suggestions:

SQL > varc varchar2

SQL > exec: C: = dbms_sqltune.CREATE_TUNING_TASK (SQL_ID= > 'c37q7z5qjnwwf')

PL/SQL procedure successfully completed.

SQL > exec dbms_sqltune.execute_tuning_task (task_name = >: C)

PL/SQL procedure successfully completed.

SQL > select dbms_sqltune.report_tuning_task (: C) from dual

DBMS_SQLTUNE.REPORT_TUNING_TASK (: C)

GENERAL INFORMATION SECTION

Tuning Task Name: TASK_1112

Tuning Task Owner: TEST

Workload Type: Single SQL Statement

Scope: COMPREHENSIVE

Time Limit (seconds): 1800

Completion Status: COMPLETED

Started at: 08/01/2014 15:59:32

Completed at: 08/01/2014 15:59:33

Schema Name: TEST

SQL ID: c37q7z5qjnwwf

SQL Text: select count (name) from test where status='Inactive'

FINDINGS SECTION (1 finding)

1-SQL Profile Finding (see explain plans section below)

A potentially better execution plan was found for this statement.

Recommendation (estimated benefit: 51.46%)

-

-Consider accepting the recommended SQL Profile.

Execute dbms_sqltune.accept_sql_profile (task_name = > 'TASK_1112'

Task_owner = > 'TEST', replace = > TRUE)

Validation results

-

The SQL Profile was tested by executing both its plan and the original plan

And measuring their respective execution statistics. A plan may have been

Only partially executed if the other could be run to completion in less time.

Original Plan With SQL Profile Improved

Completion Status: COMPLETE COMPLETE

Elapsed Time (s):. 00212. 000221

CPU Time (s):. 002099 0002 90.47%

User O Time (s): 0 0

Buffer Gets: 210 102 51.42%

Physical Read Requests: 0 0

Physical Write Requests: 0 0

Physical Read Bytes: 0 0

Physical Write Bytes: 0 0

Rows Processed: 1 1

Fetches: 1 1

Executions: 1 1

Notes

-

1. Statistics for the original plan were averaged over 10 executions.

2. Statistics for the SQL Profile plan were averaged over 10 executions.

EXPLAIN PLANS SECTION

1-Original With Adjusted Cost

-

Plan hash value: 1950795681

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

| | 0 | SELECT STATEMENT | | 1 | 21 | 51 (2) | 00:00:01 |

| | 1 | SORT AGGREGATE | | 1 | 21 | |

| | * 2 | TABLE ACCESS FULL | TEST | 100 | 2100 | 51 (2) | 00:00:01 |

2-Using SQL Profile

-

Plan hash value: 4130896540

-

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

-

| | 0 | SELECT STATEMENT | | 1 | 21 | 2 (0) | 00:00:01 |

| | 1 | SORT AGGREGATE | | 1 | 21 | |

| | 2 | TABLE ACCESS BY INDEX ROWID | TEST | 100 | 2100 | 2 (0) | 00:00:01 |

| | * 3 | INDEX RANGE SCAN | T_IND | 100 | | 1 (0) | 00:00:01 |

-

The Dbms_sqltune package is used to create tuning tasks, perform tuning tasks, and view tuning results. The CREATE_TUNING_TASK function of the dbms_sqltune package is used to create a tuning task for SQL_ID for c37q7z5qjnwwf's SQL. Then the tuning task is carried out through the execute_tuning_task process. After the task is run, the optimizer will use techniques such as dynamic sampling to verify the difference between the evaluation content and the actual content, and adjust the execution plan according to the difference. Finally, the result of report view tuning is generated through report_tuning_task. The tuning results provide us with a suggestion that we adopt a SQL Profile, and compare the performance improvement after adopting SQL Profile. The EXPLAIN PLANS SECTION section behind report shows that with SQL Profile, the execution plan is changed to index scan, and the cardinality evaluation is very accurate, from 25000 to 100.

N Note: when you run SQL Tuning Advisor, it is recommended that you accept a SQL Profile. If you want to know what it offers you before accepting SQL Profile, you can run the following query to get it:

Sys@DLSP > select

2-- b.ATTR1-- 10g column

3 b.ATTR5-11g column

4 from

5 wri$_adv_tasks a

6 wri$_adv_rationale b

7 where

8 a.name = 'TASK_1112'

9 and b.task_id = a.id

10 order by

11 b.rec_id, b.id

12

ATTR5

OPT_ESTIMATE (@ "SEL$1", TABLE, "TEST" @ "SEL$1", SCALE_ROWS=0.004)

OPT_ESTIMATE (@ "SEL$1", INDEX_SCAN, "TEST" @ "SEL$1", "T_IND", SCALE_ROWS=0.004)

OPTIMIZER_FEATURES_ENABLE (default)

The name field of wri$_adv_tasks is the task name. In our example above, the task name can be obtained in the SQLPLUS environment through print c, or it can be found in the output of dbms_sqltune.report_tuning_task (: C).

Let's accept this SQL Profile to see if the SQL Profile you just created can be used for another query:

SQL > execute dbms_sqltune.accept_sql_profile (task_name = > 'TASK_1112',-

> task_owner = > 'TEST', replace = > TRUE)

PL/SQL procedure successfully completed.

SQL > select count (name) from test where status='Inactive'

COUNT (NAME)

-

one hundred

SQL > select * from table (dbms_xplan.display_cursor)

PLAN_TABLE_OUTPUT

SQL_ID c37q7z5qjnwwf, child number 0

-

Select count (name) from test where status='Inactive'

Plan hash value: 4130896540

-

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

-

| | 0 | SELECT STATEMENT | 2 (100) | |

| | 1 | SORT AGGREGATE | | 1 | 21 | |

| | 2 | TABLE ACCESS BY INDEX ROWID | TEST | 100 | 2100 | 2 (0) | 00:00:01 |

| | * 3 | INDEX RANGE SCAN | T_IND | 100 | | 1 (0) | 00:00:01 |

-

Note

-

-SQL Profile SYS_SQLPROF_01479094feeb0003 used for this statement

After accepting the SQL Profile provided by SQL Tuning Advisor, the execution plan Note:-SQL Profile SYS_SQLPROF_01479094feeb0003 used for this statement shows that SQL Profile has been used. Note that the SQL Profile names generated by SQL Tuning Advisor are all prefixed with SYS_SQLPROF, and the execution plan has changed from full table scan to index scan after using SQL Profile. Multiple parameters are available in the accept_sql_profile process of dbms_sqltune. Task_name and task_owner refer to the task name and user who created the SQL tuning task. Parameters name and DESCRIPTION refer to the name of SQL Profile and description of SQL Profile. Parameter CATEGORY specifies the class to which the created SQL Profile belongs. The default class is default. The parameter replace represents whether to replace the existing SQL Profile. Since a SQL can only have one SQL Profile, unlike Baseline, a SQL can have multiple Baseline, so if a SQL already exists SQL Profile, you must specify the replace parameter and set it to true when recreating. The parameter force_match indicates the text standardization method. The default is false. Once you accept the SQL Profile, you can view the information about the SQL Profile through the view dba_sql_profiles view. Because SQL Profile does not belong to a user, neither the all_sql_profiles nor the user_sql_profiles view is available.

If a SQL uses SQL Profile, the sql_profile field of the SQL's v$sql displays the name of the SQL Profile used. The following query shows the SQL Profile that exists in the system and the SQL of the SQL Profile being used in the current shared pool.

SQL > select name, category, status, substr (sql_text,1,25) sql_text, force_matching

2 from dba_sql_profiles

3 where sql_text like nvl ('& sql_text','%')

4 and name like nvl ('& name',name)

5 order by last_modified

6

Enter value for sql_text:

Enter value for name:

NAME CATEGORY STATUS SQL_TEXT FORCE_

Profile_c99yw1xkb4f1u_dwrose DEFAULT ENABLED select * from test NO

Profile_bhm28h6575bjy_dwrose DEFAULT ENABLED select test2.object_name, NO

Profile_51k1ug4rwah3c_dwrose DEFAULT ENABLED select distinct substr (ma NO

Profile_cm6stbx539mcz_dwrose DEFAULT ENABLED select count (*) from tt NO

Profile_c37q7z5qjnwwf_dwrose DEFAULT ENABLED select count (name) from t NO

SQL > select sql_id

2 child_number cn

3 plan_hash_value plan_hash

4 sql_profile

5 executions execs

6 buffer_gets / decode (nvl (executions, 0), 0,1, executions) avg_lio

7 from v$sql s

8 where upper (sql_text) like upper (nvl ('& sql_text', sql_text))

9 and sql_text not like'% from v$sql where sql_text like nvl (%')

10 and sql_id like nvl ('& sql_id', sql_id)

11 and sql_profile is not null

12 order by 1, 2, 3

Enter value for sql_text:

SQL_ID CN PLAN_HASH SQL_PROFILE EXECS AVG_LIO

C37q7z5qjnwwf 0 4130896540 profile_c37q7z5qjnwwf_dwrose 1 108

C37q7z5qjnwwf 1 4130896540 profile_c37q7z5qjnwwf_dwrose 2 105

We know from the name of SQL_PROFILE that none of these SQL Profile was created by SQL Tuning Advisor, but by hand, because the SQL Profile created by SQL Tuning Advisor is prefixed with SYS_SQLPROF.

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.