SQL ProfilesPart I.docx

1、SQL ProfilesPart ISQL Profiles-Part I在这里向大家介绍SQL Profiles，就是希望能够了解Oracle数据库的这一功能。SQL Profiles可以说是Outlines的进化。Outlines能够实现的功能SQL Profiles也完全能够实现，而SQL Profiles具有Outlines不具备的优化，个人认为最重要的有2点： SQL Profiles更容易生成、更改和控制。 SQL Profiles在对SQL语句的支持上做得更好，也就是适用范围更广。关于这2方面的优点，我后面会详细地阐述。现在我在使用Outlines的场合，均使用SQL Profi

2、les来替代。有一次准备对1条SQL语句使用Outline进行执行计划的稳定，结果使用Outline之后，系统出现大量的library cache latch的争用，不得不关闭Outline的使用，但是改用SQL Profiles不再有这个问题。这或许是个BUG，不过既然能用SQL Profiles代替，也就没再深入去研究这个问题。使用SQL Profiles无非是两个目的： 锁定或者说是稳定执行计划。 在不能修改应用中的SQL的情况下使SQL语句按指定的执行计划运行。那么SQL Profile到底是什么？在我看来，SQL Profile就是为某一SQL语句提供除了系统统计信息、对象（表和索引

3、等）统计信息之外的其他信息，比如运行环境、额外的更准确的统计信息，以帮助优化器为SQL语句选择更适合的执行计划。这些说法显得比较枯燥，还是来看看下面的测试。首先建2个测试表：view plaincopy to clipboardprint?1. SQLcreatetablet1asselectobject_id,object_namefromdba_objectswhererownumcreatetablet2asselect*fromdba_objects;6. 7. 表已创建。8. 9. SQLcreateindext2_idxont2(object_id);10. 11. 索引已创建。1

4、2. 13. SQLexecdbms_stats.gather_table_stats(user,t1,cascade=true,method_opt=forallcolumnssize1);14. 15. PL/SQL过程已成功完成。16. 17. SQLexecdbms_stats.gather_table_stats(user,t2,cascade=true,method_opt=forallcolumnssize1);18. 19. PL/SQL过程已成功完成。然后看看下面这一条SQL：1. SQLselectt1.*,t2.ownerfromt1,t2wheret1.object_n

5、amelike%T1%andt1.object_id=t2.object_id;2. 3. 已选择29行。4. 5. 执行计划6. -7. Planhashvalue:18382299748. 9. -10. |Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|11. -12. |0|SELECTSTATEMENT|2498|99920|219(4)|00:00:03|13. |*1|HASHJOIN|2498|99920|219(4)|00:00:03|14. |*2|TABLEACCESSFULL|T1|2498|72442|59(6)|00:00:

6、01|15. |3|TABLEACCESSFULL|T2|49954|536K|159(2)|00:00:02|16. -17. 18. PredicateInformation(identifiedbyoperationid):19. -20. 21. 1-access(T1.OBJECT_ID=T2.OBJECT_ID)22. 2-filter(T1.OBJECT_NAMELIKE%T1%)23. 24. 统计信息25. -26. 0recursivecalls27. 0dbblockgets28. 932consistentgets29. 0physicalreads30. 0redos

7、ize31. 1352bytessentviaSQL*Nettoclient32. 385bytesreceivedviaSQL*Netfromclient33. 2SQL*Netroundtripsto/fromclient34. 0sorts(memory)35. 0sorts(disk)36. 29rowsprocessed这里省略了SELECT出来的具体数据，但是我们关心的是返回的结果行数、执行计划以及逻辑读这些信息。首先从执行计划可以看到，这条SQL语句在2个表上都是全表扫描。在第1个表T1上，有 like %T1%这样的条件，导致只能全表扫描，这没有问题。但是第2个表，也是全表扫描

8、，这里有没有问题呢？或者说是有没有优化的余地，答案显然是肯定的。这里的问题在于执行计划ID=1的那一行，Oracle优化器评估T1 like %T1%返回的结果行数为2498行，即T1表总行数的5%，如果2个表采用index range scan+nested loop连接，oracle评估的成本会高于full table scan+hash join。下面可以看到Oracle优化器评估的index range_scan+nested loop的成本：1. SQLexplainplanforselect/*+use_nl(t1t2)index(t2)*/t1.*,t2.owner2. from

9、t1,t23. wheret1.object_namelike%T1%4. andt1.object_id=t2.object_id;5. 6. 已解释。7. 8. SQLshowplan9. 10. PLAN_TABLE_OUTPUT11. -12. Planhashvalue:378741338713. -14. |Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|15. -16. |0|SELECTSTATEMENT|2498|99920|5061(1)|00:01:01|17. |1|TABLEACCESSBYINDEXROWID|T2|1|11

10、|2(0)|00:00:01|18. |2|NESTEDLOOPS|2498|99920|5061(1)|00:01:01|19. |*3|TABLEACCESSFULL|T1|2498|72442|59(6)|00:00:01|20. |*4|INDEXRANGESCAN|T2_IDX|1|1(0)|00:00:01|21. -22. PredicateInformation(identifiedbyoperationid):23. -24. 3-filter(T1.OBJECT_NAMELIKE%T1%)25. 4-access(T1.OBJECT_ID=T2.OBJECT_ID)从执行计

11、划可以看到Oracle优化器评估的成本为5061，远远高于原来的219。但是实际的逻辑读是多少呢？1. 统计信息2. -3. 0recursivecalls4. 0dbblockgets5. 290consistentgets6. 0physicalreads7. 0redosize8. 1352bytessentviaSQL*Nettoclient9. 385bytesreceivedviaSQL*Netfromclient10. 2SQL*Netroundtripsto/fromclient11. 0sorts(memory)12. 0sorts(disk)13. 29rowsproces

12、sed加了HINT之后实际的逻辑读只有290，低于原始SQL的932。所以这里可以看出来，由于Oracle优化器过高地估计了T1表经过like操作过滤返回的行数，也就过高地估计了nest loop的成本，最终也就选择了不是最优的执行计划。下面我们用Oracle的SQL Tuning Advisor来尝试这条SQL：1. SQLvartuning_taskvarchar2(100);2. SQLDECLARE3. 2l_sql_idv$session.prev_sql_id%TYPE;4. 3l_tuning_taskVARCHAR2(30);5. 4BEGIN6. 5l_sql_id:=4zb

13、qykx89yc8v;7. 6l_tuning_task:=dbms_sqltune.create_tuning_task(sql_id=l_sql_id);8. 7:tuning_task:=l_tuning_task;9. 8dbms_sqltune.execute_tuning_task(l_tuning_task);10. 9dbms_output.put_line(l_tuning_task);11. 10END;12. 11/13. 任务_7414. 15. PL/SQL过程已成功完成。16. 17. SQLprinttuning_task;18. 19. TUNING_TASK2

14、0. -21. 任务_7422. 23. SQLSELECTdbms_sqltune.report_tuning_task(:tuning_task)FROMdual;24. 25. DBMS_SQLTUNE.REPORT_TUNING_TASK(:TUNING_TASK)26. -27. GENERALINFORMATIONSECTION28. -29. TuningTaskName:任务_7430. TuningTaskOwner:TEST131. Scope:COMPREHENSIVE32. TimeLimit(seconds):180033. CompletionStatus:COMP

15、LETED34. Startedat:12/15/201009:56:0235. Completedat:12/15/201009:56:0336. NumberofSQLProfileFindings:137. 38. -39. SchemaName:TEST140. SQLID:4zbqykx89yc8v41. SQLText:selectt1.*,t2.ownerfromt1,t2wheret1.object_namelike%T1%42. andt1.object_id=t2.object_id43. 44. -45. FINDINGSSECTION(1finding)46. -47.

16、 48. 1-SQLProfileFinding(seeexplainplanssectionbelow)49. -50. 为此语句找到了性能51. 52. Recommendation(estimatedbenefit:46.62%)53. -54. -考虑接受推荐的SQL55. executedbms_sqltune.accept_sql_profile(task_name=任务_74,replace=56. TRUE);57. 58. -59. EXPLAINPLANSSECTION60. -61. 62. 1-OriginalWithAdjustedCost63. -64. Planh

17、ashvalue:183822997465. 66. -67. |Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|68. -69. |0|SELECTSTATEMENT|29|1160|219(4)|00:00:03|70. |*1|HASHJOIN|29|1160|219(4)|00:00:03|71. |*2|TABLEACCESSFULL|T1|29|841|59(6)|00:00:01|72. |3|TABLEACCESSFULL|T2|49954|536K|159(2)|00:00:02|73. -74. 75. PredicateInfor

18、mation(identifiedbyoperationid):76. -77. 78. 1-access(T1.OBJECT_ID=T2.OBJECT_ID)79. 2-filter(T1.OBJECT_NAMELIKE%T1%)80. 81. 2-UsingSQLProfile82. -83. Planhashvalue:378741338784. 85. -86. |Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|87. -88. |0|SELECTSTATEMENT|29|1160|117(3)|00:00:02|89. |1|TABLEACCESSBYINDEXROWID|T2|1|11|2(0)|00:00:01|90. |2|NESTEDLOOPS|29|1160|117(3)|00:00:0291. |92. |*3|TABLEACCESSFULL|T1|29|841|59(6)|00