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

This article introduces how to fix the implementation plan in Oracle. The content is very detailed. Interested friends can use it for reference. I hope it will be helpful to you.

1.1 BLOG document structure map

1.2 introduction 1.2.1 introduction and precautions

Technical enthusiasts, after reading this article, you can master the following skills, and you can also learn some other knowledge that you do not know, ~ O (∩ _ ∩) Olympiad:

Common methods of ① fixed execution plan: outline, SQL Profile, SPM (key)

The use of ② coe_xfr_sql_profile.sql scripts

Tips:

① if the article code format is out of order, it is recommended to use QQ, Sogou or 360browser, you can also download the pdf format document to view, pdf document download address: http://yunpan.cn/cdEQedhCs2kFz (extraction code: ed9b)

② I use a gray background and pink font to show the areas that require special attention in the output part of the command in this BLOG. For example, in the following example, the maximum archive log number of thread 1 is 33 thread 2, and the maximum archive log number is 43, which requires special attention; while the command generally uses a yellow background and red font marking; the comments on the code or the output part of the code are generally expressed in blue font.

List of Archived Logs in backup set 11

Thrd Seq Low SCN Low Time Next SCN Next Time

-

1 32 1621589 2015-05-29 11:09:52 1625242 2015-05-29 11:15:48

1 33 1625242 2015-05-29 11:15:48 1625293 2015-05-29 11:15:58

2 42 1613951 2015-05-29 10:41:18 1625245 2015-05-29 11:15:49

2 43 1625245 2015-05-29 11:15:49 1625253 2015-05-29 11:15:53

[ZHLHRDB1:root]: / > lsvg-o

T_XDESK_APP1_vg

Rootvg

[ZHLHRDB1:root]: / >

00:27:22 SQL > alter tablespace idxtbs read write

= "2097152, 512, 1024, 1024, 1024, 1G

If there are any mistakes or imperfections in this article, please correct them as much as you can, ITPUB or QQ. Your criticism is the biggest motivation of my writing.

1.2.2 links to related reference articles

11.2.0.2 A bug of SPM: http://blog.itpub.net/26736162/viewspace-1248506/

How to view SQL Profiles information in 10g/11g: http://blog.itpub.net/26736162/viewspace-2106743/

[OUTLINE] use Oracle Outline technology to temporarily lock down the execution plan of SQL: http://blog.itpub.net/26736162/viewspace-2102180/

1.2.3 introduction to this article

This paper introduces three methods commonly used by oracle in the process of fixed execution plan, outline,SQL Profile and SPM, in which SQL Profile and SPM are the key contents to be mastered.

-Chapter II Three methods of fixed execution Plan introduction to the basics of 2.1outline2.1.1 outline

In actual projects, there is usually no problem with the implementation of some SQL in the development environment, but when there are large variables in the production environment or production environment, the execution efficiency of SQL will be unusually slow. If you change the SQL at this point, you may need to modify the source program and recompile the program. If you feel that it is expensive to modify the source program, you can use OUTLINE to change the execution plan of a particular SQL without changing the original application.

The principle of OUTLINE is to store the tuned SQL execution plan (a series of HINT), and then replace the corresponding SQL with the inefficient SQL of the current system. As a result, every time the system executes the SQL, it will use the stored execution plan to execute. Therefore, the purpose of changing the execution plan of the existing system can be achieved without changing the existing system SQL.

The OUTLINE mode also achieves the stability and change of the execution plan by storing the HINT.

When you find inefficient SQL, you can use hint to optimize it. For cases where SQL code can be modified, you can directly modify SQL code and hint, but for cases where SQL code is unmodifiable, Oracle provides the outLine function to modify hint for SQL, so as to implement planned changes!

? OutLine mechanism:

Outline saves SQL's hint in the outline table. When SQL is executed, Oracle uses hint in outline to generate an execution plan for SQL.

? To use OutLine:

(1) generate 2 Outline of new SQL and old SQL

(2) Exchange prompts for two SQL

(3) ON LOGON trigger sets the CATEGORY of session (custom category)

SQL command behavior: SQL > alter session set use_stored_outlines=special

2.1.2 demonstration of ouline usage

The test process is as follows:

SYS@test > create user lhr identified by lhr

User created.

SYS@test > grant dba to lhr

Grant succeeded.

SYS@test > grant create any outline,alter any outline,DROP ANY OUTLINE to lhr

Grant succeeded.

SYS@test > grant all on OL$HINTS to lhr

Grant succeeded.

SYS@test > conn lhr/lhr

Connected.

LHR@test > select * from v$version

BANNER

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit Production

PL/SQL Release 11.2.0.4.0-Production

CORE 11.2.0.4.0 Production

TNS for IBM/AIX RISC System/6000: Version 11.2.0.4.0-Production

NLSRTL Version 11.2.0.4.0-Production

LHR@test > create table TB_LHR_20160518 as select * from dba_tables

Table created.

LHR@test > create index idx_TB_LHR_20160518 on TB_LHR_20160518 (TABLE_NAME)

Index created.

LHR@test > SET AUTOTRACE ON

LHR@test > select owner from TB_LHR_20160518 where table_name='TB_LHR_20160518'

No rows selected

Execution Plan

Plan hash value: 2186742855

-

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

-

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

| | 1 | TABLE ACCESS BY INDEX ROWID | TB_LHR_20160518 | 1 | 34 | 1 (0) | 00:00:01 |

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

-

Predicate Information (identified by operation id):

2-access ("TABLE_NAME" = 'TB_LHR_20160518')

Note

-

-dynamic sampling used for this statement (level=2)

Statistics

11 recursive calls

0 db block gets

72 consistent gets

8 physical reads

0 redo size

333 bytes sent via SQL*Net to client

508 bytes received via SQL*Net from client

1 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

0 rows processed

LHR@test > select / * + full (TB_LHR_20160518) * / owner from TB_LHR_20160518 where table_name='TB_LHR_20160518'

No rows selected

Execution Plan

Plan hash value: 1750418716

-

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

-

| | 0 | SELECT STATEMENT | | 1 | 34 | 31 (0) | 00:00:01 |

| | * 1 | TABLE ACCESS FULL | TB_LHR_20160518 | 1 | 34 | 31 (0) | 00:00:01 |

-

Predicate Information (identified by operation id):

1-filter ("TABLE_NAME" = 'TB_LHR_20160518')

Note

-

-dynamic sampling used for this statement (level=2)

Statistics

7 recursive calls

0 db block gets

170 consistent gets

0 physical reads

0 redo size

333 bytes sent via SQL*Net to client

508 bytes received via SQL*Net from client

1 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

0 rows processed

LHR@test > set autotrace off

LHR@test > create or replace outline TB_LHR_20160518_1 on select owner from TB_LHR_20160518 where table_name='TB_LHR_20160518'

Outline created.

LHR@test > create or replace outline TB_LHR_20160518_2 on select / * + full (TB_LHR_20160518) * / owner from TB_LHR_20160518 where table_name='TB_LHR_20160518'

Outline created.

LHR@test > select name,USED,sql_text from dba_outlines where name like'% TB_LHR_20160518%'

NAME USED SQL_TEXT

TB_LHR_20160518_1 UNUSED select owner from TB_LHR_20160518 where table_name='TB_LHR_20160518'

TB_LHR_20160518_2 UNUSED select / * + full (TB_LHR_20160518) * / owner from TB_LHR_20160518 where table_name='T

LHR@test > select name,HINT from dba_outline_hints where JOIN_POS=1 and name like'% TB_LHR_20160518%'

NAME HINT

-

TB_LHR_20160518_1 INDEX_RS_ASC (@ "SEL$1"TB_LHR_20160518" @ "SEL$1" ("TB_LHR_20160518". "TABLE_NAME")

TB_LHR_20160518_2 FULL (@ "SEL$1"TB_LHR_20160518" @ "SEL$1")

LHR@test > UPDATE OUTLN.OL$ SET OL_NAME=DECODE (OL_NAME,'TB_LHR_20160518_2','TB_LHR_20160518_1','TB_LHR_20160518_1','TB_LHR_20160518_2') WHERE OL_NAME IN ('TB_LHR_20160518_1','TB_LHR_20160518_2')

2 rows updated.

LHR@test > commit

Commit complete.

LHR@test > select name,USED,sql_text from dba_outlines where name like'% TB_LHR_20160518%'

NAME USED SQL_TEXT

TB_LHR_20160518_1 UNUSED select / * + full (TB_LHR_20160518) * / owner from TB_LHR_20160518 where table_name='T

TB_LHR_20160518_2 UNUSED select owner from TB_LHR_20160518 where table_name='TB_LHR_20160518'

LHR@test > select name,HINT from dba_outline_hints where JOIN_POS=1 and name like'% TB_LHR_20160518%'

NAME HINT

-

TB_LHR_20160518_1 INDEX_RS_ASC (@ "SEL$1"TB_LHR_20160518" @ "SEL$1" ("TB_LHR_20160518". "TABLE_NAME")

TB_LHR_20160518_2 FULL (@ "SEL$1"TB_LHR_20160518" @ "SEL$1")

LHR@test > SET AUTOTRACE ON

LHR@test > alter system set use_stored_outlines=true

System altered.

LHR@test > select owner from TB_LHR_20160518 where table_name='TB_LHR_20160518'

No rows selected

Execution Plan

Plan hash value: 1750418716

-

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

-

| | 0 | SELECT STATEMENT | | 89 | 3026 | 31 (0) | 00:00:01 |

| | * 1 | TABLE ACCESS FULL | TB_LHR_20160518 | 89 | 3026 | 31 (0) | 00:00:01 |

-

Predicate Information (identified by operation id):

1-filter ("TABLE_NAME" = 'TB_LHR_20160518')

Note

-

-outline "TB_LHR_20160518_2" used for this statement

Statistics

34 recursive calls

147 db block gets

125 consistent gets

0 physical reads

624 redo size

333 bytes sent via SQL*Net to client

508 bytes received via SQL*Net from client

1 SQL*Net roundtrips to/from client

2 sorts (memory)

0 sorts (disk)

0 rows processed

LHR@test > select / * + full (TB_LHR_20160518) * / owner from TB_LHR_20160518 where table_name='TB_LHR_20160518'

No rows selected

Execution Plan

Plan hash value: 2186742855

-

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

-

| | 0 | SELECT STATEMENT | | 89 | 3026 | 6 (0) | 00:00:01 |

| | 1 | TABLE ACCESS BY INDEX ROWID | TB_LHR_20160518 | 89 | 3026 | 6 (0) | 00:00:01 |

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

-

Predicate Information (identified by operation id):

2-access ("TABLE_NAME" = 'TB_LHR_20160518')

Note

-

-outline "TB_LHR_20160518_1" used for this statement

Statistics

34 recursive calls

147 db block gets

24 consistent gets

0 physical reads

584 redo size

333 bytes sent via SQL*Net to client

508 bytes received via SQL*Net from client

1 SQL*Net roundtrips to/from client

2 sorts (memory)

0 sorts (disk)

0 rows processed

LHR@test >

2.2 basic knowledge of SQL Profile2.2.1 SQL Profile

In subsequent versions of oracle 11g, the parameter use_stored_outlines no longer exists. It means that we can't use create outline to create a hint for a sql and then use store outline to execute a plan.

SQL Profile is to provide a SQL statement with other information in addition to system statistics, object (table, index, etc.) statistics, such as running environment, additional and more accurate statistics, to help the optimizer choose a more appropriate execution plan for the SQL statement. SQL Profiles can be said to be the evolution of Outlines. SQL Profiles can also fully realize the functions that can be realized by Outlines, while SQL Profiles has the optimization that Outlines does not have, the most important of which are two points:

① SQL Profiles is easier to build, change, and control.

② SQL Profiles is better at supporting SQL statements, that is, it has a wider range of applications.

There are two purposes of using SQL Profiles:

(1) lock or stabilize the implementation of the plan.

(2) make the SQL statement run according to the specified execution plan if the SQL in the application cannot be modified.

Before 10g, sql profile appeared as one of the new features after outlines,10g. If the sql,outlines for unbound variables is not up to standard. The biggest advantage of sql profile is to optimize the execution efficiency of sql without modifying sql statements and session execution environment, which is suitable for the situation that sql can not be modified in the application.

SQL Profile is valid for the following types of statements:

SELECT statement

UPDATE statement

INSERT statement (valid only when using the SELECT clause)

DELETE statement

CREATE statement (valid only when using the SELECT clause)

MERGE statement (valid only for UPDATE and INSERT operations).

In addition, you must have system permissions such as CREATE ANY SQL PROFILE, DROP ANY SQL PROFILE, and ALTER ANY SQL PROFILE to use SQL Profile.

2.2.2 demonstration of SQL Profile usage

There are two ways to generate SQL Profile, manually and using STA.

2.2.2.1 example of using SQL Profile-manual creation of SQL Profile

Create a test table, created according to DBA_OBJECTS, with indexes on OBJECT_ID

LHR@dlhr > select * from v$version

BANNER

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit Production

PL/SQL Release 11.2.0.4.0-Production

CORE 11.2.0.4.0 Production

TNS for IBM/AIX RISC System/6000: Version 11.2.0.4.0-Production

NLSRTL Version 11.2.0.4.0-Production

LHR@dlhr > Create table TB_LHR_20160525 as select * from dba_objects

Table created.

LHR@dlhr > create index IND_TB_LHR_ID on TB_LHR_20160525 (object_id)

Index created.

Check the default execution plan of SQL, go to the index, and get the hint generated for us by specifying outline.

LHR@dlhr > explain plan for select * from TB_LHR_20160525 where object_id=: a

Explained.

LHR@dlhr > select * from table (dbms_xplan.display (null,null,'outline'))

PLAN_TABLE_OUTPUT

-

Plan hash value: 4254050152

-

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

-

| | 0 | SELECT STATEMENT | | 886 | 179K | 7 (0) | 00:00:01 |

| | 1 | TABLE ACCESS BY INDEX ROWID | TB_LHR_20160525 | 886 | 179k | 7 (0) | 00:00:01 |

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

-

Outline Data

-

/ * +

BEGIN_OUTLINE_DATA

INDEX_RS_ASC (@ "SEL$1"TB_LHR_20160525" @ "SEL$1" ("TB_LHR_20160525". "OBJECT_ID"))

OUTLINE_LEAF (@ "SEL$1")

ALL_ROWS

DB_VERSION ('11.2.0.4')

OPTIMIZER_FEATURES_ENABLE ('11.2.0.4')

IGNORE_OPTIM_EMBEDDED_HINTS

END_OUTLINE_DATA

, /

Predicate Information (identified by operation id):

2-access ("OBJECT_ID" = TO_NUMBER (: a))

Note

-

-dynamic sampling used for this statement (level=2)

32 rows selected.

If we want it to run a full table scan, first get the full table scan HINT

LHR@dlhr > explain plan for select / * + full (TB_LHR_20160525) * / * from TB_LHR_20160525 where object_id=: a

Explained.

LHR@dlhr > select * from table (dbms_xplan.display (null,null,'outline'))

PLAN_TABLE_OUTPUT

-

Plan hash value: 345881005

-

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

-

| | 0 | SELECT STATEMENT | | 886 | 179K | 352 (2) | 00:00:05 |

| | * 1 | TABLE ACCESS FULL | TB_LHR_20160525 | 886 | 179K | 352 (2) | 00:00:05 |

-

Outline Data

-

/ * +

BEGIN_OUTLINE_DATA

FULL (@ "SEL$1"TB_LHR_20160525" @ "SEL$1")

OUTLINE_LEAF (@ "SEL$1")

ALL_ROWS

DB_VERSION ('11.2.0.4')

OPTIMIZER_FEATURES_ENABLE ('11.2.0.4')

IGNORE_OPTIM_EMBEDDED_HINTS

END_OUTLINE_DATA

, /

Predicate Information (identified by operation id):

1-filter ("OBJECT_ID" = TO_NUMBER (: a))

Note

-

-dynamic sampling used for this statement (level=2)

31 rows selected.

You can see that the full table scan hint has been generated for us, we select the necessary hint to OK, the rest can not be used, use sql profile

LHR@dlhr > declare

2 v_hints sys.sqlprof_attr

3 begin

4 v_hints: = sys.sqlprof_attr ('FULL (@ "SEL$1"TB_LHR_20160525" @ "SEL$1")');-the HINT obtained from the above Outline Data section

5 dbms_sqltune.import_sql_profile ('select * from TB_LHR_20160525 where object_id=: a SQL,-SQL statement part

6 v_hints

The name of 7 'TB_LHR_20160525',-PROFILE

8 force_match = > true)

9 end

10 /

PL/SQL procedure successfully completed.

Check to see if it is in effect. It has already taken effect:

LHR@dlhr > explain plan for select * from TB_LHR_20160525 where object_id=: a

Explained.

LHR@dlhr > select * from table (dbms_xplan.display)

PLAN_TABLE_OUTPUT

-

Plan hash value: 345881005

-

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

-

| | 0 | SELECT STATEMENT | | 886 | 179K | 352 (2) | 00:00:05 |

| | * 1 | TABLE ACCESS FULL | TB_LHR_20160525 | 886 | 179K | 352 (2) | 00:00:05 |

-

Predicate Information (identified by operation id):

1-filter ("OBJECT_ID" = TO_NUMBER (: a))

Note

-

-dynamic sampling used for this statement (level=2)

-SQL profile "TB_LHR_20160525" used for this statement

18 rows selected.

LHR@dlhr > SELECT b. Name. Sqlcircle text, extractvalue (value (h),'.') As hints

2 FROM dba_sql_profiles djinghe SYS.SQLOBJust data A

3 SYS.SQLOBJ$ B

4 TABLE (XMLSEQUENCE (EXTRACT (XMLTYPE (A.COMP_DATA))

5'/ outline_data/hint')) h

6 where a.signature = b.signature

7 and a.category = b.category

8 and a.obj_type = b.obj_type

9 and a.plan_id = b.plan_id

10 and a.signature=d.signature

11 and D.name = 'TB_LHR_20160525'

NAME SQL_TEXT HINTS

TB_LHR_20160525 select * from TB_LHR_20160525 where object_id=: a FULL (@ "SEL$1"TB_LHR_20160525" @ "SEL$1")

LHR@dlhr >

First, use sqlprof_attr

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.