SQL Profile使用1
来源:互联网 发布:淘宝默认确认收货时间 编辑:程序博客网 时间:2024/05/17 01:55
SQL Profile的作用
(1)锁定或者说是稳定执行计划。
(2)在不能修改应用中的SQL的情况下使SQL语句按指定的执行计划运行SQL Profile对于一下类型语句有效:
SELECT语句;
UPDATE语句;
INSERT语句(仅当使用SELECT子句时有效);
DELETE语句;
CREATE语句(仅当使用SELECT子句时有效);
MERGE语句(仅当作UPDATE和INSERT操作时有效)。
使用SQL Profile所在用户还必须有CREATE ANY SQL PROFILE、DROP ANY SQL PROFILE和ALTER ANY SQL PROFILE等系统权限。
使用outline来确定sql profile执行计划。演示过程如下
SQL> create table t_tables as
2 select t2.object_id table_id,t1.OWNER table_owner,t1.TABLE_NAME table_name,
3 TABLESPACE_NAME,NUM_ROWS from dba_tables t1,dba_objects t2 where t1.table_name=t2.object_name;
Table created.
SQL> create table t_objects as select object_id,OWNER,object_name,object_type,DATA_OBJECT_ID from dba_objects;
Table created.
SQL> select count(*) from t_objects;
COUNT(*)
----------
99648
SQL> create index t_objects_idx on t_objects(object_id);
Index created.
搜集表的统计信息后
SQL> exec dbms_stats.gather_table_stats(user,'t_objects',cascade=>true,method_opt=>'for all columns size 1');
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_table_stats(user,'t_tables',cascade=>true,method_opt=>'for all columns size 1');
PL/SQL procedure successfully completed.
来看sql语句:
select t1.*,t2.owner from t_tables t1,t_objects t2 where t1.table_name like '%T1%' and t1.table_id=t2.object_id;
执行计划
SQL> select t1.*,t2.owner from t_tables t1,t_objects t2 where t1.table_name like '%T1%' and t1.table_id=t2.object_id;
8 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3978708767
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 341 | 16027 | 166 (4)| 00:00:02 |
|* 1 | HASH JOIN | | 341 | 16027 | 166 (4)| 00:00:02 |
|* 2 | TABLE ACCESS FULL| T_TABLES | 171 | 6156 | 7 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T_OBJECTS | 99648 | 1070K| 157 (2)| 00:00:02 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."TABLE_ID"="T2"."OBJECT_ID")
2 - filter("T1"."TABLE_NAME" LIKE '%T1%')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
713 consistent gets
0 physical reads
0 redo size
897 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
8 rows processed
执行计划中表的连接是hash join,改变两个表的连接为NESTED LOOPS ,来看看执行计划的变化
SQL> explain plan for select /*+ use_nl(t1 t2) index(t2) */ t1.*,t2.owner
2 from t_tables t1,t_objects t2
3 where t1.table_name like '%T1%' and t1.table_id=t2.object_id;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3307320638
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 341 | 16027 | 521 (1)| 00:00:07 |
| 1 | TABLE ACCESS BY INDEX ROWID| T_OBJECTS | 2 | 22 | 3 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 341 | 16027 | 521 (1)| 00:00:07 |
|* 3 | TABLE ACCESS FULL | T_TABLES | 171 | 6156 | 7 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | T_OBJECTS_IDX | 2 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("T1"."TABLE_NAME" LIKE '%T1%')
4 - access("T1"."TABLE_ID"="T2"."OBJECT_ID")
17 rows selected.
看到使用hints后执行计划已经改变,继续往下
2 select /*+ use_nl(t1 t2) index(t2) */ t1.*,t2.owner
3 from t_tables t1,t_objects t2
4 where t1.table_name like '%T1%' and t1.table_id=t2.object_id;
Explained.
SQL>
SQL> select * from table(dbms_xplan.display(null,null,'outline'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3307320638
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 341 | 16027 | 521 (1)| 00:00:07 |
| 1 | TABLE ACCESS BY INDEX ROWID| T_OBJECTS | 2 | 22 | 3 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 341 | 16027 | 521 (1)| 00:00:07 |
|* 3 | TABLE ACCESS FULL | T_TABLES | 171 | 6156 | 7 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | T_OBJECTS_IDX | 2 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
USE_NL(@"SEL$1" "T2"@"SEL$1")
LEADING(@"SEL$1" "T1"@"SEL$1" "T2"@"SEL$1")
INDEX(@"SEL$1" "T2"@"SEL$1" ("T_OBJECTS"."OBJECT_ID"))
FULL(@"SEL$1" "T1"@"SEL$1")
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("T1"."TABLE_NAME" LIKE '%T1%')
4 - access("T1"."TABLE_ID"="T2"."OBJECT_ID")
33 rows selected.
使用hint产生的outline data数据来稳定sql的执行计划,编写sql profile
SQL> declare
2 v_hints sys.sqlprof_attr;
3 begin
4 v_hints:=sys.sqlprof_attr(
5 'BEGIN_OUTLINE_DATA',
6 'USE_NL(@"SEL$1" "T2"@"SEL$1")', -------?a??ê?óéóúhint2úéú,??êμ?ò??Dèòaμ??íê??a??
7 'LEADING(@"SEL$1" "T1"@"SEL$1" "T2"@"SEL$1")',
8 'INDEX(@"SEL$1" "T2"@"SEL$1" ("T_OBJECTS"."OBJECT_ID"))',
9 'FULL(@"SEL$1" "T1"@"SEL$1")',
10 'OUTLINE_LEAF(@"SEL$1")',
11 'ALL_ROWS',
12 'OPTIMIZER_FEATURES_ENABLE(''10.2.0.1'')',
13 'IGNORE_OPTIM_EMBEDDED_HINTS',
14 'END_OUTLINE_DATA');
15 dbms_sqltune.import_sql_profile(
16 'select t1.*,t2.owner from t_tables t1,t_objects t2
17 where t1.table_name like ''%T1%'' and t1.table_id=t2.object_id',
18 v_hints,'SQLPROFILE_dbyang', --sql profile ??3?
19 force_match=>true,replace=>true);
20 end;
21 /
PL/SQL procedure successfully completed.
再回过来看看执行计划
SQL> set autot traceonly
SQL> select t1.*,t2.owner from t_tables t1,t_objects t2 where t1.table_name like '%T1%' and t1.table_id=t2.object_id;
8 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3307320638
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 341 | 16027 | 521 (1)| 00:00:07 |
| 1 | TABLE ACCESS BY INDEX ROWID| T_OBJECTS | 2 | 22 | 3 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 341 | 16027 | 521 (1)| 00:00:07 |
|* 3 | TABLE ACCESS FULL | T_TABLES | 171 | 6156 | 7 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | T_OBJECTS_IDX | 2 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("T1"."TABLE_NAME" LIKE '%T1%')
4 - access("T1"."TABLE_ID"="T2"."OBJECT_ID")
Note
-----
- SQL profile "SQLPROFILE_dbyang" used for this statement
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
39 consistent gets
0 physical reads
0 redo size
897 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
8 rows processed
Note
-----
- SQL profile "SQLPROFILE_dbyang" used for this statement
可以看到sql profile使用outline固定执行计划
如果要删除sql profile,使用如下过程
BEGIN
DBMS_SQLTUNE.DROP_SQL_PROFILE(name => 'SQLPROFILE_dbseek');
END;
/
- SQL Profile使用1
- sql profile使用
- Oracle SQL Profile使用
- sql server profile 的使用
- MySQL使用profile分析SQL执行状态
- SQL Profile 总结(四)--使用示例
- SQL Profile 总结(四)--使用示例
- 使用oracle sql profile固定执行计划
- MySQL使用profile分析SQL执行状态
- MySql 使用SHOW PROFILE 进行SQL 分析
- MySql 使用SHOW PROFILE 进行SQL 分析
- sql profile
- sql profile
- sql profile
- SQL PROFILE
- Sql profiles-->使用sql tuning生成sql profile
- 使用SQL Profile及SQL Tuning Advisor固定执行计划
- 使用SQL Profile稳定SQL语句的执行计划
- 最好不要使用游标
- linux根文件系统 /etc/resolv.conf 文件详解
- 10个奇特的 HTML5 单页网站
- Error处理:Conversion to Dalvik format failed: Unable to execute dex: java.nio.BufferOverflowException.
- easyui tabs href请求 IE浏览器请求发送2次 火狐正常
- SQL Profile使用1
- Windows下apache_2.2.9与php-5.2.6的安装与配置
- hdu1010矩形图的搜索
- EXCEL 以不同颜色显示某列重复项
- 缓解加载显示大量控件时闪烁和线条显示的问题
- 大型软化工业水处理工程
- Sar (Linux)命令,查看linux CPU使用
- javascript amcharts 3 破解方法
- 【IOS—UI】UITableViewCellStyle的四种样式