Oracle千万级以上数据量查询速度慢问题解决

您所在的位置：网站首页 › oracle大量数据导出 › Oracle千万级以上数据量查询速度慢问题解决

Oracle千万级以上数据量查询速度慢问题解决

2023-09-09 19:56| 来源: 网络整理| 查看: 265

Oracle千万级以上数据量查询速度慢问题解决 1、将原表修改为分区表1.1 分区表判断1.2 重建分区表1.2.1 将原表改名1.2.2 重建分区表1.2.3 拷贝数据 2、创建索引2.1 启动执行计划2.2 无索引2.3 索引失效2.4 需要指定索引 3、停止耗时过长的sql4、停止耗时过长的proc和job4.1 停止job4.2 停止proc 5、数据库所在服务器问题5.1 数据库无法正常执行表分析5.2 Oracle的I/O

当一张数据库表的数据量达到千万级别时，经常会出现查询耗时过长或者查询超时的情况，此时分区表已经无法帮助我们。这里根据工作经验，总结几种生产环境下Oracle查询速度慢的解决方案： 1、将原表修改为分区表。 2、创建索引。 3、停止耗时过长的sql。 4、停止耗时过长的proc和job。 5、数据库所在服务器问题。

1、将原表修改为分区表

系统投入生产环境后一段时间后，由于实际使用超出原有的预想，导致某张数据库表的数据量过大，这时就应该将该表修改为分区表。

1.1 分区表判断

查询是否是分区表：

select partition_name from user_tab_partitions where table_name = 'tableName';

注意表名大写。

1.2 重建分区表 1.2.1 将原表改名 ALTER TABLE tableName RENAME TO table_20190418; 1.2.2 重建分区表 -- Create table create table tableName ( TIMESTAMP VARCHAR2(10) not null, ... ) partition by range (TIMESTAMP) ( partition tableName2017_04_15 values less than ('2017041600') tablespace tablespaceName pctfree 10 pctused 40 initrans 1 maxtrans 255 storage ( initial 8M next 1M minextents 1 maxextents unlimited ) ); 1.2.3 拷贝数据 insert into tableName select * from tableName_20190418 where TIMESTAMP like '201904%'; commit;

注意：以上操作最好在系统的非业务高峰操作。重建分区表的操作比较简单，这里不再详述。

2、创建索引

如果已经是分区表，这时就需要通过执行计划分析是否是查询的sql缓慢。

2.1 启动执行计划 -- 分析执行计划 set autotrace on;

执行上面语句后，在执行可能查询缓慢的sql，就可以看到Oracle是如何一步步执行这个sql的。查看执行计划，如果sql没有走索引，这是可能存在3种情况：无索引、索引失效、需要指定索引。另外下面语句可以显示sql运行的耗时：

-- 显示sql运行时间 set timing on; 2.2 无索引

查询分区表是否有索引：

-- 查看分区表索引 select index_name, table_name, locality from user_part_indexes where index_name = 'TABLE_NAME';

下面是一些查询索引常用的语句：

-- 查看表索引 select * from user_indexes where index_name = 'TABLE_NAME'; -- 查看分区表索引所在表空间 SELECT index_name, tablespace_name FROM USER_IND_PARTITIONS T WHERE T.INDEX_NAME = 'TABLE_NAME'; -- 查看一张表在哪些字段上建了索引 select index_name, column_name, column_position from all_ind_columns where table_name = 'TABLE_NAME'

创建对应的分区表索引：

create index table_IDX on tableName (columnName) LOCAL ONLINE tablespace tablespaceName pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited );

local：代表创建的是分区表索引。 online：代表在线创建索引，用于生产环境。

2.3 索引失效

如果索引存在，就要确认当前索引是否仍生效：

-- 本地索引的整体状态 select * from DBA_PART_INDEXES where index_name = 'INDEX_NAME';

下面是一些相关常用语句：

-- 索引分区状态 select * from dba_ind_partitions where index_name = 'INDEX_NAME'; -- 全局索引状态 select index_name, status, last_analyzed, partitioned from dba_indexes where index_name='INDEX_NAME'

如果索引失效，需重建本地索引：

alter index indexname rebuild online; 2.4 需要指定索引

当本地索引存在并生效的情况下，执行sql时仍不走索引，此时常见的方法就是使用指定索引：

-- 强制指定索引 SELECT /*+INDEX(t IDX_T_RES_ALLOSEG_ALLOID)*/ from tableName t;

这里的/…/中间不要有空格，t为表的别名。

3、停止耗时过长的sql

如果排除了分区表和索引的问题，那么久可能是Oracle正在运行着别的耗时的sql：

-- 查找正在运行的sql select b.sid, b.username, b.serial#, a.spid, b.paddr, c.sql_text, b.machine from v$process a, v$session b, v$sqlarea c where a.addr = b.paddr and b.sql_hash_value = c.hash_value;

需要停止耗时的sql：

-- 根据运行的sql生成kill语句 select sql_text, b.sid, b.serial#, 'alter system kill session''' || b.SID || ',' || b.serial# || ''';', b.username from v$process a, v$session b, v$sqlarea c where a.addr = b.paddr and b.sql_hash_value = c.hash_value;

运行生成的sql，杀掉正在运行的sql。

4、停止耗时过长的proc和job

一般都是job中调用了proc，所以这里一起讲解。

4.1 停止job -- 查找正在执行的job select a.sid, a.job, b.what, a.LAST_DATE, a.LAST_SEC, a.THIS_DATE, a.THIS_SEC, b.priv_user, b.broken from dba_jobs_running a left outer join dba_jobs b on a.job = b.JOB; -- 查找正在执行的job的session select b.SID, b.SERIAL#, c.SPID from dba_jobs_running a, v$session b, v$process c where a.sid = b.sid and b.PADDR = c.ADDR --立刻停止job ALTER SYSTEM KILL SESSION 'sid, serial#'; 4.2 停止proc -- 查找当前运行的存储过程并生成kill语句 select b.sid, b.SERIAL#, a.OBJECT, 'alter system kill session ' || '''' || b.sid || ',' || b.SERIAL# || ''';' kill_command from SYS.V_$ACCESS a, SYS.V_$session b where a.type = 'PROCEDURE' and a.sid = b.sid and b.status = 'ACTIVE';

运行生成的sql，就可以kill掉正在执行的porc。

5、数据库所在服务器问题 5.1 数据库无法正常执行表分析 -- 数据库是否开启自动表分析 select * from V$PARAMETER where name = 'statistics_level'; -- 查看表分析job select t.OWNER, t.JOB_NAME, t.JOB_CREATOR, t.LAST_START_DATE, t.LAST_RUN_DURATION, t.NEXT_RUN_DATE, t.ENABLED, t.STATE, t.RUN_COUNT, t.FAILURE_COUNT from DBA_SCHEDULER_JOBS t where JOB_NAME = 'GATHER_STATS_JOB'; -- 查看表分析job执行情况 select t.LOG_ID, t.OWNER, t.job_name, t.SESSION_ID, t.SLAVE_PID, t.STATUS, t.CPU_USED, t.ACTUAL_START_DATE from DBA_SCHEDULER_JOB_RUN_DETAILS t where t.job_name = 'GATHER_STATS_JOB' order by t.actual_start_date desc;

这块理解的不是很清楚，就不多误导大家了。

5.2 Oracle的I/O

需要处理千万级数据量的Oracle所在服务器，Oracle每秒的I/O需要在10M以上，否则多少都会影响查询速度。

PS：数据库服务器本身的问题导致查询缓慢，是最不可能发生的。

本文仅是我自己的理解，和解决生产环境问题的方法，大家有更好的解决方案欢迎评论留言，谢谢！

以上手打，转载请注明出处（https://blog.csdn.net/qq_16239633/article/details/102719462），嘿嘿。

【本文地址】

Oracle千万级以上数据量查询速度慢问题解决

Oracle千万级以上数据量查询速度慢问题解决

今日新闻

推荐新闻