ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 那个会更好?

那个会更好?

原创 Linux操作系统 作者:lfree 时间:2007-05-02 00:00:00 0 删除 编辑

例子摘自:《oracle高效设计》
select *
from big_table t1
where last_ddl_time = (select max(last_ddl_time)
from big_table t2
where t2.owner = t1.owner )
/

select *
from big_table t1, ( select owner, max(last_ddl_time) max_time
from big_table
group by owner ) t2
where t1.owner = t2.owner
and t1.last_ddl_time = t2.max_time
/

select owner, last_ddl_time, object_name, object_type
from ( select t1.*,
max(last_ddl_time) over (partition by owner) max_time
from big_table t1
)
where last_ddl_time = max_time
/


这是oracle高效设计(中文版P393)中的关于使用分析函数的例子,我的测试结果是使用分析函数并不会很好!big_table大小45M,记录数406032。


例子1:
Execution Plan
----------------------------------------------------------
Plan hash value: 4038969119

----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 293 | 34867 | 2580 (5)| 00:00:31 |
|* 1 | HASH JOIN | | 293 | 34867 | 2580 (5)| 00:00:31 |
| 2 | VIEW | VW_SQ_1 | 24 | 624 | 1312 (7)| 00:00:16 |
| 3 | HASH GROUP BY | | 24 | 336 | 1312 (7)| 00:00:16 |
| 4 | TABLE ACCESS FULL| BIG_TABLE | 405K| 5540K| 1253 (3)| 00:00:16 |
| 5 | TABLE ACCESS FULL | BIG_TABLE | 405K| 35M| 1260 (3)| 00:00:16 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("LAST_DDL_TIME"="VW_COL_1" AND "OWNER"="T1"."OWNER")


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
11219 consistent gets
0 physical reads
0 redo size
60026 bytes sent via SQL*Net to client
450 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1224 rows processed

例子2与1相同:忽略

但是例子3如下:

Execution Plan
----------------------------------------------------------
Plan hash value: 4069026481

-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 405K| 43M| | 6153 (2)| 00:01:14 |
|* 1 | VIEW | | 405K| 43M| | 6153 (2)| 00:01:14 |
| 2 | WINDOW SORT | | 405K| 18M| 46M| 6153 (2)| 00:01:14 |
| 3 | TABLE ACCESS FULL| BIG_TABLE | 405K| 18M| | 1260 (3)| 00:00:16 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("LAST_DDL_TIME"="MAX_TIME")


Statistics
----------------------------------------------------------
23 recursive calls
8 db block gets
5606 consistent gets
9604 physical reads
0 redo size
30144 bytes sent via SQL*Net to client
450 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
0 sorts (memory)
1 sorts (disk)
1224 rows processed

发现有许多physical reads读,检查
show parameter pga

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 80M

发现PGA太小,修改如下:

alter system set pga_aggregate_target=2000M scope=memory

在执行:

-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 405K| 43M| | 6153 (2)| 00:01:14 |
|* 1 | VIEW | | 405K| 43M| | 6153 (2)| 00:01:14 |
| 2 | WINDOW SORT | | 405K| 18M| 46M| 6153 (2)| 00:01:14 |
| 3 | TABLE ACCESS FULL| BIG_TABLE | 405K| 18M| | 1260 (3)| 00:00:16 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("LAST_DDL_TIME"="MAX_TIME")


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
5606 consistent gets
0 physical reads
0 redo size
30308 bytes sent via SQL*Net to client
450 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1224 rows processed


发现physical reads为0,但是实际上逻辑读虽然减少了,但是执行的时间实际没有例子1快。我的测试发现:


例子1,2:执行时间:3XX msecs。
例子3(pga_aggregate_target=80M)下:4 secs
例子3(pga_aggregate_target=2000M)下:7xx msecs。

也就是讲在pga_aggregate_target设置适当的情况下,例子3执行才会好一些。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-83025/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2488
  • 访问量
    6293060