SQL执行计划、绑定变量的使用

Oracle: 查看对象的统计信息

优化器会根据对象的统计信息来决定执行计划，因此这个信息很重要。

SQL:
   STUDENT 为表名
--查看表的stats
select * from user_tab_statistics where table_name = 'STUDENT'

--查看列的stats
select * from user_tab_col_statistics where table_name = 'STUDENT'

--查看索引的stats
select * from user_ind_statistics where table_name = 'STUDENT'

 

SGA 共享池：

      共享池是内存中最关键的部分之一。数据库中正在执行的所有SQL语句结合在一起将会因为他们对共享池的影响而对总的性能和可扩展性带来巨大的影响。

      共享池是Oracle缓存程序数据的地方。共享池存储sql语句的地方称为库高速缓存(library cache)。

      分配给共享池的内存是有限的，原先加载的语句就不能长时间地放在其中，管理共享池的对象的算法为：最近最少使用算法(Least Recently Used  LRU),类似于先进先出。

      为了最高效的使用共享池，语句需要可以共享，如果每条语句都是唯一的，基本上就违背了设立共享池的初衷。语句共享性越差，相应的时间就越长。

库高速缓存：

      解析包括验证语句的语法、验证提及的对象、以及确认该对象的用户权限，如果语句之前执行过，Oracle将取回之前解析的信息并重用，称为软解析，

如果语句没有执行过，Oracle就将执行所有的工作来为当前生成执行计划，并将它存在缓存中以便将来重用，称为硬解析。

   为了确定一条语句是不是之前执行过，可以通过 v&sql视图来查看存放在库高速缓存中的语句。

  例：  SELECT * FROM   V&SQL  WHERE  UPPER(SQL_TEXT)   LIKE '%TABLE_NAME%';

 库高速缓存中的SQL语句区分大小写字母。

  使用绑定变量可以共享SQL语句：

1.

sqlplus中如何使用绑定变量，可以通过variable来定义

SQL> select * from tt where id=1;

ID NAME
---------- ----------------------------------------
1 test

SQL> select * from tt where id=2;

ID NAME
---------- ----------------------------------------
2 test

SQL> variable i number;
SQL> exec :i :=1;

PL/SQL 过程已成功完成。

SQL> select *from tt where id=:i;

ID NAME
---------- ----------------------------------------
1 test

SQL> exec :i :=2;

PL/SQL 过程已成功完成。

SQL> select *from tt where id=:i;

ID NAME
---------- ----------------------------------------
2 test

SQL> print i;

I
----------
2

SQL> select sql_text,parse_calls from v$sql where sql_text like 'select * from t
t where id=%';

SQL_TEXT PARSE_CALLS
------------------------------------------------------------ -----------
select * from tt where id=2 1
select * from tt where id=1 1
select * from tt where id=:i 2
SQL>

 查询转换

在进展到执行计划的开发过程之前，会有一步为查询转换的步骤。

目的是确定如果改变查询的写法会不会提供更好的查询计划，所以写的SQL语句不一定就是最终确定执行的语句。

使用物化视图进行查询重写

 

物化视图 (Materialized View)，在以前的Oracle版本中称为快照(Snapshot)。Oracle的物化视图提供了强大的功能，可以用于预先计算并保存表连接或聚集等耗时较多的操作的结果，这样在执行查询时，就可以避免进行这些耗时的操作，而从快速地得到结果。

物化视图有很多方面和索引很相似，使用物化视图的目的是为了提高查询性能，物化视图对应用透明，增加和删除物化视图不会影响应用程序中SQL语句的正确性和有效性，物化视图需要占用存储空间，当基表发生变化时，物化视图也应当刷新。物化视图可以分为以下三种类型：包含聚集的物化视图、只包含连接的物化视图和嵌套物化视图。

物化视图可以进行分区。而且基于分区的物化视图可以支持分区变化跟踪（PCT）。具有这种特性的物化视图，当基表进行了分区维护操作后，仍然可以进
行快速刷新操作。对于聚集物化视图，可以在GROUP BY列表中使用CUBE或ROLLUP，来建立不同等级的聚集物化视图。

 

一、先来看看怎么创建一个物化视图：

 

create materialized view vi_emp
refresh force on demand
as
select d.dname,e.ename,e.job,e.hiredate 
  from scott.dept d,scott.emp e where d.deptno=e.deptno;

物化视图的刷新(refresh)的方法有四种：fast、complete、force和never，Oracle默认采用force方式，如上所示。
fast：刷新采用增量刷新，只刷新自上次刷新以后进行的修改。
complete：刷新对整个物化视图进行完全的刷新。
force：刷新时会去判断是否可以进行快速刷新，如果可以则采用fast方式，否则采 用complete的方式。
never：指物化视图不进行任何刷新。

物化视图的类型有两种：on demand和on commit
on demand：顾名思义，仅在该物化视图“需要”被刷新了，才进行刷新(refresh)，即更新物化视图，以保证和基表数据的一致性。
on commit：一旦基表有了commit，即事务提交，则立刻刷新，立刻更新物化视图，使得数据和基表一致。

二、创建定时刷新的物化视图（每天晚上10点刷新）：

 

 

create materialized view vi_emp
refresh force on demand
start with to_date('03-02-2012 14:50:59', 'dd-mm-yyyy hh24:mi:ss') next to_date(concat(to_char(sysdate + 1, 'yyyy-MM-dd'), ' 22:00:00'), 'yyyy-MM-dd hh24:mi:ss') 
as
select d.dname,e.ename,e.job,e.hiredate 
  from scott.dept d,scott.emp e where d.deptno=e.deptno;

三、删除物化视图：

drop materialized view vi_emp;

 使用物化视图进行查询重写：

     查询重写是一种发生在当一个查询或查询的一部分已经被保存为一个物化视图，转换器重写该查询以使用预先计算好的物化视图数据而不需要执行当前查询的转换。

 物化视图与普通视图的区别在于查询已经被执行并将结果存入一张表中，这样做的好处就是预先计算了查询的结果并且特定查询执行的时候可以直接调取该结果。

例子：

1、    CREATE MATERIALIZED VIEW  sales_mv（视图名）

AS    (SQL语句)

2、   SELECT   ./*+rewritr(sales_mv （物化视图名称）)*/................(物化视图的SQL语句)

 

执行计划并取得数据行：

在优化器确定了执行计划并保存到库高速缓存中以备日后重用之后，下一个步骤就是执行计划并取得满足查询的数据行。

为了完成相应，查询将会完成  解析、绑定、执行、提取的步骤， 需要返回的数据行很可能都不是在一次往返的过程中就传递给应用的，数据包将会从数据库通过网络传递给应用，一直到所有的行都到达调用者。

   列大小可以控制逻辑读取的速度：

 列大小配置是通过编程设置的：  在SQL*PLUS中，默认为15，通过使用  SET  ARRAYSIZE  n命令来改变数组的大小。