1611g新特性SQL Access Advisor.docx
- 文档编号:30465814
- 上传时间:2023-08-15
- 格式:DOCX
- 页数:17
- 大小:378.96KB
1611g新特性SQL Access Advisor.docx
《1611g新特性SQL Access Advisor.docx》由会员分享,可在线阅读,更多相关《1611g新特性SQL Access Advisor.docx(17页珍藏版)》请在冰豆网上搜索。
1611g新特性SQLAccessAdvisor
Oracle数据库11g:
面向DBA和开发人员的重要新特性
SQLAccessAdvisor
获得有关基于实际频率和使用类型(而非数据类型)进行分区、索引和创建物化视图以改进模式设计的建议。
下载Oracle数据库11g
Oracle数据库10g提供了大量帮助程序(或“顾问程序”),可帮助您决定最佳操作流程。
其中一个示例是SQLTuningAdvisor,它可以提供有关查询调整以及在流程中延长整个优化过程的建议。
但请考虑以下调整案例:
假设一个索引确实有助于某个查询,但该查询只执行一次。
这样,即使该查询可以得益于此索引,但创建索引的成本也会超出其带来的好处。
要按这种方式分析案例,您需要了解查询的访问频率和原因。
另一个顾问程序(SQLAccessAdvisor)可执行这种类型的分析。
除了像在Oracle数据库10g中一样可以分析索引、物化视图等,Oracle数据库11g中的SQLAccessAdvisor还可以分析表和查询以识别可能的分区策略—这在设计最佳模式时可以提供很大帮助。
在Oracle数据库11g中,SQLAccessAdvisor现在可以提供与整个负载相关的建议,包括考虑创建成本和维护访问结构。
在本文中,您将了解新的SQLAccessAdvisor如何解决常见问题。
(注:
出于演示目的,我们将通过一个语句演示这个功能;但是,Oracle建议使用SQLAccessAdvisor来帮助调整整个负载,而不只是一个SQL语句。
)
问题
下面是一个典型问题。
应用程序发出了以下SQL语句。
该查询似乎要消耗大量资源并且速度很慢。
selectstore_id,guest_id,count
(1)cntfromresr,transtwherer.res_idbetween2and40andt.res_id=r.res_idgroupbystore_id,guest_id/
您可以通过命令行或Oracle企业管理器数据库控制与顾问程序进行交互,但使用GUI可以提供更好的值(GUI可让您将解决方案可视化,并将许多任务简化为简单的点击操作)。
要使用企业管理器中的SQLAccessAdvisor解决SQL中的问题,请遵循以下步骤。
1.当然,第一个任务是启动企业管理器。
在Database主页上,向下滚动到页面底部,您将在这里看到几个超链接,如下图所示:
2.在该菜单中,单击AdvisorCentral,这将显示一个与下图类似的屏幕。
下面仅显示了该屏幕的顶部。
3.单击SQLAdvisors,这将显示一个与下图类似的屏幕。
4.在该屏幕中,您可以计划SQLAccessAdvisor会话,并指定其选项。
顾问程序必须收集一些要使用的SQL语句。
最简单的选项就是通过CurrentandRecentSQLActivity从共享池获取它们。
选择该选项,您可以获取共享池中缓存的所有SQL语句来进行分析。
但是,在某些情况下,您并不需要共享池中的所有语句;而仅需要其中的一组特定语句。
为此,您需要在另一个屏幕上创建一个“SQL调整工具集”,然后在这里(即,该屏幕中)引用集合名。
此外,您可能希望根据理论上预期会发生的情况来运行复合负载。
这些类型的SQL语句将不会位于共享池中,因为它们尚未处理。
相反,您需要创建这些语句并将其存储在一个特殊表中。
在第三个选项(CreateaHypotheticalWorkload...)中,您需要提供该表的名称以及模式名。
对于本文,假设您希望从共享池中获取SQL。
因此,选择第一个选项(即默认选项),如屏幕所示。
5.但是,您可能并不需要所有语句,而只需要一些关键语句。
例如,您可能只希望分析用户SCOTT(即应用程序用户)执行的SQL。
所有其他用户可能会执行即席SQL语句,但您希望在分析中排除它们。
在这种情况下,单击FilterOptions前面的“+”号,如下图所示。
6.在该屏幕中,在要求您输入用户的文本框中输入SCOTT,然后选择单选按钮IncludeonlySQL...(默认选项)。
同样,您也可以排除某些用户。
例如,您希望捕获数据库中的所有活动,除了用户SYS、SYSTEM和SYSMAN。
您可以在文本框中输入这些用户,然后单击按钮ExcludeallSQLstatements...。
7.您可以按ModuleId、Action甚至SQL语句中的特定字符串来过滤语句中访问的表。
其目的是确保只分析感兴趣的语句。
选择整个SQL缓存的小型子集可以加快分析速度。
在本例中,我们假设用户SCOTT仅执行了一个语句。
如果不是这样,您可以施加额外的过滤条件,将分析集合减少到只有一个SQL(即,原始问题语句中提到的那个SQL)。
8.单击Next。
这将显示以下屏幕(仅显示了顶部):
9.在该屏幕中,您可以指定应该搜索哪些类型的建议。
例如,在本例中,我们希望顾问程序查找潜在的索引、物化视图和分区,因此应选中这些项旁边的所有复选框。
对于AdvisorMode,您可以进行选择;默认选项LimitedMode仅处理高成本SQL语句。
当然,这可以加快速度并获得更好的结果集。
要分析所有SQL,应使用ComprehensiveMode。
(在本例中,模式的选择无关紧要,因为您只有一个SQL。
)
10.屏幕的后半部分显示了高级选项,例如,应该如何确定SQL语句的优先顺序、所使用的表空间等等。
您可以保留默认项为标记状态(稍后将描述更多内容)。
单击Next,这将显示计划屏幕。
选择RunImmediately,并单击Next。
11.单击Submit。
这将创建一个Scheduler作业。
您可以单击该屏幕中显示的作业超链接,它们位于页面顶部。
作业将显示为Running。
12.反复单击Refresh直到您看到LastRunStatus列下方的值更改为SUCCEEDED。
13.现在,返回Database主页并单击AdvisorCentral,正如您在第一步中所做的那样。
现在,您将看到SQLAccessAdvisor行,如下图所示:
14.该屏幕表明SQLAccessAdvisor任务已经COMPLETED。
现在,单击按钮ViewResult。
屏幕显示如下:
15.该屏幕说明了一切!
SQLAccessAdvisor分析了SQL语句,并发现某些解决方案可以将查询性能提高十倍。
要查看提供了哪些具体建议,单击Recommendations选项卡,这将显示详细信息屏幕,如下所示。
16.从较高级别看,该屏幕提供了许多很好的信息。
例如,对于ID=1的语句,Actions列下方有两个建议操作。
下一列ActionTypes显示了操作类型,由彩色方块表示。
根据下方的图标指南,您可以了解这两个操作分别针对索引和分区。
它们可以共同将性能提高几个数量级。
要确切了解可以提高哪个SQL语句,单击ID,这将显示以下屏幕。
当然,该分析只有一个语句,因此这里只显示一项内容。
如果您有多个语句,应该可以看到所有内容。
17.在上面的屏幕上,请注意RecommendationID列。
单击超链接将显示详细建议,如下所示:
18.该屏幕将提供非常清楚的解决方案描述。
它提出了两个建议:
创建分区表和使用索引。
随后,它发现索引已经存在,因此建议保留该索引。
如果您单击Action列下方的PARTITIONTABLE,将看到Oracle为使其成为分区表而生成的实际脚本。
但是,在单击之前,在文本框中填入表空间名称。
这将允许SQLAccessAdvisor在构建该脚本时使用该表空间:
Rem
RemRepartitioningtable"SCOTT"."TRANS"
Rem
SETSERVEROUTPUTON
SETECHOON
Rem
RemCreatingnewpartitionedtable
Rem
CREATETABLE"SCOTT"."TRANS1"
("TRANS_ID"NUMBER,
"RES_ID"NUMBER,
"TRANS_DATE"DATE,
"AMT"NUMBER,
"STORE_ID"NUMBER(3,0)
)PCTFREE10PCTUSED40INITRANS1MAXTRANS255NOCOMPRESSLOGGING
TABLESPACE"USERS"
PARTITIONBYRANGE("RES_ID")INTERVAL(3000)(PARTITIONVALUESLESSTHAN(3000)
);
begin
dbms_stats.gather_table_stats('"SCOTT"','"TRANS1"',NULL,dbms_stats.auto_sample_size);
end;
/
Rem
RemCopyingconstraintstonewpartitionedtable
Rem
ALTERTABLE"SCOTT"."TRANS1"MODIFY("TRANS_ID"NOTNULLENABLE);
Rem
RemCopyingreferentialconstraintstonewpartitionedtable
Rem
ALTERTABLE"SCOTT"."TRANS1"ADDCONSTRAINT"FK_TRANS_011"FOREIGNKEY("RES_ID")
REFERENCES"SCOTT"."RES"("RES_ID")ENABLE;
Rem
RemPopulatingnewpartitionedtablewithdatafromoriginaltable
Rem
INSERT/*+APPEND*/INTO"SCOTT"."TRANS1"
SELECT*FROM"SCOTT"."TRANS";
COMMIT;
Rem
RemRenamingtablestogivenewpartitionedtabletheoriginaltablename
Rem
ALTERTABLE"SCOTT"."TRANS"RENAMETO"TRANS11";
ALTERTABLE"SCOTT"."TRANS1"RENAMETO"TRANS";
脚本实际上将构建一个新表,然后将其重命名以匹配原始表。
19.最后一个选项卡Details将显示有关任务的某些有趣的详细信息。
尽管它们对于分析并不重要,但可以提供有关顾问程序如何得出这些结论的有价值线索,从而有助于您自己的思考过程。
该屏幕分为两部分,第一个部分是WorkloadandTaskOptions,如下所示。
20.屏幕的后半部分显示任务的运行日志。
有时,顾问程序无法处理所有SQL语句。
如果某些SQL语句被舍弃,就会在这里显示,并计入InvalidSQLString:
Statementsdiscarded计数。
如果您不明白为什么只分析了数个SQL语句,下面就是原因。
高级选项
在上面的第10步中,我使用了一个对高级设置的引用。
我们来看看这些设置的作用。
单击AdvancedOptions左侧的加号,这将显示一个屏幕,如下所示:
该屏幕允许您输入将在其中创建索引的表空间的名称、索引的创建模式等。
对于分区建议,您可以指定实现分区的表空间等。
看来,最重要的元素是Consideraccessstructurescreationcostsrecommendations复选框。
如果您选中该复选框,SQLAccessAdvisor将考虑索引本身的创建成本。
例如,是否应该创建10个新索引,相关成本可能会导致SQLAccessAdvisor建议不创建它们。
您还可以在该屏幕中指定索引的最大大小。
与SQLTuningAdvisor的差异
在简介中,我只简单描述了该工具与SQLTuningAdvisor的不同,下面我们来详细说明它们之间的差异。
一个简单演示可以最好地说明这些差异。
SQLAdvisors屏幕中,选择SQLTuningAdvisor并运行。
完成后,下面是显示结果的屏幕部分:
现在,如果您单击View查看建议,将显示一个如下所示的屏幕:
SQLTuningAdvisor提出的建议只对应以下四个目标之一:
▪为统计信息丢失或失效的对象收集统计信息
▪考虑优化器的任何数据偏差、复杂谓词或失效的统计信息
▪重新构建SQL以优化性能
▪提出新索引建议
用例
1.搜索高成本SQL语句,或者(更好的是)评估整个负载。
2.将可疑语句放入SQL调整工具集。
3.使用SQLTuningAdvisor和SQLAccessAdvisor对其进行分析。
4.得到分析结果;记录建议。
5.将建议插入SQLPerformanceAnalyzer(参见本文)。
6.在SQLPerformanceAnalyzer中检查更改前后的情况,并得出最佳解决方案。
7.重复上述操作,直到获得最佳模式设计。
8.获得最佳模式设计之后,您可能希望使用SQL计划管理基准锁定该计划(如本文所述)。
结论
调整数据库结构是最费时费力的棘手任务之一,同时也是最有成效的任务之一。
同样,分区是一个非常有效的调整工具,但分区的选择很难轻松决定。
SQLAccessAdvisor在这些过程中提供了一个非常有用的帮助。
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 1611g新特性SQL Access Advisor 1611 特性 SQL