We were informed the following sql running slower than user expected.
The METRIC_DATA table is more than 10G and we have an index including all col except POLL_DATA col.
How to improve the performance is the big trouble.
Alan suggested me to wipe out the ORDER By since we have an index but the tom said we should not rely on the index if we want the record in order.
At the same time ,Alan suggested me query data from only one partition.but when I do this I found the our data is populated from all over the world and the table in database use time zone is +00.So it is hard to tell if the data with time zone is suitable to the partition condition.I think we should modify the partition condition to suit the most data???
Any one have good idea about this?
SELECT FORMULA_ID, PROPERTY_ID, TO_CHAR(DATETIME at time zone '-04:00', 'MM/DD/YYYY HH24:MI') DATETIME, POLL_DATA FROM METRIC_DATA WHERE INVENTORY_ID = 64596 and formula_id in(9,10,11,12) and property_id in(5026,5028,5031) AND (DATETIME BETWEEN CAST(TO_TIMESTAMP_TZ('05/30/2008 00:00 -04:00', 'MM/DD/YYYY HH24:MI tzh:tzm') AS TIMESTAMP WITH LOCAL TIME ZONE) AND CAST(TO_TIMESTAMP_TZ('05/31/2008 00:00 -04:00', 'MM/DD/YYYY HH24:MI tzh:tzm') AS TIMESTAMP WITH LOCAL TIME ZONE)) ORDER BY FORMULA_ID, PROPERTY_ID, DATETIME ASC
分享到:
相关推荐
SQL Tuning - File IO Performance
SQL Tuning sqlServer,数据库教程, chm格式 英文版
oraclesql优化手册 ,开发必备
SQL Tuning使用指南 QCO的SQL Tuning模块是一种功能强大的SQL语句分析、测试、优化工具,它可以贯穿Oracle数据库应用的整个生命周期,在开发阶段、试运行阶段和生产阶段帮助获得最佳SQL语句,以提高应用系统的...
Db10g SQL Tuning Vol-I
Standard Edition HotSpot Virtual Machine Garbage Collection Tuning Guide describes the garbage collection methods included in the Java HotSpot Virtual Machine (Java HotSpot VM) and helps you determine...
Top-SQL Tuning SQL语句调整
qorvo-antenna-aperture-tuning-eguide_cn.pdf
Learn through this practical guide to SQL tuning how Oracle's own experts do it, using a freely downloadable tool called SQLTXPLAIN. This new edition has been expanded to include AWR, Oracle 12c ...
Tosska SQL Tuning Expert (TSE™) 是一款优秀的SQL 调优工具,无需用户参与即可优化 SQL 语句。该产品将为您提供最终的 SQL 性能解决方案,只需点击鼠标即可。在整个 SQL 调优过程中,您不必进行分析、猜测或手工...
Performance Tuning Guidelines for Mellanox Network Adapters Revision 1.6
Performance Tuning --oracle 9i 性能调整与优化 Performance Tuning --oracle 9i 性能调整与优化
SQL Tuning for Oracle.chm
tuning-pack-11g-datasheet-CN
现在我就向大家介绍这样一款工具:SQLTuning for SQL Server。 1. SQL Tuning 简介 SQL Turning是Quest公司出品的Quest Central软件中的一个工具。 QuestCentral(图1)是一款集成化、图形化、跨平台的数据库管理...
SQL语句是对数据库进行操作的惟一途径,对数据库系统的性能起着决定性的作用。对于同一条件下的SQL语句写法有很多,其中一些写法往往对性能又有很大影响。但是每个人掌握SQL语言的水平不同,如何才能保证写出高性能...
sql tuning oracle sql tuning
oracle的sqltuning 课程,但资源有些老,04年的
Speedemy-MySQL-Configuration-Tuning-Handbook.pdf Speedemy-MySQL-Configuration-Tuning-Handbook.pdf
Apache Kylin - Tuning - Dong Li;Apache Kylin - Tuning - Dong Li