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

Set autotrace off does not generate autotrace reports, default

The set autotrace on explainautotrace report only shows the best way to perform (optimizer execution path)

SQL > set autotrace on explain

-insert data to test whether sql has been executed

SQL > insert into scott.emp values ('5566', 'five hundred and sixty six')

1 row created.

-it has been indicated that sql has been implemented

-only shows the execution plan

Execution Plan

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

| | 0 | INSERT STATEMENT | | 1 | 100 | 1 (0) | ss00:00:01 |

| | 1 | LOAD TABLE CONVENTIONAL | EMP |

SQL > select * from scott.emp where empno=5566

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

--

5566 explain

Execution Plan

Plan hash value: 2949544139

-

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

-

| | 0 | SELECT STATEMENT | | 1 | 87 | 2 (0) | 00:00:01 |

| | 1 | TABLE ACCESS BY INDEX ROWID | EMP | 1 | 87 | 2 (0) | 00:00:01 |

| | * 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 1 (0) | 00:00:01 |

-

Predicate Information (identified by operation id):

2-access ("EMPNO" = 5566)

Set autotrace on statisticsautotrace reports sql execution statistics (SQL statement execution statistics. )

SQL > set autotrace on statistics

SQL > insert into scott.emp values ('6677 records, statistics, records, books, books

1 row created.

-indicates that sql has been executed

-display only the statistics performed by sql

Statistics

52 recursive calls

5 db block gets

80 consistent gets

0 physical reads

520 redo size

839 bytes sent via SQL*Net to client

826 bytes received via SQL*Net from client

3 SQL*Net roundtrips to/from client

10 sorts (memory)

0 sorts (disk)

1 rows processed

SQL > select * from scott.emp where empno=6677

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

--

6677 statistics

Statistics

1 recursive calls

0 db block gets

2 consistent gets

0 physical reads

0 redo size

869 bytes sent via SQL*Net to client

512 bytes received via SQL*Net from client

1 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

The set autotrace onautotrace report contains the best execution method and sql execution statistics

SQL > set autotrace on

SQL > insert into scott.emp values ('8899, June, week, month, month, week, week, day, day, week, week, week,

1 row created.

-equivalent to the sum of set autotrace on explain and set autotrace on statistics, outputting both execution plans and statistics, as well as sql

Execution Plan

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

| | 0 | INSERT STATEMENT | | 1 | 100 | 1 (0) | 00:00:01 |

| | 1 | LOAD TABLE CONVENTIONAL | EMP |

Statistics

45 recursive calls

5 db block gets

77 consistent gets

0 physical reads

512 redo size

839 bytes sent via SQL*Net to client

816 bytes received via SQL*Net from client

3 SQL*Net roundtrips to/from client

10 sorts (memory)

0 sorts (disk)

1 rows processed

SQL > select * from scott.emp where empno=8899

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

--

8899 on

Execution Plan

Plan hash value: 2949544139

-

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

-

| | 0 | SELECT STATEMENT | | 1 | 87 | 2 (0) | 00:00:01 |

| | 1 | TABLE ACCESS BY INDEX ROWID | EMP | 1 | 87 | 2 (0) | 00:00:01 |

| | * 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 1 (0) | 00:00:01 |

-

Predicate Information (identified by operation id):

2-access ("EMPNO" = 8899)

Statistics

9 recursive calls

0 db block gets

18 consistent gets

0 physical reads

0 redo size

861 bytes sent via SQL*Net to client

512 bytes received via SQL*Net from client

1 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

Set autotrace traceonly is similar to set autotrace on, but does not output the results of user queries

SQL > set autotrace traceonly

SQL > insert into scott.emp values ('9900', '9900', 'traceonlyly.'

1 row created.

-similar to set autotrace on, execution plans and statistics are output

Execution Plan

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

| | 0 | INSERT STATEMENT | | 1 | 100 | 1 (0) | 00:00:01 |

| | 1 | LOAD TABLE CONVENTIONAL | EMP |

Statistics

108 recursive calls

5 db block gets

191 consistent gets

1 physical reads

520 redo size

839 bytes sent via SQL*Net to client

825 bytes received via SQL*Net from client

3 SQL*Net roundtrips to/from client

15 sorts (memory)

0 sorts (disk)

1 rows processed

SQL > select * from scott.emp where empno=9900

-unlike set autotrace on, no query results are output

Execution Plan

Plan hash value: 2949544139

-

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

-

| | 0 | SELECT STATEMENT | | 1 | 87 | 2 (0) | 00:00:01 |

| | 1 | TABLE ACCESS BY INDEX ROWID | EMP | 1 | 87 | 2 (0) | 00:00:01 |

| | * 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 1 (0) | 00:00:01 |

-

Predicate Information (identified by operation id):

2-access ("EMPNO" = 9900)

Statistics

9 recursive calls

0 db block gets

18 consistent gets

0 physical reads

0 redo size

867 bytes sent via SQL*Net to client

512 bytes received via SQL*Net from client

1 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

SQL >

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.