Up-Looking -4 Tuning(Basic)

tunning

一、 --性能调优   排错  性能调优

设计阶段--开发阶段--部署阶段--运行阶段

DBA在这四个阶段中必须参与设计，布署是主要工作（硬件，业务需求，存储分布，用户并发数，用户资金）

运行阶段的监控维护。

往往DBA只有才运行阶段才能参与调优。

还有生产库没有经过测试就上线。

调优目标：响应时间  1）service time（cpu时间） 2）wait time等待时间

 

调优工具及方法：

1、诊断文件，查看完后删除。

1）告警日志：记录启动时非缺省参数值、错误信息、检查点信息、日志切换事件信息--DBA每天日常必须工作。检查后可删除，删掉后会自动产生。

2）后台进程跟踪文件 

3）用户跟踪文件

 

>alter system set sql_trace=true; 尽量不要去做。

 

跟踪用户操作：

1）

>grant alter session to scott;

>conn scott/tiger

>alter session set sql_trace=true;

 

>conn / as sysdba

>select p.spid from v$session s,v$process p where s.paddr=p.addr and s.username='SCOTT';

$tkprof  $ORACLE_BASE/admin/orcl/udump/****.trc  file.tpf  explain=scott/tiger sys=no

                        vim file.prf    --可以查看解析执行提取SQL的过程，以及执行计划（是否全表扫描等等）

 

2）

>@$ORACLE_HOME/sqlplus/admin/plustrce    --创建角色plustrace

>grant plustrace to scott;               --授权角色

>conn scott/tiger

>@$ORACLE_HOME/rdbms/admin/utlxplan    --创建执行计划表plan_table

>set autotrace on                      --打开自动跟踪

>create table e as select * from emp;

>insert into e select * from e;   --执行多次  

                                  recusive calls(系统SQL递归调用)

                                  db_block_gets（获取的需要使用的数据块数）、

                                  physical reads（从磁盘读的数块）、         

                                  consistent gets(一致性的读).

                                  redo size（重做日志大小）   --值越小越好

 

3）使用包进行跟踪  --web应用很难跟踪用户，9I，10G前使用

>conn / as sysdba

>select sid,username,serial# from v$session; --找SCOTT用户的SID SERIAL#

>exec dbms_system.set_sql_trace_in_session(sid,serial#,true);

 

>conn scott/tiger;

>select * from emp;

 

 

>conn / as sysdba

>select p.spid from v$session s,v$process p where s.paddr=p.addr and s.username='SCOTT';

$tkprof  $ORACLE_BASE/admin/orcl/udump/****.trc  file.tpf  explain=scott/tiger sys=no waits=yes

 

 vim file.prf --

 

4）使用dbms_monitor --10G后出来的，可以实现端对端跟踪。

>select sid,username,serial# from v$session; --找SCOTT用户的SID SERIAL#

>exec dbms_monitor.session_trace_enable(sid,serial#,true,true);

(>exec dbms_monitor.session_trace_disable(sid,serial#)

 

>conn scott/tiger

>select e.ename,d.dname from emp e,dept d

 where e.deptno=d.deptno;                      --有索引走的

 

>create table dd as select * from dept;

>select e.ename,d.dname from emp e,dd d

 where e.deptno=d.deptno;                      --无索引走，全表扫描hash join但更块，因为表数据量少。

 

>conn / as sysdba

>select p.spid from v$session s,v$process p where s.paddr=p.addr and s.username='SCOTT';

$tkprof  $ORACLE_BASE/admin/orcl/udump/****.trc  file.tpf  explain=scott/tiger sys=no waits=yes

 

EM看性能

performance --Top activity--Top session  监控连上的用户及操作。

 

 

系统性能视图：

v$sysstat  --数据库打开以来的统计量。

>select * from v$sysstat   --workarea execution --optimal  排序，全部是这个选项说明内存性能好。

                         parse  hard--需要重新解析 soft表示缓存里有，用过程或绑定变量-软分析，执行计划使用原来的。

 

 

v$sgastat

v$event_name  --等待事件，等待事件多，是一个性能下降的征兆。

 

v$system_event  --系统事件 total_waits,average_wait

>select event,total_waits,average_wait from v$system_event;  文件存放的磁盘分布

 

v$session

v$session_event --查看在线会话。

>select sid,event

 

v$session_wait  --经常用。

>select sid,event from v$session_wait;

 

v$sqlarea

>select sql_text from v$sqlarea;  --所有在共享池中的SQL语句。

 

>alter system flush shared_pool;

生成执行计划是最花费CPU，最消耗资源的。

 

性能收集信息工具：将内存性能信息根据时间段收集生成报告。

1）statspack --9I前使用，主要用来收集调优数据。需要安装。如果不指定表空间将会放在SYSAUX表空间里。

>create tablespace perf datafile '....' size 200m;  --大小必须为150M以上

>@$ORACLE_HOME/rdbms/admin/spcreate; 工具安装，根据提示输入密码和表空间名字

 

手工收集信息：两个时间点间的信息。

>execute statspack.snap;

>execute statspack.snap;

@$ORACLE_HOME/rdbms/admin/spreport    输入生成报告存放地址。

 

2）10G提供了两个工具--AWR（automatic workload resposity）  ADDM(automatic database diagnostic management)

oracle 自动收集，每隔一小时收集，信息存放于SYSAUX中，可以存放7天。

ASH-active session history活动会话历史。

 

server警告信息：提供提前告警信息，比如表空间是否到阀值需要调整，等等。

 

客户反映很慢，不是对象失效就是索引失效。

>select status,index_name from user_indexes;   --unusable索引失效

 

>alter index pk_emp rebuild;

 

一、优化器

优化器每天晚上10点自动收集统计信息(可调整)，根据统计信息选择执行路径和执行计划。

>show parameter optimizer_mode   --优化器。

--在查询时能根据统计信息生成查询语句的最优执行计划。

 

两种模式：

CBO：基于成本的优化，现在多用。根据统计信息分析，选择最佳路径，生成执行计划。

    有三个参数：all_rows（考虑吞吐量）,first_rows（考虑响应性能）.9i用的比较多的是CHOOSE

RBO：基于规则的优化。根据定制规则生成执行计划。只有一个参数RULE

 

9I没有自动统计收集信息，10G有。EM可配置-administration--Manage Optimizer Statistics

 

user_tables中有收集的统计数据

 

>select table_name,last_analyzed,num_rows from user_tables;

>exec dbms_stats.delete_schema_stats('SCOTT'); --执行后user_tables中的统计信息删除。

 

>exec dbms_stats.gather_schema_stats('SCOTT'); --马上就有统计信息。

 

统计信息的有无之间影响到执行性能。

在9i碰到性能问题，可以考虑重新收集统计信息。

 

二、实例调优 --其实就是调整SGA

PGA--用户进程独占内存。--排序、会话信息、堆栈信息

命中率--想读的数据块是否在内存。

内存调整的参数：shared_pool_size

             db_cache_size    

             log_buffer  --固定值，不申请区组分配。

 

ASMM-自动共享内存管理。由以下两个参数控制：sga_max_size（总大小，操作系统分配的总内存） 

                                  

sga_target（具体使用值，上限为sga_max_size的大小。                    db_cache_size,shared_pool_size,java_pool_size,large_pool_size）

 

内存管理进程：MMON，自动去管理内存的分配。

 

自动存储管理对于大型数据库不太提议使用。本身自动调整会影响运行性能。

 

改SGA_TARGET大小：

>alter system set sga_target=160m;显示出来会是4M的倍数，内存分配以区组(默认4m)分配

 

当sga_target=0是，为手动管理。

 

相关数据字典：

>select table_name from dict where table_name like '%SGA%'

v$sga_dynamic_components

 

 

一、共享池调整。一般200m就够了，再增大性能没有明显改善。根本原因不在调大SGA大小，根本原因还是在开发代码编写。本身维护SQL数量需要一定的资源。

查看v$sqlarea时，如果发现每条都列出来，则需要告知开发应用需要绑定变量，和使用存储过程。

 

手动更改SGA_target:sga_target=0

库缓存：共享SQL

优先调整shared_pool

 

>desc v$rowcache --计算共享池库cache，数据字典缓存命中率,低于90%需要加大

>select (sum(gets-getmisses-fixed))/sum(gets)) "rwo_hit_ratio" from v$rowcache;

 

计算库缓存区命中率 ,低点对性能影响不大，关键是看reloads,invalidations,次数要尽量少。大的话说明需要增大share_pool了。

>desc v$librarycache

>select sum(pinhits)/sum(pins) library_cache_hit_ratio from v$librarycache;

 

>select namespace,pins,pinhits,reloads,invalidations from v$librarycache;

 

>select * from v$sqlarea;  --查看共享SQL语句数，直接拿执行计划。

 

parse-语法检查、语义检查、权限-生成执行计划：硬分析，生成执行计划时最耗资源。

shared_pool不是越大越好，管理同样需要资源。

 

>show cursor_sharing    上下文 --exact（精确到每条，默认）\similar\force(绑定变量，但会产生副作用，有时会绑定到不该绑定的语句。)

 

>alter system set curor_sharing=force;

 

最好的调优办法是在开发编写代码的时候。

 

1）

>conn scott/tiger

>alter session set sql_trace=true;

>select * from emp where empno=7788;

>select * from emp where empno=7520;

>select * from emp where empno=7369;

>alter session set sql_trace=false;

 

>host tkprof $ORACLE_BASE/admin/orcl/udump/***.trc tk1.txt explain=scott/tiger sys=no

                                每条语句都需要分析执行。

 

2）退出SCOTT再进去 绑定变量-软分析，第一次生成执行计划，之后同样需要语法语意检查，但不生成执行计划，利用前一次的执行计划。

>conn scott/tiger

>alter session set sql_trace=true;

>var x number

>exec :x=7788;

>select * from emp where empno=:x;

>exec :x=7521;

>select * from emp where empno=:x;

>exec :x=7369;

>select * from emp where empno=:x;

>alter session set sql_trace=false;

 

>host tkprof $ORACLE_BASE/admin/orcl/udump/***.trc tk1.txt explain=scott/tiger sys=no

 

3）使用过程。生成一次执行计划，执行多次。第一次生成执行计划，之后不再需要语法语意检查，直接利用前一次的执行计划。

>create or replace procedure emp_test(x number) is

 name emp.ename%type;

 num emp.empno%type;

 begin

 select ename,empno into name,num from emp where empno=x;

 dbms_output.put_line(name);

 end;

 /

>set serveroutput on;

>alter session set sql_trace=true;

>exec emp_test(7788);

>exec emp_test(7521);

>exec emp_test(7369);

>alter session set sql_trace=false;

 

>host tkprof $ORACLE_BASE/admin/orcl/udump/***.trc tk1.txt explain=scott/tiger sys=no

 

2、数据缓存区--buffer_cache，数据最好全部在内存里，性能就会越好。减少I/O，减少物理读。

命中率计算：

1）、1-（physical reads）/(consistent_gets+db_block_gets获取的块)逻辑读

>select name,value from v$sysstat

Physical reads—从数据文件中读取的块

Consistent_gets—一致性读（select）

Db_block_gets—从缓冲中获取的新块(update,delete,insert)

2）、

>select name,physical_reads,db_block_gets,consistent_gets,

 1-（physical reads）/(consistent gets+db block gets) hitratio

 from v$buffer_pool_statistics;

 

db_cache_size

db_keep_cache_size  --不会换出去

db_recycle_size      --很少用的块

 

牵扯到一个数据块的问题。行迁移与行链接。

 

>conn scott/tiger

>create table m(c varchar2(20));

>begin

 for i in 1..5000 loop

 insert into m values('a');

 end loop;

 commit;

 end;

>desc user_tables;

>analyze table m compute statistics;

>select table_name,chain_cnt,num_rows from user_tables; --chain_cnt有没有行链接

 

>update m set c='aaaaaaaaa';

>commit;

>analyze table m compute statistics;

>select table_name,chain_cnt,num_rows from user_tables; 有大量链接。

 

解决办法：导入导出，移动表.但对于大表怎么解决呢？只将链接的部分移出去

>@$ORACLE_HOME/rdbms/admin/utlchain  --产生一个链接表chained_rows

>select table_name from user_tables;  chained_rows

 

>analyze table m list chained rows;  --将有链接的行放进chained_rows表

>create table m_chain as select mm.* from m mm,chained_rows cr where mm.rowid=cr.head_rowid;

                               --创建一张表m_chain，将m表里有链接的行放进去。

>delete m where rowid in(select head_rowid from chain_rows); --将m表里有链接的行删除掉。

>insert into m select * from m_chain;          --将去掉了链接的行重新插入m表。

>commit;

 

>analyze table m compute statistics;

>select table_name,chain_cnt,num_rows from user_tables;

 

 

3、log_buffer --一般不用怎么调3-5m就行

v$sgastat

v$session_wait  (log buffer space event) 满了就要稍微增大。

v$sysstat(redo buffer allocation entries,redo entries)

 

 

4、PGA--主要针对排序，特别是在数据仓库里。不用到临时表空间排序的情况是最优的情况。一般老说80%SGA,20%PGA，如果是数据仓库相反。

重要参数：

 

parameter：

control_files

db_cache_size

pga_aggregate_target

shared_pool_size

sga_max_size

optimizer_mode

optimizer_features_enable

curor_sharing

optimizer_index_cost_adj

query_rewrite_enable

db_file_multiblock_read_count

statistics_level

workarea_size_policy

open_cursors  --用户能打开的最多游标数。

db_block_size

undo_management

undo_tablespace

undo_retention

 

v$sqlarea --disk_reads表示某条语句在读磁盘块数量。sql_text，cpu_time

 

分区表：可以放在不同的表空间里。

 

索引组织表：将索引和表组织在同一张表里。第一列主键已排序，第二列为rowid。

 

簇表：多用在两张作链接的表里。将两张表管理的数据放在同一数据块里。

 

物化视图：有真正的数据，主要用在统计繁琐的事件上。

 

 

一、锁机制。写不阻塞读，读不阻塞写。

多用户情况下很重要。数据库最关键的地方就在锁机制。

锁的分类：

1、内存锁：保护数据库的  排他锁和共享锁

 1）数据锁（DML）-oracle内部自动管理,需要排队等待。

   行级锁TX：

   表级锁TM：--防止其他的DDL语句。

mode: 0-none 1-null 2-row shared 3-row exclusive（防止DDL） 4-share 5-shared row exclusive  6-exclusive

 

手工锁：lock table ..in ..mode

      select ...for update

 

查看的视图：v$lock  v$locked_object  dba_waiter dba_blockers

 

实践：

A、

session1:scott

>create table t(c1 number);

>insert into t values(3);  --不提交，锁定。当提交后会释放锁。

 

sys:

>select * from v$lock; --sid ty，id1,lmode

 

session2:scott

>alter table t add (c2 date)

 

B、manual lock

>lock table t in exclusive mode;  --加独占锁。

sys:

>select * from v$lock;

>select * from dba_waiters;

>select * from dba_blockers;  --哪个用户SID锁住表不让其他用户操作。

>select * from v$locked_object;

 

C、

session 1:

>select * from t for update;  --加上表级锁和行级锁。

session 2

>select * from t for update nowait;--若t上有锁，不等待，马上报错。

>select * from t for update wait 10;  --等待10S，若锁还没释放，报错。                    

 

D、解决insert时死锁的问题

sesson1

>alter table t add constraint pk_t primary key(c);

>insert into t values(1+(select max(c) from t),null);

 

session2:

>insert into t values(1+(select max(c) from t),null); --一直在等待

 

解决办法：

>create sequence seq_t start with 4;

>insert into t values(seq_t.nextval,null);

 

 2）字典锁（DDL）--无须等待，直接生效。

 

2、latch锁：管SGA

 

死锁：oracle会自动选择一个执行解决死锁。会记录到告警日志中。

session1

>update emp set sal=sal+100 where empno=7369;

 

session2:

>update emp set sal=sal+100 where empno=7900;

 

session1

>update emp set sal=sal+100 where empno=7900;

 

session2

>update emp set sal=sal+100 where empno=7369;

 

杀掉会话进程：

>alter sysetem kill session 'sid','serial#';

 

 

二、join--表连接，此处主要是指执行语句时的连接

nested-loop:把第一个表（也叫外表驱动表）先读进内存，再读另一张表，第二张根据第一张数据分别取。所有连接都可以使用。

               有索引用此方法比较好。

merge-sort:合并排序。只能在相等连接情况能使用。

hash-join：把一张表取出来进行散列，然后匹配连接。只能在相等连接情况能使用。

 

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/27064837/viewspace-732992/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/27064837/viewspace-732992/