-- 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 := '';
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调优