##################################################################################################################
##################################################################################################################
##################################################################################################################
##################################################################################################################
##################################################################################################################
########################### ###########################
########################### ###########################
########################### oracle数据库优化 ###########################
########################### ###########################
########################### ###########################
##################################################################################################################
##################################################################################################################
##################################################################################################################
##################################################################################################################
##################################################################################################################
---------------查看当前快照的默认statistics_level---------------
show parameter statistics_level;
有以下三种状态
TYPICAL
ALL
BASIC
---------------修改awr的保留天数为7天---------------
SQL> exec dbms_stats.alter_stats_history_retention(7);
---------------查看数据快照的snap范围---------------
select min(snap_id),max(snap_id) from dba_hist_snapshot;
---------------清除AWR快照---------------
begin
dbms_workload_repostitory.drop_snapshot_range(
low_snap_id=>188,
high_snap_id=>200);
end;
/
SQL> select DBID,SNAP_INTERVAL,RETENTION from dba_hist_wr_control;
DBID SNAP_INTERVAL RETENTION
----------------- ------------------------------ ------------------------------
3522467366 +00000 01:00:00.0 +00008 00:00:00.0
SNAP_INTERVAL：说明每小时收集一次数据
RETENTION：快照保留8天
---------------生成awr快照---------------
@?/rdbms/admin/awrrpt.sql
---------------RAC生成的数据快照---------------
@?/rdbms/admin/awrgrpt.sql
---------------生成ASH快照---------------
@?/rdbms/admin/ashrpt.sql
---------------生成addm快照---------------
@?/rdbms/admin/addmrpt.sql
---------------查看当前数据库的数据块的缓冲池---------------
select id,name,block_size,buffers from v$buffer_pool;
---------------查询缓冲区读取次数最多的sql语句---------------
select sql_text,buffer_gets,parse_calls from v$sql where buffer_gets>&number order by buffer_gets;
---------------查看相关缓冲区缓存的数据---------------
select name,value from v$sysstat where name in ('db block gets from cache','consistent gets from cache','physical reads cache');
NAME VALUE
---------------------------------------------------------------- ----------
db block gets from cache 5736335
consistent gets from cache 102874635
physical reads cache 25605735
逻辑读=db block gets from cache+consistent gets from cache
物理读=physical reads cache
数据库高速缓存的命中率=1-25605735/(5736335+102874635)
---------------使用多个缓冲池---------------
create index emo_idx on sql_test.emp(id) storage (buffer_pool keep);
alter table sql_test.emp storage(buffer_pool recycle);
alter index SQL_TEST.EMPNO_IDX storage (buffer_pool keep);
---------------查看下统计数的保存天数---------------
SQL> select dbms_stats.get_stats_history_retention from dual;
---------------验证模式STAGING的数据统计是否成功---------------
select last_analyzed,table_name,owner,num_rows,sample_size from dba_tables where owner='STAGING';
gather_database_statistics 为全库中的表统计数据
gather_schema_statistics 为某个模式统计数据
gather_table_statistics 为某个特定的表统计信息
gather_index_statistics 为某个索引表统计信息
这些统计最终会存储在 dba_tab_statistics 和 dba_tab_col_statistics
---------------查看SGA缓冲区满了之后的会话语句---------------
select distinct(session_state) from v$active_session_history;
---------------内存监控视图---------------
V$MEMORY_DYNAMIC_COMPONENTS 包含所有内存组件的当前状态
V$MEMORY_TARGET_ADVICE 提供针对 memory_target初始化参数的优化
V$MEMORY_RESIZE_OPS 包含最近完成的800个内存到小调整请求的循环历史纪录缓冲区
---------------查找比较消耗资源的sql---------------
select se.username,se.sid,su.extents,su.blocks*to_number(rtrim(p.value)) as "space",tablespace,segtype,sql_text
from v$sort_usage su,v$parameter p,v$session se,v$sql s
where p.name='db_block_size' and su.session_addr=se.saddr and s.hash_value=su.sqlhash and s.address=su.sqladdr order by se.username,se.sid ;
---------------找占用cpu多的用户session及运行sql---------------
select a.sid,spid,status,substr(a.program,1,40) prog,a.terminal,osuser,value/60/100 value,d.sql_text
from v$session a,v$process b,v$sesstat c,v$sqltext d
where c.statistic# =12
and c.sid=a.sid
and a.paddr=b.addr
and a.sql_address=d.address
order by value desc;
---------------查询当前系统耗费cpu资源最多的前24个sql语句---------------
select sql_text,cpu_time,buffer_gets from (select sql_text,cpu_time,buffer_gets from v$sql order by cpu_time desc) where rownum<24;
---------------查看会话等待时间---------------
select sw.sid,s.SERIAL#,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;
---------------查看自实例启动以来的累积合计---------------
select name,class,value from v$sysstat;
---------------查询top10 sql---------------
select * from
(select parsing_user_id,executions,sorts,command_type,disk_reads,SQL_FULLTEXT from v$sqlarea order by disk_reads)
where rownum < 10;
---------------查询自实例启动以来磁盘I/O最多的sql语句---------------
select sql_text,executions,disk_reads from v$sql where disk_reads>&number order by disk_reads desc;
---------------查找全表扫描的sql---------------
select sql_text from v$sqltext t,v$sql_plan p
where t.hash_value=p.hash_value
and p.operation='TABLE ACCESS'
and p.options='FULL'
order by p.hash_value,t.piece;
---------------查看实例（SGA，PGA）占用的内存---------------
show parameter memory_max_target
show parameter memory_target
###################################################################################################################################################
#SGA的值由两个参数来决定：1. sga_max_size(该值决定了SGA的总大小) 2.sga_target(该值决定了数据库是否启用自动内存管理) #
# #
#--------修改重做日志缓冲区------------ #
#alter system set log_buffer = 104857250 scope=spfile; #
# #
#--------当缺少绑定变量时可修改CURSOR_SHARING-------- #
#alter system set cursor_sharing=force scope=both; #
# #
#-------修改共享池大小----------- #
#alter system set shared_pool_size=200M scope=both; #
# #
#--------修改大池的大小和计算公式--------------- #
# #_of_allocated_channels*(16MB+(4*size_of_tape_buffer)) #
#alter system set large_pool_size=200M scope=both; #
# #
#-------修改库高速缓冲区--------- #
#alter system set db_cache_size=192M; #
###################################################################################################################################################
####################################################################################################################################################
#PGA自动管理由两个参数来决定：1. workarea_size_policy 2.pga_aggregate_target #
# #
#---------查看实列是否启用自动PGA内存管理--------------- #
#select name,value,isdefault from v$parameter where name in ('pga_aggregate_target','workarea_size_policy'); #
# #
#---------查看pga的使用情况----------------- #
#select * from v$pgastat; #
# #
#---------修改PGA内存大小---------------- #
#alter system set pga_aggregate_target=725M; #
####################################################################################################################################################
---------------从自动内存管理模式更改为ASMM---------------(自动管理内存和优化内存的方法，设置目标内存大小初始化参数MEMORY_TARGET 和最大内存大小初始化参数memory_max_target)
1.ALTER SYSTEM SET MEMORY_TARGET="somevalue" scope=spfile;
2.alter system SET memory_max_target="somevalue" scope=spfile;完成后，重新启动实例。
---------------启用自动共享内存管理功能---------------
1.查看当前sga_target的大小
select (( select sum(value) from v$sga) - (select current_size from v$sga_dynamic_free_memory))/(1024*1204) "sag_target(m)" from dual;
2.设置sga_target的值
alter system set sga_target=value [scope={spfile|memory|both}]
其中value是在步骤1中计算的值，或者介于所有sga组件的总大小与sga_max_size之间的某个值。
3.将自动设置大小的sga组件设置为0。为此需要编辑文本初始化参数文件，如果需要，需要重启实例。
注:如果没有指定sga_max_size 而 指定了 sga_target，oracle 就自动调整SGA各组成部分的大小，从而使分配的内存总量等同于sga_target.
---------------memory_target---------------
memory_target 是11G中 新增的参数， 用于在SGA 和 PGA 之间平衡oracle 可用的所有内存，以优化性能。
---------------从自动内存管理模式更改为ASMM---------------
1.ALTER SYSTEM SET SGA_TARGET=0; 数据库当前的sga内存分配设置sga_target.
2.将自动设置大小的sga组件的值设置为0。 完成后，重新启动实例。
---------------不用ASMM---------------
将sga_target 设置为0 可禁用自动化功能。
自动优化的参数设置为其当前大小。
sga大小总体不受影响
db2迁移到oracle.txt
