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;
阅读(1684) | 评论(0) | 转发(0) |