oracle-dba-ORACLE实例优化

￡神魔★判官ぃ 2022-07-16 16:12 241阅读 0赞

ORACLE实例优化  
SGA与实例优化  
查询重做日志缓冲区大小  
Show parameter log\_buffer;  
查看sga\_max\_size  
Show parameter sga\_max\_size;  
查看SGA信息,total system global area与sga\_max\_size值一致  
Show sga;  
修改SGA最大大小,静态参数，设置当前内存一半即可  
Alter system set sga\_max\_size=700m scope=spfile;  
查看和sga相关的静态参数  
Show parameter sga;  
 Lock\_sga:将sga锁定在物理内存内，这样sga不会使用虚拟内存，可提高数据读取速度  
Alter system set lock\_sga=true scope=spfile;  
 Pre\_page\_sga:启动数据库实例时，将整个sga读入物理内存，提要效率  
Alter system set pre\_page\_sga=true scope=spfile;  
 Sga\_target:决定是否使用sga自动管理，默认值与系统的sga\_max\_size一样大，可动态修改  
Alter system set sga\_target=700M;  
Sga可以自动调整的内存，共享池，java池，大池，数据库缓冲区，流池  
查看自动调整的内存组件信息,开启自动后，值自动设为0  
Select name,value,isdefault from v$parameter where name in(‘shared\_pool\_size’,’large\_pool\_size’,’java\_pool\_size’);  
也可以手动修改大小,不影响oracle自动调整  
Alter system set java\_pool\_size=10M;  
Show parameter java\_pool\_size;

将程序常驻内存  
 创建软件包DBMS\_SHARED\_POOL，不是默认安装，需要执行Sql脚本。  
KEEP：实现将程序常驻内存（共享池）  
UNKEEP：将程序清除出内存。  
执行失败，因为没有安装包  
Execute dbms\_shared\_pool.keep(‘HR.SECURE\_DML’);  
 安装包,非dba创建会失败,没权限  
Sql>@G:\\oracleDataBase\\app\\Administrator\\product\\11.2.0\\dbhome\_1\\RDBMS\\ADMIN\\dbmspool.sql;  
 查看用户拥有的存储过程  
Select object\_name,object\_type from dba\_objects where object\_type=’PROCEDURE’ and owner =’HR’;  
 查看存储过程内容  
Select line,text from dba\_source where name=’存储过程名’;  
 将过程常驻内存  
Execute dbms\_shared\_pool.keep(‘用户名.存储过程名’);  
 查看数据库对象在缓存中的信息  
Select name,namespace,sharable\_mem,executions,kept from v$db\_object\_cache where owner =’用户名’;  
KEP:yes说明用户的数据库对象(存储过程)保存在共享池中。  
 将存储过程清除出常驻内存,kept值为no。如果没有将程序存入常驻内存则不会显示。  
Execute dbms\_shared\_pool.unkeep(‘用户名.过程名’);  
从DBMSPOOL脚本理解软件包DBMS\_SHARED\_POOL  
KEEP过程方法如下  
Procedure keep(name varchar2,flag char default ‘P’);  
参数1：用来说明数据库对象的名字，可以是过程，触发器，序列号，以及java对象。  
参数2：说明要常驻的数据库对象的类型，默认类型为包，过程or函数。其他需要指定一个字符变量说明对象类型。

将数据常驻内存  
SGA中段的已缓存块可以放在3个缓冲池中  
 默认池：默认区域，未设置的话所有的段都放在此(缓冲区池)  
 保持池：对于用户频繁访问的数据(表，索引or数据库对象的数据块等)可以放置在此候选的缓冲区池中。放在默认池中的数据块，也可以频繁访问，但是段数据会老化而退出默认池。推荐保持池，可以长期保存  
 回收池：对于随机访问的大段可以放此，因为大的数据段会很快老化退出缓冲池，导致缓冲区的频繁刷新输出。  
保持池&回收池用户管理。  
查看保持池大小信息  
Show parameter keep;  
查看当前数据库的数据块的默认缓冲池  
Select id,name,block\_size,buffers from vbufferpool;将数据常驻内存查看表和索引的块大小Selectsegmentname,segmenttype,blocksfromdbasegmentswhereowner=′Scott′andsegmentnamein(‘表名′,′索引名′);由于dbasegments是静态数据字典，使用如下获得最新段统计信息Analyzeindexscott.索引名computestatistics;Analyzetablescott.表名computestatistics;查询数据库块大小Showparameterdbblocksize;计算要保存的表和索引的大小Select(表块\+索引块)∗数据库块大小Kbytesfromdual;设置保持池的大小Altersystemsetdbkeepcachesize=10MB;查看当前库的数据块的缓冲池信息Selectid,name,blocksize,buffersfromvbuffer\_pool;  
查看表&索引当前存放在什么缓冲池中  
Select table\_name,tablespace\_name,buffer\_pool from user\_tables where table\_name=’表名’;  
Select index\_name,table\_name,buffer\_pool from user\_indexes where index\_name=’索引名’;  
将表设置为常驻内存,并查看是否修改成功，值为keep说明成功  
Alter table 表名 storage (buffer\_pool keep);  
Select table\_name,tablespace\_name,buffer\_pool from user\_tables where table\_name=’表名’;  
将索引设置为常驻内存,并查看是否修改成功，值为keep说明成功  
Alter index 索引 storage (buffer\_pool keep);  
Select index\_name,table\_name,buffer\_pool from user\_indexes where index \_name=’ 索引’;

将常驻内存的程序恢复为默认缓冲池  
将表恢复为默认缓冲池，并查看是否更改成功  
Alter table 表名 storage(buffer\_pool default);  
Select table\_name,tablespace\_name,buffer\_pool from user\_tables where table\_name=’表名’;  
将索引恢复为默认缓冲池  
Alter index 索引 storage (buffer\_pool default);  
只是修改了索引的缓冲池设置，但是保持池依然占用内存，但是其中已经没有数据，需要释放保持池中的内存。  
Alter system set db\_keep\_cache\_size=0;  
查看与数据库香瓜你的缓冲池信息  
Select id,name,block\_size,buffers from v$buffer\_pool;

优化重做日志缓冲区  
查看重做日志缓冲区大小  
Show parameter log\_buffer;  
优化重做日志缓冲区，需要确认发生了与重做日志缓冲区相关的等待时间。否则不应该随便修改大小,可通过WAIT视图和EVENT事件视图，确认等待时间以及该事件涉及的文件和会话  
查看会话等待时间。  
Select sw.sid,s.username,sw.event,sw.wait\_time from vsessions,vsession\_wait sw where sw.event not like ‘rdbms%’ and sw.sid=s.sid order by sw.wait\_time,sw.event;  
查询与闩锁latch相关的信息  
Select latch\#,name,gets,misses,1-(missses/(gets+misses)) “gets rate” from vlatchwheremisses>1;设置重做日志缓冲区大小查看重做日志缓存区大小Showparameterlogbuffer;Selectname,value/(1024∗1024)“MB”fromvparameter where name=’log\_buffer’;  
设置重做日志缓冲区大小，静态参数，需要重启  
Alter system set log\_buffer=10485760 scope=spfile;  
查询重做日志缓冲区大小  
Show parameter log\_buffer;  
优化共享池  
共享池由2部分组成  
库高速缓存：存放SQL正文，编译后的代码以及最终执行计划。  
硬解析：在sql语句处理步骤中，解析需要经过sql语句的语法语义分析，基于优化模式选择优化方案，以及执行最终计划。  
如果有相同的sql语句的执行计划已经缓存在库高速缓存中，此时就执行一个软分析。  
数据字典高速缓存:存放于sql语句操作相关的数据库对象，如表，索引，以及其他对象和权限信息  
库高速缓存，重用sql语句可以减少硬解析的时间，从而减少sql语句的响应时间。  
数据字典高速缓存减少了对sql语句涉及的数据库对象和权限定义的磁盘访问，也减少了sql语句的响应时间  
对共享池的优化目的就是不影响性能的情况下提高sql代码以及数据库字典的使用率  
使用绑定变量  
2个sql语句不一样就分别执行硬解析，减少硬解析使用绑定变量，动态传入参数  
Select \* from tablename where clo >&变量；  
通过sql trace跟踪一个sql查询，查看是否执行了硬解析  
清空共享池，目的方便对sql语句的分析  
Alter system flush shared\_pool;  
启动会话级的sql追踪功能  
Alter session set sql\_trace=true;  
执行sql插叙你语句并通过TKPROF解释该追踪文件  
Select \* from tablename where id>4000;  
文件中mis=1说明硬解析，0说明没硬解析  
调整CURSOR\_SHARING参数  
查看cursor\_sharing默认值  
Show parameter cursot\_sharing;  
Exact:(默认值)只有正文完全相同的sql语句才可以重用。  
Force:强制共享使用只有字面值不同的sql语句。  
更改参数为force  
Alter system set cursor\_sharing=force scope=both;

设置共享池大小  
查询共享池大小,0说明自动管理  
Show parameter shared pool size;  
可以安装数据库时设置，如下使用动态设置  
Alter system set shared\_pool\_size=200M scope=both;  
优化数据库高速缓存  
查询相关缓冲区缓存数据  
Select name,value from vsysstatwherenamein(‘dbblockgetsfromcache‘,‘consistentgetsfromcache′,′physicalreadscache′);dbblockgets:数据库高速缓冲区中存在被更改的数据，而此数据其他用户访问时已经提交。用户访问的数据在数据库缓冲区中是最新的版本consistentgets：一致获取，数据库高速缓冲区中存在被更改的数据，而此数据其他用户访问时还未提交。也就是用户访问的是脏数据，这样的数据不会被访问，此时用户只能使用回滚段中的记录。physicalreadscache：物理读，在数据库高速缓冲区中没有用户要访问的数据，需要从磁盘中读取该数据块。数据库高速缓冲区命中率=1−物理读/逻辑读修改高速缓冲区大小Altersystemsetdbcachesize=192M;缓冲池用户访问多个表，且表相当大，此时表只有部分保存在库高速缓存区中，会降低数据库缓冲区的命中率，如果大表不是用户频繁访问的对象，可以使用缓冲池来存放。这样的数据库对象会被覆盖用户访问多个小表，且频繁发生，可以保存到保留池中，这样对象不会被覆盖掉其他的对象会默认存储在数据库高速缓冲区中的默认池中缓存表的大小不要超过缓冲区的10CREATEINDEX索引名storage(bufferpookeep);altertable表名storage(bufferpoorecycle);alterINDEX索引名storage(bufferpookeep);创建索引并缓存在保留池Createindexscottempenameonscott.emp(ename)storage(bufferpoolkeep);查看是否缓存在保留池Runselectindexname,tablename,bufferpoolfromuserindexeswhereindexname=′SCOTTEMPENAME′;将表放入回收池Altertable表名storage(bufferpoolrecycle);查看表缓存信息Selecttablename,cache,bufferpoolfromdbatableswheretablename=′EMP′;CACHE:Y，说明表已经缓存在缓冲区中，使用cache关键字将表缓存在默认池中Createtabletable2asselect∗fromtable1cache;查看表的缓存信息，默认使用default缓冲池，且没有被缓存Selecttablename,cache,bufferpoolfromdbatableswheretablename=′TABLE2′;开启缓存Altertabletable2cache;优化PGA内存PAG主要作用大规模数据排序，如groupbyorderby等操作。PAG优化就是将大规模数据排序放在PGA中运行，而不是使用虚拟内存而占用操作系统的SWAPAREA。查看PGA排序区大小SORTAREASIZEPGA排序区设置自动管理，需要设置排序区的值(PAGAGGREGATETARGET)和设置为自动管理(WORKAREASIZEPOLICY为AUTO)查看PGA排序区是否自动管理Selectname,value,isdefaultfromvparameter where name in (‘pga\_aggregate\_target’,’workarea\_size\_policy’);  
查看PAG状态信息  
Select \* from vpgastat;AggregatePGAtargetparameter:设置当前的PGA内存总和AggregatePGAautotarget:为pga的排序区分配的内存大小。Cachehitpercentage:说明排序区在pga的排序区完成的比例。100说明全部的排序都在此进行。查看PGA的排序区进行排序的百分比，如果不为100，则需要适当增加pagaggregatetarget的值Select∗fromvpgastat where name like ‘cache%’;  
调整pga内存大小  
Alter system set pga\_aggregate\_target=76M;  
查看pga内存大小  
Show parameter pga;  
查看pga大小以及pga中排序区大小  
Select \* from vpgastatwherenamein(‘aggregatePGAtargetparameter′,′aggregatePGAautotarget′);查看PGA的排序区排序的详细信息。Selectpgatargetforestimate/(1024∗1024)asestdtarget,estdpgacachehitpercentage,estdoveralloccountfromvpga\_target\_advice;