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

This article mainly introduces "how to use SPM BASELINE". In daily operation, I believe many people have doubts about how to use SPM BASELINE. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts about "how to use SPM BASELINE". Next, please follow the editor to study!

Evolution of baselines

If the optimizer realizes that there may be a better execution plan for a sql that has already created a baseline, then the optimizer automatically generates a baseline whose accepted status of the dba_sql_plan_baselines in the view is NO. DBA can evolve baseline in two ways: the dbms_spm.evolve_sql_plan_baseline function and SQL Tuning Advisor.

Test@DLSP > create index t_ind on test (status)

Index created.

Test@DLSP > select count (name) from test where status=: a

COUNT (NAME)

-

one hundred

Test@DLSP > select sql_handle, plan_name, origin, accepted,fixed

2 from dba_sql_plan_baselines

3 where sql_text like'% count (name)%'

SQL_HANDLE PLAN_NAME ORIGIN ACCEPT FIXED

SQL_619bd8394153fd05 SQL_PLAN_636ys750p7z8519ccc485 AUTO-CAPTURE NO NO

SQL_619bd8394153fd05 SQL_PLAN_636ys750p7z856b581ab9 MANUAL-LOAD YES NO

After we index the status field of test, we execute the query again, and a new baseline is generated in dba_sql_plan_baselines. The baseline is generated by: AUTO-CAPTURE,accepted is NO. Next, we use the functions dbms_spm.evolve_sql_plan_baseline and SQL Tuning Advisor to evolve baseline.

1) the method of dbms_spm package

In the following code, we evolve the baseline,verify parameter to YES through the evolve_sql_plan_baseline function of the dbms_spm package: it will only evolve if there is a real performance improvement.

Test@DLSP > SELECT dbms_spm.evolve_sql_plan_baseline (

2 sql_handle = > 'SQL_619bd8394153fd05'

3 plan_name = > 'SQL_PLAN_636ys750p7z8519ccc485'

4 time_limit = > 10

5 verify = > 'yes'

6 commit = > 'yes'

7)

8 FROM dual

DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE (SQL_HANDLE= > 'SQL_619BD8394153FD05',PLAN_NAME=

Evolve SQL Plan Baseline Report

Inputs:

-

SQL_HANDLE = SQL_619bd8394153fd05

PLAN_NAME = SQL_PLAN_636ys750p7z8519ccc485

TIME_LIMIT = 10

VERIFY = yes

COMMIT = yes

Plan: SQL_PLAN_636ys750p7z8519ccc485

-

Plan was verified: Time used. 05 seconds.

Plan passed performance criterion: 2.06 times better than baseline plan.

Plan was changed to an accepted plan.

Baseline Plan Test Plan Stats Ratio

Execution Status: COMPLETE COMPLETE

Rows Processed: 1 1

Elapsed Time (ms): 2.167. 253 8.57

CPU Time (ms): 2.221. 222 10

Buffer Gets: 210 102 2.06

Physical Read Requests: 0 0

Physical Write Requests: 0 0

Physical Read Bytes: 0 0

Physical Write Bytes: 0 0

Executions: 1 1

Report Summary

Number of plans verified: 1

Number of plans accepted: 1

After the function dbms_spm.evolve_sql_plan_baseline is executed, a report is generated, which compares the resource consumption of the execution plan corresponding to the two baseline in detail. Because the performance of the baseline to be evolved did improve after it was validated, the optimizer received the baseline. The following code also shows that the accepted field in the view dba_sql_plan_baselines has also changed from NO to YES. Re-execute the query, and we have also used our newly created baseline.

Test@DLSP > select sql_handle, plan_name, origin, accepted,fixed

2 from dba_sql_plan_baselines

3 where sql_text like'% count (name)%'

SQL_HANDLE PLAN_NAME ORIGIN ACCEPT FIXED

SQL_619bd8394153fd05 SQL_PLAN_636ys750p7z8519ccc485 AUTO-CAPTURE YES NO

SQL_619bd8394153fd05 SQL_PLAN_636ys750p7z856b581ab9 MANUAL-LOAD YES NO

Test@DLSP > select count (name) from test where status=: a

COUNT (NAME)

-

one hundred

Test@DLSP > select * from table (dbms_xplan.display_cursor)

PLAN_TABLE_OUTPUT

SQL_ID aa8mzbnrzu42f, child number 0

-

Select count (name) from test where status=: a

Plan hash value: 4130896540

-

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

-

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

| | 1 | SORT AGGREGATE | | 1 | 25 |

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

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

-

Predicate Information (identified by operation id):

3-access ("STATUS" =: a)

Note

-

-SQL plan baseline SQL_PLAN_636ys750p7z8519ccc485 used for this statement

2) SQL Tuning Advisor mode

We go back to the days when baseline didn't evolve.

Test@DLSP > select sql_handle, plan_name, origin, accepted,fixed

2 from dba_sql_plan_baselines

3 where sql_text like'% count (name)%'

SQL_HANDLE PLAN_NAME ORIGIN ACCEPT FIXED

SQL_619bd8394153fd05 SQL_PLAN_636ys750p7z8519ccc485 AUTO-CAPTURE NO NO

SQL_619bd8394153fd05 SQL_PLAN_636ys750p7z856b581ab9 MANUAL-LOAD YES NO

We create a tuning task through the CREATE_TUNING_TASK function of the dbms_sqltune package.

Test@DLSP > varc varchar2

Test@DLSP > exec: C: = dbms_sqltune.CREATE_TUNING_TASK (SQL_ID= > 'aa8mzbnrzu42f')

PL/SQL procedure successfully completed.

Test@DLSP > exec dbms_sqltune.execute_tuning_task (task_name = >: C)

PL/SQL procedure successfully completed.

Test@DLSP > select dbms_sqltune.report_tuning_task (: C) from dual

DBMS_SQLTUNE.REPORT_TUNING_TASK (: C)

GENERAL INFORMATION SECTION

Tuning Task Name: TASK_980

Tuning Task Owner: TEST

Workload Type: Single SQL Statement

Scope: COMPREHENSIVE

Time Limit (seconds): 1800

Completion Status: COMPLETED

Started at: 07/29/2014 15:48:50

Completed at: 07/29/2014 15:48:51

Schema Name: TEST

SQL ID: aa8mzbnrzu42f

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

Bind Variables:

1-(VARCHAR2 (2000)): Inactive

FINDINGS SECTION (2 findings)

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. The SQL plan baseline

Corresponding to the plan with the SQL profile will also be updated to an

Accepted plan.

Execute dbms_sqltune.accept_sql_profile (task_name = > 'TASK_980'

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):. 002302. 000358 84.44%

CPU Time (s): 002199. 0003 86.35%

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.

After the completion of the tuning task, a report,report is generated. The suggestion is given in the sql profile, let's accept a tuning task to complete the optimization task.

Test@DLSP > execute dbms_sqltune.accept_sql_profile (task_name = > 'TASK_980',task_owner = >' TEST', replace = > TRUE)

PL/SQL procedure successfully completed.

Test@DLSP > select sql_handle, plan_name, origin, accepted,fixed

2 from dba_sql_plan_baselines

3 where sql_text like'% count%'

SQL_HANDLE PLAN_NAME ORIGIN ACCEPT FIXED

SQL_619bd8394153fd05 SQL_PLAN_636ys750p7z856b581ab9 MANUAL-LOAD YES NO

SQL_619bd8394153fd05 SQL_PLAN_636ys750p7z8519ccc485 AUTO-CAPTURE YES NO

Test@DLSP > select * from table (dbms_xplan.display_cursor)

PLAN_TABLE_OUTPUT

- -

SQL_ID aa8mzbnrzu42f, child number 0

-

Select count (name) from test where status=: a

Plan hash value: 4130896540

-

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

-

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

| | 1 | SORT AGGREGATE | | 1 | 25 |

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

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

-

Predicate Information (identified by operation id):

3-access ("STATUS" =: a)

Note

-

-SQL profile SYS_SQLPROF_0147811f30c60000 used for this statement

-SQL plan baseline SQL_PLAN_636ys750p7z8519ccc485 used for this statement

After accepting the SQL PROFILE, the baseline that was previously unacceptable has also become acceptable. Looking at the execution plan after executing the SQL, the index scan has been done, and the Note section of the execution plan shows that the SQL uses both SQL profile and baseline. Here we can simply prove that a SQL statement can use both SQL profile and baseline, and both work. In our above example, although we can see that both SQL profile and baseline are used through the Note section, because the functions of both are the same, both let the execution plan be scanned by the index, so we can not confirm that both of them played a role or which one played a role. We can construct an example:

1) ask SQL profile to do one thing, which baseline didn't do

2) ask baseline to do one thing, which SQL profile didn't do

3) the two things done above are guaranteed not to conflict (for example, a full table scan and an index scan will conflict)

We can leave the baseline still, then delete the generated SQL profile, and then re-generate a SQL profile for the SQL, adding the gather_plan_statistics hint to the SQL.

Test@DLSP > exec dbms_sqltune.drop_sql_profile ('profile_aa8mzbnrzu42f_dwrose')

PL/SQL procedure successfully completed.

Test@DLSP > var a varchar2

Test@DLSP > exec: a: = 'Inactive'

PL/SQL procedure successfully completed.

Test@DLSP > select count (name) from test where status=: a

COUNT (NAME)

-

one hundred

Test@DLSP > @ profile

Enter value for sql_id: aa8mzbnrzu42f

PLAN_TABLE_OUTPUT

SQL_ID aa8mzbnrzu42f, child number 0

-

Select count (name) from test where status=: a

Plan hash value: 4130896540

-

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

-

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

| | 1 | SORT AGGREGATE | | 1 | 25 |

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

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

-

Outline Data

-

/ * +

BEGIN_OUTLINE_DATA

IGNORE_OPTIM_EMBEDDED_HINTS

OPTIMIZER_FEATURES_ENABLE ('11.2.0.3')

DB_VERSION ('11.2.0.3')

OPT_PARAM ('optimizer_dynamic_sampling' 10)

OPT_PARAM ('_ optimizer_skip_scan_enabled' 'false')

ALL_ROWS

OUTLINE_LEAF (@ "SEL$1")

INDEX_RS_ASC (@ "SEL$1"TEST" @ "SEL$1" ("TEST". "STATUS"))

END_OUTLINE_DATA

, /

Predicate Information (identified by operation id):

3-access ("STATUS" =: a)

Note

-

-SQL plan baseline SQL_PLAN_636ys750p7z8519ccc485 used for this statement

40 rows selected.

Enter value for hint_text: gather_plan_statistics

Profile profile_aa8mzbnrzu42f_dwrose created.

Test@DLSP > select count (name) from test where status=: a

COUNT (NAME)

-

one hundred

Test@DLSP > select * from table (dbms_xplan.display_cursor (null,null,'iostats last'))

PLAN_TABLE_OUTPUT

SQL_ID aa8mzbnrzu42f, child number 0

-

Select count (name) from test where status=: a

Plan hash value: 4130896540

-

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

-

| | 0 | SELECT STATEMENT | | 1 | 1 | 000.00 SELECT STATEMENT 00.01 | 102 |

| | 1 | SORT AGGREGATE | | 1 | 1 | 1 | 000.00 SORT AGGREGATE 00.01 | 102 |

| | 2 | TABLE ACCESS BY INDEX ROWID | TEST | 1 | 100 | 100 | 000.00 | 00.01 | 102 |

| | * 3 | INDEX RANGE SCAN | T_IND | 1 | 100 | 100 | 00Rank 00.01 | 2 |

-

Predicate Information (identified by operation id):

3-access ("STATUS" =: a)

Note

-

-SQL profile profile_aa8mzbnrzu42f_dwrose used for this statement

-SQL plan baseline SQL_PLAN_636ys750p7z8519ccc485 used for this statement

At this point, the study on "how to use SPM BASELINE" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.