Oracle如何管理带约束的B树索引

管理带约束的B树索引
B树索引与主键和唯一键约束是分不开的。这是因为Oracle使用索引来强制执行主键和唯一键约束。若没有相关联的B树索引,将不能启用主键或唯一约束。

在创建主键或唯一键约束时,你可以选择让Oracle自动创建相应索引。在这种情况下,如果删除或禁用约束,Oracle也将自动删除相应的索引。

单独创建索引和约束也是可以的。当单独创建索引和约束时,允许删了或禁用约束,而不会自动删除相应的索引。如果有大量数据需要处理,你可能希望禁用约束,而不删除相应索引。

因为在定义外键约束时,Oracle不会自动创建索引,所以必须手动在与外键约束相关的列上创建索引。在大多数情况下,在外键列上创建B树索引是有益的,因为它有助于避免锁定问题,并提高通过主键和外键列连接父/子表的查询的性能。

1 在主键列上创建B树索引
主键约束保证在一列(或列的组合)中的值可用于唯一标识表内的记录。每个表只能有一个主键约束。主键约束不能包含空值。主键约束可以被看做是唯一(unique)约束和非空(NOT NULL)约束的结合。为每个表创建主键索引有如下几个很好的理由。
.强制执行了主键列在表内必须是唯一的这一业务需求。是的,在某些情况下,可能有一个不需要主键的表(比如日志表),但在大多数情况下,主键对每个表都是必需的。
.主键中的许多列,在访问应用程序的查询的where子句中被频繁使用。这些列上的索引将会改善查询的性能。
.除非已定义父表主键或唯一键约束,否则Oracle将不允许创建子表的外键约束。因此,如果需要外键约束,就必须使用主键或唯一键约束。

对任何启用的主键,Oracle都需要一个与之对应的索引。有几种技术可用来创建主键约束及其对应的索引。
.首先创建表。然后在单独的alter table语句中添加主键约束。alter table语句同时创建了主键约束和索引。
.在create table 语句中内联(与列一起)或在单独的部分中指定主键约束。
.首先创建表,然后使用create index语句创建包含主键列的索引,最后使用alter table … add constraint语句添加主键约束。

1.1.使用alter table来创建主键约束和索引
下面介绍的这种技术是创建主键约束和相关联的索引最可取的方法。这种方法允许对表的创建与约束和索引的定义分别进行管理。如果你使用的应用程序包含数千个表,约束和索引,那么将创建表与建立相应的约束和索引分开,可以使用管理和诊断安装问题变得更容易。这不是死板的规定,相反,它是从诊断问题演变而来的一种偏好。

在这个例子中,表和主键约束是分别创建的。首先,创建表时没有定义任何约束。

SQL> create table cust2
  2  (
  3  cust_id number,
  4  first_name varchar2(200),
  5  last_name varchar2(200)
  6  ) tablespace reporting_data;

Table created.

然后添加主键约束

SQL> alter table cust2 add constraint cust2_pk primary key(cust_id) using index tablespace reporting_index;

Table altered.

此代码示例使用alter table … add constraint语句同时创建主键约束和唯一索引。约束和索引都被命名为CUSTS_PK。

1.2.使用create table创建主键约束和索引
另一种常见的方法是用create table语句创建主键约束和索引。可以内联(和列一起)直接指定一个约束。这种方法的优点是简单。如果在开发或测试环境中进行试验,这种方法是快速且有效的。但这种方法也有一个缺点,它不允许在多个列上定义主键。例如:

SQL> create table cust3
  2  (
  3  cust_id number primary key,
  4  first_name varchar2(30),
  5  last_name varchar2(30)
  6  )
  7  tablespace reporting_data;

Table created.

SQL> insert into cust3 values(1,'jing','yong');

1 row created.

SQL> commit;

Commit complete.

SQL> select a.segment_name,a.segment_type,a.extents,a.bytes from user_segments a,user_indexes b where a.segment_name=b.index_name and b.table_name in('CUST3');

SEGMENT_NAME                                                                                                                     SEGMENT_TYPE          EXTENTS      BYTES
-------------------------------------------------------------------------------------------------------------------------------- ------------------ ---------- ----------
SYS_C0023486                                                                                                                     INDEX                       1    1048576

在这段代码中,Oracle创建了主键约束和相应的唯一索引。Oracle自动生成像SYS_C0023486这样的随机名称(约束和索引也被赋予了相同的名称)。

如果想要明确地对约束和索引提供名称,就可以执行下面这样的语句:

SQL> create table cust4
  2  (
  3  cust_id number constraint cust4_pk primary key,
  4  first_name varchar2(30),
  5  last_name varchar2(30)
  6  )
  7  tablespace reporting_data;

Table created.

SQL> insert into cust4 values(1,'jing','yong');

1 row created.

SQL> commit;

Commit complete.

SQL> select a.segment_name,a.segment_type,a.extents,a.bytes from user_segments a,user_indexes b where a.segment_name=b.index_name and b.table_name in('CUST4');

SEGMENT_NAME                                                                                                                     SEGMENT_TYPE          EXTENTS      BYTES
-------------------------------------------------------------------------------------------------------------------------------- ------------------ ---------- ----------
CUST4_PK                                                                                                                         INDEX                       1    1048576

也可以指定放置索引的表空间,如下所示:

SQL> create table cust5
  2  (
  3  cust_id number constraint cust5_pk primary key using index tablespace reporting_index,
  4  first_name varchar2(30),
  5  last_name varchar2(30)
  6  )
  7  tablespace reporting_data;

Table created.

还可以在create table语句单独的部分中定义主键约束(不和列在一起)。下面是在单独的部分定义主键约束的例子:

SQL> create table cust6
  2  (
  3  cust_id number,
  4  first_name varchar2(30),
  5  last_name varchar2(30),
  6  constraint cust6_pk primary key(cust_id) using index tablespace reporting_index
  7  )
  8  tablespace reporting_data;

Table created.

这种技术被称为外联(out-of-line),因为约束的声明与列定义之间是用逗号隔开的。该做法与内联的方法相比,具有可以为主键指定多列的优势。

1.3.分别创建B-tree索引和主键约束
还可以首先创建索引,然后改变表以应用主键约束。为了保持这个例子的完整性,这里也显示了create table语句。

SQL> create table cust7
  2  (
  3  cust_id number,
  4  first_name varchar2(30),
  5  last_name varchar2(30)
  6  )
  7  tablespace reporting_data;

Table created.

SQL> create unique index cust7_pk on cust7(cust_id) tablespace reporting_index;

Index created.

SQL> alter table cust7 add constraint cust7_pk primary key(cust_id);

Table altered.

这种方法的优点是,可以独立于索引删除或禁用主键约束。在大型数据库环境中,出于数据加载时性能方面的原因,有时可能想要删除或禁用约束。有时可能需要能删除约束,但不删除索引的灵活性。在大型数据库环境中,重建索引会花费很长的时间并消耗大量的系统资源。

另一种稍微有些牵强的情况是,有可能创建一个与主键约束定义的列不同的列的索引。例如:

SQL> create table cust8
  2  (
  3  cust_id number,
  4  first_name varchar2(30),
  5  last_name varchar2(30)
  6  )
  7  tablespace reporting_data;

Table created.

SQL> create index cust8_pk on cust8(cust_id,first_name,last_name) tablespace reporting_index;

Index created.

SQL> alter table cust8 add constraint cust8_pk primary key(cust_id);

Table altered.

建议不要创建与约束列不同的列的主键索引,但这么做是可以的。你应该知道有这种情况,避免在诊断问题时感到困惑。

1.4.查看主键约束和索引的详细信息
用如下语句可以确认某个索引的详细信息:

SQL> select index_name,index_type,uniqueness from user_indexes where table_name='CUST7';

INDEX_NAME                                                                                                                       INDEX_TYPE                  UNIQUENES
-------------------------------------------------------------------------------------------------------------------------------- --------------------------- ---------
CUST7_PK                                                                                                                         NORMAL                      UNIQUE

输出如上所示,为了验证约束的信息,可执行如下查询:

SQL> select constraint_name,constraint_type from user_constraints where table_name='CUST7';

CONSTRAINT_NAME                                                                                                                  CONSTRAINT_TYPE
-------------------------------------------------------------------------------------------------------------------------------- ------------------------------
CUST7_PK                                                                                                                         P

1.5.删除主键约束和索引
创建主键约束时自动创建的索引不能直接删除。在这种情况下,如果像下面这样直接删除索引:

SQL> drop index cust4_pk;

就会收到如下错误信息:

drop index cust4_pk
           *
ERROR at line 1:
ORA-02429: cannot drop index used for enforcement of unique/primary key

要删除索引,必须首先删除或禁用主键约束。例如,下面的语句将删除创建约束时自动创建的索引:

SQL> alter table cust4 disable constraint cust4_pk;

Table altered.
SQL> alter table cust4 drop constraint cust4_pk;

Table altered.

或者

SQL> alter table cust4 drop primary key;

Table altered.

在删除或禁用主键约束时,可以选择不删除相关索引。可以使用drop/disable constraint子句的keep index子句来保留索引。例如:

SQL> alter table cust4 drop constraint cust4_pk keep index;

Table altered.

此代码指示Oracle删除约束,但保留索引。如果要处理的是很大的表,那么出于载入或操纵数据时性能方面的原因,可能要禁用或删除约束,而非索引,因为删除与一个大表相关联的索引后,可能需要相当长的时间和大量资源来重新创建它。

要注意的另一个方面是,如果主键或唯一键被已启用的外键引用,而试图删除父表上的约束,如下所示:

SQL> alter table cust1 drop primary key;

就会收到如下错误信息:

alter table cust1 drop primary key
*
ERROR at line 1:
ORA-02273: this unique/primary key is referenced by some foreign keys

在这种情况下,需要先删除或禁用引用的外键,或使用cascade子句在主键约束已经被删除或禁用时,自动删除外键约束。例如:

SQL>alter table cust1 drop constraint cust1_pk cascade;
SQL>alter table cust1 disable constraint cust1_pk cascade;
SQL>alter table cust1 drop primary key cascade;

级联删除约束只删除了所有依赖外键的约束,但不从子表中删除任何数据。

2 在唯一键列上创建B树索引
唯一键约束的主要目的是强制地不属于主键一部分的列的唯一性。如果有非主键列在一个表中是唯一的这种业务需求,那么应该使用唯一约束。例如,你可能有定义在客户表的cust_id列上的主键,但可能还需要一个last_name和first_name列的组合上的唯一键约束。

唯一键与主键有两方面的差别。首先,唯一键可以包含NULL值,其次每个表可以定义多个唯一键(而每个表只能定义一个主键)。

如果需要为某个列创建唯一约束,那么可以通过下列几种不同的方式来实现这一需求。
.使用alter table语句来创建一个唯一约束。这将自动创建一个唯一的B树索引。
.使用create table语句来创建一个唯一约束。这也将自动创建一个唯一的B树索引。
.分别创建B树索引和约束。如果想要在禁用或删除约束时分别管理索引和约束,那么可以使用这种方法。
.只创建唯一B树索引,而不费心去创建唯一键约束。如果索引中的列不能被子表的外键引用,那么可以使用这种方法。

2.1.使用alter table来创建唯一约束和索引
这种方法是我们启用唯一键约束并创建相应索引的首选方法。正如在主键约束和索引部分中提到的,将创建表的语句与创建约束和索引的语句分离,往往更容易诊断安装问题。

下面的示例演示如何创建一个表,然后在非主键列上添加一个唯一键约束。

SQL> create table cust9
  2  (
  3  cust_id number,
  4  first_name varchar2(30),
  5  last_name varchar2(30)
  6  )
  7  tablespace reporting_data;

Table created.

接下来,使用alter table语句在cust表的last_name和first_name列的组合上创建一个名为cust9_ux1的唯一约束。

SQL> alter table cust9 add constraint cust9_uk1 unique(last_name,first_name) using index tablespace reporting_index;

Table altered.

此语句创建了该唯一约束。此外,Oracle会自动创建一个具有相同名称的相关索引。

2.2.使用create table创建唯一约束和索引
使用create table方法的优点在于,它简单且可以把索引和约束的创建封装在一个语句中。在用create table语句定义唯一约束时,它可以用内网联方式,也可以用外联方式来定义。

第一个例子显示了如何在一个列上内联地创建唯一键约束和索引。由于内联唯一键约束只可以定义在列上,所以我们添加了SSN列,它以内联内式定义了唯一键约束。

SQL> create table cust10
  2  (
  3  cust_id number constraint cust10_pk primary key using index tablespace reporting_index,
  4  first_name varchar2(30),
  5  last_name varchar2(30),
  6  ssn varchar2(15) constraint cust10_uk1 unique using index tablespace reporting_index
  7  )
  8  tablespace reporting_data;

Table created.

下一个例子使用外联技术在first_name和last_name列的组合上创建了一个唯一约束:

SQL> create table cust11
  2  (
  3  cust_id number constraint cust11_pk primary key using index tablespace reporting_index,
  4  first_name varchar2(30),
  5  last_name varchar2(30),
  6  ssn varchar2(15),
  7  constraint cust11_uk1 unique(first_name,last_name) using index tablespace reporting_index
  8  )
  9  tablespace reporting_data;

Table created.

外联方式的定义具有允许在多列上创建一个唯一键约束的优势。

2.3.分别创建B树索引和唯一键约束
如果需要分别管理索引和约束,那么可以先创建索引,然后再创建约束。例如:

SQL> create table cust12
  2  (
  3  cust_id number,
  4  first_name varchar2(30),
  5  last_name varchar2(30)
  6  )
  7  tablespace reporting_data;

Table created.

SQL> create unique index cust12_uk1 on cust12(first_name,last_name) tablespace reporting_index;

Index created.

SQL> alter table cust12 add constraint cust12_uk1 unique(first_name,last_name);

Table altered.

分别创建索引和约束的好处是,可以在不删除底层索引的情况下删了或禁用约束。在处理大量数据时,可考虑使用这种方法。如果你有任何理由需要禁用约束,然后重新启用它,就可以这样做而不删除索引(因为重新创建大索引可能需要很长一段时间)。

2.4.只创建唯一索引
还可以只创建唯一索引而不添加唯一约束。如果你从来没有计划用外键引用一个唯一键,那么只创建一个唯一索引而不定义唯一约束也是可以的。下面是创建一个无关联约束的唯一索引的例子:

SQL> create table cust13
  2  (
  3  cust_id number,
  4  first_name varchar2(30),
  5  last_name varchar2(30)
  6  )
  7  tablespace reporting_data;

Table created.

SQL> create unique index cust13_uk1 on cust13(first_name,last_name) tablespace reporting_index;

Index created.

在用上述语句明确地创建唯一索引时,Oracle虽然创建了一个唯一索引,但却没有在dba/all/user_constraints中为约束添加条目。为什么这很重要呢?,考虑这种情况:

SQL> insert into cust13 values(1,'JAMES','STARK');

1 row created.

SQL> insert into cust13 values(2,'JAMES','STARK');

下面是被抛出的相应错误消息提示:

insert into cust13 values(2,'JAMES','STARK')
*
ERROR at line 1:
ORA-00001: unique constraint (JY.CUST13_UK1) violated

如果让你来诊断这个问题的话,你首先会检查的地方是dba_constraints,你会按错误消息中显示的名称在其中查找一个约束。然而,没有查到任何信息。

SQL> select constraint_name from dba_constraints where constraint_name='CUST13_UK1';

no rows selected

没有相关记录可能会使用人困惑:向表中插入数据时,抛出的错误消息已经表明违反了唯一约束,但在与约束相关的数据字典视图中却没有它的信息。在这种情况下,必须在dba_indexes中查看已经创建的唯一索引的详细信息。例如:

SQL> select index_name,uniqueness from dba_indexes where index_name='CUST13_UK1';

INDEX_NAME                                         UNIQUENES
-------------------------------------------------- ---------
CUST13_UK1                                         UNIQUE

如果你希望能够使用与约束相关的数据字典视图来报告唯一键约束,就应该也定义一个约束。

2.5.删除唯一键约束和索引
如果索引是创建唯一键约束时自动创建的,那么不能直接删除该索引。在这种情况下,必须删除或禁用唯一键约束,而相关的索引会自动被删除。例如:

SQL> drop index cust11_uk1;
drop index cust11_uk1
           *
ERROR at line 1:
ORA-02429: cannot drop index used for enforcement of unique/primary key

SQL>alter table cust11 drop constraint cust11_uk1;

这行语句同时删除约束和索引。如果想保留索引,那么可以指定keep index子句。

SQL>alter table cust drop constraint cust11_uk1 keep index;

如果分别创建索引和唯一键约束,或者如果没有唯一键约束与唯一索引相关联,那么可以直接删除该索引。

3 索引外键列
外键约束确保插入数据到子表时,相应的父表记录存在。这是一个保证数据符合父/子业务关系规则的机制。外键也被称为参照完整性约束。

不同于主键和唯一键约束,Oracle不会自动创建外键列上的索引。因此,必须在定义为外键约束的列的基础上手动创建一个外键索引。在大多数情况下,应该在与外键关联的列上创建索引。这里有两个原因。.Oracle经常可以利用外键列上的索引,来改善使用外键列来连接父表和子表的查询性能。.如果外键列上没有B树索引存在,在往子表插入数据或从子表删除数据时,它会锁定父表中的所有行。对于频繁修改父表和子表的应用程序,这将导致锁定和死锁问题。

首先讨论如何在一个外键列上创建B树索引,然后再介绍用来检测未被索引的外键列的一些技巧。

3.1.在外键列上实现索引
假设有这样的需求:必须为address1表的每条记录分配cust14表中存在的一个相应cust_id列。为了强制执行这种关系,在address1表上创建如下外键约束:

SQL> create table cust14(
  2  cust_id number,
  3  last_name varchar2(30),
  4  first_name varchar2(30)
  5  )
  6  tablespace reporting_data;

Table created.



SQL> create table address1(
  2  address_id number,
  3  cust_id number,
  4  street varchar2(30),
  5  city varchar2(30),
  6  state varchar2(30)
  7  )
  8  tablespace reporting_data;

Table created.


SQL> alter table address1 add constraint addr1_fk1 foreign key(cust_id) references cust14(cust_id);
alter table address1 add constraint addr1_fk1 foreign key(cust_id) references cust14(cust_id)
                                                                                     *
ERROR at line 1:
ORA-02270: no matching unique or primary key for this column-list

注意,外键列必须引用父表中定义了主键或唯一键约束的列。否则,就会收到错误提示信息”ORA-02270: 此列列表无匹配的唯一键或主键”。

因为在连接cust14表和address1表时,外键列被广泛使用,且外键列上的索引会提高性能,所以在这种情况下,必须手动创建一个索引。例如,在address1表的cust_id外键列上创建普通的B树索引。

SQL>create index addr1_fk1 on address1(cust_id);

索引名不必与外键名称相同。是否这样做,只是一种个人喜好。我们觉得约束和相应的索引具有相同的名称时,维护环境更容易。

创建索引时,如果不指定表空间名称,Oracle会在用户的默认表空间放置索引。一般情况下,最好明确指定该表的索引的存放位置。例如:

create index addr1_fk1 on address1(cust_id) tablespace reporting_index;

注意,外键列上的索引,并不一定是B树类型的。在数据仓库环境中,经常在星型模式的事实表的外键列上使用位图索引。与B树索引不同,外键列上的位图索引不能解决父/子表锁定的问题。使用星型模式的应用程序通常不从事实表删除或修改子记录,因此在数据仓库环境中,在外键列上使用位图索引,锁定不是什么问题。

3.2.确定外键列是否已经被索引
如果你从头开始创建一个应用程序,那么创建程序代码,并确保每一个外键约束都有相应的索引很容易。但是,如果你继承了一个现成的数据库,就需要审慎地检查外键列是否已经被索引。

你可以使用数据字典视图来验证,外键约束的所有列上是否有相应的索引。其基本思路是检查每个外键约束,看它是否有一个相应的索引,这个任务并不像一开始看上去那么简单。用下面的查询作为例子,它可以用来指导你按正确的途径入手:

SQL> col owner for a30
SQL> col cons_name for a30
SQL> col tab_name for a30
SQL> col cons_column for a30
SQL> col ind_column for a30
SQL> select distinct
  2  a.owner owner,
  3  a.constraint_name cons_name,
  4  a.table_name tab_name,
  5  b.column_name cons_column,
  6  nvl(c.column_name,'***Check index***') ind_column
  7  from dba_constraints a,dba_cons_columns b,dba_ind_columns c
  8  where a.constraint_type='R'
  9  and a.owner=UpPER('&&user_name')
 10  and a.owner=b.owner
 11  and a.constraint_name=b.constraint_name
 12  and b.column_name=c.column_name(+)
 13  and b.table_name=c.table_name(+)
 14  and b.position=c.column_position(+)
 15  order by tab_name,ind_column;
old   9: and a.owner=UpPER('&&user_name')
new   9: and a.owner=UpPER('JY')

OWNER                          CONS_NAME                      TAB_NAME                       CONS_COLUMN                    IND_COLUMN
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------
JY                             ADDR_FK1                       ADDRESS                        CUST_ID                        CUST_ID

虽然此查询简单并易于理解,但它并不能在所有情况下都正确地报告出未索引的外键。例如,在多列外键的情况下,以不同于索引列的顺序定义约束也不要紧,只要索引列在该索引中位于前面即可。换句话说,如果约束被定义为col1和col2,那么定义一个先是col2,然后是col1的B树索引也没关系。

另一方面,使用B树索引有助于避免锁定的问题,但位图索引却做不到这点。在这种情况下,查询还应该检查索引类型。

在这些情况下,需要用更复杂的查询来检测与外键列相关的索引问题。下面的例子是一个更复杂的查询,它使用listagg分析函数来比较外键约束列(作为字符串返回一行)与相应的索引列:

这个查询会先提示你输入一个模式名称,然后将显示没有相应的索引的外键约束。此查询还检查了索引类型,位图索引可以在外键列上存在,但它不能防止锁定问题。

SQL> select
  2   case when ind.index_name is not null then
  3     case when ind.index_type in('BITMAP') then
  4        '** Bitmp idx **'
  5     else
  6        'indexed'
  7     end
  8   else
  9     '** Check idx **'
 10   end checker,
 11   ind.index_type,
 12   cons.owner,cons.table_name,ind.index_name,cons.constraint_name,cons.cols
 13   from( select
 14         c.owner,c.table_name,c.constraint_name,
 15         listagg(cc.column_name,',') within group (order by cc.column_name) cols
 16         from dba_constraints c,dba_cons_columns cc
 17         where c.owner=cc.owner
 18         and c.owner=UPPER('&&schema')
 19         and c.constraint_name=cc.constraint_name
 20         and c.constraint_type='R'
 21         group by c.owner,c.table_name,c.constraint_name) cons
 22  left outer join
 23  (select
 24    table_owner,table_name,index_name,index_type,cbr,listagg(column_name,',') within group (order by column_name) cols
 25    from(select
 26         ic.table_owner,ic.table_name,ic.index_name,ic.column_name,ic.column_position,i.index_type,
 27         connect_by_root(ic.column_name) cbr
 28         from dba_ind_columns ic,dba_indexes i
 29         where ic.table_owner=UPPER('&&schema')
 30         and ic.table_owner=i.table_owner
 31         and ic.table_name=i.table_name
 32         and ic.index_name=i.index_name
 33         connect by prior ic.column_position-1=ic.column_position
 34         and prior ic.index_name=ic.index_name)
 35  group by table_owner,table_name,index_name,index_type,cbr) ind
 36  on cons.cols=ind.cols
 37  and cons.table_name=ind.table_name
 38  and cons.owner=ind.table_owner
 39  order by checker,cons.owner,cons.table_name;
Enter value for schema: JY
old  18:        and c.owner=UPPER('&&schema')
new  18:        and c.owner=UPPER('JY')
old  29:        where ic.table_owner=UPPER('&&schema')
new  29:        where ic.table_owner=UPPER('JY')


CHECKER                        INDEX_TYPE                     OWNER                          TABLE_NAME                     INDEX_NAME                     CONSTRAINT_NAME                COLS
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------
** Check idx **                                               JY                             ADDRESS                                                       ADDR_FK1                       CUST_ID

表锁和外键
下面用一个简单的例子来演示外键列没有索引时的锁定问题。

首先创建两个表(dept和emp)并用一个外键约束把它们关联起来。

SQL> create table emp(emp_id number primary key,dept_id number);

Table created.

SQL> create table dept(dept_id number primary key);

Table created.

SQL> alter table emp add constraint emp_fk1 foreign key(dept_id) references dept(dept_id);

Table altered.

插入数据

SQL> insert into dept values(10);

1 row created.

SQL> insert into dept values(20);

1 row created.

SQL> insert into dept values(30);

1 row created.

SQL> insert into emp values(1,10);

1 row created.

SQL> insert into emp values(2,20);

1 row created.

SQL> insert into emp values(3,30);

1 row created.

SQL> commit;

Commit complete.

打开两个终端会话。在一个会话中,从子表删除一条记录,但不提交。

SQL> delete from emp where dept_id=10;

1 row deleted.

现在尝试(在另一个会话里)从父表中删除一些不受子表删除操作影响的数据。

SQL> delete from dept where dept_id=30;

对父表数据的删除操作会挂起,直到子表的事务初步提交(或回滚)。如果子表中的外键列上没有常规的B树索引,那么任何时间尝试往子表插入数据或删除子表的数据时,它都会在父表上放置一个全表锁,在子表的事务完成前,该全表锁会一直阻止删除或更新父表的数据。

回滚删除子表数据的操作

SQL> rollback;

Rollback complete.

当回滚删除子表数据的操作后,删除父表数据的操作报错,因为违反完整性约束

SQL> delete from dept where dept_id=30;
delete from dept where dept_id=30
*
ERROR at line 1:
ORA-02292: integrity constraint (JY.EMP_FK1) violated - child record found

现在额外创建一个子表外键列上的索引,并再次运行前面的操作。

SQL> create index emp_fk1 on emp(dept_id);

Index created.

再次执行删除操作,删除子表的数据

SQL> delete from emp where dept_id=10;

1 row deleted.

在另一个会话中删除父表数据不会挂起会立即报违反完整性约束

SQL> delete from dept where dept_id=30;
delete from dept where dept_id=30
*
ERROR at line 1:
ORA-02292: integrity constraint (JY.EMP_FK1) violated - child record found

这样就能够独立地运行前面列出的两个delete语句。当外键列上有B树索引时,如果从子表中删除数据,Oracle将不会过分地锁定父表中的所有行数据。

Oracle如何实现B树索引

实现B树索引
这里将介绍使用B树索引时会遇到的典型任务。典型的任务包括。
.创建索引
.报告索引
.显示重新创建索引需要的代码
.删除索引

1 创建B树索引
下面给出的是一个示例脚本,它创建一个表,并在单独的表空间创建与之相关的索引。表和索引从表空间继承存储属性,这是因为在create table或create index语句中没有指定存储参数。此外,你希望主键和唯一键约束自动创建B树索引。

SQL> create table cust1(
  2  cust_id number,
  3  last_name varchar2(30),
  4  first_name varchar2(30)
  5  )
  6  tablespace reporting_data;

Table created.

SQL> alter table cust1 add constraint cust_pk primary key(cust_id) using index tablespace reporting_index;

Table altered.

SQL> alter table cust1 add constraint cust_uk1 unique(last_name,first_name) using index tablespace reporting_index;

Table altered.

SQL> create table address(
  2  address_id number,
  3  cust_id number,
  4  street varchar2(30),
  5  city varchar2(30),
  6  state varchar2(30)
  7  )
  8  tablespace reporting_data;

Table created.

SQL> alter table address add constraint addr_fk1 foreign key(cust_id) references cust1(cust_id);

Table altered.

SQL> create index addr_fk1 on address(cust_id) tablespace reporting_index;

Index created.

此脚本创建了两个表。父表是cust1,它的主键是cust_id。子表是address,它的主键是address_id。在address表中,cust_id列作为外键存在,它映射到cust1表的cust_id列。

此脚本也创建了三个B树索引。其中第一个是创建主键约束时自动创建的。第二个索引是创建唯一约束时自动创建的。第三个索引是明确创建在address表中的cust_id外键列上的。所有这三个索引都是在reporting_index表空间中创建的,而表是在reporting_data表空间中创建的。

2 报告索引
上面的例子中创建的索引的详细信息可以通过查询数据字典来验证。

SQL> select index_name,index_type,table_name,tablespace_name,status from user_indexes where table_name in('CUST1','ADDRESS');

INDEX_NAME                                         INDEX_TYPE                  TABLE_NAME                                         TABLESPACE_NAME                                    STATUS
-------------------------------------------------- --------------------------- -------------------------------------------------- -------------------------------------------------- --------
ADDR_FK1                                           NORMAL                      ADDRESS                                            REPORTING_INDEX                                    VALID
CUST_PK                                            NORMAL                      CUST1                                              REPORTING_INDEX                                    VALID
CUST_UK1                                           NORMAL                      CUST1                                              REPORTING_INDEX                                    VALID

运行以下查询来验证创建了索引的列:

SQL> select index_name,column_name,column_position from user_ind_columns where table_name in('CUST1','ADDRESS') order by index_name,column_position;

INDEX_NAME                                         COLUMN_NAME                    COLUMN_POSITION
-------------------------------------------------- ------------------------------ ---------------
ADDR_FK1                                           CUST_ID                                      1
CUST_PK                                            CUST_ID                                      1
CUST_UK1                                           LAST_NAME                                    1
CUST_UK1                                           FIRST_NAME                                   2

要显示区的数目和已使用的空间,可以运行以下查询:

SQL> select a.segment_name,a.segment_type,a.extents,a.bytes from user_segments a,user_indexes b where a.segment_name=b.index_name and b.table_name in('CUST1','ADDRESS');

no rows selected

请注意,这个例子的输出结果显示,没有为索引分配段,区或空间。

从Oracle 11g第2版开始,在创建表时,如果还没有往表中插入数据,相关的段(和区)将会初步推迟创建。这意味着直到数据行被插入到相关的表之后,才会为相关的索引创建段。为了说明这一点,给CUST1表插入一行,也给ADDRESS表插入一行,如下所示:

SQL> insert into cust1 values(1,'STARK','JIM');

1 row created.

SQL> insert into address values(100,1,'Vacuum Ave','Portland','OR');

1 row created.

SQL> commit;

Commit complete.

重新运行这个查询(段的使用报告)产生的输出如下:

SQL> select a.segment_name,a.segment_type,a.extents,a.bytes from user_segments a,user_indexes b where a.segment_name=b.index_name and b.table_name in('CUST1','ADDRESS');

SEGMENT_NAME                                                                                                                     SEGMENT_TYPE          EXTENTS      BYTES
-------------------------------------------------------------------------------------------------------------------------------- ------------------ ---------- ----------
ADDR_FK1                                                                                                                         INDEX                       1     131072
CUST_PK                                                                                                                          INDEX                       1     131072
CUST_UK1                                                                                                                         INDEX                       1     131072

3 显示创建索引的代码
有时候可能需要删除一些索引。这些索引可能是由过时的应用程序建立的,也可能是你自己以前建立的,但已经用不到了。在删除索引之前,建议你首先生成重新创建索引所需的数据定义语言(DDL)。如果删除索引对性能有不利影响而需要重新创建它,就可以重新创建索引(就像没有删除它一样)。

可以使用dbms_metadata.get_ddl函数来显示对象的DDL。确保为LONG变量设置适当的值,使用返回的CLOB值能全部显示出来。例如:

SQL> set long 1000000
SQL> select dbms_metadata.get_ddl('INDEX','ADDR_FK1') from dual;

下面是输出结果:

DBMS_METADATA.GET_DDL('INDEX','ADDR_FK1')
--------------------------------------------------------------------------------

  CREATE INDEX "JY"."ADDR_FK1" ON "JY"."ADDRESS" ("CUST_ID")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
  STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "REPORTING_INDEX"

这段代码显示了重新创建索引需要的所有内容。这些代码中的许多值反映了从索引表空间继承的默认设置或存储参数。

如果想要显示当前连接的用户的所有索引元数据,可以运行下面的代码:

SQL> select dbms_metadata.get_ddl('INDEX',index_name) from user_indexes;

DBMS_METADATA.GET_DDL('INDEX',INDEX_NAME)
--------------------------------------------------------------------------------

  CREATE INDEX "JY"."CUST_IDX1" ON "JY"."CUST" ("LAST_NAME")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"


  CREATE INDEX "JY"."CUST_IDX2" ON "JY"."CUST" ("FIRST_NAME")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"


  CREATE UNIQUE INDEX "JY"."CUST_PK" ON "JY"."CUST1" ("CUST_ID")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
  STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)


  CREATE UNIQUE INDEX "JY"."CUST_UK1" ON "JY"."CUST1" ("LAST_NAME", "FIRST_NAME")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
  STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "REPORTING_INDEX"


  CREATE INDEX "JY"."ADDR_FK1" ON "JY"."ADDRESS" ("CUST_ID")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
  STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "REPORTING_INDEX"

如果当前连接的用户有很多索引,这个查询将会产生大量的输出。

4 删除B树索引
如果确定不再使用某个索引了,那么应该删除它。在删除索引之前,应采取必要的预防措施,以确保不会对性能产生不利影响。如果可能的话,最好的办法是在与生产环境同等条件(在硬件,数据,负载等方面)的测试环境中删除索引,确定对性能的不利影响。如果不可能进行彻底的测试,那么在删除前考虑先做以下工作。
.启用对索引的监测。
.使用索引不可见。
.使用索引不可用。

这样做是为了在实际删除之前,先确定该索引没有用于任何目的。监控索引会让你了解应用程序的select语句是否使用了它。但索引监控不会告诉你该索引是否被用于其他内部用途,如用来强制执行某个约束或防止锁定问题。

使用一个索引不可见需要Oracle 11g及以上版本。不可见索引仍然由Oracle维护,但查询优化器确定执行计划时不考虑它。请注意,不可见的索引仍然可以由Oracle在内部使用,用来避免锁定问题或强制执行约束。所以,使用索引不可见并不是用来确定该索引是否被使用的完全可靠的方法。

下面是使用索引 不可见的一个例子:

SQL> alter index addr_fk1 invisible;

Index altered.

此代码使用索引对查询优化器不可见,因此,它不能在查询中用来检索行。然而,当修改表中的记录时,该索引结构仍然由Oracle维护。如果确定该索引对性能非常关键,那么可以通过如下命令很容易地使用它再次对优化器可见。

SQL> alter index addr_fk1 visible;

Index altered.

删除索引之前的另一种选择是使其不可用。

SQL> alter index addr_fk1 unusable;

Index altered.

此代码使得索引不可用,但不会删除它。不可用表示,不但优化器不会使用索引,而且当DML语句操作它的表时,Oracle也不会维护该索引。此外,不可用的索引不能在内部使用,用于强制执行约束或避免锁定问题。

如果需要重新启用不可用的索引,那么就必须重建它。而重建一个大型的索引,会消耗大量的时间和资源。

SQL> alter index addr_fk1 rebuild;

Index altered.

当确信不需要某个索引后,就可以使用drop index语句来删除它。这个语句将永久删除该索引,找回该索引的唯一办法是重新创建它。

SQL> drop index addr_fk1;

Index dropped.

Oracle如何创建B树索引

创建B树索引之前,为了慎重起起见,有必要从架构层面考虑一些将影响可维护性和可用性的问题。以下是建立索引之前,应该考虑的架构性问题。
.在创建索引之前,首先对它的大小进行估计。
.考虑指定表索引的表空间(与表分离)。这使得分开管理表和索引变得更轻松,如备份和恢复任务。
.允许对象从它闪的表空间继承存储参数。
.定义创建索引时要使用的命令标准。

1 在创建索引前估计索引的大小
一张大表上创建索引之前,可能需要估计它将会占用的空间大小。预测索引大小最好的方法是在测试环境中创建它,测试环境中有生产环境的典型数据集。如果不能建立生产数据的完整副本,那么经常可以用数据的一个子集来推断在生产中所需索引空间的大小。如果你没有使用削减的生产数据的奢侈条件,还可以使用dbms_space.create_index_cost存储过程来估算索引的大小。例如,如下代码估算了在cust表的first_name列上创建索引的大小:

SQL> set serverout on
SQL> exec dbms_stats.gather_table_stats(user,'CUST');

PL/SQL procedure successfully completed.

SQL> variable used_bytes number
SQL> variable alloc_bytes number
SQL> exec dbms_space.create_index_cost('create index cust_idx2 on cust(first_name)',:used_bytes,:alloc_bytes);

PL/SQL procedure successfully completed.

下面是这个例子的一些示例输出:

SQL> print :used_bytes

USED_BYTES
----------
      7490

SQL> print :alloc_bytes

ALLOC_BYTES
-----------
      65536

used_bytes变量给出了索引数据需要多少空间的估计。alloc_bytes变量提供了将在表空间内分配多大空间的估计。

下一步,创建索引。

SQL> create index cust_idx2 on cust(first_name);

Index created.

用如下查询显示所占用的空间的实际数额:

SQL> select bytes from user_segments where segment_name='CUST_IDX2';

     BYTES
----------
     65536

输出显示空间分配字节数的估计量等于实际使用量。

根据记录数,列数,数据类型和统计数据的准确性,输出的结果可能会有所不同。除初始大小之外,还要牢记随着记录插入到表中,该索引将增大。必须对索引占用的空间进行监控,并确保有足够的磁盘空间,以适应未来的增长需求。

2 为索引创建单独的表空间
对于关键的应用程序,必须提前考虑表和索引会消耗多少空间,以及它们增长的速度有多快。空间消耗和对象的增长对数据库可用性有直接影响。如果空间用尽了,那么数据库将变得不可用。最好的管理办法是,针对空间要求创建表空间,并在创建对象时明确指定表空间名。考虑到这一点,我们建议将表和索引分别保存到单独的表空间。考虑以下原因。
.支持采用不同的备份和恢复要求。你可能希望灵活地用与备份表不同的频率来备份索引。或者可以选择不备份索引,因为你知道可以重新创建它们。
.如果让表或索引从表空间继承它的存储特性,使用单独的表空间可以为表空间内创建的对象量身定制存储属性。表和索引往往有不同的存储要求(如区的大小,记录等)。
.运行维护报告时,如果报告针对不同的表空间具有不同的节(section),有时管理表和索引会更容易。

如果这些原因出现在你的环境中,那么可能值得付出额外的努力,对表和索引采用不同的表空间。如果你没有前面提到的任何需要,那么把表和索引保存在相同的表空间是不错的选择。

DBA经常出于性能的原因,考虑把索引放置在单独的表空间。如果你有从头开始建立存储系统的奢侈条件,可以把挂载点(mount point)设置为有自己的磁盘和控制器,那么可能会看到把表和索引存储在不同表空间的一些IO上的好处。如今,存储管理员往往会分配给你的SAN中的一大片存储,并且无法保证数据和索引将存储在单独的磁盘和控制器上。因此,把表和索引存储在不同表空间的做法,通常对提高性能没什么帮助。换句话说,性能获得提高不是通过将表和索引存储到不同的表空间实现的,而是由于在所有可用的设备上均匀地分布IO实现的。

下面的代码显示的是为表和索引单独建立表空间的例子。它使用固定大小的区和自动段空间管理(ASSM)创建了本地管理的表空间。

SQL> create tablespace reporting_data datafile '+DATA/JYCS/reporting_data01.dbf' size 1G extent management local uniform size 1M
  2  segment space management auto;

Tablespace created.


SQL> create tablespace reporting_index datafile '+DATA/JYCS/reporting_index01.dbf' size 500M extent management local uniform size 128K
  2  segment space management auto nologging;

Tablespace created.

我们更倾向于使用统一大小的区,因为这确保了表空间内存的所有区大小相同,从而减少了对象创建和删除时的碎片。ASSM的功能允许Oracle自动管理存储属性,而以前这需要手动监测和由DBA维护。

3 从表空间继承存储参数
创建表或索引时,有几个与表空间相关的技术细节需要注意。例如,如果创建表和索引时不指定存储参数,则表和索引会继承表空间的存储参数。这是在大多数情况下所需的行为。这样就可以不必手动指定这些参数。如果需要创建一个具有与表空间不同的存储参数的对象,那么用create table/index语句来实现。

此外,请记住,如果不明确指定表侬间,默认情况下,表和索引创建在用户的默认表空间中。在开发和测试环境中,这是可以接受的。对于生产环境,则应该考虑在create table/index语句中明确命令表空间。

4 命令标准
在创建和管理索引时,制定一些命名标准是非常可取的。考虑以下因素.
.当错误消息中包含表示表,索引类型等的信息时,简化了对问题的诊断。
.显示索引信息的报告更容易被分组,因此更具可读性并更容易地发现其中的规律和问题。鉴于这些需求,这里有一些示例索引命名指南。
.主键索引名称应该包含表名和一个后缀,如_UKN,其中N是一个数字。
.外键列上的索引应包含外键表和一个后缀,如_FKN,其中N是一个数字。
.对于不用于约束的索引,使用表名和一个后缀,如_IDXN,其中N是一个数字。
.基于函数的索引的名称应包含表名和一个后缀,如_FCN,其中N是一个数字。

一些厂商在命名索引时使用前缀。例如,主键索引将被命名为PK_CUST(而不是CUST_PK)。所有这些不同的命名标准都是有效的。

Oracle中的B树索引

B树索引是Oracle的默认的索引类型。因为表中的行标识符(rowid)和相关的列值存储在一个平衡的树状结构的索引块中,所以该索引类型被称为B树索引。使用Oracle的B树索引有以下几个原因.
.提高SQL语句的性能
.强制执行主键和唯一键约束的唯一性
.减少通过主键和外键约束关联的父表和子表间潜在的锁定问题

Oracle如何使用B树索引
为了充分理解B树索引的内部实现,以便在建立数据库应用程序时能做出明智的索引决定。将举例说明,首先创建测试表cust

SQL> create table cust(
  2  cust_id number,
  3  last_name varchar2(30),
  4  first_name varchar2(30));

Table created.

在last_name列上创建B树索引

SQL> create index cust_idx1 on cust(last_name);

Index created.

向表cust中插入数据

SQL> insert into cust(cust_id,last_name,first_name)
  2  select rownum rn ,a.last_name,a.first_name from hr.employees a
  3  union
  4  select rownum+(107*1) rn ,a.last_name,a.first_name from hr.employees a
  5  union
  6  select rownum+(107*2) rn ,a.last_name,a.first_name from hr.employees a
  7  union
  8  select rownum+(107*4) rn ,a.last_name,a.first_name from hr.employees a
  9  union
 10  select rownum+(107*5) rn ,a.last_name,a.first_name from hr.employees a
 11  union
 12  select rownum+(107*6) rn ,a.last_name,a.first_name from hr.employees a
 13  union
 14  select rownum+(107*7) rn ,a.last_name,a.first_name from hr.employees a
 15  union
 16  select rownum+(107*8) rn ,a.last_name,a.first_name from hr.employees a
 17  union
 18  select rownum+(107*9) rn ,a.last_name,a.first_name from hr.employees a
 19  union
 20  select rownum+(107*10) rn ,a.last_name,a.first_name from hr.employees a;

1070 rows created.

SQL> commit;

Commit complete.

SQL> select distinct last_name,first_name from cust where rownum<11;

LAST_NAME                      FIRST_NAME
------------------------------ ------------------------------
Austin                         David
Banda                          Amit
Atkinson                       Mozhe
Bissot                         Laura
Ande                           Sundar
Bates                          Elizabeth
Bell                           Sarah
Bernstein                      David
Baer                           Hermann
Baida                          Shelli

10 rows selected.

插入数据后,确保该表的统计信息是最新的,以便为查询优化器提供足够的信息,从而做出如何检索数据的更好决定,执行如下命令收集表的统计信息:

SQL> exec dbms_stats.gather_table_stats(ownname=>'JY',tabname=>'CUST',cascade=>true);

PL/SQL procedure successfully completed.

不建议使用analyze语句(带compute和estimate子句)来收集统计信息。提供此功能只是为了向后兼容。

当向表中插入数据时,Oracle将分配由物理数据库块组成的区。Oracle还将为索引分配数据块。对于每个插入到表中的记录,Oracle还将创建一个包含rowid和列值的索引条目(本例中是rowid和last_name列的值)。每个索引项的rowid指向存储该表的列值的数据文件和数据块号。

当从一个表及其对应的索引选择数据时,存在三种情况。
.SQL查询所需的所有表的数据都在索引结构中。因此,只需要访问索引块。不需要从表中读取数据块。
.查询所需的所有信息没有都包含在索引块中。因此,查询优化器选择既访问索引块也要访问表块来检索需要的数据,以满足查询条件。
.查询优化器选择不访问索引。因此只访问表块。

场景1.所有的数据位于索引块中
这里将介绍两种情况。在每种情况下,执行查询需要的所有数据,包括返回给用户的数据,以及在where子句中被评估的数据,都位于该索引中。
.索引范围扫描(index range scan):如果优化器确定它使用索引结构检索查询所需的多个行时是有效的,那么就使用这种扫描。索引范围扫描被广泛用于各种各样的情况。

.索引快速全扫描(index fast full scan):如果优化器确定表中的大部分行需要进行检索,那么就使用这种扫描。但所有需要的信息都存储在索引中。由于索引结构通常比表结构小,优化器确定全索引扫描(比全表扫描)更高效。这种情况对统计(count)值的查询是很常见的。

首先演示的是索引范围扫描。在这种情况下,运行下面的查询:

select last_name from cust where last_name='Austin';

为了在该查询中返回数据,Oracle最小需要读取多少块,也就是说为了满足此查询,访问物理块最有效的方式是什么,优化器可以选择读取表结构的每个块。然而,这会导致很大的IO开销,因此,它不是检索数据的最优化方法。

对于这个例子,检索数据最有效的方法是使用索引结构。要返回包含last_name列中值为Austin的行,Oracle将需要读取3个索引块。通过使用Oracle的autotrace(自动跟踪)实用程序,可以确认。

SQL> set autotrace on
SQL> select last_name from cust where last_name='Austin';

LAST_NAME
------------------------------
Austin
Austin
Austin
Austin
Austin
Austin
Austin
Austin
Austin
Austin

10 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3472749082

------------------------------------------------------------------------------
| Id  | Operation        | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |           |    10 |    80 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| CUST_IDX1 |    10 |    80 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("LAST_NAME"='Austin')

此输出显示,Oracle只需要使用cust_idx1索引来检索数据,以满足查询的结果集。不需要访问表中的数据块,只需要访问索引块。这对于给定的查询,这是特别高效的索引策略。当索引包含查询所需的所有列值时,它被称为覆盖索引。

下面列出为这个例子使用自动跟踪所显示的统计信息:

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        653  bytes sent via SQL*Net to client
        624  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         10  rows processed

一致获取(consistent gets)的值表示有三个读内存操作。数据库块获取(db block gets)加一致获取等于总的内存读取操作。由于索引块已经在内存中,因此返回此查询的结果集不需要物理读取。此外,有10行进行了处理,这与cust表中last_name为Austin的记录数相符。

下面显示导致执行索引快速全扫描的一个例子。

select count(last_name) from cust;

使用set autotrace on生成执行计划。下面是相应的输出:

SQL> select count(last_name) from cust;

COUNT(LAST_NAME)
----------------
            1070


Execution Plan
----------------------------------------------------------
Plan hash value: 2246355899

-----------------------------------------------------------------------------------
| Id  | Operation             | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |           |     1 |     8 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE       |           |     1 |     8 |            |          |
|   2 |   INDEX FAST FULL SCAN| CUST_IDX1 |  1070 |  8560 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

此输出显示,确定表内的计数只用到了索引结构。在这种情况下,优化器确定采取索引快速全扫描比全表扫描更高效。

Statistics
----------------------------------------------------------
         48  recursive calls
          0  db block gets
         91  consistent gets
          0  physical reads
          0  redo size
        559  bytes sent via SQL*Net to client
        624  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
          1  rows processed


场景2.索引中不包含所有信息
考虑这样一种情况:假设需要从cust表获得更多信息。首先,回顾一下前面的查询语句,并且还要在查询结果中返回first_name列。现在,要获得新增的数据元素,就需要访问表本身。下面是新的查询语句:

select last_name,first_name from cust where last_name='Austin';

使用set autotrace on,并执行前面的查询语句:

SQL> alter system flush buffer_cache;

System altered.

SQL> select last_name,first_name from cust where last_name='Austin';

LAST_NAME                      FIRST_NAME
------------------------------ ------------------------------
Austin                         David
Austin                         David
Austin                         David
Austin                         David
Austin                         David
Austin                         David
Austin                         David
Austin                         David
Austin                         David
Austin                         David

10 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2100940648

-------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |           |    10 |   150 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| CUST      |    10 |   150 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | CUST_IDX1 |    10 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("LAST_NAME"='Austin')

此输出信息指示,cust_idx1索引是通过一次索引范围扫描(index range scan)访问的。索引范围扫描标识出满足此查询结果所需的索引块。此外,表是过table access by index rowid batched来读取的。通过索引的rowid访问表,表示Oracle利用存储在索引中的rowid找到表块包含的相应行。把rowid映射到相应的表块,这些块中含有last_name值为Austin的数据。由于我们清空了buffer cache了,这样查询共执行了6次物理读取,9次内存读取。

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          9  consistent gets
          6  physical reads
          0  redo size
        896  bytes sent via SQL*Net to client
        624  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         10  rows processed

场景3.只有表块被访问
在某些情况下,即使有索引存在,Oracle也会确定只使用表块比通过索引访问更为有效。当Oracle检查表内的每一行时,这被称为全表扫描。
例如,执行此查询:

SQL> select * from cust;

下面是相应的执行计划和统计信息:

Execution Plan
----------------------------------------------------------
Plan hash value: 260468903

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  1070 | 19260 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| CUST |  1070 | 19260 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------


Statistics
----------------------------------------------------------
        204  recursive calls
          9  db block gets
        389  consistent gets
         20  physical reads
       1080  redo size
      38869  bytes sent via SQL*Net to client
       1405  bytes received via SQL*Net from client
         73  SQL*Net roundtrips to/from client
         33  sorts (memory)
          0  sorts (disk)
       1070  rows processed

此输出显示,需要一致读取(consistent gets)389个块以及数据库块获取(db block gets)9个块和物理读取20个块。Oracle检索表中的每一行以返回满足查询所需的结果。在这种情况下,必须读取表中已使用的所有块,Oracle无法使用索引来加快数据检索。

Oracle Shared Pool Memory Management

Oracle在管理共享池内存方面面临着难以置信的挑战。多年来的所有改变、bug、补丁和各种性能问题都说明了这一点。虽然这可能会引起一些同情,但当面对与内存管理相关的棘手问题时,同情很快就会转化为愤怒。在本节中,我将解释如何管理共享池内存、多年来的管理进展、如何分配和释放内存、如何处理可能出现的4031错误,以及最后如何解决共享池锁存器争用。

From Hashing to Subpools
在Oracle 7和Oracle 8i中,共享池管理是在一种有趣的哈希结构帮助下执行的。如果还记得我们关于cache buffer 哈希链与library cache哈希链,那么这将非常有意义,但这里存在一种苦恼。当一个进程需要共享池中的内存时,它产生的哈希和链与所请求的内存大小相关。链也通常被称作heap,它是可用内存块链表。因此,从概念上讲,前几个链与大约1KB的内存块相关,后几个链与大约2KB的内存块相关,以此类推。虽然这确实很巧妙,但是经过一段时间对大小不一致的内存进行分配和释放之后,链实际上可以变成几千个节点长。请记住,哈希缓冲区链的大小平均在0到1之间。所以一个由几千个节点组成的链是巨大的。更糟的是,只有一个共享池latch锁来覆盖所有哈希链!清洗共享池帮助很大,因为链将减少到一个可观的规模。但这无法操作大型生产数据库,因此Oracle不得不进行更改。

Oracle9i引入了子池,这自然会导致多个共享池锁存器。基于哈希的策略被多个子池替换,每个子池包含一个在标准LRU策略上操作的堆。Oracle也开始标准化内存需求大小,这增加了找到可接受内存大小块的可能性。子池、多个共享池锁latch和LRU策略极大地减少了共享池内存管理问题。如果您同时管理过Oracle8i和Oracle9i系统,您可能会经历这种变化,并注意到有很大的不同。

数据库系统中共享池子池的数量可以通过查看实例参数_kghdsidx_count或通过计算x$kghlu视图中的行数来判断。

下面的查询显示了与共享池子池相关的一系列SQL语句。在这个例子中,一个大小为800MB的共享池存在三个子池。x$ksmss查询对于每个子池返回一行记录并且如果存在java pool还会另外加一行记录。设置子池数量的实例参数_kghdsidx_count不能被动态修改。如果你想影响Oracle调用一个子池号发生改变,你必须设置实例参数并回收实例。

SQL> @spspinfo
SQL> select sum(bytes/1024/1024) sp_size
2 from v$sgastat
3 where pool='shared pool';
SP Size (MB)
------------
         800
SQL> select count(*) no_sp from x$kghlu;
Num of SPs
----------
         4
SQL> select INST_ID, KSMDSIDX, KSMSSLEN
2 from x$ksmss
3 where ksmssnam='free memory';
INST_ID    KSMDSIDX   KSMSSLEN
---------- ---------- -----------
1          0          301989888
1          1          18818468
1          2          12659340
1          3          7697300
1          4          20482152
SQL> select i.ksppinm param,v.ksppstvl value
2 from x$ksppi i, x$ksppcv v
3 where v.indx=i.indx
4 and v.inst_id=i.inst_id
5 and i.ksppinm='_kghdsidx_count';
PARAM                VALUE
-------------------- -----
_kghdsidx_count      4

Oracle对子池的数量设置了严格的限制。在Oracle 11g中,可以使用7个共享池子池来启动实例,但有8个子池,该实例没有启动——实际上,在重新启动之前需要关闭实例。

有趣的是,Oracle不必遵从子池号的意愿。实际上,在一个类似于上面查询结果的Oracle数据库11.1g的示例中,实例参数被设置为2,实例重新启动,但是Oracle创建了三个子池。在Oracle数据库11.2g中,实例参数再次被设置为2,实例重新启动,并且按照指定的Oracle创建了两个子池。在没有手动设置实例参数的情况下运行Oracle数据库11.1g和11.2g, Oracle只创建了一个子池。因此,尽管你可以影响甲骨文,它仍然保留做出改变的权利。

内存分配与回收
内存分配是相当简单的。它遵循标准的LRU算法并与pinning与locking一起使用。当一个Oracle进程(服务器或后台进程)请求内存时,Oracle内核中的一部分称作为heap manager(堆管理器)的会被执行。虽然细节不断变化,但概念算法基本相同。

Oracle进程需要特定数量的内存,这些内存被转换为多个特定大小内存块的请求。堆管理器搜索与每个请求匹配的单个大小的内存块。多个内存块(认为是非连续的)是不行的。如果进程请求4KB内存,堆管理器必须从共享池内存中返回4KB内存块的地址。

在Oracle9i中,Oracle进程获得子池latch,并将在放弃之前搜索子池至多5次。因为内存的情况可能会发生急剧且快速的变化,允许多次传递会增加找到内存的可能性。然而在五次搜索之后,当持有各自共享池latch时,如果合适的大小的内存块没有找到,Oracle将会放弃,并posts错误代码4031,“out of mmemory”信息,并且会话将会停止处理。对于每个Oracle DBA来说,这在生产系统中将是不可接受的。

在Oracle Database 10g中,Oracle进程对内存的要求更加强烈。如果在五次搜索之后在当前共享池中没有找到合适的内存,进程将移动到另一个子池。这一过程将继续进行到所有定义的子池被搜索完为止。如果在这时,没有找到合适的内存,就像以前一样,Oracle将会放弃并posts 4031错误并且停止处理。Oracle在这个版本中所做的是消耗更多CPU和更长时间地持有共享池latch来减少返回错误消息的机会。从数据库操作的角度来看,性能较慢总比没有性能好。在我们解决性能问题时,至少可以执行工作。

当内存不足时,Oracle将回收不被频繁访问的内存块。可能在尝试检索SQL语句的文本时遇到过这种情况,并且它不再缓存在共享池中。幸运地是,Oracle不会回收内存供其它对象使用。例如,如果一个游标被pinned(固定)了,Oracle将不会回收相关的内存,不管该内存是不是被频繁访问。事实上,即使清除共享池也不会删除被固定的游标。如果真的想清空共享池并且想从头开始,可以重启实例。

共享池latch竞争识别与解决
共享池latch被用来序列化共享池内存的管理。这意味着像搜索内存,LRU活动,分配内存与回收内存请求共享池latch这样的操作。因为从Oracle 9i开始存在多个子池,并且每个子池有它自己的共享池latch,只要使用这个版本或之后的版本就可以大大减少共享池lat这个特别的解决方案非常简洁,因为它只需要很少的工作,而且我们不是在玩弄Oracle的共享池LRU算法。但是,请记住,更多的子池可能需要更多的共享池内存,需要重新启动实例才能使实例参数更改生效,Oracle保留不尊重您的建议的权利ch竞争的可能性。但有些时候这些仍然不够。下面有些方案可能减少latch获取时间latch持有时间或者两者都减少。

固定大且频繁使用的对象
此策略用来确保对象成功进入缓存,不用管内存活动或对象大小。任何包第一次被调用时,整个包被加载到内存中。操作中在激活共享池后如果需要触发,将强制执行大量的内存管理活动,这将导致对象不能被加载而触发4031错误。即使如果对象被成功加载,用户可能会注意到应用程序的延迟。

有些时候可能想要固定小对象。例如,假设一个对象有一种高强度活动模式,长时间的暂停导致对象的内存被释放,然后是另一段高强度活动。为了确保没有应用程序延迟且为了减少内存管理,我们可以简单固定对象。

大多数大型Oracle应用程序都提供一个脚本,其中包含要固定在共享池中的对象,并且它们将建议在实例启动后立即运行该脚本。重要的是要知道,即使您的应用程序供应商提供了这样一个列表,您也可以通过了解您的组织实际使用对象的方式来细化这个列表。供应商应用程序开发人员通常会创建固定列表。然而大多数应用程序开发商认为他们的对象是最重要的并且应该总是被固定。但实际上,很多时候,在应用程序在生产环境中运行之前,没有人真正知道您的组织将如何使用它。因此如果出现4031错误,这对于修改固定列表来说是一个好消息。

想要确保对象总是固定在共享池中有四个简单步骤要操作。关键词pin常被使用,dbms_shared_pool包的keep函数被用来确保对象保留在共享池中。缺省情况下当创建数据库时这个包不会被加载,因此第一步就是要加载它。下面的代码就是用来创建这个过程。

[oracle@jytest1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon May 6 14:30:28 2019

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> @$ORACLE_HOME/rdbms/admin/dbmspool.sql

Session altered.


Package created.


Grant succeeded.


Session altered.

下一步骤是找到大的或频繁的对象。Oracle保持对共享池对象使用进行跟踪并且可以通过v$db_object_cache视图来查看这些信息。下面是使用OSM脚本dboc.sql来识别潜在的对象。您可能会看到一组比其他包大得多的包,以及执行得比其他包频繁得多的包。还可能有一些对象,您个人知道它们具有不同寻常的执行配置文件,而您希望缓存它们。

一旦有了要保存的对象列表,下一步就是确定如何将它们放入缓存中。keep函数用于固定对象,或者更好地说,用于将对象保存在共享池中。

SQL> @dboc 10 20
old   9: where  a.sharable_mem >= &min_size
new   9: where  a.sharable_mem >= 20
old  10:   and  a.executions >= &min_exec
new  10:   and  a.executions >= 10

DB/Inst: jy/jy2                                                   07-May 08:26am
Report:   dboc.sql             OSM by OraPub, Inc.                Page         1
                      Oracle Database Object Cache Summary

                                                 Obj          Exe  Size
Owner        Obj Name                            Type Loads   (k)  (KB) Kept?
------------ ----------------------------------- ---- ----- ----- ----- -----
SYS          DBMS_STATS_INTERNAL                 PBDY     0    32   492 NO
SYS          PLITBLM                             PKG      0     8     8 NO
SYS          DBMS_ASSERT                         PBDY     0     6    16 NO
SYS          STANDARD                            PBDY     0     3    32 NO
SYS          DBMS_STATS_INTERNAL                 PKG      0     1   122 NO
SYS          DBMS_SQLDIAG                        PBDY     0     1    40 NO
SYS          DBMS_SQLTUNE_UTIL0                  PBDY     0     1    16 NO
SYS          DBMS_AUTO_TASK                      PBDY     0     0    24 NO
SYS          DBMS_AUTO_TASK                      PKG      0     0    28 NO
SYS          DBMS_STANDARD                       PKG      0     0    44 NO
SYS          DBMS_ADVISOR                        PBDY     0     0    69 NO
SYS          DBMS_SQLTUNE_UTIL2                  PBDY     0     0    20 NO
SYS          DBMS_UTILITY                        PKG      0     0    12 NO
SYS          PRVT_ADVISOR                        PBDY     0     0   176 NO
SYS          DBMS_SQLTUNE_UTIL1                  PBDY     0     0    57 NO
SYS          DBMS_STATS_ADVISOR                  PBDY     0     0   167 NO
SYS          DBMS_SYS_ERROR                      PBDY     0     0     8 NO
SYS          DBMS_OUTPUT                         PBDY     0     0    12 NO
SYS          DBMS_UTILITY                        PBDY     0     0    57 NO
SYS          DBMS_PDB                            PBDY     0     0    12 NO
SYS          DBMS_STATS_ADVISOR                  PKG      0     0    24 NO
SYS          DBMS_SQLTUNE_INTERNAL               PBDY     0     0   532 NO

22 rows selected.


SQL> l
  1  select a.owner ownerx,
  2         a.name  namex,
  3         decode(a.type,'PACKAGE','PKG','PACKAGE BODY','PBDY','FUNCTION','FNC','PROCEDURE','PRC') typex,
  4         a.loads/1000 loadsx,
  5         a.executions/1000 execsx,
  6         a.sharable_mem/1024 sizex,
  7         a.kept keptx
  8  from   v$db_object_cache a
  9  where  a.sharable_mem >= &min_size
 10    and  a.executions >= &min_exec
 11    and  a.type in ('PACKAGE','PACKAGE BODY','FUNCTION','PROCEDURE')
 12* order by executions desc, sharable_mem desc, name

为了将一个游标保存在共享池中,从v$sql,v$sqlarea或者v$open_cursor中收集它的地址与哈希值。下面的代码显示地址(6877c238)和哈希值(1356456286)在它们之间使用逗号进行连接作为一个参数输入,第二个参数是C,因为我们要保存一个游标。对于保存触发器参数为T,对于序列,使用Q,对于包,过程与函数,参数为P。

SQL> exec dbms_shared_pool.keep('6877C238,1356456286','C');
PL/SQL procedure successfully completed.

上面的代码片段可以用于编程结构,但是大多数人发现下面的选项最容易使用。下面的代码用来保存jy方案中的TuoMi过程。

SQL> exec dbms_shared_pool.keep('jy.TuoMi');

PL/SQL procedure successfully completed.

最后,在发出上述代码片段之后,您可以轻松地进行检查,以确保确实保存了对象。从下面的输出结果可以看到jy.TUOMI过程对象的Kept列被设置为YES。

SQL> @dboc 0 0
old   9: where  a.sharable_mem >= &min_size
new   9: where  a.sharable_mem >= 0
old  10:   and  a.executions >= &min_exec
new  10:   and  a.executions >= 0

DB/Inst: jy/jy2                                                   07-May 08:48am
Report:   dboc.sql             OSM by OraPub, Inc.                Page         1
                      Oracle Database Object Cache Summary

                                                 Obj          Exe  Size
Owner        Obj Name                            Type Loads   (k)  (KB) Kept?
------------ ----------------------------------- ---- ----- ----- ----- -----
SYS          DBMS_STATS_INTERNAL                 PBDY     0    32   492 NO
SYS          PLITBLM                             PKG      0     8     8 NO
SYS          DBMS_ASSERT                         PBDY     0     6    16 NO
..............
SYS          DBMS_SQLTUNE_INTERNAL               PKG      0     0    71 NO
JY           TUOMI                               PRC      0     0    36 YES
SYS          DBMS_SMB_INTERNAL                   PBDY     0     0    32 NO
SYS          DBMS_SQLTUNE                        PKG      0     0    32 NO
..............
99 rows selected.

经常有人问我,多长时间修改一次固定列表。就我个人而言,除非有很好的理由,否则我不喜欢调用任何数据库更改。改进固定列表的一个很好的理由是,如果系统突然开始出现共享池latch争用,或者遇到4031个错误。这一点非常重要:如果添加了应用程序功能、发生了应用程序升级或应用程序使用发生了显著变化,则从更主动的角度细化固定列表。

清空共享池
虽然不在任何列表的最上面,但是只要刷新共享池就可以立即缓解共享池latch争用。对于oracle9i之前的系统尤其如此,那时还不存在子池。这很明显不是一个最优解决方案,因为每个对象都没有固定在共享池中将被删除并且它们的内存会被回收。初始结果可能会适得其反,因为它可能会导致立即进行大量的硬解析,正如我们所知,这会消耗大量CPU资源,并强制执行非自然数量的锁。然而,这种不幸的情况很快就会平息下来。

有时,共享池大小的组合,数据库版本(Oracle 9i)与应用程序的使用将使DBA别无选择,只能计划定期共享池刷新。这就是现实情况。

如下面的代码片段所示,刷新共享池非常简单,但效果确实显著

SQL> alter system flush shared_pool;

System altered.

增加子池数量
最简单、最强大和最合适的共享池latch解决方案之一是简单地添加子池,增加子池也将增加共享池latch。前面的“从哈希到子池”小节详细介绍了这个过程。这个特别的解决方案非常简洁,因为它只需要很少的工作,而且我们不是在玩弄Oracle的共享池LRU算法。但是,请记住,更多的子池可能需要更多的共享池内存,需要重新启动实例才能使实例参数更改生效,Oracle保留不尊重您的建议的权利。

减少共享池大小
这听起来可能很奇怪,在子池存在之前,增加共享池大小最终可能导致共享池latch争用。每一种算法的性能都是有限的,都是针对特定情况而设计的。当情况发生变化时,算法可能无法按预期执行。不要忘记,增加缓存来支持更多的活动几乎总是需要更多的CPU资源来管理。因此,可能会有一个收益递减点。Oracle最初的共享池内存管理算法在大约600MB的共享池中运行得相当好,但是当它达到750MB左右时,dba开始看到大量的共享池latch争用是非常常见的。一旦引入了子池,特别是与我概述的其他解决方案相结合,共享池latch争用就可以成功地解决。

4031错误解决方案
Oracle在决定什么时候放弃,什么时候继续使用CPU和保持latches之间有一个微妙的平衡。多年来,Oracle耗尽共享池内存的可能性已经降低,但是4031错误的几率仍然高度依赖于Oracle共享池内存的数量和应用程序。下面是一个实际的4031错误消息

ORA-04031: unable to allocate 4192 bytes of shared memory ("shared
pool","SELECT * F...","sql area (6,0)","kafco :
qkacol"):4031:375:2008:ocicon.c

上面的信息显示,4KB内存正尝试在子池6中进行分配,但是由于某些原因,不能完成分配。幸运地是有一些方法来减少收到4031错误的机会。

清空共享池
与解决共享池latch争用一样,4031错误的一个解决方案也是清空共享池。虽然没有DBA愿意承认定期清空共享池,但这仍然有效。根据Oracle版本、分配的共享池内存的数量以及应用程序独特的内存使用模式,这可能是您的最佳选择。对于oracle 9i之前的系统尤其如此。

增加共享池大小
从概念上讲,增加共享池内存为Oracle提供了更大的灵活性来满足内存请求。然而,除了好处之外,在转移计算资源时也总是有成本的。在大多数情况下,收益实际上大于成本,因此,如果操作系统有可用内存,或者可以将内存从其他Oracle缓存转移到共享池,增加共享池内存很可能减少4031个错误。

请记住,每当您要求Oracle管理更多内存时,都需要更多的CPU来管理这些内存。在oracle9i之前的系统中尤其如此,因为可能存在非常长的内存链堆。如果链有数千个块长,而4031个错误可能会消失,那么在试图获取共享池latch和扫描长链时,这种情况可能会表现为严重的共享池latch争用和大量CPU消耗——所以要小心。

如Oracle文档所述,如果您通过自动内存管理获得解放,您可能需要设置最小的共享池大小。在增加缓冲区缓存的过程中,Oracle会自动减少共享池的大小,以至于开始出现4031个错误。

增加共享池保留大小
当一个较大的包最初被加载到一个已经非常活跃的共享池中时,就会出现一个常见的内存分配挑战。共享池越活跃,特别是当它很小并且对象大小非常不同时,就越有可能找不到所需的内存。

假设我们的服务器进程需要内存来存储一个大游标。当Oracle搜索共享池内存时,如果对象大小大于阈值,Oracle首先搜索保留区域。如果在保留区域中没有找到内存,Oracle将到非保留区域搜索。这种策略有助于将较小的对象排除在保留区域之外,从而将其保留给较大的对象。

有三个实例参数可以组合使用:
.shared_pool_reserved_size被用来直接设置共享池保留大小以字节为单位
.隐藏参数_shared_pool_reserved_pct,它的缺省值为5(5%),可以被用来代替shared_pool_reserved_size。
.一个相对大的对象是由实例参数_shared_pool_reserved_min_alloc来定义的,它的缺省值为4400字节。有趣的是缺省值4400字节仅仅比常见的单个块请求4096字节大。因此,在默认情况下,Oracle表示任何大于一个典型大小的内存块请求都被认为是大的,因此应该从保留的大小中获得内存。

前两个参数中的任何一个都可以用来为相对较大的对象设置共享池保留内存大小。如果您设置其中一个参数,Oracle将计算另一个参数。通过仔细调整这些参数,性能分析人员可以增加进程找到大量内存的可能性,同时仍然维护大量内存给相对较小对象使用。虽然这些参数通常不会调整,但如果发生4031错误,它们的小心调整可能会修复问题。

最小化游标固定时间
当执行游标时,游标也被固定。毕竟,您不希望SQL语句在执行期间突然消失!这是好消息。潜在的坏消息是,当执行完成时,固定游标被释放。如果没有其他进程固定游标,Oracle可以随意销毁,即释放关联的内存。现在假设有人想重新执行游标。如果它已被释放,将执行硬解析,因为整个游标将被重建!每个应用程序使用模式都是独特的;因此,当与更小的共享池或许多独特的SQL语句(或者两者都有)结合使用时,内存管理和库缓存活动可能会变得异常紧张。减少硬解析的一种方法是固定游标,使它们不能被释放。

Oracle提供了一个特殊的实例参数,该参数将保持所有会话的所有游标固定到关闭游标为止。但是,这种好处是以增加共享池内存消耗为代价的,因此,增加了接收4031错误的可能性。Oracle非常清楚这一点,所以为了鼓励回收释放内存并降低发生4031个错误的可能性,cursor_space_for_time实例参数默认设置为false。

如果系统正经历4031错误,你应该要检查cursor_space_for_time参数值。如果你的系统在过去某个时间点已经经历了严重的共享池latch急用,那么可以理解有人将cursor_space_for_time设置为true了。虽然你可能不会决定设置cursor_space_for_time参数为false。但这是一个有效选项应该被考虑。

减小保留对象的内存消耗
如果有太多对象通过执行dbms_shared_pool.keep过程被强制保留在共享池中,它们可能会消耗大量的内存Oracle可能无法成功地管理剩下的内存。此外,如果没有将大型对象保存在共享池中,则实例已经运行了一段时间,然后引用该对象,当强制加载该对象时,内存可能不可用。关键是不要随意地将对象保存在共享池中。

升级数据库版本到10gr2
当然,4031个错误不是升级的惟一原因,而是从Oracle数据库10gr2开始将内存标准化为4KB块。虽然我永远不会仅仅因为这个改进就建议升级到这个版本,但是这可能是升级的一部分原因。

就像对段区大小进行标准化一样,拥有标准的内存块大小可以提高快速找到合适内存的可能性。可以找到的内存越快,消耗的CPU周期就越少,必须持有共享池latch的时间就越短,存在大量浪费的小内存块的可能性就越小(增加4031错误的可能性)。

Proudly powered by WordPress | Indrajeet by Sus Hill.