Mega Code Archive

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 RNTSOFT 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>