- 浏览: 20766 次
- 性别:
- 来自: 杭州
最新评论
作者: fuyuncat
来源: www.HelloDBA.com
SQL优化器(SQL Tuning Advisor STA)是Oracle10g中推出的帮助DBA优化工具,它的特点是简单、智能,DBA值需要调用函数就可以给出一个性能很差的语句的优化结果。下面介绍一下它的使用。
使用STA一定要保证优化器是CBO模式下。
一、利用STA优化语句
对于问题语句的收集,可以通过Oracle10g的另一个自动化工具:数据库自动诊断监视工具(ADDM)。它的使用可以参照我的另一篇文章《Oracle10g数据库自动诊断监视工具(ADDM)使用指南》。
我们下面简单介绍一下如何优化一条找到的问题语句。正如前面所述说的,STA是用起来很简单(只要你会调用存储过程,都能使用这个工具),三个步骤就可以完成一次语句调优。
测试环境创建:
SQL> CREATE TABLE bigtab AS SELECT rownum as "id", a.* FROM dba_objects a;
Table created. SQL> create table smalltab as select rownum as "id", a.* FROM dba_tables a;
Table created.SQL> ALTER TABLE bigtab MODIFY (empno NUMBER);
Table altered. SQL> DECLARE 2 n NUMBER; 3 BEGIN 4 FOR n IN 1..100 5 LOOP 6 INSERT INTO bigtab SELECT rownum as "id", a.* FROM dba_objects a; 7 COMMIT; 8 END LOOP; 9 END;
/
PL/SQL procedure successfully completed.
这里创建一张大表和一张小表,并且都没有索引,下面执行一个查询:
SQL> set timing on
SQL> set autot on
SQL> select count(*) from bigtab a, smalltab b where a.object_name=b.table_name;
COUNT(*)
----------
135000
Elapsed: 00:00:05.59
Execution Plan
----------------------------------------------------------
Plan hash value: 3089226980
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 36 | 3550 (2)| 00:00:43 |
| 1 | SORT AGGREGATE | | 1 | 36 | | |
|* 2 | HASH JOIN | | 155K| 5462K| 3550 (2)| 00:00:43 |
| 3 | TABLE ACCESS FULL| SMALLTAB | 1223 | 22014 | 11 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| BIGTAB | 1205K| 20M| 3526 (1)| 00:00:43 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."OBJECT_NAME"="B"."TABLE_NAME")
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
16013 consistent gets
14491 physical reads
0 redo size
412 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
可以看出这个语句执行性能很差:16013 consistent gets。
第一步:创建优化任务并执行
通过调用函数CREATE_TUNING_TASK来创建优化任务,调用存储过程EXECUTE_TUNING_TASK执行该任务:
SQL> set autot off
SQL> set timing off
SQL> DECLARE
2 my_task_name VARCHAR2(30);
3 my_sqltext CLOB;
4 BEGIN
5 my_sqltext := 'select count(*) from bigtab a, smalltab b where a.object_name=b.table_name';
6 my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
7 sql_text => my_sqltext,
8 user_name => 'DEMO',
9 scope => 'COMPREHENSIVE',
10 time_limit => 60,
11 task_name => 'tuning_sql_test',
12 description => 'Task to tune a query on a specified table');
13
14 DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'tuning_sql_test');
15 END;
16 /
PL/SQL procedure successfully completed.
在函数CREATE_TUNING_TASK,sql_text是需要优化的语句,user_name是该语句通过哪个用户执行,scope是优化范围(limited或comprehensive),time_limit优化过程的时间限制,task_name优化任务名称,description优化任务描述。
可以通过视图USER_ADVISOR_LOG和USER_ADVISOR_LOG来查看创建过的优化任务。
SQL> select task_name, status from USER_ADVISOR_LOG where task_name='tuning_sql_
test';
TASK_NAME STATUS
------------------------------ -----------
tuning_sql_test COMPLETED
第二步:查看优化结果
通过函数可以查看优化结果。
SQL> set long 10000
SQL> set longchunksize 1000
SQL> set linesize 100
SQL> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'tuning_sql_test') from DUAL;
DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')
----------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : tuning_sql_test
Tuning Task Owner : DEMO
Scope : COMPREHENSIVE
Time Limit(seconds) : 60
Completion Status : COMPLETED
Started at : 11/30/2005 13:16:43
Completed at : 11/30/2005 13:16:44
Number of Index Findings : 1
Schema Name: DEMO
SQL ID : 6p64dnnsqf9pm
SQL Text : select count(*) from bigtab a, smalltab b where
a.object_name=b.table_name
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- Index Finding (see explain plans section below)
The execution plan of this statement can be improved by creating one or more
indices.
Recommendation (estimated benefit: 100%)
----------------------------------------
- Consider running the Access Advisor to improve the physical schema design
or creating the recommended index.
create index DEMO.IDX$$_06C50001 on DEMO.SMALLTAB('TABLE_NAME');
- Consider running the Access Advisor to improve the physical schema design
or creating the recommended index.
create index DEMO.IDX$$_06C50002 on DEMO.BIGTAB('OBJECT_NAME');
Rationale
---------
Creating the recommended indices significantly improves the execution plan
of this statement. However, it might be preferable to run "Access Advisor"
using a representative SQL workload as opposed to a single statement. This
will allow to get comprehensive index recommendations which takes into
account index maintenance overhead and additional space consumption.
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original
-----------
Plan hash value: 3089226980
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 36 | 3550 (2)| 00:00:43 |
| 1 | SORT AGGREGATE | | 1 | 36 | | |
|* 2 | HASH JOIN | | 155K| 5462K| 3550 (2)| 00:00:43 |
| 3 | TABLE ACCESS FULL| SMALLTAB | 1223 | 22014 | 11 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| BIGTAB | 1205K| 20M| 3526 (1)| 00:00:43 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."OBJECT_NAME"="B"."TABLE_NAME")
2- Using New Indices
--------------------
Plan hash value: 494801882
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 36 | 1108 (3)| 00:00:14 |
| 1 | SORT AGGREGATE | | 1 | 36 | | |
|* 2 | HASH JOIN | | 155K| 5462K| 1108 (3)| 00:00:14 |
| 3 | INDEX FAST FULL SCAN| IDX$$_06C50001 | 1223 | 22014 | 3 (0)| 00:00:01 |
| 4 | INDEX FAST FULL SCAN| IDX$$_06C50002 | 1205K| 20M| 1093 (2)| 00:00:14 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."OBJECT_NAME"="B"."TABLE_NAME")
-------------------------------------------------------------------------------
看一下这个优化建议报告:
第一部分是关于这次优化任务的基本信息:如任务名称、执行时间、范围、涉及到的语句等等。
第二部分是关于这次优化任务的所找到的问题以及给出的优化建议。前面先给出了问题描述:可以通过建立更多的所引来提高性能;然后是建议的具体内容:在表smalltab的字段table_name上创建索引,在表bigtab的字段object_name上创建索引;最后是相关注意事项:此次优化虽然给出了创建索引的建议,但是最好通过SQL访问建议器(SQL Access Advisor SAA)结合整个数据库的工作量来深入分析,那样就能给出考虑了索引维护和空间消耗等因素的更加合理的建议。
最后,报告还给出了原有的查询计划,以及采用优化建议以后的查询计划的对比。可以看出COST值大大下降。
发表评论
-
Java反射Reflection--运行时生成instance
2009-11-18 10:17 1311文章来自: 博客浙江(blog.zj.com) 原文参考:ht ... -
Java反射经典实例 Java Reflection
2009-11-18 10:12 737http://jeplove.blog.zj.com/blog ... -
JProfiler试用手记
2009-04-13 16:00 800JProfiler是一款Java的性能监控工具。可以查看当前应 ... -
优化SQL的另一种思维1 性能调整综述
2009-04-12 17:34 1257作者: Peak Wong, 出处:IT专家网, 责任编辑: ... -
JAVA基础--JAVA中的反射机制详解
2009-02-25 22:41 737JAVA反射机制 JAVA反射机制是在运行状态中,对于任 ... -
Java中int和Integer的区别详解
2009-02-23 21:18 1101Java中int和Integer关系是比较微妙的。关系如下: ... -
Portal(门户)是什么--IT术语
2009-02-23 21:12 3056Portal 的组成可以分为 ... -
StringBuilder类
2009-02-20 21:40 12591.3 StringBuilder类 一个String对象 ... -
Java的内存泄漏
2009-02-20 18:13 761一 问题的提出 Java ... -
JDK核心API:Java1.5语言新特性简单总结
2009-02-20 18:05 9661. 自动装箱与拆箱 对应C# 例1.1 ... -
优秀Java程序员必须了解GC的工作原理
2009-02-20 17:57 852一个优秀的Java程序员必 ... -
转:大型互联网网站架构心得之一
2009-02-06 22:10 1092我们知道,对于一个大 ... -
一 CVS服务器的安装:
2008-12-28 18:43 1320一 CVS服务器的安装: 1。查看你的操作系统上是否安装了C ... -
Jive论坛与Spring框架的经典结合
2008-12-28 15:42 931没有一种新技术是凭空诞生的,它的萌芽或胚胎总是或多或少显现于以 ... -
开源技术分析:AOP和Spring事务处理
2008-12-27 22:01 758一.为什么要用框架 ...
相关推荐
文档为oracle调优的STA无脑命令,直接安装命令贴到sqlplus里面就好,sql_id号从awr报告里面获得,想调优的语句找到sql_id,替换到文档里面的sql_id位置,最后无脑贴进去就可以使用STA调优助手了。
Oracle 10g之后的优化器支持两种模式,一个是normal模式,一个是tuning模式。在大多数情况下,优化器处于normal模式。基于CBO的normal模式只考虑很小部分的执行计划集合用于选择哪个执行计划,因为它需要在尽可能短...
Step7语句表监控时RLO,STA,STANDARD的含义
oracle数据库sql turning advise(sta)的使用简单案例
Oracle 1z0-051
这是一种改进的状态转换算法 ...近年来,状态转换算法(STA)已成为一种新颖的全局优化元启发式方法。在以前的版本中,连续 STA 中变换算子的参数保持不变或周期性地减小。在这个改进版本中,考虑了STA的最优参数选择。
STA1052ROM3.1版本CD\MP3\WMA全功能
STA1052 CD Servo Controller
openwrt总结(设置sta)
STA客户跨线程调用STA对象,使用了marshal,消息循环。
STA1050 CD Servo Controller
STA508 is a monolithic quad half bridge stage in Multipower BCD technology. The device can be used as dual bridge or reconfigured, by connecting CONFIG pin to Vdd pin, as single bridge with double ...
sta-1040 sta sta
实现了一个STA组件, 然后里面有STA客户调用和MTA客户调用。
Android8.0平台实现双wifi模块STA+AP模式
很有用的音频放大集成块 STA328 很难找到的
openwrt配置WiFi的AP+STA中继模式的关键步骤,内包含相关配置文件,以及uci2dat源码
1.数据库访问性能优化; 2.字符串操作性能优化; 3.优化 Web 服务器计算机和特定应用程序的配置文件以符合您的特定需要; 4.一定要禁用调试模式; 5.对于广泛依赖外部资源的应用程序,请考虑在多处理器计算机上启用网络...
RT2870STA.dat
RT3070 STA linux driver