Time and autotrace a big table

   
SQL>
SQL>
SQL> set echo on
SQL>
SQL> set termout off
SQL>
SQL>
SQL> create table my_all_objects
  2  nologging
  3  as
  4  select * from all_objects
  5  union all
  6  select * from all_objects
  7  union all
  8  select * from all_objects
  9  /

Table created.

SQL>
SQL>
SQL> analyze table my_all_objects compute statistics;

Table analyzed.

SQL>
SQL> set autotrace on
SQL> set timing on
SQL> select owner, count(*) from my_all_objects group by owner;

OWNER                          COUNT(*)
------------------------------ --------
MDSYS                           1374.00
TSMSYS                             6.00
FLOWS_020100                    3255.00
PUBLIC                          8298.00
OUTLN                             21.00
JAVA2S                           603.00
CTXSYS                          1014.00
HR                               102.00
FLOWS_FILES                       33.00
SYSTEM                          1266.00
DBSNMP                           138.00

OWNER                          COUNT(*)
------------------------------ --------
XDB                             1002.00
SYS                            ########

13 rows selected.

Elapsed: 00:00:00.31

Execution Plan
----------------------------------------------------------
Plan hash value: 2509106709

-------------------------------------------------------------------------------------
| Id  | Operation          | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                |    13 |    65 |   136   (6)| 00:00:02 |
|   1 |  HASH GROUP BY     |                |    13 |    65 |   136   (6)| 00:00:02 |
|   2 |   TABLE ACCESS FULL| MY_ALL_OBJECTS | 36945 |   180K|   130   (2)| 00:00:02 |
-------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        470  consistent gets
          0  physical reads
          0  redo size
        694  bytes sent via SQL*Net to client
        380  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         13  rows processed

SQL>
SQL> set timing off
SQL> set autotrace off
SQL> drop table my_all_objects;

Table dropped.

SQL>
SQL>

Related examples in the same category

1.	Time command
2.	Time query for a huge table
3.	time a query with where clause
4.	Time lower text function
5.	Loop timing
6.	timing unconditional loop
7.	Statistics query Timing on index
8.	Performace difference between sql and pl/sql
9.	TIMING table copying
10.	Timing passing table collection parameter to a procedure

Time and autotrace a big table : Timing « SQL Plus « Oracle PL / SQL

Related examples in the same category