SQL 调优1

                                             SQL 调优1

调整的方法
调整的工具
内存组件调整  shared_pool data_buffer pga IO
性能文件确定

调整的方法：
调整的流程 架构设计 建模 程序 数据库 硬件
调整  每次只调整一个地方 top
     消耗资源最多 执行的次数最多 执行的时间最长
     达到优化的目标就不优化了
     优化的过程中一定不要产生新的性能问题 ----->熟悉别人的生产环境

调整的工具：
1   v$ x$ 动态性能试图--收集当前数据的状态 找您执行语句的时候的数据库的状态
2   statspack
3   awr
4   rda
5   http协议的EM管理后台
6   操作系统的OS命令 ------> vmstat iostat sar ipcs
7   告警日志文件 (backgroup_dump_dest)alert_sid.log <-------------> <>.trc(user_dump_dest)

找Waring 和 ORA 开头的错误
查找告警日志的绝对路径
select value ||'/alert_'||instance_name || '.log' from v$parameter ，v$instance wehere naem='background_dump_dest'
select value ||'/alert_'||instance_name ||'.log' from v$parameter ,v$instance where name='background_dump_dest'
路径 ：/u01/app/oracle/admin/denver/bdump/alert_denver.log   。。。。。。。>vi 文件

WARNING
ORA
oerr ora 错误编号 ------>查看错误方法 -------注意 方法一 累积解决ora错误的经验只有靠时间积累 方法二 metalink解决
每天写新的文件 备份服务器 scp 到备份服务器 保证alert_denver.log 每天都是新的
cp /dev/null /u01/app/oracle/admin/denver/bdump/alert_db10g.log 追加时间的格式
date +%Y%m%d 结果  20120718  《------使用这种格式
date +%y%m%d 结果  120718 
date +%Y%M%D 结果  20121007/18/12
date +%Y%M%D 结果  20121207/18/12
date +%Y%M%d 结果  20121118

物理日志

后台进程的日志
show parameter backupgroud_dump_dest
show db_writer
db_writer_processes
db_writer_processes<=10 dbw0-dbw9
db_writer_processes>=10 dbw0-dbw9 dbwa-dbwj
在32位上只有 10个dbwn进程
64位上有20个进程分别是dbw0~dbw9 dbwa~dbwj这几个进程

归档进程的日志
arcn  归档进程最大有30个 默认值是2-8个值
alter system set log_archive_max_processes=2;  -------》设置归档进程的个数
日志切换忙不过来 IO现在不够用 就要增加arcn

用户进程的日志
spid--会话进程的编号
select instance_name from v$instance; -------》查看数据库的实例名
select sid,serial#,paddr from v$session where username='scott'

在v$process中查看的是用户的名字 比如oracle
select pid,spid from v$process where addr=
ho ps -ef | grep 5026
用户进程默认不产生日志 需要使用用户进程的跟踪
sys
打开所有的用户进程的跟踪
alter system set sql_trace=true false
user这种方式是用户自己打开进行监控 而 有一定的局限啊
alter session set sql_trace=true
/u01/oracle/admin/denver/udump/

是用下面的这种方式就不许要再用户进程下set sql_trace=true
---使用dbms_monitor 进行用户进程跟踪
可以绑定变量
select sid,serial# from v$session where usernaem='SCOTT"

exec dbms_monitor.session_trace_enable(134,23,true,false)
exec dbms_monitor.session_trace_disable(134,23)
scott
alter session set sql_trace=true
slect value from v$parameter where name='user_dump_dest'

得到用户进程的日志<----------------sql_trace=true
select value||'/'||instance_name||'_ora_'||spid||'.trc' from
v$parameter p ,v$instance i,v$process r,v$session s where s.paddr=r.addr
and s.username='SCOTT' and p.name='user_dump_dest'

使得读的更轻松
tkprof /u01/oracle/admin/denver/udump/db10g_ora_5018.trc db10g_ora_5018.txt
vi db10g_ora_5018.txt

其他工具包跟踪会话
dbms_session
跟踪自己更总自己的会话

dbms_session.session_trace_disable()

更总一个用户的会话
打开
exec dbms_system.set_sql_trace_in_session(134,29,true)
exec dbms_system.set_sql_trace_in_seeson(134,29,false)
关闭
也可用debug命令跟踪
跟踪 10046 10053 适用于复杂的SQL分析的时候 简单的语句
exec dbms_system.set_ev(sid,serial#,1046,12,'')
关闭跟踪
exec                    sid,serial#,false)

调忧工具AWR
9i statspack 10g 11g 已经安装并且使用
/u01/app/oracle/product/10.2.0/db/rdbms/admin/

start ?/rdbms/admin/awrrpt.sql
?=$ORALCE_HOME

awr每一小时对内存的数据收集一次 数据放到sys用户下 保留时间为7天
awr收集数据 分析数据 为自动化提供
所有的快照都在里面
dba——hist——snapshot

select snap_id from dba_hist_snapshot
出一个连续额的信息 数据库必须是7*24小时的
@？/
中间不能关闭数据库 假如有空置就说明数据库被关闭过

load profile 负载文件
排序 越少越好 多多不
块的更改的值

实例问题查找
（redo | buffer） nowait 命中率高于98%
  缓冲区大于95% 低于说明内存不够用 内存中的排序高大于100%
数据字典命中率 高于95%  soft 软分析的比例越高越好
execute 执行的命中率 越低越好 latch 删硕的命中率越大越好

TOP5 说明数据库中最严重的问题

latch free 删锁的问题很严重 等待的次数多 等待时间长是我们需要关注的

/opt/soft/statspace.pdf

AWR 工具包对应的名字
dbms_
人为创建快照
exec dbms_workload_repository.create_snapshot();

exec dbms_workload_repository.modify_snapshot_settings(7*24*60,60)
启动OEM图形界面
emctl stasrt dbconsole

                                        调优2

OWI oracle等待事件 用来诊断数据出现的问题
select sid,serial#,event from v$session
v$session_wait
select sid,seq#,event,seconds_in_wait from v$session_wait order by 4 desc ----->收集一个时间段来收集

每个月去熟悉一个等待事件
创建快照的命令
exec dbms_workload_repository.create_snapshot() 收集的间隔高于10分钟

内存部分的调优
pga的部分
语句排序 合并 链接 bash
pga一定在100% 不能小也不能大
pga 变化的原因可能会出现下面的2中情况
1 pga小了
2 莫种语句严重使用pga排序 当内存排序内存完成不了的时候使用temp排序

temp排序
1 不用文件系统 使用raw asm提高磁盘排序能力
2 当内存很多的时候 使用shm排序

以后发现临时表空间不够了
采用10g开始的新功能 表空间组
表空间组 支持失败转移和负载均衡的功能

查找表空间组 
表空间组就是下面的建了一个表空间之后就可以看到这个表空间和组好
dba_tablespace_groups
临时表空间
dba_temp_files

alter tablespace   tempg
alter database default temporary tablespace <>

临时表空间自动增长 排序多 表空间很大 一段时间之后表空间的物理文件很大 占用很多磁盘空间 排序的时候需要
空间 排序后不需要空间临时表空间的回收
a 建立新的设置为正在使用的 删除原来的
b 表空间resize 到一个比较小的值
c 11g中可以直接对表空间进行收缩

查看默认表空间
select * from database_properties where property_name like '%DEFA%‘
du -sh 路径
alter database tempfile 路径 resize 50m
大表表空间可以直接对表间进行resize 小表表空间只能对数据文件进行resize

11g 空间回收命令其他版本
alter tablespace temp shrink space

排序的性能
mem
one-pass 一次通过
mutil-pass

v$sort_usage
select * frm v$pgastat show paramter pga_

操作练习
create tablespace pgadb datafile ... size 50m autoextend on
create table scott.pga tablespace pgadb as select * from scott.emp
insert into scott.pga  select * from scott.pga
crete table scott.pgan as select rowid id,a.* from scott.pga a

exec dbms_workload_repository.create_snapshot()
60秒之后创建索引

sga部分

sga自动管理 只要内存够用就可以使用
sga_max_size=总大小
sga_target=自动管理大小
ASMM
sga_target=设置大小后 其他的sga组件不需要设置
但是 log_buffer 不能自动管理 需要设置大小
larege_pool_size 要设置大小 1-4个粒度
内存的最小单位是粒度
linux sga_target <1g 4M 否则16M
java次设置1-4个粒度
要用到 java_pool_size xml java 功能 设置1-2 个粒度
留池不需要设置大小 oracle中不再使用高级复制 和流复制
使用Goldengate

Goldengate 的时候设置1-2个粒度
shared——pool_size db_cache_size 使用自动管理维护
假如内存不足 自动管理将出现sga东东 则需要设值sga_target=o shard_pool_size=30%
sga_target db_cache_size=30% sga_target 经过一段时间比如1天 在看建议向导得到这两个组件的大小
设置为建议的大小 并把sga——target还原

v$sgainfo 可以看到系统全局区的大小size
select * from v$sgainfo
alter system set sga_max_size=1024m scope=spfile  <-------粒度的大小跟这个有关
show parameter java_

v$db_cache_advice
size_factor 

v$shared_pool_advice
alter system set shared_pool_size=88m
alter system set db_cache_size=16m  ------>只是负责怎加不减小

sga_target>0之后
alter system set sga_target=155m
v$sga_target_

v$pga_target_advice

11g
pga+sga 全自动自动管理 内存自动管理 AMM
需要使用建议试图向导

show parameter memory

配置11g OEM
emca -config dbcontrol db -repos recreate
emca -deconfig decontrol db

db_nk_cache_size
数据缓冲区 db_cache_size
db_block_size 对应的内存区域
与db_block_size 不一样的大小 需要设置db_nk_cache_size
比如
db_block_size=8192
create tablespace <> ....blocksize=8192
alter system set db_nk_cache_size
show parameter db_blcok_size
建立不同块的大小
什么情况下要用这个语句呢
再经常扫描
把每个块设置成 2k 2k
块越大保留的空间越多
8k 联机事物处理 数据存储业务

缓冲区的使用方式
默认方式空间管理
 
buffer_pool default次
有现的内存可以放无限的数据
重用
建表建索引的时候可以用到
create index | table <> .... storege(buffer_pool) default| recycle |keep) keep池的作用----->放进内存中去访问和调用   

select user_name,tale_names from user_tables
alter table e1 storage(buffer_pool keep | recycle|default)
default --db_cahe_size --db_nk_cache_size
11gr2 任然要设置这俩个值
keep db_keep_cahe_size
recyle db_recycle_cache_size

固定内存大小
keep
dbms_shared_pool
执行这个脚本就可以有莫个功能
?/rdbms/admin/dbmspool.sql

查看那些对象有没有被缓存
v$db_object_cache
select owner,kep ,name from v$db_object_cache

用keep绑定
exec dbms_shared_pool.keep('scott.getsal','p') p-------->function
unkeep 取消绑定
exec dbms_shared_pool.unkeep('scott.getsal','p')
select tname from
v$sql_cursor
v$session
select sid,username,

                                             调优3

段 IO
段 管理的方式本地管理
区 管理的方式是自动管理
create tablespace <> size extent management local segment space management auto
select dbms_metadata.get_ddl('tablespace', 表空间名字) from dual
select dbms_metadata.get_ddl('TABLE','EMP') from dual
select dbms_metadata.get_ddl('INDEX','PK_EMP') from dual
alter table emp pctfee 0
alter table emp pctfee 0
表空间剩余多少 如何知道的
字典的方式记录 所有的表空间剩余的情况需要sys收集与报告
表空间自己用一个位图块去记录剩余量 ASSM(自动共享段管理)<------->自动段空间管理
区参数的分配
存储参数
初始区的
next 默认值是5个block 在原来增长的基础上增长多少个block
pctincrease 我们增长50%就增加下一个段
minexts 最小多少个区

物理属性
pctfree 0-99 0 insert可以把这个块插满 默认值是10%

行的迁移 原来的block放不下了 到另外一个块去放了
1 blcock 的数据倒出来 把表的内容截断 然后又倒出去 ------>项目中无法用
2 直接对表进行move命令可以完成对表数据的整理 -----> 不会收高水位线
3 shrke space 命令可以完成对表的整理  ----> 回收高水位线
4 联机表的定义
pctused + pctfree 最大值=100%

freelist
pctused 定义好了就不能有点复杂 稍后再详细理论
支持多少个事务

操作

常规表
scott
create table emptt as select * from emp where 0=1  ---------->只是表结构
alter table emptt pctfree 0
inset into select * from emp
commit

查找block的改变
select rowid,rownum from emptt

update emptt set job='ANR' where job='ang'
这时行的迁移发生了

alter table emptt move ---->表空间的剩余量必须有使用量那么大
slect table_name,row_movement from user_tables where table_name='EMPTT'
alter table emptt enable row movement -----> 这时 回到高水位线 回收空间
alter table emptt shrink space
alter table emptt disable row movement
alter table emptt shrink space compact 空间行的整理 高水位线不会下降
alter table emptt shrink space
alter table emptt shrink space cascade
alter table emptt shrink space compact cascade
alter table emptt shrink space
alter index <> rebuild 索引必须重建

分区表

建立一个分区表

insert into empl select * from emp
alter table empl move ----> 报错
alter table empl enable row
alter shrink space
alte rable disable row movement
selelct table_name ,patition_name from user_tab_partitions
alter table empl move partition emp1_p1
还要重建索引 分区表的索引分为 全局索引 和本地索引
alter table name enable row movement
alter table name shrink space
alter table name disable row movement

slect table from user_tables

select 'alter_table' || table_name || 'enable row movement;'||chr(10)||"alter table '||table_name||
"shrink

表和列太多了 就要把它分开 避免行的迁移
行的连接
最大的块是32k char 2000 varcahr2 4000
分析一个行有没有链接 /U01/oracle/10g/rdbms/admin/utlchain.sql

行的迁移处理方法
@?/rdbms/admin/utlchain.sql ----->执行脚本
analyze table emptt list chained rows 分析一个表的链接存在bu
select table_name,head_rowid from chained_rows
desc chai
导出行的结果
删掉行
然后插入
创建一个表去备份
create table emptt_r as select * from emptt where rowid in (sleect head_rowid from chained_rows

select * from emptt where rowid in (select head_rowid from chaind_rows where table_name='EMPTT'
insert into emptt

move --- index

IO 在硬件上优化IO
裸设备
lvm
yum install /sbin/lvcreate
5个硬盘 lvcreate -i(磁盘的个数) 5 -I(磁盘的个数*莫个) 20k -l

图形界面的吊带化的值
system-config-lvm

干掉swap分区
swapoff -a
/etc/fstab

asm
系统中的参数与IO 有一定的关系
show parameter db_file_multiblock_read_count
set autot trace exp
select * from emp where ename='KING'
table access full ----->全表扫描

index unique scan 只需要扫描一个block
filter
sys
show parameter db_
db_writer_processes

服务器性能调整中有一个是锁定冲突
表行有主键 唯一性约束
事物正在修改行所 其他的只能等待行所释放后再锁定行

模拟锁定冲突
scott
update emp set sal = sal+1 where empno=7566
任何一个DML 语句都会有行锁 只有行有主键 唯一型约束的时候才会造成锁定冲突

select blocking_session from v$session
这句话是 找到硕定冲突的会话
select sid,serial#,event from v$session where sid in (select blocking_session from v$session)
杀死会话
alter system kill session 'sid,serial#'

查看等待时间锁定冲突的方法
命令行模式
OEM界面
AWR 报表

如何确定性能问题
学习OWI
addm    可以找出70%的性能问题
perform -- run addm now          只能找出距上一次快照之间的性能问题

找过去时间
start ?/rdbms/admin/addmrpt.sql

内存    oracle
CPU        os    
IO        os
段空间行的迁移
索引状态
锁定冲突
addm
em   --- statack top5

完成下面的作业
racle@updba ~]$ emp  --> ename='....'
[oracle@updba ~]$ 2000 次查询语句
[oracle@updba ~]$ 10
[oracle@updba ~]$
[oracle@updba ~]$ emp  ---> empno='...'
[oracle@updba ~]$ 2000 次查询语句
[oracle@updba ~]$ 10
[oracle@updba ~]$
[oracle@updba ~]$ --->
[oracle@updba ~]$ 3 group   size 5m
[oracle@updba ~]$ create table ttt as select * from emp;
[oracle@updba ~]$ insert into ttt select * from ttt;
[oracle@updba ~]$ .....
[oracle@updba ~]$ 5
[oracle@updba ~]$ commit;
[oracle@updba ~]$ create table ttta as select * from emp;
[oracle@updba ~]$ insert into ttt select * from ttt;
[oracle@updba ~]$ commit;
[oracle@updba ~]$ insert into ttt select * from ttt;
[oracle@updba ~]$ commit;
[oracle@updba ~]$ .....5
[oracle@updba ~]$ ----

~