SET ECHO OFF
REM ***************************************************************************
REM ******************* Troubleshooting Oracle Performance ********************
REM ************************* http://top.antognini.ch *************************
REM ***************************************************************************
REM
REM File name...: depts_wo_emps.sql
REM Author......: Christian Antognini
REM Date........: August 2008
REM Description.: This script was used to generate the execution plans used as
REM examples in the section "Altering the SQL Statement."
REM Notes.......: -
REM Parameters..: -
REM
REM You can send feedbacks or questions about this script to top@antognini.ch.
REM
REM Changes:
REM DD.MM.YYYY Description
REM ---------------------------------------------------------------------------
REM
REM ***************************************************************************
SET TERMOUT ON
SET FEEDBACK OFF
SET VERIFY OFF
SET SCAN ON
@../connect.sql
SET ECHO ON
REM
REM Setup test environment
REM
DROP TABLE dept;
CREATE TABLE dept
(deptno NUMBER(2),
dname VARCHAR2(14),
loc VARCHAR2(13) );
ALTER TABLE dept ADD CONSTRAINT dept_pk PRIMARY KEY (deptno);
INSERT INTO dept VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO dept VALUES (20, 'RESEARCH', 'DALLAS');
INSERT INTO dept VALUES (30, 'SALES', 'CHICAGO');
INSERT INTO dept VALUES (40, 'OPERATIONS', 'BOSTON');
execute dbms_stats.gather_table_stats(user, 'dept')
DROP TABLE emp;
CREATE TABLE emp
(empno NUMBER(4) NOT NULL,
ename VARCHAR2(10),
job VARCHAR2(9),
mgr NUMBER(4),
hiredate DATE,
sal NUMBER(7, 2),
comm NUMBER(7, 2),
deptno NUMBER(2));
ALTER TABLE emp ADD CONSTRAINT emp_pk PRIMARY KEY (empno);
ALTER TABLE emp ADD CONSTRAINT emp_dept_pk FOREIGN KEY (deptno) REFERENCING DEPT (deptno);
INSERT INTO emp VALUES
(7369, 'SMITH', 'CLERK', 7902,
to_date('17-DEC-1980', 'DD-MON-YYYY'), 800, NULL, 20);
INSERT INTO emp VALUES
(7499, 'ALLEN', 'SALESMAN', 7698,
to_date('20-FEB-1981', 'DD-MON-YYYY'), 1600, 300, 30);
INSERT INTO emp VALUES
(7521, 'WARD', 'SALESMAN', 7698,
to_date('22-FEB-1981', 'DD-MON-YYYY'), 1250, 500, 30);
INSERT INTO emp VALUES
(7566, 'JONES', 'MANAGER', 7839,
to_date('2-APR-1981', 'DD-MON-YYYY'), 2975, NULL, 20);
INSERT INTO emp VALUES
(7654, 'MARTIN', 'SALESMAN', 7698,
to_date('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30);
INSERT INTO emp VALUES
(7698, 'BLAKE', 'MANAGER', 7839,
to_date('1-MAY-1981', 'DD-MON-YYYY'), 2850, NULL, 30);
INSERT INTO emp VALUES
(7782, 'CLARK', 'MANAGER', 7839,
to_date('9-JUN-1981', 'DD-MON-YYYY'), 2450, NULL, 10);
INSERT INTO emp VALUES
(7788, 'SCOTT', 'ANALYST', 7566,
to_date('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO emp VALUES
(7839, 'KING', 'PRESIDENT', NULL,
to_date('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10);
INSERT INTO emp VALUES
(7844, 'TURNER', 'SALESMAN', 7698,
to_date('8-SEP-1981', 'DD-MON-YYYY'), 1500, 0, 30);
INSERT INTO emp VALUES
(7876, 'ADAMS', 'CLERK', 7788,
to_date('12-JAN-1983', 'DD-MON-YYYY'), 1100, NULL, 20);
INSERT INTO emp VALUES
(7900, 'JAMES', 'CLERK', 7698,
to_date('3-DEC-1981', 'DD-MON-YYYY'), 950, NULL, 30);
INSERT INTO emp VALUES
(7902, 'FORD', 'ANALYST', 7566,
to_date('3-DEC-1981', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO emp VALUES
(7934, 'MILLER', 'CLERK', 7782,
to_date('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10);
execute dbms_stats.gather_table_stats(user, 'emp')
PAUSE
REM
REM The test queries...
REM
EXPLAIN PLAN FOR
SELECT deptno
FROM dept
WHERE deptno NOT IN (SELECT deptno FROM emp);
SELECT * FROM table(dbms_xplan.display);
PAUSE
为SQL语句创建执行计划EXPLAIN PLAN FOR
EXPLAIN PLAN FOR
SELECT deptno
FROM dept
WHERE NOT EXISTS (SELECT 1 FROM emp WHERE emp.deptno = dept.deptno);
查看最近一次执行的SQL执行计划
SELECT * FROM table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3222363744
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 5 (20)| 00:00:01 |
|* 1 | HASH JOIN ANTI | | 1 | 6 | 5 (20)| 00:00:01 |
| 2 | INDEX FULL SCAN | DEPT_PK | 4 | 12 | 1 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 14 | 42 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")
15 rows selected.
PAUSE
EXPLAIN PLAN FOR
SELECT deptno FROM dept
MINUS
SELECT deptno FROM emp;
SELECT * FROM table(dbms_xplan.display);
PAUSE
EXPLAIN PLAN FOR
SELECT dept.deptno
FROM dept, emp
WHERE dept.deptno = emp.deptno(+) AND emp.deptno IS NULL;
SELECT * FROM table(dbms_xplan.display);
PAUSE
REM
REM Cleanup
REM
DROP TABLE emp;
PURGE TABLE emp;
DROP TABLE dept;
PURGE TABLE dept;
REM ***************************************************************************
REM ******************* Troubleshooting Oracle Performance ********************
REM ************************* http://top.antognini.ch *************************
REM ***************************************************************************
REM
REM File name...: depts_wo_emps.sql
REM Author......: Christian Antognini
REM Date........: August 2008
REM Description.: This script was used to generate the execution plans used as
REM examples in the section "Altering the SQL Statement."
REM Notes.......: -
REM Parameters..: -
REM
REM You can send feedbacks or questions about this script to top@antognini.ch.
REM
REM Changes:
REM DD.MM.YYYY Description
REM ---------------------------------------------------------------------------
REM
REM ***************************************************************************
SET TERMOUT ON
SET FEEDBACK OFF
SET VERIFY OFF
SET SCAN ON
@../connect.sql
SET ECHO ON
REM
REM Setup test environment
REM
DROP TABLE dept;
CREATE TABLE dept
(deptno NUMBER(2),
dname VARCHAR2(14),
loc VARCHAR2(13) );
ALTER TABLE dept ADD CONSTRAINT dept_pk PRIMARY KEY (deptno);
INSERT INTO dept VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO dept VALUES (20, 'RESEARCH', 'DALLAS');
INSERT INTO dept VALUES (30, 'SALES', 'CHICAGO');
INSERT INTO dept VALUES (40, 'OPERATIONS', 'BOSTON');
execute dbms_stats.gather_table_stats(user, 'dept')
DROP TABLE emp;
CREATE TABLE emp
(empno NUMBER(4) NOT NULL,
ename VARCHAR2(10),
job VARCHAR2(9),
mgr NUMBER(4),
hiredate DATE,
sal NUMBER(7, 2),
comm NUMBER(7, 2),
deptno NUMBER(2));
ALTER TABLE emp ADD CONSTRAINT emp_pk PRIMARY KEY (empno);
ALTER TABLE emp ADD CONSTRAINT emp_dept_pk FOREIGN KEY (deptno) REFERENCING DEPT (deptno);
INSERT INTO emp VALUES
(7369, 'SMITH', 'CLERK', 7902,
to_date('17-DEC-1980', 'DD-MON-YYYY'), 800, NULL, 20);
INSERT INTO emp VALUES
(7499, 'ALLEN', 'SALESMAN', 7698,
to_date('20-FEB-1981', 'DD-MON-YYYY'), 1600, 300, 30);
INSERT INTO emp VALUES
(7521, 'WARD', 'SALESMAN', 7698,
to_date('22-FEB-1981', 'DD-MON-YYYY'), 1250, 500, 30);
INSERT INTO emp VALUES
(7566, 'JONES', 'MANAGER', 7839,
to_date('2-APR-1981', 'DD-MON-YYYY'), 2975, NULL, 20);
INSERT INTO emp VALUES
(7654, 'MARTIN', 'SALESMAN', 7698,
to_date('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30);
INSERT INTO emp VALUES
(7698, 'BLAKE', 'MANAGER', 7839,
to_date('1-MAY-1981', 'DD-MON-YYYY'), 2850, NULL, 30);
INSERT INTO emp VALUES
(7782, 'CLARK', 'MANAGER', 7839,
to_date('9-JUN-1981', 'DD-MON-YYYY'), 2450, NULL, 10);
INSERT INTO emp VALUES
(7788, 'SCOTT', 'ANALYST', 7566,
to_date('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO emp VALUES
(7839, 'KING', 'PRESIDENT', NULL,
to_date('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10);
INSERT INTO emp VALUES
(7844, 'TURNER', 'SALESMAN', 7698,
to_date('8-SEP-1981', 'DD-MON-YYYY'), 1500, 0, 30);
INSERT INTO emp VALUES
(7876, 'ADAMS', 'CLERK', 7788,
to_date('12-JAN-1983', 'DD-MON-YYYY'), 1100, NULL, 20);
INSERT INTO emp VALUES
(7900, 'JAMES', 'CLERK', 7698,
to_date('3-DEC-1981', 'DD-MON-YYYY'), 950, NULL, 30);
INSERT INTO emp VALUES
(7902, 'FORD', 'ANALYST', 7566,
to_date('3-DEC-1981', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO emp VALUES
(7934, 'MILLER', 'CLERK', 7782,
to_date('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10);
execute dbms_stats.gather_table_stats(user, 'emp')
PAUSE
REM
REM The test queries...
REM
EXPLAIN PLAN FOR
SELECT deptno
FROM dept
WHERE deptno NOT IN (SELECT deptno FROM emp);
SELECT * FROM table(dbms_xplan.display);
PAUSE
为SQL语句创建执行计划EXPLAIN PLAN FOR
EXPLAIN PLAN FOR
SELECT deptno
FROM dept
WHERE NOT EXISTS (SELECT 1 FROM emp WHERE emp.deptno = dept.deptno);
查看最近一次执行的SQL执行计划
SELECT * FROM table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3222363744
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 5 (20)| 00:00:01 |
|* 1 | HASH JOIN ANTI | | 1 | 6 | 5 (20)| 00:00:01 |
| 2 | INDEX FULL SCAN | DEPT_PK | 4 | 12 | 1 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 14 | 42 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")
15 rows selected.
PAUSE
EXPLAIN PLAN FOR
SELECT deptno FROM dept
MINUS
SELECT deptno FROM emp;
SELECT * FROM table(dbms_xplan.display);
PAUSE
EXPLAIN PLAN FOR
SELECT dept.deptno
FROM dept, emp
WHERE dept.deptno = emp.deptno(+) AND emp.deptno IS NULL;
SELECT * FROM table(dbms_xplan.display);
PAUSE
REM
REM Cleanup
REM
DROP TABLE emp;
PURGE TABLE emp;
DROP TABLE dept;
PURGE TABLE dept;