One .1  BLOG Document structure chart

wps73.tmp 

One .2   Preface

 

One .2.1   Guidance and notes

Technology enthusiasts , After reading this article , You can master the following skills , You can also learn something else you don't know ,~O(∩_∩)O~:

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

② coe_xfr_sql_profile.sql Use of scripts

 

  Tips:

       ①  If the article code format is wrong , Recommended QQ、 Sogou or 360 browser , You can also download pdf Format to view ,pdf Document download address :http://yunpan.cn/cdEQedhCs2kFz ( Extraction code :ed9b) 

       ②  This article BLOG I use gray background and pink font to show the output part of the command that needs special attention , For example, in the following example ,thread 1 The maximum archive log number for is 33,thread 2 The maximum archive log number for is 43 It's something that needs special attention ; Commands are usually marked with yellow background and red font ; Comments on the code or the output part of the code are generally 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 ,ITPUB Message or QQ All can , Your criticism is the biggest motivation of my writing .

 

 

One .2.2   Links to related reference articles

11.2.0.2 Of SPM One of the bug :http://blog.itpub.net/26736162/viewspace-1248506/

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

【OUTLINE】 Use Oracle Outline Technology is temporarily locked in SQL Implementation plan of :http://blog.itpub.net/26736162/viewspace-2102180/

 

 

 

 

One .2.3   Brief introduction

This paper introduces oracle Often used in the process of a fixed execution plan 3 Methods ,outline,SQL Profile and SPM, among SQL Profile and SPM It's the key thing to master .

 

 

---------------------------------------------------------------------------------------------------------------------

 

 

Chapter two   Three methods of fixed execution plan

Two .1  outline

Two .1.1  outline Basic knowledge of

In the actual project , Usually in the development environment, some SQL  There's no problem with execution , When the production environment or the amount of data in the production environment changes a lot , Its SQL  The execution efficiency of will be extremely slow . At this point, if you change SQL , You may need to modify the source program and recompile the program . If you think the cost of modifying the source program is relatively large , You can use OUTLINE Change specific... Without changing the original application SQL  Implementation plan of .

OUTLINE The principle of this is to adjust it well SQL  Implementation plan of ( A series of HINT) Store it up , Then the execution plan corresponds to SQL  With the current system that's inefficient SQL  Instead of . So that the system can execute the SQL  when , Will use the stored execution plan to execute . So it can be done without changing the existing system SQL  To achieve the purpose of changing its implementation plan under the circumstances of the situation .

OUTLINE The way is also through storage HINT To achieve stability and change in the implementation plan .

When it's found that SQL after , have access to hint Optimization of he , about SQL Where code can be modified , Directly modifying SQL Code plus hint that will do , But for SQL When the code is not modifiable ,Oracle Provides outLine Function for SQL modify hint, So that the implementation plan changes !

 

ØOutLine Mechanism :

Outline Save the SQL Of hint stay outline In the table of . When executed SQL when ,Oracle Will use outline Medium hint for SQL Generate execution plan .

Ø Use   OutLine Steps for :

(1) Generate new SQL Old man SQL Of 2 individual Outline

(2) Two exchanges SQL A reminder of

(3) ON LOGON Trigger settings session Of CATEGORY( Custom categories )

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

Two .1.2  ouline Using the demonstration

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(@"SEL1""TBLHR20160518"@"SEL1""TBLHR20160518"@"SEL1" ("TB_LHR_20160518"."TABLE_NAME")

TB_LHR_20160518_2              FULL(@"SEL1""TBLHR20160518"@"SEL1""TBLHR20160518"@"SEL1")

 

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(@"SEL1""TBLHR20160518"@"SEL1""TBLHR20160518"@"SEL1" ("TB_LHR_20160518"."TABLE_NAME")

TB_LHR_20160518_2              FULL(@"SEL1""TBLHR20160518"@"SEL1""TBLHR20160518"@"SEL1")

 

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>

 

 

 

Two .2  SQL Profile

Two .2.1  SQL Profile Basic knowledge of

stay oracle 11g In subsequent versions of ,use_stored_outlines This parameter no longer exists . It means we can't use it like in previous versions create outline The way to come for a sql establish hint, And then use store outline To fix the execution plan .

SQL Profile It's for a certain person SQL Statement provides in addition to system statistics 、 object ( Tables and indexes, etc ) Information other than statistics , For example, the operating environment 、 Extra, more accurate statistics , Help optimizer for SQL Statement to choose a more suitable execution plan .SQL Profiles Can be said to be Outlines Evolution .Outlines Functions that can be realized SQL Profiles And it can be realized , and SQL Profiles have Outlines Optimization that doesn't have , The two most important points : 

① SQL Profiles It's easier to generate 、 Change and control . 

② SQL Profiles In the face of SQL Better statement support , That is to say, the scope of application is wider . 

 

Use SQL Profiles Two purposes : 

( One )   Lock in or stabilize the execution plan . 

( Two )   In an application that cannot be modified SQL In the case of SQL Statement runs according to the specified execution plan . 

 10g Before a outlines,10g after sql profile As one of the new features . If for unbound variables sql,outlines It's not enough .sql profile The biggest advantage is that it doesn't change sql Statement and session execution environment sql Efficiency of execution , It can't be modified in the application sql when .

SQL Profile Valid for the following types of statements :

     SELECT sentence ;

     UPDATE sentence ;

     INSERT sentence ( Only when using SELECT Clause );

     DELETE sentence ;

     CREATE sentence ( Only when using SELECT Clause );

     MERGE sentence ( Just as UPDATE and INSERT It is effective in operation ).

in addition , Use SQL Profile There has to be CREATE ANY SQL PROFILE、DROP ANY SQL PROFILE and ALTER ANY SQL PROFILE And so on .

 

Two .2.2  SQL Profile Using the demonstration

Yes 2 Speciation SQL Profile Methods , Manual and use STA To generate .

Two .2.2.1  SQL Profile Examples of use -- Create... By hand SQL Profile

Create test table , according to DBA_OBJECTS establish ,OBJECT_ID There's an index on

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.

 

 

see SQL Default execution plan , The index is gone , By designation outline You can get what the system generated for us hint

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(@"SEL1""TBLHR20160525"@"SEL1""TBLHR20160525"@"SEL1" ("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 go full meter scanning , 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(@"SEL1""TBLHR20160525"@"SEL1""TBLHR20160525"@"SEL1")

      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 the full table scan hint Has generated for us , We choose the necessary hint Just OK 了 , The rest can be avoided , Use sql profile

 

LHR@dlhr> declare

  2        v_hints sys.sqlprof_attr;

  3  begin

  4        v_hints := sys.sqlprof_attr('FULL(@"SEL1""TBLHR20160525"@"SEL1""TBLHR20160525"@"SEL1")'); ---------- From above Outline Data Part of it HINT

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

  6                                 v_hints,

  7                                 'TB_LHR_20160525', --------PROFILE  Name

  8                                  force_match => true);

  9  end;

10  /

 

PL/SQL procedure successfully completed.

 

 

See if it works , It's in 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,d.sql_text,  extractvalue(value(h),'.') as hints

  2     FROM dba_sql_profiles d,SYS.SQLOBJ$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(@"SEL1""TBLHR20160525"@"SEL1""TBLHR20160525"@"SEL1")

 

LHR@dlhr>

 

 

 

 

One 、  Use coe_xfr_sql_profile.sql Script generation sqlprof_attr data

The most troublesome sqlprof_attr('FULL(t1@SEL$1)') It's how to write the format here . stay mos Articles on note 215187.1 Medium sqlt.zip The catalog of utl Scripts are provided in coe_xfr_sql_profile.sql You can generate this information .


1. Build test tables and data

SYS@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 scott.test as select * from dba_objects;

 

Table created.

 

LHR@dlhr> create index scott.idx_test_01 on scott.test(object_id);

 

Index created.

 

LHR@dlhr> exec dbms_stats.gather_table_stats('scott','test',cascade=>true);

 

PL/SQL procedure successfully completed.

 

LHR@dlhr> update scott.test set object_id=10 where object_id>10;

 

 

LHR@dlhr> commit;

Commit complete.

 

 

 

LHR@dlhr> select OBJECT_ID ,count(1) from scott.test group by OBJECT_ID;

 

OBJECT_ID   COUNT(1)

---------- ----------

         6          1

         7          1

         5          1

         8          1

         3          1

         2          1

        10      87076

         4          1

         9          1

 

9 rows selected.

 

 


2. Execute query statement  
-- Execute the original query statement , Look at the execution plan and find the index , In fact, most of the branches in the table are object_id Have been updated to 10, So it's not reasonable to index .

LHR@dlhr>

LHR@dlhr> set autot traceonly explain stat

LHR@dlhr>

LHR@dlhr> select * from scott.test where object_id=10;

 

87076 rows selected.

 

 

Execution Plan

----------------------------------------------------------

Plan hash value: 3384190782

 

-------------------------------------------------------------------------------------------

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

-------------------------------------------------------------------------------------------

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

|   1 |  TABLE ACCESS BY INDEX ROWID| TEST        |     1 |    98 |     2   (0)| 00:00:01 |

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

-------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   2 - access("OBJECT_ID"=10)

 

 

Statistics

----------------------------------------------------------

          0  recursive calls

          0  db block gets

      13060  consistent gets

          0  physical reads

          0  redo size

    9855485  bytes sent via SQL*Net to client

      64375  bytes received via SQL*Net from client

       5807  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

      87076  rows processed

 

LHR@dlhr> select /*+ full(test)*/* from scott.test where object_id=10;

 

87076 rows selected.

 

 

Execution Plan

----------------------------------------------------------

Plan hash value: 217508114

 

--------------------------------------------------------------------------

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

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |     1 |    98 |   351   (2)| 00:00:05 |

|*  1 |  TABLE ACCESS FULL| TEST |     1 |    98 |   351   (2)| 00:00:05 |

--------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   1 - filter("OBJECT_ID"=10)

 

 

Statistics

----------------------------------------------------------

          1  recursive calls

          0  db block gets

       6973  consistent gets

          0  physical reads

          0  redo size

    4159482  bytes sent via SQL*Net to client

      64375  bytes received via SQL*Net from client

       5807  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

      87076  rows processed

 

 


3. Query the... Of the above two statements sql_id,plan_hash_value

 

LHR@dlhr> set autot off

LHR@dlhr>

LHR@dlhr> col sql_text format a100

LHR@dlhr> select sql_text,sql_id,plan_hash_value from v$sql

  2  where sql_text like 'select * from scott.test where object_id=10%';

 

SQL_TEXT                                                                                             SQL_ID        PLAN_HASH_VALUE

---------------------------------------------------------------------------------------------------- ------------- ---------------

select * from scott.test where object_id=10                                                          cpk9jsg2qt52r      3384190782

 

LHR@dlhr> select sql_text,sql_id,plan_hash_value from v$sql

  2  where sql_text like 'select /*+ full(test)*/* from scott.test where object_id=10%';

 

SQL_TEXT                                                                                             SQL_ID        PLAN_HASH_VALUE

---------------------------------------------------------------------------------------------------- ------------- ---------------

select /*+ full(test)*/* from scott.test where object_id=10                                          06c2mucgn6t5g       217508114

 

 

 


4. hold coe_xfr_sql_profile.sql Put it in $ORACLE_HOME/rdbms/admin Next , Or put it in /tmp You can do it all the time .

wps83.tmp


5. For the two above sql produce outline data Of sql.

[ZHLHRSPMDB2:oracle]:/oracle>cd /tmp

[ZHLHRSPMDB2:oracle]:/tmp>

[ZHLHRSPMDB2:oracle]:/tmp>

[ZHLHRSPMDB2:oracle]:/tmp>

[ZHLHRSPMDB2:oracle]:/tmp>

[ZHLHRSPMDB2:oracle]:/tmp>sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.4.0 Production on Thu May 26 09:15:14 2016

 

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

 

 

Connected to:

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

With the Partitioning, Real Application Clusters, OLAP, Data Mining

and Real Application Testing options

 

SYS@dlhr> @$ORACLE_HOME/rdbms/admin/coe_xfr_sql_profile.sql cpk9jsg2qt52r 3384190782

 

Parameter 1:

SQL_ID (required)

 

 

 

PLAN_HASH_VALUE AVG_ET_SECS

--------------- -----------

     3384190782        .046

 

Parameter 2:

PLAN_HASH_VALUE (required)

 

 

Values passed to coe_xfr_sql_profile:

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SQL_ID         : "cpk9jsg2qt52r"

PLAN_HASH_VALUE: "3384190782"

 

SQL>BEGIN

  2    IF :sql_text IS NULL THEN

  3      RAISE_APPLICATION_ERROR(-20100, 'SQL_TEXT for SQL_ID &&sql_id. was not found in memory (gv$sqltext_with_newlines) or AWR (dba_hist_sqltext).');

  4    END IF;

  5  END;

  6  /

SQL>SET TERM OFF;

SQL>BEGIN

  2    IF :other_xml IS NULL THEN

  3      RAISE_APPLICATION_ERROR(-20101, 'PLAN for SQL_ID &&sql_id. and PHV &&plan_hash_value. was not found in memory (gv$sql_plan) or AWR (dba_hist_sql_plan).');

  4    END IF;

  5  END;

  6  /

SQL>SET TERM OFF;

 

Execute coe_xfr_sql_profile_cpk9jsg2qt52r_3384190782.sql

on TARGET system in order to create a custom SQL Profile

with plan 3384190782 linked to adjusted sql_text.

 

 

COE_XFR_SQL_PROFILE completed.

 

SQL>@$ORACLE_HOME/rdbms/admin/coe_xfr_sql_profile.sql 06c2mucgn6t5g 217508114

 

Parameter 1:

SQL_ID (required)

 

 

 

PLAN_HASH_VALUE AVG_ET_SECS

--------------- -----------

      217508114        .113

 

Parameter 2:

PLAN_HASH_VALUE (required)

 

 

Values passed to coe_xfr_sql_profile:

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SQL_ID         : "06c2mucgn6t5g"

PLAN_HASH_VALUE: "217508114"

 

SQL>BEGIN

  2    IF :sql_text IS NULL THEN

  3      RAISE_APPLICATION_ERROR(-20100, 'SQL_TEXT for SQL_ID &&sql_id. was not found in memory (gv$sqltext_with_newlines) or AWR (dba_hist_sqltext).');

  4    END IF;

  5  END;

  6  /

SQL>SET TERM OFF;

SQL>BEGIN

  2    IF :other_xml IS NULL THEN

  3      RAISE_APPLICATION_ERROR(-20101, 'PLAN for SQL_ID &&sql_id. and PHV &&plan_hash_value. was not found in memory (gv$sql_plan) or AWR (dba_hist_sql_plan).');

  4    END IF;

  5  END;

  6  /

SQL>SET TERM OFF;

 

Execute coe_xfr_sql_profile_06c2mucgn6t5g_217508114.sql

on TARGET system in order to create a custom SQL Profile

with plan 217508114 linked to adjusted sql_text.

 

 

COE_XFR_SQL_PROFILE completed.

 


6. Replace file coe_xfr_sql_profile_cpk9jsg2qt52r_3384190782.sql Medium SYS.SQLPROF_ATTR part , Change it to  
coe_xfr_sql_profile_06c2mucgn6t5g_217508114.sql The result of SYS.SQLPROF_ATTR part , among :


coe_xfr_sql_profile_cpk9jsg2qt52r_3384190782.sql Of SYS.SQLPROF_ATTR

h := SYS.SQLPROF_ATTR(

q'[BEGIN_OUTLINE_DATA]',

q'[IGNORE_OPTIM_EMBEDDED_HINTS]',

q'[OPTIMIZER_FEATURES_ENABLE('11.2.0.4')]',

q'[DB_VERSION('11.2.0.4')]',

q'[ALL_ROWS]',

q'[OUTLINE_LEAF(@"SEL$1")]',

q'[INDEX_RS_ASC(@"SEL1""TEST"@"SEL1""TEST"@"SEL1" ("TEST"."OBJECT_ID"))]',

q'[END_OUTLINE_DATA]');

 

----coe_xfr_sql_profile_06c2mucgn6t5g_217508114.sql Of SYS.SQLPROF_ATTR

h := SYS.SQLPROF_ATTR(

q'[BEGIN_OUTLINE_DATA]',

q'[IGNORE_OPTIM_EMBEDDED_HINTS]',

q'[OPTIMIZER_FEATURES_ENABLE('11.2.0.4')]',

q'[DB_VERSION('11.2.0.4')]',

q'[ALL_ROWS]',

q'[OUTLINE_LEAF(@"SEL$1")]',

q'[FULL(@"SEL1""TEST"@"SEL1""TEST"@"SEL1")]',

q'[END_OUTLINE_DATA]');

 

The generated files are in the current directory :

wps94.tmp

 

wps95.tmp

 

7. The replacement has been carried out SYS.SQLPROF_ATTR Of SQL,coe_xfr_sql_profile_cpk9jsg2qt52r_3384190782.sql

SQL> @/tmp/coe_xfr_sql_profile_cpk9jsg2qt52r_3384190782.sql 

SQL>@coe_xfr_sql_profile_cpk9jsg2qt52r_3384190782.sql

SQL>REM

SQL>REM Header:215187.1coexfrsqlprofilecpk9jsg2qt52r3384190782.sql11.4.4.42016/05/26carlos.sierraHeader:215187.1coexfrsqlprofilecpk9jsg2qt52r3384190782.sql11.4.4.42016/05/26carlos.sierra

SQL>REM

SQL>REM Copyright (c) 2000-2012, Oracle Corporation. All rights reserved.

SQL>REM

SQL>REM AUTHOR

SQL>REM   carlos.sierra@oracle.com

SQL>REM

SQL>REM SCRIPT

SQL>REM   coe_xfr_sql_profile_cpk9jsg2qt52r_3384190782.sql

SQL>REM

SQL>REM DESCRIPTION

SQL>REM   This script is generated by coe_xfr_sql_profile.sql

SQL>REM   It contains the SQL*Plus commands to create a custom

SQL>REM   SQL Profile for SQL_ID cpk9jsg2qt52r based on plan hash

SQL>REM   value 3384190782.

SQL>REM   The custom SQL Profile to be created by this script

SQL>REM   will affect plans for SQL commands with signature

SQL>REM   matching the one for SQL Text below.

SQL>REM   Review SQL Text and adjust accordingly.

SQL>REM

SQL>REM PARAMETERS

SQL>REM   None.

SQL>REM

SQL>REM EXAMPLE

SQL>REM   SQL> START coe_xfr_sql_profile_cpk9jsg2qt52r_3384190782.sql;

SQL>REM

SQL>REM NOTES

SQL>REM   1. Should be run as SYSTEM or SYSDBA.

SQL>REM   2. User must have CREATE ANY SQL PROFILE privilege.

SQL>REM   3. SOURCE and TARGET systems can be the same or similar.

SQL>REM   4. To drop this custom SQL Profile after it has been created:

SQL>REM  EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE('coe_cpk9jsg2qt52r_3384190782');

SQL>REM   5. Be aware that using DBMS_SQLTUNE requires a license

SQL>REM  for the Oracle Tuning Pack.

SQL>REM   6. If you modified a SQL putting Hints in order to produce a desired

SQL>REM  Plan, you can remove the artifical Hints from SQL Text pieces below.

SQL>REM  By doing so you can create a custom SQL Profile for the original

SQL>REM  SQL but with the Plan captured from the modified SQL (with Hints).

SQL>REM

SQL>WHENEVER SQLERROR EXIT SQL.SQLCODE;

SQL>REM

SQL>VAR signature NUMBER;

SQL>VAR signaturef NUMBER;

SQL>REM

SQL>DECLARE

  2  sql_txt CLOB;

  3  h       SYS.SQLPROF_ATTR;

  4  PROCEDURE wa (p_line IN VARCHAR2) IS

  5  BEGIN

  6  DBMS_LOB.WRITEAPPEND(sql_txt, LENGTH(p_line), p_line);

  7  END wa;

  8  BEGIN

  9  DBMS_LOB.CREATETEMPORARY(sql_txt, TRUE);

10  DBMS_LOB.OPEN(sql_txt, DBMS_LOB.LOB_READWRITE);

11  -- SQL Text pieces below do not have to be of same length.

12  -- So if you edit SQL Text (i.e. removing temporary Hints),

13  -- there is no need to edit or re-align unmodified pieces.

14  wa(q'[select * from scott.test where object_id=10]');

15  DBMS_LOB.CLOSE(sql_txt);

16  h := SYS.SQLPROF_ATTR(

17  q'[BEGIN_OUTLINE_DATA]',

18  q'[IGNORE_OPTIM_EMBEDDED_HINTS]',

19  q'[OPTIMIZER_FEATURES_ENABLE('11.2.0.4')]',

20  q'[DB_VERSION('11.2.0.4')]',

21  q'[ALL_ROWS]',

22  q'[OUTLINE_LEAF(@"SEL$1")]',

23  q'[FULL(@"SEL1""TEST"@"SEL1""TEST"@"SEL1")]',

24  q'[END_OUTLINE_DATA]');

25  :signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt);

26  :signaturef := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt, TRUE);

27  DBMS_SQLTUNE.IMPORT_SQL_PROFILE (

28  sql_text    => sql_txt,

29  profile     => h,

30  name        => 'coe_cpk9jsg2qt52r_3384190782',

31  description => 'coe cpk9jsg2qt52r 3384190782 '||:signature||' '||:signaturef||'',

32  category    => 'DEFAULT',

33  validate    => TRUE,

34  replace     => TRUE,

35  force_match => FALSE /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ );

36  DBMS_LOB.FREETEMPORARY(sql_txt);

37  END;

38  /

 

PL/SQL procedure successfully completed.

 

SQL>WHENEVER SQLERROR CONTINUE

SQL>SET ECHO OFF;

 

            SIGNATURE

---------------------

10910590721604799112

 

 

           SIGNATUREF

---------------------

15966118871002195466

 

 

... manual custom SQL Profile has been created

 

 

COE_XFR_SQL_PROFILE_cpk9jsg2qt52r_3384190782 completed


8. Look at the resulting sql profile, At this time, the original sentence is not added hint I also swept the whole watch  
select * from dba_sql_profiles;

 

SYS@dlhr> col sql_text for a50

SYS@dlhr> col hints for a50

SYS@dlhr>  SELECT b.name,to_char(d.sql_text) sql_text,  extractvalue(value(h),'.') as hints

  2     FROM dba_sql_profiles d,SYS.SQLOBJ$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 = 'coe_cpk9jsg2qt52r_3384190782';

 

NAME                           SQL_TEXT                                           HINTS

------------------------------ -------------------------------------------------- --------------------------------------------------

coe_cpk9jsg2qt52r_3384190782   select * from scott.test where object_id=10        BEGIN_OUTLINE_DATA

coe_cpk9jsg2qt52r_3384190782   select * from scott.test where object_id=10        IGNORE_OPTIM_EMBEDDED_HINTS

coe_cpk9jsg2qt52r_3384190782   select * from scott.test where object_id=10        OPTIMIZER_FEATURES_ENABLE('11.2.0.4')

coe_cpk9jsg2qt52r_3384190782   select * from scott.test where object_id=10        DB_VERSION('11.2.0.4')

coe_cpk9jsg2qt52r_3384190782   select * from scott.test where object_id=10        ALL_ROWS

coe_cpk9jsg2qt52r_3384190782   select * from scott.test where object_id=10        OUTLINE_LEAF(@"SEL$1")

coe_cpk9jsg2qt52r_3384190782   select * from scott.test where object_id=10        FULL(@"SEL1""TEST"@"SEL1""TEST"@"SEL1")

coe_cpk9jsg2qt52r_3384190782   select * from scott.test where object_id=10        END_OUTLINE_DATA

 

8 rows selected.

 

SYS@dlhr>

 

 

9. verification SQL Profile Whether to take effect

 

SYS@dlhr> set autot traceonly explain stat

SYS@dlhr> select * from scott.test where object_id=10;

 

87076 rows selected.

 

 

Execution Plan

----------------------------------------------------------

Plan hash value: 217508114

 

--------------------------------------------------------------------------

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

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |     1 |    98 |   351   (2)| 00:00:05 |

|*  1 |  TABLE ACCESS FULL| TEST |     1 |    98 |   351   (2)| 00:00:05 |

--------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   1 - filter("OBJECT_ID"=10)

 

Note

-----

   - SQL profile "coe_cpk9jsg2qt52r_3384190782" used for this statement

 

 

Statistics

----------------------------------------------------------

          0  recursive calls

          0  db block gets

       6973  consistent gets

          0  physical reads

          0  redo size

    4159482  bytes sent via SQL*Net to client

      64375  bytes received via SQL*Net from client

       5807  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

      87076  rows processed



Be careful : 
1. This test is just to demonstrate passing coe_xfr_sql_profile.sql Realize manual add hint Methods , In fact, the best way to deal with the above statement problem is to collect again scott.test That's the right statistics . 
2. When one sql both sql profile And then there is stored outline when , Optimizer preference stored outline. 
3.force_match Parameters ,TRUE:FORCE (match even when different literals in SQL),FALSE:EXACT (similar to CURSOR_SHARING). 
4. adopt sql profile Manually add hint The method is very simple , But for sql Add the most reasonable hint That's the key . 
5. After the test , Can pass  exec dbms_sqltune.drop_sql_profile(name =>'coe_cpk9jsg2qt52r_3384190782' ); Delete this sql profile.

6. perform coe_xfr_sql_profile.sql When scripting, users need to have permission to generate files in the current directory , The best current directory is /tmp



Two .2.2.2  SQL Profile Examples of use -- Use STA To generate SQL Profile

utilize STA After optimizing the statement ,STA Will analyze the statement , Adopt the optimal optimization strategy , And give the optimized query plan . You can follow STA The suggestion given is to rewrite the statement . however , In some cases , You may not be able to rewrite statements ( For example, in a production environment , Your statement is in another package ). This is the time to use sql profile, Store optimization strategies in profile in ,Oracle When building the query plan for this statement , You don't use the statistics you already have , While using profile The strategy of , Generate a new query plan .

 

One 、  First step : Give users permission

[ZHLHRSPMDB2:oracle]:/oracle>sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.4.0 Production on Wed May 25 16:47:29 2016

 

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

 

 

Connected to:

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

With the Partitioning, Real Application Clusters, OLAP, Data Mining

and Real Application Testing options

 

SYS@dlhr>

SYS@dlhr>

SYS@dlhr>

SYS@dlhr> GRANT CREATE ANY SQL PROFILE TO LHR;

 

Grant succeeded.

 

SYS@dlhr> GRANT DROP ANY SQL PROFILE TO LHR;

 

Grant succeeded.

 

SYS@dlhr> GRANT ALTER ANY SQL PROFILE TO LHR;

 

Grant succeeded.

 

SYS@dlhr> conn lhr/lhr

Connected.

LHR@dlhr>

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 lhr.TB_LHR_20160525_01 as select * from dba_objects;

 

Table created.

 

LHR@dlhr> create index lhr.TB_LHR_20160525_01_idx on TB_LHR_20160525_01(object_id);  

 

Index created.

 

LHR@dlhr> exec dbms_stats.gather_table_stats('lhr','TB_LHR_20160525_01',cascade=>true,degree=>4);  

 

PL/SQL procedure successfully completed.

 

LHR@dlhr> set autot on

LHR@dlhr> select /*+no_index(TB_LHR_20160525_01 TB_LHR_20160525_01_idx)*/count(*) from lhr.TB_LHR_20160525_01 where object_id = 100 ;

 

  COUNT(*)

----------

         1

 

 

Execution Plan

----------------------------------------------------------

Plan hash value: 3612989399

 

-----------------------------------------------------------------------------------------

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

-----------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |                    |     1 |     5 |   351   (2)| 00:00:05 |

|   1 |  SORT AGGREGATE    |                    |     1 |     5 |            |          |

|*  2 |   TABLE ACCESS FULL| TB_LHR_20160525_01 |     1 |     5 |   351   (2)| 00:00:05 |

-----------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   2 - filter("OBJECT_ID"=100)

 

 

Statistics

----------------------------------------------------------

          1  recursive calls

          0  db block gets

       1249  consistent gets

          0  physical reads

          0  redo size

        526  bytes sent via SQL*Net to client

        520  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

LHR@dlhr> set autot off

LHR@dlhr> SELECT v.SQL_ID, v.SQL_TEXT FROM vsqlvWHEREv.SQLTEXTlike′sqlvWHEREv.SQLTEXTlike′sql%' ;

 

SQL_ID

-------------

SQL_TEXT

------------------------------------------------------------------------------------------------------------------------------------

7jt1btjkcczb8

select /*+no_index(TB_LHR_20160525_01 TB_LHR_20160525_01_idx)*/count(*) from lhr.TB_LHR_20160525_01 where object_id = 100

 

7suktf0w95cry

EXPLAIN PLAN SET STATEMENT_ID='PLUS150249' FOR select /*+no_index(TB_LHR_20160525_01 TB_LHR_20160525_01_idx)*/count(*) from lhr.TB_L

HR_20160525_01 where object_id = 100

 

Two 、  The second step : establish 、 Perform optimization tasks

LHR@dlhr> DECLARE

  2      my_task_name VARCHAR2(30);

  3      my_sqltext       CLOB;

  4  BEGIN

  5      my_sqltext := 'select /*+no_index(TB_LHR_20160525_01 TB_LHR_20160525_01_idx)*/count(*) from lhr.TB_LHR_20160525_01 where object_id = 100';

  6      my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(

  7                              sql_text          => my_sqltext,

  8                              user_name       => 'LHR',

  9                              scope           => 'COMPREHENSIVE',

10                              time_limit    => 60,

11                              task_name       => 'sql_profile_test',

12                              description => 'Task to tune a query on a specified table');

13      DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'sql_profile_test');

14  END;

15  /

 

PL/SQL procedure successfully completed.

 

Or you can use it sqlid To generate optimization tasks , as follows :

 

LHR@dlhr> DECLARE

  2    a_tuning_task VARCHAR2(30);

  3  BEGIN

  4    a_tuning_task := dbms_sqltune.create_tuning_task(sql_id    => '7jt1btjkcczb8',

  5                                                     task_name => 'sql_profile_test_SQLID');

  6    dbms_sqltune.execute_tuning_task(a_tuning_task);

  7  END;

  8  /

 

PL/SQL procedure successfully completed.

3、 ... and 、  The third step : Check out the optimization suggestions

LHR@dlhr> set autot off

LHR@dlhr> set long 10000

LHR@dlhr> set longchunksize 1000

LHR@dlhr> set linesize 100

LHR@dlhr> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'sql_profile_test') from DUAL;

 

DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_PROFILE_TEST')

----------------------------------------------------------------------------------------------------

GENERAL INFORMATION SECTION

-------------------------------------------------------------------------------

Tuning Task Name   : sql_profile_test

Tuning Task Owner  : LHR

Workload Type      : Single SQL Statement

Scope              : COMPREHENSIVE

Time Limit(seconds): 60

Completion Status  : COMPLETED

Started at         : 05/25/2016 16:58:31

Completed at       : 05/25/2016 16:58:32

 

-------------------------------------------------------------------------------

Schema Name: LHR

SQL ID     : 9kzm8scz6t92z

SQL Text   : select /*+no_index(TB_LHR_20160525_01

             TB_LHR_20160525_01_idx)*/count(*) from lhr.TB_LHR_20160525_01

             where object_id = 100

 

-------------------------------------------------------------------------------

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: 99.83%)

  ------------------------------------------

  - Consider accepting the recommended SQL profile.

    execute dbms_sqltune.accept_sql_profile(task_name => 'sql_profile_test',

            task_owner => 'LHR', 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):             .006278            .00004      99.36 %

  CPU Time (s):                 .003397           .000021      99.38 %

  User I/O Time (s):                  0                 0

  Buffer Gets:                     1249                 2      99.83 %

  Physical Read Requests:             0                 0

  Physical Write Requests:            0                 0

 

DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_PROFILE_TEST')

----------------------------------------------------------------------------------------------------

  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: 3612989399

 

-----------------------------------------------------------------------------------------

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

-----------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |                    |     1 |     5 |   351   (2)| 00:00:05 |

|   1 |  SORT AGGREGATE    |                    |     1 |     5 |            |          |

|*  2 |   TABLE ACCESS FULL| TB_LHR_20160525_01 |     1 |     5 |   351   (2)| 00:00:05 |

-----------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   2 - filter("OBJECT_ID"=100)

 

2- Using SQL Profile

--------------------

Plan hash value: 661515879

 

--------------------------------------------------------------------------------------------

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

--------------------------------------------------------------------------------------------

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

|   1 |  SORT AGGREGATE   |                        |     1 |     5 |            |          |

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

--------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

 

DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_PROFILE_TEST')

----------------------------------------------------------------------------------------------------

   2 - access("OBJECT_ID"=100)

 

-------------------------------------------------------------------------------

 

 

Here you can see , A new query plan is given in the optimization proposal . Now? , We decided to accept the proposal , And don't rewrite statements .

 

Four 、  Step four : Accept profile

LHR@dlhr> set autot on

LHR@dlhr> select /*+no_index(TB_LHR_20160525_01 TB_LHR_20160525_01_idx)*/count(*) from lhr.TB_LHR_20160525_01 where object_id = 100 ;

 

  COUNT(*)

----------

         1

 

 

Execution Plan

----------------------------------------------------------

 

Plan hash value: 3612989399

 

-----------------------------------------------------------------------------------------

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

-----------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |                    |     1 |     5 |   351   (2)| 00:00:05 |

|   1 |  SORT AGGREGATE    |                    |     1 |     5 |            |          |

|*  2 |   TABLE ACCESS FULL| TB_LHR_20160525_01 |     1 |     5 |   351   (2)| 00:00:05 |

-----------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   2 - filter("OBJECT_ID"=100)

 

 

Statistics

----------------------------------------------------------

          0  recursive calls

          0  db block gets

       1249  consistent gets

          0  physical reads

          0  redo size

        526  bytes sent via SQL*Net to client

        520  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

LHR@dlhr> execute dbms_sqltune.accept_sql_profile(task_name =>'sql_profile_test_SQLID', task_owner => 'LHR', replace => TRUE);

 

PL/SQL procedure successfully completed.

 

LHR@dlhr> set autot off

LHR@dlhr>  SELECT e.task_name, b.name, d.sql_text, extractvalue(value(h), '.') as hints

  2     FROM dba_sql_profiles d,

  3           dba_advisor_tasks e,

  4          SYS.SQLOBJ$DATA A,

  5          SYS.SQLOBJ$ B,

  6          TABLE(XMLSEQUENCE(EXTRACT(XMLTYPE(A.COMP_DATA),

  7                                    '/outline_data/hint'))) h

  8    where a.signature = b.signature

  9      and a.category = b.category

10      and a.obj_type = b.obj_type

11      and a.plan_id = b.plan_id

12      and a.signature = d.signature

13      and d.task_id=e.task_id

14      and d.name = 'SYS_SQLPROF_0154e728ad3f0000'

15     ;

 

TASK_NAME                      NAME

------------------------------ ------------------------------

SQL_TEXT

----------------------------------------------------------------------------------------------------

HINTS

----------------------------------------------------------------------------------------------------

sql_profile_test               SYS_SQLPROF_0154e728ad3f0000

select /*+no_index(TB_LHR_20160525_01 TB_LHR_20160525_01_idx)*/count(*) from lhr.TB_LHR_20160525_01

where object_id = 100

OPTIMIZER_FEATURES_ENABLE(default)

 

sql_profile_test               SYS_SQLPROF_0154e728ad3f0000

select /*+no_index(TB_LHR_20160525_01 TB_LHR_20160525_01_idx)*/count(*) from lhr.TB_LHR_20160525_01

where object_id = 100

IGNORE_OPTIM_EMBEDDED_HINTS

 

 

 

The bag is used here DBMS_SQLTUNE Another function of :ACCEPT_SQL_PROFILE. among , Parameters task_name That is, the name of the optimization proposal task we created ,name yes profile Name , It can be any legal name . In addition, this function has other functions , Here's the prototype of this function :

DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (

   task_name    IN  VARCHAR2,

   object_id    IN  NUMBER   := NULL,

   name         IN  VARCHAR2 := NULL,

   description  IN  VARCHAR2 := NULL,

   category     IN  VARCHAR2 := NULL;

   task_owner   IN VARCHAR2  := NULL,

   replace      IN BOOLEAN   := FALSE,

   force_match  IN BOOLEAN   := FALSE)

RETURN VARCHAR2;

 

Description yes profile Description information of ;task_owner Is the owner of the optimization proposal task ;replace by TRUE when , If this profile Already exist , Instead of it ;force_match by TURE when , Represents a forced match with a statement , That is to force the use of bound variables , And system parameters cursor_sharing Set to FORCE Similar to , by FALSE when , And cursor_sharing Set to EXACT Similar to , It's a perfect match .

Here I would like to make a special mention of category This parameter , You can set this parameter , Make specific conversations using this profile. stay 10g in , Each session has a new parameter SQLTUNE_CATEGORY, His default value is DEFAULT. And when we call this function , If this parameter is not specified , Then its value is also DEFAULT, And if we give this profile It specifies an other CATEGORY value , Such as FOR_TUNING, So only conversational participation SQLTUNE_CATEGORY Also for the FOR_TUNING when , To use this porfile. Why is this parameter very useful ? Imagine an environment like this : You use... On a production system STA Tune a statement ,STA Optimization suggestions have been given , But you don't dare to implement the advice it gives ( After all, it's just a machine , Not completely trusted ), You can create a special CATEGORY Of profile, Then make it in your own conversation SQLTUNE_CATEGORY For this special CATEGORY, Then we can see the actual effect of the optimization proposal without affecting the production environment .

  In addition, you can use the view DBA_SQL_PROFILES To see what you've created profile.

5、 ... and 、  Step five : see profile The effect of

LHR@dlhr> set autot on

LHR@dlhr> select /*+no_index(TB_LHR_20160525_01 TB_LHR_20160525_01_idx)*/count(*) from lhr.TB_LHR_20160525_01 where object_id = 100 ;

 

  COUNT(*)

----------

         1

 

 

Execution Plan

----------------------------------------------------------

Plan hash value: 661515879

 

--------------------------------------------------------------------------------------------

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

--------------------------------------------------------------------------------------------

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

|   1 |  SORT AGGREGATE   |                        |     1 |     5 |            |          |

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

--------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   2 - access("OBJECT_ID"=100)

 

Note

-----

   - SQL profile "SYS_SQLPROF_0154e728ad3f0000" used for this statement

 

 

Statistics

----------------------------------------------------------

          1  recursive calls

          0  db block gets

          2  consistent gets

          0  physical reads

          0  redo size

        526  bytes sent via SQL*Net to client

        520  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

 

 

from NOTE Part of it can be seen , The sentence uses profile Data in , Created a new query plan . And there is some additional information in the query plan , Indicates that the statement is a ’SYS_SQLPROF_0154e728ad3f0000’ This profile, Instead of the query plan generated from the statistics on the object .

But the above methods mainly rely on sql tuning advisor, If it can't generate the execution plan you want . You can also do it manually , adopt sql profile hold hint add . complex SQL Of hint You can use scripts coe_xfr_sql_profile.sql To produce the original sentence outline data And plus hint Of the statement outline data, Then replace the corresponding SYS.SQLPROF_ATTR, Finally, execute the generated sql That's all right. .

 

Use PLSQL DEVELOPER 11 View execution plan , Here's the picture , The benefits of the new version :

wpsA6.tmp 

 

 

Two .3  SPM(SQL Plan Management)

Two .3.1  SPM Basic knowledge of

 

SQL  Of the statement SQL  When the execution plan changes , There may be performance risks .

SQL  There are many reasons why plans change , Such as optimizing program version 、 Optimizer Statistics 、 Optimize program parameters 、 Scheme definition 、 System design and SQL  Profile creation, etc .

In previous versions Oracle DB  Various planning control techniques are introduced into the project ( Such as stored outline (storedoutline(9i)) and SQL  Profiles, etc (SQLprofile(10g))), Used to resolve performance regression caused by plan changes . however , These technologies are passive processes that require manual intervention .

SQL  Plan management is a kind of random management Oracle Database 11g  New features introduced , By maintaining the so-called “SQL  Plan baseline (SQL plan baseline(11g))” To enable the system to control itself SQL  The plan evolves . When this feature is enabled , Just prove that the newly generated SQL  Planning and SQL  Integration of plan baselines does not lead to performance regression , You can do this integration . therefore , Executing a SQL  When the sentence is , Only use the corresponding SQL  The plan included in the plan baseline . have access to SQL  Optimization sets are automatically loaded or implanted SQL  Plan baseline .

SQL  The main advantage of the plan management function is the stable performance of the system , There will be no return to the plan . Besides , This function can also save DBA  A lot of time , This time is usually spent identifying and analyzing SQL  Performance regression and finding available solutions .Oracle11g in ,Oracle Provide dbms_spm Package management SQL Plan,SPM It's a prevention mechanism , It records and evaluates sql Implementation plan of , Put the known efficient sql The implementation plan is established as SQL Plan Baselines,SQL Plan Baseline Its function is to keep SQL Performance without having to focus on system changes .

 

stay SQL Plan BaseLines Capture phase ,Oracle Record SQL And check whether the execution plan has changed , If SQL The changed execution plan is safe , be SQL Just use the new execution plan , therefore ,Oracle Maintain a single SQL Historical information about the implementation plan ,Oracle Maintenance of SQL The history of execution plan is only for repeated execution SQL,SQL Plan Baseline You can do it by hand load, It can also be set to auto capture .

 

load SQL  There are two ways to plan a baseline :

(1)  Instant capture , Auto capture (Automatic Plan Capture):

Use automatic schedule to capture , The method is : Will initialize parameters OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES  Set to TRUE. By default , This parameter is set to FALSE. Set this parameter to TRUE  Will turn on automatic identification repeatable SQL  sentence , And the ability to automatically create plan history for such statements .  If you want to activate automatic SQL Plan Capture, You need to set OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES, This parameter defaults to False, If set to True, Is automatically captured SQL Plan, The system will automatically create and maintain SQL Plan History,SQL Plan History Including the focus of the optimizer : such as an execution plan, SQL text, outline, bind variables, and compilation environment.

 

(2)  Batch loading (Manual Plan Loading):

Use DBMS_SPM  Package ; The package supports manual management SQL  Plan baseline . Use this package , Can be SQL  Plan to cache from cursor or existing SQL  Optimization set (STS)  Load directly into SQL In the plan baseline . To start with STS  Load into SQL  Plan baseline SQL  sentence , Needs to be SQL The plan is stored in STS in . Use DBMS_SPM  You can change the status of a baseline plan from accepted to not accepted ( And change from never accepted to accepted ), You can also export baseline plans from the staging table , Then use the exported baseline plan to SQL  The plan baseline is loaded into other databases .

 

You can also manually load an existing SQL Plan As SQL Plan Baseline, Manually loaded SQL Plan It doesn't check its performance :

-- from SQL Tuning Set Medium load :

DECLARE

  my_plans pls_integer;

BEGIN

  my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(sqlset_name => 'tset1');

END;

/

 

-- from Cursor Cache Medium load

DECLARE my_plans pls_integer;

BEGIN

  my_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id => '7qqnad1j615m7');

END;

/

 

stay SQL  Plan baseline evolution phase ,Oracle DB  The performance of the new plan will be evaluated in a routine way , And integrate better performance plans into SQL  In the plan baseline .

The optimization program is SQL  Statement to find a new plan , The plan is added to the plan history as an unaccepted plan . then , be relative to SQL  Plan baseline performance , Verify the performance of the program . If it is verified that an unacceptable plan does not lead to a performance regression ( Manual or automatic ), The plan will be changed to an accepted plan , And integrated into SQL  In the plan baseline . The process of successfully verifying an unaccepted plan includes : The performance of this program and from SQL Compare the performance of a plan selected from the plan baseline , Make sure it performs better .

 

Evolution SQL  There are two ways to plan a baseline :

(1) Use DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE  function . This function will return a report , Shows whether some existing history plans have been moved to the plan baseline . You can also specify specific plans to test in the history .

 

(2) function SQL  Optimize guidance : By using SQL  Optimization guides manual or automatic optimization SQL  sentence , Evolution SQL Plan baseline .SQL Optimization guidance finds optimized plans , And confirm that its performance is better than that from the corresponding SQL  When planning the performance of the plan selected in the baseline , A proposal will be generated to accept SQL  Profile . Accepted the SQL After the profile , The optimized plan will be added to the corresponding SQL  In the plan baseline .

 

stay SQL Plan Baselines The stage of evolution ,Oracle Evaluate the new Plan And the better performance Plan Deposit SQL Plan Baselines in , have access to dbms_spm package The process of EVOLVE_SQL_PLAN_BASELINE New SQL Plan Save what already exists SQL Plan Baselines in , new Plan Will be as has been Accept Plan Add to SQL Plan Baselines in .

SET SERVEROUTPUT ON

SET LONG 10000

DECLARE report clob;

BEGIN report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE (sql_handle => 'SYS_SQL_593bc74fca8e6738');

DBMS_OUTPUT.PUT_LINE(report);

END;

/

 

If you add a plan to the plan History , Then the plan will be associated with some important attributes :

(1)SIGNATURE、SQL_HANDLE、SQL_TEXT  and PLAN_NAME  Is an important identifier for a search operation .

 

(2) Use ORIGIN  You can be sure that the plan is automatically captured (AUTO-CAPTURE)、 Manual evolution (MANUALLOAD)、 adopt SQL  Optimization guides automatic evolution (MANUAL-SQLTUNE)  Or through automatic SQL  Optimize the automatic evolution of (AUTO-SQLTUNE).

 

(3)  ENABLED  and ACCEPTED:ENABLED Property indicates that the plan is enabled , It can be used by optimization program . If not set ENABLED, The system will not consider this plan .ACCEPTED  Property indicates that the user is changing the schedule to ACCEPTED  The plan has been verified to be effective ( Automatic by the system or manually by the user ). If you change a plan to ACCEPTED, Only if DBMS_SPM.ALTER_SQL_PLAN_BASELINE() When changing its state , The plan is right and wrong ACCEPTED  Of . You can delete ENABLED Settings are temporarily disabled ACCEPTED  plan . The plan has to be ENABLED  and ACCEPTED, The optimizer will consider using it .

 

(4)  FIXED  Indicates that the optimizer will only consider marking as FIXED  The plan for , Regardless of other plans . for example , If there is 10  A baseline plan , Three of these plans are marked as FIXED, The optimizer will use only the best of the three plans , And ignore all the other plans . If a SQL  The plan baseline contains at least one enabled repaired plan , Then SQL  The plan baseline is FIXED  Of . If it's being repaired SQL  A new plan has been added to the plan baseline , Then manually declare these new plans as FIXED  Before , You can't use these new plans .

have access to DBA_SQL_PLAN_BASELINES View to see the properties of each plan . then , have access to DBMS_SPM.ALTER_SQL_PLAN_BASELINE  Function to change some of these properties . You can also use DBMS_SPM.DROP_SQL_PLAN_BASELINE Function to delete a plan or the entire plan History .

notes :DBA_SQL_PLAN_BASELINES  The view contains some additional properties ; Use these attributes to determine when each plan was last used , And whether a plan should be automatically cleared .

 

If you are using automatic plan capture , For the first time SQL  When the statement is marked as repeatable , Its best cost plan will be added to the corresponding SQL  In the plan baseline . then , The plan will be used to execute the corresponding statements .

If a SQL  Statement has a plan baseline , And initialize the parameters OPTIMIZER_USE_SQL_PLAN_BASELINES  Set to TRUE( The default value is ), Then the optimizer will use the comparison plan selection strategy . Every compilation SQL  When the sentence is , Optimization programs will first use the traditional cost based search method to establish an optimal cost plan , Then try to SQL  Find a matching plan in the plan baseline . If you find a matching plan , The optimizer will continue to run as usual . If no matching plan is found , The optimizer will first add the new plan to the plan History , And then calculate SQL The cost of each accepted program in the program baseline , And choose the one with the lowest cost . Copy the accepted plans using the outlines stored with them . therefore , about SQL  The statement is , To have a SQL  The advantage of planning a baseline is : The optimizer always selects the SQL  An accepted plan in the plan baseline .

 

adopt SQL  Plan management , The optimization program can generate the best cost plan , You can also generate baseline plans . This information will be dumped in the plan_table  Of other_xml  In the column .

Besides , You can also use new dbms_xplain.display_sql_plan_baseline  function , Displays the information given in a plan baseline sql_handle  One or more execution plans for . If you also specify plan_name, The corresponding execution plan will be displayed .

 

notes : To preserve backward compatibility , If one of the user's sessions SQL  The storage outline pair of the statement is active , The statement will be compiled using this storage outline . Besides , Even if automatic schedule capture is enabled for the session , The plan generated by the optimizer using the storage outline is not stored in SMB  in .

 

Although the storage outline does not have any explicit migration process , But it can be used DBMS_SPM  In the package LOAD_PLAN_FROM_CURSOR_CACHE  Process or LOAD_PLAN_FROM_SQLSET  The process migrates it to SQL  Plan baseline . When the migration is complete , The original storage outline should be disabled or deleted .

stay SQL Plan Selection stage ,SQL Every time I compile , The optimizer uses a cost based approach , Build up best-cost Implementation plan of , And then to match SQL Plan Baselines Medium SQL Plan, If you find a match SQL Plan, The execution plan will be used , If no match is found SQL Plan, The optimizer will go SQL Plan History Search for the lowest cost SQL Plan, If the optimizer is SQL Plan History No task matching found in SQL Plan, Then SQL Plan Be treated as a Non-Accept Plan Be deposited SQL Plan History, new SQL Plan It won't be used until it's verified that it won't cause any performance problems .

 

SPM Related data dictionary :

SELECT * FROM dba_sql_plan_baselines;

SELECT * FROM dba_sqlset_plans;

SELECT * FROM dba_advisor_sqlplans;

 

Two .3.2   Delete Plans  and  Baselines

DROP_SQL_PLAN_BASELINE The function can be derived from baselines in drop  An execution plan for execution , If not implemented plan name, Then drop  be-all plan. namely drop 了 baseline.

Parameter

Description

sql_handle

SQL statement handle. It identifies plans associated with a SQL statement that are to be dropped. If NULL then plan_name must be specified.

plan_name

Plan name. It identifies a specific plan. Default NULL means to drop all plans associated with the SQL statement identified by sql_handle.

 

 

-- Delete some SQL Of baseline

SET SERVEROUTPUT ON

DECLARE

l_plans_dropped  PLS_INTEGER;

BEGIN

l_plans_dropped := DBMS_SPM.drop_sql_plan_baseline (

   sql_handle => 'SQL_7b76323ad90440b9',

   plan_name  => NULL);

  

DBMS_OUTPUT.put_line(l_plans_dropped);

END;

 


-- Delete all baseline

 

declare

 

  v_plan_num PLS_INTEGER;

begin

 

  for cur in (SELECT * FROM dba_sql_plan_baselines) loop

 

    begin

      v_plan_num := dbms_spm.drop_sql_plan_baseline(sql_handle => cur.sql_handle);

    exception

      when others then

        null;

    end;

 

  end loop;

 

end;

/

 

Two .3.3  SPM Using the demonstration

-- Cancel auto capture , You can also do without canceling auto capture :

show parameter baselines

ALTER SYSTEM SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=FALSE;

 

 

[ZHLHRSPMDB2:oracle]:/oracle>ORACLE_SID=dlhr

[ZHLHRSPMDB2:oracle]:/oracle>sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.4.0 Production on Thu May 26 15:47:55 2016

 

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

 

 

Connected to:

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

With the Partitioning, Real Application Clusters, OLAP, Data Mining

and Real Application Testing options

 

SYS@dlhr> conn lhr/lhr

Connected.

LHR@dlhr>

LHR@dlhr>

LHR@dlhr>

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> show parameter baselines

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

optimizer_capture_sql_plan_baselines boolean     TRUE

optimizer_use_sql_plan_baselines     boolean     TRUE

 

LHR@dlhr> ALTER SYSTEM SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=FALSE;

 

System altered.

 

 

 

 

 

-- Create tables and insert data :

CREATE TABLE tb_spm_test_lhr (

id           NUMBER,

  description  VARCHAR2(50)

);

 

DECLARE

  TYPE t_tab IS TABLE OF tb_spm_test_lhr%ROWTYPE;

  l_tab t_tab := t_TAB();

BEGIN

  FOR i IN 1 .. 10000 LOOP

    l_tab.extend;

    l_tab(l_tab.last).id := i;

   l_tab(l_tab.last).description := 'Description for ' || i;

  END LOOP;

 

  FORALL i IN l_tab.first .. l_tab.last

    INSERT INTO tb_spm_test_lhr VALUES l_tab(i);

 

  COMMIT;

END;

/

 

 

EXEC DBMS_STATS.gather_table_stats(USER, 'tb_spm_test_lhr', cascade=>TRUE);

 

set autot trace

SELECT description FROM   tb_spm_test_lhr WHERE  id = 100;

 

 

 

LHR@dlhr> CREATE TABLE tb_spm_test_lhr (

  2   id           NUMBER,

  3    description  VARCHAR2(50)

  4  );

 

Table created.

 

LHR@dlhr> 

LHR@dlhr> DECLARE

  2    TYPE t_tab IS TABLE OF tb_spm_test_lhr%ROWTYPE;

  3    l_tab t_tab := t_TAB();

  4  BEGIN

  5    FOR i IN 1 .. 10000 LOOP

  6      l_tab.extend;

  7      l_tab(l_tab.last).id := i;

  8     l_tab(l_tab.last).description := 'Description for ' || i;

  9    END LOOP;

10   

11    FORALL i IN l_tab.first .. l_tab.last

12      INSERT INTO tb_spm_test_lhr VALUES l_tab(i);

13   

14    COMMIT;

15   END;

16   /

 

PL/SQL procedure successfully completed.

 

 

LHR@dlhr> set autot trace

LHR@dlhr> SELECT description FROM   tb_spm_test_lhr WHERE  id = 100;

 

 

Execution Plan

----------------------------------------------------------

Plan hash value: 2196561629

 

-------------------------------------------------------------------------------------

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

-------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |                 |     1 |    40 |    13   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| TB_SPM_TEST_LHR |     1 |    40 |    13   (0)| 00:00:01 |

-------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   1 - filter("ID"=100)

 

Note

-----

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

 

 

Statistics

----------------------------------------------------------

          4  recursive calls

          0  db block gets

         94  consistent gets

          0  physical reads

          0  redo size

        546  bytes sent via SQL*Net to client

        519  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

 

 

 

----- Get the SQL_ID:

set autot off

col SQL_TEXT format a100

select distinct a.SQL_ID,a.SQL_TEXT from v$sql a

WHERE a.SQL_TEXT like '%SELECT description FROM   tb_spm_test_lhr WHERE  id = 100%'

and a.SQL_TEXT not like '%v$sql%'

AND    sql_text NOT LIKE '%EXPLAIN%';

 

LHR@dlhr> set autot off

LHR@dlhr> col SQL_TEXT format a100

LHR@dlhr> select distinct a.SQL_ID,a.SQL_TEXT from v$sql a

  2  WHERE a.SQL_TEXT like '%SELECT description FROM   tb_spm_test_lhr WHERE  id = 100%'

  3  and a.SQL_TEXT not like '%v$sql%'

  4  AND    sql_text NOT LIKE '%EXPLAIN%';

 

SQL_ID        SQL_TEXT

------------- ----------------------------------------------------------------------------------------------------

garkwg3yy2ram SELECT description FROM   tb_spm_test_lhr WHERE  id = 100

 

 

---- Use SQL_ID  from cursor cache Manually capture the execution plan :

SET SERVEROUTPUT ON

DECLARE

l_plans_loaded  PLS_INTEGER;

BEGIN

l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(

   sql_id => '&sql_id'); 

DBMS_OUTPUT.put_line('Plans Loaded: ' || l_plans_loaded);

END;

/

-- -- Use DBA_SQL_PLAN_BASELINES To view SPM  Information :

col sql_handle for a35

col plan_name for a35

set lin 300

SELECT SQL_HANDLE,plan_name,origin,enabled,accepted,fixed

FROM   dba_sql_plan_baselines

WHERE  sql_text LIKE '%tb_spm_test_lhr%'

AND    sql_text NOT LIKE'%dba_sql_plan_baselines%';

 

-- Refresh Share Pool, Make next time SQL  Hard parsing is required for execution :

ALTER SYSTEM FLUSH SHARED_POOL;

 

LHR@dlhr> SET SERVEROUTPUT ON

LHR@dlhr> DECLARE

  2   l_plans_loaded  PLS_INTEGER;

  3  BEGIN

  4   l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(

  5     sql_id => '&sql_id'); 

  6   DBMS_OUTPUT.put_line('Plans Loaded: ' || l_plans_loaded);

  7  END;

  8  /

Enter value for sql_id: garkwg3yy2ram

old   5:    sql_id => '&sql_id');

new   5:    sql_id => 'garkwg3yy2ram');

Plans Loaded: 1

 

PL/SQL procedure successfully completed.

 

 

LHR@dlhr> col sql_handle for a35

LHR@dlhr> col plan_name for a35

LHR@dlhr> set lin 300

LHR@dlhr> SELECT sql_handle, plan_name,enabled, accepted

  2  FROM   dba_sql_plan_baselines

  3  WHERE  sql_text LIKE '%tb_spm_test_lhr%'

  4  AND    sql_text NOT LIKE'%dba_sql_plan_baselines%';

 

SQL_HANDLE                          PLAN_NAME                           ENA ACC

----------------------------------- ----------------------------------- --- ---

SQL_4f19d3cf57be7303                SQL_PLAN_4y6fmtxbvwws3184920d2      YES YES

 

LHR@dlhr> ALTER SYSTEM FLUSH SHARED_POOL;

 

System altered.

 

LHR@dlhr> set autot trace

SELECT description FROM   tb_spm_test_lhr WHERE  id = 100;

LHR@dlhr>

 

Execution Plan

----------------------------------------------------------

Plan hash value: 2196561629

 

-------------------------------------------------------------------------------------

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

-------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |                 |     1 |    40 |    13   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| TB_SPM_TEST_LHR |     1 |    40 |    13   (0)| 00:00:01 |

-------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   1 - filter("ID"=100)

 

Note

-----

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

   - SQL plan baseline "SQL_PLAN_4y6fmtxbvwws3184920d2" used for this statement

 

 

Statistics

----------------------------------------------------------

        555  recursive calls

         16  db block gets

        667  consistent gets

          0  physical reads

       3056  redo size

        546  bytes sent via SQL*Net to client

        519  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

         32  sorts (memory)

          0  sorts (disk)

          1  rows processed

 

 

 

--- Create index , Collect statistics , And query the same SQL:

 

CREATE INDEX spm_test_tab_idx ON tb_spm_test_lhr(id);

EXEC DBMS_STATS.gather_table_stats(USER,'tb_spm_test_lhr', cascade=>TRUE);

 

set autot trace

SELECT description FROM   tb_spm_test_lhr WHERE  id = 100;

 

 

LHR@dlhr> CREATE INDEX spm_test_tab_idx ON tb_spm_test_lhr(id);

Index created.

 

LHR@dlhr> EXEC DBMS_STATS.gather_table_stats(USER,'tb_spm_test_lhr', cascade=>TRUE);

 

 

PL/SQL procedure successfully completed.

 

LHR@dlhr>

LHR@dlhr>

LHR@dlhr>

LHR@dlhr> set autot trace

LHR@dlhr> SELECT description FROM   tb_spm_test_lhr WHERE  id = 100;

 

 

 

Execution Plan

----------------------------------------------------------

Plan hash value: 2196561629

 

-------------------------------------------------------------------------------------

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

-------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |                 |     1 |    25 |    13   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| TB_SPM_TEST_LHR |     1 |    25 |    13   (0)| 00:00:01 |

-------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   1 - filter("ID"=100)

 

Note

-----

   - SQL plan baseline "SQL_PLAN_4y6fmtxbvwws3184920d2" used for this statement

 

 

Statistics

----------------------------------------------------------

        640  recursive calls

         39  db block gets

        493  consistent gets

          2  physical reads

      12268  redo size

        546  bytes sent via SQL*Net to client

        519  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

         10  sorts (memory)

          0  sorts (disk)

          1  rows processed

 

 

 

-- Here we create the index , But it's still full scan , Using index here is obviously the best solution .

-- see SPM  View :

set autot off

col sql_handle for a35

col plan_name for a35

set lin 300

SELECT SQL_HANDLE,plan_name,origin,enabled,accepted,fixed

FROM   dba_sql_plan_baselines

WHERE  sql_text LIKE '%tb_spm_test_lhr%'

AND    sql_text NOT LIKE'%dba_sql_plan_baselines%';

 

LHR@dlhr> set autot off

LHR@dlhr> col sql_handle for a35

LHR@dlhr> col plan_name for a35

LHR@dlhr> set lin 300

LHR@dlhr> SELECT sql_handle, plan_name,enabled, accepted

  2  FROM   dba_sql_plan_baselines

  3  WHERE  sql_text LIKE '%tb_spm_test_lhr%'

  4  AND    sql_text NOT LIKE'%dba_sql_plan_baselines%';

 

SQL_HANDLE                          PLAN_NAME                           ENA ACC

----------------------------------- ----------------------------------- --- ---

SQL_4f19d3cf57be7303                SQL_PLAN_4y6fmtxbvwws3184920d2      YES YES

SQL_4f19d3cf57be7303                SQL_PLAN_4y6fmtxbvwws38b725570      YES NO

 

-- adopt baselines Result of query , You can see our SQL  Produced 2 This is an implementation plan . But we don't think the optimal execution plan is marked as ACCEPT, So I didn't use .

 

Next we're going to execute the plan : Evolution is about cost The low execution plan is marked as accept

LHR@dlhr> SET LONG 10000

LHR@dlhr> SELECT DBMS_SPM.evolve_sql_plan_baseline(sql_handle => '&sql_handle') FROM dual;

Enter value for sql_handle: SQL_4f19d3cf57be7303

old   1: SELECT DBMS_SPM.evolve_sql_plan_baseline(sql_handle => '&sql_handle') FROM dual

new   1: SELECT DBMS_SPM.evolve_sql_plan_baseline(sql_handle => 'SQL_4f19d3cf57be7303') FROM dual

 

DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(SQL_HANDLE=>'SQL_4F19D3CF57BE7303')

--------------------------------------------------------------------------------

 

-------------------------------------------------------------------------------

                        Evolve SQL Plan Baseline Report

-------------------------------------------------------------------------------

 

Inputs:

-------

  SQL_HANDLE = SQL_4f19d3cf57be7303

  PLAN_NAME  =

  TIME_LIMIT = DBMS_SPM.AUTO_LIMIT

  VERIFY     = YES

  COMMIT     = YES

 

Plan: SQL_PLAN_4y6fmtxbvwws38b725570

------------------------------------

  Plan was verified: Time used .018 seconds.

  Plan passed performance criterion: 15 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):                  .308           .025             12.32

  CPU Time(ms):                      .164           .015             10.93

  Buffer Gets:                         45              3                15

  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

 

 

 

-- Look again DBA_SQL_PLAN_BASELINES View :

set autot off

col sql_handle for a35

col plan_name for a35

set lin 300

SELECT SQL_HANDLE,plan_name,origin,enabled,accepted,fixed

FROM   dba_sql_plan_baselines

WHERE  sql_text LIKE '%tb_spm_test_lhr%'

AND    sql_text NOT LIKE'%dba_sql_plan_baselines%';

 

LHR@dlhr> set autot off

LHR@dlhr> col sql_handle for a35

LHR@dlhr> col plan_name for a35

LHR@dlhr> set lin 300

LHR@dlhr> SELECT sql_handle, plan_name,enabled, accepted

  2  FROM   dba_sql_plan_baselines

  3  WHERE  sql_text LIKE '%tb_spm_test_lhr%'

  4  AND    sql_text NOT LIKE'%dba_sql_plan_baselines%';

 

SQL_HANDLE                          PLAN_NAME                           ENA ACC

----------------------------------- ----------------------------------- --- ---

SQL_4f19d3cf57be7303                SQL_PLAN_4y6fmtxbvwws3184920d2      YES YES

SQL_4f19d3cf57be7303                SQL_PLAN_4y6fmtxbvwws38b725570      YES YES

 

 

-- Re execution SQL:

set autot trace

SELECT description FROM   tb_spm_test_lhr WHERE  id = 100;

 

LHR@dlhr> set autot trace

LHR@dlhr> SELECT description FROM   tb_spm_test_lhr WHERE  id = 100;

 

 

Execution Plan

----------------------------------------------------------

Plan hash value: 2587945646

 

------------------------------------------------------------------------------------------------

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

------------------------------------------------------------------------------------------------

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

|   1 |  TABLE ACCESS BY INDEX ROWID| TB_SPM_TEST_LHR  |     1 |    25 |     2   (0)| 00:00:01 |

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

------------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   2 - access("ID"=100)

 

Note

-----

   - SQL plan baseline "SQL_PLAN_4y6fmtxbvwws38b725570" used for this statement

 

 

Statistics

----------------------------------------------------------

         13  recursive calls

         14  db block gets

         18  consistent gets

          0  physical reads

       3048  redo size

        553  bytes sent via SQL*Net to client

        519  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

 

 

-- This time the index was used correctly . Because it's only marked as ENABLE and  ACCEPT Of plan To be used .

 

The following example marks the execution plan of our first full table scan as fixed. Marked as fixed Priority will be given to the implementation plan of .FIXED  Indicates that the optimizer will only consider marking as FIXED  The plan for , Regardless of other plans . for example , If there is 10  A baseline plan , Three of these plans are marked as FIXED, The optimizer will use only the best of the three plans , And ignore all the other plans . If a SQL  The plan baseline contains at least one enabled repaired plan , Then SQL  The plan baseline is FIXED  Of . If it's being repaired SQL  A new plan has been added to the plan baseline , Then manually declare these new plans as FIXED  Before , You can't use these new plans .

 

set autot off

select * from table(dbms_xplan.display_sql_plan_baseline (sql_handle => '&sql_handle', format => 'basic'));

 

SET SERVEROUTPUT ON

DECLARE

l_plans_altered  PLS_INTEGER;

BEGIN

l_plans_altered := DBMS_SPM.alter_sql_plan_baseline(

   sql_handle      => '&sql_handle',

   plan_name       => '&plan_name',

   attribute_name  => 'fixed',

   attribute_value => 'YES');

 

DBMS_OUTPUT.put_line('Plans Altered: ' || l_plans_altered);

END;

/

LHR@dlhr> SET SERVEROUTPUT ON

LHR@dlhr> DECLARE

  2   l_plans_altered  PLS_INTEGER;

  3  BEGIN

  4   l_plans_altered := DBMS_SPM.alter_sql_plan_baseline(

  5     sql_handle      => '&sql_handle',

  6     plan_name       => '&plan_name',

  7     attribute_name  => 'fixed',

  8     attribute_value => 'YES');

  9  

10   DBMS_OUTPUT.put_line('Plans Altered: ' || l_plans_altered);

11  END;

12  /

Enter value for sql_handle: SQL_4f19d3cf57be7303

old   5:    sql_handle      => '&sql_handle',

new   5:    sql_handle      => 'SQL_4f19d3cf57be7303',

Enter value for plan_name: SQL_PLAN_4y6fmtxbvwws3184920d2

old   6:    plan_name       => '&plan_name',

new   6:    plan_name       => 'SQL_PLAN_4y6fmtxbvwws3184920d2',

Plans Altered: 1

 

PL/SQL procedure successfully completed.

 

 

 

-- verification :

set autot off

col sql_handle for a35

col plan_name for a35

set lin 300

SELECT SQL_HANDLE,plan_name,origin,enabled,accepted,fixed

FROM   dba_sql_plan_baselines

WHERE  sql_text LIKE '%tb_spm_test_lhr%'

AND    sql_text NOT LIKE'%dba_sql_plan_baselines%';

 

LHR@dlhr> set autot off

LHR@dlhr> select * from table(dbms_xplan.display_sql_plan_baseline (sql_handle => '&sql_handle', format => 'basic'));

Enter value for sql_handle: SQL_4f19d3cf57be7303

old   1: select * from table(dbms_xplan.display_sql_plan_baseline (sql_handle => '&sql_handle', format => 'basic'))

new   1: select * from table(dbms_xplan.display_sql_plan_baseline (sql_handle => 'SQL_4f19d3cf57be7303', format => 'basic'))

 

PLAN_TABLE_OUTPUT

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------

 

--------------------------------------------------------------------------------

SQL handle: SQL_4f19d3cf57be7303

SQL text: SELECT description FROM   tb_spm_test_lhr WHERE  id = 100

--------------------------------------------------------------------------------

 

--------------------------------------------------------------------------------

Plan name: SQL_PLAN_4y6fmtxbvwws3184920d2         Plan id: 407445714

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

--------------------------------------------------------------------------------

 

Plan hash value: 2196561629

 

---------------------------------------------

| Id  | Operation         | Name            |

---------------------------------------------

|   0 | SELECT STATEMENT  |                 |

|   1 |  TABLE ACCESS FULL| TB_SPM_TEST_LHR |

---------------------------------------------

 

--------------------------------------------------------------------------------

Plan name: SQL_PLAN_4y6fmtxbvwws38b725570         Plan id: 2339526000

Enabled: YES     Fixed: NO      Accepted: YES     Origin: AUTO-CAPTURE

--------------------------------------------------------------------------------

 

Plan hash value: 2587945646

 

--------------------------------------------------------

| Id  | Operation                   | Name             |

--------------------------------------------------------

|   0 | SELECT STATEMENT            |                  |

|   1 |  TABLE ACCESS BY INDEX ROWID| TB_SPM_TEST_LHR  |

|   2 |   INDEX RANGE SCAN          | SPM_TEST_TAB_IDX |

--------------------------------------------------------

 

34 rows selected.

 

LHR@dlhr> set autot off

LHR@dlhr> col sql_handle for a35

LHR@dlhr> col plan_name for a35

LHR@dlhr> set lin 300

LHR@dlhr> SELECT SQL_HANDLE,plan_name,origin,enabled,accepted,fixed

  2  FROM   dba_sql_plan_baselines

  3  WHERE  sql_text LIKE '%tb_spm_test_lhr%'

  4  AND    sql_text NOT LIKE'%dba_sql_plan_baselines%';

 

SQL_HANDLE                          PLAN_NAME                           ORIGIN         ENA ACC FIX

----------------------------------- ----------------------------------- -------------- --- --- ---

SQL_4f19d3cf57be7303                SQL_PLAN_4y6fmtxbvwws3184920d2      MANUAL-LOAD    YES YES YES

SQL_4f19d3cf57be7303                SQL_PLAN_4y6fmtxbvwws38b725570      AUTO-CAPTURE   YES YES NO

 

 

 

-- Check out our previous SQL:

set autot trace

SELECT description FROM   tb_spm_test_lhr WHERE  id = 100;

 

LHR@dlhr> set autot trace

LHR@dlhr> SELECT description FROM   tb_spm_test_lhr WHERE  id = 100;

 

 

Execution Plan

----------------------------------------------------------

Plan hash value: 2196561629

 

-------------------------------------------------------------------------------------

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

-------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |                 |     1 |    25 |    13   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| TB_SPM_TEST_LHR |     1 |    25 |    13   (0)| 00:00:01 |

-------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   1 - filter("ID"=100)

 

Note

-----

   - SQL plan baseline "SQL_PLAN_4y6fmtxbvwws3184920d2" used for this statement

 

 

Statistics

----------------------------------------------------------

          6  recursive calls

          8  db block gets

         46  consistent gets

          0  physical reads

          0  redo size

        546  bytes sent via SQL*Net to client

        519  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

 

 

-- We've gone through a full table scan , According to the previous example , We know that it's better to go around here , But because we set the execution plan of full table scan to fixed, So use this execution plan first .

Two .4   summary

1、coe_xfr_sql_profile.sql The script needs to start from MOS download , Wheat seedlings have been downloaded and put on the cloud disk , You can download it , You know the address

2、outline yes 9i The content of ,SQL Profile yes 10g New features ,SPM yes 11g New features