控制執行計劃之-SQL Profile（一）

建立一個測試表test，在列object_id上建立索引，建立完成後分析表。

>select  DATABASE_ROLE from v$database;

DATABASE_ROLE

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

PRIMARY

create table test as select * from dba_objects;

create index t on test(object_id);

begin

    dbms_stats.gather_table_stats(ownname          =>'monitor',

                                  tabname          => 'TEST',

                                  no_invalidate    => FALSE,

                                  estimate_percent => 100,

                                  force            => true,

                                  degree         => 5,

                                  method_opt       => 'for  all columns  size 1',

                                  cascade          => true);

  end;

  /

檢視SQL_ID，根據SQL_ID獲取語句的執行計劃。

>select sql_id,sql_text from v$sql where sql_text like '%test w%';

SQL_ID                              SQL_TEXT

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

f16x80gfqm2fv                 select sql_id,sql_text from v$sql where sql_text like '%test w%'

>@plan

Enter value for hash_value: btuhzhv88wwv3

PLAN_TABLE_OUTPUT

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

SQL_ID  btuhzhv88wwv3, child number 0

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

select * from test where object_id=1

Plan hash value: 1353936115

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

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

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

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

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

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

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

Predicate Information (identified by operation id):

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

   2 - access("OBJECT_ID"=1)

執行計劃選擇走了索引掃描，如果我們想用SQL Profile來穩固執行計劃：

>@profile

Enter value for sql_id: btuhzhv88wwv3

PLAN_TABLE_OUTPUT

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

SQL_ID  btuhzhv88wwv3, child number 0

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

select * from test where object_id=1

Plan hash value: 1353936115

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

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

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

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

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

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

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

Outline Data

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

  /*+

      BEGIN_OUTLINE_DATA

      IGNORE_OPTIM_EMBEDDED_hintS

      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')

      DB_VERSION('11.2.0.3')

      OPT_PARAM('_optim_peek_user_binds' 'false')

      OPT_PARAM('_optimizer_skip_scan_enabled' 'false')

      ALL_ROWS

      OUTLINE_LEAF(@"SEL$1")

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

      END_OUTLINE_DATA

  */

Predicate Information (identified by operation id):

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

   2 - access("OBJECT_ID"=1)

35 rows selected.

Enter value for hint_text: INDEX_RS_ASC(@"SEL$1" "TEST"@"SEL$1" ("TEST"."OBJECT_ID"))

Profile profile_btuhzhv88wwv3_dwrose created.

登入備庫，看看同樣的查詢是否已經可以使用到新建立的SQL Profile了：

>conn monitor/monitor

Connected.

>select  DATABASE_ROLE from v$database;

DATABASE_ROLE

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

PHYSICAL STANDBY

>select * from test where object_id=1;

no rows selected

>@plan

Enter value for hash_value: btuhzhv88wwv3

PLAN_TABLE_OUTPUT

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

SQL_ID  btuhzhv88wwv3, child number 0

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

select * from test where object_id=1

Plan hash value: 1353936115

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

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

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

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

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

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

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

Predicate Information (identified by operation id):

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

   2 - access("OBJECT_ID"=1)

Note

-----

   - SQL profile profile_btuhzhv88wwv3_dwrose used for this statement

我們看到SQL Profile已經使用到了。