sql profile 2

SQL> conn test/test
Connected.
SQL> create table lgx as select * from dba_objects;

SQL> create index ind_lgx_objid on lgx(object_id) tablespace users;

Index created.

SQL> set autot trace exp
SQL> select object_name from lgx where object_id=102

Execution Plan
----------------------------------------------------------
Plan hash value: 2552784261

---------------------------------------------------------------------------------------------
| Id  | Operation            | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |            |      1 |     79 |      2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| LGX        |      1 |     79 |      2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN        | IND_LGX_OBJID |      1 |        |      1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_ID"=102)

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

SQL> select /*+full(lgx)*/ object_name from lgx where object_id=999;

SQL> select sql_text,sql_id ,a.PLAN_HASH_VALUE from v$sql a where sql_text   like '%lgx%'

select /*+full(lgx)*/ object_name from lgx where object_id=999
8v98vab5x14vq       2904261390


SQL> @coe_xfr_sql_profile.sql

Parameter 1:
SQL_ID (required)

Enter value for 1: 8v98vab5x14vq


PLAN_HASH_VALUE AVG_ET_SECS
--------------- -----------
     2904261390        .014

Parameter 2:
PLAN_HASH_VALUE (required)

Enter value for 2: 2904261390

Values passed to coe_xfr_sql_profile:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL_ID           : "8v98vab5x14vq"
PLAN_HASH_VALUE: "2904261390"

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_8v98vab5x14vq_2904261390.sql
on TARGET system in order to create a custom SQL Profile
with plan 2904261390 linked to adjusted sql_text.


COE_XFR_SQL_PROFILE completed.

$ vi coe_xfr_sql_profile_8v98vab5x14vq_2904261390.sql

 14  wa(q'[ select object_name from lgx where object_id=999]'); --去除HINT, 强制SQL走全表扫描
     force_match => TRUE
     
SQL>@coe_xfr_sql_profile_8v98vab5x14vq_2904261390.sql

SQL>select name,type,status from dba_sql_profiles;

NAME                   TYPE    STATUS
------------------------------ ------- --------
SQLPROFILE_MYPROFILE1           MANUAL  ENABLED
coe_8v98vab5x14vq_2904261390   MANUAL  ENABLED

SQL> r
  1* select object_name from lgx where object_id=910

Execution Plan
----------------------------------------------------------
Plan hash value: 2904261390

--------------------------------------------------------------------------
| Id  | Operation      | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |     |     1 |    30 |   345   (1)| 00:00:05 |
|*  1 |  TABLE ACCESS FULL| LGX  |     1 |    30 |   345   (1)| 00:00:05 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_ID"=910)

Note
-----
   - SQL profile "coe_8v98vab5x14vq_29042613" used for this statement

 
+++删除SQL PROFILE
SQL>begin
DBMS_SQLTUNE.DROP_SQL_PROFILE(name => 'coe_8v98vab5x14vq_2904261390');
end;