作者为:? SHOUG成员 – ORACLE ACS高级顾问罗敏 多年前的一段往事 记得多年以前在一个10g平台的数据仓库项目上遇到一个非常难优化的SQL语句,当时即便我采集了统计信息、甚至在语句中增加了HINT,Oracle产生的执行计划都不如人意。最后,不得不通过SR寻求老
作者为:?
SHOUG成员 – ORACLE ACS高级顾问罗敏
- 多年前的一段往事
记得多年以前在一个10g平台的数据仓库项目上遇到一个非常难优化的SQL语句,当时即便我采集了统计信息、甚至在语句中增加了HINT,Oracle产生的执行计划都不如人意。最后,不得不通过SR寻求老外高手的指点,他建议我采用10g刚出炉的一个新技术,即让我为该语句生成SQL Profile信息,然后再执行该语句。一切OK了,太神了!
也记得当时我问老外,以后是不是遇到非常复杂的、优化难度很大的SQL语句,就扔给Oracle,特别是产生一遍SQL Profile来辅助优化器时?鬼子不无得意地回答:“That’s right!”
- 再次感叹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牛啊!
- 实施细节
下面就是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 ”
各位读者现在只需将你需要优化的语句和所属用户名填入上述脚本之中,也可以照葫芦画瓢开练了。
- SQL Profile到底是什么东西?
SQL Profile信息存储在Oracle数据字典之中,除了dba_sql_profiles视图显示的有限信息之外,的确有种看不见、摸不着的讳莫如深的感觉。SQL Profile到底是什么东西?其实SQL Profiling可以与表和统计信息的关系相类比,SQL Profile就是一条SQL语句的统计信息。例如:当我们遇到一个复杂且资源消耗非常大SQL语句时,Oracle可通过一些取样的数据,或者可以执行该语句一个片段,以及分析该语句的历史执行情况,来评估整体执行计划是否最优化。而这些辅助信息,就是SQL Profile信息,并保存在数据字典之中。
SQL Profiling工作原理如下图:
即上图上半部分显示11g自动优化工具SQL Tuning Advisor在针对某条SQL语句产生SQL Profile信息之后,在上图的下半部分,当Oracle正式需要执行该SQL语句时,优化器不仅利用该语句所访问对象的统计信息,而且利用SQL Profile信息,来产生整体上更优的执行计划。
- 什么时候该使用自动调优工具和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:
- Utilize Sql Tuning Advisor from Script
- Oracle Acs资深顾问罗敏 老罗技术核心感悟:分表还是分区?
- 如何验证SQL PROFILE的性能?
- 【Oracle Database 12c新特性】SYS_AUTO_SPM_EVOLVE_TASK 自动作业
- SQL Performance Analyzer SPA常用脚本汇总
- More About SYS_AUTO_SQL_TUNING_TASK
- Know about Oracle High Water Mark
- 11g新动态性能视图V$SQL_MONITOR,V$SQL_PLAN_MONITOR
原文地址:Oracle Acs资深顾问罗敏 老罗技术核心感悟:牛! 11g的自动调优和SQL Profile, 感谢原作者分享。

oracle asm指的是“自动存储管理”,是一种卷管理器,可自动管理磁盘组并提供有效的数据冗余功能;它是做为单独的Oracle实例实施和部署。asm的优势:1、配置简单、可最大化推动数据库合并的存储资源利用;2、支持BIGFILE文件等。

方法:1、利用“select*from user_indexes where table_name=表名”语句查询表中索引;2、利用“select*from all_indexes where table_name=表名”语句查询所有索引。

在oracle中,可以利用“TO_SINGLE_BYTE(String)”将全角转换为半角;“TO_SINGLE_BYTE”函数可以将参数中所有多字节字符都替换为等价的单字节字符,只有当数据库字符集同时包含多字节和单字节字符的时候有效。

在Oracle中,可利用lsnrctl命令查询端口号,该命令是Oracle的监听命令;在启动、关闭或重启oracle监听器之前可使用该命令检查oracle监听器的状态,语法为“lsnrctl status”,结果PORT后的内容就是端口号。

在oracle中,可以利用“drop sequence sequence名”来删除sequence;sequence是自动增加数字序列的意思,也就是序列号,序列号自动增加不能重置,因此需要利用drop sequence语句来删除序列。

在oracle中,可以利用“select ... From all_tab_columns where table_name=upper('表名') AND owner=upper('数据库登录用户名');”语句查询数据库表的数据类型。

方法:1、利用“LOWER(字段值)”将字段转为小写,或者利用“UPPER(字段值)”将字段转为大写;2、利用“REGEXP_LIKE(字符串,正则表达式,'i')”,当参数设置为“i”时,说明进行匹配不区分大小写。

方法:1、利用“alter system set sessions=修改后的数值 scope=spfile”语句修改session参数;2、修改参数之后利用“shutdown immediate – startup”语句重启服务器即可生效。


Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

AI Hentai Generator
Generate AI Hentai for free.

Hot Article

Hot Tools

Zend Studio 13.0.1
Powerful PHP integrated development environment

EditPlus Chinese cracked version
Small size, syntax highlighting, does not support code prompt function

Dreamweaver Mac version
Visual web development tools

Atom editor mac version download
The most popular open source editor

mPDF
mPDF is a PHP library that can generate PDF files from UTF-8 encoded HTML. The original author, Ian Back, wrote mPDF to output PDF files "on the fly" from his website and handle different languages. It is slower than original scripts like HTML2FPDF and produces larger files when using Unicode fonts, but supports CSS styles etc. and has a lot of enhancements. Supports almost all languages, including RTL (Arabic and Hebrew) and CJK (Chinese, Japanese and Korean). Supports nested block-level elements (such as P, DIV),
