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

This article shows you what is the difference between EXPLAIN PLAN FOR and SET AUTOTRACE. The content is concise and easy to understand. It will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

Usually we use EXPLAIN PLAN FOR and SET AUTOTRACE to see the execution plan of the sql statement, so here is a small experiment to see the difference between the two methods in terms of sql execution.

Yang@rac1 > create table yang_t as select * from t

Table created.

Yang@rac1 > set autot on exp

Yang@rac1 > select * from yang_t

ID NAME

--

130864 YANG_SEQ

132031 YANG_A

132032 SYS_C0066382

132033 YANG_B

132034 SYS_C0066383

132035 FACT

132036 MLOG$_YANG_A

132037 MLOG$_YANG_B

132038 MLOG$_FACT

132039 T

131949 YANG_ROWID

131951 YANG_PK

131952 SYS_C0066303

131955 YANG_OBJECT

131956 YANG_OID

131957 SYS_C0066304

132018 YANG_C

132017 MV_CAPABILITIES_TABLE

132030 MLOG$_YANG_PK

132027 MLOG$_YANG_ROWID

LINKORACL

LINKYANG

22 rows selected.

Execution Plan

Plan hash value: 2508602004

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

| | 0 | SELECT STATEMENT | | 22 | 1738 | 3 (0) | 00:00:01 |

| | 1 | TABLE ACCESS FULL | YANG_T | 22 | 1738 | 3 (0) | 00:00:01 |

Note

-

-dynamic sampling used for this statement (level=2)

Yang@rac1 > set autot off

Yang@rac1 > select executions,parse_calls from v$sqlarea where sql_text like 'select * from yang_t'

EXECUTIONS PARSE_CALLS

--

1 1

Yang@rac1 > set autot on exp

Yang@rac1 > set autotrace traceonly

Yang@rac1 > select * from yang_t

22 rows selected.

Execution Plan

Plan hash value: 2508602004

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

| | 0 | SELECT STATEMENT | | 22 | 1738 | 3 (0) | 00:00:01 |

| | 1 | TABLE ACCESS FULL | YANG_T | 22 | 1738 | 3 (0) | 00:00:01 |

Note

-

-dynamic sampling used for this statement (level=2)

Statistics

21 recursive calls

61 db block gets

33 consistent gets

6 physical reads

14040 redo size

1082 bytes sent via SQL*Net to client

520 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

22 rows processed

Yang@rac1 > set autot off

Query again whether to execute or not. You can see that when you use set autotrace to view the execution plan, oracle executes the sql statement.

Yang@rac1 > select executions,parse_calls from v$sqlarea where sql_text like 'select * from yang_t'

EXECUTIONS PARSE_CALLS

--

2 2

Dml the test table. And check the implementation of dml.

Yang@rac1 > insert into yang_t values (1Jue 2)

1 row created.

Yang@rac1 > commit

Commit complete.

Yang@rac1 > select executions,parse_calls from v$sqlarea where sql_text like 'insert into yang_t values (1BI 2);

No rows selected

Yang@rac1 > select executions,parse_calls from v$sqlarea where sql_text like 'insert into yang_t values%'

EXECUTIONS PARSE_CALLS

--

1 1

= EXPLAIN PLAN FOR =

Experiment with EXPLAIN PLAN to see the execution plan of the sql statement.

Yang@rac1 > EXPLAIN PLAN FOR SELECT * FROM YANG_T

Explained.

Yang@rac1 > SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY)

PLAN_TABLE_OUTPUT

-

Plan hash value: 2508602004

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

| | 0 | SELECT STATEMENT | | 23 | 1817 | 4 (0) | 00:00:01 |

| | 1 | TABLE ACCESS FULL | YANG_T | 23 | 1817 | 4 (0) | 00:00:01 |

Note

-

-dynamic sampling used for this statement (level=2)

12 rows selected.

You can see from the query results below that oracle did not execute the sql statement to view the execution plan when using EXPLAIN PLAN FOR to view the execution plan.

Yang@rac1 > select executions,parse_calls from v$sqlarea where sql_text like 'SELECT * FROM YANG_T%'

No rows selected

Yang@rac1 > select executions,parse_calls from v$sqlarea where sql_text like 'SELECT * FROM YANG%'

No rows selected

Yang@rac1 > select executions,parse_calls from v$sqlarea where sql_text like 'select * from yang_t'

EXECUTIONS PARSE_CALLS

--

2 2

Yang@rac1 >

Summary:

When viewing the execution plan in EXPLAIN PLAN FOR mode, the oracle itself does not actually execute the sql statement, but only parses the sql to get the execution plan.

To view the execution plan of the sql statement in SET AUTOTRACE mode, oracle parses and executes the sql.

What is the difference between EXPLAIN PLAN FOR and SET AUTOTRACE? have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

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.