Re: Tom Kyte's book obsolescence

From: Gints Plivna <gints.plivna_at_gmail.com>
Date: Sat, 2 Feb 2008 05:48:37 -0800 (PST)
Message-ID: <a83a8bea-157e-45a0-b404-ec078b4ff457@s37g2000prg.googlegroups.com>

On 1 Febr., 17:02, Mladen Gogala <mgog..._at_yahoo.com> wrote:
> This is a longer post and I beg your forgiveness for that.
>
> Recently, I've had a complaint that analytic function is not executing
> very fast. The Oracle versions involved were 10.2.0.3 and 11.1.0.6. I
> wrote the same query as a self-join and, to my infinite surprise, the
> query was running faster then with the analytic functions. I remembered a
> book that says it shouldn't be that way. The book was "Effective Oracle
> by Design", by Tom Kyte. I created the structures, ran the queries and the
> book was wrong on versions 10g and 11g. It was correct on the version 9i.

<..skipped..>

OK I was excited and tried to reporduce the cases. So as Mladen has already provided how to set up bigtab, I just used his version (with BTW 2 additional columns for all_objects in 11g) on my home PC (2G RAM). I was really surprised!
sga_target and sga_max_size both 1.5 G
Without any explicit changes subselect version run in 1.01 sec while analytic version run 36.96 secs. However the hard disk diode was flashing all the time and autotrace showed 1 sort on disk. So with
alter session set workarea_size_policy = manual; alter session set sort_area_size = 2000000000; (~2G) alter session set hash_area_size = 2000000000; (~2G) I was able to minimize the analytic version down to 3.46 secs (with one sort in memory according to autotrace) which is 10 times faster than the first analytic shot but 3 times slower than subquery version anyway.
All the sqls are below.

Speaking about the Mladen's "implication"
> 2) Analytic functions are now just a neat trick, no longer a performance
> improvement. The cost of the window buffer sort is relatively high
> compared to the cost of simple join.
I don't agree with that. Analytics are just much more than maximum row within a group. For example with lag and lead one can look either fixed or computed rows backwards and forward (how to do that without analytics???), TOP N analysis not always include finding just maximum but finding N, where N>1 and generally all that would need more joins than one or sometimes even cannot simply be done with simple SQL without analytics.

Gints Plivna
http://www.gplivna.eu

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> set autot traceonly
SQL> set timing on
SQL> select owner, last_ddl_time, object_name, object_type

  2 from bigtab t1
  3 where last_ddl_time = (select max(last_ddl_time)

  4                         from bigtab t2
  5                         where t2.owner = t1.owner)

  6 /

544 rows selected.

Elapsed: 00:00:01.01

Execution Plan

---

   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=8527 Card=201 Byte
          s=14874)

   1    0   HASH JOIN (Cost=8527 Card=201 Bytes=14874)
   2    1     VIEW OF 'VW_SQ_1' (VIEW) (Cost=4287 Card=23 Bytes=598)
   3    2       HASH (GROUP BY) (Cost=4287 Card=23 Bytes=506)
   4    3         TABLE ACCESS (FULL) OF 'BIGTAB' (TABLE) (Cost=4234 C
          ard=1000000 Bytes=22000000)

   5    1     TABLE ACCESS (FULL) OF 'BIGTAB' (TABLE) (Cost=4234 Card=
          1000000 Bytes=48000000)

Statistics

---

          0  recursive calls
          0  db block gets
      30807  consistent gets
          0  physical reads
        404  redo size
      23449  bytes sent via SQL*Net to client
        908  bytes received via SQL*Net from client
         38  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        544  rows processed

SQL> select owner, last_ddl_time, object_name, object_type   2 from (
  3 select t1.*, max(last_ddl_time) over (partition by owner) max_time
  4 from bigtab t1
  5 )
  6 where last_ddl_time = max_time;

544 rows selected.

Elapsed: 00:00:36.96

Execution Plan

---

   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=16200 Card=1000000
           Bytes=63000000)

   1    0   VIEW (Cost=16200 Card=1000000 Bytes=63000000)
   2    1     WINDOW (SORT) (Cost=16200 Card=1000000 Bytes=48000000)
   3    2       TABLE ACCESS (FULL) OF 'BIGTAB' (TABLE) (Cost=4242 Car
          d=1000000 Bytes=48000000)

Statistics

---

         71  recursive calls
         18  db block gets
      15383  consistent gets
      38531  physical reads
          0  redo size
      14359  bytes sent via SQL*Net to client
        908  bytes received via SQL*Net from client
         38  SQL*Net roundtrips to/from client
          0  sorts (memory)
          1  sorts (disk)
        544  rows processed

SQL> alter session set workarea_size_policy = manual;

Session altered.

Elapsed: 00:00:00.01
SQL> alter session set sort_area_size = 2000000000;

Session altered.

Elapsed: 00:00:00.00
SQL> alter session set hash_area_size = 2000000000;

Session altered.

Elapsed: 00:00:00.01
SQL> select owner, last_ddl_time, object_name, object_type   2 from (
  3 select t1.*, max(last_ddl_time) over (partition by owner) max_time
  4 from bigtab t1
  5 )
  6 where last_ddl_time = max_time;

544 rows selected.

Elapsed: 00:00:03.46

Execution Plan

---

   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4295 Card=1000000
          Bytes=63000000)

   1    0   VIEW (Cost=4295 Card=1000000 Bytes=63000000)
   2    1     WINDOW (SORT) (Cost=4295 Card=1000000 Bytes=48000000)
   3    2       TABLE ACCESS (FULL) OF 'BIGTAB' (TABLE) (Cost=4242 Car
          d=1000000 Bytes=48000000)

Statistics

---

          1  recursive calls
          0  db block gets
      15383  consistent gets
          0  physical reads
          0  redo size
      14455  bytes sent via SQL*Net to client
        908  bytes received via SQL*Net from client
         38  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
        544  rows processed

Received on Sat Feb 02 2008 - 07:48:37 CST