ACCESSOracle试行陈设与总结音讯的片段总计

Execution Plan

  0      SELECT STATEMENT Optimizer=CHOOSE
  1    0   SORT (AGGREGATE)
  2    1     VIEW OF ‘DBA_OBJECTS’
  3    2       UNION-ALL
  4    3         FILTER
  5    4           TABLE ACCESS (BY INDEX ROWID) OF ‘OBJ$’
  6    5             NESTED LOOPS
  7    6               TABLE ACCESS (FULL) OF ‘USER$’
8    6               INDEX (RANGE SCAN) OF ‘I_OBJ2’ (UNIQUE)
  9    4           TABLE ACCESS (BY INDEX ROWID) OF ‘IND$’
10    9             INDEX (UNIQUE SCAN) OF ‘I_IND1’ (UNIQUE)
11    3         NESTED LOOPS
12   11           TABLE ACCESS (FULL) OF ‘USER$’
13   11           INDEX (RANGE SCAN) OF ‘I_LINK1’ (NON-UNIQUE)

Execution Plan

  0      SELECT STATEMENT Optimizer=CHOOSE
  1    0   VIEW OF ‘DBA_OBJECTS’
  2    1     UNION-ALL
  3    2       FILTER
  4    3         TABLE ACCESS (BY INDEX ROWID) OF ‘OBJ$’
  5    4           NESTED LOOPS
  6    5             TABLE ACCESS (FULL) OF ‘USER$’
  7    5             INDEX (RANGE SCAN) OF ‘I_OBJ2’ (UNIQUE)
  8    3         TABLE ACCESS (BY INDEX ROWID) OF ‘IND$’
  9    8           INDEX (UNIQUE SCAN) OF ‘I_IND1’ (UNIQUE)
10    2       TABLE ACCESS (BY INDEX ROWID) OF ‘LINK$’
11   10         NESTED LOOPS
12   11           TABLE ACCESS (FULL) OF ‘USER$’
13   11           INDEX (RANGE SCAN) OF ‘I_LINK1’ (NON-UNIQUE)

5、analyze

analyze table hr.employees compute(estimate)
statistics;(compute搜聚每1行数据的总括音信,比较耗时;estimate搜集壹些数据行的总括音讯)

select
t.owner,t.table_name,t.tablespace_name,t.blocks,t.empty_blocks,t.avg_space
from dba_tables t
where t.owner=’HR’;

Time      |

|    0 | SELECT STATEMENT   |       |      5 |    150 |      3    (0)|
00:00:01 |
|*   1 |   TABLE ACCESS FULL| EMP   |      5 |    150 |      3    (0)|

Statistics

         0 recursive calls
         0 db block gets
     25754 consistent gets
         0 physical reads
         0 redo size
       383 bytes sent via SQL*Net to client
       503 bytes received via SQL*Net from client
         2 SQL*Net roundtrips to/from client
         0 sorts (memory)
         0 sorts (disk)
         1 rows processed
(3). set autotrace traceonly;–同set autotrace on 只是不出示查询输出
SQL> set autotrace traceonly;
SQL> select count(*) from dba_objects;

PLAN_TABLE_OUTPUT

    1 – filter(“DEPTNO”=20)

13 rows selected.

3、SQL>exec
dbms_stats.delete_table_stats(USECRUISER,’表’);(删除表的总括音讯)

SQL>exec
dbms_stats.gather_table_stats(USER,’表’,METHOD_OPT=>’FO帕杰罗 ALL
COLUMNS SIZE 拾0′)(采撷表的总括消息)

四、AUTOTRACE的多少个常用选项

       set autotrace off —————- 不生成autotrace
报告,那是缺省情势
set autotrace on explain —— autotrace只展现优化器试行路线报告
set autotrace on statistics — 只展现实行总括消息
set autotrace on —————– 包罗实践安插和总结新闻
set autotrace traceonly —— 同set autotrace on,不过不显得查询输

(1). set autotrace on explain; –只体现推行布置
SQL> set autotrace on explain;
SQL> 

select count(*) from dba_objects;

COUNT(*)

    31820

00:00:01 |

Execution Plan

  0      SELECT STATEMENT Optimizer=CHOOSE
  1    0   SORT (AGGREGATE)
  2    1     VIEW OF ‘DBA_OBJECTS’
  3    2       UNION-ALL
  4    3         FILTER
  5    4           TABLE ACCESS (BY INDEX ROWID) OF ‘OBJ$’
  6    5             NESTED LOOPS
  7    6               TABLE ACCESS (FULL) OF ‘USER$’
  8    6               INDEX (RANGE SCAN) OF ‘I_OBJ2’ (UNIQUE)
  9    4           TABLE ACCESS (BY INDEX ROWID) OF ‘IND$’
10    9             INDEX (UNIQUE SCAN) OF ‘I_IND1’ (UNIQUE)
11    3         NESTED LOOPS
12   11           TABLE ACCESS (FULL) OF ‘USER$’
13   11           INDEX (RANGE SCAN) OF ‘I_LINK1’ (NON-UNIQUE)
(二). set autotrace on statistics;–只展现计算消息
SQL> set autotrace on statistics;
SQL> select count(*) from dba_objects;

 

[日期:2011-08-05]
来源:Linux社区  作者:wangshengfeng1986211
[字体:

]

2010-07-01 15:03

1、SET AUTOTRACE ON EXPLAIN
(set autot on exp)
SQLPLUS的下令,在实施SQL语句的同时体现推行布署,设置EXP(LAIN)的目标是只呈现推行布置而不出示计算消息.。
2、SQL>explain plan for select ““““;
SQL>select * from table(dbms_xplan.display);

实施了set autotrace on
explain语句之后,接下去的查询、插入、更新、删除语句就会显得实践安排,直到施行“set
autotrace off;”语句。假设是安装了set autotrace
on,除了会来得试行布署之外,还会议及展览示一些管用的计算音信。

施行EXPLAIN PLAN FO奥迪Q5 可以只显示实践布置,然后施行如下查询

SQL> select * from table(dbms_xplan.display);

如:

SQL> explain plan for select * from emp where deptno=’20’;

Explained.

SQL> select * from table(dbms_xplan.display);

COUNT(*)

    31820

Statistics

         0 recursive calls
         0 db block gets
     25754 consistent gets
         0 physical reads
         0 redo size
       383 bytes sent via SQL*Net to client
       503 bytes received via SQL*Net from client
         2 SQL*Net roundtrips to/from client
         0 sorts (memory)
         0 sorts (disk)
         1 rows processed
(四).set autotrace traceonly
explain;–相比实用的选项,只体现实践陈设,不过与set autotrace on
explain;相比较不会举行语句,对于只有查看大表的Explain Plan极度实用。
SQL> set autotrace traceonly explain;
SQL> select * from dba_objects;
已用时间: 00: 00: 00.00

Predicate Information (identified by operation id):

PLAN_TABLE_ACCESS,OUTPUT

Plan hash value: 3956160932


| Id   | Operation          | Name | Rows   | Bytes | Cost (%CPU)|

相关文章