源码-PL/SQL从入门到精通-第十八章-PL/SQL性能优化建议-Part 2

--18.1.3 使用DBMS_Trace包
--配置与使用dbms_trace
--使用DBA权限，执行tracetab.sql
conn /as sysdba
@?/rdbms/admin/tracetab.sql

--创建同义词并分配可访问的权限
CREATE OR REPLACE PUBLIC SYNONYM plsql_trace_runs FOR SYS.plsql_trace_runs;
CREATE OR REPLACE PUBLIC SYNONYM plsql_trace_events FOR SYS.plsql_trace_events;
CREATE OR REPLACE PUBLIC SYNONYM plsql_trace_runnumber FOR SYS.plsql_trace_runnumber;
GRANT SELECT,INSERT,UPDATE,DELETE ON plsql_trace_events TO PUBLIC;
GRANT SELECT,INSERT,UPDATE,DELETE ON plsql_trace_runs TO PUBLIC;
GRANT SELECT ON plsql_trace_runnumber TO PUBLIC;

--代码18.5 创建dbms_trace测试程序
CREATE OR REPLACE PROCEDURE do_something (p_times IN NUMBER)
AS
   l_dummy   NUMBER;          --定义一个用来累加的局部变量
BEGIN
   FOR i IN 1 .. p_times      --循环p_times执行累加
   LOOP
      SELECT l_dummy + 1
        INTO l_dummy
        FROM DUAL;
   END LOOP;
END;
/

--使用dbms_trace跟踪程序
DECLARE
   l_result   BINARY_INTEGER;          --保存临明结果变量
BEGIN
   --跟踪所有的调用
   DBMS_TRACE.set_plsql_trace (DBMS_TRACE.trace_all_calls);
   do_something (100);
   --停止PL/SQL跟踪
   DBMS_TRACE.clear_plsql_trace;
   --跟踪所有的SQL语句
   DBMS_TRACE.set_plsql_trace (DBMS_TRACE.trace_all_sql);
   do_something (100);
   --停止跟踪
   DBMS_TRACE.clear_plsql_trace;
   --跟踪所有行数据
   DBMS_TRACE.set_plsql_trace (DBMS_TRACE.trace_all_lines);
   do_something (100);
   --停止跟踪
   DBMS_TRACE.clear_plsql_trace;
END;
/

--查看跟踪信息
SELECT r.runid,
       TO_CHAR(r.run_date, 'DD-MON-YYYY HH24:MI:SS') AS run_date,
       r.run_owner
FROM   plsql_trace_runs r
ORDER BY r.runid;

--查询跟踪的详细信息
SELECT e.runid,
       e.event_seq,
       TO_CHAR(e.event_time, 'DD-MON-YYYY HH24:MI:SS') AS event_time,
       e.event_unit_owner,
       e.event_unit,
       e.event_unit_kind,
       e.proc_line,
       e.event_comment
FROM   plsql_trace_events e
WHERE  e.runid = 1
ORDER BY e.runid, e.event_seq;



SELECT empno FROM emp;

--18.2 PL/SQL性能优化技巧
--查看执行计划
conn /as sysdba
@?/rdbms/admin/utlexplan.sql

--以下代码暂未调试
set autotrace on explain
select * from emp where empno=5093;

SELECT a.empno, a.ename, c.deptno, c.dname, a.log_action, b.sal
  FROM emp b, dept c, emp_log a
 WHERE a.deptno = b.deptno
 AND a.empno=b.empno 
 AND c.deptno IN (20, 30)



 SELECT a.empno, a.ename, c.deptno, c.dname, a.log_action, a.mgr
  FROM emp b, dept c, emp_log a
 WHERE c.deptno IN (20, 30)
 AND  a.deptno = b.deptno
 
 TOAD_PLAN_TABLE
 
 
 SELECT ename,deptno FROM emp WHERE empno=7369 
 
 SELECT empno,ename,deptno FROM emp WHERE deptno=20;
 
 
 SELECT * FROM emp
 
 

 
SELECT COUNT(*),SUM(sal) FROM　emp WHERE deptno = 20
UNION
SELECT COUNT(*),SUM(sal) FROM　emp WHERE deptno =30



SELECT COUNT (DECODE (deptno, 20, 'X', NULL)) dept20_count,
       COUNT (DECODE (deptno, 30, 'X', NULL)) dept30_count,
       SUM (DECODE (deptno, 20, sal, NULL)) dept20_sal,
       SUM (DECODE (deptno, 30, sal, NULL)) dept30_sal
  FROM emp;




SELECT CEIL(100/3) FROM dual;
SELECT FLOOR(100/3) FROM dual;
SELECT TRUNC(100/3) FROM dual;




SELECT   empno, deptno, SUM (sal)
    FROM emp
   WHERE deptno IN (20, 30)
GROUP BY empno, deptno
  HAVING SUM (sal) > 1000
　　

SELECT * FROM emp

SELECT empno,deptno FROM emp WHERE empno >=7500 OR deptno=20;



SELECT empno, ename, job, sal
  FROM emp
 WHERE empno > 7500 OR ename LIKE 'S%';


SELECT empno, ename, job, sal
  FROM emp
 WHERE empno > 7500
UNION
SELECT empno, ename, job, sal
  FROM emp
 WHERE ename LIKE 'S%';
 
 
 
 SELECT empno, ename, job, sal
  FROM emp
 WHERE deptno=20 OR deptno=30;
 
 
  SELECT empno, ename, job, sal
  FROM emp
 WHERE deptno IN (20,30);


SELECT * FROM dept;



SELECT *
  FROM emp
 WHERE deptno IN (SELECT deptno
                    FROM dept
                   WHERE loc = 'CHICAGO');
                   
                   
SELECT a.*
  FROM emp a
 WHERE NOT EXISTS (SELECT 1
                     FROM dept b
                    WHERE a.deptno = b.deptno AND loc = 'CHICAGO');   
                   

SELECT *
  FROM emp
 WHERE deptno NOT IN (SELECT deptno
                    FROM dept
                   WHERE loc = 'CHICAGO');      
                   
                   

SELECT a.*
  FROM emp a, dept b
 WHERE a.deptno = b.deptno AND b.loc <> 'CHICAGO';      
 
 
 

DECLARE
   v_sal   NUMBER        := &sal;                 --使用绑定变量输入薪资值
   v_job   VARCHAR2 (20) := &job;                 --使用绑定变量输入job值
BEGIN
   IF (v_sal > 5000) OR (v_job = '销售')          --判断执行条件
   THEN
      DBMS_OUTPUT.put_line ('符合匹配的OR条件');
   END IF;
END;                               



DECLARE
   v_sal   NUMBER        := &sal;                 --使用绑定变量输入薪资值
   v_job   VARCHAR2 (20) := &job;                 --使用绑定变量输入job值
BEGIN
   IF (Check_Sal(v_sal) > 5000) AND (v_job = '销售')          --判断执行条件
   THEN
      DBMS_OUTPUT.put_line ('符合匹配的AND条件');
   END IF;
END;           


DECLARE
   v_sal   NUMBER        := &sal;                     --使用绑定变量输入薪资值
   v_job   VARCHAR2 (20) := &job;                     --使用绑定变量输入job值
BEGIN
   IF (v_job = '销售') AND (Check_Sal(v_sal) > 5000)  --判断执行条件
   THEN
      DBMS_OUTPUT.put_line ('符合匹配的AND条件');
   END IF;
END;