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

Sql plan baselines

In a mature production system, we always want to run as stably as possible, and execute the plan as firmly as possible. However, due to the change of statistical information, the modification of optimization parameters, the modification of system settings, the creation of profile, etc., it is possible to cause changes in the execution plan, thus there will be changes in performance. This change is not necessarily a bad direction, such as the upgrade of the optimizer version, which generally optimizes the execution plan, but as an operation and maintenance DBA, you may still believe in yourself to make sure you are foolproof.

In response to possible changes in the implementation plan, oracle provides baseline, which we translate as a baseline to ensure the stability of the implementation plan.

The following is illustrated by experiments:

There are two ways to generate sql baseline:

(1) you need to generate AWR reports or sql tuning set (STS), so you first need to check the STS available on the current system:

[oracle@localhost ~] $sqlplus / nolog

SQL*Plus: Release 11.2.0.1.0 Production on Sun Dec 23 00:10:50 2012

Copyright (c) 1982, 2009, Oracle. All rights reserved.

SQL > conn scott/scott

Connected.

SQL > select * from dba_sqlset

ID NAME OWNER DESCRIPTI CREATED LAST_MODI STATEMENT_COUNT

2 my_sql_tuningset SCOTT i/o intensive workload 22-DEC-12 22-DEC-12 0

SQL > declare

2 my_plans pls_integer

3 begin

4 my_plans:=dbms_spm.load_plans_from_sqlset (sqlset_name= > 'my_sql_tuningset')

5 end

6 /

PL/SQL procedure successfully completed.

If you are extracting from an AWR report, you need to generate the STS from the AWR report before performing the above steps.

(2) obtain sql from shared_pool_area

For example, we have just executed select * from dept where deptno=10, and we want to classify this statement into baseline, as follows:

SQL > select sql_id from v$sql where sql_text like 'select * from dept%'

SQL_ID

-

96kbu89824wkq

SQL > declare

2 my_plans pls_integer

3 begin

4 my_plans:=dbms_spm.load_plans_from_cursor_cache (sql_id= > '96kbu89824wkq')

5 end

6 /

PL/SQL procedure successfully completed.

Each time the database compiles a sql statement, the optimizer first finds the optimal plan based on cost, then looks in the baseline to see if there is a matching plan, and if so, uses the matching plan; if not, compares the plans in the baseline to select the one with the lowest cost. The initial optimal plan will not be used, but will be put into the planning history as an unacceptable plan, and the optimal plan just selected will be used if all the plans in the baseline are not available due to system changes.

If oracle confirms that an unacceptable plan will not cause a performance problem (better than the plan in the baseline), it will put it in the baseline.

SQL > show parameter optimizer_use_sql

-- the value of this parameter is true, which means that baselines are allowed

NAME TYPE VALUE

-

Optimizer_use_sql_plan_baselines boolean TRUE

SQL > set autot on

SQL > select * from dept where deptno=10

DEPTNO DNAME LOC

10 ACCOUNTING NEW YORK

Execution Plan

Plan hash value: 2852011669

-

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

-

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

| | 1 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 | 20 | 1 (0) | 00:00:01 |

| | * 2 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0) | 00:00:01 |

-

Predicate Information (identified by operation id):

2-access ("DEPTNO" = 10)

Note

-

-SQL plan baseline "SQL_PLAN_1tkkvscv7fp020348d329" used for this statement

From the above query, we can see that the statement uses the baseline "SQL_PLAN_1tkkvscv7fp020348d329"

Display the contents of the baseline with the following statement:

SQL > select * from table (dbms_xplan.display_sql_plan_baseline (

2 plan_name= > 'SQL_PLAN_1tkkvscv7fp020348d329',format= >' basic'))

PLAN_TABLE_OUTPUT

SQL handle: SYS_SQL_1cca5bc336775402

SQL text: select * from dept where deptno=10

Plan name: SQL_PLAN_1tkkvscv7fp020348d329 Plan id: 55104297

Enabled: YES Fixed: NO Accepted: YES Origin: MANUAL-LOAD

PLAN_TABLE_OUTPUT

Plan hash value: 2852011669

| | Id | Operation | Name | |

| | 0 | SELECT STATEMENT |

| | 1 | TABLE ACCESS BY INDEX ROWID | DEPT |

| | 2 | INDEX UNIQUE SCAN | PK_DEPT |

20 rows selected.

It contains the content of the statement, execution plan and other information.

If you do not know plan_name, you can obtain it in the following ways:

Select * from dba_sql_plan_baselines

Let's take a look at whether the statement can run properly when the index is deleted and cannot be used with baseline, and whether it has an impact on the baseline: (for experimental purposes only, do not delete the primary key in practice)

SQL > alter table dept drop index PK_DEPT

Alter table dept drop index PK_DEPT

*

ERROR at line 1:

ORA-00905: missing keyword

SQL > drop index PK_DEPT

Drop index PK_DEPT

*

ERROR at line 1:

ORA-02429: cannot drop index used for enforcement of unique/primary key

The index cannot be deleted due to primary key constraints

SQL > alter table dept drop constraint pk_dept

Alter table dept drop constraint pk_dept

*

ERROR at line 1:

ORA-02273: this unique/primary key is referenced by some foreign keys

The primary key cannot be deleted due to a foreign key constraint

SQL > drop table emp

Table dropped.

SQL > alter table dept drop constraint pk_dept

Table altered.

SQL > set autot on

SQL > select * from dept where deptno=10

DEPTNO DNAME LOC

10 ACCOUNTING NEW YORK

Execution Plan

Plan hash value: 3383998547

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

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

| | * 1 | TABLE ACCESS FULL | DEPT | 1 | 20 | 3 (0) | 00:00:01 |

Predicate Information (identified by operation id):

1-filter ("DEPTNO" = 10)

There is no way to use the baseline, so we can only scan the whole table. So does the baseline still exist?

SQL > select plan_name from dba_sql_plan_baselines

PLAN_NAME

-

SQL_PLAN_1tkkvscv7fp020348d329

SQL_PLAN_1tkkvscv7fp020e23be79

SQL_PLAN_dxw60bwfynb5h0e23be79

-- the baseline still exists

Even so, after we re-established the index pk_dept, the baseline was no longer available:

SQL > create index pk_dept on dept (deptno)

Index created.

SQL > select * from dept where deptno=10

DEPTNO DNAME LOC

10 ACCOUNTING NEW YORK

Execution Plan

Plan hash value: 2985873453

-

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

-

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

| | 1 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 | 20 | 2 (0) | 00:00:01 |

| | * 2 | INDEX RANGE SCAN | PK_DEPT | 1 | | 1 (0) | 00:00:01 |

-

Predicate Information (identified by operation id):

2-access ("DEPTNO" = 10)

By hand, we can insert the execution plan (corresponding baseline) that we think is not a problem with performance into the system baseline, at which time oracle does not check whether the inserted baseline performance is really good.

We use the above statement to scan the query using the full table and generate a baseline

SQL > set autot on

SQL > select / * + full (dept) * / * from dept where deptno=10

DEPTNO DNAME LOC

10 ACCOUNTING NEW YORK

Execution Plan

Plan hash value: 3383998547

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

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

| | * 1 | TABLE ACCESS FULL | DEPT | 1 | 20 | 3 (0) | 00:00:01 |

Predicate Information (identified by operation id):

1-filter ("DEPTNO" = 10)

SQL > select sql_id from v$sql where sql_text like 'select / * + full (dept) * / * from dept%'

SQL_ID

-

1gbphzt5d0159

SQL > declare

2 plan pls_integer

3 begin

4 plan:=dbms_spm.load_plans_from_cursor_cache (sql_id= > '1gbphzt5d0159')

5 end

6 /

PL/SQL procedure successfully completed.

However, this statement will not be used by select * from dept where deptno=10;. Even the following statements cannot be used for baselines:

SQL > select / * + full (dept) * / * from dept where deptno='10'

DEPTNO DNAME LOC

10 ACCOUNTING NEW YORK

Execution Plan

Plan hash value: 3383998547

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

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

| | * 1 | TABLE ACCESS FULL | DEPT | 1 | 20 | 3 (0) | 00:00:01 |

Predicate Information (identified by operation id):

1-filter ("DEPTNO" = 10)

Oracle believes that two statements are different, and only statements that are exactly the same except for the number of spaces are the same statement.

How do I delete a baseline?

Only the drop_sql_plan_baseline function is provided in dbms_spm

Find the source code of dbms_spm, and the function is declared as follows:

FUNCTION drop_sql_plan_baseline (sql_handle IN VARCHAR2: = NULL

Plan_name IN VARCHAR2: = NULL

)

RETURN PLS_INTEGER

So you need to define a variable of type pls_integer to accept the result:

SQL > declare

Ret pls_integer

Begin

Ret:=dbms_spm.drop_sql_plan_baseline (sql_handle= > 'SYS_SQL_1cca5bc336775402'

Plan_name= > 'SQL_PLAN_1tkkvscv7fp020348d329')

End

/ 2 3 4 5 6

PL/SQL procedure successfully completed.

SQL > select plan_name from dba_sql_plan_baselines

PLAN_NAME

-

SQL_PLAN_1tkkvscv7fp020e23be79

SQL_PLAN_1tkkvscv7fp02da6909c3

SQL_PLAN_dxw60bwfynb5h0e23be79

As you can see, SQL_PLAN_1tkkvscv7fp020348d329 has been deleted

This time mainly talked about the generation, viewing and deletion of baseline. Baseline plays an important role in the stability and performance of sql in the system, so the next part of the experiment is the import and export of baseline.

If you create a baseline for the statement before creating the index, then create the index. At this point, querying by index may be more efficient than a full table scan.

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.