Oracle性能优化实战 修改版（改正原有测试的指标数据）

最新推荐文章于 2024-07-12 17:34:46 发布
最新推荐文章于 2024-07-12 17:34:46 发布
阅读量1.3k 收藏
点赞数
分类专栏： Oracle 文章标签： oracle 测试 性能优化 date logging byte
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/CSKnight/article/details/1569366
版权

今天领导口头下达一个任务,是研究如何通过索引加快某张表的检索速度.先介绍一下该测试数据库的情况，Oracle9i，Win2003Server，使用users表空间，数据量为2.4G，其中99%的数据集中在GPRSDATA表里。该表数据结构如下：

CREATE TABLE GPRSDATA
(
  SID           VARCHAR2(11 BYTE)               NOT NULL,
  GPS_TIME      DATE                            NOT NULL,
  LONGITUDE     NUMBER(11,4)                    DEFAULT 0,
  LATITUDE      NUMBER(11,4)                    DEFAULT 0,
  ALTITUDE      NUMBER(11,4)                    DEFAULT 0,
  VELOCITY      NUMBER(7,4)                     DEFAULT 0,
  ANGLE         NUMBER(7,4)                     DEFAULT 0,
  ALARM_STATUS  NUMBER(8)                       DEFAULT 0,
  SYS_ID        NUMBER(38),
  DISC_ID       VARCHAR2(11 BYTE),
  DISC_TYPE     VARCHAR2(11 BYTE),
  ROAM_FLAG     VARCHAR2(11 BYTE),
  DISC_CODE     VARCHAR2(11 BYTE),
  OWNER_CODE    VARCHAR2(11 BYTE),
  SEND_FLAG     NUMBER(11)                      DEFAULT 1                     NOT NULL,
  SEND_TIME     DATE,
  CREAT_TIME    DATE,
  MSG_SEQ       VARCHAR2(30 BYTE),
  GET_FLAG      NUMBER(11)                      DEFAULT 0
)
LOGGING
NOCACHE
NOPARALLEL
NOMONITORING;

数据量为1400来万条，将来还会继续增加。估计诸位都会质疑当初设计是否有误。老衲在此不予以解释，并且老衲确定该表短期内不会有所调整。因此只好在不改表整体设计情况下，对数据库进行尽可能的调优。
该表已建立一个索引，当初建立该索引所费时间不详，估计不短。使用了GPS_TIME和SID组合索引。脚本如下：
CREATE INDEX GIS_C000003 ON HISGPRS_DATA_T
(GPS_TIME, SID)
LOGGING
NOPARALLEL;

在建立索引后进行字段GPS_TIME时间范围的查询，数据库端SQL执行时间需要1~2分钟，查询结果在页面加载的时间也很长。在B/S系统上实在是不能接受的速度。(待续)

================这是分割线，下面是调整方案====================================

HISGPRS_DATA_T表调整方案

1       概况

表HISGPRS_DATA_T存储了车辆的轨迹数据，每辆车的GPS数据每隔 10s或30s写入一次，现阶段至少有500辆车，因此该表每天写入数据量至少有24*60*500=720,000条。若是所有车辆都按 10m 间隔写入则每天写入数据量将达到24*60*6*500=4,320,000条，一个星期的数据将超过三千万。现有的表设计不能有效支持这么庞大的数据量，因此需要对其进行调整。

2       环境

数据库版本

Oracle9i Enterprise Edition Release 9.2.0 .1.0

数据库工具

TOAD for Oracle

服务器

Windows server 2003

表概况

名称

HISGPRS_DATA_T

数据量

14,096,175条

约束

SYS_ID   PRIMARY KEY

索引

(GPS_TIME,SID)

3       测试1

1.         执行一年的GPRS数据，查询语句如下：

SELECT HISGPRS_DATA_T.GPS_TIME AS 定位时间,

HISGPRS_DATA_T.LONGITUDE AS 经度,

HISGPRS_DATA_T.LATITUDE AS 纬度

FROM HISGPRS_DATA_T

WHERE HISGPRS_DATA_T.SID = '80100516'  and

HISGPRS_DATA_T.GPS_TIME between to_date(' 2006-2-27 ','yyyy-MM-dd') and to_date('2007-2-26 23:59:59','yyyy-MM-dd HH24:MI:SS')

ORDER BY HISGPRS_DATA_T.GPS_TIME DESC;

执行时间03：32mins，返回记录335,211条。

 

2.         修改查询条件为查询一个月的GPRS数据：

SELECT HISGPRS_DATA_T.GPS_TIME AS 定位时间, HISGPRS_DATA_T.LONGITUDE AS 经度,

  HISGPRS_DATA_T.LATITUDE AS 纬度

  FROM HISGPRS_DATA_T

  WHERE HISGPRS_DATA_T.SID = '80100516'  and

HISGPRS_DATA_T.GPS_TIME between to_date(' 2007-01-01 ','yyyy-MM-dd') and to_date('2007-01-31 23:59:59','yyyy-MM-dd HH24:MI:SS')

ORDER BY HISGPRS_DATA_T.GPS_TIME DESC;

执行时间23secs，返回记录13,088条。

 

3.         修改查询条件为查询一周的GPRS数据：

SELECT HISGPRS_DATA_T.GPS_TIME AS 定位时间, HISGPRS_DATA_T.LONGITUDE AS 经度,

HISGPRS_DATA_T.LATITUDE AS 纬度

FROM HISGPRS_DATA_T

WHERE HISGPRS_DATA_T.SID = '80100516'  and 

HISGPRS_DATA_T.GPS_TIME between to_date(' 2007-02-01 ','yyyy-MM-dd') and to_date('2007-02-07 23:59:59','yyyy-MM-dd HH24:MI:SS')

ORDER BY HISGPRS_DATA_T.GPS_TIME DESC;

执行时间02：41mins，返回记录189,445条。

4       调整

4.1    创建RANGE-HASH分区表：

1.         使用exp备份HISGPRS_DATA_T数据。

2.         重命名HISGPRS_DATA_T：

ALTER TABLE HISGPRS_DATA_T RENAME TO HISGPRS_DATA_TOLD

3.         创建存储子分区的表空间：

CREATE TABLESPACE TBS_GPRS_SUB_1 DATAFILE

  'TBS_GPRS_SUB_1' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED

LOGGING

PERMANENT

EXTENT MANAGEMENT LOCAL AUTOALLOCATE

BLOCKSIZE 8K

SEGMENT SPACE MANAGEMENT MANUAL;

 

CREATE TABLESPACE TBS_GPRS_SUB_2 DATAFILE

  'TBS_GPRS_SUB_2' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED

LOGGING

PERMANENT

EXTENT MANAGEMENT LOCAL AUTOALLOCATE

BLOCKSIZE 8K

SEGMENT SPACE MANAGEMENT MANUAL;

 

CREATE TABLESPACE TBS_GPRS_SUB_3 DATAFILE

  'TBS_GPRS_SUB_3' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED

LOGGING

PERMANENT

EXTENT MANAGEMENT LOCAL AUTOALLOCATE

BLOCKSIZE 8K

SEGMENT SPACE MANAGEMENT MANUAL;

 

CREATE TABLESPACE TBS_GPRS_SUB_4 DATAFILE

  'TBS_GPRS_SUB_4' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED

LOGGING

PERMANENT

EXTENT MANAGEMENT LOCAL AUTOALLOCATE

BLOCKSIZE 8K

SEGMENT SPACE MANAGEMENT MANUAL;

4.         创建名称为HISGPRS_DATA_T的RANGE-HASH分区表，表结构和HISGPRS_DATA_TOLD相同：

CREATE TABLE IISPR.HISGPRS_DATA_T

(

  SID           VARCHAR2(11 BYTE)               NOT NULL,

  GPS_TIME      DATE                            NOT NULL,

  LONGITUDE     NUMBER(11,4)                    DEFAULT 0,

  LATITUDE      NUMBER(11,4)                    DEFAULT 0,

  ALTITUDE      NUMBER(11,4)                    DEFAULT 0,

  VELOCITY      NUMBER(7,4)                     DEFAULT 0,

  ANGLE         NUMBER(7,4)                     DEFAULT 0,

  ALARM_STATUS  NUMBER(8)                       DEFAULT 0,

  SYS_ID        NUMBER(38)                      NOT NULL,

  DISC_ID       VARCHAR2(11 BYTE),

  DISC_TYPE     VARCHAR2(11 BYTE),

  ROAM_FLAG     VARCHAR2(11 BYTE),

  DISC_CODE     VARCHAR2(11 BYTE),

  OWNER_CODE    VARCHAR2(11 BYTE),

  SEND_FLAG     NUMBER(11)     DEFAULT 1      NOT NULL,

  SEND_TIME     DATE,

  CREAT_TIME    DATE,

  MSG_SEQ       VARCHAR2(30 BYTE),

  GET_FLAG      NUMBER(11)                      DEFAULT 0

)

TABLESPACE USERS

PCTUSED    0

PCTFREE    10

INITRANS   1

MAXTRANS   255

STORAGE    (

            INITIAL          50M

            NEXT             10M

            MINEXTENTS       1

            MAXEXTENTS       2147483645

            PCTINCREASE      0

            BUFFER_POOL      DEFAULT

           )

PARTITION BY RANGE (GPS_TIME) SUBPARTITION BY HASH (SID)

        SUBPARTITIONS 8 STORE IN (TBS_GPRS_SUB_1,TBS_GPRS_SUB_2,TBS_GPRS_SUB_3,TBS_GPRS_SUB_4)

(

    PARTITION HISGPRS_B2007 VALUES LESS THAN(TO_DATE(' 2007-01-01 ','YYYY-MM-DD'))

        LOGGING

        NOCOMPRESS

        PCTUSED    40

        PCTFREE    10

        INITRANS   1

        MAXTRANS   255

)

CACHE

NOPARALLEL

NOMONITORING;

4.2    创建分区索引

1.         创建存储索引的表空间TBS_GPRS_IDX：

CREATE TABLESPACE TBS_GPRS_IDX DATAFILE

  'TBS_GPRS_IDX' SIZE 1024M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED

LOGGING

PERMANENT

EXTENT MANAGEMENT LOCAL AUTOALLOCATE

BLOCKSIZE 8K

SEGMENT SPACE MANAGEMENT MANUAL;

2.         创建全局分区唯一索引HISGPRS_SYS_ID：

CREATE UNIQUE INDEX HISGPRS_SYS_ID ON HISGPRS_DATA_T(SYS_ID)

GLOBAL PARTITION BY RANGE(SYS_ID)

(

    PARTITION IDXP_SYSID VALUES LESS THAN(MAXVALUE) TABLESPACE TBS_GPRS_IDX

);

3.         创建本地分区索引IDX_TIME_SID：

CREATE INDEX IDX_TIME_SID ON HISGPRS_DATA_T(GPS_TIME,SID) LOCAL TABLESPACE TBS_GPRS_IDX;

4.         查看索引状态，确认STATUS的值都是USABLE：

SELECT *

FROM DBA_IND_PARTITIONS WHERE INDEX_NAME = 'HISGPRS_SYS_ID';

 

SELECT *

FROM DBA_IND_SUBPARTITIONS WHERE INDEX_NAME = 'IDX_TIME_SID';

4.3    创建JOB

1.         确认是否存在DBMS_JOB包：

DESC dbms_job;

如果提示ORA-04043错误，则执行下面的语句：

@D:/oracle/ora92/rdbms/admin/dbmsjob.sql

2.         创建存储过程PROC_ALTER_PARTITION，该存储过程每次执行都删除之前第90天的分区（此处可修改为备份数据后删除），创建当前日期十天后的分区：

CREATE OR REPLACE PROCEDURE PROC_ALTER_PARTITION

AS

BEGIN

   EXECUTE IMMEDIATE    'ALTER TABLE HISGPRS_DATA_T DROP PARTITION HISGPRS_'

                     || TO_CHAR (SYSDATE - 90, 'yyyymmdd') || ' UPDATE GLOBAL INDEX';

 

   EXECUTE IMMEDIATE    'alter table test add partition test_'

                     || TO_CHAR (SYSDATE, 'yyyymmdd')

                     || ' values less than ( to_date('''

                     || TO_CHAR (SYSDATE + 1, 'yyyy-mm-dd')

                     || ''',''yyyy-mm-dd'')) tablespace test';

END PROC_ALTER_PARTITION;

3.         创建job，每天执行一次：

variable job_partition number;

begin

dbms_job.submit(:job_partition,'PROC_ALTER_PARTITION;',trunc(sysdate),'trunc(sysdate)+1',true);

    commit;

end;

5       测试2

1.         执行一年的GPRS数据，查询语句如下：

SELECT HISGPRS_DATA_T.GPS_TIME AS 定位时间,

HISGPRS_DATA_T.LONGITUDE AS 经度,

HISGPRS_DATA_T.LATITUDE AS 纬度

FROM HISGPRS_DATA_T

WHERE HISGPRS_DATA_T.SID = '80100516'  and

HISGPRS_DATA_T.GPS_TIME between to_date(' 2006-2-27 ','yyyy-MM-dd') and to_date('2007-2-26 23:59:59','yyyy-MM-dd HH24:MI:SS')

ORDER BY HISGPRS_DATA_T.GPS_TIME DESC;

执行时间 14 sec，返回记录335,211条。

 

2.         修改查询条件为查询一个月的GPRS数据：

SELECT HISGPRS_DATA_T.GPS_TIME AS 定位时间, HISGPRS_DATA_T.LONGITUDE AS 经度,

  HISGPRS_DATA_T.LATITUDE AS 纬度

  FROM HISGPRS_DATA_T

  WHERE HISGPRS_DATA_T.SID = '80100516'  and

HISGPRS_DATA_T.GPS_TIME between to_date(' 2007-01-01 ','yyyy-MM-dd') and to_date('2007-01-31 23:59:59','yyyy-MM-dd HH24:MI:SS')

ORDER BY HISGPRS_DATA_T.GPS_TIME DESC;

执行时间 3secs，返回记录13,088条。

 

3.         修改查询条件为查询一周的GPRS数据：

SELECT HISGPRS_DATA_T.GPS_TIME AS 定位时间, HISGPRS_DATA_T.LONGITUDE AS 经度,

HISGPRS_DATA_T.LATITUDE AS 纬度

FROM HISGPRS_DATA_T

WHERE HISGPRS_DATA_T.SID = '80100516'  and 

HISGPRS_DATA_T.GPS_TIME between to_date(' 2007-02-01 ','yyyy-MM-dd') and to_date('2007-02-07 23:59:59','yyyy-MM-dd HH24:MI:SS')

ORDER BY HISGPRS_DATA_T.GPS_TIME DESC;

执行时间 7 secs，返回记录189,445条。

6       总结

经过此次调整后，性能有如下变化：

1.         HISGPRS_DATA_T的查询性能得到大幅提高。

2.         HISGPRS_DATA_T的删除性能得到大幅提高。

3.         HISGPRS_DATA_T的插入性能不会受到明显影响。

4.         自动维护超过90天的历史数据。

5.         部分分区表故障不会影响其他分区表数据的使用。

6.         分区表出现故障，只需修复该分区表即可。

7.         每次自动维护分区表时会自动维护索引，但仍建议定期重建索引。

8.         数据文件占用磁盘空间变大。

9.         索引占用空间变大。

 

 

CSKnight
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
Oracle查询优化改写2.0 第三章 操作多个表
sehunhh的博客
11-07 199
--------------------------------------------chapter3操作多个表------------------------------------------ --3.1union all 与 空字符串 union all 通常用于合并多个数据集，oracle中常把空字符串当做null处理 select sysdate from dual where '' is null;--2020/11/3 15:23:28 但空字符串与null并不等价 select 1 as
Oracle查询优化改写——抄书学习04
weixin_41946505的博客
02-17 185
4. 通过与T100做笛卡尔积枚举30天的所有日期 select min_hd + (t500.id -1) as 日期 from （select min（hiredate） as min_hd , Max(hiredate) as max_hd from emp where ename in ('blake','jones') ） x ,t500 where t.50...
Oracle 查询优化改写（第三章）
小猪宝宝的博客
06-13 121
操作多个表
oracle case when修改,用case when 改写优化 sql
weixin_39588445的博客
04-07 283
开发给出的语句如下：2014/7/415:55:33selecta.attributeId,a.attributeName,a.attributeValue,b.attributeValue,c.attributeValuefrom(selectb.attributeId,b.attributeName,a.attributeValue,a.product_idfrom(SELECTT...
oracle之查询重写
DAVID的专栏
06-28 6492
查询重写是优化器的一个机制,优化器在解析的时候会对你的sql进行改写,改写成oracle认为效率更高的语句,改写可能是基于成本的或者是基于规则的,查询重写一般包括是否重写物化视图,view merge,子查询转成表连接等等。 物化视图的一个重要的特性就是支持查询重写。 如果初始化参数query_rewrite_enabled设置为TRUE，且使用CBO时，当发出对基表的查询，O
Oracle数据库性能优化实战指南.pdf
01-26
Oracle数据库性能优化是数据库管理员（DBA）的关键职责，尤其在企业数据日益增长的背景下，这一任务变得至关重要。本实战指南旨在提供一个全面的框架，帮助DBA们掌握Oracle数据库性能优化的核心技术和策略。 首先，...
oracle数据库性能优化.pdf
05-27
Oracle数据库性能优化是确保系统高效运行的关键环节，尤其是在大数据量和高并发的环境中。Oracle数据库因其先进、完整和集成的特性，在市场中占据主导地位，因此深入理解和掌握Oracle的优化技术至关重要。 首先，...
oracle数据库性能优化完整版资料.ppt
11-14
oracle数据库性能优化完整版资料.ppt
ORACLE存储过程性能优化技巧
06-19
ORACLE存储过程性能优化技巧
Oracle性能优化与故障诊断案例分享
01-10
Oracle性能优化与故障诊断案例分享，通过具体案例来进行分析，从而优化及诊断解决问题。
oracle 优化案例 模拟,Oracle进行模拟测试数据的一个例子
weixin_39903571的博客
04-10 86
Oracle进行模拟测试数据的一个例子原创于2009年08月02日，2009年10月22日迁移至此。-- 'u', 'U' -返回全是大写的字符串-- 'l', 'L' -返回全是小写的字符串-- 'a', 'A' -返回大小写结合的字符串-- 'x', 'X' -返回全是大写和数字的字符串-- 'p', 'P' -返回键盘上出现字符的随机组合SELECTtrunc(dbms_random.val...
Oracle 优化和性能调整
weixin_30457551的博客
06-30 405
分析评价Oracle数据库性能主要有数据库吞吐量、数据库用户响应时间两项指标。数据库用户响应时间又可以分为系统服务时间和用户等待时间两项，即： 　　数据库用户响应时间=系统服务时间＋用户等待时间 　　因此，获得满意的用户响应时间有两个途径：一是减少系统服务时间，即提高数据库的吞吐量；二是减少用户等待时间，即减少用户访问同一数据库资源的冲突率。　　数据库性能优化包括如下几个部分： ...
oracle分区使用
qx020814的博客
07-12 204
又开始了一年一度的踩坑环节，刚刚和公司的一个大佬讨论了一下数据采集的表结构，打算按月分表，按天分区。1、首先选表时，就把选择的月份拼接在前面，所以这里的查询其实需要判断，不能查询未到的日期。按照指定的数据分区，如果不存在，就创建一个新的分区。2、查询表时，将指定的分表字段带进查询中。大佬还是很厉害的，好用！
如何解决 PostgreSQL 中由于索引不当导致的性能下降问题？
技术笔记
07-12 736
比如说，有一个订单表，其中“订单日期”这个列经常被用于按时间范围查询订单，但却没有为其创建索引，这就会导致数据库在查询时需要遍历整个表，极大地降低了查询速度。命令，我们可以查看查询的执行计划。再举一个例子，如果我们发现某个表上存在多个类似的索引，比如“idx_age_1”和“idx_age_2”，并且它们的定义几乎相同，这时候就可以考虑删除其中一个冗余的索引，以减少维护成本和提高性能。然而，就像任何复杂的系统一样，它也可能会遇到性能方面的挑战，其中由于索引不当导致的性能下降问题是比较常见且令人头疼的。
SQL Server镜像与日志：数据保护的双重保障
2401_85763803的博客
07-11 782
数据库镜像是一种实时的数据复制技术，它通过在主服务器和镜像服务器之间建立一个会话，将主数据库的所有变更实时复制到镜像数据库。如果主服务器发生故障，可以快速切换到镜像服务器，从而实现高可用性。日志传送是一种基于事务日志的数据复制技术，它将主数据库的事务日志文件发送到一个或多个辅助服务器上，然后应用这些日志以更新辅助数据库。与数据库镜像不同，日志传送不是实时的，它依赖于日志文件的生成和发送频率。
Oracle字符集修改
最新发布
余十步的博客
07-12 747
Oracle数据库默认的字符集编码为US7ASCII，这个编码是不支持中文的，如果想要在数据库存储中文，就需要修改编码为。执行此命令可能会报错：ORA-12712: 新字符集必须为旧字符集的超集。-- 关闭Oracle数据库的Advanced Queuing进程。-- 关闭Oracle数据库的Job Queue进程。-- 修改为ZHS16GBK/GB18030。-- 设置Oracle数据库为限制访问状态。修改字符集的前提是知道我们现在用的是什么字符集。7. 关闭数据库，重新启动。-- 修改为UTF-8。
PostgreSQL 如何解决因长时间未优化导致的数据库性能衰退？
技术笔记
07-12 1060
在数据库的世界里，PostgreSQL 就像是一位忠实可靠的伙伴，但如果长时间对它疏于照顾和优化，它也可能会“闹脾气”，表现出性能衰退的情况。有时候，不是数据库本身的问题，而是我们向它提出的“要求”不合理。在数据库中，如果没有为经常用于查询的字段创建合适的索引，数据库在执行查询时就不得不遍历大量的数据，从而导致性能下降。如果服务器的硬件资源有限，比如 CPU 处理能力不足、内存不够、磁盘 I/O 速度慢等，也会制约 PostgreSQL 的性能发挥，如同让一个大力士在狭小的空间里施展拳脚，难以施展开来。
构建LangChain应用程序的示例代码：62、如何使用Oracle AI向量搜索和Langchain构建端到端的RAG（检索增强生成）pipeline
Hugo的博客
07-10 1065
本指南突出了Oracle AI向量搜索的强大功能，包括与关系型数据的结合、广泛的文档格式支持、灵活的嵌入和摘要生成选项，以及优化的搜索性能。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值