【DB笔试面试605】在Oracle中,SQL概要(SQL Profile)的作用是什么?
- 2019 年 10 月 10 日
- 筆記
题目部分
在Oracle中,SQL概要(SQL Profile)的作用是什么?
♣
答案部分
SQL Profile就是为某条SQL语句提供除了系统统计信息、对象(表和索引等)统计信息之外的其它信息,比如运行环境、额外的更准确的统计信息,以帮助优化器为SQL语句选择更适合的执行计划。SQL Profile可以说是Outline的进化。Outline能够实现的功能SQL Profile也完全能够实现,而SQL Profile具有Outline不具备的优化,最重要的有两点:①SQL Profile更容易生成、更改和控制。②SQL Profile在对SQL语句的支持上做得更好,也就是适用范围更广。
对于sqlprof_attr部分的数据可以使用脚本coe_xfr_sql_profile.sql脚本生成。
使用SQL Profile的两个目的:①锁定或者说是稳定执行计划。②在不能修改应用中的SQL的情况下使SQL语句按指定的执行计划运行。
SQL Profile最大的优点是在不修改SQL语句和会话执行环境的情况下去优化SQL的执行效率,适合无法在应用程序中修改SQL时。
SQL Profile对以下类型语句有效:
l SELECT语句;
l UPDATE语句;
l INSERT语句(仅当使用SELECT子句时有效);
l DELETE语句;
l CREATE语句(仅当使用SELECT子句时有效);
l MERGE语句(仅当作UPDATE和INSERT操作时有效)。
另外,使用SQL Profile还必须有CREATE ANY SQL PROFILE、DROP ANY SQL PROFILE和ALTER ANY SQL PROFILE等系统权限。
有两种生成SQL Profile的方法,分别是手动和采用STA来生成。
(一)SQL Profile使用示例–手工创建SQL Profile
创建测试表,根据DBA_OBJECTS创建,OBJECT_ID上有索引
LHR@dlhr> select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production PL/SQL Release 11.2.0.4.0 - Production CORE 11.2.0.4.0 Production TNS for IBM/AIX RISC System/6000: Version 11.2.0.4.0 - Production NLSRTL Version 11.2.0.4.0 - Production LHR@dlhr> Create table TB_LHR_20160525 as select * from dba_objects; Table created. LHR@dlhr> create index IND_TB_LHR_ID on TB_LHR_20160525(object_id); Index created.
查看SQL默认执行计划,走了索引,通过指定Outline可以获取到系统为我们生成的hint
LHR@dlhr> explain plan for select * from TB_LHR_20160525 where object_id= :a; Explained. LHR@dlhr> select * from table(dbms_xplan.display(null,null,'outline')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------- Plan hash value: 4254050152 ----------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 886 | 179K| 7 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| TB_LHR_20160525 | 886 | 179K| 7 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IND_TB_LHR_ID | 354 | | 1 (0)| 00:00:01 | ----------------------------------------------------------------------------------------------- Outline Data ------------- /*+ BEGIN_OUTLINE_DATA INDEX_RS_ASC(@"SEL$1" "TB_LHR_20160525"@"SEL$1" ("TB_LHR_20160525"."OBJECT_ID")) OUTLINE_LEAF(@"SEL$1") ALL_ROWS DB_VERSION('11.2.0.4') OPTIMIZER_FEATURES_ENABLE('11.2.0.4') IGNORE_OPTIM_EMBEDDED_HINTS END_OUTLINE_DATA */ Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OBJECT_ID"=TO_NUMBER(:A)) Note ----- - dynamic sampling used for this statement (level=2) 32 rows selected.
如果我们想让它走全表扫描,首先获取全表扫描HINT
LHR@dlhr> explain plan for select /*+ full(TB_LHR_20160525) */* from TB_LHR_20160525 where object_id= :a; Explained. LHR@dlhr> select * from table(dbms_xplan.display(null,null,'outline')); PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------- Plan hash value: 345881005 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 886 | 179K| 352 (2)| 00:00:05 | |* 1 | TABLE ACCESS FULL| TB_LHR_20160525 | 886 | 179K| 352 (2)| 00:00:05 | ------------------------------------------------------------------------------------- Outline Data ------------- /*+ BEGIN_OUTLINE_DATA FULL(@"SEL$1" "TB_LHR_20160525"@"SEL$1") OUTLINE_LEAF(@"SEL$1") ALL_ROWS DB_VERSION('11.2.0.4') OPTIMIZER_FEATURES_ENABLE('11.2.0.4') IGNORE_OPTIM_EMBEDDED_HINTS END_OUTLINE_DATA */ Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("OBJECT_ID"=TO_NUMBER(:A)) Note ----- - dynamic sampling used for this statement (level=2) 31 rows selected.
可以看到全表扫描的Hint已经为我们生成了,我们选取必要的hint就OK了,其它的可以不要,使用SQL Profile
LHR@dlhr> declare 2 v_hints sys.sqlprof_attr; 3 begin 4 v_hints := sys.sqlprof_attr('FULL(@"SEL$1" "TB_LHR_20160525"@"SEL$1")'); ----------从上面Outline Data部分获取到的HINT 5 dbms_sqltune.import_sql_profile('select * from TB_LHR_20160525 where object_id= :a', ----------SQL语句部分 6 v_hints, 7 'TB_LHR_20160525', --------PROFILE 的名字 8 force_match => true); 9 end; 10 / PL/SQL procedure successfully completed.
查看是否生效,已经生效了:
LHR@dlhr> explain plan for select * from TB_LHR_20160525 where object_id= :a; Explained. LHR@dlhr> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------------------------------------Plan hash value: 345881005 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 886 | 179K| 352 (2)| 00:00:05 | |* 1 | TABLE ACCESS FULL| TB_LHR_20160525 | 886 | 179K| 352 (2)| 00:00:05 | ------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("OBJECT_ID"=TO_NUMBER(:A)) Note ----- - dynamic sampling used for this statement (level=2) - SQL profile "TB_LHR_20160525" used for this statement 18 rows selected. LHR@dlhr> SELECT b.name,d.sql_text, extractvalue(value(h),'.') as hints 2 FROM dba_sql_profiles d,SYS.SQLOBJ$DATA A, 3 SYS.SQLOBJ$ B, 4 TABLE(XMLSEQUENCE(EXTRACT(XMLTYPE(A.COMP_DATA), 5 '/outline_data/hint'))) h 6 where a.signature = b.signature 7 and a.category = b.category 8 and a.obj_type = b.obj_type 9 and a.plan_id = b.plan_id 10 and a.signature=d.signature 11 and D.name = 'TB_LHR_20160525'; NAME SQL_TEXT HINTS ------------------------------ -------------------------------------------------------------------------------- ------------------------------------------------------- TB_LHR_20160525 select * from TB_LHR_20160525 where object_id= :a FULL(@"SEL$1" "TB_LHR_20160525"@"SEL$1")
最麻烦的sqlprof_attr('FULL(t1@SEL$1)')是这里的格式如何写,在Mos上的文章note 215187.1中的sqlt.zip的目录utl中提供了脚本coe_xfr_sql_profile.sql可以生成这些信息。
本文选自《Oracle程序员面试笔试宝典》,作者:李华荣。