使用分析函数和不使用分析函数的性能对比测试

set autotrace traceonly statistics;

 

select t1.owner, t1.last_ddl_time, t1.object_name, t1.object_type

from big_table t1

where last_ddl_time = (select max(last_ddl_time)

                         from big_table t2

                        where t2.owner = t1.owner )

/

 

select t1.owner, t1.last_ddl_time, t1.object_name, t1.object_type

  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 owner, last_ddl_time, object_name, object_type,

                max(last_ddl_time) over (partition by owner) max_time

           from big_table t1

           )

 where last_ddl_time = max_time

/

 

set autotrace off

 

这三个查询获得的结果集是一样的，其中第三个查询使用了分析函数，现在对比一下测试结果：

第一个查询：

统计信息

----------------------------------------------------------

        222  recursive calls

          0  db block gets

      58305  consistent gets

      48329  physical reads

       1052  redo size

     146763  bytes sent via SQL*Net to client

       3256  bytes received via SQL*Net from client

        263  SQL*Net roundtrips to/from client

          5  sorts (memory)

          0  sorts (disk)

       3923  rows processed

第二个查询：

统计信息

----------------------------------------------------------

          1  recursive calls

          0  db block gets

      58241  consistent gets

      48001  physical reads

       1008  redo size

     146763  bytes sent via SQL*Net to client

       3256  bytes received via SQL*Net from client

        263  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

       3923  rows processed

第三个查询：

统计信息

----------------------------------------------------------

        113  recursive calls

         26  db block gets

      28992  consistent gets

      87678  physical reads

        504  redo size

     122732  bytes sent via SQL*Net to client

       3256  bytes received via SQL*Net from client

        263  SQL*Net roundtrips to/from client

          0  sorts (memory)

          1  sorts (disk)

       3923  rows processed

从测试结果中可以看出，使用分析函数的物理I/O相当高，比前两个查询高出一倍。

 

再查看一下tkprof报告：

select t1.owner, t1.last_ddl_time, t1.object_name, t1.object_type

from big_table t1

where last_ddl_time = (select max(last_ddl_time)

                         from big_table t2

                        where t2.owner = t1.owner )

 

call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse        1      0.00       0.01          0          0          0           0

Execute      1      0.00       0.00          0          0          0           0

Fetch      263      3.12       7.63      48329      58241          0        3923

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total      265      3.12       7.65      48329      58241          0        3923

 

Misses in library cache during parse: 1

Optimizer mode: ALL_ROWS

Parsing user id: 5 

 

Rows     Row Source Operation

-------  ---------------------------------------------------

   3923  HASH JOIN  (cr=58241 pr=48329 pw=0 time=13666623 us)

     21   VIEW  VW_SQ_1 (cr=28992 pr=24321 pw=0 time=4044773 us)

     21    HASH GROUP BY (cr=28992 pr=24321 pw=0 time=4044622 us)

2000001     TABLE ACCESS FULL BIG_TABLE (cr=28992 pr=24321 pw=0 time=10000041 us)

2000001   TABLE ACCESS FULL BIG_TABLE (cr=29249 pr=24008 pw=0 time=8000077 us)

 

 

Elapsed times include waiting on following events:

  Event waited on                             Times   Max. Wait  Total Waited

  ----------------------------------------   Waited  ----------  ------------

  SQL*Net message to client                     263        0.00          0.00

  db file scattered read                       3023        0.08          4.64

  SQL*Net message from client                   263        0.02          0.09

********************************************************************************

 

select t1.owner, t1.last_ddl_time, t1.object_name, t1.object_type

  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

 

call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse        1      0.00       0.00          0          0          0           0

Execute      1      0.00       0.00          0          0          0           0

Fetch      263      3.39       7.50      48001      58241          0        3923

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total      265      3.39       7.51      48001      58241          0        3923

 

Misses in library cache during parse: 1

Optimizer mode: ALL_ROWS

Parsing user id: 5 

 

Rows     Row Source Operation

-------  ---------------------------------------------------

   3923  HASH JOIN  (cr=58241 pr=48001 pw=0 time=13899793 us)

     21   VIEW  (cr=28992 pr=24000 pw=0 time=3870272 us)

     21    HASH GROUP BY (cr=28992 pr=24000 pw=0 time=3870119 us)

2000001     TABLE ACCESS FULL BIG_TABLE (cr=28992 pr=24000 pw=0 time=8000055 us)

2000001   TABLE ACCESS FULL BIG_TABLE (cr=29249 pr=24001 pw=0 time=8000081 us)

 

 

Elapsed times include waiting on following events:

  Event waited on                             Times   Max. Wait  Total Waited

  ----------------------------------------   Waited  ----------  ------------

  SQL*Net message to client                     263        0.00          0.00

  db file scattered read                       3007        0.03          4.60

  SQL*Net message from client                   263        0.00          0.07

  db file sequential read                         1        0.00          0.00

********************************************************************************

 

select owner, last_ddl_time, object_name, object_type

  from ( select owner, last_ddl_time, object_name, object_type,

                max(last_ddl_time) over (partition by owner) max_time

           from big_table t1

           )

 where last_ddl_time = max_time

 

call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse        1      0.00       0.00          0          0          0           0

Execute      1      0.00       0.00          0          0          0           0

Fetch      263     14.09      52.91      87678      28992         26        3923

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total      265     14.09      52.92      87678      28992         26        3923

 

Misses in library cache during parse: 1

Optimizer mode: ALL_ROWS

Parsing user id: 5 

 

Rows     Row Source Operation

-------  ---------------------------------------------------

   3923  VIEW  (cr=28992 pr=87678 pw=49804 time=32953072 us)

2000001   WINDOW SORT (cr=28992 pr=87678 pw=49804 time=52352685 us)

2000001    TABLE ACCESS FULL BIG_TABLE (cr=28992 pr=23999 pw=0 time=10000047 us)

 

 

Elapsed times include waiting on following events:

  Event waited on                             Times   Max. Wait  Total Waited

  ----------------------------------------   Waited  ----------  ------------

  SQL*Net message to client                     263        0.00          0.00

  direct path write temp                       2072        0.03          1.21

  db file scattered read                       1506        0.04          3.08

  direct path read temp                       37183        0.21         35.12

  SQL*Net message from client                   263        0.02          0.10

********************************************************************************

 

从测试报告可以看出，前两个查询实际上使用的是同一个查询计划，最后一个使用分析函数的SQL使用不同的查询计划。从查询效率上来看，测试结果和《Oracle高效设计》上提供的有很大区别，使用分析函数的查询，在WINDOW SORT步骤付出的代价极大，进行了大量物理I/O。

这可能和排序区域尺寸较小有关，

show parameter sort_area_size;

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

sort_area_size                       integer     65536

 

这个值的确很小，只有64k，修改一下：

alter session set sort_area_size = 102400000;

改成100M，再测试一下，发现测试结果差不多，看来使用分析函数效率未必会提高，有可能还会变差很多。