Oracle 优化（一）

￡神魔★判官ぃ 2022-06-14 00:24 215阅读 0赞

**SQL\*PLUS下使用AUTOTRACE**

**1.AUTOTRACE简介  **

AUTOTRACE是SQL\*Plus的一项功能，其作用是自动跟踪SQL语句，为SQL 语句生成一个 **执行计划**并且提供与 **该语句的处理有关的统计信息**。

SQL\*Plus AUTOTRACE 可以用来替代 SQL Trace 使用，AUTOTRACE 的好处是不必设置跟踪文件的格式，并且它将自动为 SQL 语句显示执行计划。AUTOTRACE与执行计划的区别是AUTOTRACE 分析和执行语句；而EXPLAIN PLAN仅分析语句，而不负责执行语句。

AUTOTRACE在SQL\*PLUS下执行，使用AUTOTRACE不会产生跟踪文件。

**2.配置AUTOTRACE**

(1).确保表PLAN\_TABLE已经创建，如果没有则如下创建：  
orcl:/u01/app/oracle/product/11.2.0/db\_1/rdbms/admin@oracle>ll utlxplan.sql  
\-rw-r--r-- 1 oracle oinstall 3267 6月   6 2004 utlxplan.sql

SQL> @?/rdbms/admin/utlxplan.sql

Table created.

SQL> create public synonym plan\_table$ for plan\_table;

Synonym created.

SQL> grant all on plan\_table$ to public;

Grant succeeded.

SQL>

(2).确保角色plustrace已经创建，如果没有则如下创建：

**3.使用AUTOTRACE**

SQL> set autotrace on;  
SQL>  
SQL> select count(\*) from dba\_objects;

COUNT(\*)  
\----------  
     91989

Execution Plan  
\----------------------------------------------------------  
Plan hash value: 2509769952

\--------------------------------------------------------------------------------  
\--------

\--------------------------------------------------------------------------------  
\--------

|   0 | SELECT STATEMENT  |        |     1 |       |   402  (1)| 00  
:00:04 |

|   1 |  SORT AGGREGATE   |        |     1 |       |     |  
       |

|   2 |   VIEW    | DBA\_OBJECTS | 90218 |       |   402  (1)| 00  
:00:04 |

|   3 |    UNION-ALL   |        |       |       |     |  
       |

|\*  4 |     FILTER   |        |       |       |     |  
       |

|\*  5 |      HASH JOIN   |        | 90217 |  9691K|   402  (1)| 00  
:00:04 |

|   6 |       INDEX FULL SCAN  | I\_USER2     |    92 |   368 |     1  (0)| 00  
:00:01 |

|\*  7 |       HASH JOIN   |        | 90217 |  9338K|   401  (1)| 00  
:00:04 |

|   8 |        INDEX FULL SCAN  | I\_USER2     |    92 |  2024 |     1  (0)| 00  
:00:01 |

|\*  9 |        TABLE ACCESS FULL | OBJ$        | 90217 |  7400K|   400  (1)| 00  
:00:04 |

|  10 |      NESTED LOOPS  |        |     1 |    29 |     2  (0)| 00  
:00:01 |

|\* 11 |       INDEX SKIP SCAN  | I\_USER2     |     1 |    20 |     1  (0)| 00  
:00:01 |

|\* 12 |       INDEX RANGE SCAN  | I\_OBJ4      |     1 |     9 |     1  (0)| 00  
:00:01 |

|  13 |     NESTED LOOPS  |        |     1 |    17 |     0  (0)| 00  
:00:01 |

|  14 |      INDEX FULL SCAN  | I\_LINK1     |     1 |    13 |     0  (0)| 00  
:00:01 |

|  15 |      TABLE ACCESS CLUSTER| USER$       |     1 |     4 |     0  (0)| 00  
:00:01 |

|\* 16 |       INDEX UNIQUE SCAN  | I\_USER\#     |     1 |       |     0  (0)| 00  
:00:01 |

\--------------------------------------------------------------------------------  
\--------

Predicate Information (identified by operation id):  
\---------------------------------------------------

4 - filter("O"."TYPE\#"<>4 AND "O"."TYPE\#"<>5 AND "O"."TYPE\#"<>7 AND  
       "O"."TYPE\#"<>8 AND "O"."TYPE\#"<>9 AND "O"."TYPE\#"<>11 AND "O"."TYP  
E\#"<>12 AND

"O"."TYPE\#"<>13 AND "O"."TYPE\#"<>14 AND "O"."TYPE\#"<>22 AND "O"."T  
YPE\#"<>87 AND

"O"."TYPE\#"<>88 OR BITAND("U"."SPARE1",16)=0 OR ("O"."TYPE\#"=4 OR  
"O"."TYPE\#"=5

OR "O"."TYPE\#"=7 OR "O"."TYPE\#"=8 OR "O"."TYPE\#"=9 OR "O"."TYPE\#"=  
10 OR

"O"."TYPE\#"=11 OR "O"."TYPE\#"=12 OR "O"."TYPE\#"=13 OR "O"."TYPE\#"=  
14 OR

"O"."TYPE\#"=22 OR "O"."TYPE\#"=87) AND (SYS\_CONTEXT('userenv','curr  
ent\_edition\_na

me')='ORA$BASE' AND "U"."TYPE\#"<>2 OR "U"."TYPE\#"=2 AND  
       "U"."SPARE2"=TO\_NUMBER(SYS\_CONTEXT('userenv','current\_edition\_id')  
) OR  EXISTS

(SELECT 0 FROM SYS."USER$" "U2",SYS."OBJ$" "O2" WHERE  
       "O2"."OWNER\#"="U2"."USER\#" AND "O2"."TYPE\#"=88 AND "O2"."DATAOBJ\#"  
=:B1 AND

"U2"."TYPE\#"=2 AND "U2"."SPARE2"=TO\_NUMBER(SYS\_CONTEXT('userenv','  
current\_editio

n\_id')))))  
   5 - access("O"."SPARE3"="U"."USER\#")  
   7 - access("O"."OWNER\#"="U"."USER\#")  
   9 - filter("O"."TYPE\#"<>10 AND BITAND("O"."FLAGS",128)=0 AND  
       "O"."NAME"<>'\_NEXT\_OBJECT' AND "O"."NAME"<>'\_default\_auditing\_opti  
ons\_' AND

"O"."LINKNAME" IS NULL)  
  11 - access("U2"."TYPE\#"=2 AND "U2"."SPARE2"=TO\_NUMBER(SYS\_CONTEXT('userenv','  
       current\_edition\_id')))  
       filter("U2"."TYPE\#"=2 AND "U2"."SPARE2"=TO\_NUMBER(SYS\_CONTEXT('userenv','  
       current\_edition\_id')))  
  12 - access("O2"."DATAOBJ\#"=:B1 AND "O2"."TYPE\#"=88 AND  
       "O2"."OWNER\#"="U2"."USER\#")  
  16 - access("L"."OWNER\#"="U"."USER\#")

Statistics  
\----------------------------------------------------------  
   0  recursive calls  
   0  db block gets  
       1168  consistent gets  
   0  physical reads  
   0  redo size  
 528  bytes sent via SQL\*Net to client  
 524  bytes received via SQL\*Net from client  
   2  SQL\*Net roundtrips to/from client  
   0  sorts (memory)  
   0  sorts (disk)  
   1  rows processed

**4. AUTOTRACE设置命令 **

<table style="width:472px; height:207px"> 
 <tbody> 
  <tr> 
   <td> 
    <div> 
     <strong>序号</strong> 
    </div> </td> 
   <td> 
    <div> 
     <strong>命令</strong> 
    </div> </td> 
   <td> 
    <div> 
     <strong>解释</strong> 
    </div> </td> 
  </tr> 
  <tr> 
   <td> 
    <div>
      1 
    </div> </td> 
   <td> 
    <div>
      SET AUTOTRACE OFF 
    </div> </td> 
   <td> 
    <div>
      此为默认值，即关闭Autotrace 
    </div> </td> 
  </tr> 
  <tr> 
   <td> 
    <div>
      2 
    </div> </td> 
   <td> 
    <div>
      SET AUTOTRACE ON 
    </div> </td> 
   <td> 
    <div>
      产生结果集和解释计划并列出统计 
    </div> </td> 
  </tr> 
  <tr> 
   <td> 
    <div>
      3 
    </div> </td> 
   <td> 
    <div>
      SET AUTOTRACE ON EXPLAIN 
    </div> </td> 
   <td> 
    <div>
      显示结果集和解释计划不显示统计 
    </div> </td> 
  </tr> 
  <tr> 
   <td> 
    <div>
      4 
    </div> </td> 
   <td> 
    <div>
      SETAUTOTRACE TRACEONLY 
    </div> </td> 
   <td> 
    <div>
      显示解释计划和统计，尽管执行该语句但将看不到结果集 
    </div> </td> 
  </tr> 
  <tr> 
   <td> 
    <div>
      5 
    </div> </td> 
   <td> 
    <div>
      SET AUTOTRACE TRACEONLY STATISTICS 
    </div> </td> 
   <td> 
    <div>
      只显示统计 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

5.    AUTOTRACE STATISTICS含义

<table style="width:472px; height:275px"> 
 <tbody> 
  <tr> 
   <td> 
    <div> 
     <strong>序号</strong> 
    </div> </td> 
   <td> 
    <div> 
     <strong>列名</strong> 
    </div> </td> 
   <td> 
    <div> 
     <strong>解释</strong> 
    </div> </td> 
  </tr> 
  <tr> 
   <td> 
    <div>
      1 
    </div> </td> 
   <td> 
    <div>
      recursive call 
    </div> </td> 
   <td> 
    <div>
      递归调用SQL的个数；Oracle在执行这个SQL的时候，有时候会生成很多额外的SQL语句，这个就称为递归调用 
    </div> </td> 
  </tr> 
  <tr> 
   <td> 
    <div>
      2 
    </div> </td> 
   <td> 
    <div>
      db block gets 
    </div> </td> 
   <td> 
    <div>
      从buffer cache中读取的block的数量 
    </div> </td> 
  </tr> 
  <tr> 
   <td> 
    <div>
      3 
    </div> </td> 
   <td> 
    <div>
      consistent gets 
    </div> </td> 
   <td> 
    <div>
      从buffer cache中读取的undo数据的block的数量 
    </div> </td> 
  </tr> 
  <tr> 
   <td> 
    <div>
      4 
    </div> </td> 
   <td> 
    <div>
      physical reads 
    </div> </td> 
   <td> 
    <div>
      从磁盘读取的block的数量 
    </div> </td> 
  </tr> 
  <tr> 
   <td> 
    <div>
      5 
    </div> </td> 
   <td> 
    <div>
      redo size 
    </div> </td> 
   <td> 
    <div>
      DML生成的redo的大小 
    </div> </td> 
  </tr> 
  <tr> 
   <td> 
    <div>
      6 
    </div> </td> 
   <td> 
    <div>
      sorts (memory) 
    </div> </td> 
   <td> 
    <div>
      在内存执行的排序量 
    </div> </td> 
  </tr> 
  <tr> 
   <td> 
    <div>
      7 
    </div> </td> 
   <td> 
    <div>
      sorts (disk) 
    </div> </td> 
   <td> 
    <div>
      在磁盘上执行的排序量,如果memory空间使用不足，是会使用disk的空间的 
    </div> </td> 
  </tr> 
  <tr> 
   <td> 
    <div>
      8 
    </div> </td> 
   <td> 
    <div>
      bytes sent via SQL*Net to client 
    </div> </td> 
   <td> 
    <div>
      利用sql*net传入到client的字节数； 
    </div> </td> 
  </tr> 
  <tr> 
   <td> 
    <div>
      9 
    </div> </td> 
   <td> 
    <div>
      bytes received via SQL*Net from client 
    </div> </td> 
   <td> 
    <div>
      利用sql*net传出client的字节数； 
    </div> </td> 
  </tr> 
 </tbody> 
</table>