网站建设优化方案东莞网站设计品牌
测试使用 sql profile 固定执行计划：
Oracle 10g之前有outlines,10g之后 sql profile 。如果针对非绑定变量的sql,outlines则效果不佳，不建议使用 。
1、准备测试用表
SQL> create table zzh_ob as select * from dba_objects;
SQL> create index ind_obid on zzh_ob(object_id);
SQL> select object_id from zzh_ob where rownum<2;
OBJECT_ID
----------
16
SQL> exec dbms_stats.gather_table_stats(user,'zzh_ob',cascade=>true);
原sql执行计划:
SQL> set autot trace explain
SQL> select * from zzh_ob where object_id=16;
Execution Plan
----------------------------------------------------------
Plan hash value: 3485916696
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 127 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| ZZH_OB | 1 | 127 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_OBID | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=16)
新sql执行计划
SQL> select /*+ full(zzh_ob) */ * from zzh_ob where object_id=16;
Execution Plan
----------------------------------------------------------
Plan hash value: 474274488
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 127 | 388 (1)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| ZZH_OB | 1 | 127 | 388 (1)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=16)
2、获取新sql的sql_id
SQL> col sql_id for a20
SQL> col sql_text for a120
SQL> select sql_id,sql_text from v$sql where sql_text like '%full(zzh_ob)%';
SQL_ID SQL_TEXT
-------------------- ------------------------------------------------------------------------------------------------------------------------
g5btxvsh51ct5 EXPLAIN PLAN SET STATEMENT_ID='PLUS1200001' FOR select sql_id,sql_text from v$sql where sql_text like '%full(zzh_ob)%'
4kn9w9q3xf73k EXPLAIN PLAN SET STATEMENT_ID='PLUS1200001' FOR select /*+ full(zzh_ob) */ * from zzh_ob where object_id=16
68x7hd7uaqqk1 select sql_id,sql_text from v$sql where sql_text like '%full(zzh_ob)%'
1syfgv2q5ggn2 select /*+ full(zzh_ob) */ * from zzh_ob where object_id=16
3、获取新sql的outline
SQL> set pagesize 1000
SQL> select * from table(dbms_xplan.display_cursor('1syfgv2q5ggn2',null,'outline'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 1syfgv2q5ggn2, child number 0
-------------------------------------
select /*+ full(zzh_ob) */ * from zzh_ob where object_id=16
Plan hash value: 474274488
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 388 (100)| |
|* 1 | TABLE ACCESS FULL| ZZH_OB | 1 | 127 | 388 (1)| 00:00:01 |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('19.1.0')
DB_VERSION('19.1.0')
ALL_ROWS
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "ZZH_OB"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=16)
32 rows selected.
4、创建sql profile(SQLPROFILE_01)
SQL> declare
v_hints sys.sqlprof_attr;
begin
v_hints:=sys.sqlprof_attr(
'BEGIN_OUTLINE_DATA',
'IGNORE_OPTIM_EMBEDDED_HINTS',
'OPTIMIZER_FEATURES_ENABLE(''19.1.0'')',
'DB_VERSION(''19.1.0'')',
'ALL_ROWS',
'OUTLINE_LEAF(@"SEL$1")',
'FULL(@"SEL$1" "ZZH_OB"@"SEL$1")',
'END_OUTLINE_DATA');
dbms_sqltune.import_sql_profile(
'select * from zzh_ob where object_id=16',
v_hints,'SQLPROFILE_01',
force_match=>true,replace=>false);
end;
/
5、查看是否使用 sql profile
SQL> set autot trace explain
SQL> select * from zzh_ob where object_id=16;
Execution Plan
----------------------------------------------------------
Plan hash value: 474274488
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 127 | 388 (1)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| ZZH_OB | 1 | 127 | 388 (1)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=16)
Note
-----
- SQL profile "SQLPROFILE_01" used for this statement
Statistics
----------------------------------------------------------
247 recursive calls
0 db block gets
1544 consistent gets
2 physical reads
0 redo size
2685 bytes sent via SQL*Net to client
401 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
11 sorts (memory)
0 sorts (disk)
1 rows processed
查询已经创建的 sql profile ：
SELECT name, created, category, sql_Text from dba_sql_profiles ORDER BY created DESC;