oracle-dba-ORACLE实例优化

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 v$buffer_pool; 将数据常驻内存 查看表和索引的块大小 Select segment_name,segment_type,blocks from dba_segments where owner =’Scott’ and segment_name in (‘表名’,’索引名’); 由于dba_segments是静态数据字典，使用如下获得最新段统计信息 Analyze index scott.索引名 compute statistics; Analyze table scott.表名 compute statistics; 查询数据库块大小 Show parameter db_block_size; 计算要保存的表和索引的大小 Select (表块+索引块)*数据库块大小 Kbytes from dual; 设置保持池的大小 Alter system set db_keep_cache_size=10MB; 查看当前库的数据块的缓冲池信息 Select id,name,block_size,buffers from v$buffer_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 v$session s, v$session_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 v$latch where misses>1; 设置重做日志缓冲区大小 查看重做日志缓存区大小 Show parameter log_buffer; Select name,value/(1024*1024) “MB” from v$parameter 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 v$sysstat where name in (‘db block gets from cache‘, ‘consistent gets from cache’,’physical reads cache’); db block gets :数据库高速缓冲区中存在被更改的数据，而此数据其他用户访问时已经提交。用户访问的数据在数据库缓冲区中是最新的版本 consistent gets：一致获取，数据库高速缓冲区中存在被更改的数据，而此数据其他用户访问时还未提交。也就是用户访问的是脏数据，这样的数据不会被访问，此时用户只能使用回滚段中的记录。 physical reads cache：物理读，在数据库高速缓冲区中没有用户要访问的数据，需要从磁盘中读取该数据块。 数据库高速缓冲区命中率=1-物理读/逻辑读 修改高速缓冲区大小 Alter system set db_cache_size=192M; 缓冲池 用户访问多个表，且表相当大，此时表只有部分保存在库高速缓存区中，会降低数据库缓冲区的命中率，如果大表不是用户频繁访问的对象，可以使用缓冲池来存放。这样的数据库对象会被覆盖 用户访问多个小表，且频繁发生，可以保存到保留池中，这样对象不会被覆盖掉 其他的对象会默认存储在数据库高速缓冲区中的默认池中 缓存表的大小不要超过缓冲区的10% CREATE INDEX 索引名 storage (buffer_poo keep); alter table 表名storage (buffer_poo recycle); alter INDEX 索引名 storage (buffer_poo keep); 创建索引并缓存在保留池 Create index scott_emp_ename on scott.emp(ename) storage (buffer_pool keep); 查看是否缓存在保留池 Run select index_name,table_name,buffer_pool from user_indexes where index_name=’SCOTT_EMP_ENAME’; 将表放入回收池 Alter table 表名 storage (buffer_pool recycle); 查看表缓存信息 Select table_name,cache,buffer_pool from dba_tables where table_name=’EMP’; CACHE:Y，说明表已经缓存在缓冲区中， 使用cache关键字将表缓存在默认池中 Create table table2 as select * from table1 cache; 查看表的缓存信息，默认使用default缓冲池，且没有被缓存 Select table_name,cache,buffer_pool from dba_tables where table_name=’TABLE2’; 开启缓存 Alter table table2 cache; 优化PGA内存 PAG主要作用大规模数据排序，如group by order by等操作。 PAG优化就是将大规模数据排序放在PGA中运行，而不是使用虚拟内存而占用操作系统的SWAP AREA。 查看PGA排序区大小 SORT_AREA SIZE PGA排序区设置自动管理，需要设置排序区的值(PAG_AGGREGATE_TARGET)和设置为自动管理(WORKAREA_SIZE_POLICY 为AUTO) 查看PGA排序区是否自动管理 Select name,value,isdefault from v$parameter where name in (‘pga_aggregate_target’,’workarea_size_policy’);
查看PAG状态信息
Select * from v$pgastat; Aggregate PGA target parameter :设置当前的PGA内存总和 Aggregate PGA auto target:为pga的排序区分配的内存大小。 Cache hit percentage:说明排序区在pga的排序区完成的比例。100说明全部的排序都在此进行。 查看PGA的排序区进行排序的百分比，如果不为100，则需要适当增加pag_aggregate_target的值 Select * from v$pgastat where name like ‘cache%’;
调整pga内存大小
Alter system set pga_aggregate_target=76M;
查看pga内存大小
Show parameter pga;
查看pga大小以及pga中排序区大小
Select * from v$pgastat where name in (‘aggregate PGA target parameter’ ,’aggregate PGA auto target’); 查看PGA的排序区排序的详细信息。 Select pga_target_for_estimate/(1024*1024)as estd_target,estd_pga_cache_hit_percentage,estd_overalloc_count from v$pga_target_advice;