2020 Oracle数据库小优化Tips

1.SGA(System Global(内存共享) area)调整

a)Sga由 共享池 和数据缓存区,日志缓冲区组成

b)alter system set sga_target=1024m scope=both; (oracle自动分配sga)

c)查询sga select * from v$sga;
共享池(share pool):用于缓存 最近被执行的sql语句和最近使用的数据定义

1)查询不命中数和共享池缓存的使用率 (共享池活动信息都保存在v$libarycache系统表中)

Select sum(pins) “请求的数量”,sum(reloads) “不命中数” from v$ libarycache
Select sum(sum(pins-reloads))/sum(pins) “命中率” from v$ libarycache

2)命中数过低说明共享池过小 就会消耗cpu资源,过高就消耗内存 一般控制在80-90%

3)调节共享池的语句为:
设置共享池大小(热修改)alter system set shared_pool_size=’80M’
刷新共享池 alter system flush shared_pool

e) 数据缓存区(database buffer cache):用户缓存从文件中检索出来的数据块,可以大大提高查询和更新数据的性能

1)数据缓存区活动信息都保存在 v$sysstat 中
2)该表中name列中值 dbblock gets表示数据请求的总数，consistent gets缓存区满足请求的数据,physical reads 请求在磁盘中读取的数据
3)计算命中率的方法为1-( physical reads/(dbblock gets+consistent gets))
4)查询命中数的sql语句为:
select name,value from v $sysstat where name in(‘db block gets’,‘consistent gets’,‘physical reads’)

2. 建立表分区:

CREATE TABLESPACE data01 DATAFILE ‘C:/DATA01.dbf’ SIZE 500M
CREATE TABLESPACE data02 DATAFILE ‘C:/DATA02.dbf’ SIZE 500M
CREATE TABLESPACE data03 DATAFILE ‘C:/DATA03.dbf’ SIZE 500M

1)范围分区
Create table goods(id number primary key,name varchar(100),price number) partition by range(price)(
partition p_1 values less than(100) tablespace data01,
partition p_2 values less than(1000) tablespace data02,
partition p_3 values less than(maxvalue) tablespace data03
)

2)列表分区
Create table goods(id number primary key,name varchar(100),price number,addr)
partition by list(addr)(
partition p_1 values(‘长沙’) tablespace data01,
partition p_2 values (‘深圳’)tablespace data02,
partition p_3 values (‘武汉’) tablespace data03
)

3)散列分区
值和范围无法确定的情况 由系统决定使用哪个分区
Create table goods(id number primary key,name varchar(100),price number) partition by hash(price)(
partition p_1 tablespace data01,
partition p_2 tablespace data02,
partition p_3 tablespace data03
)

3. 建立索引

Create index I_ID on TB (CName)

4、sql优化

a) 筛选数据先查询较多的数据
b) 不使用*
c) 使用索引提高查询的效率
d) 避免使用or操作 where 子句包含or 操作，执行时不使用索引可以使用in条件来替换
e) 避免使用not where 子句包含not 关键字，执行时该字段的索引失效,使用比较运算符替换not
f) 避免like的特殊形式 查询时，尽可能少使用like
g) 避免having子句 尽可能在where 子句中指定条件

5、程序优化

使用程序的缓存
使用连接池技术