Oracle PL/SQL Tutorial/SQL PLUS Session Environment/autotrace — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
|
(нет различий)
| |
Версия 13:45, 26 мая 2010
Содержание
- 1 AUTOTRACE exists (subquery)
- 2 Autotrace explain a select statement
- 3 AUTOTRACE table joining
- 4 AUTOTRACE table joining and aggregate function
- 5 autotrace traceonly statistics
- 6 Introduction to SQL Tuning - Workshop
- 7 set arraysize 5
- 8 set autotrace traceonly explain, and condition
- 9 set autotrace traceonly explain for bitmap index
- 10 set autotrace traceonly statistics
- 11 set autotrace traceonly statistics for "select * from tableName"
- 12 set autotrace traceonly statistics to trace a function
AUTOTRACE exists (subquery)
SQL>CREATE TABLE project (
2 pro_id NUMBER(4),
3 pro_name VARCHAR2(40),
4 budget NUMBER(9,2),
5 CONSTRAINT project_pk PRIMARY KEY (pro_id)
6 );
Table created.
SQL>
SQL>
SQL>INSERT INTO project(pro_id, pro_name, budget)VALUES (1001, "A",12345);
1 row created.
SQL>INSERT INTO project(pro_id, pro_name, budget)VALUES (1002, "ERP",23456);
1 row created.
SQL>INSERT INTO project(pro_id, pro_name, budget)VALUES (1003, "SQL",34567);
1 row created.
SQL>INSERT INTO project(pro_id, pro_name, budget)VALUES (1004, "CRM",45678);
1 row created.
SQL>INSERT INTO project(pro_id, pro_name, budget)VALUES (1005, "VPN",56789);
1 row created.
SQL>
SQL>
SQL>CREATE TABLE server_usage (
2 pro_id NUMBER(4),
3 emp_id NUMBER,
4 time_log_date DATE,
5 hours_logged NUMBER(8,2),
6 dollars_charged NUMBER(8,2),
7 CONSTRAINT server_usage_pk PRIMARY KEY (pro_id, emp_id, time_log_date)
8 );
Table created.
SQL>INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)
2 VALUES (1001,101,to_date("4-Apr-2004","dd-mon-yyyy"),1123,222);
1 row created.
SQL>INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)
2 VALUES (1002,102,to_date("4-Apr-2005","dd-mon-yyyy"),1124,223);
1 row created.
SQL>INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)
2 VALUES (1003,103,to_date("4-Apr-2006","dd-mon-yyyy"),1125,224);
1 row created.
SQL>INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)
2 VALUES (1004,104,to_date("4-Apr-2007","dd-mon-yyyy"),1126,225);
1 row created.
SQL>INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)
2 VALUES (1005,105,to_date("4-Apr-2008","dd-mon-yyyy"),1127,226);
1 row created.
SQL>INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)
2 VALUES (1001,106,to_date("4-Apr-2009","dd-mon-yyyy"),1128,227);
1 row created.
SQL>INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)
2 VALUES (1002,107,to_date("4-Apr-2010","dd-mon-yyyy"),1129,228);
1 row created.
SQL>
SQL>CREATE TABLE emp (
2 emp_id NUMBER,
3 ename VARCHAR2(40),
4 hire_date DATE DEFAULT sysdate,
5 end_date DATE,
6 rate NUMBER(5,2),
7 CONSTRAINT emp_pk PRIMARY KEY (emp_id)
8 );
Table created.
SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (101, "Mary", to_date("15-Nov-1961","dd-mon-yyyy"),null,169);
1 row created.
SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (102, "Tom", to_date("16-Sep-1964","dd-mon-yyyy"),to_date("5-May-2004","dd-mon-yyyy"),135);
1 row created.
SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (104, "Peter", to_date("29-Dec-1987","dd-mon-yyyy"),to_date("1-Apr-2004","dd-mon-yyyy"),99);
1 row created.
SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (105, "Mike", to_date("15-Jun-2004","dd-mon-yyyy"),null,121);
1 row created.
SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (107, "Less", to_date("2-Jan-2004","dd-mon-yyyy"),null,45);
1 row created.
SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (108, "Park", to_date("1-Mar-1994","dd-mon-yyyy"),to_date("15-Nov-2004","dd-mon-yyyy"),220);
1 row created.
SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (110, "Ink", to_date("4-Apr-2004","dd-mon-yyyy"),to_date("30-Sep-2004","dd-mon-yyyy"),84);
1 row created.
SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (111, "Tike", to_date("23-Aug-1976","dd-mon-yyyy"),null,100);
1 row created.
SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (112, "Inn", to_date("15-Nov-1961","dd-mon-yyyy"),to_date("4-Apr-2004","dd-mon-yyyy"),70);
1 row created.
SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (113, "Kate", to_date("3-Mar-2004","dd-mon-yyyy"),to_date("31-Oct-2004","dd-mon-yyyy"),300);
1 row created.
SQL>
SQL>
SQL>SET ECHO ON
SQL>SET AUTOTRACE ON
SQL>
SQL>SELECT emp_id, ename
2 FROM emp
3 WHERE EXISTS (SELECT *
4 FROM server_usage
5 WHERE server_usage.pro_id = 1001
6 AND server_usage.emp_id = emp.emp_id);
User System Privileges Page 1
---------- --------------------
########## Mary
1 row selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2816981487
----------------------------------------------
| Id | Operation | Name |
----------------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | FILTER | |
| 2 | TABLE ACCESS FULL| EMP |
|* 3 | INDEX RANGE SCAN | SERVER_USAGE_PK |
----------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( EXISTS (SELECT 0 FROM "SERVER_USAGE" "SERVER_USAGE"
WHERE "SERVER_USAGE"."EMP_ID"=:B1 AND "SERVER_USAGE"."PRO_ID"=1
001))
3 - access("SERVER_USAGE"."PRO_ID"=1001 AND
"SERVER_USAGE"."EMP_ID"=:B1)
Note
-----
- rule based optimizer used (consider using cbo)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
14 consistent gets
0 physical reads
0 redo size
470 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>
SQL>set autotrace off
SQL>
SQL>drop table emp;
Table dropped.
SQL>drop table project;
Table dropped.
SQL>drop table server_usage;
Table dropped.
Autotrace explain a select statement
SQL>
SQL>
SQL> create table employees(
2 empno NUMBER(4)
3 , ename VARCHAR2(8)
4 , init VARCHAR2(5)
5 , job VARCHAR2(8)
6 , mgr NUMBER(4)
7 , bdate DATE
8 , msal NUMBER(6,2)
9 , comm NUMBER(6,2)
10 , deptno NUMBER(2) ) ;
Table created.
SQL>
SQL>
SQL> insert into employees values(1,"Jason", "N", "TRAINER", 2, date "1965-12-18", 800 , NULL, 10);
1 row created.
SQL> insert into employees values(2,"Jerry", "J", "SALESREP",3, date "1966-11-19", 1600, 300, 10);
1 row created.
SQL> insert into employees values(3,"Jord", "T" , "SALESREP",4, date "1967-10-21", 1700, 500, 20);
1 row created.
SQL> insert into employees values(4,"Mary", "J", "MANAGER", 5, date "1968-09-22", 1800, NULL, 20);
1 row created.
SQL> insert into employees values(5,"Joe", "P", "SALESREP",6, date "1969-08-23", 1900, 1400, 30);
1 row created.
SQL> insert into employees values(6,"Black", "R", "MANAGER", 7, date "1970-07-24", 2000, NULL, 30);
1 row created.
SQL> insert into employees values(7,"Red", "A", "MANAGER", 8, date "1971-06-25", 2100, NULL, 40);
1 row created.
SQL> insert into employees values(8,"White", "S", "TRAINER", 9, date "1972-05-26", 2200, NULL, 40);
1 row created.
SQL> insert into employees values(9,"Yellow", "C", "DIRECTOR",10, date "1973-04-27", 2300, NULL, 20);
1 row created.
SQL> insert into employees values(10,"Pink", "J", "SALESREP",null,date "1974-03-28", 2400, 0, 30);
1 row created.
SQL>
SQL>
SQL> set autotrace on explain
SQL>
SQL> select ename from employees where empno < 7;
ENAME
--------
Jason
Jerry
Jord
Mary
Joe
Black
6 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1445457117
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 114 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMPLOYEES | 6 | 114 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("EMPNO"<7)
Note
-----
- dynamic sampling used for this statement
SQL>
SQL> set autotrace off
SQL>
SQL> drop table employees;
Table dropped.
SQL>
AUTOTRACE table joining
SQL>
SQL>CREATE TABLE project (
2 pro_id NUMBER(4),
3 pro_name VARCHAR2(40),
4 budget NUMBER(9,2),
5 CONSTRAINT project_pk PRIMARY KEY (pro_id)
6 );
Table created.
SQL>
SQL>
SQL>INSERT INTO project(pro_id, pro_name, budget)VALUES (1001, "A",12345);
1 row created.
SQL>INSERT INTO project(pro_id, pro_name, budget)VALUES (1002, "ERP",23456);
1 row created.
SQL>INSERT INTO project(pro_id, pro_name, budget)VALUES (1003, "SQL",34567);
1 row created.
SQL>INSERT INTO project(pro_id, pro_name, budget)VALUES (1004, "CRM",45678);
1 row created.
SQL>INSERT INTO project(pro_id, pro_name, budget)VALUES (1005, "VPN",56789);
1 row created.
SQL>
SQL>
SQL>CREATE TABLE server_usage (
2 pro_id NUMBER(4),
3 emp_id NUMBER,
4 time_log_date DATE,
5 hours_logged NUMBER(8,2),
6 dollars_charged NUMBER(8,2),
7 CONSTRAINT server_usage_pk PRIMARY KEY (pro_id, emp_id, time_log_date)
8 );
Table created.
SQL>INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)
2 VALUES (1001,101,to_date("4-Apr-2004","dd-mon-yyyy"),1123,222);
1 row created.
SQL>INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)
2 VALUES (1002,102,to_date("4-Apr-2005","dd-mon-yyyy"),1124,223);
1 row created.
SQL>INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)
2 VALUES (1003,103,to_date("4-Apr-2006","dd-mon-yyyy"),1125,224);
1 row created.
SQL>INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)
2 VALUES (1004,104,to_date("4-Apr-2007","dd-mon-yyyy"),1126,225);
1 row created.
SQL>INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)
2 VALUES (1005,105,to_date("4-Apr-2008","dd-mon-yyyy"),1127,226);
1 row created.
SQL>INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)
2 VALUES (1001,106,to_date("4-Apr-2009","dd-mon-yyyy"),1128,227);
1 row created.
SQL>INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)
2 VALUES (1002,107,to_date("4-Apr-2010","dd-mon-yyyy"),1129,228);
1 row created.
SQL>
SQL>CREATE TABLE emp (
2 emp_id NUMBER,
3 ename VARCHAR2(40),
4 hire_date DATE DEFAULT sysdate,
5 end_date DATE,
6 rate NUMBER(5,2),
7 CONSTRAINT emp_pk PRIMARY KEY (emp_id)
8 );
Table created.
SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (101, "Mary", to_date("15-Nov-1961","dd-mon-yyyy"),null,169);
1 row created.
SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (102, "Tom", to_date("16-Sep-1964","dd-mon-yyyy"),to_date("5-May-2004","dd-mon-yyyy"),135);
1 row created.
SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (104, "Peter", to_date("29-Dec-1987","dd-mon-yyyy"),to_date("1-Apr-2004","dd-mon-yyyy"),99);
1 row created.
SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (105, "Mike", to_date("15-Jun-2004","dd-mon-yyyy"),null,121);
1 row created.
SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (107, "Less", to_date("2-Jan-2004","dd-mon-yyyy"),null,45);
1 row created.
SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (108, "Park", to_date("1-Mar-1994","dd-mon-yyyy"),to_date("15-Nov-2004","dd-mon-yyyy"),220);
1 row created.
SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (110, "Ink", to_date("4-Apr-2004","dd-mon-yyyy"),to_date("30-Sep-2004","dd-mon-yyyy"),84);
1 row created.
SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (111, "Tike", to_date("23-Aug-1976","dd-mon-yyyy"),null,100);
1 row created.
SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (112, "Inn", to_date("15-Nov-1961","dd-mon-yyyy"),to_date("4-Apr-2004","dd-mon-yyyy"),70);
1 row created.
SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (113, "Kate", to_date("3-Mar-2004","dd-mon-yyyy"),to_date("31-Oct-2004","dd-mon-yyyy"),300);
1 row created.
SQL>
SQL>
SQL>SET ECHO ON
SQL>SET AUTOTRACE TRACEONLY EXPLAIN
SQL>
SQL>SELECT ename, SUM(hours_logged)
2 FROM emp, server_usage
3 WHERE emp.emp_id = server_usage.emp_id
4 GROUP BY ename;
Execution Plan
----------------------------------------------------------
Plan hash value: 2561811960
------------------------------------------------------
| Id | Operation | Name |
------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT GROUP BY | |
| 2 | NESTED LOOPS | |
| 3 | TABLE ACCESS FULL | SERVER_USAGE |
| 4 | TABLE ACCESS BY INDEX ROWID| EMP |
|* 5 | INDEX UNIQUE SCAN | EMP_PK |
------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("EMP"."EMP_ID"="SERVER_USAGE"."EMP_ID")
Note
-----
- rule based optimizer used (consider using cbo)
SQL>
SQL>
SQL>set autotrace off
SQL>
SQL>drop table emp;
Table dropped.
SQL>drop table project;
Table dropped.
SQL>drop table server_usage;
Table dropped.
AUTOTRACE table joining and aggregate function
SQL>CREATE TABLE project (
2 pro_id NUMBER(4),
3 pro_name VARCHAR2(40),
4 budget NUMBER(9,2),
5 CONSTRAINT project_pk PRIMARY KEY (pro_id)
6 );
Table created.
SQL>
SQL>
SQL>INSERT INTO project(pro_id, pro_name, budget)VALUES (1001, "A",12345);
1 row created.
SQL>INSERT INTO project(pro_id, pro_name, budget)VALUES (1002, "ERP",23456);
1 row created.
SQL>INSERT INTO project(pro_id, pro_name, budget)VALUES (1003, "SQL",34567);
1 row created.
SQL>INSERT INTO project(pro_id, pro_name, budget)VALUES (1004, "CRM",45678);
1 row created.
SQL>INSERT INTO project(pro_id, pro_name, budget)VALUES (1005, "VPN",56789);
1 row created.
SQL>
SQL>
SQL>CREATE TABLE server_usage (
2 pro_id NUMBER(4),
3 emp_id NUMBER,
4 time_log_date DATE,
5 hours_logged NUMBER(8,2),
6 dollars_charged NUMBER(8,2),
7 CONSTRAINT server_usage_pk PRIMARY KEY (pro_id, emp_id, time_log_date)
8 );
Table created.
SQL>INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)
2 VALUES (1001,101,to_date("4-Apr-2004","dd-mon-yyyy"),1123,222);
1 row created.
SQL>INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)
2 VALUES (1002,102,to_date("4-Apr-2005","dd-mon-yyyy"),1124,223);
1 row created.
SQL>INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)
2 VALUES (1003,103,to_date("4-Apr-2006","dd-mon-yyyy"),1125,224);
1 row created.
SQL>INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)
2 VALUES (1004,104,to_date("4-Apr-2007","dd-mon-yyyy"),1126,225);
1 row created.
SQL>INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)
2 VALUES (1005,105,to_date("4-Apr-2008","dd-mon-yyyy"),1127,226);
1 row created.
SQL>INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)
2 VALUES (1001,106,to_date("4-Apr-2009","dd-mon-yyyy"),1128,227);
1 row created.
SQL>INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)
2 VALUES (1002,107,to_date("4-Apr-2010","dd-mon-yyyy"),1129,228);
1 row created.
SQL>
SQL>CREATE TABLE emp (
2 emp_id NUMBER,
3 ename VARCHAR2(40),
4 hire_date DATE DEFAULT sysdate,
5 end_date DATE,
6 rate NUMBER(5,2),
7 CONSTRAINT emp_pk PRIMARY KEY (emp_id)
8 );
Table created.
SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (101, "Mary", to_date("15-Nov-1961","dd-mon-yyyy"),null,169);
1 row created.
SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (102, "Tom", to_date("16-Sep-1964","dd-mon-yyyy"),to_date("5-May-2004","dd-mon-yyyy"),135);
1 row created.
SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (104, "Peter", to_date("29-Dec-1987","dd-mon-yyyy"),to_date("1-Apr-2004","dd-mon-yyyy"),99);
1 row created.
SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (105, "Mike", to_date("15-Jun-2004","dd-mon-yyyy"),null,121);
1 row created.
SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (107, "Less", to_date("2-Jan-2004","dd-mon-yyyy"),null,45);
1 row created.
SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (108, "Park", to_date("1-Mar-1994","dd-mon-yyyy"),to_date("15-Nov-2004","dd-mon-yyyy"),220);
1 row created.
SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (110, "Ink", to_date("4-Apr-2004","dd-mon-yyyy"),to_date("30-Sep-2004","dd-mon-yyyy"),84);
1 row created.
SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (111, "Tike", to_date("23-Aug-1976","dd-mon-yyyy"),null,100);
1 row created.
SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (112, "Inn", to_date("15-Nov-1961","dd-mon-yyyy"),to_date("4-Apr-2004","dd-mon-yyyy"),70);
1 row created.
SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (113, "Kate", to_date("3-Mar-2004","dd-mon-yyyy"),to_date("31-Oct-2004","dd-mon-yyyy"),300);
1 row created.
SQL>
SQL>
SQL>SET ECHO ON
SQL>SET AUTOTRACE ON EXPLAIN
SQL>
SQL>SELECT ename, SUM(hours_logged)
2 FROM emp, server_usage
3 WHERE emp.emp_id = server_usage.emp_id
4 GROUP BY ename;
User System Privileges Page 1
SUM(HOURS_LOGGED)
-------------------- -----------------
Less 1129
Mary 1123
Mike 1127
Peter 1126
Tom 1124
5 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2561811960
------------------------------------------------------
| Id | Operation | Name |
------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT GROUP BY | |
| 2 | NESTED LOOPS | |
| 3 | TABLE ACCESS FULL | SERVER_USAGE |
| 4 | TABLE ACCESS BY INDEX ROWID| EMP |
|* 5 | INDEX UNIQUE SCAN | EMP_PK |
------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("EMP"."EMP_ID"="SERVER_USAGE"."EMP_ID")
Note
-----
- rule based optimizer used (consider using cbo)
SQL>
SQL>set autotrace off
SQL>
SQL>drop table emp;
Table dropped.
SQL>drop table project;
Table dropped.
SQL>drop table server_usage;
Table dropped.
autotrace traceonly statistics
SQL> create table employees(
2 empno NUMBER(4)
3 , ename VARCHAR2(8)
4 , init VARCHAR2(5)
5 , job VARCHAR2(8)
6 , mgr NUMBER(4)
7 , bdate DATE
8 , msal NUMBER(6,2)
9 , comm NUMBER(6,2)
10 , deptno NUMBER(2) ) ;
Table created.
SQL>
SQL>
SQL> insert into employees values(1,"Jason", "N", "TRAINER", 2, date "1965-12-18", 800 , NULL, 10);
1 row created.
SQL> insert into employees values(2,"Jerry", "J", "SALESREP",3, date "1966-11-19", 1600, 300, 10);
1 row created.
SQL> insert into employees values(3,"Jord", "T" , "SALESREP",4, date "1967-10-21", 1700, 500, 20);
1 row created.
SQL> insert into employees values(4,"Mary", "J", "MANAGER", 5, date "1968-09-22", 1800, NULL, 20);
1 row created.
SQL> insert into employees values(5,"Joe", "P", "SALESREP",6, date "1969-08-23", 1900, 1400, 30);
1 row created.
SQL> insert into employees values(6,"Black", "R", "MANAGER", 7, date "1970-07-24", 2000, NULL, 30);
1 row created.
SQL> insert into employees values(7,"Red", "A", "MANAGER", 8, date "1971-06-25", 2100, NULL, 40);
1 row created.
SQL> insert into employees values(8,"White", "S", "TRAINER", 9, date "1972-05-26", 2200, NULL, 40);
1 row created.
SQL> insert into employees values(9,"Yellow", "C", "DIRECTOR",10, date "1973-04-27", 2300, NULL, 20);
1 row created.
SQL> insert into employees values(10,"Pink", "J", "SALESREP",null,date "1974-03-28", 2400, 0, 30);
1 row created.
SQL>
SQL>
SQL> set autotrace traceonly statistics
SQL>
SQL> select * from employees;
10 rows selected.
Statistics
----------------------------------------------------------
188 recursive calls
0 db block gets
26 consistent gets
0 physical reads
0 redo size
1266 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)
10 rows processed
SQL>
SQL> set autotrace off
SQL>
SQL> drop table employees;
Table dropped.
SQL>
Introduction to SQL Tuning - Workshop
SQL>
SQL> create table employee
2 (
3 empl_no integer primary key
4 ,lastname varchar2(20) not null
5 ,firstname varchar2(15) not null
6 ,midinit varchar2(1)
7 ,street varchar2(30)
8 ,city varchar2(20)
9 ,state varchar2(2)
10 ,zip varchar2(5)
11 ,zip_4 varchar2(4)
12 ,area_code varchar2(3)
13 ,phone varchar2(8)
14 ,company_name varchar2(50));
Table created.
SQL>
SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(1,"Jones","Joe","J","10 Ave","New York","NY","11111","1111","111", "111-1111","A Company");
1 row created.
SQL>
SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(2,"Smith","Sue","J","20 Ave","New York","NY","22222","2222","222", "222-111","B Company");
1 row created.
SQL>
SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(3,"Anderson","Peggy","J","500 St","New York","NY","33333","3333","333", "333-3333","C Company");
1 row created.
SQL>
SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(4,"Andy","Jill", null,"930 St","New York","NY","44444","4444","212", "634-7733","D Company");
1 row created.
SQL>
SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(5,"OK","Carl","L","19 Drive","New York","NY","55555","3234","212", "243-4243","E Company");
1 row created.
SQL>
SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(6,"Peter","Jee","Q","38 Ave","New York","NY","66666","4598","212", "454-5443","F Inc");
1 row created.
SQL>
SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(7,"Baker","Paul","V","738 St.","Queens","NY","77777","3842","718", "664-4333","G Inc");
1 row created.
SQL>
SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(8,"Young","Steve","J","388 Ave","New York","NY","88888","3468","212", "456-4566","H Associates Inc");
1 row created.
SQL>
SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(9,"Mona","Joe","T","9300 Ave","Kansas City","MO","99999","3658","415", "456-4563","J Inc");
1 row created.
SQL>
SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(10,"Hackett","Karen","S","Kings Rd. Apt 833","Bellmore","NY","61202","3898","516", "767-5677","AA Inc");
1 row created.
SQL>
SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(11,"Bob","Jack","S","12 Giant Rd.","Newark","NJ","27377","3298","908", "123-7367","Z Associates");
1 row created.
SQL>
SQL>
SQL>
SQL>
SQL> -- 2.
SQL> desc plan_table
Name Null? Type
----------------------------------------- -------- ----------------------------
STATEMENT_ID VARCHAR2(30)
PLAN_ID NUMBER
TIMESTAMP DATE
REMARKS VARCHAR2(4000)
OPERATION VARCHAR2(30)
OPTIONS VARCHAR2(255)
OBJECT_NODE VARCHAR2(128)
OBJECT_OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(30)
OBJECT_ALIAS VARCHAR2(65)
OBJECT_INSTANCE NUMBER(38)
OBJECT_TYPE VARCHAR2(30)
OPTIMIZER VARCHAR2(255)
SEARCH_COLUMNS NUMBER
ID NUMBER(38)
PARENT_ID NUMBER(38)
DEPTH NUMBER(38)
POSITION NUMBER(38)
COST NUMBER(38)
CARDINALITY NUMBER(38)
BYTES NUMBER(38)
OTHER_TAG VARCHAR2(255)
PARTITION_START VARCHAR2(255)
PARTITION_STOP VARCHAR2(255)
PARTITION_ID NUMBER(38)
OTHER LONG
OTHER_XML CLOB
DISTRIBUTION VARCHAR2(30)
CPU_COST NUMBER(38)
IO_COST NUMBER(38)
TEMP_SPACE NUMBER(38)
ACCESS_PREDICATES VARCHAR2(4000)
FILTER_PREDICATES VARCHAR2(4000)
PROJECTION VARCHAR2(4000)
TIME NUMBER(38)
QBLOCK_NAME VARCHAR2(30)
SQL>
SQL> -- 3.
SQL> set autotrace on
SQL> select empl_no, state, phone from employee where state = "NY";
EMPL_NO ST PHONE
---------- -- --------
1 NY 111-1111
2 NY 222-111
3 NY 333-3333
4 NY 634-7733
5 NY 243-4243
6 NY 454-5443
7 NY 664-4333
8 NY 456-4566
10 NY 767-5677
9 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2119105728
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 198 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMPLOYEE | 9 | 198 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("STATE"="NY")
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
48 recursive calls
0 db block gets
12 consistent gets
0 physical reads
0 redo size
698 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)
9 rows processed
SQL>
SQL> -- 4.
SQL> set autotrace off
SQL> explain plan
2 set statement_id = "test"
3 for
4 select empl_no, state, phone
5 from employee
6 where state = "NY";
Explained.
SQL>
SQL> drop table employee;
Table dropped.
SQL>
set arraysize 5
SQL>
SQL> create table t as select * from all_objects;
Table created.
SQL>
SQL> set autotrace traceonly statistics;
SQL>
SQL> set arraysize 2
SQL> select * from t;
12652 rows selected.
Statistics
----------------------------------------------------------
288 recursive calls
0 db block gets
6498 consistent gets
160 physical reads
0 redo size
1312833 bytes sent via SQL*Net to client
69955 bytes received via SQL*Net from client
6327 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
12652 rows processed
SQL>
SQL> set arraysize 5
SQL> select * from t;
12652 rows selected.
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2672 consistent gets
0 physical reads
0 redo size
830868 bytes sent via SQL*Net to client
28210 bytes received via SQL*Net from client
2532 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
12652 rows processed
SQL> set arraysize 10
SQL> select * from t;
12652 rows selected.
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1416 consistent gets
0 physical reads
0 redo size
670213 bytes sent via SQL*Net to client
14295 bytes received via SQL*Net from client
1267 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
12652 rows processed
SQL> set arraysize 15
SQL> select * from t;
12652 rows selected.
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1004 consistent gets
0 physical reads
0 redo size
616619 bytes sent via SQL*Net to client
9653 bytes received via SQL*Net from client
845 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
12652 rows processed
SQL> set arraysize 100
SQL> select * from t;
12652 rows selected.
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
289 consistent gets
0 physical reads
0 redo size
525560 bytes sent via SQL*Net to client
1766 bytes received via SQL*Net from client
128 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
12652 rows processed
SQL> set arraysize 5000
SQL> select * from t;
12652 rows selected.
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
167 consistent gets
0 physical reads
0 redo size
509812 bytes sent via SQL*Net to client
402 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
12652 rows processed
SQL> set autotrace off
SQL>
SQL> drop table t;
Table dropped.
SQL>
SQL>
set autotrace traceonly explain, and condition
SQL>
SQL>
SQL> create table t
2 as
3 select mod(ROWNUM,3) a, ROWNUM b, ROWNUM c
4 from all_tables;
Table created.
SQL>
SQL> set autotrace traceonly explain
SQL> select * from t where b = 1 and c = 1;
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
----------------------------------
| Id | Operation | Name |
----------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | TABLE ACCESS FULL| T |
----------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("C"=1 AND "B"=1)
Note
-----
- rule based optimizer used (consider using cbo)
SQL>
SQL>
SQL> set autotrace off
SQL>
SQL> drop table t;
Table dropped.
set autotrace traceonly explain for bitmap index
SQL>
SQL> CREATE TABLE EMP(
2 EMPNO NUMBER(4) NOT NULL,
3 ENAME VARCHAR2(10),
4 JOB VARCHAR2(9),
5 MGR NUMBER(4),
6 HIREDATE DATE,
7 SAL NUMBER(7, 2),
8 COMM NUMBER(7, 2),
9 DEPTNO NUMBER(2)
10 );
Table created.
SQL> INSERT INTO EMP VALUES(2, "Jack", "Tester", 6,TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(3, "Wil", "Tester", 6,TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(4, "Jane", "Designer", 9,TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES(5, "Mary", "Tester", 6,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(7, "Chris", "Designer", 9,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES(8, "Smart", "Helper", 4,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES(9, "Peter", "Manager", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES(10, "Take", "Tester", 6,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(13, "Fake", "Helper", 4,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL>
SQL> CREATE TABLE DEPT(
2 DEPTNO NUMBER(2),
3 DNAME VARCHAR2(14),
4 LOC VARCHAR2(13)
5 );
Table created.
SQL>
SQL> INSERT INTO DEPT VALUES (10, "ACCOUNTING", "NEW YORK");
1 row created.
SQL> INSERT INTO DEPT VALUES (20, "RESEARCH", "DALLAS");
1 row created.
SQL> INSERT INTO DEPT VALUES (30, "SALES", "CHICAGO");
1 row created.
SQL> INSERT INTO DEPT VALUES (40, "OPERATIONS", "BOSTON");
1 row created.
SQL>
SQL>
SQL> alter table dept add constraint dept_pk primary key(deptno);
Table altered.
SQL>
SQL> create bitmap index emp_bm_idx on emp( d.dname)from emp e, dept d where e.deptno = d.deptno
2 /
SQL> begin
2 dbms_stats.set_table_stats( user, "EMP", numrows => 1000000, numblks => 300000 );
3 dbms_stats.set_table_stats( user, "DEPT",numrows => 100000, numblks => 30000 );
4 end;
5 /
PL/SQL procedure successfully completed.
SQL>
SQL> set autotrace traceonly explain
SQL>
SQL> select count(*) from emp, dept where emp.deptno = dept.deptno and dept.dname = "SALES"
2 /
Execution Plan
----------------------------------------------------------
Plan hash value: 1546158010
------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 35 | 50088 |
| 1 | SORT AGGREGATE | | 1 | 35 | |
|* 2 | HASH JOIN | | 10M| 333M| 50088 |
|* 3 | TABLE ACCESS FULL| DEPT | 1000 | 22000 | 4554 |
| 4 | TABLE ACCESS FULL| EMP | 1000K| 12M| 45533 |
------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
3 - filter("DEPT"."DNAME"="SALES")
Note
-----
- cpu costing is off (consider enabling it)
SQL>
SQL> set autotrace off
SQL>
SQL> drop table emp;
Table dropped.
SQL> drop table dept;
Table dropped.
SQL>
SQL>
set autotrace traceonly statistics
SQL>
SQL> create table emp
2 ( empno NUMBER(4) constraint E_PK primary key
3 , ename VARCHAR2(8)
4 , init VARCHAR2(5)
5 , job VARCHAR2(8)
6 , mgr NUMBER(4)
7 , bdate DATE
8 , sal NUMBER(6,2)
9 , comm NUMBER(6,2)
10 , deptno NUMBER(2) default 10
11 ) ;
Table created.
SQL> insert into emp values(1,"Tom","N", "Coder", 13,date "1965-12-17", 800 , NULL, 20);
1 row created.
SQL> insert into emp values(2,"Jack","JAM", "Tester",6,date "1961-02-20", 1600, 300, 30);
1 row created.
SQL> insert into emp values(3,"Wil","TF" , "Tester",6,date "1962-02-22", 1250, 500, 30);
1 row created.
SQL> insert into emp values(4,"Jane","JM", "Designer", 9,date "1967-04-02", 2975, NULL, 20);
1 row created.
SQL> insert into emp values(5,"Mary","P", "Tester",6,date "1956-09-28", 1250, 1400, 30);
1 row created.
SQL> insert into emp values(6,"Black","R", "Designer", 9,date "1963-11-01", 2850, NULL, 30);
1 row created.
SQL> insert into emp values(7,"Chris","AB", "Designer", 9,date "1965-06-09", 2450, NULL, 10);
1 row created.
SQL> insert into emp values(8,"Smart","SCJ", "Coder", 4,date "1959-11-26", 3000, NULL, 20);
1 row created.
SQL> insert into emp values(9,"Peter","CC", "Designer",NULL,date "1952-11-17", 5000, NULL, 10);
1 row created.
SQL> insert into emp values(10,"Take","JJ", "Tester",6,date "1968-09-28", 1500, 0, 30);
1 row created.
SQL> insert into emp values(11,"Ana","AA", "Coder", 8,date "1966-12-30", 1100, NULL, 20);
1 row created.
SQL> insert into emp values(12,"Jane","R", "Manager", 6,date "1969-12-03", 800 , NULL, 30);
1 row created.
SQL> insert into emp values(13,"Fake","MG", "Coder", 4,date "1959-02-13", 3000, NULL, 20);
1 row created.
SQL> insert into emp values(14,"Mike","TJA","Manager", 7,date "1962-01-23", 1300, NULL, 10);
1 row created.
SQL>
SQL>
SQL> set autotrace traceonly statistics
SQL>
SQL> select * from emp;
14 rows selected.
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
1433 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)
14 rows processed
SQL>
SQL> set autotrace off
SQL>
SQL>
SQL> drop table emp;
Table dropped.
set autotrace traceonly statistics for "select * from tableName"
SQL>
SQL> create table t as select * from all_objects where 1=0;
Table created.
SQL>
SQL> select * from t;
no rows selected
SQL>
SQL> set autotrace traceonly statistics
SQL>
SQL> select * from t;
no rows selected
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
995 bytes sent via SQL*Net to client
369 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
SQL>
SQL> set autotrace off
SQL>
SQL> insert into t select * from all_objects;
12599 rows created.
SQL>
SQL> rollback;
Rollback complete.
SQL>
SQL> select * from t;
no rows selected
SQL>
SQL> set autotrace traceonly statistics
SQL>
SQL> select * from t;
no rows selected
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
166 consistent gets
0 physical reads
0 redo size
995 bytes sent via SQL*Net to client
369 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
SQL>
SQL> set autotrace off
SQL>
SQL> drop table t;
Table dropped.
SQL>
SQL>
set autotrace traceonly statistics to trace a function
SQL>
SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
2 ENAME VARCHAR2(10),
3 JOB VARCHAR2(9),
4 MGR NUMBER(4),
5 HIREDATE DATE,
6 SAL NUMBER(7, 2),
7 COMM NUMBER(7, 2),
8 DEPTNO NUMBER(2));
Table created.
SQL>
SQL>
SQL> INSERT INTO EMP VALUES (7369, "SMITH", "CLERK", 7902, TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7499, "ALLEN", "SALESMAN", 7698, TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7521, "WARD", "SALESMAN", 7698, TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7566, "JONES", "MANAGER", 7839, TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10);
1 row created.
SQL>
SQL>
SQL> create or replace function some_function return number
2 as
3 l_user varchar2(30) default user;
4 l_cnt number;
5 begin
6 select count(*) into l_cnt from dual;
7 return l_cnt;
8 end;
9 /
Function created.
SQL> set autotrace traceonly statistics;
SQL> select ename, some_function
2 from emp
3 /
14 rows selected.
Statistics
----------------------------------------------------------
164 recursive calls
0 db block gets
71 consistent gets
0 physical reads
0 redo size
651 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)
14 rows processed
SQL>
SQL> set autotrace off
SQL> select ename, (select count(*) from dual)
2 from emp;
ENAME (SELECTCOUNT(*)FROMDUAL)
---------- ------------------------
SMITH 1
ALLEN 1
WARD 1
JONES 1
MARTIN 1
BLAKE 1
CLARK 1
SCOTT 1
KING 1
TURNER 1
ADAMS 1
JAMES 1
FORD 1
MILLER 1
14 rows selected.
SQL>
SQL>
SQL> drop table emp;
Table dropped.
SQL>
SQL>