Mega Code Archive

Autotrace a query with group clause

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> insert into my_all_objects 2 ( owner, object_name, object_type, object_id ) 3 values 4 ( 'New Owner', 'New Name', 'New Type', 1111111 ); 1 row created. SQL> SQL> SQL> set autotrace traceonly SQL> select owner, count(*) 2 from my_all_objects 3 where owner = 'New Owner' 4 group by owner; Execution Plan ---------------------------------------------------------- Plan hash value: 3282931909 --------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 5 | 85 | 131 (2)| 00:00:02 | | 1 | SORT GROUP BY NOSORT| | 5 | 85 | 131 (2)| 00:00:02 | |* 2 | TABLE ACCESS FULL | MY_ALL_OBJECTS | 5 | 85 | 131 (2)| 00:00:02 | --------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("OWNER"='New Owner') Note ----- - dynamic sampling used for this statement Statistics ---------------------------------------------------------- 50 recursive calls 0 db block gets 548 consistent gets 466 physical reads 0 redo size 476 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) 1 rows processed SQL> set autotrace off SQL> SQL> drop table my_all_objects; Table dropped. SQL> SQL>