如何手工创建SQL Profile

本篇内容介绍了“如何手工创建SQL Profile”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！ORACLE 10G版本，可以通过查看sys.sqlprof$、sys.sqlprof$attr来获得SQL Profile使用的hint，但是11G后这两个数据字典基表不再有效，需要通过查看sys.sqlobj$data、sys.sqlobj$来查看SQL Profile使用的hint。 我们继续接着上面一节，看看通过SQL Tuning Advisor创建的SQL Profile使用到的hint。（11G版本） SQL>SELECT extractValue(value(h),’.’) AS hint 2 FROM sys.sqlobj$data od, sys.sqlobj$ so, 3 table(xmlsequence(extract(xmltype(od.comp_data),’/outline_data/hint’))) h 4 WHERE so.name = ‘SYS_SQLPROF_01479094feeb0003’ 5 AND so.signature = od.signature 6 AND so.category = od.category 7 AND so.obj_type = od.obj_type 8 AND so.plan_id = od.plan_id; hint——————————————————————————OPT_ESTIMATE(@”SEL$1″, TABLE, “TEST”@”SEL$1″, SCALE_ROWS=0.004)OPT_ESTIMATE(@”SEL$1”, INDEX_SCAN, “TEST”@”SEL$1”, “T_IND”, SCALE_ROWS=0.004)OPTIMIZER_FEATURES_ENABLE(default)这些hint都不是我们日常所用的hint，大部分是以OPT_ESTIMATE打头的，例如OPT_ESTIMATE(@”SEL$1″, TABLE, “TEST”@”SEL$1”, SCALE_ROWS=0.004)代表的是把表test经过谓词过滤后返回的基数修正为原始评估的基数乘以0.004，也就是缩小了250倍：基数从25000缩小为100。按照OPT_ESTIMATE提示缩小后的基数非常的准确，由于OPT_ESTIMATE告诉了优化器非常准确的基数信息，因此优化器再次评估执行计划的时候选择了索引扫描。就如我们看到的SQL Profile并没有明确的告诉优化器使用索引扫描，只是告诉它应该如何纠正优化器的原始评估，以得到更好的基数信息。但是随着时间的推移，这些提示信息可能会变得过时，最终变得不再有效，因此使用了SQL Profile的SQL也可能会遭遇执行计划发生变化，没起到锁定执行计划的作用。本章后面会介绍如何让SQL Profile起到锁定执行计划的作用。 nNote：SQL Profile里可能会包含哪些hint？这里对SQL Profile里一些常出现的hint做出解释。1)OPT_ESTIMATE(@SEL$1, TABLE, TEST@SEL$1, SCALE_ROWS=10)返回10倍于预估的表的基数2)OPT_ESTIMATE(@SEL$1, INDEX_SCAN, TEST@SEL$1, TEST_IDX, SCALE_ROWS=.1)返回十分之一的预估的索引的基数3)OPT_ESTIMATE(@SEL$1, JOIN, (TEST1@SEL$1,TEST2@SEL$1),SCALE_ROWS=4.2)当test1,test2做join时，返回4.2倍与预估的基数4)TABLE_STATS(“HR”, “EMPLOYEES”, scale, blocks=10, rows=107)为表提供统计信息：如行数、块数5)COLUMN_STATS(“HR”, “EMPLOYEES”, “EMPLOYEE_ID”, scale,length=3 DISTINCT=107 nulls=0 min=100 max=207)为表上的列提供统计信息：如空值、最大值、最小值等6)INDEX_STATS(“HR”, “EMPLOYEES”, “EMP_IDX”, scale, blocks=5, rows=107)为索引提供统计信息：如索引块数、索引条目数7)ALL_ROWS设置优化器的模式为ALL_ROWS8)IGNORE_OPTIM_EMBEDDED_hintS忽略嵌入在SQL里的hint虽然ORACLE官方只提供了通过SQL Tuning Advisor来创建SQL Profile，但是一些ORACLE的爱好者慢慢的发现了SQL Tuning Advisor底层的运作机制，发现SQL Tuning Advisor其实是通过调用dbms_sqltune包的import_sql_profile来创建的SQL Profile。通过import_sql_profile过程，可以为任何的SQL创建想要的SQL Profile。我们来看看import_sql_profile如何使用。 PROCEDURE IMPORT_SQL_PROFILEArgument Name Type In/Out Default?—————————— ———————– —— ——–SQL_TEXT CLOB INPROFILE SQLPROF_ATTR INNAME VARCHAR2 IN DEFAULTDESCRIPTION VARCHAR2 IN DEFAULTCATEGORY VARCHAR2 IN DEFAULTVALIDATE BOOLEAN IN DEFAULTREPLACE BOOLEAN IN DEFAULTFORCE_MATCH BOOLEAN IN DEFAULT使用IMPORT_SQL_PROFILE来创建SQL Profile需要提供一些参数，SQL_TEXT指SQL语句的文本，我们可以从v$sqlarea的sql_fulltext中获得SQL语句的完整文本信息，PROFILE指的是需要为这个SQL文本绑定的hint集合，name为SQL Profile的名称，DESCRIPTION为对SQL Profile的描述信息，CATEGORY为SQL Profile所属的类信息，默认为default，VALIDATE代表创建的SQL Profile是否有效，默认为true,REPLACE代表是否取代之前存在的SQL Profile，FORCE_MATCH代表采用何种文本标准化方式产生签名，默认为false。关于FORCE_MATCH的意义，在本章文本标准化与signature一节有详细解释。我们来手工创建一个SQL Profile看看： SQL>exec dbms_sqltune.drop_sql_profile(‘profile_c37q7z5qjnwwf_dwrose’);PL/SQL procedure successfully completed.SQL>declare 2 l_profile_name varchar2(30); 3 cl_sql_text clob; 4 begin 5 select sql_fulltext 6 into cl_sql_text 7 from v$sqlarea 8 where sql_id = ‘c37q7z5qjnwwf’; 9 10 select ‘profile_’ || ‘c37q7z5qjnwwf’ || ‘_dwrose’ 11 into l_profile_name 12 from dual; 13 dbms_sqltune.import_sql_profile(sql_text => cl_sql_text, 14 profile => 15 sqlprof_attr(‘INDEX_RS_ASC(TEST T_IND)’), 16 category => ”, 17 name => l_profile_name, 18 force_match => FALSE); 19 end; 20 / PL/SQL procedure successfully completed.我们先通过dbms_sqltune包的drop_sql_profile过程删除了通过SQL Tuning Advisor创建的SQL Profile，然后通过import_sql_profile手工创建了一个SQL Profile，而且我们使用了我们常见的hint INDEX_RS_ASC(TEST T_IND)，而不是SQL Profile默认的以OPT_ESTIMATE打头的hint，上面的代码已经成功的创建了一个SQL Profile，我们看看使用常规的hint会不会起作用。 SQL>select count(name) from test where status=’Inactive’;COUNT(NAME)———–1001 row selected.SQL>select * from table(dbms_xplan.display_cursor);PLAN_TABLE_OUTPUT——————————————————————————-SQL_ID c37q7z5qjnwwf, child number 1————————————-select count(name) from test where status=’Inactive’Plan hash value: 1950795681—————————————————————————| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |—————————————————————————| 0 | SELECT STATEMENT | | | | 51 (100)| || 1 | SORT AGGREGATE | | 1 | 21 | | ||* 2 | TABLE ACCESS FULL| TEST | 25000 | 512K| 51 (2)| 00:00:01 |—————————————————————————Note—— SQL Profile profile_c37q7z5qjnwwf_dwrose used for this statementSQL>SELECT extractValue(value(h),’.’) AS hint 2 FROM sys.sqlobj$data od, sys.sqlobj$ so, 3 table(xmlsequence(extract(xmltype(od.comp_data),’/outline_data/hint’))) h 4 WHERE so.name = ‘profile_c37q7z5qjnwwf_dwrose’ 5 AND so.signature = od.signature 6 AND so.category = od.category 7 AND so.obj_type = od.obj_type 8 AND so.plan_id = od.plan_id; hint——————————————————————————-INDEX_RS_ASC(TEST T_IND)虽然执行计划的输出Note部分显示已经使用到了SQL Profile，但是执行计划并没有如我们预期一样被改变，依然是全表扫描，查看存储hint的基表也显示索引扫描的hint已经被绑定到了这个SQL上免费主机域名，那么问题出哪了？ 这是由于SQL Profile对于hint是非常挑剔的，SQL Profile里接受的hint需要提供Query Block Name(初始化参数类的hint不需要提供Query Block Name），否则优化器会忽略掉这些hint，我们重新设置SQL Profile的Hints，在Hints中加上Query Block Name看看。（Query Block Name相关知识参考本章Query Block Name一节）SQL>declare 2 l_profile_name varchar2(30); 3 cl_sql_text clob; 4 begin 5 select sql_fulltext 6 into cl_sql_text 7 from v$sqlarea 8 where sql_id = ‘c37q7z5qjnwwf’; 9 10 select ‘profile_’ || ‘c37q7z5qjnwwf’ || ‘_dwrose’ 11 into l_profile_name 12 from dual; 13 dbms_sqltune.import_sql_profile(sql_text => cl_sql_text, 14 profile => 15 sqlprof_attr(‘INDEX_RS_ASC(@SEL$1 TEST@SEL$1 T_IND))’), 16 category => ”, 17 name => l_profile_name, 18 force_match => FALSE); 19 end; 20 / PL/SQL procedure successfully completed.SQL>select count(name) from test where status=’Inactive’;COUNT(NAME)———–1001 row selected.SQL>select * from table(dbms_xplan.display_cursor);PLAN_TABLE_OUTPUT——————————————————————————-SQL_ID c37q7z5qjnwwf, child number 1————————————-select count(name) from test where status=’Inactive’Plan hash value: 4130896540————————————————————————————–| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |————————————————————————————–| 0 | SELECT STATEMENT | | | | 218 (100)| || 1 | SORT AGGREGATE | | 1 | 21 | | || 2 | TABLE ACCESS BY INDEX ROWID| TEST | 25000 | 512K| 218 (1)| 00:00:03 ||* 3 | INDEX RANGE SCAN | T_IND | 25000 | | 63 (0)| 00:00:01 |————————————————————————————–Note—— SQL Profile profile_c37q7z5qjnww免费主机域名f_dwrose used for this statement这一次hint起作用了，执行计划输出的Note部分可以知道创建的SQL Profile已经起作用了， 执行计划已经走了索引扫描，看来SQL Profile可以接受常规的hint ,只不过这些hint要包含Query Block Name，如果SQL Profile发现指定的hint无效，会简单的忽略掉这些hint，不会报任何的错误，也不会做任何的校验。既然常规的hint可以对SQL Profile起作用，那么我们也可以用SQL Profile来锁定执行计划了。从上面的执行计划输出也可以看到由于我们使用了常规的hint，因此执行计划的基数信息并没有得到纠正，仅仅是通过index_rs_asc这种暴力的hint把执行计划强制修正为索引扫描了。 “如何手工创建SQL Profile”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注云技术网站，小编将为大家输出更多高质量的实用文章！

相关推荐: oracle中如何使用exp/imp导入11g数据到9i

这篇文章将为大家详细讲解有关oracle中如何使用exp/imp导入11g数据到9i，小编觉得挺实用的，因此分享给大家做个参考，希望大家阅读完这篇文章后可以有所收获。 方法1:导出导入都使用11g客户端 –11g客户端导出 [oracle@xifenfei …