问题 下面是一个典型问题。应用程序发出了以下 SQL 语句。该查询乎要消耗大量资源并且速度很慢。 select store_id, guest_id, count(1) cnt from res r, trans t where r.res_id between 2 and 40 and t.res_id = r.res_id group by store_id, guest_id / 您
问题
下面是一个典型问题。应用程序发出了以下 SQL 语句。该查询似乎要消耗大量资源并且速度很慢。
<span>select store_id, guest_id, count(1) cnt from res r, trans t where r.res_id between 2 and 40 and t.res_id = r.res_id group by store_id, guest_id / 您可以通过命令行或 Oracle 企业管理器数据库控制与顾问程序进行交互,但<strong>使用</strong> GUI 可以提供更好的值(GUI 可让您将解决方案可视化,并将许多任务简化为简单的点击操作)。 </span><p><span>要<strong>使用</strong>企业管理器中的 SQL Access Advisor 解决 SQL 中的问题,请遵循以下步骤。</span></p><ol> <li> <span>当然,第一个任务是启动企业管理器。在 Database 主页上,向下滚动到页面底部,您将在这里看到几个超链接,如下图所示: </span><p><span><img src="/static/imghwm/default1.png" data-src="/inc/test.jsp?url=http%3A%2F%2Fwww.oracle.com%2Focom%2Fgroups%2Fpublic%2F%40otn%2Fdocuments%2Fdigitalasset%2F114311.jpg&refer=http%3A%2F%2Fblog.csdn.net%2Fhaungyuncheng%2Farticle%2Fdetails%2F23039705" class="lazy" alt="SQL Access Advisor的使用" ></span></p> </li> <li> <span>在该菜单中,单击 <strong>Advisor Central</strong>,这将显示一个与下图类似的屏幕。下面仅显示了该屏幕的顶部。 </span><p><span><img src="/static/imghwm/default1.png" data-src="/inc/test.jsp?url=http%3A%2F%2Fwww.oracle.com%2Focom%2Fgroups%2Fpublic%2F%40otn%2Fdocuments%2Fdigitalasset%2F117605.jpg&refer=http%3A%2F%2Fblog.csdn.net%2Fhaungyuncheng%2Farticle%2Fdetails%2F23039705" class="lazy" alt="SQL Access Advisor的使用" ></span></p> </li> <li> <span>单击 <strong>SQL Advisors</strong>,这将显示一个与下图类似的屏幕。 </span><p><span><img src="/static/imghwm/default1.png" data-src="/inc/test.jsp?url=http%3A%2F%2Fwww.oracle.com%2Focom%2Fgroups%2Fpublic%2F%40otn%2Fdocuments%2Fdigitalasset%2F123806.jpg&refer=http%3A%2F%2Fblog.csdn.net%2Fhaungyuncheng%2Farticle%2Fdetails%2F23039705" class="lazy" alt="SQL Access Advisor的使用" ></span></p> </li> <li> <span>在该屏幕中,您可以计划 SQL Access Advisor 会话,并指定其选项。顾问程序必须收集一些要<strong>使用</strong>的 SQL 语句。最简单的选项就是通过 Current and Recent SQL Activity 从共享池获取它们。选择该选项,您可以获取共享池中缓存的所有 SQL 语句来进行分析。 </span><p><span>但是,在某些情况下,您并不需要共享池中的所有语句;而仅需要其中的一组特定语句。为此,您需要在另一个屏幕上创建一个“SQL 调整工具集”,然后在这里(即,该屏幕中)引用集合名。 </span></p> <p><span>此外,您可能希望根据理论上预期会发生的情况来运行复合负载。这些类型的 SQL 语句将不会位于共享池中,因为它们尚未处理。相反,您需要创建这些语句并将其存储在一个特殊表中。在第三个选项 (<strong>Create a Hypothetical Workload...</strong>) 中,您需要提供该表的名称以及模式名。 </span></p> <p><span>对于本文,假设您希望从共享池中获取 SQL。因此,选择第一个选项(即默认选项),如屏幕所示。 </span></p> </li> <li> <span>但是,您可能并不需要所有语句,而只需要一些关键语句。例如,您可能只希望分析用户 SCOTT(即应用程序用户)执行的 SQL。所有其他用户可能会执行即席 SQL 语句,但您希望在分析中排除它们。在这种情况下,单击 <strong>Filter Options</strong> 前面的“+”号,如下图所示。 </span><p><span><img src="/static/imghwm/default1.png" data-src="/inc/test.jsp?url=http%3A%2F%2Fwww.oracle.com%2Focom%2Fgroups%2Fpublic%2F%40otn%2Fdocuments%2Fdigitalasset%2F104252.jpg&refer=http%3A%2F%2Fblog.csdn.net%2Fhaungyuncheng%2Farticle%2Fdetails%2F23039705" class="lazy" alt="SQL Access Advisor的使用" ></span></p> </li> <li><span>在该屏幕中,在要求您输入用户的文本框中输入 SCOTT,然后选择单选按钮 <strong>Include only SQL...</strong>(默认选项)。同样,您也可以排除某些用户。例如,您希望捕获数据库中的所有活动,除了用户 SYS、SYSTEM 和 SYSMAN。您可以在文本框中输入这些用户,然后单击按钮 <strong>Exclude all SQL statements...</strong>。 </span></li> <li><span>您可以按 Module Id、Action 甚至 SQL 语句中的特定字符串来过滤语句中访问的表。其目的是确保只分析感兴趣的语句。选择整个 SQL 缓存的小型子集可以加快分析速度。在本例中,我们假设用户 SCOTT 仅执行了一个语句。如果不是这样,您可以施加额外的过滤条件,将分析集合减少到只有一个 SQL(即,原始问题语句中提到的那个 SQL)。 </span></li> <li> <span>单击 <strong>Next</strong>。这将显示以下屏幕(仅显示了顶部): </span><p><span><img src="/static/imghwm/default1.png" data-src="/inc/test.jsp?url=http%3A%2F%2Fwww.oracle.com%2Focom%2Fgroups%2Fpublic%2F%40otn%2Fdocuments%2Fdigitalasset%2F104207.jpg&refer=http%3A%2F%2Fblog.csdn.net%2Fhaungyuncheng%2Farticle%2Fdetails%2F23039705" class="lazy" alt="SQL Access Advisor的使用" ></span></p> </li> <li><span>在该屏幕中,您可以指定应该搜索哪些类型的建议。例如,在本例中,我们希望顾问程序查找潜在的索引、物化视图和分区,因此应选中这些项旁边的所有复选框。对于 Advisor Mode,您可以进行选择;默认选项 Limited Mode 仅处理高成本 SQL 语句。当然,这可以加快速度并获得更好的结果集。要分析所有 SQL,应<strong>使用</strong> Comprehensive Mode。(在本例中,模式的选择无关紧要,因为您只有一个 SQL。) </span></li> <li><span>屏幕的后半部分显示了高级选项,例如,应该如何确定 SQL 语句的优先顺序、所<strong>使用</strong>的表空间等等。您可以保留默认项为标记状态(稍后将描述更多内容)。单击 <strong>Next</strong>,这将显示计划屏幕。选择 <strong>Run Immediately</strong>,并单击 <strong>Next</strong>。 </span></li> <li><span>单击 <strong>Submit</strong>。这将创建一个 Scheduler 作业。您可以单击该屏幕中显示的作业超链接,它们位于页面顶部。作业将显示为 <strong>Running</strong>。 </span></li> <li><span>反复单击 <strong>Refresh</strong> 直到您看到 <strong>Last Run Status</strong> 列下方的值更改为 <strong>SUCCEEDED</strong>。 </span></li> <li> <span>现在,返回 Database 主页并单击 <strong>Advisor Central</strong>,正如您在第一步中所做的那样。现在,您将看到 <strong>SQL Access Advisor</strong> 行,如下图所示: </span><p><span><img src="/static/imghwm/default1.png" data-src="/inc/test.jsp?url=http%3A%2F%2Fwww.oracle.com%2Focom%2Fgroups%2Fpublic%2F%40otn%2Fdocuments%2Fdigitalasset%2F123123.jpg&refer=http%3A%2F%2Fblog.csdn.net%2Fhaungyuncheng%2Farticle%2Fdetails%2F23039705" class="lazy" alt="SQL Access Advisor的使用" ></span></p> </li> <li> <span>该屏幕表明 SQL Access Advisor 任务已经 <strong>COMPLETED</strong>。现在,单击按钮 <strong>View Result</strong>。屏幕显示如下: </span><p><span><img src="/static/imghwm/default1.png" data-src="/inc/test.jsp?url=http%3A%2F%2Fwww.oracle.com%2Focom%2Fgroups%2Fpublic%2F%40otn%2Fdocuments%2Fdigitalasset%2F119058.jpg&refer=http%3A%2F%2Fblog.csdn.net%2Fhaungyuncheng%2Farticle%2Fdetails%2F23039705" class="lazy" alt="SQL Access Advisor的使用" ></span></p> </li> <li> <span>该屏幕说明了一切!SQL Access Advisor 分析了 SQL 语句,并发现某些解决方案可以将查询性能提高十倍。要查看提供了哪些具体建议,单击 <strong>Recommendations</strong> 选项卡,这将显示详细信息屏幕,如下所示。 </span><p><span><img src="/static/imghwm/default1.png" data-src="/inc/test.jsp?url=http%3A%2F%2Fwww.oracle.com%2Focom%2Fgroups%2Fpublic%2F%40otn%2Fdocuments%2Fdigitalasset%2F116660.jpg&refer=http%3A%2F%2Fblog.csdn.net%2Fhaungyuncheng%2Farticle%2Fdetails%2F23039705" class="lazy" alt="SQL Access Advisor的使用" ></span></p> </li> <li> <span>从较高级别看,该屏幕提供了许多很好的信息。例如,对于 ID = 1 的语句,Actions 列下方有两个建议操作。下一列 Action Types 显示了操作类型,由彩色方块表示。根据下方的图标指南,您可以了解这两个操作分别针对索引和分区。它们可以共同将性能提高几个数量级。 </span><p><span>要确切了解可以提高哪个 SQL 语句,单击 ID,这将显示以下屏幕。当然,该分析只有一个语句,因此这里只显示一项内容。如果您有多个语句,应该可以看到所有内容。 </span></p> <p><span><img src="/static/imghwm/default1.png" data-src="/inc/test.jsp?url=http%3A%2F%2Fwww.oracle.com%2Focom%2Fgroups%2Fpublic%2F%40otn%2Fdocuments%2Fdigitalasset%2F104210.jpg&refer=http%3A%2F%2Fblog.csdn.net%2Fhaungyuncheng%2Farticle%2Fdetails%2F23039705" class="lazy" alt="SQL Access Advisor的使用" ></span></p> </li> <li> <span>在上面的屏幕上,请注意 Recommendation ID 列。单击超链接将显示详细建议,如下所示: </span><p><span><img src="/static/imghwm/default1.png" data-src="/inc/test.jsp?url=http%3A%2F%2Fwww.oracle.com%2Focom%2Fgroups%2Fpublic%2F%40otn%2Fdocuments%2Fdigitalasset%2F116329.jpg&refer=http%3A%2F%2Fblog.csdn.net%2Fhaungyuncheng%2Farticle%2Fdetails%2F23039705" class="lazy" alt="SQL Access Advisor的使用" ></span></p> </li> <li> <span>该屏幕将提供非常清楚的解决方案描述。它提出了两个建议:创建分区表和<strong>使用</strong>索引。随后,它发现索引已经存在,因此建议保留该索引。 </span><p><span>如果您单击 Action 列下方的 <strong>PARTITION TABLE</strong>,将看到 Oracle 为使其成为分区表而生成的实际脚本。但是,在单击之前,在文本框中填入表空间名称。这将允许 SQL Access Advisor 在构建该脚本时<strong>使用</strong>该表空间:</span></p> <pre class="brush:php;toolbar:false"><span>Rem Rem Repartitioning table "SCOTT"."TRANS" Rem SET SERVEROUTPUT ON SET ECHO ON Rem Rem Creating new partitioned table Rem CREATE TABLE "SCOTT"."TRANS1" ( "TRANS_ID" NUMBER, "RES_ID" NUMBER, "TRANS_DATE" DATE, "AMT" NUMBER, "STORE_ID" NUMBER(3,0) ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING TABLESPACE "USERS" PARTITION BY RANGE ("RES_ID") INTERVAL( 3000) ( PARTITION VALUES LESS THAN (3000) ); begin dbms_stats.gather_table_stats('"SCOTT"', '"TRANS1"', NULL, dbms_stats.auto_sample_size); end; / Rem Rem Copying constraints to new partitioned table Rem ALTER TABLE "SCOTT"."TRANS1" MODIFY ("TRANS_ID" NOT NULL ENABLE); Rem Rem Copying referential constraints to new partitioned table Rem ALTER TABLE "SCOTT"."TRANS1" ADD CONSTRAINT "FK_TRANS_011" FOREIGN KEY ("RES_ID") REFERENCES "SCOTT"."RES" ("RES_ID") ENABLE; Rem Rem Populating new partitioned table with data from original table Rem INSERT /*+ APPEND */ INTO "SCOTT"."TRANS1" SELECT * FROM "SCOTT"."TRANS"; COMMIT; Rem Rem Renaming tables to give new partitioned table the original table name Rem ALTER TABLE "SCOTT"."TRANS" RENAME TO "TRANS11"; ALTER TABLE "SCOTT"."TRANS1" RENAME TO "TRANS"; </span>
脚本实际上将构建一个新表,然后将其重命名以匹配原始表。
高级选项
在上面的第 10 步中,我使用了一个对高级设置的引用。我们来看看这些设置的作用。
单击 Advanced Options 左侧的加号,这将显示一个屏幕,如下所示:
该屏幕允许您输入将在其中创建索引的表空间的名称、索引的创建模式等。对于分区建议,您可以指定实现分区的表空间等。
看来,最重要的元素是 Consider access structures creation costs recommendations 复选框。如果您选中该复选框,SQL Access Advisor 将考虑索引本身的创建成本。例如,是否应该创建 10 个新索引,相关成本可能会导致 SQL Access Advisor 建议不创建它们。
您还可以在该屏幕中指定索引的最大大小。
与 SQL Tuning Advisor 的差异
在简介中,我只简单描述了该工具与 SQL Tuning Advisor 的不同,下面我们来详细说明它们之间的差异。一个简单演示可以最好地说明这些差异。
SQL Advisors 屏幕中,选择 SQL Tuning Advisor 并运行。完成后,下面是显示结果的屏幕部分:现在,如果您单击 View 查看建议,将显示一个如下所示的屏幕:
SQL Tuning Advisor 提出的建议只对应以下四个目标之一:
- 为统计信息丢失或失效的对象收集统计信息
- 考虑优化器的任何数据偏差、复杂谓词或失效的统计信息
- 重新构建 SQL 以优化性能
- 提出新索引建议
用例
- 搜索高成本 SQL 语句,或者(更好的是)评估整个负载。
- 将可疑语句放入 SQL 调整工具集。
- 使用 SQL Tuning Advisor 和 SQL Access Advisor 对其进行分析。
- 得到分析结果;记录建议。
- 将建议插入 SQL Performance Analyzer(参见本文)。
- 在 SQL Performance Analyzer 中检查更改前后的情况,并得出最佳解决方案。
- 重复上述操作,直到获得最佳模式设计。
- 获得最佳模式设计之后,您可能希望使用 SQL 计划管理基准锁定该计划(如本文所述)。
结论
调整数据库结构是最费时费力的棘手任务之一,同时也是最有成效的任务之一。同样,分区是一个非常有效的调整工具,但分区的选择很难轻松决定。SQL Access Advisor 在这些过程中提供了一个非常有用的帮助。

MySQL數據庫升級的步驟包括:1.備份數據庫,2.停止當前MySQL服務,3.安裝新版本MySQL,4.啟動新版本MySQL服務,5.恢復數據庫。升級過程需注意兼容性問題,並可使用高級工具如PerconaToolkit進行測試和優化。

MySQL備份策略包括邏輯備份、物理備份、增量備份、基於復制的備份和雲備份。 1.邏輯備份使用mysqldump導出數據庫結構和數據,適合小型數據庫和版本遷移。 2.物理備份通過複製數據文件,速度快且全面,但需數據庫一致性。 3.增量備份利用二進制日誌記錄變化,適用於大型數據庫。 4.基於復制的備份通過從服務器備份,減少對生產系統的影響。 5.雲備份如AmazonRDS提供自動化解決方案,但成本和控制需考慮。選擇策略時應考慮數據庫大小、停機容忍度、恢復時間和恢復點目標。

MySQLclusteringenhancesdatabaserobustnessandscalabilitybydistributingdataacrossmultiplenodes.ItusestheNDBenginefordatareplicationandfaulttolerance,ensuringhighavailability.Setupinvolvesconfiguringmanagement,data,andSQLnodes,withcarefulmonitoringandpe

在MySQL中優化數據庫模式設計可通過以下步驟提升性能:1.索引優化:在常用查詢列上創建索引,平衡查詢和插入更新的開銷。 2.表結構優化:通過規範化或反規範化減少數據冗餘,提高訪問效率。 3.數據類型選擇:使用合適的數據類型,如INT替代VARCHAR,減少存儲空間。 4.分區和分錶:對於大數據量,使用分區和分錶分散數據,提升查詢和維護效率。

tooptimizemysqlperformance,lofterTheSeSteps:1)inasemproperIndexingTospeedUpqueries,2)使用ExplaintplaintoAnalyzeandoptimizequeryPerformance,3)ActiveServerConfigurationStersLikeTlikeTlikeTlikeIkeLikeIkeIkeLikeIkeLikeIkeLikeIkeLikeNodb_buffer_pool_sizizeandmax_connections,4)

MySQL函數可用於數據處理和計算。 1.基本用法包括字符串處理、日期計算和數學運算。 2.高級用法涉及結合多個函數實現複雜操作。 3.性能優化需避免在WHERE子句中使用函數,並使用GROUPBY和臨時表。

MySQL批量插入数据的高效方法包括:1.使用INSERTINTO...VALUES语法,2.利用LOADDATAINFILE命令,3.使用事务处理,4.调整批量大小,5.禁用索引,6.使用INSERTIGNORE或INSERT...ONDUPLICATEKEYUPDATE,这些方法能显著提升数据库操作效率。

在MySQL中,添加字段使用ALTERTABLEtable_nameADDCOLUMNnew_columnVARCHAR(255)AFTERexisting_column,刪除字段使用ALTERTABLEtable_nameDROPCOLUMNcolumn_to_drop。添加字段時,需指定位置以優化查詢性能和數據結構;刪除字段前需確認操作不可逆;使用在線DDL、備份數據、測試環境和低負載時間段修改表結構是性能優化和最佳實踐。


熱AI工具

Undresser.AI Undress
人工智慧驅動的應用程序,用於創建逼真的裸體照片

AI Clothes Remover
用於從照片中去除衣服的線上人工智慧工具。

Undress AI Tool
免費脫衣圖片

Clothoff.io
AI脫衣器

Video Face Swap
使用我們完全免費的人工智慧換臉工具,輕鬆在任何影片中換臉!

熱門文章

熱工具

SublimeText3 Mac版
神級程式碼編輯軟體(SublimeText3)

DVWA
Damn Vulnerable Web App (DVWA) 是一個PHP/MySQL的Web應用程序,非常容易受到攻擊。它的主要目標是成為安全專業人員在合法環境中測試自己的技能和工具的輔助工具,幫助Web開發人員更好地理解保護網路應用程式的過程,並幫助教師/學生在課堂環境中教授/學習Web應用程式安全性。 DVWA的目標是透過簡單直接的介面練習一些最常見的Web漏洞,難度各不相同。請注意,該軟體中

禪工作室 13.0.1
強大的PHP整合開發環境

MantisBT
Mantis是一個易於部署的基於Web的缺陷追蹤工具,用於幫助產品缺陷追蹤。它需要PHP、MySQL和一個Web伺服器。請查看我們的演示和託管服務。

ZendStudio 13.5.1 Mac
強大的PHP整合開發環境