oracle学习2

1 IBM提出的QUBE模型
  响应时间=随机读的次数*10ms+顺序读的次数*0.01ms+处理的记录数*0.1ms

2 谓词：SQL中的那些条件，比如where子句
  selectivity:过滤比率，比如100条记录，查找后只有15条，则cardinality为15，即

oracle执行
计划中的rows一列，而 selectivity为0.15.
3 全表扫描，会读高水位线以下的数据。使用的是多块读的方法，每次读的数据块数量是

db_file_mulitiblock
_read_count参数控制

4 聚簇因子(dba_indexes视图中的clustering_factor，如果该值低，表示记录在表中的

顺序
也是按这个索引的数据保存，二者匹配，速度快，否则该值高，则数据记录在表中的保存

顺序和
索引顺序偏离很远，则这个值很高，效果不好

5 NEST LOOP，
  比如A,B表select a.*,b.* from a,b where a.id=p.id,
   其中一个表为驱动表，比如A，另外一个表为被驱动表，比如为B
则先读取A表的第一条记录，然后遍历驱动表B，根据条件a.id=p.id找到符合的记录，每

找到一条返回给用户
继续重复找A表中的所有记录直到被处理完。

6 soft merge:
   连接表的两个表的地位一样，把每个表按连接顺序排序，生成排序集，然后通过对两

个排序集各进行
一次遍历就可以获得结果集合，对每个表先进行排序，排序后两表都要进行遍历。
   oracle中，OMem，1Mem,0/1/M三列分别为采用内存中排序，1路排序，多路排序所需要

站用的内存的估算量
7 HASH JOIN：
   对驱动表A构造哈希桶，全表扫描A的所有记录，在ID列上构造哈希桶
以全表方式扫描B，依次对B的每条记录在b.p_id上用相同的哈希算法算值，然后跟A的桶

碰撞检测。

  HASH JOIN构健在PGA中，对它的访问不需要LATCH机制

8 oracle对于in,exists的优化是提前终止连接，比如A代表部门，B代表员工
   select ... from A where a.id in (select * from b),找出有员工的部门，则对于A

表中的某条记录，
只要在B中发现一条匹配的，则停止对B的查找，继续处理A的下一条记录，也叫semi-

join.
  从9I开始,in 和exists执行计划一样

9 NOT EXISTS中，不关心子查询是否有null，直接返回，但not in中如果有null,则返回

NULL，
oracle会把not in转换为not exits,但由于用了nvl隐藏函数，使得上面的索引不可用，
降低效果

10 并行度：
   slave proceess:真正并行干活的进程
   query coodinator:指挥进程，负责协调调度
  当多套并行操作时，涉及producer/consumer机制，table queue则为Orale
使用SGA方式作为进程间数据传递的通道，当producer向table quere写数据时，叫PX

SEND
当cousmmer向table cahce写数据，叫PX RECEIVE，当query coodinatore从table queue
读数据时，对应px coodinator;每套producer,cosumner有一对应的table queue
  show parameter parallel,table queue一定要放到large pool里中去（使用SGA自动调

整
即可）



11 oracle 9i前，一直坚持使用的是内存优先的原则，考虑的是内存命中率，而10G开始
使用的等待事件。AWR完成数据收集后，会触发addm(数据诊断监控程序），会给出修改的

意见。
  1) statistics_level参数
       有basic,typical,all三个值，缺省为typcal,all收集更详细的数据，可以借助
v$statistics_level视图
    AWR收集到的信息最初被保存在SGA的shared pool中，但最多使用5%的空间，当达到

限制要写到磁盘，MMON进程负责固定时间去写。
   2）默认每个小时产生1次快照，保留时间7天
     select * from dba_hist_wr_control;
   修改AWR的收集间隔，改为每30分一次：
     execute dbms_workload_repository.modify_snapshot_settings

(interval=>30,retention=>14400);
  3)使用
     @awrrpt (脚本在oracle_home/rdbms/admin下）

  4）ASH（即时数据分析），以秒为单位，只收集v$session和v$session_wait中的信息
，ASH使用的空间从v$sgastat中看：
   select pool,name,bytes/1024/1024 from v$sgastat where name like 'ASH%';
  5)表监控
     监控对象上发生的对象变更（CRUD),如果发生改变的数量超过总数10%，则重新统计
   select * from v$statistics_level
    如果发现modification monitoring中的列为typical，则证明启动了动态监控
  6）用动态数据收集，把对象的统计信息设置为null,当遇到一个
对象的统计信息为null时，会动态收集其统计信息，
  select /*+ dynamic_sampling(a 2) */ * from a
    a为表名，2为采样的是2倍的缺省的数据块数量（32）
  7） 查看默认的统计信息保留历史
     select dbms_stats.get_stats_history_retention from dual;
   修改保留策略
     execute dbms_stats.alter_stats_history_petention(45);

导入导出统计信息
    可以将生产环境的统计信息导出到开发库中，模拟生产环境，借助中间表来实现

    在使用DBMS_STATS分析表的时候，我们经常要保存之前的分析，以防分析后导致系统

性能低下然后进行快速恢复。

1、首先创建一个分析表，该表是用来保存之前的分析值：

SQL> begin
2 dbms_stats.create_stat_table(ownname=>'TEST',stattab=>'STAT_TABLE');
3 end;
4 /

PL/SQL 过程已成功完成。

SQL> begin
2 dbms_stats.gather_table_stats(ownname=>'TEST',tabname=>'T1');
3 end;
4 /

PL/SQL 过程已成功完成。


2、导出表分析信息到stat_table中

SQL> begin
2 dbms_stats.export_table_stats

(ownname=>'TEST',tabname=>'T1',stattab=>'STAT_TABLE');
3 end;
4 /

PL/SQL 过程已成功完成。

SQL> select count(*) from TEST.STAT_TABLE;

COUNT(*)
----------
         4

EXPORT_COLUMN_STATS：导出列的分析信息
EXPORT_INDEX_STATS：导出索引分析信息
EXPORT_SYSTEM_STATS：导出系统分析信息
EXPORT_TABLE_STATS：导出表分析信息
EXPORT_SCHEMA_STATS：导出方案分析信息
EXPORT_DATABASE_STATS：导出数据库分析信息
IMPORT_COLUMN_STATS：导入列分析信息
IMPORT_INDEX_STATS：导入索引分析信息
IMPORT_SYSTEM_STATS：导入系统分析信息
IMPORT_TABLE_STATS：导入表分析信息
IMPORT_SCHEMA_STATS：导入方案分析信息
IMPORT_DATABASE_STATS：导入数据库分析信息
GATHER_INDEX_STATS：分析索引信息
GATHER_TABLE_STATS：分析表信息，当cascade为true时，分析表、列（索引）信息
GATHER_SCHEMA_STATS：分析方案信息
GATHER_DATABASE_STATS：分析数据库信息
GATHER_SYSTEM_STATS：分析系统信息


4、删除分析信息

SQL> begin
2 dbms_stats.delete_table_stats(ownname=>'TEST',tabname=>'T1');
3 end;
4 /

PL/SQL 过程已成功完成。

SQL> SELECT num_rows,blocks,empty_blocks as empty, avg_space, chain_cnt,

avg_row_len FROM dba_tables WHERE owner = 'TEST'
AND table_name = 'T1';

NUM_ROWS     BLOCKS      EMPTY AVG_SPACE CHAIN_CNT AVG_ROW_LEN
---------- ---------- ---------- ---------- ---------- -----------

没有查到分析数据


5、导入分析信息

SQL> begin
2 dbms_stats.import_table_stats

(ownname=>'TEST',tabname=>'T1',stattab=>'STAT_TABLE');
3 end;
4 /

PL/SQL 过程已成功完成。

SQL> SELECT num_rows,blocks,empty_blocks as empty, avg_space, chain_cnt,

avg_row_len FROM dba_tables WHERE owner = 'TEST'
AND table_name = 'T1';

NUM_ROWS     BLOCKS      EMPTY AVG_SPACE CHAIN_CNT AVG_ROW_LEN
---------- ---------- ---------- ---------- ---------- -----------
      1000          5          0          0          0          16
可以查到分析数据




12 锁定对象的统计信息后，则无论数据如何变化，执行计划都不发生改变；
  1）select table_name,blocks from user_tables;
  2)锁定
     exec dbms_stats.lock_schema_stats('product');
 
  3)清除数据等操作后，再收集，统计信息不会有任何变化
  
  4）解除锁定
      exec dbms_stats.unlock_schema_status('product');
13 统计数据分为无负载模式和负载模式
      无负载时，oracle用一个内设置的压力测试生成一定的负载，
其统计量有三种：
     cpusppendnw:每秒CPU运算次数
     ioseektim:IO平均寻址时间
     iotfrspped:磁盘吞吐量
   select pname,pval1,pval2 from sys.aux_stats$ where sname='SYSSTATS_MAIN';
   负载模式的话，也是跟statpack差不多，通过创建系统快照的方式，通过对比来实现
14 
  响应时间=服务时间+等待时间
oracle的等待事件（OWI,oracle wait interface):注意是等待资源竞争的才纳入等待
事件的体系
   常见的等待事件视图：
    1） v$event_name 等待事件的定义
    2）  v$system_event记录了实例启动以来，所有进程所经历的等待事件的汇总
    select * from v$system_event即可
    3） v$session_event记录了自实例启动以来每个会话所经历的等待事件的汇总。
    4）v$session_wait是整个OWI中最重要的，描述当前会话正在经历的等待事件

其中的wait_time表示等待时间
   5）v$session_wait_history:保留了每个连接会话的最近的10个等待事件，比如
    查看今天IO最多等待的SQL
   SELECT sql_id,count(*) from v$active_session_history
where sample_time>sysdate-1 and
session_state='WAITING' and wait_class='User I/O'
group by sql_id order by count(*) desc;


   得到语句的sql_id后，再找具体的sql语句：
   select sql_text from v$sqlarea where sql_id='&1';
  然后输入第一个sqlid即可：

15 oracle 10g起，提供了metric,用单位时间来描述系统吞吐能力，可以通过

v$metricname查看。所有的都由mmon进程定期采样，分为两组：一组为
Long Duration Metric组(每隔60秒采样），另外一组为short duration metric group（

每隔15秒采样）。
   视图v$sytemetric_history保留了1个小时以内的历史数据。server-generated alert
是orcle 10g开始提供的一种全新的报警机制，比如超过了阕值的报警叫stateful alert;
被特定事件触发的为stateless alert.
   addm是预警性框架：desc dba_thresholds
      select metrics_name,warning_value,critical_value,status from

dba_thresholds;
 
16 timemodel,时间视图包含v$sys_time_model和v$sess_time_model两个视图，
分别可以从系统范围和会话范围找出各个组件的时间消耗，单位是毫秒，下面的
语句查出各个组件的时间消耗：
   select stat_name,round(value/1000000) "Time (Sec)" from v$sys_time_model;

17 ADDM自动诊断顾问
   ADDM完成分析后，其结果会保存在AWR中。查看最后一次产生报告的内容：

18 AUTO SQL TUNNING（ATO）
    分为常规模式和调优模式。ATO工作在调优模式，即由DBA触发的，效果比较好
 
DBA触发的任务接口叫sql tunning advisor.利用这个东西把SQL语句提交给ATO，从而进

行调优化。
    SQL PROFILE可以不对原始的SQL进行任何修改，而生成计划，如果生成后，会将计划

放到数据字典中去；此外，还可以使用sql access advisor生成一个基于全局分析的执行

计划助手，进行总体的综合分析。
   使用：监视阶段，诊断阶段，解决阶段。

19 ASSM ：自动管理SGA，
其中java pool,large pool,share pool等都可以自动管理，oralce 10g还有一个
叫MMAN进程的，会定期检查AWR的性能，并根据这些信息来决定SGA的最佳分布。
  启动ASSM功能：
    必须打开statistics_level,sga_target，这个参数定义SGA的总的大小。如果设置成

0，禁止了ASSM。如果使用了SGA_TARGET，也可以手动设置，如果新的值大于现有分配的

值，则该组件空间会向新的参数值看齐，如果小于已分配的值，则用已分配的。
  测试：show parameter sga_target;
   取消:alter system set sga_target=0;

   查看各个组件的情况
   select component,current_size,max_size,user_specified_size from
v$sga_dynamic_components;

20 顾问功能：
    顾问可以在oem中的指导中心中进去看到。各类指导功能

21 可以通过设置fast_start_mttr_target来设置参数值，含义为oracle多长时间能提供

恢复，如果该值设置大，意味检查点次数少，但恢复recovery时间会长；可以借助
mttr advisor获得；可以用如下方法获得相关时间：
   alter system set fast_start_mttr_target=1
  然后：
   select target_mttr,estimated_mttr from v$instance_recovery;
  其中estimated为估计的时间：
  然后再把fast_start_mttr_target设置成一个很大的值
  alter system set fast_start_mttr_target=3600;
正常时间下运行一段时间，再看target_mttr这一列，就是最大时间花费
 
22 联机日志顾问
    查看日志切换时间：
    select to_char(first_time,'yyyy-mm-dd hh24:mi:ss') from v$archived_log
order by sequence#
   如果关闭了fast_start_mttr_target设置为0，就等于禁止了redo log siezing

advisor的功能
    查看推荐联机日志文件的大小：
   select optimal_logfile_size from v$instance_recovery;
23 UNdo设置建议助手大小设置，默认为15分钟

24 段顾问设置：
   一个表空间可以设置多个阕值，一个为warning级别（85%)，一个为critical级别
(97%)

25 限制用户的行为
   IP地址限制：可以在sqlnet.ora文件中进行限制
  tcp.validnode_checking=yes, 启用地址限制
tcp.invited_nodes=(addr1,addr2) 允许访问IP列表
tcp.excluded_nodes=(addr1,addr2) 不允许访问IP列表
26 oracle提供了三种权限：对象object,系统权限system,角色role,有个普通的
用户public，每个用户都有public权限。
  VPD提供的是行级数据保护，实际上就是创建一个要保护的函数，然后用
dbms_rls.add_policy将函数和要保护的表关联

27 审计功能：标准审计和FGA审计。
  审计SYS这些操作，保存在audit_FILE_DEST参数指定的目录中
如果默认的审计是放在SYSTEM表空间，需要移到其他空间去。
  audit table;
select username,userhost,owner,obj_name,action_name from dba_audit_trail;
  到oracle 10g中，FGA可以支持insert,delete,update的审计，都记录在
表sys.fga_log$中，并通过dba_fga_audit_trail视图展示出来。
FGA可以设置审计条件，比如当用户查看某些敏感数据时，才审计