Shulou(Shulou.com)06/01 Report--

Turning on the autotrace switch in sqlplus can also get the execution plan of the target sql, and in addition, you can also get the resource consumption of the target sql during execution, that is, by setting the AUTOTRACE switch, we can observe the additional physical reads, logical reads, and the sorted number of redo generated by the target sql execution.

(1) execute the command set autotrace on in the current session of sqlplus, and the autotrace switch can be fully turned on in the current session. In this way, all subsequent sql executed in this session will show the execution plan and resource consumption corresponding to these sql in addition to the results of the sql execution.

(2) when you execute the command set autotrace off in the current session of sqlplus, you can turn off the autotrace switch in the current session, so that all subsequent sql executed in this session will only display the result of sql execution, and the default value of autotrace switch is OFF.

(3) when you execute set autotrace traceonly in the current session of sqlplus, you can completely turn on the autotrace switch in the current session without displaying the specific content of the execution result of sql. The only difference between this method and set autotrace on is that for set autotrace traceonly, oracle only displays the number of execution results of sql, but not the specific content of execution results. In this case, we often do not care about the specific content of the execution results of these sql. They are only concerned about their implementation plans and resource consumption.

(4) if you execute set autotrace traceonly explain in the current session of sqlplus, you can turn on the autotrace switch in the current session to show only the paradigm of the sql execution plan. This approach differs from set autotrace traceonly in that set autotrace traceonly explain does not show the resource consumption and execution results of the target sql, but only the execution plan of the target sql.

(5) by executing the command set autotrace traceonly statistic in the current session of sqlplus, you can turn on the autotrace switch in the current session to show only the resource consumption of sql. The only difference between this approach and set autotrace traceonly is that set autotrace traceonly statistic does not show the execution plan of the target sql, but only the number of execution results and resource consumption of the target sql.

The commands for setting the autotrace switch also follow oracle's consistent convention that you can use abbreviations, specifically:

The keyword autotrace can be abbreviated to autot

The keyword traceonly can be abbreviated to trace

The keyword explain can be abbreviated to exp

The keyword statistic can be abbreviated to stat

According to the above abbreviation rules, the following words are equivalent

Set autotrace on is abbreviated as set autot on

Set autotrace off is abbreviated as set autot off

Set autotrace traceonly is abbreviated as set autot trace

Set autotrace traceonly explain is abbreviated as set autot trace exp

Set autotrace traceonly statistic is abbreviated as set autot trace stat

Here we will use select empno,ename,dname from emp adept b where a.deptnob.deptno; to illustrate:

Use set autotrace on first

SQL > select empno,ename,dname from emp adept b where a.deptno=b.deptno

EMPNO ENAME DNAME

7782 CLARK ACCOUNTING

7839 KING ACCOUNTING

7934 MILLER ACCOUNTING

7566 JONES RESEARCH

7902 FORD RESEARCH

7876 ADAMS RESEARCH

7369 SMITH RESEARCH

7788 SCOTT RESEARCH

7521 WARD SALES

7844 TURNER SALES

7499 ALLEN SALES

EMPNO ENAME DNAME

7900 JAMES SALES

7698 BLAKE SALES

7654 MARTIN SALES

14 rows selected.

Execution Plan

Plan hash value: 844388907

-

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

-

| | 0 | SELECT STATEMENT | | 14 | 364 | 6 (17) | 00:00:01 |

| | 1 | MERGE JOIN | | 14 | 364 | 6 (17) | 00:00:01 |

| | 2 | TABLE ACCESS BY INDEX ROWID | DEPT | 4 | 52 | 2 (0) | 00:00:01 |

| | 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0) | 00:00:01 |

| | * 4 | SORT JOIN | | 14 | 182 | 4 (25) | 00:00:01 |

| | 5 | TABLE ACCESS FULL | EMP | 14 | 182 | 3 (0) | 00:00:01 |

-

Predicate Information (identified by operation id):

4-access ("A". "DEPTNO" = "B". "DEPTNO")

Filter ("A". "DEPTNO" = "B". "DEPTNO")

Statistics

0 recursive calls

0 db block gets

10 consistent gets

0 physical reads

0 redo size

941 bytes sent via SQL*Net to client

524 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

1 sorts (memory)

0 sorts (disk)

14 rows processed

We can see that executing the target sql after set autotrace on shows the execution plan and resource consumption of the sql in addition to the specific execution results.

Then use set autotrace traceonly

SQL > select empno,ename,dname from emp adept b where a.deptno=b.deptno

14 rows selected.

Execution Plan

Plan hash value: 844388907

-

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

-

| | 0 | SELECT STATEMENT | | 14 | 364 | 6 (17) | 00:00:01 |

| | 1 | MERGE JOIN | | 14 | 364 | 6 (17) | 00:00:01 |

| | 2 | TABLE ACCESS BY INDEX ROWID | DEPT | 4 | 52 | 2 (0) | 00:00:01 |

| | 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0) | 00:00:01 |

| | * 4 | SORT JOIN | | 14 | 182 | 4 (25) | 00:00:01 |

| | 5 | TABLE ACCESS FULL | EMP | 14 | 182 | 3 (0) | 00:00:01 |

-

Predicate Information (identified by operation id):

4-access ("A". "DEPTNO" = "B". "DEPTNO")

Filter ("A". "DEPTNO" = "B". "DEPTNO")

Statistics

0 recursive calls

0 db block gets

10 consistent gets

0 physical reads

0 redo size

941 bytes sent via SQL*Net to client

524 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

1 sorts (memory)

0 sorts (disk)

14 rows processed

We can see that when the target sql is executed after the set autotrace traceonly is executed, except that the specific content of the execution result of the sql is not displayed, the other results are exactly the same as those using set autotrace on.

Let's use set autotrace traceonly explain

SQL > set autotrace traceonly explain

SQL > select empno,ename,dname from emp adept b where a.deptno=b.deptno

Execution Plan

Plan hash value: 844388907

-

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

-

| | 0 | SELECT STATEMENT | | 14 | 364 | 6 (17) | 00:00:01 |

| | 1 | MERGE JOIN | | 14 | 364 | 6 (17) | 00:00:01 |

| | 2 | TABLE ACCESS BY INDEX ROWID | DEPT | 4 | 52 | 2 (0) | 00:00:01 |

| | 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0) | 00:00:01 |

| | * 4 | SORT JOIN | | 14 | 182 | 4 (25) | 00:00:01 |

| | 5 | TABLE ACCESS FULL | EMP | 14 | 182 | 3 (0) | 00:00:01 |

-

Predicate Information (identified by operation id):

4-access ("A". "DEPTNO" = "B". "DEPTNO")

Filter ("A". "DEPTNO" = "B". "DEPTNO")

We can see that the target sql is executed after the set autotrace traceonly explain, showing only the execution plan of that sql.

Finally, let's use set autotrace traceonly statistic

SQL > select empno,ename,dname from emp adept b where a.deptno=b.deptno

14 rows selected.

Statistics

0 recursive calls

0 db block gets

10 consistent gets

0 physical reads

0 redo size

941 bytes sent via SQL*Net to client

524 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

1 sorts (memory)

0 sorts (disk)

14 rows processed

We can see that the target sql is executed after the set autotrace traceonly statistic is executed, showing only the number of execution results of that sql and resource usage consumption.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.