View SQL Execution plans and statistics in Oracle

-- Obtain the SQL Execution Plan and statistics without displaying the Query Information SQLgt; setautotracetraceonly; SQLgt; select * fromtest; Execution Plan -----

-- Obtain the SQL Execution Plan and statistical information, without displaying the Query Information SQLgt; set autotrace traceonly; SQLgt; select * from test; Execution Plan -----

Homepage → Database Technology

Background:

Read News

View SQL Execution plans and statistics in Oracle

[Date:] Source: Linux community Author: AnyJack [Font:]

-- Obtain the SQL Execution Plan and statistics without displaying Query Information
SQL> set autotrace traceonly;
SQL> select * from test;
Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
--------------------------------------------------------------------------
| 0 | select statement | 2 | 130 | 3 (0) | 00:00:01 |
| 1 | table access full | TEST | 2 | 130 | 3 (0) | 00:00:01 |
--------------------------------------------------------------------------
Note
-----
-Dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive cballs
0 db block gets
8 consistent gets
6 physical reads
0 redo size
523 bytes sent via SQL * Net to client
416 bytes encoded ed via SQL * Net from client
2 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
========================================================== ======================================
-- Only display statistics
SQL> set autot traceonly stat;
SQL> select * from test;
Statistics
----------------------------------------------------------
0 recursive cballs
0 db block gets
8 consistent gets
0 physical reads
0 redo size
523 bytes sent via SQL * Net to client
416 bytes encoded ed via SQL * Net from client
2 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
========================================================== ======================================
-- Display only the execution plan
SQL> set autot traceonly exp;
SQL> select * from test;
Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
--------------------------------------------------------------------------
| 0 | select statement | 2 | 130 | 3 (0) | 00:00:01 |
| 1 | table access full | TEST | 2 | 130 | 3 (0) | 00:00:01 |
--------------------------------------------------------------------------
Note
-----
-Dynamic sampling used for this statement
========================================================== ======================================
-- Enable SQL tracking to display query results, execution plans, and statistics
SQL> set autot on;
SQL> select * from test;
T_ID T_NAME
Bytes -------------------------------------------------------------------------------------
1 t1
2 t2
Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
--------------------------------------------------------------------------
| 0 | select statement | 2 | 130 | 3 (0) | 00:00:01 |
| 1 | table access full | TEST | 2 | 130 | 3 (0) | 00:00:01 |
--------------------------------------------------------------------------
Note
-----
-Dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive cballs
0 db block gets
8 consistent gets
0 physical reads
0 redo size
523 bytes sent via SQL * Net to client
416 bytes encoded ed via SQL * Net from client
2 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
========================================================== ======================================
-- Disable SQL tracing and only display query results
SQL> set autot off;
SQL> select * from test;
T_ID T_NAME
-------------------------------------------------------
1 t1
2 t2

0

Use SAS macro to implement the decode function in Oracle

Oracle RMAN loss archive log: ORA-19625: error identifying file

Oracle tutorial

Image Information

View All comments in this article (0)

Comments

Latest Information

Popular this week