timing and auto tracing a select statement with group
SQL> SQL> SQL> CREATE TABLE employees 2 ( employee_id number(10) not null, 3 last_name varchar2(50) not null, 4 email varchar2(30), 5 hire_date date, 6 job_id varchar2(30), 7 department_id number(10), 8 salary number(6), 9 manager_id number(6) 10 ); Table created. SQL> SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary,department_id ,manager_id) 2 values ( 1001, 'Lawson', 'lawson@g.com', '01-JAN-2002','MGR', 30000,1 ,1004); 1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id ,manager_id) 2 values ( 1002, 'Wells', 'wells@g.com', '01-JAN-2002', 'DBA', 20000,2, 1005 ); 1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id ,manager_id) 2 values( 1003, 'Bliss', 'bliss@g.com', '01-JAN-2002', 'PROG', 24000,3 ,1004); 1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id) 2 values( 1004, 'Kyte', 'tkyte@a.com', SYSDATE-3650, 'MGR',25000 ,4, 1005); 1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id) 2 values( 1005, 'Viper', 'sdillon@a .com', SYSDATE, 'PROG', 20000, 1, 1006); 1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id,manager_id) 2 values( 1006, 'Beck', 'clbeck@g.com', SYSDATE, 'PROG', 20000, 2, null); 1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id) 2 values( 1007, 'Java', 'java01@g.com', SYSDATE, 'PROG', 20000, 3, 1006); 1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id) 2 values( 1008, 'Oracle', 'wvelasq@g.com', SYSDATE, 'DBA', 20000, 4, 1006); 1 row created. SQL> SQL> SQL> set autotrace on SQL> set timing on SQL> SQL> select employee_id, 2 count(*) total_orders 3 from employees 4 group by employee_id 5 / EMPLOYEE_ID TOTAL_ORDERS ----------- ------------ 1003 1 1006 1 1001 1 1002 1 1007 1 1004 1 1005 1 1008 1 8 rows selected. Elapsed: 00:00:00.11 Execution Plan ---------------------------------------------------------- Plan hash value: 1192169904 -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 8 | 104 | 3 (34)| 00:00:01 | | 1 | HASH GROUP BY | | 8 | 104 | 3 (34)| 00:00:01 | | 2 | TABLE ACCESS FULL| EMPLOYEES | 8 | 104 | 2 (0)| 00:00:01 | -------------------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement Statistics ---------------------------------------------------------- 28 recursive calls 0 db block gets 9 consistent gets 0 physical reads 0 redo size 574 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) 8 rows processed SQL> set autotrace off SQL> set timing off SQL> SQL> drop table employees; Table dropped. SQL>