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

Https://blog.csdn.net/u010719917/article/details/52002679

1. Check the execution plan executed by the wrong SQL and confirm whether it is necessary to use baseline control to execute the plan.

(on the premise that we cannot modify SQL directly, otherwise we can rewrite it directly through SQL)

2. Turn on the baseline capture parameter alter session set optimizer_capture_sql_plan_baselines=TRUE (disable FALSE)

Execute SQL 2 times, capture execution plan, and then close alter session set optimizer_capture_sql_plan_baselines=FALSE

Check the baseline view select * from dba_sql_plan_baselines for confirmation

Customize the correct SQL plan, force to add HINT, and then execute to generate a new SQL_ID, a new execution plan PLAN_ID

Select * from table (dbms_xplan.display_cursor) get SQL_PLAN ID and SQL_ID

Import SQL baselines into the baseline base table (data dictionary)

Declare

K1 pls_integer

Begin

K1R = DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (

Sql_id= > 'b3h69kwfphm3jacks, (ID of the new SQL)

Sql_handle= > 'SQL_7313a4bc2778b751'

Plan_hash_value= > '2949544139'); (new SQL plan)

End

Delete the original SQL baseline

Declare

K1 pls_integer

Begin

K1:=DBMS_SPM.drop_SQL_PLAN_BASELINE (

Sql_handle= > 'SQL_7313a4bc2778b751'

Plan_name= > 'SQL_PLAN_764x4rhmrjdujd8a279cc')

End

Execute the original SQL to verify whether the optimizer has adopted the baseline; similarly, the SQL of the foreground executes the new execution plan during the execution; adjust the baseline retention time and baseline space occupancy select * from dba_sql_management_config

Several ways to create a baseline

1. Automatically capture the baseline. By setting optimizer_cature_sql_plan_baselines to true, the optimizer generates and saves the baseline for more than two repeated SQL statements (can be modified at the system level or session level).

2. Load from the SQL tuning collection, and load the baseline from the SQL tuning collection by using the package dbms_spm.load_plans_from_sqlset

DECLARE

L_plans_loaded PLS_INTEGER

BEGIN

L_plans_loaded: = DBMS_SPM.load_plans_from_sqlset (sqlset_name = > 'my_sqlset')

END

3. Load from the library cache and create a baseline for a statement that is already in the cursor cache through the package dbms_spm.load_plans_from_cursor_cache function

DECLARE

L_plans_loaded PLS_INTEGER

BEGIN

L_plans_loaded: = DBMS_SPM.load_plans_from_cursor_cache (sql_id = > '1fkh93md0802njournal journal planetary hashworthy value= > null)

END

4.

Execute dbms_sqltune.create_sql_plan_baseline (task_name = > 'TASK_4929', owner_name = >' HBJZT', plan_hash_value = > 333597355)

IV. Several states of the baseline

The baseline corresponding to a SQL statement, which I have summarized into three states

1.accepted (acceptable), the optimizer will only consider the execution plan in this baseline.

2.no-accepted (unacceptable), the baseline of this state, which the optimizer does not consider during SQL statement parsing. The baseline of this state must be evolved and verified before it can be considered by the optimizer before it can be transformed into an accepted state.

3.fixed is yes (fixed), and the baseline in this state inherently has the highest priority! Priority over the other two types of baselines. 5. check the baselines.

1. Basic view: dba_sql_plan_baselines, dba_sql_management_config

2. In the underlying view: sqlobj$data, sqlobj$ (save specific hint), view the execution plan statement saved in the baseline as follows:

Select extractvalue (value (d),'/ hint') as outline_hints

From xmltable ('/ outline_data/hint' passing)

(select xmltype (comp_data) as xmlval

From sqlobj$data sod, sqlobj$ so

Where so.signature = sod.signature

And so.plan_id = sod.plan_id

And comp_data is not null

And name like'& baseline_plan_name')) d

3. Use the function to view the details of the baseline:

Select * from table (dbms_xplan.display_sql_plan_baseline (sql_handle= > 'SYS_SQL_11bcd50cd51504e9',plan_name= >' SQL_PLAN_13g6p1maja1790cce5f0e'))

VI. Evolutionary baseline

In order to verify that an execution plan in an unacceptable state in the baseline is more efficient than an execution plan in an acceptable state, it must be verified by evolution that the optimizer needs to execute the SQL statement with a different execution plan, and observe whether the execution plan baseline in the unacceptable state will lead to better performance, if the performance is indeed higher. The baseline of this unacceptable state will be converted to an acceptable state. There are two ways to evolve:

1. Execute the operation manually

SELECT DBMS_SPM.evolve_sql_plan_baseline (sql_handle = > 'SYS_SQL_xxxxxxxxxxxxx') From dual

There are several parameters of time_limit/verify/commit. You can refer to the documentation.

2. The tuning package realizes the automatic evolution of the baseline, which can be understood as starting a scheduling task and periodically checking whether there is an unacceptable state of the baseline that can be evolved.

VII. Modify the baseline

You can modify some properties of the baseline through the dbms_spm.alter_sql_plan_baseline package, mainly the following properties

1.ENABLED: set the value of this property to NO to tell Oracle 11g to temporarily disable a plan, and a SQL plan must be marked as both ENABLED and ACCEPTED, otherwise CBO will ignore it

2.FIXED: set to YES, that plan will be the only option for the optimizer [highest priority], even if a plan may have a lower cost. This allows DBA to undo the default behavior of SMB, which is particularly useful for converting a storage profile to a stable SQL plan baseline, note that when a new plan is added to the SQL plan baseline marked FIXED, the new plan cannot be utilized unless it is declared as FIXED

3.AUTOPURG: set the value of this property to NO to tell Oracle 11g to keep it indefinitely, so you don't have to worry about SMB's automatic cleanup mechanism

4.plan_name: change the name of SQL plan

5.description: change the SQL plan description

Syntax:

SET SERVEROUTPUT ON

DECLARE

V_text PLS_INTEGER

BEGIN

V_text: = DBMS_SPM.alter_sql_plan_baseline (sql_handle = > 'SYS_SQL_xxxxxx',plan_name = >' SYS_SQL_PLAN_xxxxxxxxx'

Attribute_name = > 'fixed',attribute_value = >' YES')

DBMS_OUTPUT.put_line ('Plans Altered:' | | v_text)

END

/

VIII. Migration baseline

Dbms_spm provides several procedures to migrate SQL plan baselines between databases

Create_stgtab_baseline creates a plan baseline to save the table

Pack_stgtab_baseline copies the baseline from the data dictionary to the table in the first step

Unpack_stgtab_baseline copies the baseline from the save table to the data dictionary in the migration database

The process is roughly as follows:

1. Create a user table that holds the contents of the baseline table in the data dictionary

Exec dbms_spm.create_stgtab_baseline (table_name = > 'BASELINE_TEST',table_owner = >' SCOTT',tablespace_name = >'')

2. Insert the contents of the baseline table in the data dictionary into the user table created in the first step.

Exec: I: = dbms_spm.pack_stgtab_baseline (table_name = > 'BASELINE_TEST', table_owner = >' SCOTT')

Note: can be inserted in a variety of ways, such as SQL-related baselines containing specific characters, and sql_handle to accurately identify a baseline, as detailed in the document

3. Migrate the user table through the migration tool

Exp/imp or expdp/impdp

4. Insert the baseline content saved in the migrated user table into the data dictionary of the current database, so as to realize the migration.

Exec: I: = dbms_spm.unpack_stgtab_baseline (table_name = > 'BASELINE_TEST',table_owner = >' SCOTT')

Note: a variety of methods can be supported, as in step 2, see the document for details

IX. Delete the baseline

You can manually delete the baseline in the data dictionary through the dbms_SPM.drop_sql_plan_baseline package

For the baseline used, fixed is the baseline of no and will be automatically deleted after a certain retention period (see dba_sql_management_config view)

The manual deletion method is as follows

SET SERVEROUTPUT ON

DECLARE

V_text PLS_INTEGER

BEGIN

V_text: = DBMS_SPM.drop_sql_plan_baseline (sql_handle = > 'SYS_SQL_7b76323ad90440b9',plan_name = > NULL)

DBMS_OUTPUT.put_line (v_text)

END

/

Fix a SQL statement as our expected execution plan

I usually implement it through the following steps (for reference only)

1. Create a baseline for the SQL statement

2. Add hint Lai Yuxing to the SQL statement to ensure that the execution plan after adding hint to the SQL statement is the same as we expected

3. Add the execution plan generated in step 2 to the baseline created in the first step (note that, as mentioned earlier, a SQL statement can have multiple baselines! )

4. Delete the execution plan created in step 1 of the baseline (so that we can ensure that there is only the execution plan we expect in the baseline, that is, save the execution plan of the step 2 SQL statement)

5. Verify whether it is effective

Follow up with examples to deepen understanding!

Example (fix a SQL statement as our expected execution plan)

First, run two statements with the same structure. The following experiment uses the SQL plan baseline to fix the execution plan of one statement through the execution plan of another statement.

SQL > select sql_handle,plan_name,dbms_lob.substr (sql_text,60,1) sql_text,ACCEPTED from dba_sql_plan_baselines

No rows selected

SQL > alter system flush shared_pool

The system has changed.

SQL > select / * outlinetest2 * / / * + full (dh_stat) * / * from dh_stat where id=711

ID NAME TYPE

711 I_STREAMS_PROCESS_PARAMS1 INDEX

SQL > select / * outlinetest3 * / / * + index (dh_stat) * / * from dh_stat where id=711

ID NAME TYPE

711 I_STREAMS_PROCESS_PARAMS1 INDEX

SQL > select sql_text,sql_id,hash_value,child_number,plan_hash_value,to_char (LAST_ACTIVE_TIME,'hh34:mi:ss') time

2 from v$sql a where sql_text like'% outlinetest%' and sql_text not like'% Vroomsql%'

SQL_TEXT SQL_ID HASH_VALUE CHILD_NUMBER PLAN_HASH_VALUE TIME

Select / * outlinetest2 * / / * + full (dh_stat) * / * from d 4vaj9fgjysy9c 3823925548 0 1845196118 12:27:31

H_stat where id=711

Select / * outlinetest3 * / / * + index (dh_stat) * / * from fm35jcmypb3qu 4250242778 0 2780970545 12:27:41

Dh_stat where id=711

SQL > select * from table (dbms_xplan.display_cursor ('4vaj9fgjysy9c))

PLAN_TABLE_OUTPUT

-

SQL_ID 4vaj9fgjysy9c, child number 0

-

Select / * outlinetest2 * / / * + full (dh_stat) * / * from dh_stat where

Id=711

Plan hash value: 1845196118

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

| | 0 | SELECT STATEMENT | 124100 | | |

| | * 1 | TABLE ACCESS FULL | DH_STAT | 1 | 38 | 124 (1) | 00:00:02 |

Predicate Information (identified by operation id):

1-filter ("ID" = 711)

19 lines have been selected.

SQL > select * from table (dbms_xplan.display_cursor ('fm35jcmypb3qu','',''))

PLAN_TABLE_OUTPUT

-

SQL_ID fm35jcmypb3qu, child number 0

-

Select / * outlinetest3 * / / * + index (dh_stat) * / * from dh_stat where

Id=711

Plan hash value: 2780970545

-

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

-

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

| | 1 | TABLE ACCESS BY INDEX ROWID | DH_STAT | 1 | 38 | 2 (0) | 00:00:01 |

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

-

Predicate Information (identified by operation id):

2-access ("ID" = 711)

20 rows have been selected.

SQL > DECLARE

2 k1 pls_integer

3 begin

4 K1: = DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (

5 sql_id= > '4vaj9fgjysy9c'

6 plan_hash_value= > 1845196118

7)

8 end

9 /

The PL/SQL process completed successfully.

SQL > select sql_handle,plan_name,dbms_lob.substr (sql_text,60,1) sql_text,ACCEPTED from dba_sql_plan_baselines

SQL_HANDLE PLAN_NAME SQL_TEXT ACC

SYS_SQL_11bcd50cd51504e9 SQL_PLAN_13g6p1maja17934f41c8d select / * outlinetest2 * / / * + full (dh_stat) * / * from d YES

H_sta

When the sql plan baseline was first produced, the execution plan could not be found on the first query, and it could not be seen until the second execution, as follows

SQL > select / * outlinetest2 * / / * + full (dh_stat) * / * from dh_stat where id=711

ID NAME TYPE

711 I_STREAMS_PROCESS_PARAMS1 INDEX

SQL > select * from table (dbms_xplan.display_cursor ('4vaj9fgjysy9c))

PLAN_TABLE_OUTPUT

SQL_ID: 4vaj9fgjysy9c cannot be found

SQL > select sql_text,sql_id,hash_value,child_number,plan_hash_value,to_char (LAST_ACTIVE_TIME,'hh34:mi:ss') time

2 from v$sql a where sql_text like'% outlinetest%' and sql_text not like'% Vroomsql%'

SQL_TEXT SQL_ID HASH_VALUE CHILD_NUMBER PLAN_HASH_VALUE TIME

Select / * outlinetest3 * / / * + index (dh_stat) * / * from fm35jcmypb3qu 4250242778 0 2780970545 12:27:41

Dh_stat where id=711

SQL > select / * outlinetest2 * / / * + full (dh_stat) * / * from dh_stat where id=711

ID NAME TYPE

711 I_STREAMS_PROCESS_PARAMS1 INDEX

SQL > select sql_text,sql_id,hash_value,child_number,plan_hash_value,to_char (LAST_ACTIVE_TIME,'hh34:mi:ss') time

2 from v$sql a where sql_text like'% outlinetest%' and sql_text not like'% Vroomsql%'

SQL_TEXT SQL_ID HASH_VALUE CHILD_NUMBER PLAN_HASH_VALUE TIME

Select / * outlinetest2 * / / * + full (dh_stat) * / * from d 4vaj9fgjysy9c 3823925548 0 1845196118 12:30:54

H_stat where id=711

Select / * outlinetest3 * / / * + index (dh_stat) * / * from fm35jcmypb3qu 4250242778 0 2780970545 12:27:41

Dh_stat where id=711

SQL > select * from table (dbms_xplan.display_cursor ('4vaj9fgjysy9c))

PLAN_TABLE_OUTPUT

-

SQL_ID 4vaj9fgjysy9c, child number 0

-

Select / * outlinetest2 * / / * + full (dh_stat) * / * from dh_stat where

Id=711

Plan hash value: 1845196118

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

| | 0 | SELECT STATEMENT | 124100 | | |

| | * 1 | TABLE ACCESS FULL | DH_STAT | 1 | 38 | 124 (1) | 00:00:02 |

Predicate Information (identified by operation id):

1-filter ("ID" = 711)

Note

-

-SQL plan baseline SQL_PLAN_13g6p1maja17934f41c8d used for this statement

23 lines have been selected.

Load the execution plan that meets our expectations into the sql baseline generated for the first time!

SQL > DECLARE

2 k1 pls_integer

3 begin

4 K1: = DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (

5 sql_id= > 'fm35jcmypb3qu'

6 plan_hash_value= > 2780970545 SCL handle = > 'SYS_SQL_11bcd50cd51504e9'

7)

8 end

9 /

The PL/SQL process completed successfully.

As you can see, there are currently two plan_name under SYS_SQL_11bcd50cd51504e9

SQL > select sql_handle,plan_name,dbms_lob.substr (sql_text,60,1) sql_text,ACCEPTED from dba_sql_plan_baselines

SQL_HANDLE PLAN_NAME SQL_TEXT ACC

SYS_SQL_11bcd50cd51504e9 SQL_PLAN_13g6p1maja1790cce5f0e select / * outlinetest2 * / / * + full (dh_stat) * / * from d YES

H_sta

SYS_SQL_11bcd50cd51504e9 SQL_PLAN_13g6p1maja17934f41c8d select / * outlinetest2 * / / * + full (dh_stat) * / * from d YES

H_sta

Delete the first plan_name, that is, remove the version of the execution plan that we don't need!

SQL > DECLARE

K1 pls_integer

Begin

K1: = DBMS_SPM.drop_sql_plan_baseline (sql_handle= > 'SYS_SQL_11bcd50cd51504e9',plan_name= >' SQL_PLAN_13g6p1maja17934f41c8d')

End

/

The PL/SQL process completed successfully.

Through some of the tests below, we can see that the new SQL plan baseline has taken effect normally, just in time the statement contains the full prompt, and the execution plan also takes the index location data.

SQL > select / * outlinetest2 * / / * + full (dh_stat) * / * from dh_stat where id=711

ID NAME TYPE

711 I_STREAMS_PROCESS_PARAMS1 INDEX

SQL > select * from table (dbms_xplan.display_cursor ('4vaj9fgjysy9c))

PLAN_TABLE_OUTPUT

-

SQL_ID 4vaj9fgjysy9c, child number 1

-

Select / * outlinetest2 * / / * + full (dh_stat) * / * from dh_stat where

Id=711

Plan hash value: 2780970545

-

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

-

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

| | 1 | TABLE ACCESS BY INDEX ROWID | DH_STAT | 1 | 38 | 2 (0) | 00:00:01 |

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

-

Predicate Information (identified by operation id):

2-access ("ID" = 711)

Note

-

-SQL plan baseline SQL_PLAN_13g6p1maja1790cce5f0e used for this statement

24 rows have been selected.

You can use dba_sql_plan_baselines to display general information about available SQL plan baselines, or you can display details of executing SQL plan baselines in the following way!

Select * from table (dbms_xplan.display_sql_plan_baseline (sql_handle= > 'SYS_SQL_11bcd50cd51504e9',plan_name= >' SQL_PLAN_13g6p1maja1790cce5f0e'))

PLAN_TABLE_OUTPUT

-

SQL handle: SYS_SQL_11bcd50cd51504e9

SQL text: select / * outlinetest2 * / / * + full (dh_stat) * / * from dh_stat where

Id=711

Plan name: SQL_PLAN_13g6p1maja1790cce5f0e Plan id: 214851342

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

Plan hash value: 2780970545

-

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

-

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

| | 1 | TABLE ACCESS BY INDEX ROWID | DH_STAT | 1 | 38 | 2 (0) | 00:00:01 |

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

-

Predicate Information (identified by operation id):

2-access ("ID" = 711)

26 rows have been selected.

View the collection of hint prompts saved in the SQL plan baseline

SQL > conn / as sysdba

Connected.

SQL > select

2 extractvalue (value (d),'/ hint') as outline_hints

3 from

4 xmltable ('/ outline_data/hint'

5 passing (

6 select

7 xmltype (comp_data) as xmlval

8 from

9 sqlobj$data sod, sqlobj$ so

10 where so.signature = sod.signature

11 and so.plan_id = sod.plan_id

12 and comp_data is not null

13 and name like'& baseline_plan_name'

14)

15) d

Enter the value of baseline_plan_name: SQL_PLAN_13g6p1maja1790cce5f0e

Original value 13: and name like'& baseline_plan_name'

New value 13: and name like 'SQL_PLAN_13g6p1maja1790cce5f0e'

OUTLINE_HINTS

-

IGNORE_OPTIM_EMBEDDED_HINTS

OPTIMIZER_FEATURES_ENABLE ('11.2.0.1')

DB_VERSION ('11.2.0.1')

ALL_ROWS

OUTLINE_LEAF (@ "SEL$1")

INDEX_RS_ASC (@ "SEL$1"DH_STAT" @ "SEL$1" ("DH_STAT". "ID"))

6 rows have been selected.

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.