查看执行计划、统计信息、执行时间并且返回sql结果集:
SQL> set autotrace on;
SQL> set timing on;
SQL> select count(*) from t;
COUNT(*)
----------
50295
已用时间: 00: 00: 00.01
执行计划
----------------------------------------------------------
Plan hash value: 2966233522
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 159 (2)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T | 50295 | 159 (2)| 00:00:02 |
-------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
509 bytes sent via SQL*Net to client
211 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
查看执行计划、统计信息、执行时间不返回sql结果集:
SQL> set timing on;
SQL> set autotrace traceonly;
SQL> select * from t2;
已选择402344行。
已用时间: 00: 00: 20.66
执行计划
----------------------------------------------------------
Plan hash value: 1513984157
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 402K| 33M| 1240 (3)| 00:00:15 |
| 1 | TABLE ACCESS FULL| T2 | 402K| 33M| 1240 (3)| 00:00:15 |
--------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
911 bytes sent via SQL*Net to client
190 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
402344 rows processed
只看执行计划、执行时间不返回sql结果集:
SQL> set timing on;
SQL> set autotrace traceonly explain;
SQL> select * from t;
已用时间: 00: 00: 00.01
执行计划
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50295 | 4273K| 161 (3)| 00:00:02 |
| 1 | TABLE ACCESS FULL| T | 50295 | 4273K| 161 (3)| 00:00:02 |
--------------------------------------------------------------------------
只看统计信息、执行时间不返回sql结果集:
SQL> set timing on;
SQL> set autotrace traceonly statistics;
SQL> select * from t;
已选择50295行。
已用时间: 00: 00: 02.59
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
911 bytes sent via SQL*Net to client
189 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
50295 rows processed
查看帮助信息:
SQL> set autotrace
用法: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
如何开看统计信息:
SQL> show parameter db_block_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_size integer 8192
SQL> set linesize 200
SQL> alter system flush shared_pool; --清空shared_pool
系统已更改。
SQL> alter system flush buffer_cache; --清空buffer_cache
系统已更改。
SQL> set timing on;
SQL> set autotrace traceonly;
SQL> select * from t2 order by object_name;
已选择402344行。
已用时间: 00: 00: 23.79 --执行了23.79秒
执行计划
----------------------------------------------------------
Plan hash value: 2552596561
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 402K| 33M| | 9361 (2)| 00:01:53 |
| 1 | SORT ORDER BY | | 402K| 33M| 92M| 9361 (2)| 00:01:53 |
| 2 | TABLE ACCESS FULL| T2 | 402K| 33M| | 1240 (3)| 00:00:15 |
-----------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
525 recursive calls
30 db block gets
5606 consistent gets
11129 physical reads
0 redo size
13462598 bytes sent via SQL*Net to client
295442 bytes received via SQL*Net from client
26824 SQL*Net roundtrips to/from client
4 sorts (memory)
1 sorts (disk)
402344 rows processed
执行了23.79秒。
消耗的内存:5606*8192/1024/1024=43.7M
I/O消耗:11129*8192/1024/1024=86.9M
简而言之:
select * from ...........where ....... consistent gets
update * from ..........where........ db block gets