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

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、停止耗时过长的sql
• 4、停止耗时过长的proc和job
• • 4.1 停止job
  • 4.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），嘿嘿。