Home > Database > Mysql Tutorial > body text

Oracle SQL tuning 数据库优化步骤图文教程

WBOY
Release: 2016-06-07 16:20:00
Original
1354 people have browsed it

SQL Turning 是Quest公司出品的Quest Central软件中的一个工具。Quest Central是一款集成化、图形化、跨平台的数据库管理解决方案,可以同时管理 Oracle、DB2 和 SQL server 数据库。 一、SQL Tuning for SQL Server简介 SQL语句的优化对发挥数据库的最佳性

   SQL Turning 是Quest公司出品的Quest Central软件中的一个工具。Quest Central是一款集成化、图形化、跨平台的数据库管理解决方案,可以同时管理 Oracle、DB2 和 SQL server 数据库。

  一、SQL Tuning for SQL Server简介

  SQL语句的优化对发挥数据库的最佳性能非常关键。然而不幸的是,应用优化通常由于时间和资源的因素而被忽略。SQL Tuning (SQL优化)模块可以对比和评测特定应用中SQL语句的运行性能,提出智能化的优化建议,帮助用户改善应用的响应时间。SQL优化模块具有非介入式SQL采集、自动优化和专家建议等功能,全面改善SQL优化工作。

  二、SQL Tuning for SQL Server的使用

  1、打开Quest Database Management Solutions弹出窗口如图1所示

Oracle SQL tuning 数据库优化步骤图文教程 三联

  图1

  2、在红色标记处打开SQL Tuning 优化SQL

  (1)建立连接。

  在Quest Central主界面上的“Database”树上选择“SQL Server”,然后在下方出现的“Tools”框中选择“SQL Tuning”选项,打开“Lanch SQL Tuning for SQL Server Connections”对话框(图2、图3)。我们在这里建立数据库服务器的连接,,以后的分析工作都会在它上面完成。

Oracle SQL tuning 数据库优化步骤图文教程

  图2 “建立连接”对话框

Oracle SQL tuning 数据库优化步骤图文教程

  图3

  双击“New Connection”图标,在弹出窗口中输入数据库的信息,单击“OK”,然后单击“Connect”即可。

  (2)分析原始SQL语句 ,在单击“Connect”后将弹出一个新窗口,如图4

Oracle SQL tuning 数据库优化步骤图文教程

  图4

  在打开窗口的“Oriangal SQL”文本框内输入需要分析的原始SQL语句,红色标记处选择对应的数据库名,SQL语句代码如下:

  图5 分析原始SQL语句

  原始SQL语句

  然后点击工具栏上的“Execute”按钮,执行原始的SQL语句,SQL Tuning会自动分析SQL的执行计划,并把分析结果显示到界面上(图5)。

Oracle SQL tuning 数据库优化步骤图文教程

  (3)优化SQL。

  现在我们点击工具栏上的“Optimize Statement”按钮,让SQL Tuning开始优化SQL,完成后,可以看到SQL Tuning产生了19条与原始SQL等价的优化方案(图6)。

Oracle SQL tuning 数据库优化步骤图文教程

  图6 SQL优化方案

  (4)获得最优SQL。

  接下来,我们来执行上面产生的优化方案,以选出性能最佳的等效SQL语句。在列表中选择需要执行的优化方案(默认已全部选中),然后点击工具栏上的“Execute”按钮旁边的下拉菜单,选择“Execute Selected”。等到所有SQL运行完成后,点击界面左方的“Tuning Resolution”按钮,

  可以看到最优的SQL已经出来啦,运行时间竟然可以提高21%!(图7)

Oracle SQL tuning 数据库优化步骤图文教程

  图7 “Tuning Resolution”界面

  最优的SQL语句如下:

  5)学习书写专家级的SQL语句 。

  优化后的SQL语句

  SELECT dbo.Person_BasicInfo.*,

  dbo.Graduater_GraduaterRegist.RegistNO AS RegistNO,

  dbo.Graduater_GraduaterRegist.RegistTime AS BaoDaoTime,

  dbo.Graduater_GraduaterRegist.RegistMan AS RegistMan,

  dbo.Graduater_Business.ComeFrom AS ComeFrom,

  dbo.Graduater_Business.Code AS Code,

  dbo.Graduater_Business.Status AS Status,

  dbo.Graduater_Business.ApproveResult AS ApproveResult,

  dbo.Graduater_Business.NewCorp AS NewCorp,

  dbo.Graduater_Business.CommendNumber AS CommendNumber,

  dbo.Graduater_Business.EmployStatus AS EmployStatus,

  dbo.Graduater_Business.NewCommendTime AS NewCommendTime,

  dbo.Graduater_Business.GetSource AS GetSource,

  dbo.Graduater_Business.EmployTime AS EmployTime,

  dbo.Graduater_Business.Job AS Job,

  dbo.Graduater_Business.FillMan AS FillMan,

  dbo.Graduater_Business.FillTime AS FillTime,

  dbo.Graduater_Business.IsCommendOK AS IsCommendOK,

  dbo.Graduater_Business.ApproveUser AS ApproveUser,

  dbo.Graduater_Business.ApproveTime AS ApproveTime,

  dbo.Graduater_Business.RegistTime AS RegistTime,

  dbo.Graduater_Business.EmployCorp AS EmployCorp,

  dbo.Graduater_Business.JobRemark AS JobRemark,

  CASE WHEN dbo.Graduater_Business.ComeFrom = 'WS' THEN '网上登记'

  WHEN dbo.Graduater_Business.ComeFrom = 'HP' THEN '华普大厦'

  WHEN dbo.Graduater_Business.ComeFrom = 'JD' THEN '精典大厦'

  WHEN dbo.Graduater_Business.ComeFrom = 'MC' THEN '赛马场'

  WHEN ComeFrom = 'ZX' THEN '高指中心' END AS ComeFromName,

  dbo.Person_Contact.Address AS Address,

  dbo.Person_Contact.Zip AS Zip,

  dbo.Person_Contact.Telephone AS Telephone,

  dbo.Person_Contact.Mobile AS Mobile,

  dbo.Person_Contact.Email AS Email,

  dbo.Person_Contact.IM AS IM,

  dbo.Person_Skill.ForeignLanguage AS ForeignLanguage,

  dbo.Person_Skill.ForeignLanguageLevel AS ForeignLanguageLevel,

  dbo.Person_Skill.CantoneseLevel AS CantoneseLevel,

  dbo.Person_Skill.MandarinLevel AS MandarinLevel,

  dbo.Person_Skill.Language AS Language,

  dbo.Person_Skill.TechnicalTitle AS TechnicalTitle,

  dbo.Person_Skill.ComputerLevel AS ComputerLevel,

  dbo.Person_EmployPurpose.JobType AS JobType,

  dbo.Person_EmployPurpose.Vocation AS Vocation,

  dbo.Person_EmployPurpose.JobPlace AS JobPlace,

  dbo.Person_EmployPurpose.Salary AS Salary,

  dbo.Person_EmployPurpose.OnJobDate AS OnJobDate,

  dbo.Person_EmployPurpose.CorpType AS CorpType,

  dbo.Person_EmployPurpose.Job AS RequireJob,

  YEAR(GETDATE()) - YEAR(dbo.Person_BasicInfo.Birthday) AS Age,

  dbo.Graduater_Business.EmployType AS EmployType,

  dbo.Graduater_Business.EmployTypeCode AS EmployTypeCode,

  dbo.Graduater_Business.EmployCorpType AS EmployCorpType,

  CASE WHEN dbo.Graduater_Business.PrintStatus = '已打印' THEN '已打印'

  ELSE '未打印' END AS PrintStatus,

  dbo.Graduater_Business.PrintTime AS PrintTime,

  CASE WHEN dbo.Graduater_Business.EmployStatus = '是' THEN '已就业'

  ELSE '未就业' END AS EmployStatusView

  FROM dbo.Person_BasicInfo

  INNER JOIN dbo.Graduater_Business

  ON dbo.Person_BasicInfo.PersonID = dbo.Graduater_Business.PersonID

  LEFT OUTER JOIN dbo.Graduater_GraduaterRegist

  ON dbo.Graduater_Business.GradBusinessID = dbo.Graduater_GraduaterRegist.GraduaterGUID

  INNER JOIN dbo.Person_Contact

  ON dbo.Person_BasicInfo.PersonID = dbo.Person_Contact.PersonID

  INNER JOIN dbo.Person_Skill

  ON dbo.Person_BasicInfo.PersonID = dbo.Person_Skill.PersonID

  INNER JOIN dbo.Person_EmployPurpose

  ON dbo.Person_BasicInfo.PersonID = dbo.Person_EmployPurpose.PersonID

  OPTION (FORCE ORDER)

  (

source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template