oracle数据库优化实验手册

oracle数据库优化

实验一：oracle安装与环境准备

一、oracle 11g安装

操作系统：windows XP/windows 7

版本：oracle 11g

http://pan.baidu.com/s/1gd9w4rp

二、oracle 11g环境配置

将OracleDBConsoleXX，OracleJobSchedulerXX，XXTNSListener和OracleServiceXX设为手动。当需要时，再手动启用。

创建新用户，便于实验的开展。如果是实验数据库，不影响其他用户使用，当然也可以使用sys用户。

sql> create user gmcc identified by gmcc;
sql> grant connect,dba to gmcc;

设置SQL*plus (oracle 11g后autotrace无需安装)

sql> set autotrace on

三、建立实验用表

建立一个BIG_TABLE用于整个过程的实验。表通过all_objects这张表生成。为了在实验中更好的验证，建议大家在这个表中的数据达到100万-500万。数量级接近我们地市客户数量。

create table big_table
as
select rownum id, a.* from all_objects a 
where 1=0;

设置为nologging，用以提高性能。

alter table big_table nologging;

输入参数1为希望的行数，往表中多次插入数据, 直到数据至少几百万行：

declare
    l_cnt number;
    l_rows number:=&1;
begin
    insert /*+ append */ into big_table
    select rownum, a.* from all_objects a 
    where rownum <= l_rows;

    l_cnt :=sql%rowcount;
    commit;

    while(l_cnt < l_rows)
    loop
        insert /*+ append */ into big_table
        select rownum+l_cnt, OWNER, OBJECT_NAME, SUBOBJECT_NAME, 
        OBJECT_ID, DATA_OBJECT_ID, OBJECT_TYPE, CREATED, LAST_DDL_TIME, TIMESTAMP, 
        STATUS, TEMPORARY, GENERATED, SECONDARY, NAMESPACE, EDITION_NAME
        from big_table
        where rownum <= l_rows - l_cnt;
        l_cnt:=l_cnt+sql%rowcount;
        commit;
    end loop;
end;
/

最后增加关键字的约束，同时做一下表的统计。

alter table big_table add constraint
big_table_pk primary key(id);

exec dbms_stats.gather_table_stats(user, 'BIG_TABLE', estimate_percent=>1);

建立一个表common_table，用于后面关联查询

create table common_table
as
select rownum id, a.* from all_objects a where object_type='TABLE';

alter table common_table add constraint
common_table_pk primary key(id);

exec dbms_stats.gather_table_stats(user, 'COMMON_TABLE', estimate_percent=>1);

到此，课程的准备工作完成！

实验二：oracle 的结构体系

一、oracle实例与数据库

实例包含进程和内存。oracle的进程可以通过v$process这张表进行查询。而内存占用参数可以查询memory_target(oracle 11g)或者sga_target和pga_target(oracle 10g)来查看。

select pid, pname, round(pga_used_mem/1024/1024,2)||'MB' 
from v$process;

数据库包括一系列操作系统的文件，其中最重要的是数据文件。可以通过查询v$datafile这张表进行查看。

select name, blocks from v$datafile;

二、pga, sga的查看与配置

pga的状态，通过查看v$pgastat这张表。在oracle 10g版本下，需要配置参数pga_target来设置；而在oracle 11g的版本下，只需要配置memory_target这个参数。

select name||' | '||round(value/1024/1024)||'MB' from v$pgastat;

sga的状态，通过查看v$sgastat这张表。同样在oracle 11g下面，通过配置memory_target这个参数来设置。

select * from v$sgastat;

三、内存参数设置。

内存参数主要通过pga_aggregate_target, sga_target, sga_max_size, memory_target 以及 memory_max_target等参数。这些参数都是写在spfile文件中的，所以参数的修改也是在spfile中。

SQL> show parameter sga_max_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_max_size                         big integer 512M

SQL> alter system set sga_target=600M;
alter system set sga_target=600M
*
第 1 行出现错误:
ORA-02097: 无法修改参数, 因为指定的值无效
ORA-00823: 指定的 sga_target 的值大于 sga_max_size

设置sga_target大于sga_max_size出现错误，要增大sga_target必须增大sga_max_size
sga_max_size为静态的参数，只能修改spfile的值

修改spfile文件中的sga_max_size后，系统运行的sga_max_size仍然为512M，需要重启数据库，参数才会修改。

alter system set sga_max_size=600M scope=spfile;

系统已更改。

SQL> show parameter sga_max_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_max_size                         big integer 512M

也可以通过查看pfile并修改达到更改参数的目的

create pfile='e:/newpfile.ora' from spfile;

文件已创建。

create spfile from pfile=‘d:/newpfile.ora’

四、oracle的事务处理方式和并发方式

思考以下问题：

数据库并发读写应当如何实现？实现有什么困难？
oracle采取什么方式提高数据库的并发能力？
oracle的行级锁有什么特点？为什么要采用这种锁？
多版本控制是什么？与commit和rollback的关系？
redo和undo的作用分别是什么？

查看和修改redo日志文件。

查看日志文件组，如果发现日志文件过小，可以进行调整。

select group#, sequence#, members, bytes, status, archived from v$log;

新增文件组4

alter database add logfile 
group 4 (‘e:/oracle/product/10.0.2/oradata/ORCL/redo41.log') size 200m;

切换重做日志

alter system switch logfile;

实验三：oracle的SQL调优

一、oracle语句的执行计划

设置终端输出执行时间：

set timing on

为了每次执行过程的公平，执行前都将缓存给清理干净。

alter system flush buffer_cache;

分别执行一下以下两个过程, 请分别记录一下执行时间：
过程1：

begin
  for i in 1..100000
  loop
      execute immediate 
    'select object_name from big_table where id=:x' using i;
  end loop;
end;
/

过程2：

begin
  for i in 1..100000
  loop
      execute immediate 
    'select object_name from big_table where id='||i ;
  end loop;
end;
/

思考一下问题：

两个语句的主要作用是什么？
两个语句的区别在哪里？为什么执行过程时间差别这么大？
oracle的SQL调优，需不需要理解oracle语句执行过程呢？
通过这个案例，在应用服务的开发中，要避免的陷阱是什么？

我们来看一下一个典型的sql语句的执行过程。

set autotrace on

select a.object_name 
from 
big_table a inner join common_table b 
on ( a.object_id=b.object_id)
where a.id=100;

执行计划如何分析？
统计结果如何分析？
如果上述语句没有where子句，执行结果又会变成什么？

二、索引及连接方式

通过实验比较B树索引与位图索引的性能差别。

像前面准备的时候一样，通过all_objects这个视图来创建两个表btree_table, bitmap_table，分别用B树索引和位图索引来对这两个表中的OBJECT_TYPE, OWNER, NAMESPACE分别进行索引。

生成btree_table和bitmap_table的语句。
生成btree_table的B树索引的语句。
生成bitmap_table的位图索引的语句。
比较当运行
select * from btree_table where OBJECT_TYPE='TABLE' and OWNER='SYS' and NAMESPACE=1;
和
select * from bitmap_table where OBJECT_TYPE='TABLE' and OWNER='SYS' and NAMESPACE=1;
执行的差别。
在什么情况下使用位图索引呢？

三、oracle语句优化

下面那些语句会使用到btree_table表的索引？

语句一：

select * from btree_table where owner||'.'||object_type='SYS.TABLE';

语句二：

select * from btree_table where owner like '%S';

语句三:

select * from btree_table where owner like 'S%';

语句四:

select * from btree_table where object_type like 'TABLE%';

语句五：

select * from btree_table where object_type like '%TABLE';

语句六：

select * from btree_table where namepace-10>0;

语句七：

select * from btree_table where namepace>10;

语句八：

select * from btree_table where namepace<>10;

语句九：

select * from btree_table where namepace>10 or namepace<10;

四、生成awr报告

SQL> @?/RDBMS/ADMIN/awrrpt.sql （注：?是ORACLE_HOME）
输入 report_type 的值:html     （注：确定报告的格式）
输入 num_days 的值:1     （注：选择快照的天数）
输入 begin_snap 的值:425     （注：起始快照）
输入 end_snap 的值:427     （注：结束快照）
输入 report_name 的值:d:\scmis-awr-2011-10-29.html     （注：报告生成的名称和位置）

五、生成addm报告

SQL> @?/RDBMS/ADMIN/addmrpt.sql （注：?是ORACLE_HOME）

实验四：oracle物理存储优化

一、行迁移的产生和清除

建一个表，用来做行迁移的实验。

create table chain_table(id number, name varchar(4000));

往表中插入2000行数据

declare
    i number;
    begin
        for i in 1..2000 
        loop
        insert into chain_table values (i, rpad('abc', 100));
        end loop;
    commit;
end;
/

更新表，使得字符串都变成2000个字符

update chain_table set name=rpad(‘123’, 2000, '*');
commit;

强制回写到数据库中。并且清除缓存。

alter system checkpoint;
alter system flush buffer_cache;

查询行链接，首次执行，运行脚本utlchain.sql生成相关分析用表。

@?/rdbms/admin/utlchain.sql

开始分析行链接情况。

analyze table chain_table list chained rows;
select * from CHAINED_ROWS;

方法一：清除行链接, 最简单的方法就是复制一下表，然后清除数据，再复制进去。对于没有外键限制的表，可以采取这种方式。

create table chain_table_tmp as select * from chain_table ;
truncate table chain_table
insert into chain_table select * from table_name_tmp

方法二：采取标准的处理方法，使用行移动的方法来解决。

alter table utlrp.sql enable row movement ; 
alter table chain_table shrink space compcat;
alter table chain_table shrink space;

最后运行命令，重新编译无效对象。

@?/rdbms/admin/utlrp.sql

二、多种数据库表的应用

表压缩的应用

使用前面准备的bigtable复制一个一样的表，使用的表压缩技术，然后进行存储的比较。

create table bigtable_1 compress as select * from bigtable;

收集表的信息：

sql>execute dbms_stats.gather_table_stats(ownname=>'SYS', tabname=>'BIGTABLE');
sql>execute dbms_stats.gather_table_stats(ownname=>'SYS', tabname=>'BIGTABLE_1');

统计一下空间对比：

select table_name||'|'||blocks||'|'||num_rows 
from dba_tables 
where table_name like ‘BIGTABLE%’;

空间差异是多少？

聚簇表

构建一个索引聚簇表，使用列owner varchar(30)作为聚簇列，复制视图sys.all_tables和sys.all_views分别建立在这个聚簇上。

create cluster mycluster( owner varchar(30)) size 128;

create table mytables
(  
    owner varchar(30), 
    table_name varchar(100)
)cluster mycluster(owner);

create table myview
(
    owner varchar(30), 
    view_name(100)
)cluster mycluster(owner);

insert into mytables(owner, table_name)
select owner, table_name from sys.all_tables;

insert into myview(owner, view_name)
select owner, view_name from sys.all_views;

为了对比，我们建立两个没有通过聚簇关联的表。并且建立索引，我们看看结果。

create table mytable_1 as select * from mytables;
create table myview_1 as select * from myview;
create index mytable_1_index on mytable_1(owner);
create index myview_1_index on myview_1(owner);

强制oracle写磁盘，并且清空缓存。

alter system checkpoint;
alter system flush buffer_cache;

对比看看关联结果，耗费的差异情况。

set autotrace traceonly
select * from myviews a , mytables b where a.owner=b.owner and a.owner=‘HR';
select * from myview_1 a , mytable_1 b where a.owner=b.owner and a.owner =‘HR’;

IOT表

通过all_objects的OBJECT_ID, OBJECT_NAME建立一个IOT表。

create table iot_table
( 
    object_id number,
    object_name varchar(30),
    primary key(object_id)
) organization index;

insert into iot_table select object_id, object_name from all_objects;

另外，构造一个一样的普通表，对比一下看看。

create table short_table
( 
    object_id number,
    object_name varchar(30),
    primary key(object_id)
) as select * from iot_table;

写磁盘，请缓存，比较一下看看。

alter system checkpoint;
alter system flush buffer_cache;

set autotrace on
select * from iot_table where object_id=23;
select * from short_table where object_id=23;

三、表分区机制与应用

1. 区间分区例子

建立一个表，按时间进行区间分区。

create table range_example
(    rang_key_column date NOT NULL,
     data  varchar(20)
)
partition by range(rang_key_column)
(   
    partition part_1 values less than (to_date('2010-01-01', 'yyyy-mm-dd')) compress,
    partition part_2 values less than (to_date('2011-01-01', 'yyyy-mm-dd')) compress,
    partition part_max values less than (maxvalue)
) ;

将最后一个分区再切分成两个分区。

alter table range_example 
split partition part_max at (to_date('2012-01-01', 'yyyy-mm-dd')) 
into ( partition part_3, partition part_max);

2. 散列分区例子

建立一个表，以散列值进行分区

Create table hash_example
(    hash_key_column date NOT NULL,
     data  varchar(20)
)  Partition by hash(hash_key_column)
(   partition part_1,
    partition part_2 
);

增加一个散列分区。

alter table hash_example add partion part_3;

3. 列表分区例子

根据列表内容分成多个区。

create table list_example
(   state_name varchar(32),
    data varchar(20)
) partition by list(state_name)
(  partition part_1 values('guangdong‘, ‘guangxi’),
   partition part_2 values('shanghai'),
   partition part_default values(default)
)
alter table list_example split partition part_default values('beijing') into
(  partition part_3, partition part_default);

4. 间隔分区例子

create table audit_trail 
( 
    ts  timestamp, 
    data  varchar2(30) 
)partition by range(ts)  
interval(numtoyminterval(1, 'month')) 
store in (users, example) 
(partition p0 values less than(to_date('01-01-1900')));

当插入一行数据的时候，系统自动增加一个区间。

insert into audit_trail(ts, data) 
values ( to_timestamp(’27-feb-2010’, ‘dd-mon-yyyy’), ‘xx’);

5. 引用分区例子

建立一个分区父表。

create table orders 
(
    order#  number primary key, 
    order_date date, 
    data varchar2(30) 
) enable row movement partition by range( order_date)
(  
    partition part_2009 values less than(to_date(’01-01-2010’, ‘dd-mm-yyyy’)) ,
     partition part_2010 values less than(to_date(’01-01-2011’, ‘dd-mm-yyyy’))  
);

insert into orders values(1, to_date(’01-jun-2009’, ‘dd-mon-yyyy’), ‘xxx’);
insert into orders values(1, to_date(’01-jun-2010’, ‘dd-mon-yyyy’), ‘xxx’);

建立一个子表的引用分区。

create table order_line_items
( 
    order# number, 
    line# number, 
    data varchar2(30), 
    constraint c1_pk primary key(order#, line#), 
    Constraint c1_fk_p foreign key(order#) references orders
) 
    enable row movement partition by reference(c1_fk_p) ;

向分区子表插入数据。

insert into order_line_items values(1, 1, ‘yyy’);
insert into order_line_items values(2, 1, ‘yyy’);

引用分区的便利之处在于：当你将父表中的分区删除掉，那么oracle也会自动级联传递到子表中，同时删除子表对应的分区。这样避免了由于外键约束而不能删除的问题。

alter table orders drop partition part_2009 update global indexes;

6.组合分区的例子

create table composite_example 
( 
    range_key_column  date,
      hash_key_column    int,
      data  varchar2(20)
) partition by range ( range_key_column)
    subpartition by hash( hash_key_column) subpartitions 2
    (  
        partition part_1  values less than (to_date('01/01/2008', 'dd/mm/yyyy'))
        ( 
            subpartition part_1_sub_1,  
            subpartition part_1_sub_2 
        ),
           partition part_2  values less than (to_date(’01/01/2008’, ‘dd/mm/yyyy’))
        ( 
            subpartition part_2_sub_1,  
            subpartition part_2_sub_2 
        )
    )
)

7.实验

内容：

请将big_table表按照1-4类分区方法分别进行分区。写出分区的语句，并说明分区的目的是什么。
根据引用分区和组合分区的作用，将工作中遇到的大表进行分区，写出分区的语句，并说明分区的目的是什么。

四、索引分区

1. 局部索引分区

create table partitioned_table
( 
    a int, 
    b int, 
    data char(20)
) partition by range(a)  
(   partition part_1 values less than(2) tablespace p1,
    partition part_2 values less than(3) tablespace p2
);

局部前缀索引

create index local_prefixed on partitioned_table (a, b) local;

局部非前缀索引

create index local_nonprefixed on partitioned_table (b) local;

2. 全局索引分区

create table partitioned
(
    timestamp date, 
    id int
) partition by range(timestamp)  
(   
    partition part_1 values less than (to_date('01-01-2000', 'dd-mm-yyyy')),
    partition part_2 values less than (to_date('01-01-2001', 'dd-mm-yyyy')),
    partition part_3 values less than (to_date('01-01-2002', 'dd-mm-yyyy')),
    partition part_4 values less than (to_date('01-01-2003', 'dd-mm-yyyy')),
);

create index partitioned_index on partitioned(id) global partiton by range(id)
(   partition part_1 values less than (1000),
    partition part_2 values less than (maxvalue)
);

3. 滑动窗口和索引

建立一个分区及全局索引，用以做滑动窗口。

create table partitioned
( 
    timestamp date, 
    id int 
)partition by range (timestamp)
( 
    partition fy_2004 values less than (to_date('01-01-2005', 'dd-mm-yyyy')),
    partition fy_2005 values less than (to_date('01-01-2006', 'dd-mm-yyyy'))  
);

create index partitioned_idx_global on partitioned(timestamp) global;

利用all_object 生成样例数据。

insert into partitioned partition(fy_2004) 
select to_date('31-12-2004','dd-mm-yyyy')-mod(rownum,360), object_id 
from all_objects;

insert into partitioned partition(fy_2005) 
select to_date('31-12-2005','dd-mm-yyyy')-mod(rownum,360), object_id
from all_objects;

建立空表fy_2004，用于存放旧的数据。

create table fy_2004 (timestamp date, id int);
create index fy_2004_idx on  fy_2004(timestamp date);

建立表fy_2006，里面存放新的数据。下面进行数据的交换.

create table fy_2006(timestamp date, id int)
select to_date('31-12-2006', 'dd-mm-yyyy') – mod(rownum, 360), object_id
from all_objects;

create index fy_2006_idx on  fy_2006(timestamp date);

归档数据并清除旧分区

alter table partitioned 
exchange partition fy_2004 
with table fy_2004 
including indexes without validation update global indexes;

alter table partitioned drop partiton fy_2004 update global indexes

前面新数据已经装载，这里新增空白分区并交换数据和索引。

alter table partitioned 
    add partition fy_2006 less than ( to_date('01-01-2007', 'dd-mm-yyyy'));

alter table partitioned 
    exchange partition fy_2006
    with table fy_2006 
    including indexes without validation
    update global indexes ;

4. 案例题一

表分区在数据仓库/决策系统中，分区是一个强大的工具，它一方面使你的数据存储更加方便有效，同时通过合理的查询使效率提升。在地市的实际工作中，我们常常使用表分区来解决一些存储量巨大的历史数据。
我们碰到了一个难题，我们有一个数据仓库，数据量非常大，月均数据量超过2G。总体数据量已超过2T，数据量过高造成运行慢，备份能力不足。我们应该如何设计这个系统呢？

地市的社会酬金系统，每个月都要存储计酬的原始数据，计酬结果数据等信息。
该数据库每月新增的各类数据超过2G。并且数据要保留超过5年以上。
每月计酬完毕以后，数据需要提供给社会渠道前台系统查询，了解清楚计酬过程，提升出酬透明度。

问题：

数据库应该怎么进行分区？
数据量大，要保留时间长，在服务器存储不足的情况下，如何节约存储空间？
如果这个数据库是你来规划，应该采取什么方式，以及日常应该如何维护？

5. 案例题二

某地市的触点营销平台，每月上架大量的触点数据。因为触点数据使用周期比较长，客户数量多，所以数据量较大。1月-4月数据量已经超过上亿条。开发人员按照上架时间对触点数据进行分区，并构造全局索引，发现数据批量导入的速度仍然非常慢。

为了导入的速度，他们采取的是先删除索引，导入数据，然后重建索引的办法。
由于表中数据量较大，导致凌晨生成全局索引时，耗时时间较长。
历史数据过多，导致表数据占用磁盘空间过大。

问题：

数据库数据装载的方式有没问题？这样做的好处和坏处在哪里？
触点数据保留价值不高，应该如何进行处理？
如果这个数据库是你来规划，应该采取什么方式，以及日常应该如何维护？

实验五：oracle备份与恢复

一、flashback(误操作恢复)

Flashback是oracle 9i以后引入的查询，用于在需要时，依赖undo来查询以前某个版本的数据。
Oracle 11g提供多种flashback的操作，用于恢复误操作的数据。

flashback的特点：

flashback database 用于恢复数据库到某一个时点
flashback table 用于恢复误drop的表
flashback query 实现行级恢复
flashback table 恢复表到先前状态

检查是否启用了flash recovery area

show parameter db_recovery_file

检查是否启用了自动归档模式

archive log list;

启用自动归档模式

startup mount;
alter database archivelog;
alter database open

启用flashback_on

alter database flashback on

查看scn与时间对应关系。

select scn, to_char(time_dp, 'yyyymmdd-hh24:mi:ss') 
from sys.smon_scn_time;

恢复到时间点或者scn

flashback database to 
timestamp to_timestamp('09-10-14 14:37:05′,'yy-mm-dd hh24:mi:ss');

flashback database to scn 1385839

误删除了一个表T

drop table gmcc.t;

闪回表T

flashback table gmcc.t to before drop;

误删除了一个表中的数据

delete from gmcc.t where x>1;
commit;

通过flashback仍然可以查询之前的版本数据, 然后通过插入或者覆盖的方式来恢复表的行数据。

select * from gmcc.t 
as of timestamp to_timestamp('20150422 10:55:00', 'yyyymmdd hh24:mi:ss');

flash table

允许行移动

alter table gmcc.t enable row movement;

闪回表数据

flashback table gmcc.t 
to timestamp to_timestamp('20150422 10:55:00', 'yyyymmdd hh24:mi:ss');

为了避免恢复错误，可以查询一下操作日志，确认恢复到的时间。

select sql_text from v_$sql 
where sql_text like 'delete%gmcc.t%' 
order by last_active_time asc;

二、Imp/exp（数据迁移与恢复）

由于Impdp/expdp是在服务器端的导入导出，所以主要使用的区别是指明服务端路径。

create directory dpdata1 as '/home/oracle/dpdata1';
grant read, write on directory dpdata1 to gmcc;

导出的例子

$expdp gmcc/gmcc tables=mytable directory=dpdata1 dumpfile=mytable.dmp  \
job_name=MYTABLE_EXPORT

导入的例子

$impdp gmcc/gmcc tables=mytable directory=dpdata1 dumpfile=mytable.dmp  \
job_name=MYTABLE_EXPORT

三、Rman(标准的备份与恢复)

简单实用

连接数据库，进入rman

$ rman target /

查看参数

RMAN> show all;

查看备份情况

RMAN> list backup;

查看数据库的表空间和数据文件

RMAN> report schema;

简单的全备份，数据库datafile和spfile都会保存

RMAN>  backup database format ‘e:/whole_%d_%U’

启用autobackup，并备份controlfile

RMAN> configure controlfile autobackup on; 
RMAN> backup current controlfile

进入nomount的状态后，恢复controlfile

RMAN>startup nomount; 
RMAN>restore controlfile from autobackup;

进入mount的状态后，恢复数据库

RMAN>startup mount; 
RMAN>restore database;

备份策略

bak_inc0：周日进行数据库的全备份。

run {
        allocate channel ch1 type disk;
        backup as compressed backupset  incremental level 0
        format '/u01/oracle/bk/rmbk/incr0_%d_%U'
        tag 'day_incr0'
        database plus archivelog delete input;
        release channel ch1;
        }

bak_inc2：周一二，四五六，进行数据库的增量备份。

run {
        allocate channel ch1 type disk;
        backup as compressed backupset  incremental level 2
        format '/u01/oracle/bk/rmbk/incr2_%d_%U'
        tag 'day_incr0'
        database plus archivelog delete input;
        release channel ch1;
        }

bak_inc1：周三进行数据库的增量备份。

run {
        allocate channel ch1 type disk;
        backup as compressed backupset  incremental level 1
        format '/u01/oracle/bk/rmbk/incr1_%d_%U'
        tag 'day_incr0'
        database plus archivelog delete input;
        release channel ch1;
        }

脚本通过语句执行

rman target / log=/u01/oracle/bk/log/bak_inc0.log \
append cmdfile = /u01/oracle/bk/scripts/bak_inc0.rcv

在linux系统，通常是设置crontab来设置定期执行，而在windows系统中设置计划任务来执行。