- 浏览: 271373 次
- 性别:
- 来自: 广州
最新评论
-
Yiwu_zh:
步骤清晰,跟着来做,真搭建好了
不过用的是10.5版本,输出有 ...
搭建简单的DB2 HADR -
luogen33:
db2备份单个表 -
gthao:
1 楼正解。。如果schema里面有内容的话,就删除不了,会报 ...
db2建立schema -
znttql:
整理的太好了 非常感谢啊
DB2 日期 时间 -
alvin198761:
能根据schame备份数据库不??
db2备份单个表
很好的参考:
http://www.iteye.com/topic/498902
1.首先在ADDM中看到建议调优的sql,跑一下SQL Tuning Advisor
看到一些建议,在EM这里更多的是建议你建index(毕竟这是最简单的方法)
2.观察需要调整表的结构,看是否适合做index
还是已经做了而因为有函数等原因使得失效
3.请程序员检查代码
正如参考里面说的,更改代码的效果往往要比建index要好得多
以下是具体的优化结果,
其实就是在原有基础上缩减了日期范围(acct_date已经做了index),使得子集大大减少(重180天到1天)
原来:
优化后:
写在最后:
觉得SQL tuning的东西DBA的组织力、执行力比纯技术要重要得多!!
http://www.iteye.com/topic/498902
1.首先在ADDM中看到建议调优的sql,跑一下SQL Tuning Advisor
看到一些建议,在EM这里更多的是建议你建index(毕竟这是最简单的方法)
2.观察需要调整表的结构,看是否适合做index
还是已经做了而因为有函数等原因使得失效
ALTER INDEX index_name MONITORING USAGE; SELECT * FROM V$OBJECT_USAGE;
3.请程序员检查代码
正如参考里面说的,更改代码的效果往往要比建index要好得多
以下是具体的优化结果,
其实就是在原有基础上缩减了日期范围(acct_date已经做了index),使得子集大大减少(重180天到1天)
原来:
SQL> SET SERVEROUTPUT ON; SQL> SET LINESIZE 1000; SQL> SET AUTOTRACE TRACEONLY; SQL> SELECT q.work_ctr, SUBSTR (q.prod_nbr, 2, 2) AS size1, 2 q.prod_nbr AS prod_nbr, COALESCE (a.pass_qty, 0) pass_qty, 3 q.qty AS inspect_qty 4 FROM (SELECT main_wc AS work_ctr, prod_nbr, SUM (pass_qty) pass_qty 5 FROM HMASPAS_SOUTPUT 6 WHERE fac_id = 'A' 7 AND trans_date >= '2009-11-24 07.30.00' 8 AND trans_date <= '2009-11-24 19.30.00' 9 GROUP BY main_wc, prod_nbr) a 10 RIGHT JOIN 11 (SELECT work_ctr, prod_nbr, COUNT (*) AS qty 12 FROM HMASHIS_WQDH 13 WHERE fac_id = 'A' 14 AND trans_date >= '2009-11-24 07.30.00' 15 AND trans_date <= '2009-11-24 19.30.00' 16 GROUP BY work_ctr, prod_nbr) q 17 ON q.work_ctr = a.work_ctr AND q.prod_nbr = a.prod_nbr 18 ORDER BY q.work_ctr, q.prod_nbr, a.pass_qty, q.qty; 37 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 544019557 --------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 56 | 85722 (2)| 00:17:09 | | 1 | SORT ORDER BY | | 1 | 56 | 85722 (2)| 00:17:09 | |* 2 | HASH JOIN OUTER | | 1 | 56 | 85721 (2)| 00:17:09 | | 3 | VIEW | | 1 | 28 | 5 (20)| 00:00:01 | | 4 | HASH GROUP BY | | 1 | 44 | 5 (20)| 00:00:01 | |* 5 | TABLE ACCESS BY INDEX ROWID| HMASHIS_WQDH | 1 | 44 | 4 (0)| 00:00:01 | |* 6 | INDEX RANGE SCAN | INDEX2_WQDH | 1 | | 3 (0)| 00:00:01 | | 7 | VIEW | | 29 | 812 | 85715 (2)| 00:17:09 | | 8 | HASH GROUP BY | | 29 | 1363 | 85715 (2)| 00:17:09 | |* 9 | TABLE ACCESS FULL | HMASPAS_SOUTPUT | 29 | 1363 | 85714 (2)| 00:17:09 | --------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("Q"."PROD_NBR"="A"."PROD_NBR"(+) AND "Q"."WORK_CTR"="A"."WORK_CTR"(+)) 5 - filter("FAC_ID"='A') 6 - access("TRANS_DATE">='2009-11-24 07.30.00' AND "TRANS_DATE"<='2009-11-24 19.30.00') 9 - filter("TRANS_DATE">='2009-11-24 07.30.00' AND "FAC_ID"='A' AND "TRANS_DATE"<='2009-11-24 19.30.00') Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 393981 consistent gets 324974 physical reads 0 redo size 2043 bytes sent via SQL*Net to client 509 bytes received via SQL*Net from client 4 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 37 rows processed
优化后:
SQL> SET SERVEROUTPUT ON; SQL> SET LINESIZE 1000; SQL> SET AUTOTRACE TRACEONLY; SELECT q.work_ctr, SUBSTR (q.prod_nbr, 2, 2) AS size1, SQL> 2 q.prod_nbr AS prod_nbr, COALESCE (A.pass_qty, 0) pass_qty, 3 q.qty AS inspect_qty 4 FROM (SELECT main_wc AS work_ctr, prod_nbr, SUM (pass_qty) pass_qty 5 FROM HMASPAS_SOUTPUT 6 WHERE fac_id = 'A' 7 AND acct_date >= 8 TO_CHAR (TO_DATE ('2009-11-24', 'YYYY-MM-DD') - 1, 9 'YYYY-MM-DD' 10 ) 11 AND acct_date <= 12 TO_CHAR (TO_DATE ('2009-11-24', 'YYYY-MM-DD') + 1, 13 'YYYY-MM-DD' 14 ) 15 AND trans_date >= '2009-11-24 07.30.00' 16 AND trans_date <= '2009-11-24 19.30.00' 17 GROUP BY main_wc, prod_nbr) A 18 RIGHT JOIN 19 (SELECT work_ctr, prod_nbr, COUNT (*) AS qty 20 FROM HMASHIS_WQDH 21 WHERE fac_id = 'A' 22 AND acct_date >= 23 TO_CHAR (TO_DATE ('2009-11-24', 'YYYY-MM-DD') - 1, 24 'YYYY-MM-DD' 25 ) 26 AND acct_date <= 27 TO_CHAR (TO_DATE ('2009-11-24', 'YYYY-MM-DD') + 1, 28 'YYYY-MM-DD' 29 ) 30 AND trans_date >= '2009-11-24 07.30.00' 31 AND trans_date <= '2009-11-24 19.30.00' 32 GROUP BY work_ctr, prod_nbr) q 33 ON q.work_ctr = A.work_ctr AND q.prod_nbr = A.prod_nbr 34 ORDER BY q.work_ctr, q.prod_nbr, A.pass_qty, q.qty; 37 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 2416477161 ------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 56 | 1010 (1)| 00:00:13 | | 1 | SORT ORDER BY | | 1 | 56 | 1010 (1)| 00:00:13 | |* 2 | HASH JOIN OUTER | | 1 | 56 | 1009 (1)| 00:00:13 | | 3 | VIEW | | 1 | 28 | 5 (20)| 00:00:01 | | 4 | HASH GROUP BY | | 1 | 55 | 5 (20)| 00:00:01 | |* 5 | TABLE ACCESS BY INDEX ROWID| HMASHIS_WQDH | 1 | 55 | 4 (0)| 00:00:01 | |* 6 | INDEX RANGE SCAN | INDEX2_WQDH | 1 | | 3 (0)| 00:00:01 | | 7 | VIEW | | 1 | 28 | 1003 (1)| 00:00:13 | | 8 | HASH GROUP BY | | 1 | 58 | 1003 (1)| 00:00:13 | |* 9 | TABLE ACCESS BY INDEX ROWID| HMASPAS_SOUTPUT | 1 | 58 | 1002 (1)| 00:00:13 | |* 10 | INDEX RANGE SCAN | IDX_HMASPAS_SOUTPUT | 31455 | | 102 (1)| 00:00:02 | ------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("Q"."PROD_NBR"="A"."PROD_NBR"(+) AND "Q"."WORK_CTR"="A"."WORK_CTR"(+)) 5 - filter("ACCT_DATE">='2009-11-23' AND "FAC_ID"='A' AND "ACCT_DATE"<='2009-11-25') 6 - access("TRANS_DATE">='2009-11-24 07.30.00' AND "TRANS_DATE"<='2009-11-24 19.30.00') 9 - filter("TRANS_DATE">='2009-11-24 07.30.00' AND "FAC_ID"='A' AND "TRANS_DATE"<='2009-11-24 19.30.00') 10 - access("ACCT_DATE">='2009-11-23' AND "ACCT_DATE"<='2009-11-25') Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 1726 consistent gets 0 physical reads 0 redo size 2043 bytes sent via SQL*Net to client 509 bytes received via SQL*Net from client 4 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 37 rows processed
写在最后:
觉得SQL tuning的东西DBA的组织力、执行力比纯技术要重要得多!!
发表评论
-
Oracle Cursor Sharing
2011-05-06 01:57 0上周被问到一个问题:oracle参数cursor_sharin ... -
sqlplus登录自动运行脚本
2011-04-20 00:02 2007如果每次使用sqlplus都要设置如serveroutput或 ... -
被问到的问题,导出DDL
2011-04-15 20:31 887导出DDL定义的几种方法 有时候经常需要导出建表语句,在这 ... -
about RESETLOGS
2010-09-15 22:12 888终于要面对RESETLOGS了, ... -
BLOCK、EXTENT、SEGMENT、TABLESPACE、Data Dictionary
2010-08-14 16:44 1336BlockBlock 可以分为 Header Ta ... -
STATISTICS_LEVEL
2010-07-19 16:15 1058看图说话: Oracle Advisory B ... -
SGA_TARGET
2010-07-19 11:03 1393当SGA_TARGET设置为非零值,oracle 10g会自动 ... -
method of segment space management
2010-07-13 16:40 782Locally Managed Tablespaces有两种管 ... -
042 nologging 题目
2010-07-13 15:30 1203原题如下: if the tablespace is in t ... -
oracle sql解析顺序
2010-07-12 16:47 1437今天被问到了一个where rownum与order by的顺 ... -
利用oracle学习DB2
2010-07-01 11:14 876现在开始要接触多一个D ... -
关于RAC failover的实践
2010-05-21 09:19 1103关于之前RAC的failover同事们都有一个猜想,failo ... -
卸载clusterware
2010-05-20 09:32 1343最简单的就是运行以下两个脚本: ./rootdelete. ... -
dataguard成功实现物理switchover
2010-04-26 16:28 1314一直都不能实现switchover,在看了不少文档后终于实现, ... -
使用exp/imp转移大的数据库定义(脚本)
2010-04-23 15:05 934都知道exp rows=n可以不导出具体的数据,但是做全库导出 ... -
oracle 连接解释方法
2010-04-13 14:05 978Easy Connect: 格式: <usern ... -
RMAN-06428
2010-03-25 15:11 1234今天建立新的nbu oracle client 遇到RMAN- ... -
oracle import改表名
2010-03-24 16:23 4880小技巧 现在工作有大量exp/imp工作,其中遇到过需要该表 ... -
dataguard 笔记
2010-03-23 09:50 1106推荐按照三思的文档去做,当然首先要了解dataguard的原理 ... -
fga的教训
2010-03-17 10:30 1241这是一个教训,记录下来 一个简单的需求,监测一个表记录不明被 ...
相关推荐
Oracle SQL Tuning with Oracle SQLTXPLAIN: Oracle Database 12c Edition by Stelios Charalambides English | 8 May 2017 | ISBN: 1484224353 | 408 Pages | PDF | 24.39 MB Learn through this practical guide ...
ORACLE SQL TUNING
来源oracle官方ppt,讲解sql tuning技巧
oracle的sqltuning 课程,但资源有些老,04年的
使用ORACLE SQL Tuning advisor快速优化低效的SQL语句, 这里是一个使用范例,有9个步骤, 后面 其他语句,都按照这个步骤来做就可以了。
Oracle SQL Tuning with Oracle SQLTXPLAIN,Oracle SQL Tuning with Oracle SQLTXPLAIN,Oracle SQL Tuning with Oracle SQLTXPLAIN
Oracle SQL Tuning with Oracle SQLTXPLAIN.pdf
英文原版 ORACLE SQL PERFORMANCE TUNING AND OPTIMIZATION
Oracle SQL Tuning with SQLTXPLAIN is a practical guide to SQL tuning the way Oracle's own experts do it, using a freely downloadable tool called SQLTXPLAIN. Using this simple tool you'll learn how to ...
官方的Oracle 12C SQL 优化的Student Guide 和Activity Guide.
sql tuning oracle sql tuning
Tosska SQL Tuning Expert (TSE™) 是一款优秀的SQL 调优工具,无需用户参与即可优化 SQL 语句。该产品将为您提供最终的 SQL 性能解决方案,只需点击鼠标即可。在整个 SQL 调优过程中,您不必进行分析、猜测或手工...
oracle sql性能调优,结合实例的查询计划的介绍和分析
Oracle SQL Tuning Pocket Reference Copyright Chapter 1. Oracle SQL TuningPocket Reference Section 1.1. Introduction Section 1.2. The SQL Optimizers Section 1.3. Rule-Based Optimizer Problems and ...
高清彩版 Oracle SQL Performance Tuning and Optimization: It's all about the Cardinalities
SQL Tuning使用指南 QCO的SQL Tuning模块是一种功能强大的SQL语句分析、测试、优化工具,它可以贯穿Oracle数据库应用的整个生命周期,在开发阶段、试运行阶段和生产阶段帮助获得最佳SQL语句,以提高应用系统的...
SQL Tuning sqlServer,数据库教程, chm格式 英文版
3本经典oracle sql性能优化的的书,值得一看: Oracle.High.Performance.Tuning.for.9i.and.10g.chm Oracle性能优化技术内幕.pdf SQL Performance Tuning -0201791692.chm