Oracle优化 securecrt

http://www.eygle.com/case/how.to.getsql.which.cost.most.CPU.htm

count(*)   row_id   　查询是否存在记录　　
first-row　　会首先返回第一条记录。而后返回其他记录

使用Oracle特有的查询语法, 可以达到事半功倍的效果

1. 树查询
create table tree (
    id number(10) not null primary key,
    name varchar2(100) not null,
    super number(10) not null                // 0 is root
);
-- 从子到父
select * from tree start with id = ? connect by id = prior super
-- 从父到子
select * from tree start with id = ? connect by prior id = suepr
-- 整棵树
select * from tree start with super = 0 connect by prior id = suepr

2. 分页查询
select * from (
    select my_table.*, rownum  my_rownum from (
        select name, birthday from employee order by birthday
    ) my_table where rownum < 120
) where my_rownum >= 100;

3. 累加查询, 以scott.emp为例
select empno, ename, sal, sum(sal) over(order by empno) result from emp;

     EMPNO ENAME             SAL     RESULT
---------- ---------- ---------- ----------
      7369 SMITH             800        800
      7499 ALLEN            1600       2400
      7521 WARD             1250       3650
      7566 JONES            2975       6625
      7654 MARTIN           1250       7875
      7698 BLAKE            2850      10725
      7782 CLARK            2450      13175
      7788 SCOTT            3000      16175
      7839 KING             5000      21175
      7844 TURNER           1500      22675
      7876 ADAMS            1100      23775
      7900 JAMES             950      24725
      7902 FORD             3000      27725
      7934 MILLER           1300      29025

4. 高级group by
select decode(grouping(deptno),1,'all deptno',deptno) deptno,
       decode(grouping(job),1,'all job',job) job,
       sum(sal) sal
from emp
group by ROLLUP(deptno,job);
DEPTNO                                   JOB              SAL
---------------------------------------- --------- ----------
10                                       CLERK           1300
10                                       MANAGER         2450
10                                       PRESIDENT       5000
10                                       all job         8750
20                                       CLERK           1900
20                                       ANALYST         6000
20                                       MANAGER         2975
20                                       all job        10875
30                                       CLERK            950
30                                       MANAGER         2850
30                                       SALESMAN        5600
30                                       all job         9400
all deptno                               all job        29025

5. use hint
当多表连接很慢时,用ORDERED提示试试,也许会快很多
SELECT /**//*+ ORDERED */*
  FROM a, b, c, d
WHERE 


Oracle 9i 数据库WITH查询语法小议)

Oracle9i新增了WITH语法功能，可以将查询中的子查询命名，放到SELECT语句的最前面。

下面看一个简单的例子:

SQL> WITH
2 SEG AS (SELECT SEGMENT_NAME, SUM(BYTES)/1024 K FROM USER_SEGMENTS GROUP BY SEGMENT_NAME),
3 OBJ AS (SELECT OBJECT_NAME, OBJECT_TYPE FROM USER_OBJECTS)
4 SELECT O.OBJECT_NAME, OBJECT_TYPE, NVL(S.K, 0) SIZE_K
5 FROM OBJ O, SEG S
6 WHERE O.OBJECT_NAME = S.SEGMENT_NAME (+)
7 ;
OBJECT_NAME OBJECT_TYPE SIZE_K
DAIJC_TEST TABLE 128
P_TEST PROCEDURE 0
IND_DAIJC_TEST_C1 INDEX 128

通过WITH语句定义了两个子查询SEG和OBJ，在随后的SELECT语句中可以直接对预定义的子查询进行查询。从上面的例子也可以看出，使用WITH语句，将一个包含聚集、外连接等操作SQL清晰的展现出来。

WITH定义的子查询不仅可以使查询语句更加简单、清晰，而且WITH定义的子查询还具有在SELECT语句的任意层均可见的特点。

即使是在WITH的定义层中，后定义的子查询都可以使用前面已经定义好的子查询:

SQL> WITH
2 Q1 AS (SELECT 3 + 5 S FROM DUAL),
3 Q2 AS (SELECT 3 * 5 M FROM DUAL),
4 Q3 AS (SELECT S, M, S + M, S * M FROM Q1, Q2)
5 SELECT * FROM Q3;
S M S+M S*M
8 15 23 120

利用WITH定义查询中出现多次的子查询还能带来性能提示。Oracle会对WITH进行性能优化，当需要多次访问WITH定义的子查询时，Oracle会将子查询的结果放到一个临时表中，避免同样的子查询多次执行，从而有效的减少了查询的IO数量。

看一个简单的例子，首先构造一张大表，现在要取出大表中ID最小、ID最大以及ID等于平均值的记录，看看普通写法和WITH语句的区别:

SQL> CREATE TABLE T_WITH AS SELECT ROWNUM ID, A.* FROM DBA_SOURCE A WHERE ROWNUM < 100001;
表已创建。
SQL> SET TIMING ON
SQL> SET AUTOT ON
SQL> SELECT ID, NAME FROM T_WITH
2 WHERE ID IN
3 (
4 SELECT MAX(ID) FROM T_WITH
5 UNION ALL
6 SELECT MIN(ID) FROM T_WITH
7 UNION ALL
8 SELECT TRUNC(AVG(ID)) FROM T_WITH
9 );
ID NAME
1 STANDARD
50000 DBMS_BACKUP_RESTORE
100000 INITJVMAUX
已用时间: 00: 00: 00.09
执行计划
Plan hash value: 647530712
-----------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
-----------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 129 |
|* 1 | HASH JOIN | | 3 | 129 |
| 2 | VIEW | VW_NSO_1 | 3 | 39 |
| 3 | HASH UNIQUE | | 3 | 39 |
| 4 | UNION-ALL | | | |
| 5 | SORT AGGREGATE | | 1 | 13 |
| 6 | TABLE Access FULL| T_WITH | 112K| 1429K|
| 7 | SORT AGGREGATE | | 1 | 13 |
| 8 | TABLE ACCESS FULL| T_WITH | 112K| 1429K|
| 9 | SORT AGGREGATE | | 1 | 13 |
| 10 | TABLE ACCESS FULL| T_WITH | 112K| 1429K|
| 11 | TABLE ACCESS FULL | T_WITH | 112K| 3299K|
-----------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("ID"="$nso_col_1")
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
5529 consistent gets
0 physical reads
0 redo size
543 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3 rows processed

为了避免第一次执行时物理读的影响，查询结果选取了SQL的第三次运行，物理读为0时的统计信息。

观察执行计划可以看到，先后对T_WITH表进行了4次全表扫描，并产生了5529个逻辑读。下面看看WITH语句的表现:

SQL> WITH
2 AGG AS (SELECT MAX(ID) MAX, MIN(ID) MIN, TRUNC(AVG(ID)) AVG FROM T_WITH)
3 SELECT ID, NAME FROM T_WITH
4 WHERE ID IN
5 (
6 SELECT MAX FROM AGG
7 UNION ALL
8 SELECT MIN FROM AGG
9 UNION ALL
10 SELECT AVG FROM AGG
11 );
ID NAME
---------- ------------------------------
1 STANDARD
50000 DBMS_BACKUP_RESTORE
100000 INITJVMAUX
已用时间: 00: 00: 00.07
执行计划
----------------------------------------------------------
Plan hash value: 1033356310
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 129 |
| 1 | TEMP TABLE TRANSFORMATION | | | |
| 2 | LOAD AS SELECT | T_WITH | | |
| 3 | SORT AGGREGATE | | 1 | 13 |
| 4 | TABLE ACCESS FULL | T_WITH | 112K| 1429K|
|* 5 | HASH JOIN | | 3 | 129 |
| 6 | VIEW | VW_NSO_1 | 3 | 39 |
| 7 | HASH UNIQUE | | 3 | 39 |
| 8 | UNION-ALL | | | |
| 9 | VIEW | | 1 | 13 |
| 10 | TABLE ACCESS FULL | SYS_TEMP_0FD9D662E_BF2EDF12 | 1 | 13 |
| 11 | VIEW | | 1 | 13 |
| 12 | TABLE ACCESS FULL | SYS_TEMP_0FD9D662E_BF2EDF12 | 1 | 13 |
| 13 | VIEW | | 1 | 13 |
| 14 | TABLE ACCESS FULL | SYS_TEMP_0FD9D662E_BF2EDF12 | 1 | 13 |
| 15 | TABLE ACCESS FULL | T_WITH | 112K| 3299K|
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("ID"="$nso_col_1")
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
2 recursive calls
8 db block gets
2776 consistent gets
1 physical reads
648 redo size
543 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3 rows processed

观察这次的执行计划，发现只对T_WITH表进行了两次全表扫描，而从逻辑读上也可以观察到，这次只产生了2776的逻辑读，正好是上面不使用WITH语句的一半。

通过分析执行计划，Oracle执行了WITH子查询一次，并将结果放到了临时表中，在随后对子查询的多次访问中，都从临时表中直接读取了数据，这应该也是那1个物理读的由来。

通过上面的例子可以看到，将子查询放到WITH语句中不仅可以简化查询语句的结构，对于子查询需要多次执行的情况，还有可能提示查询的性能。

可惜的是，WITH语句只能用在SELECT语句中，UPDATE和DELETE语句不支持WITH语法:

SQL> SET AUTOT OFF
SQL> SET TIMING OFF
SQL> WITH SUBQ AS (SELECT 1 FROM DUAL)
2 SELECT ID, NAME FROM T_WITH WHERE ID IN (SELECT * FROM SUBQ);
ID NAME
---------- ------------------------------
1 STANDARD
SQL> WITH SUBQ AS (SELECT 1 FROM DUAL)
2 UPDATE T_WITH SET ID = 1 WHERE ID IN (SELECT * FROM SUBQ);
UPDATE T_WITH SET ID = 1 WHERE ID IN (SELECT * FROM SUBQ)
*第 2 行出现错误:
ORA-00928: 缺失 SELECT 关键字
SQL> WITH SUBQ AS (SELECT 1 FROM DUAL)
2 DELETE T_WITH WHERE ID IN (SELECT * FROM SUBQ);
DELETE T_WITH WHERE ID IN (SELECT * FROM SUBQ)
*第 2 行出现错误:
ORA-00928: 缺失 SELECT 关键字

评论

3 楼 julyboxer 2008-04-13  

淘宝DBA

http://rdc.taobao.com/blog/dba/

2 楼 julyboxer 2008-04-03  

with pd as(select * from pay_detail t1 where t1.agent_id = '731CS999')
select t2.emp_id as empId, nvl(sum(pay_money), 0) as count_money, sum(decode(PAY_TYPE, '0', pay_money, '1', pay_money, 0)) tamsself, sum(decode(PAY_TYPE, '2', pay_money, '3', pay_money, '5', pay_money, 0)) crm  from pd t2 where is_payoff=1 group by t2.emp_id

1 楼 julyboxer 2008-04-03  

oracle9 statspack
oracle awr