SQL Profile與dataguard

SQL Profile還有一個極大的好處就是它可以在主庫上建立後在備庫上使用，試想如果你有一套ACTIVE DATAGUARD對外提供查詢服務，遭遇到了執行計劃錯誤的問題，而這些SQL固化技術都不能在GATAGUARD上使用，那心情會有多麼糟糕。SPM baseline不能用於DATAGUARD，但是SQL profile是支援的。如果想讓SQL Profile在備庫生效，我們首先需要在主庫上建立一個SQL Profile，然後在備庫執行同樣SQL文字的SQL時，就會使用到建立的SQL PRIFILE，我們來看看實驗：

建立一個測試表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

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

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

| 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

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

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

| 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

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

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

| 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已經使用到了。

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

SQL Profile與dataguard

相關文章