【Oracle】SQL优化的重要工具--AUTOTRACE

Dear 丶 2022-09-26 02:19 177阅读 0赞

# 【AUTOTRACE】SQL优化的重要工具--AUTOTRACE #

提到**SQL**优化，不能不提**AUTOTRACE**的强大功能。使用起来非常便捷，不过在是使用之前，需要做一些配置的工作。简要的描述一下这个过程，供没有使用过的朋友参考。  
  
**1.使用sys用户执行plustrce脚本**  
sys@ora10g> @?/sqlplus/admin/plustrce  
sys@ora10g> drop role plustrace;  
drop role plustrace  
          \*  
ERROR at line 1:  
ORA-01919: role 'PLUSTRACE' does not exist  
  
  
sys@ora10g> create role plustrace;  
  
Role created.  
  
sys@ora10g> grant select on v\_$sesstat to plustrace;  
  
Grant succeeded.  
  
sys@ora10g> grant select on v\_$statname to plustrace;  
  
Grant succeeded.  
  
sys@ora10g> grant select on v\_$mystat to plustrace;  
  
Grant succeeded.  
  
sys@ora10g> grant plustrace to dba with admin option;  
  
Grant succeeded.  
  
sys@ora10g> set echo off  
  
**2.将plustrace授权给public用户，以便保证所有用户都有权使用autotrace功能**  
sys@ora10g> grant plustrace to public;  
  
Grant succeeded.  
  
**3.连接到具体的用户中，这里是sec用户**  
sys@ora10g> conn sec/sec  
Connected.  
  
**4.使用“set autotrace”查看autotrace的命令提示**  
sec@ora10g> set autotrace  
Usage: SET AUTOT\[RACE\] \{OFF | ON | TRACE\[ONLY\]\} \[EXP\[LAIN\]\] \[STAT\[ISTICS\]\]  
  
**5.这里演示一种设置方法**  
sec@ora10g> set autotrace traceonly  
sec@ora10g> select \* from cat;  
  
20 rows selected.  
  
  
Execution Plan  
\----------------------------------------------------------  
Plan hash value: 3442307057  
  
\--------------------------------------------------------------------------------  
| Id  | Operation             | Name   | Rows  | Bytes | Cost (%CPU)| Time     |  
\--------------------------------------------------------------------------------  
|   0 | SELECT STATEMENT      |        |   646 | 47804 |    33   (4)| 00:00:01 |  
|\*  1 |  FILTER               |        |       |       |            |          |  
|\*  2 |   TABLE ACCESS FULL   | OBJ$   |  1324 | 97976 |    33   (4)| 00:00:01 |  
|\*  3 |   TABLE ACCESS CLUSTER| TAB$   |     1 |    12 |     2   (0)| 00:00:01 |  
|\*  4 |    INDEX UNIQUE SCAN  | I\_OBJ\# |     1 |       |     1   (0)| 00:00:01 |  
\--------------------------------------------------------------------------------  
  
Predicate Information (identified by operation id):  
\---------------------------------------------------  
  
   1 - filter("O"."TYPE\#"=4 OR "O"."TYPE\#"=5 OR "O"."TYPE\#"=6 OR  
              "O"."TYPE\#"=2 AND  NOT EXISTS (SELECT /\*+ \*/ 0 FROM "SYS"."TAB$" "T"  
              WHERE "T"."OBJ\#"=:B1 AND (BITAND("T"."PROPERTY",512)=512 OR  
              BITAND("T"."PROPERTY",8192)=8192)))  
   2 - filter("O"."OWNER\#"=USERENV('SCHEMAID') AND "O"."LINKNAME" IS  
              NULL)  
   3 - filter(BITAND("T"."PROPERTY",512)=512 OR  
              BITAND("T"."PROPERTY",8192)=8192)  
   4 - access("T"."OBJ\#"=:B1)  
  
  
Statistics  
\----------------------------------------------------------  
          0  recursive calls  
          0  db block gets  
        207  consistent gets  
          0  physical reads  
          0  redo size  
        876  bytes sent via SQL\*Net to client  
        492  bytes received via SQL\*Net from client  
          2  SQL\*Net roundtrips to/from client  
          0  sorts (memory)  
          0  sorts (disk)  
         20  rows processed  
  
更多的有趣内容，请您慢慢挖掘。  
  
\-- The End --