控制执行计划之-SQL Profile（一）

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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22034023/viewspace-1245102/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22034023/viewspace-1245102/