五月南研oracle学习体会

本次到南京参加培训，主要是学习‘数据库性能调整’和‘Oracle SQL 优化’两方面的内容：

1    数据库性能调整

1.1   Oracle的调优方法论和解决方案

1.2   调优工具介绍

1.3   调优过程

2    Oracle SQL 优化

2.1   SQL优化基础

2.2   常用的优化方法

2.3   提升SQL效率的系统层手段

本次课程的主要是从数据库优化的方法论到具体数据库环境应用，给我们讲述了oracle一整套优化的思路和一些具体优化的方法和应用。使我原先散乱的知识点能够串起来，也更明白了自己应该加强哪方面的学习，研究。以下是我在这次培训中学到的东西，现在和大家分享下。

先了解的数据库调优的方法论

对于oracle数据库我们采用的是自上而下进行调优的方法。首先对于一个数据的优化应该是从这个数据创建开始的，数据库的目的和相关性能的要求才是驱动数据库调优的空间所在，因为我们知道这么一个现实，就是任何资源总是有限的，只有用最少的代价去获取最大的效益的才是我们调整资源的使用的目的所在；所以数据库的优化也应当是从数据库的设计开始的，数据设计应该达到的第三范式以上，设计才相对合理。一个良好的数据库结构才是整个系统系能体现的关键（真的以数据为基础的系统）。

其次就是要考虑在应用程序代码层上优化，对于以数据为基础系统在程序中多少会有涉及到程序控制数据访问的情况，而往往是应为程序员考虑不到等各种原因导致数据库访问的代码没有进行优化处理，往往会使得系统性能下降。因此程序书写范式，规则应该是要有统一。访问的字段，关联的方式，连接的字段都要进行合理的选择。

最后再考虑对数据库实例迚行调优，oracle数据库创建后就存在一个数据库实例，对实例的优化主要是人数据库在有限承载能力的主机上发挥出数据最优的性能。值有对有限资源的合理分配，才不会出现小马拉大车（动不了），大马拉小车（没发挥最大作用）的局面。

以上就是我们在进行数据库调优的方法和次序，这些调整对性能的影响是依次递减的，也就是说实例，外部环境的优化对系统性能的提高作用不是最主要的，同事代价也是最大的。

下面我们就要看看决定性能的几大因素，第一个因素是数据库的环境，主要是指数据库所处的主机情况，存储设备的情况和外设网络状况。我们通常会遇到这样一些问题，如服务器配置够高，内存不是问题等等。这些看起来似乎和小的差异，有的时候却成为数据性能差异症结处。在这里有个例子说明内存的重要性，我们假设有一条数据记录，同一时间有多个用户发出访问请求，要知道oracle为了保证数据库读写一致性的时候会将记录锁定，防止其他用户修改，当一个用户锁定记录后，其他用户的请求就会形成一个数据栓，在内存等待一个周期后在提请求，这样无形中就形成了内存的损耗，给内存带来压力，所以只有合理的内存分配才会是数据库性能得到很好的发挥。

第二个因素是实例/数据库，主要是RAC的分区，内存分配和I/O的分配问题。Oracle实例会在内存中开辟一块内存空间单独为oracle所有。要合理的分配这个空间是各个功能都得到很好的发挥。

第三个因素是应用层面上，主要是数据库架构、对象模式、SQL/PLSQL的设计调整问题。这也有类似的案例说明，如0.1秒和0.2秒差距很小。很多人可能会有这样的想法，的确这两个数值差距很小，但是对以数据库而言，每个记录差距0.1的话，就会是一个很大开销。因此在程序设计上应该注意这一个微小的差异。才能是数据库性能更高效。

数据库的性能不仅是更快，也意味着更稳定的质量和花销更低的成本。对于系统的性能的调优我们遵循的规则的设置合理的调优目标，在达到调优目标时停止。在现有系统中寻找最有潜力获得最大性能提升的点，一般我们要做的是发掘最长的等待事件和发掘消耗最大的服务时间的事件。找这些事件后，就可以对事件进行分析，调优，从而达到系统性能提升的目的。

再看看数据库调优工具的使用。

调优工具主要有：

l  基本工具类企业管理器，报警日志，跟踪文件，系统性能视图和表。

l  Statspack & AWR

l  ADDM (Automatic Database Diagnostic Monitor) 自动数据库诊断监视器。

企业管理器基本可以完成对数据管理工作，包括对性能的监控、调整，对数据库的管理和维护。（下图是本机数据EM界面）

报警日志是记录了数据库死锁，归档，超时等事件和信息，为错误定位和调优提供一个参考依据。

Statspack & AWR是传统的错误定位和调优工具，都具有读取数据库快照来分析数据库的工作情况的能力，AWR新增了包含ASH (Active Session History)，运行DBA基于历史时间段分析特定Session的等待事件，针对服务和针对历史信息的视图的统计信息，对Streams的特征统计。更全面的反应数据库性能问题。

数据库调优工具仅是提供调优和错误定位的参考信息，在这里就不一一做介绍，具体的使用方式可找相应参考书籍阅读。

最后看数据库调优调优的过程。

这里主要是讲述了调整数据库的具体过程，也就是调整方法方案的实际操作。首先要了解oracle的基本框架，主要是由SGA和几个必须的ORACLE后台进程几个部分组成（如下图）：

SGA(System Global Area 也称 Shared Global Area) 主要由以下几个部分组成：共享池(Shared Pool)，数据高速缓存区(Data Buffer Cache)，重做日志缓存区(Redo Log Buffer)，如下图：

Shared Pool的优化：包括Library cache和Data dictionarycache。这两个区域驻留的一些解析了的SQL语句，及数据字典。为了使我们的数据库运行效率更高，我们通常是希望类似的语句一次解析后，就一直使用着，不要每次都从新解析一次，以减少数据库消耗，从而提升性能。

针对Library cache的优化措施：

l  增加代码的重用性，注意代码的书写的一致性。

l  使用变量绑定，必要时设置CURSOR_SHARING = SIMILAR。

l  可以通过statspack和AWR report中的Load profile，Instanceefficiency，Top wait和Time model获得相关数据。

针对Data dictionarycache的优化措施：

l  Getmisses的总数不应该超过Gets的总数的15%，否则应该增加SHARED_POOL_SIZE。

Data Buffer Cache的优化：所有数据的访问都是经过这个区域的。也就是说程序要访问的数据必须先读到这个区域来，程序才可以使用。我们都清楚内存的读写速度远远大于硬盘读写速度，oracle要将数据库中的数据读到内存就很耗损性能的，我们有时候希望某些常访问的数据停留在这里，希望某些不常用的数据及时的清除等等，所有要合理的分配好这个区域。

三种Data buffer cache：

l  KEEP：保存最有可能被重用的对象，这里面的对象不会被换出，用DB_KEEP_CACHE_SIZE指定

l  RECYCLE：保存被重用的可能性小的对象，这里面的对象经常被换出，用DB_RECYCLE_CACHE_SIZE指定

l  DEFAULT：即普通的buffer cache，用DB_CACHE_SIZE指定

使用STORAGE从句指定对象被保存的Buffer cache

SQL> CREATE INDEX cust_idx … STORAGE （BUFFER_POOL_KEEP)；

SQL> ALTER TABLE customer STORAGE (BUFFER_POOL_RECYCLE);

除了内存方面的有话，还讲到了有关表空间的优化，数据块的有，数据结构的优化，还有就是数据库文件的优化，I/O优化等等。

通过了这一次全面的培训使我更了解了数据库优化工作的重要性，也了解到了目前广西现场数据员工作的重要性和任务的艰巨性，面对着这个已经运行了几年的数据库和这个结构复杂的系统，数据库慢慢显现出其性能的重要性，我相信有了这次指导，使我能够更全面的了解数据员工作的重点和自己今后要加强工作能力方向。也希望公司能够有更多的类似培训，以增强个人工作能力和工作效率。

                                                  杨    牧

                                                20##年6月3日

 

第二篇：oracle学习总结

1. oracle登录方式

答： (1) 进入dos窗口 （WINDOW+R，cmd）

(2) sqlplus /nolog

(3) connect sys/sys as sysdba;

2. oracle 创建表空间

create tablespace data01 datafile '/oracle/DATA02.dbf' size 2048M; create tablespace mpac datafile '/oracle/mpac1.dbf' size 1536M;

3. 删除表空间

DROP TABLESPACE data01 INCLUDING CONTENTS AND DATAFILES;

4. 创建用户 create user PERSONNEL_MANAGE identified by PERSONNEL_MANAGE; grant connect,resource to PERSONNEL_MANAGE;

5. 修改字段名

alter table student rename column major to majors ;

6. Oracle中数据库表的导出导入

7. Oracle中编写存储过程.

（1）创建表

create table testTB

( id varchar2(30), name varchar2(30) )

（2）在表的基础上创建存储过程

create or replace procedure testa(para1 in varchar2,para2 in varchar2) as begin insert into testTB(id,name)values(para1,para2); end testa;

（3） 调用存储过程

call testa(1,'10');

execute proc_test('dinya_test','id','number(8) not null', 'name', 'varchar2(100)');

8 游标的使用

create or replace procedure dept_procedure1(a in varchar2,v_a out dept%rowtype) is

--游标声明

cursor c_de(a in varchar2) is select* from dept where dname=a; begin open c_de(a); loop

fetch c_de into v_a; exit when c_de%notfound; end loop; close c_de; dbms_output.put_line('deptno:'||v_a.deptno); dbms_output.put_line('dname:'||v_a.dname); dbms_output.put_line('loc:'||v_a.loc); end;

调用存储过程（包含游标）：

declare

v_row dept%rowtype; begin dept_procedure1('lucy',v_row); end;

待验证：Oracle 如何扩展表空间 第一步：查看表空间的名字及文件所在位置：

select tablespace_name, file_id, file_name,round(bytes/(1024*1024),0) total_space from

dba_data_files order by tablespace_name

第二步：增大所需表空间大小

alter database datafile '表空间位置'resize 新的尺寸。

例如：alter database datafile '\oracle\oradata\anita_2008.dbf' resize 4000m。

第三步：设置表空间自动扩展：alter database datafile '\oracle\oradata\anita_2008.dbf' autoextend on next 100m maxsize 10000m

SYS用户在CMD下以DBA身份登陆：

在CMD中打sqlplus /nolog

然后再

conn / as sysdba

//创建临时表空间

create temporary tablespace user_temp

tempfile 'D:\oracle\oradata\Oracle9i\user_temp.dbf'

size 50m

autoextend on

next 50m maxsize 20480m

extent management local;

//创建数据表空间

create tablespace test_data

logging

datafile 'D:\oracle\oradata\Oracle9i\user_data.dbf'

size 50m

autoextend on

next 50m maxsize 20480m

extent management local;

//创建用户并指定表空间

create user username identified by password

default tablespace user_data

temporary tablespace user_temp;

//给用户授予权限

grant connect,resource to username;

//以后以该用户登录，创建的任何数据库对象都属于user_temp 和user_data表空间，

这就不用在每创建一个对象给其指定表空间了

撤权：

revoke 权限... from 用户名;

删除用户命令

drop user user_name cascade;

建立表空间

CREATE TABLESPACE data01

DATAFILE '/oracle/oradata/db/DATA01.dbf' SIZE 500M

UNIFORM SIZE 128k; #指定区尺寸为128k,如不指定，区尺寸默认为64k

删除表空间

DROP TABLESPACE data01 INCL ING CONTENTS AND DATAFILES;

一、建立表空间

CREATE TABLESPACE data01

DATAFILE '/oracle/oradata/db/DATA01.dbf' SIZE 500M

UNIFORM SIZE 128k; #指定区尺寸为128k,如不指定，区尺寸默认为64k

二、建立UNDO表空间

CREATE UNDO TABLESPACE UNDOTBS02

DATAFILE '/oracle/oradata/db/UNDOTBS02.dbf' SIZE 50M

#注意：在OPEN状态下某些时刻只能用一个UNDO表空间，如果要用新建的表空间，必须切换到该表空间:

ALTER SYSTEM SET undo_tablespace=UNDOTBS02;

三、建立临时表空间

CREATE TEMPORARY TABLESPACE temp_data

TEMPFILE '/oracle/oradata/db/TEMP_DATA.dbf' SIZE 50M

四、改变表空间状态

1.使表空间脱机

ALTER TABLESPACE game OFFLINE;

如果是意外删除了数据文件，则必须带有RECOVER选项

ALTER TABLESPACE game OFFLINE FOR RECOVER;

2.使表空间联机

ALTER TABLESPACE game ONLINE;

3.使数据文件脱机

ALTER DATABASE DATAFILE 3 OFFLINE;

4.使数据文件联机

ALTER DATABASE DATAFILE 3 ONLINE;

5.使表空间只读

ALTER TABLESPACE game READ ONLY;

6.使表空间可读写

ALTER TABLESPACE game READ WRITE;

五、删除表空间

DROP TABLESPACE data01 INCL ING CONTENTS AND DATAFILES;

六、扩展表空间

首先查看表空间的名字和所属文件

select tablespace_name, file_id, file_name,

round(bytes/(1024*1024),0) total_space

from dba_data_files

order by tablespace_name;

1.增加数据文件

ALTER TABLESPACE game

ADD DATAFILE '/oracle/oradata/db/GAME02.dbf' SIZE 1000M;

2.手动增加数据文件尺寸

ALTER DATABASE DATAFILE '/oracle/oradata/db/GAME.dbf' RESIZE 4000M;

3.设定数据文件自动扩展

ALTER DATABASE DATAFILE '/oracle/oradata/db/GAME.dbf AUTOEXTEND ON NEXT 100M

MAXSIZE 10000M;

设定后查看表空间信息

SELECT A.TABLESPACE_NAME,A.BYTES TOTAL,B.BYTES USED,

C.BYTES FREE,

(B.BYTES*100)/A.BYTES "% USED",(C.BYTES*100)/A.BYTES "% FREE"

FROM SYS.SM$TS_AVAIL A,SYS.SM$TS_USED B,SYS.SM$TS_FREE C

WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND

A.TABLESPACE_NAME=C.TABLESPACE