Oracle，day10，让性能更优秀_oracle 有填充因子吗-优快云博客

本文链接：https://blog.youkuaiyun.com/holy516203/article/details/53729562

前言：

东莞信大融合创新研究院。。光荣的成为了一名科研人员？

Oracle初始化参数#

在之前的章节中，我们已经学习了基本的Oracle开发技术，以及如何进行PL/SQL编程。Oracle是一个跨平台的数据库系统，会面对不同的操作系统平台及硬件环境。因此多数据情况下，对基于Oracle数据库之上的应用软件，需要进行参数调整，以达到性能优化的目的。本章主要介绍系统调整，查询优化，数据优化几个方面，并对Oracle性能优化有关内容进行介绍。

Oracle数据库系统中起到调节作用的参数叫做初始化参数。在Oracle 8i及以前的版本中，这些初始化参数记录在initSID. ora文件中，此文件又称静态初始化文件。而在Oracle9i及后续版本中，这些参数都记录在spfileSID. ora_进制文件中，此文件又称服务器参数文件。下面我们主要介绍初始化参数的用法与SPFILE文件的关系。

SPFILE是一个二进制文件，只能由Oracle系统进行写入。如果对参数进行修改，可将所有修改的参数写到SPFILE中或仅使当前Oracle实例有效而不必写到初始化文件中。

如果需要对初始化参数文件中的参数进行修改，可以使用ALTER SYSTEM或者ALTERSESSION命令完成。用ALTER SYSTEM所修改的参数会影响到整个数据库实例，而使用ALTERSESSION命令修改的参数只影响该会话，通常一次登录即是一次会话。

0racle数据库SGA调整

理解Oracle内存分配

在Oracle数据库的每一个版本中，对于内存都有特别的要求，一般会将这些要求包含在安装说明文档里。Oracle的存储信息参数指的是对内存和硬盘的要求。因为内存的存取速度比硬盘要快很多，因此用内存来存放数据更能满足快速请求的要求。但是内存资源一般比硬盘相对稀缺，而且内存的配置也是很有限的，所以调整内存的分配可以有效利用内存，这是进行数据库性能调整工作中重要的一项内容。

一般来说，内存的调整是在应用程序和SQL语句调整之后。如果应用程序和SQL调整前就进行了内存分配，那么在修改完应用程序和SQL语句之后，还需要对Oracle内存结构进行再次的调整。此外，建议在调整I/O前先调整内存分配，调整内存分配以建立Oracle进行I/O操作所必须的内存总量。

select * from v$sga;

这行代码可以让我们看到sga缓存区的大小，其中，fixed size表示固定大小；variable size表示可变的大小；database buffers表示数据库的缓存；redo buffers重做日志缓存。他们values都是以B为单位的，也就是一个字节为一个单位。

按理说占的大运行的速率应该更高，但是cpu和硬盘的速率也决定了oracle的速率。

内存调整相关名次

在讲解SGA调整之前，我们首先来回顾一下什么是SGA;并由此接触相关的名词。

SGA：即System Global Area的缩写，是Oracle实例的组成部分，在实例启动时进行分配。作为系统的SGA主要由三个部分组成，分别是共享池、数据缓冲区、日志缓冲区。
共享池(Share Pool)：用于缓存最近被执行的SQL语句和最近被使用的数据定义，主要包括库缓存和数据字典缓冲区。库缓存主要用来存放用户SQL命令，而数据字典缓冲区用于存放数据库允许的动态信息。
数据缓冲区(Database Buffer Cache)：用于缓存从数据文件中检索出来的数据块，可以大大提高查询和更新数据的性能。（我的理解是缓存我们通过DML语句操作得到的结果，个人见解，请多指教。）
日志缓冲区(Log Buffer)：应用程序对数据库数据的操作，都会记录在日志文件中，为了提高日志文件的操作效率，Oracle数据库会在日志缓冲区中先记录日志，之后在特定的时间点，后台进程会批量写入到日志文件中。（就是个中转站的赶脚。。）

共享池分配调整

由于库缓存和数据字典缓存丢失引起的操作时间往往比SGA中其他组件的缓存丢失要多。因此，在SGA中首先应考虑对共享池的调整。在调整共享池时，应该首先集中在库缓存上，因为库缓存的大小和数据字典缓存的大小没有单独设置，而是Oracle自动按照一定的算法在共享池中进行分配。而按照Oracle内存空间的分配算法，如果缓存的命中率高，那么数据字典缓存的命中率也会很高。库缓存的命中率高而数据字典缓存命中率低得情况是很少见的。如果共享池很小，就会消耗很多CPU资源并且容易引起竞争。但是如果把共享池设置为很大，就会消耗很多内存资源，同时其他组件能使用的内存资源也相对减少。另一方面，当共享池很大时，缓冲的内容就会很多，这样也必然使查找的速度变慢。

调整共享池主要包括三方面：库高速缓存、数据字典缓存、会话信息。由于Oracle管理共享池中的数据算法，使得数据字典缓存中的数据比库高速缓存中的数据在内存中存留的时间更长，因此，只要把库高速缓存调整成可以接受的命中率，就能提高数据字典缓存的命中率。

select sum(pins) "请求数",sum(reloads) "不命中数" from v$librarycache;

上面这行代码就是看我们查看库高速缓存的代码，一般情况下，命中率大于70%我们就不需要调整。数据越多，性能可能就会越差。

如果你觉得上面这行代码还得计算，那么就用下面这一行直接看命中率是多少。

select (sum(pins-reloads))/sum(pins) "命中率" from v$librarycache;

Tips： pins：用于显示在库高速缓存中执行的次数或者请求数。
reloads：用于显示在执行阶段库高速缓存不命中的次数

对数据字典调整

应用程序已经开始运行，同时数据库处于一种相对稳定状态之后，就可以借助动态性能视图V$ROWCACHE来检查、调整数据字典高速缓存的活动。

select sum(gets) 请求数,sum(getmisses) 不命中数 from v$rowcache;

select (sum(gets-getmisses-usage-fixed))/sum(gets) 数据字典使用率 from v$rowcache;

Tips： gets：用于显示数据字典请求总数；
getmisses：用于显示不命中的请求数；
usage：用于显示有效数据的缓存项数目；
fixed：用于显示固定的缓存项数目。

调整shared_pool_size的值

show parameter shared_pool_size;
alter system set shared_pool_size=100M scope=spfile;

show parameter open_cursors;
alter system set open_cursors=300;

如果我们需要调整共享池的大小，就用上面的代码调整，一个是调整共享池，另一个是调整打开的游标。scope=spfile就是存放在开头说的那个文件当中。一般这两个数据都是只改大不改小。

采用ALTER SYSTEM命令修改初始参数后，会影响整个ORACLE实例。为了确保查看修改效果，最好重启数据库实例。

虽然提高shared_pool_size参数的取值，有利于提高库高速缓存和数据字典缓存的命中率，从而提高Oracle运行性能，但不能盲目将shared_pool_size设置过大，因为shared_pool_size过大会增加管理负担和CPU的开销，超过500M可能是危险的，达到1G可能造成CPU的严重负担，甚至导致系统瘫痪。建议值设置可在200MB–350MB之间。如果shared_pool_size超过350M命中率还不高，可以试着从应用程序上找原因。

此外，如果是10g以后的版本，还要查看sga_target这个参数的设置，如果sga_target=0，可以按照上一段描述的经验调整shared_pool_size的取值。如果不是，那就说明Oracle自动分配shared_pool_size的取值，但是ORACLE会把指定的Shared_pool_size取值当做下限，就是说ORACLE在自动调整SGA中各个组件大小的时候不会把share_pool_size调到100M以下。

调整数据缓存区

Oracle数据库启动后不断收集和统计数据存取的情况，并将其存放在动态性能视图v$sysstat中。比方说请求数，命中率等都会在里面放。

select name,value from v$sysstat where name in('db block gets','consistent gets','physical reads');

Tips： db block gets：数据请求总数；
consistent gets：对内存缓冲区存取技能满足的请求数；
physical reads：磁盘文件存取的总数。

计算数据缓存命中率的公式如下：

命中率=1 - (physical reads／(db block gets+consistent gets))数据库缓存的命中率越高，说明缓冲区高速缓存能够满足使用，且性能良好。如果高速缓存大到足以容纳最经常存取的数据，在保持高命中率的前提下，可以通过适当减少初始化参数db_block_buffers的值来减少高速缓存的大小，从而节省内存的使用。

需要注意的是：如果通过计算得到的命中率低于70%，则会引发性能的下降，这时就应该立即通过增加db_block_buffers的值来扩大缓冲区高速缓存的大小。

索引

什么是索引

Oracle中索引的概念和SQL Server中的基本一样，索引是跟表相关的一种数据库逻辑存储结构，跟现实生活中图书的目录很相似，它能够加快数据的读取速度和完整性检查，即能够使对应表的SQL语句执行的更快、效率更高。

虽然索引能够优化性能，特别是查询，但是在使用索引时，还是应该遵循以下基本原则：合理安排索引列在create index语句中，列的排序会影响通过索引进行查询的性能，我们通常把最常用的列放在前面。

索引带来的是查询效率高，但是插入效率低。

1.限制表中索引的数量

虽然Oracle对与索引的数量没有限制，但是我们不能滥用索引。如果索引过多，修改表中的数据时对索引的更改的工作量会很大，效率也很低。

2.指定索引数据块空间的使用

创建索引时，索引的数据块是用表中现存的值进行填充，其最大值由PCTFREE进行设置。这里的PCTFREE就相当于sqlserver中的填充因子。填充因子：我们不把每个空间都填满，而是空出来一部分，方便我们添加数据。

3.根据索引大小设置存储参数

创建索引前应该预先估计索引的大小，以便更好的规划磁盘空间。

索引的分类

单列索引与符合索引

一个索引可以由一个或多个列组成，用于创建索引的列被称为“索引列”。
单列索引是基于单个列所创建的索引，复合索引是基于多列所创建的索引。

唯一索引与非唯一索引

唯一索引是索引列值不能重复的索引，非唯一索引是索引列值可以重复的索引。
无论是唯一还是非唯一索引，索引列都允许取null值。

标准（B-tree index，B树）索引

我们在使用create index 语句创建索引的时候，默认创建的就是B树索引。B树索引是用的最多的。以中间树为节点，左边都比根节点小，右边都比根节点大。
B树索引能够适应多种查询条件，包括使用“=”的精确匹配、使用“like”的模糊匹配、使用“<”或“>”的比较条件。
B树索引的局限是：当查询数据的范围超过表的10%之后，就不能显现出B树索引的良好性能了。主要用于精确查询。

位图索引

基数：是指某个列可能拥有的不重复值的个数。例如，sex列的基数为2（性别只能是男或女），maritalstatus列的基数为3（婚姻状况只能是未婚、已婚、离异）。
对于一些基数小的列，B树索引处理方式的效率比较低。
对于基数很小，只存在有限的几个固定值的列（如性别、婚姻状况、行政区、职称等），为了加快查询效率，应该在这些列上创建位图索引。
当某列的基数与表的总行数的比例小于1%时，建议在列上创建位图索引。

函数索引

在oracle中，不仅能够对表中的列创建索引，还可以对包含有列的函数或表达式创建索引，这种索引被称为“函数索引”。就是可以传参的，在索引的时候可以传参数。
根据函数或表达式的结果的基数情况，函数索引既可以采用普通的B树索引，也可以采用位图索引。

索引的应用

创建索引的语法：

CREATE [UNIQUE] INDEX [SCHEMA.lindex name 
ON table_name (col_name)
[TABLESPACE  ts ]   --表示索引存储的表空间
[STORAGE s]     --表示存储参数
[PCTFREE pf]        --表示索引数据块空闲空间的百分比
[NOSORT ns]     --表示不再排序
[SCHEMA]        --表示oracle模式，缺省默认为当前账户

修改索引的语法：

ALTER [UNIQUE] INDEX   index name
[INITRANS n]        --表示一个块内同时访问的初始事务的入口数
[MAXTRANS n]        --表示一个块内同时访问的最大事务的入口数
REBUILD         --表示根据原来的索引结构重建索引
[STORAGE <storage>] --表示存储数据，与CREATE INDEX相同

示例：

alter index idx_id rebuild storage(initial 1M next 512k);
--数据库对idx_id重新生成，并申请1M空间，超出1M每次额外申请512kb的空间来保存索引

删除索引：

drop index schema.index name;
--表结构被删除，那么与该表相关的索引也会一起被删除

创建唯一索引

在某一列上创建唯一索引，就是这一列的值不允许出现重复值。主键都是唯一索引。

语法：

（在emp表的ename列上创建一个唯一索引idx_emp_ename）

create unique index ide_emp_ename on emp(ename);

创建复合索引

如果select语句中的where子句引用了复合索引中的所有列或大多数列，则使用复合索引可以显著的提高查询速度。多列查询效率高。
创建此类索引时，应该注意定义中使用的列的顺序，通常，最频繁访问的列应该放置在列表的最前面。比方说，ID比名字常用，名字比工作常用。

语法：

create index idx_emp_ename_job on emp(ename,job);

创建位图索引

由于emp表的job列、deptno列的取值范围有限，并且经常需要基于这些列进行查询、统计、汇总工作，所以应该基于这些列创建位图索引。

语法：

create bitmap index idx_bm_job on emp(job);

Tips： set autotrace on explain;

这一行代码是用来看执行效率的，只是只能在sqlplus里面看到那些信息，比如cpu占用率，执行耗时等属性

创建索引的原则

一般不需要为数据量很小的表创建索引。
对于数据量比较大的表，如果经常需要查询的记录数小于表中所有记录数的10%，则可以考虑为该表创建索引。
应该为大部分列值不重复的列创建索引。大部分重复值的要创建也应该创建位图索引。
对于取值范围较大的列（如ename列），应该创建B树索引；对于取值范围较小的列（如sex列），应该创建位图索引。对于查询来讲，有索引总比没有强。
对于包含很多歌null值，但是经常需要查询所有非null值记录的列，应当创建索引。
不能在clob或blob等大对象数据类型列上创建索引。
如果大部分情况下只对表执行只读操作，可以为该表创建更多的索引以提高查询速度。
如果大部分情况下需要对表执行更新操作，则应该为该表少创建一些索引，以提高更新速度。

表分区

为什么使用表分区

分区功能能够将表、索引或索引组织表进一步细分为段，这些数据库对象的段叫做分区。每个分区有自己的名称，还可以选择自己的存储特性。从数据库管理员的角度来看，一个分区后的对象具有多个段，这些段既可进行集体管理，也可单独管理，这就使数据库管理员在管理分区后的对象时有相当大的灵活性。从应用程序的角度来看，分区后的表与非分区表完全相同，使用SQL DML命令访问分区后的表时，无需任何修改。

Oracle的表分区功能通过改善可管理性、性能和可用性，从而为各式应用程序带来了极大的好处。

表分区的好处

改善查询性能：对分区对象的查询可以仅搜索自己关心的分区，提高检索速度。
增强可用性：如果表的某个分区出现故障，表在其他分区的数据仍然可用。
维护方便：如果表的某个分区出现故障，需要修复数据，只修复该分区即可。
均衡I/O：可以把不同的分区映射到磁盘以平衡I/O，改善整个系统性能。

表分区的实现

1.范围分区

范围分区将数据基于范围映射到每一个分区，这个范围是你在创建分区时指定的分区键决定的。这种分区方式是最为常用的，并且分区键经常采用日期。举个例子：你可能会将销售数据按照月份进行分区。

当使用范围分区时，请考虑以下几个规则：
(1)每一个分区都必须有一个VALUES LESS THEN子句，它指定了一个不包括在该分区中的上限值。分区键的任何值等于或者大于这个上限值的记录都会被加入到下一个高一些的分区中。
(2)所有分区，除了第一个，都会有一个隐式的下限值，这个值就是此分区的前一个分区的上限值。
(3)在最高的分区中MAXVALUE被定义。MAXVALUE代表了一个不确定的值。这个值高于其它分区中的任何分区键的值，也可以理解为高于任何分区中指定的VALUE LESS THEN的值，同时包括空值。

语法：

PARTITION BY RANGE(RANGE_COLUMN)(
    PARTITION part_1 VALUES LESS THAN (parameter) TABLESPACE space_name
        …
)

Tips： RANGE_ COLUMN：表示按照表中哪个字段进行分区；
PARTITION：表示进行分区、划分；
part—1：表示所分区的名称；
space_name：所属的表空间。

示例：

假设有一个CUSTOMER表，表中有数据200000行，我们将此表通过CUSTOMER ID进行分区，每个分区存储100000行，我们将每个分区保存到单独的表空间中，这样数据文件就可以跨越多个物理磁盘。很高级啊！可以把一个表放在多个磁盘里。

CREATE TABLE CUSTOMER
(
CUSTOMER_ID NUMBER NOT NULL PRIMARY KEY,
FIRST_NAME VARCHAR2(30) NOT NULL,
LAST_NAME VARCHAR2(30) NOT NULL,
PHONE VARCHAR2(15) NOT NULL,
EMAIL VARCHAR2(80),
STATUS CHAR(1)
)
PARTITION BY RANGE(CUSTOMER_ID)
(
    PARTITION CUS_PART1 VALUES LESS THAN (100000) TABLESPACE CUS_TS01,
    PARTITION CUS_PART1 VALUES LESS THAN (200000) TABLESPACE CUS_TS02
)

上面我们演示的是先创建了一个表，多达200000条数据，然后我们对他进行分区，0-10W一个分区（不包括10W），10W-20W一个分区（不包括20W），20W-max一个分区。当然我们把每个分区都放到了不同的表空间，这些表空间也是需要手动创建的。

create tablespace users01 datafile 'd:/aaa/users01.mdf' size 100M;  --创建表空间的语法

当然我们也可以不指定表空间：

create table tab_range_test(
  range_key_column date not null,
  data varchar2(20)
)
partition range (range_key_column)(
  partition part1 values less than (to_date('01/01/2010','dd/MM/yyyy')),
  partition part2 values less than (to_date('01/01/2011','dd/MM/yyyy')),
  partition part1 values less than (maxvalue)
)

这里就是没有指定表空间，并且用最常见的时间来进行了分区。然后我们进行查询的时候，就可以只在这个分区里面进行查询。查询代码如下：

select * from part_tab partition(part1) where range_key_column>'01-1月-2010';

Tips： 这里要讲一点题外话，其实上面这些东西，非常重要！数据库建模是一个项目最关键的一步。真正在写项目的时候，敲的代码是很少的，我们把数据库建模做好，用框架或者也可以说逆向工程，直接生成对数据库的增删改查代码之后这个项目的雏形就已经有了。一个对数据库建模很熟练的人才是很需要的，所有公司都很需要，敲代码只是很基础的东西，而且会敲代码的人很多，底层到表层的人才到处都是，缺的是中间层的人才。当然，了解ui和ue的人才也很需要。

2.列表分区

该分区的特点是某列的值只有几个，基于这样的特点我们可以采用列表分区。比如，我们按部门分区，开发部一个分区，策划部一个分区，测试部一个分区，这样就方便我们去查找员工了。

语法：

PARTITION BY LIST(COL)(
    PARTITION list_name VALUES  (parameter) 
)

示例：

CREATE TABLE LISTTABLE
(
ID INT PRIMARY KEY,
NAME VARCHAR2(20),
AREA VARCHAR2（10）
）
PARTITION BY LIST(AREA)
(
PARTITION PART1 VALUES(‘BEIJING’) TABLESPACE PART1_TB,
PARTITION PART2 VALUES(‘SHANGHAI’) TABLESPACE PART2_TB
)

上述就是我们创建了一个表，把他按照地区分区的。

3.散列分区

散列分区又称Hash分区，是在列的取值难以确定的情况下采用的一种分区方式。

散列分区通过指定分区编号将数据均匀分布在磁盘设备上，使得这些分区大小一致，这就降低了I/O磁盘争抢的情况。

通常满足以下条件时，建议使用散列分区：

hash分区可以由hash键来分布
DBA无法获知具体的数据值
数据的分布由oracle处理
每个分区有自己的表空间

语法：

create table hash_table(
  col number(8),
  inf varchar2(100)
)
partition by hash(col)(
  partition part01 tablespace hash_ts01,
  partition part02 tablespace hash_ts02,
  partition part03 tablespace hash_ts03,
)

其实就是平均分区啦，减少磁盘竞争，均衡分区，提高性能。

索引分区

在Oracle中，索引分区分为本地索引分区和全局索引分区两种。

全局索引分区不反映基础表的结构，因此只能进行范围分区。

本地索引分区反映基础表的结构，因此对表的分区进行维护时，系统会自动对索引分区进行维护。

1.本地索引分区

本地索引分区就是使用和分区表同样的分区键进行分区的索引，即索引分区采用的列与该表的分区采用的列是相同的。

本地索引分区不需要指定分区范围，因为索引对于表而言是属于本地的。使用本地索引分区的优势有很多，如下所示：

支持分区独立性
只有本地索引能支持单一分区的装入和卸载
本地索引可以单独重建
位图索引仅由本地索引支持

语法：

Create table dept(
Dep_id number(5),
Dep_name varchar2(20)
)
Partition by range(dept_id)(
Partition d_p1 values less than(10) tablespace dp1,
Partition d_p2 values less than(20) tablespace dp2,
Partition d_p3 values less than(MAXVALUE) tablespace dp3
);
Create index dep_index on dept(dep_id) local(
Partition d_p1 tablespace dp1,
Partition d_p2 tablespace dp2,
Partition d_p3 tablespace dp3
）;

创建本地索引分区，就是分好区之后创建索引，然后加上local就是本地了。。。比如这里，在创建部门表的时候，按照部门编号进行分区，并分别指定数据存储的空间，最后在部门编号上添加了本地索引分区。

全局索引分区

全局索引分区就是没有与分区表有相同分区键的分区索引，当分区中出现许多事务并且需要保证所有分区中的数据记录唯一时，采用全局索引分区。需要注意全局索引分区不支持位图索引分区。

语法：

Create index g_index on dept(dep_id) global partitioin by range(dept_id)
(
Partition g1 values less than (100),
Partition g2 values less than (300),
Partition g3 values less than (MAXVALUE)
);

Tips： 注意这里在分区的时候加上了范围，也就是values less than，而本地索引分区是没有加的。

优化SQL语句

对查询进行优化，应尽量避免全盘扫描，首先应考虑在where及order by 涉及的列上建立索引。
应尽量避免在where子句中对字段进行NULL值判断，否则将导致引擎放弃使用索引而进行全盘扫描，如：

select id from t where num is null; 
--可以在null上设置默认值0，确保表中num列没有NULL值，然后用下面的代码查询
select id from t where num=0;

应尽量避免在where子句中使用！=或<>操作符，否则将进行全盘扫描。
应尽量避免在where子句中使用or来连接条件，否则将导致全盘扫描，如：

select id from t where num=10 or num=20;
--尽量避免上面这种，使用下面的查询方法

select id from t where num=10;
union all
select id from t where num=20;

--union all 在这里的意思是记录合并，其实我们这个方法的原理就是，执行了两次查询，然后把两次查询得到的结果合并起来，并且不处理重复项，只合并。

--扩展一个，还有union 这个关键字，这个关键字的意思就比union all 要高级一点了，他的意思是删除掉查询出来重复的记录，并且把合并后的结果排序后再返回，union all 就是直接合并返回。

in 和 not in 也要慎用，否则会导致全盘扫描，如：

select id fron t where num in(1,3);
--对于连续的数值，能用between就不要用in了，如下：

select id from t where num between 1 and 3;

尽量避免在where子句中对字段进行函数操作，这将导致全表扫描。如：

select id from t where substr(name,1,3)='abc'
--上面这句是查找，name以abc为头三位开头的字段。可以使用模糊查询，如下：
select id from t where name like 'abc%'

全模糊查询也将导致全表扫描：

select id from t where name like '%abc%';
--若要提高效率，可以考虑全文检索。

应尽量避免在where子句中对字段进行表达式操作，这将导致全表扫描。如：

select id from t where num/2=100;
--应该为：
select id from t where num=200;

很多时候用exists 代替 in是一个好的选择：

select num from a where num in(select num from b);
--应该为：
select num from a where exists(select 1 from b where b.num = a.num);

索引并不是越多越好，索引固然可以提高相应的select的效率，但同时也降低了insert和update的效率，因为insert和update时有可能会重建索引，一个表的索引最好不要超过6个。一个表的触发器最多12个
尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，因为引擎在处理查询和连接时会逐个比较字符串内的每一个字符，而对于数字型而言只需要比较一次就够了。当然，能用日期的就不要用字符，也很重要。
任何地方都不要使用select * from t，用具体的字段列名代表*，不要返回用不到的任何字段。