oracle 优化学习（一）

select sid from v$mystat where rownum=1;查询目前回话的sid。
当系统发生阻塞时，可以查询v$lock信息。
 select machine from v$session where sid in (145,144);用来查询每个用户使用的机器的名称。
锁不会对某个表上加个锁或者某几行，锁是以数据块的一个属性存在的，是物理的，不是逻辑上属于某个表或者某个行。
latch 可以理解为一种轻量级的锁，它不会造成阻塞，只会造成等待，是资源争用的等待。
常见的latch争用：共享池的lanch争用（常见的原因是没有绑定变量），数据缓冲池的latch争用。
select * from v$latchname where name like 'library cache%'
如果看到library cache这样的latch争用，是共享池出了问题，这种问题基本上是sql语句导致的，比如没有绑定变量或存储过程
被反复分析。
alter table minsize records_per_block;可以通过以下命令将每个数据块存放记录的数量减到最少。
当索引很密集的时候（比如不同实例产生的连续序列号在同一个数据块中，但不同实例来访问就会造成热快），而反向索引则可以
解决这样的问题。例如：1234,1235变为4321,5321.这样本来是放在相同数据块上的键值，现在分布到了不同的数据块上。但是他会导致index range scan不能用。
当发生索引根数据块时，产生热快，可以把索引分区。
当表没有做分析的时候，oracle会使用动态采样来收集统计信息。这个动作只有在sql执行的第一次，即应分析阶段使用。后续的分析将直接采用第一次的硬分析。
CBO的两种可选的运行模式：FIRST_ROWS(n),ALL_ROWS.ALL_ROWS强调的是以最快的速度将SQL执行完毕，并将所有结果集反馈回来。
而FIRST_ROWS(n)则侧重于返回前N条记录的执行时间。
hash join 适合两张关联的表都比较大的时候。
基数在sql分析中起着很大的作用，因为他会影响查询的方式。
sql的执行计划可以通过：1，explain plan for 2，set autotrace on 3，第三方软件，Toad，PL/SQL DEVELOPER
1， explain plan for sql语句， select * from table（dbms_xplan.display）;
执行计划中filter 和 access 的区别：filter只是过滤，没有访问路径的问题，而access会影响数据的访问路径（表，还是索引）。
如果表没有做分析，CBO可以通过动态采样的方式来获得正确的执行路径，而如果表分析过，可是却太旧就会导致错误的执行计划。
执行表分析，DBMS_STATS.GATHER_TABLE_STATS(USER,'TABLE_NAME',CASCADE=>TRUE);
如果CBO认为使用hint会导致错误的结果，则hint将被忽略。
如果在表中指定了别名，那么hint必须使用别名。
在系统，回话级设置优化器类型。alter system set optimizer_mode=all_rows;
hint 格式，/*+ ksdfkdsfk */
/*+ leading(t1,t) */ 是指定哪个表为驱动表
nest loop 是从一张表中读取数据，访问另一张表来做匹配。，适合一个关联表比较小的时候。
select degree from user_tables where table_Name='t'可以查询出并行度。
driving_site(表名)这个提示在分布式数据库中很有用，是将这个表本地数据送到远程，
cache，把表放在缓存中。
user_tables的last_analyzed 是检查表有没有进行分析的。
user_indexes同理
oracle 做直方图分析时会将要分析的列上的数据分成很多数量相同的部分，每一部分称为一个桶，这样就可以非常容易知道这个
列上的数值分布情况。
可以从user_histograms 查出直方图是否分析过的相关信息。
删除相关的表的分析信息：DBMS_STATS.DELETE_TABLE_STATS(,,,,,,)
可以创建一个表用于存放性能数据。DBMS_STATS.CREATE_STAT_TABLE(,,,,,,) 也可以DROP 进行删除。
在分析对象时加入参数stattab=>'一个创建好的表名'。如：exec dbms_stats.set_table_stats(user,'t',statab=>'statab');
DBMS_STATS.EXPORT_DATABASE_STATS 等，可以用来将已有的性能指标导入到用户建好的表中存放。
当有的时候觉得当前数据基本不变，并且执行计划很准确时，则可以锁定表的统计信息。DBMS_STATS.LOCK_TABLE_STATS（将不允许被更新）。
DBMS_STATS.RESTORE_TABLE_STATS 这些存储过程提供了一种统计信息的恢复方法。
select dbms_stats.get_history_availability from dual;提供了分析数据恢复的最早时间点，只有在这个时间点之后的分析数据才可以被恢复。
使用手工设置DBMS_STATS.SET_TABLES_STATS设置的值无法恢复。
DBMS_STATS.SET_TABLES_STATS可以用来模拟大数据环境。

动态采样还有一个好处就是，它可以对不同列之间的相关性做统计。
但是缺点是：1，采样的数据块是有限的，对于海量数据而言会有偏差。2，采样会消耗系统资源，特别是OLTP数据库。

执行计划中出现PX(PARALLEL EXECUTION)及为并行执行。有时候并行服务进程数量是并行度的2倍，是因为启用了并行服务父进程的缘故。例如有需要排序的信息并行协调进程就会在启动另外的并行服务进程用来给前面得到的数据做排序。

PX Dep Credit:send blkd 是当并行服务进程想并行协调进程QC或父进程发送信息时，用一时间只能有一个并行进程可以向上发信息，如果别的也在发则就得等，直到获得一个发送消息的credit。当然有时也是没事做，再那闲着空等。区别出空等还是资源不足：计算平均等待时间。差不多是2秒（这个等待超时为2秒），说明是闲的没事，小于2秒挺多则说明是并行服务之间的竞争导致的，可以降低并发度。

对于DML操作，只有操作对象时分区表时才会采用并行。因为分区表的话可以根据每个分区来启动一个并行服务进程。另外在执行DML操作之前要先执行ALTER SESSION ENABLE PARALLEL DML;执行计划中（有并行操作）IN-OUT（数据流的执行方向）列中PCWC代表 PARALLEL COMBINED WITH CHILD 表示同一组并行进程之间的数据交互，表示子操作也是并行的，。S->P代表serial to parallel 是一个串行向并行进程发送数据的过程，其他同理可推。

parallel_min_persent 表示申请并行服务进程时的最小值（是个百分数）。如果申请不到这个数量的并行服务时就会报错   ORA-12827 insufficient parallel query slaves available。

直接加载可以在insert中加入hint append 如：insert /*+ append */ into,,,,,,，或sql*loader中使用DIRECT = TRUE 。他可以将数据直接追加到数据段的最后，不需要花费时间在段中寻找空间，数据不经过data buffer 直接写到数据文件中。直接加载的数据放在高水位以上，加载完成后就将表的高水位线移到新加入的数据之后，就可以使用了。

对象是logging状态时直接加载不会明显减少redo的产生，但是nologging是就会减少很多，但是加载完赶紧备份，因为不能通过归档恢复。当使用直接加载时，约束性索引会变为unable可以通过rebuild重建。并行直接加载带有约束性索引的则不允许。但是可以通过SKIP_INDEX_MAINTENANCE = TRUE 来设置。加载完成后索引也会变成unable 需要手工rebuild。

为了更容易找到此次生成的trace文件，可以设置标识。alter session set tracefile_identifier='mytest';   alter session set sql_trace=true;当然也可以通过alter system 来设置系统级的。可以使用tkprof来规范化文件的格式。

tkprof的相关参数：1，explain = user/password 不但输入实际的执行路径而且还有执行计划。 2，sys=（YES/NO）会在文件中输入所有sys用户的操作。3，aggregate=(yes/no) 将所有相同的SQL语句在输入文件中做合并。

10046事件按照收集信息的内容可以分为4个级别： level 1：等同于sql_trace的功能， level 4：在level 1 的基础上增加收集绑定变量的信息。level 8 在1 的基础上增加等待时间的信息。level 12 level 4 + level 8.  alter session set events '10046 trace name context forever,level 4'; 但是绑定变量读的信息只能在trace原文件中获取，tkprof之后是没有的。

对其他回话进行跟踪：1， select  sid,serial#,username from v$session where username is not null;   首先获得需要trace的回话信息，包含sid和serial#。,2，exec dbms_system.set_sql_trace_in_session(sid,serial,true); 或者用以下方式:EXECUTE DBMS_SYSTEM.SET_EV(SID,Serial#,event,level, '')如EXECUTE dbms_system.set_ev (1,21,10046,8,'');要Disable则将level改为0,如: EXECUTE dbms_system.set_ev (1,21,10046,0,'');

SQL_TRACE 和 STATSPACK是两个使用与不同级别的性能分析工具：1，当遇到一个数据库系统整体性能下降，而没有特定对象可以分析时，做一个statspack（awr）报告是合适的。这样可以发现系统的瓶颈在哪里，如国在sql语句上那么我们将获得相应的sql语句，然后使用sql_trace进行分析。

使用10056事件来生成SQL分析过程的trace文件，这个跟踪文件不能用tkprof进行处理。 alter session set events='10056 trace name context forever,level 1'; 在这个生成的跟踪文件的sql语句中基本引用到的对象基本信息，在索引信息中有CLUF，索引的聚合因子：他表示索引中的键值和原表上的数据分布的一种关系,当索引键值和表中数据的排列顺序大致相同时，说明索引键值指向的数据块越集中，这个因子就越小，越有利于索引的使用；当索引键值指向的数据块越多时（数据的排序和索引相差很大），这个因子就越大，越不利于索引的使用。

性能视图的使用：

为了获取用户连接到数据库的信息，需要获得用户的sid，可以通过查询v$session的machine列（客户端机器的名称），program列或者module列（客户段端发起连接的名称）来确定用户的sid号。然后用v$sql中的sql_id列与v$session中的sql_id列进行连接。select a.sql_text,b.stutas,b.last_call_et,b.event from v$sql a ,v$session b where a.sql_id = b.sql_id and b.sid=333;

可以通过对比sql的使用cpu的时间和它执行时间的百分比来得出是否一直处于等待。select sql_text,cpu_time/(1000*1000) ,elapsed_time/(1000*1000) ,(cpu_time/elapsed_time)  pct from v$sql where sql_text like ',,,,,,';     另外，可以使用  alter system flush shared_pool;清空共享池，这样可以将SQL语句重新分析。 v$sql与v$sqlarea的区别：v$sql是从child cursor的级别上统计信息，即使相同的sql语句也可能会因为环境的不同而执行计划不同。而v$sqlarea是从parent cursor的级别上统计信息，即sql语句长的是否一样。  视图v$shared_cursor存放了SQL在执行过程中游标共享的信息，用来分析长得一样的SQL为什么没有共享。通过查询v$session的blocking_session可以定位阻塞其他回话的sid号。v$lock也可以用来查询阻塞信息。v$sessstat 这个视图记录了某个session从运行以来的各种资源统计数据，通过关联v$statsname可以查询出某个session的资源消耗情况。 v$session_wait 用来记录回话的一些等待信息。

性能报告：AWR,STATSPACK

statspack 的性能采集是通过快照snapshot 的方式实现的，即statspack在某一个时间点对系统的性能数据做一个快照，并将快照保存在用户的表中，在另一个时间执行相同的动作，这样通过对比这两个时间点之间系统性能数据的变化来分析系统性能的状况。 创建快照：exec statspack.snap;   然后运行spreport.sql。

AWR性能报告：运行$ORACLE_HOME/rdbms/admin目录下的awrrpt.sql. 还用一个awrrpti.sql  使用这个脚本可以生成其他数据库或其他实例的AWR报告。在OLTP系统中要注意library hit（共享池中SQL的命中率） 和buffer hit 如果共享池的命中率很低说明有很多sql不能重用，需要重新解析，这会大大降低系统的的性能和sql的执行效率。AWR是一个数据的合集，例如里面的DB TIME指的是用户操作时间。如果一个用户等30秒那么10个就是300秒，一个CPU 处理30秒，14个就是120秒，所以说是合集，这些时间都是以累计的方式记录在AWR中。Top 5 timed events 是AWR最重要的事情。

db file sequential read 是指oracle在访问索引数据块的时候会以db file sequential read 方式来将数据块读入到内存中。

latch：row cache objects 是一个共享池争用的等待事件。

read by other session：是一个会话在等待另外一个会话将数据块读入内存。（说明数据中有热快）

某个区间的性能报告并不能精确地反映出在这个采样周期中资源的消耗情况，因为有些在这个区间运行的sql在这个时间周期开始之前就已经运行很久了，恰好结束于这个采样周期。这些因素都导致了采样周期里面的数据并不绝对是这个时间段发生的所有数据库操作的资源使用的数据。SQL在操作I/O（物理，逻辑）的时候基本上消耗很少的CPU。sql版本数，version count ，一个SQL产生多版本的原因有很多，比如绑定变量的值，优化器的模式等因素都会导致一条SQL语句产生几个子游标。cluster wait time 的值是RAC结构造成的，是一种GC等待（global cache RAC）

在PGA内存建议中，要关注两个点，一个是，oracle估算的PGA大小不会导致分配内存。另一个是，物理读写的值不再增加的点。

buffer busy waits 这个事件是由于某些数据块访问的太过频繁而导致热快的产生。

还有另外一个新的性能工具ASH，实际上是AWR性能数据的一部分，AWR的数据长期保存在数据字典表中。ASH则侧重于当前数据中活动回话的信息分析,oracle会对数据库中活动的回话信息进行采样，这些信息放在一个动态的循环使用的内存区域中位于SGA区。这些数据可以通过v$active_session_history来得到。运行$ORACLE_HOME/RDBMS/ADMIN下的ashrpt.sql即可。

在ASH报告中可能要输入target_service_hash的值，而通过v$active_services视图的name_hash列获得需要生成报告服务hash值。

（第二遍学习《让oracle跑得更快》学习总结，这数据块读入内存书写得通俗易懂，很好看）

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

转载于:http://blog.itpub.net/25155154/viewspace-688518/