search
HomeDatabaseMysql TutorialOracle Acs资深顾问罗敏 老罗技术核心感悟:牛! 11g的自动调优和

作者为:? SHOUG成员 – ORACLE ACS高级顾问罗敏 多年前的一段往事 记得多年以前在一个10g平台的数据仓库项目上遇到一个非常难优化的SQL语句,当时即便我采集了统计信息、甚至在语句中增加了HINT,Oracle产生的执行计划都不如人意。最后,不得不通过SR寻求老

作者为:?

SHOUG成员 – ORACLE ACS高级顾问罗敏

  1. 多年前的一段往事

记得多年以前在一个10g平台的数据仓库项目上遇到一个非常难优化的SQL语句,当时即便我采集了统计信息、甚至在语句中增加了HINT,Oracle产生的执行计划都不如人意。最后,不得不通过SR寻求老外高手的指点,他建议我采用10g刚出炉的一个新技术,即让我为该语句生成SQL Profile信息,然后再执行该语句。一切OK了,太神了!

也记得当时我问老外,以后是不是遇到非常复杂的、优化难度很大的SQL语句,就扔给Oracle,特别是产生一遍SQL Profile来辅助优化器时?鬼子不无得意地回答:“That’s right!”

  1. 再次感叹SQL Profile的牛!

若干年之后的2014年,在面对一条将近200行的SQL语句进行优化时,发现该语句执行计划已经基本找不出明显问题,例如既没有全表扫描,也没有全索引扫描,甚至语句的Cost也非常低(当然Cost并不十分准确)。但是语句执行效率并不高,达到30秒,资源消耗也非常高,例如Buffer Gets达到1,246,155次。客户当然不满意,如何进一步优化?

山穷水尽之际,想起了上述多年前的往事,更想起了神奇的SQL Profile技术。于是,在搜索到最新的11g文档《Automatic SQL Tuning and SQL Profiles (Doc ID 271196.1)》之后,照猫画虎般地开练了。效果如何?以下就是优化前后的对比:

这是优化之前的各项指标:

Stat Name Statement Total Per Execution % Snap Total
Elapsed Time (ms) 30,273 30,272.96 17.76
CPU Time (ms) 29,968 29,968.19 17.79
Executions 1
Buffer Gets 1,246,155 1,246,155.00 14.68
Disk Reads 5,437 5,437.00 0.80

这是优化之后的各项指标:

Stat Name Statement Total Per Execution % Snap Total
Elapsed Time (ms) 4,653 4,652.71 3.00
CPU Time (ms) 4,470 4,470.23 2.90
Executions 1
Buffer Gets 303,480 303,480.00 2.32
Disk Reads 9,740 9,740.00 1.39

可见,语句响应速度从30秒下降到4秒多,Buffer Gets从1,246,155下降到303,480!我对语句没做任何改动,也没创建新的索引,执行计划就更好了,实际效果更是如此的好!SQL Profile牛啊!

  1. 实施细节

下面就是11g自动优化工具和SQL Profile技术综合运用的详细过程:

  • 生成自动优化任务

declare

my_task_name VARCHAR2(30);

my_sqltext CLOB;

begin

my_sqltext := ‘';

my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(

sql_text => my_sqltext,

user_name => ‘’,

scope => ‘COMPREHENSIVE’,

time_limit => 60,

task_name => ‘test1′,

description => ‘Task to tune a query on a specified table’);

end;

/

  • 执行自动优化任务

begin

DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => ‘test1′);

end;

/

  • 查询Oracle产生的自动优化报告

set long 10000

set longchunksize 1000

set linesize 100

set heading off

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( ‘test1′) from DUAL;

set heading on

  • 接受Oracle自动优化任务产生的SQL Profile

DECLARE

my_sqlprofile_name VARCHAR2(30);

begin

my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (

task_name => ‘test1′,

name => ‘test1′);

end;

/

OK了,可以运行需要调优的语句,并观察优化效果了。效果就是上面显示的那组令人激动不已的数据,而且在Oracle产生的新执行计划中,明白无误地显示采用SQL Profile了:

“SQL profile “test1″ used for this statement ”

各位读者现在只需将你需要优化的语句和所属用户名填入上述脚本之中,也可以照葫芦画瓢开练了。

  1. SQL Profile到底是什么东西?

SQL Profile信息存储在Oracle数据字典之中,除了dba_sql_profiles视图显示的有限信息之外,的确有种看不见、摸不着的讳莫如深的感觉。SQL Profile到底是什么东西?其实SQL Profiling可以与表和统计信息的关系相类比,SQL Profile就是一条SQL语句的统计信息。例如:当我们遇到一个复杂且资源消耗非常大SQL语句时,Oracle可通过一些取样的数据,或者可以执行该语句一个片段,以及分析该语句的历史执行情况,来评估整体执行计划是否最优化。而这些辅助信息,就是SQL Profile信息,并保存在数据字典之中。

SQL Profiling工作原理如下图:

sql profile

即上图上半部分显示11g自动优化工具SQL Tuning Advisor在针对某条SQL语句产生SQL Profile信息之后,在上图的下半部分,当Oracle正式需要执行该SQL语句时,优化器不仅利用该语句所访问对象的统计信息,而且利用SQL Profile信息,来产生整体上更优的执行计划。

  1. 什么时候该使用自动调优工具和SQL Profile?

Oracle 11g的自动调优工具和SQL Profile的确像潘多拉盒子一样充满魔力。继续上述优化案例,尽管该语句被Oracle优化了,但我仔细对照了优化前后50多步的执行计划,怎么也没找出到底是哪些步骤被Oracle优化得效果如此之好,真是太神奇了!

是否一遇到复杂语句就依靠自动调优工具和SQL Profile进行优化呢?且慢,首先,尽管应用性能问题很多,但最主要的问题还是一些传统的、基础性问题。例如:缺乏合适的索引;复合索引设计不合理,特别是索引顺序不对,导致索引效率不高;SQL语句中错误地使用函数,导致索引无法使用;等等。针对这些问题,合理运用20%的基础技术,特别是索引技术,其实能解决80%的问题。这些技术也是DBA和应用开发人员的基本功和基本设计开发规范,过度依赖自动化工具反而会让我们自己的基本技能退化的。其次,自动调优工具和SQL Profile也非包治百病的灵丹妙药,也有看走眼的时候。Oracle自动工具怎么可能比你更了解你的数据模型和数据分布情况,进而给出更准确的优化策略呢?第三,Oracle自动工具使用起来也并不简单,而且需要DBA与开发人员紧密配合,针对大部分基础性问题,有经验的DBA和开发人员其实一眼就能看出问题,何必杀鸡用牛刀呢?

那何时使用自动调优工具和SQL Profile进行优化呢?本人的经验:当针对一些复杂SQL语句,运用传统的、人工分析方法难以奏效时,建议尝试使用这些新技术。

无论如何,Oracle 11g的自动调优工具和SQL Profile还是牛!不得不服!

Related posts:

  1. Utilize Sql Tuning Advisor from Script
  2. Oracle Acs资深顾问罗敏 老罗技术核心感悟:分表还是分区?
  3. 如何验证SQL PROFILE的性能?
  4. 【Oracle Database 12c新特性】SYS_AUTO_SPM_EVOLVE_TASK 自动作业
  5. SQL Performance Analyzer SPA常用脚本汇总
  6. More About SYS_AUTO_SQL_TUNING_TASK
  7. Know about Oracle High Water Mark
  8. 11g新动态性能视图V$SQL_MONITOR,V$SQL_PLAN_MONITOR
Statement
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
What are the differences in syntax between MySQL and other SQL dialects?What are the differences in syntax between MySQL and other SQL dialects?Apr 27, 2025 am 12:26 AM

MySQLdiffersfromotherSQLdialectsinsyntaxforLIMIT,auto-increment,stringcomparison,subqueries,andperformanceanalysis.1)MySQLusesLIMIT,whileSQLServerusesTOPandOracleusesROWNUM.2)MySQL'sAUTO_INCREMENTcontrastswithPostgreSQL'sSERIALandOracle'ssequenceandt

What is MySQL partitioning?What is MySQL partitioning?Apr 27, 2025 am 12:23 AM

MySQL partitioning improves performance and simplifies maintenance. 1) Divide large tables into small pieces by specific criteria (such as date ranges), 2) physically divide data into independent files, 3) MySQL can focus on related partitions when querying, 4) Query optimizer can skip unrelated partitions, 5) Choosing the right partition strategy and maintaining it regularly is key.

How do you grant and revoke privileges in MySQL?How do you grant and revoke privileges in MySQL?Apr 27, 2025 am 12:21 AM

How to grant and revoke permissions in MySQL? 1. Use the GRANT statement to grant permissions, such as GRANTALLPRIVILEGESONdatabase_name.TO'username'@'host'; 2. Use the REVOKE statement to revoke permissions, such as REVOKEALLPRIVILEGESONdatabase_name.FROM'username'@'host' to ensure timely communication of permission changes.

Explain the differences between InnoDB and MyISAM storage engines.Explain the differences between InnoDB and MyISAM storage engines.Apr 27, 2025 am 12:20 AM

InnoDB is suitable for applications that require transaction support and high concurrency, while MyISAM is suitable for applications that require more reads and less writes. 1.InnoDB supports transaction and bank-level locks, suitable for e-commerce and banking systems. 2.MyISAM provides fast read and indexing, suitable for blogging and content management systems.

What are the different types of JOINs in MySQL?What are the different types of JOINs in MySQL?Apr 27, 2025 am 12:13 AM

There are four main JOIN types in MySQL: INNERJOIN, LEFTJOIN, RIGHTJOIN and FULLOUTERJOIN. 1.INNERJOIN returns all rows in the two tables that meet the JOIN conditions. 2.LEFTJOIN returns all rows in the left table, even if there are no matching rows in the right table. 3. RIGHTJOIN is contrary to LEFTJOIN and returns all rows in the right table. 4.FULLOUTERJOIN returns all rows in the two tables that meet or do not meet JOIN conditions.

What are the different storage engines available in MySQL?What are the different storage engines available in MySQL?Apr 26, 2025 am 12:27 AM

MySQLoffersvariousstorageengines,eachsuitedfordifferentusecases:1)InnoDBisidealforapplicationsneedingACIDcomplianceandhighconcurrency,supportingtransactionsandforeignkeys.2)MyISAMisbestforread-heavyworkloads,lackingtransactionsupport.3)Memoryengineis

What are some common security vulnerabilities in MySQL?What are some common security vulnerabilities in MySQL?Apr 26, 2025 am 12:27 AM

Common security vulnerabilities in MySQL include SQL injection, weak passwords, improper permission configuration, and unupdated software. 1. SQL injection can be prevented by using preprocessing statements. 2. Weak passwords can be avoided by forcibly using strong password strategies. 3. Improper permission configuration can be resolved through regular review and adjustment of user permissions. 4. Unupdated software can be patched by regularly checking and updating the MySQL version.

How can you identify slow queries in MySQL?How can you identify slow queries in MySQL?Apr 26, 2025 am 12:15 AM

Identifying slow queries in MySQL can be achieved by enabling slow query logs and setting thresholds. 1. Enable slow query logs and set thresholds. 2. View and analyze slow query log files, and use tools such as mysqldumpslow or pt-query-digest for in-depth analysis. 3. Optimizing slow queries can be achieved through index optimization, query rewriting and avoiding the use of SELECT*.

See all articles

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

Video Face Swap

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Tools

PhpStorm Mac version

PhpStorm Mac version

The latest (2018.2.1) professional PHP integrated development tool

MinGW - Minimalist GNU for Windows

MinGW - Minimalist GNU for Windows

This project is in the process of being migrated to osdn.net/projects/mingw, you can continue to follow us there. MinGW: A native Windows port of the GNU Compiler Collection (GCC), freely distributable import libraries and header files for building native Windows applications; includes extensions to the MSVC runtime to support C99 functionality. All MinGW software can run on 64-bit Windows platforms.

EditPlus Chinese cracked version

EditPlus Chinese cracked version

Small size, syntax highlighting, does not support code prompt function

DVWA

DVWA

Damn Vulnerable Web App (DVWA) is a PHP/MySQL web application that is very vulnerable. Its main goals are to be an aid for security professionals to test their skills and tools in a legal environment, to help web developers better understand the process of securing web applications, and to help teachers/students teach/learn in a classroom environment Web application security. The goal of DVWA is to practice some of the most common web vulnerabilities through a simple and straightforward interface, with varying degrees of difficulty. Please note that this software

ZendStudio 13.5.1 Mac

ZendStudio 13.5.1 Mac

Powerful PHP integrated development environment