查看Oracle执行计划的几种方法 / oracle中DateTime类型的字段，建立索引后，查寻时索引如何生效？/ oracle 中sql语句怎么加多个强制索引

一、通过PL/SQL Dev工具

    1、直接File->New->Explain Plan Window，在窗口中执行sql可以查看计划结果。其中，Cost表示cpu的消耗，单位为n%，Cardinality表示执行的行数，等价Rows。

    2、先执行 EXPLAIN PLAN FOR   select * from tableA where paraA=1，再 select * from table(DBMS_XPLAN.DISPLAY)便可以看到oracle的执行计划了，看到的结果和1中的一样，所以使用工具的时候推荐使用1方法。

注意：PL/SQL Dev工具的Command window中不支持set autotrance on的命令。还有使用工具方法查看计划看到的信息不全，有些时候我们需要sqlplus的支持。

 

二、通过sqlplus

1．最简单的办法

Sql> set autotrace on

Sql> select * from dual;

　　执行完语句后，会显示explain plan 与 统计信息。

　　这个语句的优点就是它的缺点，这样在用该方法查看执行时间较长的sql语句时，需要等待该语句执行成功后，才返回执行计划，使优化的周期大大增长。如果不想执行语句而只是想得到执行计划可以采用：

Sql> set autotrace traceonly

这样，就只会列出执行计划，而不会真正的执行语句，大大减少了优化时间。虽然也列出了统计信息，但是因为没有执行语句，所以该统计信息没有用处，如果执行该语句时遇到错误，解决方法为：

(1）在要分析的用户下：

Sqlplus > @ ?

dbmsadminutlxplan.sql

(2) 用sys用户登陆

Sqlplus > @ ?sqlplusadminplustrce.sql

Sqlplus > grant plustrace to user_name;

- - user_name是上面所说的分析用户

 

　2．用explain plan命令

(1) sqlplus > explain plan for select * from testdb.myuser

(2) sqlplus > select * from table(dbms_xplan.display);

　　上面这2种方法只能为在本会话中正在运行的语句产生执行计划，即我们需要已经知道了哪条语句运行的效率很差，我们是有目的只对这条SQL语句去优化。其实，在很多情况下，我们只会听一个客户抱怨说现在系统运行很慢，而我们不知道是哪个SQL引起的。此时有许多现成的语句可以找出耗费资源比较多的语句，如：

SELECT ADDRESS, substr(SQL_TEXT,1,20) Text, buffer_gets, executions,buffer_gets/executions AVG FROM  v$sqlarea   WHEREexecutions>0 AND buffer_gets > 100000 ORDER BY 5;

ADDRESS      TEXT                     BUFFER_GETS    EXECUTIONS        AVG

-------- ---------------------------------------- ----------- ---------- ------------------------------------------------------------

66D83D64   select t.name,  (sel               421531        60104             7.01336017

66D9E8AC   select t.schema, t.n               1141739        2732             417.913250

66B82BCC   select s.synonym_nam             441261        6                73543.5

　　从而对找出的语句进行进一步优化。当然我们还可以为一个正在运行的会话中运行的所有SQL语句生成执行计划，这需要对该会话进行跟踪，产生trace文件，然后对该文件用tkprof程序格式化一下，这种得到执行计划的方式很有用，因为它包含其它额外信息，如SQL语句执行的每个阶段(如Parse、Execute、Fetch)分别耗费的各个资源情况(如CPU、DISK、elapsed等)。

 

3、启用SQL_TRACE跟踪所有后台进程活动：

全局参数设置： .OracleHome/admin/SID/pfile中指定: SQL_TRACE = true (10g)

    当前session中设置：

    SQL> alter session set SQL_TRACE=true;

    SQL> select * from dual;

    SQL> alter session set SQL_TRACE=false;

    对其他用户进行跟踪设置：

    SQL> select sid,serial#,username from v$session where username='XXX';

       SID    SERIAL# USERNAME

    ------ ---------- ------------------

       127      31923 A

       128      54521 B

    开启跟踪:SQL> exec dbms_system.set_SQL_TRACE_in_session(127,31923,true);

    关闭跟踪:SQL> exec dbms_system.set_SQL_TRACE_in_session(127,31923,false);

    然后使用oracle自带的tkprof命令行工具格式化跟踪文件。

4、使用10046事件进行查询：

    10046事件级别：

    Lv1  - 启用标准的SQL_TRACE功能,等价于SQL_TRACE

    Lv4  - Level 1 + 绑定值(bind values)

    Lv8  - Level 1 + 等待事件跟踪

    Lv12 - Level 1 + Level 4 + Level 8

    全局设定：

    OracleHome/admin/SID/pfile中指定: EVENT="10046 trace name context forever,level 12"

    当前session设定：

    开启:SQL> alter session set events '10046 trace name context forever, level 8';

    关闭:SQL> alter session set events '10046 trace name context off';

    对其他用户进行设置：

    SQL> select sid,serial#,username from v$session where username='XXX';

       SID    SERIAL# USERNAME

    ------ ---------- ------------------

       127      31923 A

    SQL> exec dbms_system.set_ev(127,31923,10046,8,'A');

5、使用tkprof格式化跟踪文件： (根据下面SQL语句得到的文件都不存在该目录下,郁闷啊,懵懂啊...)

一般，一次跟踪可以分为以下几步：

1、界定需要跟踪的目标范围，并使用适当的命令启用所需跟踪。

2、经过一段时间后，停止跟踪。此时应该产生了一个跟踪结果文件。

3、找到跟踪文件，并对其进行格式化，然后阅读或分析。

--使用一下SQL找到当前session的跟踪文件：

SELECT d.value||'/' ||lower(rtrim(i.instance, chr(0 )))|| '_ora_' ||p.spid||'.trc' trace_file_name
from
( select p.spid from v$mystat m,v$session s, v$process p
where m.statistic# = 1and s.sid = m.sidand p.addr = s.paddr) p,
( select t.instance from v$thread t,v$parameter v
where v.name = 'thread'and (v.value =0or t.thread# = to_number(v.value))) i,
( select value from v$parameter where name = 'user_dump_dest' ) d;
-- 其它用户的 session
    SELECT d.value|| '/' ||lower(rtrim(i.instance, chr(0 )))||'_ora_' ||p.spid||'.trc' trace_file_name
    from
    ( select p.spid from v$session s, v$process p
          where s.sid= '27'and s. SERIAL#='30'and p.addr = s.paddr) p,
    ( select t.instance from v$thread t,v$parameter v
          where v.name = 'thread' and (v.value =0or t.thread# = to_number(v.value))) i,
    ( select value from v$parameter where name ='user_dump_dest' ) d;

 

--查找后使用tkprof命令,将TRACE文件格式为到D盘的explain_format.txt文件中

SQL> $tkprof d:/oracle/admin/FZLGFM/udump/fzlgfm_ora_3468.trc  d:/explain_format.txt

文件内容大致如下(看不太懂....懵懂啊.....天啊....神啊.....过几时就懂了/////////////)

TKPROF: Release 9.2.0.1.0 - Production on 星期二 4月 20 13:59:20 2010

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Trace file: d:/oracle/admin/FZLGFM/udump/fzlgfm_ora_3468.trc

Sort options: default

********************************************************************************

count    = number of times OCI procedure was executed

cpu      = cpu time in seconds executing

elapsed  = elapsed time in seconds executing

disk     = number of physical reads of buffers from disk

query    = number of buffers gotten for consistent read

current  = number of buffers gotten in current mode (usually for update)

rows     = number of rows processed by the fetch or execute call
********************************************************************************

alter session set events '10046 trace name context forever, level 8'

 

call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse        0      0.00       0.00          0          0          0           0

Execute      1      0.00       0.00          0          0          0           0

Fetch        0      0.00       0.00          0          0          0           0

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total        1      0.00       0.00          0          0          0           0

 

Misses in library cache during parse: 0

Misses in library cache during execute: 1

Optimizer goal: CHOOSE

Parsing user id: SYS

======================================================================================================================

在oracle的表里面有一个dateTime的字段，记录精确到秒，我给此字段建立索引之后，为什么在查询区间的时候只有在12个小时范围内，索引生效，超出即不生效
如下面代码
select * from act where id=2001
and cstart_time >= to_date('2011-7-1 02:00:00','yyyy-MM-dd hh24:mi:ss')
and cstart_time < to_date('2011-7-1 18:00:00','yyyy-MM-dd hh24:mi:ss')
若是这样，索引并不生效。
但如果把最后一句改成
and cstart_time < to_date('2011-7-1 14:00:00','yyyy-MM-dd hh24:mi:ss')
索引即生效。请高手指教，是不是我建立的索引有问题，还是在建立索引时对
datetime类型有限制？收起

wholycarputer| 浏览 8176 次

我有更好的答案

邀请更新发布于2011-08-04 17:04最佳答案

这就是ORACLE  CBO优化器的聪明之处了。在ORACLE 的官方文档有这样的话：Create an index if you frequently want to retrieve less than about 15% of therows in a large table.意思就是如果你检索的数据占整个表数据的15%以下的时候建索引。也就是说一般情况下如果你查询的数据占表数据的15%以下的时候，用索引会比全表扫描效率高。如果超过15%的话可能就是全表扫描的效率更好！当然这个15%也不是固定的，也要根据不同情况来分！而ORACLE 的CBO优化器会自动根据表的统计数据和你要检索的数据，去分析是用索引的效率更高还是全表扫描的效率更高。 如果一定要用索引的话，那就加个hints吧!

追问

恩，感谢你的回答，我已经强制执行索引了。再追问一下。因为强制执行索引要写到SQL里面。因为我的表每天早上要有大量数据写入，因此要先删除索引，之后再重新创建索引。故如果在删除索引的这段时间，执行SQL，此时无索引，程序是否出错？

追答

我觉得没什么问题，加个hints只是提示CBO优化器去使用索引，但是CBO也有可能不按照提示执行。不过没测试过，你可以先测试下。还有我觉的先删除索引在重建索引的方式来写入大量数据的方式是不是麻烦点。虽然说没有索引插入会快点，但是重建索引也很需要资源啊，如果是插入的数据都是按dateTime来在现在的表中的日期以后的，是不会引起dateTime索引分割的。

追问

恩已经测试过了。即使索引不存在，也不影响SQL的执行。非常感谢！

============================================================================================================

使用hint技术,表别名+索引名         select/*+         INDEX(pa IDX_PAGREE_1)         INDEX(pi IDX_PITEM_5)         INDEX(pd IDX_PRODUCTS_3)         */  *           from table1           c,               table2     pa,               table3  pi,               table4  pd         where pa.customerid = c.customerid           and pi.purchaseagreementid = pa.id           and pi.productid = pd.id           and pd.statusid=3601;