2020 Oracle数据库小优化Tips

阳光穿透心脏的1/2处 2022-11-21 03:49 160阅读 0赞

### Oracle数据库小优化Tips ###

*   *  1.SGA(System Global(内存共享) area)调整
     *  2. 建立表分区:
     *  3. 建立索引
     *  4、sql优化
     *  5、程序优化

## 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、程序优化 ##

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