oracle优化使用命令，记录到这（我也忘记哪来的）

郤诗蕊

登陆
sqlplus  <用户名>[/<密码>][@<服务名>] as sysdba
sqlplus sys/huhao@sa as sysdba
set oracle_sid=sa
sqlplus / as sysdba

alter system
alter system命令用来修改Oracle的一些参数，我们修改Oracle的内存配置时就要用到它。
alter system 有一个scope选项，它有三个可选值：memory，spfile，both。
memory:只改变当前实例的参数，如果重启Oracle，则会恢复到修改前的值。
spfile:只改变spfile的参数，在Oracle重启后会使用修改后的值。spfile是指Oracle启动时用到的配置文件，一些参数都保存在这个文件里，Oracle在启动时读取这个文件并进行相应的初始化设置。
both:改变实例及spfile的参数。
Oracle的总体结构
每 个Oracle数据库都是由Oracle Instance(实例)与数据库(数据文件，控制文件、重做日志文件)组成，其中所谓的实例就是用户同数据库交互的媒介，用户通过与一个实例相连来操作 数据库。而实例又是由统一的内存结构(SGA，PGA，UGA)和一批内存驻留进程组成。实例在操作系统中用ORACLE_SID来标识，在Oracle 中用参数INSTANCE_NAME来标识，它们两个的值是相同的。数据库启动时，系统首先在服务器内存中分配系统全局区(SGA)，构成了Oracle 的内存结构，然后启动若干个常驻内存的操作系统进程，即组成了Oracle的进程结构。内存区域和后台进程合称为一个Oracle实例。
SGA
系统全局区--System global area(SGA) 是一组共享的内存结构，它里面存储了oracle数据库实例（instance）的数据和控制文件信息。如果有多个用户同时连接到数据库，他们会共享这一 区域，因此SGA也称之为Shared Global Area。当数据库实例启动时，SGA的内存被自动分配;当数据库实例关闭时，SGA内存被回收。
SGA是占用内存最大的一个区域，同时也是影响数据库性能的重要因素。
SGA主要包括以下几部分：
 共享池
 数据缓冲区
 大型池
 Java池
 日志缓冲区
上面几部分内存加起来，就是SGA内存的总和。其中比较重要的是共享池和数据缓冲区，后面将会重点讲解它们。
查看SGA
SGA是可以动态调整大小的，也 就是说调整其大小是不用shutdown数据库的。在初始化参数中设置可以设置sga_max_size这个参数，当SGA的各部分的和要大于设置的 sga_max_size的参数的时候，设置的sga_max_size将会被忽略掉，而是将各部分的大小相加。当sga_max_size的大小大于各 部分的大小相加时，会使用sga_max_size的参数。
SGA是占用内存最大的一个区域，同时也是影响数据库性能的重要因素。
通过下面的命令来查看SGA：
show parameter sga;

共享池
共享池是对SQL，PL/SQL程序进行语法分析，编译，执行的内存区域。
它包含三个部分：
 库缓冲区(Library Cache)包含SQL，PL/SQL语句的分析码，执行计划；
 数据字典缓冲区(Data Dictionary Cache)表，列定义，权限；
 用户全局区(Usr Global Area)用户MTS会话信息。
这三个部分都不可单独定义大小，必须通过共享池间接定义。
你可能会问，为什么要缓存SQL语句呢？不缓存又能怎么样呢？要想搞清楚这个问题，我们要先了解一个SQL在Oracle里的执行过程。
SQL执行过程
在Oracle里执行一个SQL语句，一般都要经过下面几个步骤：
 Create a Cursor 创建游标；
 Parse the Statement 解析语句；
 Bind Any Variables 绑定变量；
 Run the Statement 运行语句；
 Close the Cursor 关闭游标；
如果是一个查询SQL，则还要经过下面的步骤：
 Describe Results of a Query 描述查询的结果集；
 Define Output of a Query 定义查询的输出数据；
 Fetch Rows of a Query 获取查询出来的行。
SQL解析过程
从上面的步骤可以看出，每执行一个SQL，都需要对它进行解析（Parse），而一个解析过程，需要完成下面的工作：
 语法检查，验证它是否是合法的语句，有没有语法错误；
 语义检查，实现数据字典的查找，以验证是否符合表和列的定义，类型是否正确；
 （如果是CBO优化模式，关于CBO，请看后面Oracle的优化器一章）收集参考对象的统计；
 在所要求的对象上获取语法分析锁，使得在语句的语法分析过程中不改变这些对象的定义；
 检查用户的权限是否足够；
 从许多可能的执行路径中选择此语句最佳的执行计划；
 将它装入共享SQL区；
 生成语句的编译版本（P-CODE）。
解析是一个昂贵的操作，因为解析过程中需要消耗许多资源，而且费时，正因为如此，Oracle创造了共享池的概念，共享池会自动将解析过的SQL缓存起来，以后碰到相同的SQL，就不用再解析了，这样可以大大提高SQL的执行速度。
缓存SQL的原理
ORACLE 执行SQL语句时，先将SQL语句的字串通过一个hash算法得出一个hash值，然后检查共享池中是否已存在这个哈希值，若有就用已缓存的执行计划来执 行这个语句（即缓存命中，后面我们会提到共享池的命中率，就是这个概念），若没有（即缓存缺失）则需进行解析。
由于Oracle是通过SQL字符的hash值来判断是否为相同的SQL语句，因此，如果你的SQL有一点小小的变换，在Oracle看来，就是另外一个SQL了，会对它进行重新解析。
例如：
1.select id, name from members where id = 1403   
2. select id, name From members where id = 1403   
3. select name, id from members where id = 1403  
这三条SQL在Oracle看来就是三条不同的SQL。
绑定变量
Statement stmt=conn.createStatement();   
String member_id=member.id;   
String sql="select id,name from members where id ="+member_id;   
stmt.executeQuery(sql);

修改共享池
alter system set shared_pool_size=90M scope=both;
shared_pool_size是一个动态参数，可以在运行时修改，因此这里的scope设置为both，新的内存大小马上生效，并且还将修改保存在Oracle的启动文件里。
查看共享池命中率
select sum(pinhits) / sum(pins) * 100 "看命中率咯" from v$librarycache;

数据缓冲区
如 果每次执行一个操作时，Oracle都必须从磁盘读取所有数据块并在改变它之后又必须把每一块写入磁盘，显然效率会非常低。数据缓冲区存放需要经常访问的 数据，供所有用户使用。修改数据时，首先从数据文件中取出数据，存储在数据缓冲区中，修改/插入数据也存储在缓冲区中，commit或DBWR(下面有详 细介绍)进程的其他条件引发时，数据被写入数据文件。数据缓冲区的大小是可以动态调整的，但是不能超过sga_max_size的限制。
show parameter db_cache_size;
alter system set db_cache_size=50M scope=both;
查看命中率:
select (1 - ((physical.value - direct.value - lobs.value) / logical.value)) * 100 "命中率"  
from v$sysstat physical,   
v$sysstat direct,   
v$sysstat lobs,   
v$sysstat logical   
where physical.name = 'physical reads'  
and direct.name = 'physical reads direct'  
and lobs.name = 'physical reads direct (lob)'  
and logical.name = 'session logical reads';  
PGA(Process Global Area)，是server process一段私有内存区，它包含有全局变量，数据结构和一些控制信息。在Oracle8i 中，PGA调整非常复杂，要调整SORT_AREA_SIZE、HASH_AREA_SIZE、BITMAP_MERGE_AREA_SIZE、 CREATE_BITMAP_AREA_SIZE等参数。在ORACLE9I以后，只需要调整 PGA_AGGREGATE_TARGET。
每个 用户连接到Oracle,都会占用一定的内存，当然，用户一般连上之后还会做一些操作，比如一般的查询，有排序操作的查询，或用到Hash连接的查询。这 些都需要内存，而这些内存（还有一些其他的，这里就不说了。）加起来就是PGA的大小了。如果PGA设置的过小，会导致Oracle频繁同磁盘进行交换， 性能将会受到很大影响。
show parameter pga;
alter system set pga_aggregate_target=90M scope=both;