如何提升Oracle数据库搜索效率

发布网友发布时间：2022-04-29 12:50

我来回答

共2个回答

懂视网时间：2022-04-30 15:33

名称

--------------------------------

NAME 名称

VALUE 值

UNIT 单位

-------------------统计项

select * from v$pgastat

NAME VALUE UNIT

---------------------------------------- ---------- ----------

aggregate PGA target parameter 150994944 bytes

aggregate PGA auto target 93579264 bytes

global memory bound 30198784 bytes

total PGA inuse 47017984 bytes

total PGA allocated 56666112 bytes

maximum PGA allocated 58632192 bytes

total freeable PGA memory 2883584 bytes

process count 23

max processes count 48

PGA memory freed back to OS 5177344 bytes

total PGA used for auto workareas 0 bytes

maximum PGA used for auto workareas 0 bytes

total PGA used for manual workareas 0 bytes

maximum PGA used for manual workareas 0 bytes

over allocation count 0

bytes processed 6438912 bytes

extra bytes read/written 0 bytes

cache hit percentage 100 percent

recompute count (total) 123

对于上面的解释如下

1 aggregate PGA target parameter 150994944 bytes ： pga_aggregate_target
2 aggregate PGA auto target 93579264 bytes : 剩余的能被工作区使用的内存。
3 global memory bound 30198784 bytes :单个SQL最大能用到的内存
4 total PGA inuse 47017984 bytes ：正被耗用的pga(包括workare pl/sql等所有占用的pga)
5 total PGA allocated 56666112 bytes ：当前实例已分配的PGA内存总量。
一般来说，这个值应该小于 PGA_AGGREGATE_TARGET ，
但是如果进程需求的PGA快速增长，它可以在超过PGA_AGGREGATE_TARGET的限定值
6 maximum PGA allocated 58632192 bytes :pga曾经扩张到的最大值
7 total freeable PGA memory 2883584 bytes ：可释放的pga
8 process count 23 :当前process
9 max processes count 48 :最大时候的process
10 PGA memory freed back to OS 5177344 bytes
11 total PGA used for auto workareas 0 bytes :当前auto模式下占用的workara size 大小
12 maximum PGA used for auto workareas 0 bytes :auto模式下占用的workara size最大大小
13 total PGA used for manual workareas 0 bytes ：当前manual模式下占用的workara size 大小
14 maximum PGA used for manual workareas 0 bytes :manual模式下占用的workara size最大大小
15 over allocation count 0 :使用量超过pga大小的次数
16 bytes processed 6438912 bytes :pga使用的字节
17 extra bytes read/written 0 bytes ：向临时段写的字节
18 cache hit percentage 100 percent ：bytes processed/(bytes processed+extra bytes read/written)
19 recompute count (total) 123

global memory bound:一个串行操作能用到的最大内存
=min(5%*pga_aggregate_target,50%*_pga_max_size,_smm_max_size)，
当你修改参数pga_aggregate_target的值时，Oracle系统会根据pga_aggregate_target和_pga_max_size
这两个值来自动修改参数_smm_max_size。具体修改的规则是：
如果_pga_max_size大于5%*pga_aggregate_target，则_smm_max_size为5%*pga_aggregate_target。
如果_pga_max_size小于等于5%*pga_aggregate_target，则_smm_max_size为50%*_pga_max_size。

total PGA in used:当前正在使用的PGA，可以从v$process的pga_used_mem字段中获取
select sum(a.PGA_USED_MEM),sum(a.PGA_ALLOC_MEM),sum(a.PGA_MAX_MEM) from v$process a
v$pgastat 中的 total PGA in used、total PGA allocated、maximum PGA allocated
这3个值差不多

在执行创建索引前，我们还要介绍一个视图v$session_longops视图

[sql] view plaincopy

SQL> desc v$session_longops
名称是否为空? 类型
----------------------------------------- -------- ----------------
SID NUMBER
SERIAL# NUMBER
OPNAME VARCHAR2(64)
TARGET VARCHAR2(64)
TARGET_DESC VARCHAR2(32)
SOFAR NUMBER
TOTALWORK NUMBER
UNITS VARCHAR2(32)
START_TIME DATE
LAST_UPDATE_TIME DATE
TIME_REMAINING NUMBER
ELAPSED_SECONDS NUMBER
CONTEXT NUMBER
MESSAGE VARCHAR2(512)
USERNAME VARCHAR2(30)
SQL_ADDRESS RAW(4)
SQL_HASH_VALUE NUMBER
QCSID NUMBER

其中SID和SERIAL#是与v$session中的匹配的，
OPNAME：指长时间执行的操作名.如：Table Scan
TARGET：被操作的object_name. 如：tableA
TARGET_DESC：描述target的内容
SOFAR：这个是需要着重去关注的，表示已要完成的工作数，如扫描了多少个块。
TOTALWORK：指目标对象一共有多少数量（预计）。如块的数量。
UNITS：
START_TIME：进程的开始时间
LAST_UPDATE_TIM：最后一次调用set_session_longops的时间
TIME_REMAINING：估计还需要多少时间完成，单位为秒
ELAPSED_SECONDS：指从开始操作时间到最后更新时间
CONTEXT：
MESSAGE：对于操作的完整描述，包括进度和操作内容。
USERNAME：与v$session中的一样。
SQL_ADDRESS：关联v$sql
SQL_HASH_VALUE：关联v$sql
QCSID：主要是并行查询一起使用。

下面测试正式开始

1、使用python脚本创建随机数

import random

‘‘‘

Created on 2012-3-26

@author: jscn-xw

‘‘‘

for j in range(1,10):

for i in range(1,10000000):

print random.randint(100000000,999999999),random.randint(100000000,999999999)

2、创建测试表

SQL> create table tbim(id1 number,id2varchar2(12)) nologging;

3、load进入数据

3.1 创建控制文件(tbim.ctl)

load data

--infile ‘/home/oracle/bi_logfile.txt‘

into table tbim

append

fields terminated by ‘ ‘

OPTIONALLY ENCLOSED BY ‘"‘

trailing nullcols

(

id1 ,

id2

)

3.2 sqlldr进入数据库

[oracle@jscns-05CTL]$ sqlldr userid=security/security control=tbim.ctldata=/home/oracle/tbim.bcp

4 测试

SQL> set timing on

SQL> select count(*) from tbim ;

COUNT(*)

----------

400000000

Elapsed: 00:00:06.57

4.1 什么参数都不加测试创建速度

SQL> create index id1_ind on tbim(id1) tablespace imindex;

Index created.

Elapsed: 00:16:23.51

这个时候注意观察临时表空间的变化情况，我们注意临时表空间在不断的增加。还要注意v$session_longops视图的变化。

主要关注SOFAR、TIME_REMAINING、ELAPSED_SECONDS字段的变化和值

4.2 加上nologing参数

SQL> drop index id1_ind;

SQL> create index id1_ind on tbim(id1)tablespace imindex nologging;

Index created.

Elapsed: 00:16:40.20

4.3 加上parallel参数

SQL> drop index id1_ind;

SQL> create index id1_ind on tbim(id1)tablespace imindex nologging parallel 4;

Index created.

Elapsed: 00:09:03.74

感觉parallel不靠谱，而且nologging效果也不是很明显，至少对于oracle11gR2来说。

4.4 调整sort_area_size

SQL> alter session setworkarea_size_policy=manual;

SQL> alter session setsort_area_size=2000000000;

SQL> create index id1_ind on tbim(id1)tablespace imindex nologging parallel 4;

Index created.

Elapsed: 00:08:12.79

这个效果还是比较明显的

4.5 修改其他参数

修改全表扫描时一次读取的block的数量db_file_multiblock_read_count

直接路径IO的大小，10351 event level 128

禁用block checksum/checking

备选的排序算法_newsort_type

SQL> alter session setdb_file_multiblock_read_count=1024;

SQL> alter session set events ‘10351trace name context forever, level 128‘;

SQL> alter session setsort_area_size=2000000000;

SQL> alter session set"_sort_multiblock_read_count"=128;

SQL> alter session enable parallel ddl;

SQL> alter session setdb_block_checking=false;

SQL> alter system setdb_block_checksum=false;

SQL> create index id1_ind on tbim(id1)tablespace imindex nologging parallel 4;

Index created.

Elapsed: 00:07:37.57

5、总结

我可以通过以下手段加快创建索引速度：

1）除此之外，还可以适当的调整并行查询的数量（一般不超过8）；

2）索引和表分离，单独的临时表表空间；

3）把表调整为nologging状态，或者创建索引的时候指定nologging；

4）我们可以适当调整数据库相关参数加快左右创建索引速度，示例如下：

SQL> alter session setdb_file_multiblock_read_count=1024;

SQL> alter session set events ‘10351trace name context forever, level 128‘;

SQL> alter session setsort_area_size=2000000000;

SQL> alter session set"_sort_multiblock_read_count"=128;

SQL> alter session set "_sort_multiblock_read_count"=128;

SQL> alter session enable parallel ddl;

SQL> alter session setdb_block_checking=false;

SQL> alter system setdb_block_checksum=false;

通过以上调整一般可以加快40%以上的创建速度

oracle海量数据中提升创建索引的速度

标签：

热心网友时间：2022-04-30 12:41

需要用索引来解决，索引的创建规则如下：
1、表的
主键
、
外键
必须有索引；
2、数据量超过300的表应该有索引；
3、经常与其他表进行连接的表，在连接字段上应该建立索引；
4、经常出现在Where子句中的字段，特别是大表的字段，应该建立索引；
5、索引应该建在选择性高的字段上；
6、索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引；
7、复合索引的建立需要进行仔细分析；尽量考虑用单字段索引代替：
A、
正确选择
复合索引中的主列字段，一般是选择性较好的字段；
B、复合索引的几个字段是否经常同时以AND方式出现在Where子句中？单字段查询是否极少甚至没有？如果是，则可以建立复合索引；否则考虑单字段索引；
C、如果复合索引中包含的字段经常单独出现在Where子句中，则分解为多个单字段索引；
D、如果复合索引所包含的字段超过3个，那么仔细考虑其必要性，考虑减少复合的字段；
E、如果既有单字段索引，又有这几个字段上的复合索引，一般可以删除复合索引；
8、频繁进行
数据操作
的表，不要建立太多的索引；
9、删除无用的索引，避免对执行计划造成负面影响；
以上是一些普遍的建立索引时的判断依据。一言以蔽之，索引的建立必须慎重，对每个索引的必要性都应该经过仔细分析，要有建立的依据。因为太多的索引与不充分、不正确的索引对性能都毫无益处：在表上建立的每个索引都会增加存储开销，索引对于插入、删除、更新操作也会增加处理上的开销。另外，过多的复合索引，在有单字段索引的情况下，一般都是没有
存在价值
的；相反，还会降低数据增加删除时的性能，特别是对频繁更新的表来说，负面影响更大。