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_nameemp_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 ownUSER_ERRORS - The current set of errors for all stored objects you own.USER_OBJECTS - The objects you ownUSER_OBJECT_SIZE - The size of the objects you ownUSER_SOURCE - The text source code for all objects you ownUSER_TRIGGER - The database triggers you own */-- USER_DEPENDENCIES-- Use the USER_DEPENDENCIES view to see which objects reference or depend on a particular objectSELECT 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, STATUSSET PAGESIZE 66COLUMN object_type FORMAT A20COLUMN object_name FORMAT A30COLUMN status FORMAT A10BREAK ON object_type SKIP 1SPOOL psobj.lisSELECT 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 codeSET PAGESIZE 66COLUMN name FORMAT A30COLUMN type FORMAT A15COLUMN source_size FORMAT 999999COLUMN parsed_size FORMAT 999999COLUMN code_size FORMAT 999999TTITLE 'Size of PL/SQL Objects > 2000 Bytes'SPOOL pssize.lisSELECT 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).ISELECT 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          1SELECT 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_nameFROM   empWHERE  emp_cat   = ( SELECT MAX(category)                        FROM   emp_categories )AND    sal_range = ( SELECT MAX(sal_range)                        FROM   emp_categories )AND    emp_dept  = 0020;-- 上例中, 两个子查询是分开的, 那么需要独立扫描两次, 改进如下:SELECT emp_nameFROM   empWHERE (emp_cat, sal_range)                    = ( SELECT MAX(category), MAX(sal_range)                        FROM   emp_categories )AND    emp_dept  = 0020;-- 修改的时候也一样, 参考下例:UPDATE empSET    emp_cat   = ( SELECT MAX(category)                     FROM   emp_categories ),       sal_range = ( SELECT MAX(sal_range)                     FROM   emp_categories )WHERE  emp_dept  = 0020;-- 改进UPDATE empSET   (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   empWHERE  dept_no NOT IN ( SELECT dept_no                           FROM   dept                           WHERE  dept_cat = 'A');-- 改进SELECT . . .FROM   emp EWHERE  NOT EXISTS ( SELECT  'X'                       FROM   dept                       WHERE  dept_no  = E.dept_no                       AND    dept_cat = 'A'   );-- 5. reduce database access-- 例如: 低效, 分别两次访问 databaseSELECT emp_name, salary, gradeFROM    empWHERE  emp_no = 0342;SELECT emp_name, salary, gradeFROM    empWHERE  emp_no = 0291;-- 改进, 一次访问数据库SELECT   emp_name, salary, grade,FROM     emp AWHERE    emp_no IN (0342,0291) ;-- 6. 使用 * 时, 最好给表起个别名, 然后使用, 这样高效, 例如 ####################################INSERT INTO emp_auditSELECT USER, SYSDATE, A.*FROM   emp AWHERE  emp_no = :emp_no;-- 7. union 和 union all-- 一般如果不考虑去掉 重复的, 还有排序等内容的话, 使用 union all-- 8. decode 可以被活用, 例如:SELECT COUNT(*), SUM(salary)FROM   empWHERE  dept_no  =  0020AND    emp_name LIKE 'SMITH%' ;SELECT COUNT(*), SUM(salary)FROM   empWHERE  dept_no  =  0030AND    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  empWHERE  emp_name LIKE 'SMITH%';

一般SQL调优