Xpert 调优

-- 10046 event 可以定义 SQL TRACE 级别
/*
|| 默认的10046级别跟 SQL TRACE 一样, 另外还有一些级别:
|| level 1: SQL Tracing
|| level 4: Tracing with bind variable values
|| level 8: Tracing with wait events
|| level 12 Tracing with bind variables and wait events (4+8=12)

level 4, 12 可以看到绑定变量的值.
*/

alter session set events '10046 trace name context forever, level 4';
-- 关闭 10046, 只需要将 level 设置成 0

-- If you wish to set the 10046 event in another user's session,
-- you will need to use the DBMS_SYSTEM, The SET_EV procedure can set an event in any session
-- You will need the SID and SERIAL# of that session from V$SESSION.
-- example:
exec dbms_system.set_ev(sid,serial#,10046,12,'');

-- 另外要使用 TKPROF 来查看 trace file 生成的报告, 直接看 trace file 无法看懂

10046 event

-- Program data instantiated within a program is stored in PGA for each user, rather than in the SGA
-- 执行pl/sql程度的相关数据, 存储在PGA中, 被编译好的PL/SQL程序本身是共享的.
-- This means that each user has his own copy of program data. If a procedure declares a PL/SQL table
-- and fills it full of 1000 strings, every user who executes that procedure has his own copy of that table and its 1000 
-- strings. This characteristic does however provide you with a fair amount of flexibility 
-- to finding the delicate resource balance between CPU and memory.

-- 权限问题
-- Stored code runs under the authority of that code’s owner, not that of the schema which is executing the code. 
-- 也就是说, 存储过程执行时是创建存储过程那个用户的权限.
-- Even though stored code runs under the authority of that code’s owner, the USER function still 
-- returns the name of the schema currently connected to Oracle.

-- 执行 procedure
-- sqlplus 环境,　execute calc_totals 或 exec calc_totals
-- 如果是匿名的, 直接就执行了, 比如
begin
    calc_totals;
end;

-- 执行 function, 因为 function 有返回值, 所以, 它不能直接执行, 要有个类似变量来接收函数返回值.
-- 直接执行, 可以类似这种
exec DBMS_OUTPUT.PUT_LINE (total_sales (1997))

-- 远程调用
schema_name.[package_name.]module_name@database_link_name
emp_name := full_name@new_york (:emp.employee_id);

basic

-- sql*plus 提供的session变量
-- session variable , 可以使用的数据类型 varchar2, varchar2(N), char
-- number, number(N)

varibale X varchar2(10)
EXECUTE :X := '123';
PRINT X

session varibale

-- Because stored objects are contained in tables in the data dictionary, you can use SQL itself to get information 
-- about the currently available programs. The following views are the most useful to familiarize yourself with:

/*
USER_DEPENDENCIES - The dependencies to and from objects you own
USER_ERRORS - The current set of errors for all stored objects you own.
USER_OBJECTS - The objects you own
USER_OBJECT_SIZE - The size of the objects you own
USER_SOURCE - The text source code for all objects you own
USER_TRIGGER - The database triggers you own 
*/

-- USER_DEPENDENCIES
-- Use the USER_DEPENDENCIES view to see which objects reference or depend on a particular object
SELECT referenced_name, 
       referenced_type, 
       referenced_owner, 
       referenced_link_name
  FROM user_dependencies
 WHERE name = UPPER ('&1');

-- USER_OBJECTS
-- Displaying Information About Stored Objects, 3列比较重要 OBJECT_NAME, OBJECT_TYPE, STATUS
SET PAGESIZE 66
COLUMN object_type FORMAT A20
COLUMN object_name FORMAT A30
COLUMN status FORMAT A10
BREAK ON object_type SKIP 1
SPOOL psobj.lis
SELECT object_type, object_name, status
  FROM user_objects
 WHERE object_type IN ('PACKAGE', 'PACKAGE BODY', 'FUNCTION', 'PROCEDURE')
 ORDER BY object_type, status, object_name
/
SPOOL OFF

-- USER_OBJECT_SIZE
-- tell you about the size of your code
SET PAGESIZE 66
COLUMN name FORMAT A30
COLUMN type FORMAT A15
COLUMN source_size FORMAT 999999
COLUMN parsed_size FORMAT 999999
COLUMN code_size FORMAT 999999
TTITLE 'Size of PL/SQL Objects > 2000 Bytes'
SPOOL pssize.lis
SELECT name, type, source_size, parsed_size, code_size
  FROM user_object_size
 WHERE code_size > 2000
 ORDER BY code_size DESC
/
SPOOL OFF

-- USER_SOURCE
-- You should always maintain the source code of your programs in text files (or a development tool specifically designed
-- to store and manage PL/SQL code outside of the database).I
SELECT text 
  FROM user_source 
 WHERE name = UPPER ('&1') 
 ORDER BY line;

Check Object

-- 1. 选择驱动表, 多表连接时
-- When Oracle processes multiple tables, it must join the two tables. 
--  There are four different join methods in the Oracle database, 
-- the Nested Loop Join, the Sort-Merge Join, the Hash Join, and the Cartesian Join
-- Cartesian Join : 笛卡尔积连接
-- Sort-Merge Join: has a sort phase and a merge phase, it scans and sorts the first (driving) 
--                   table (the one specified second in the FROM clause). Next, it scans the second
--                    table (the one specified first in the FROM clause) and merges all of the rows
--                    retrieved from the second table with those retrieved from the first table.
-- Nested Loop Join: every row in the driving table and then finds matches in the second table.
--                    If there is no index on the join column, a full table scan is performed on
--                    the second table for every row in the driving table.
--                    这种的话, 如果主驱动表100W列, 而另一个表只有10列的话, 肯定是将只有10列的表设置为
--                    主驱动表比较好
-- Hash Join:     uses a hashing algorithm (hence its name) to divide the driving table's rows into
--                multiple buckets. The second table is hashed with the same algorithm.
-- 注意: 主驱动表是  is the second table in the FROM clause of the SQL statement.
-- 例如:
/*
Table TAB1  has 16,384   rows.
Table TAB2  has 4        rows.
*/
SELECT COUNT (*) FROM TAB1, TAB2         -- 4.00 seconds elapsed
                     count     phys      cr        cur      rows
                     ------    -----    -----     -----    ------
        Parse          1         0      0         0
        Execute        1         384    386       10         0
        Fetch          1         0      0         0          1

SELECT COUNT (*) FROM TAB2, TAB1 -- 37.32 seconds elapsed
                     count     phys      cr        cur      rows
                     ------    -----    -----     -----    ------
        Parse          1         0      0         0
        Execute        1         95     49247     32770      0
        Fetch          1         0      0         0          1

-- 多表查询join 时, 选择几个表之间的交集的那个表作为驱动表.
-- 多表时, 貌似 from 后的第一个表是驱动表, 或者通过交集? 看下边例子
SELECT   . . .
FROM     location L,
         category C,
         emp E
  WHERE  E.emp_no BETWEEN 1000 AND 2000
    AND  E.cat_no =  C.cat_no
    AND  E.locn   =  L.locn
-- 更高效的做法
SELECT     . . .
 FROM      emp E,
           location L,
           category C
    WHERE  E.cat_no =  C.cat_no
      AND  E.locn   =  L.locn
      AND  E.emp_no BETWEEN 1000 AND 2000

调优法则

-- 1. 高效的删除重复的行 #################################
DELETE FROM emp E
      WHERE E.rowid > (SELECT MIN(X.rowid)
                            FROM emp X
                           WHERE X.emp_no = E.emp_no);

-- 2. 一般情况下, 在列上有索引时, 选择使用 IN 或 UNION 比 OR 更好. #################################
SELECT . . .                     SELECT . . .
   FROM   location                  FROM   location
   WHERE  loc_id = 10               WHERE  loc_id = 10
   OR     region = 'MELBOURNE'      OR     loc_id = 20
                                    OR     loc_id = 30
-- 改进:
SELECT . . .                     SELECT . . .
   FROM   location                  FROM   location
   WHERE  loc_id = 10               WHERE  loc_in IN (10,20,30)
   UNION
   SELECT . . .
   FROM   location
   WHERE  region = 'MELBOURNE'

-- 3. 最小化表扫描 minimize the number of table lookups (subquery blocks) in queries, 例如: ##########
SELECT emp_name
FROM   emp
WHERE  emp_cat   = ( SELECT MAX(category)
                        FROM   emp_categories )
AND    sal_range = ( SELECT MAX(sal_range)
                        FROM   emp_categories )
AND    emp_dept  = 0020;
-- 上例中, 两个子查询是分开的, 那么需要独立扫描两次, 改进如下:
SELECT emp_name
FROM   emp
WHERE (emp_cat, sal_range)
                    = ( SELECT MAX(category), MAX(sal_range)
                        FROM   emp_categories )
AND    emp_dept  = 0020;
-- 修改的时候也一样, 参考下例:
UPDATE emp
SET    emp_cat   = ( SELECT MAX(category)
                     FROM   emp_categories ),
       sal_range = ( SELECT MAX(sal_range)
                     FROM   emp_categories )
WHERE  emp_dept  = 0020;
-- 改进
UPDATE emp
SET   (emp_cat, sal_range)
                 = ( SELECT MAX(category), MAX(sal_range)
                     FROM   emp_categories )
WHERE  emp_dept  = 0020;

-- 4. not exists 与 not in 的比较 not exists 好 ########################################
SELECT . . .
FROM   emp
WHERE  dept_no NOT IN ( SELECT dept_no
                           FROM   dept
                           WHERE  dept_cat = 'A');
-- 改进
SELECT . . .
FROM   emp E
WHERE  NOT EXISTS ( SELECT  'X'
                       FROM   dept
                       WHERE  dept_no  = E.dept_no
                       AND    dept_cat = 'A'   );
-- 5. reduce database access
-- 例如: 低效, 分别两次访问 database
SELECT emp_name, salary, grade
FROM    emp
WHERE  emp_no = 0342;

SELECT emp_name, salary, grade
FROM    emp
WHERE  emp_no = 0291;
-- 改进, 一次访问数据库
SELECT   emp_name, salary, grade,
FROM     emp A
WHERE    emp_no IN (0342,0291) ;

-- 6. 使用 * 时, 最好给表起个别名, 然后使用, 这样高效, 例如 ####################################
INSERT INTO emp_audit
SELECT USER, SYSDATE, A.*
FROM   emp A
WHERE  emp_no = :emp_no;

-- 7. union 和 union all
-- 一般如果不考虑去掉 重复的, 还有排序等内容的话, 使用 union all

-- 8. decode 可以被活用, 例如:
SELECT COUNT(*), SUM(salary)
FROM   emp
WHERE  dept_no  =  0020
AND    emp_name LIKE 'SMITH%' ;

SELECT COUNT(*), SUM(salary)
FROM   emp
WHERE  dept_no  =  0030
AND    emp_name LIKE 'SMITH%' ;
-- 改进
SELECT COUNT(DECODE(dept_no, 0020, 'X',    NULL)) D0020_kount,
       COUNT(DECODE(dept_no, 0030, 'X',    NULL)) D0030_kount,
       SUM  (DECODE(dept_no, 0020, salary, NULL)) D0020_sal,
       SUM  (DECODE(dept_no, 0030, salary, NULL)) D0030_sal
 FROM  emp
WHERE  emp_name LIKE 'SMITH%';

一般SQL调优