oracle sql调优系列1(decode)
来源:互联网 发布:保护地球环境资料数据 编辑:程序博客网 时间:2024/06/03 19:58
oracle sql调优系列1(decode)
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
SQL> set autotrace on
执行sql语句1:
SELECT COUNT(*),SUM(sal) FROM emp WHERE deptno=30
union all
SELECT COUNT(*),SUM(sal) FROM emp WHERE deptno=20;
union all
SELECT COUNT(*),SUM(sal) FROM emp WHERE deptno=20;
COUNT(*) SUM(SAL)
---------- ----------
6 9400
5 10875
Execution Plan
----------------------------------------------------------
Plan hash value: 2390793
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 14 | 6 (50)| 00:00:01 |
| 1 | UNION-ALL | | | | | |
| 2 | SORT AGGREGATE | | 1 | 7 | | |
|* 3 | TABLE ACCESS FULL| EMP | 6 | 42 | 3 (0)| 00:00:01 |
| 4 | SORT AGGREGATE | | 1 | 7 | | |
|* 5 | TABLE ACCESS FULL| EMP | 5 | 35 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 14 | 6 (50)| 00:00:01 |
| 1 | UNION-ALL | | | | | |
| 2 | SORT AGGREGATE | | 1 | 7 | | |
|* 3 | TABLE ACCESS FULL| EMP | 6 | 42 | 3 (0)| 00:00:01 |
| 4 | SORT AGGREGATE | | 1 | 7 | | |
|* 5 | TABLE ACCESS FULL| EMP | 5 | 35 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
---------------------------------------------------
3 - filter("DEPTNO"=30)
5 - filter("DEPTNO"=20)
5 - filter("DEPTNO"=20)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
14 consistent gets
0 physical reads
0 redo size
516 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)
2 rows processed
执行sql语句2(decode):
SQL> select count(decode(deptno,20,'x',null)) d20_count,
2 count(decode(deptno,30,'x',null)) d30_count,
3 sum(decode(deptno,20,sal,null)) d20_count,
4 sum(decode(deptno,30,sal,null)) d30_count
5 from emp
6 ;
2 count(decode(deptno,30,'x',null)) d30_count,
3 sum(decode(deptno,20,sal,null)) d20_count,
4 sum(decode(deptno,30,sal,null)) d30_count
5 from emp
6 ;
D20_COUNT D30_COUNT D20_COUNT D30_COUNT
---------- ---------- ---------- ----------
5 6 10875 9400
---------- ---------- ---------- ----------
5 6 10875 9400
Execution Plan
----------------------------------------------------------
Plan hash value: 2083865914
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 7 | | |
| 2 | TABLE ACCESS FULL| EMP | 14 | 98 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 7 | | |
| 2 | TABLE ACCESS FULL| EMP | 14 | 98 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
600 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)
1 rows processed
总结:sql语句1产生了14个逻辑读,sql语句2产生了7个逻辑读,很显然sql语句1的性能
远远低于sql2的性能.
0 0
- oracle sql调优系列1(decode)
- decode [Oracle SQL][转贴]
- oracle sql decode
- oracle中的SQL函数:decode()
- ORACLE SQL性能调优系列一
- Oracle 数据库SQL中 decode()函数简介
- Oracle 的 SQL语句中 decode()函数
- oracle-SQL-case when 改用 DECODE
- oracle decode
- oracle decode
- Oracle DECODE
- oracle decode()
- oracle decode()
- ORACLE decode
- oracle decode
- Oracle DECODE
- oracle decode
- oracle decode
- 压缩感知——大白话版
- Linux内核创建一个新进程的过程
- VIM配置自动添加作者信息
- 理解Objective-C中的消息发送
- POJ3278---Catch That Cow
- oracle sql调优系列1(decode)
- 安装oracle 11g过程中,prerequisite checks过程中各种failed问题解决
- 乐视智能硬件再发力,超级自行车发布
- 寄存器变量
- 最小费用最大流算法
- LUA string库详解
- 线性表
- 割点和桥
- 第五周 项目1-体验常成员函数