您现在的位置是:首页>博客详情

Oracle千万级表分区实战(2)基本知识积累

FreshMan2020年03月17日 16:01oracle,分表 分区 local index63

简介Oracle千万级表分区实战,该系列文章将详细讲述一个实际的问题的解决过程,并将讲述在解决该问题中需要积累的知识和注意事项。有目的的学习总是最快的。将分为以下几步:1、问题及需要处理流程;2、基本知识积累;3、解决问题

        通过上一篇文章的分析,我们需要进行数据表的分表,分区处理掌握一些基本的操作。对于我们一个不了解的人来说肯定是需要对知识有一定积累才能够完成该任务的。首先我们不能那线上的数据跑吧,那么问题来了,没有那么多的数据怎么能知道最终的性能如何呢,这就需要我们自己通过某种方式生成一个测试表,表中包含大量的数据。

        1、oracle生成千万测试数据

--生成多条测试数据
select rownum as id,
       to_char(sysdate+rownum/24/3600,'yyyy-mm-dd hh24:mi:ss')as inc_datetime,
       trunc(dbms_random.value(0, 100)) as random_id,
       dbms_random.string('x', 20) random_string
  from dual
connect by level <= 10;

        微信图片_20200317160031.png

        上面SQL是利用了Oracle数据库语法的几个使用小技巧实现的:

        1)利用oracle特有的connect by树形连接语法生成测试记录,level<=10表示要生成10条记录;

        2)利用rownum虚拟列生成递增的整数数据;

        3)利用sysdate函数加一些简单运算来生成日期数据,本例中是每条记录的时间加1秒;

        4)利用dbms_random.value函数生成随机的数值型数据,本例中是生成0到100之间的随机数;

        5)利用dbms_random.string函数生成随机的字符型数据,本例中是生成长度为20的随机字符串,字符串中可以包含字符或数值。

        ok,那么如果生成10万条测试记录表可以将上面的10修改为10万;估计可能一分钟能生成完成。

        2、查询表空间信息

        在上面生成数据时也不一定就一帆风顺,因为你在测试环境生成,可能会出现表空间不足的问题。下面记录几个SQL用于查询,查看表空间信息:

        --查询表空间使用情况

SELECT a.tablespace_name,
       a.bytes / 1024 / 1024 total,
       b.bytes / 1024 / 1024 used,
       c.bytes / 1024 / 1024 free,
       (b.bytes * 100) / a.bytes "% USED ",
       (c.bytes * 100) / a.bytes "% FREE "
  FROM sys.sm$ts_avail a, sys.sm$ts_used b, sys.sm$ts_free c
 WHERE a.tablespace_name = b.tablespace_name
   AND a.tablespace_name = c.tablespace_name;

        QQ拼音截图未命名.png

        上面的TOTAL,USED,FREE分别表示对应表空间的总容量,单位为MB,%USED,%FREE分别表示已经使用百分比,空闲百分比。从这个结果也可以看出,名字为ZWY的空间已经所剩不多了。那接下来需要将空间扩大,扩大就需要知道表空间文件存放在哪里:

--查询表空间文件路径
select * from dba_data_files where tablespace_name = 'ZWY';

        QQ拼音截图未命名.png

        找到了文件的路径,这就可以扩大表空间了。扩大表空间有一些限制:一个数据文件最大只能32G;扩容的方式有:

        1)手工改变已经存在数据文件的大小

--增加表空间文件
alter tablespace ZWY add datafile '/oracle/ora10g/oradata/uf20/zwy02.dbf' size 50000m;

        2)允许已经存在的数据文件自动增长

ALTER DATABASE DATAFILE '/oracle/ora10g/oradata/uf20/zwy02.dbf'
 AUTOEXTEND ON NEXT 100M MAXSIZE 20480M;


        3)增加数据文件

alter tablespace ZWY add datafile '/oracle/ora10g/oradata/uf20/zwy03.dbf' size 50000m;

        3、生成测试表完成查看表占用的物理空间大小

--查询某张表物理空间大小
select segment_name, bytes / 1024 / 1024 as m
  from user_segments
 where segment_type = 'TABLE'
 order by m desc;

        QQ拼音截图未命名.png

        4、在操作中不禁会想,我们这些操作会产生一些日志啊,这也会占用空间的啊,那么日志文件相关的记录怎么查询呢,提供如下:

--数据库中拥有的日志文件数
select * from V$LOG;
--需要归档的日志文件数
select * from V$ARCHIVE;
--查看日志文件
select * from v$logfile;
--查看最近操作
select * from v$sql;
--查看所有最近操作
select * from v$sqlarea;
--查看数据库级别日志的归档模式、记录模式
select log_mode, force_logging from v$database;
--参看表空间级别日志记录模式
select tablespace_name, logging, force_logging from dba_tablespaces;
--查看对象级别的日志记录模式
select table_name, logging from user_tables;
--数据库从非强制日子模式切换到强制日志模式
--alter database force logging;
--数据库从强制日子模式切换到非强制日志模式
--alter database no force logging;--查询rudo日志大小
select name,value,class from v$sysstat where name='redo size';

        为什么会考虑这个问题呢,是因为在对一个非常大的表执行insert的时候,会消耗非常多的资源,因为update表的时候,oracle需要生成redo log和undo log;此时最好的解决办法是用insert,并且将表设置为nologging;当把表设为nologging后,并且使用的insert时,速度是最快的,这个使用oracle只会生成最低限度的必须的redo log,儿没有一点undo信息。

        下一篇详细讨论nologging模式