Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Straight SQL always put perform PL/SQL?

Re: Straight SQL always put perform PL/SQL?

From: <fitzjarrell_at_cox.net>
Date: Tue, 18 Dec 2007 09:31:51 -0800 (PST)
Message-ID: <b93caacd-4b6a-4b25-890f-f172cdfcc746@18g2000hsf.googlegroups.com>


Comments embedded.

On Dec 17, 3:48 pm, dshprope..._at_gmail.com wrote:
> Thanks very much for your respond. I read the "write consistency' as
> well. I think it is the opertimizer somehow messed up, the cursor
> knows to use the index, while update statement choose a full table
> scan on a big table.
>
> To demonstrate it, I re-create the simplified situation of our case.
> =====================================
> create table test (id1 number,id2 number,id3 char(1), col1 char(2000))
> pctfree 90;
> (this is to mimic creating a huge table which makes full table scan
> expensive)

Yet the db_file_multiblock_read_count setting, coupled with db_block_size, can make those table scans appear to be less expensive. Additionally the optimizer_index_cost_adj setting can affect how your indexes are viewed from a cost perspective; the default value of 100 sets the relative cost of an index scan to equal the cost of a table scan. Simply making an artificially large table under the guise of making a table scan appear expensive is a misunderstanding on your part.

> declare
> v_number number :=1;
> begin
> loop
> insert into test values (v_number, v_number + 1,'n','a');
> v_number := v_number+1;
> exit when v_number > 1000000;
> end loop;
> commit;
> end;
>
> create table test1 as select * from test where id1 < 400000;
> update test1 set col1='b';
> update test set id3='Y' where id1 < 500;
> commit;
>
> create unique index test_ind on test(id1,id2);
> create bitmap index test_idx3 on test(id3);

What purpose does this serve in your example? You provide no proof this index is even used.

> create unique index test1_ind on test1(id1,id2);
>
> exec dbms_stats.gather_table_stats('scott', 'TEST', method_opt=>'for
> all indexed
> columns', cascade=>TRUE);
> exec dbms_stats.gather_table_stats('scott', 'TEST1', method_opt=>'for
> all indexed
> columns', cascade=>TRUE);
>
> SQL> declare
> 2 v_count number;
> 3 CURSOR test_cur IS
> 4 select test.id1,test.id2,test.col1 from test, test1
> 5 where test.id1=test1.id1
> 6 and test.id2 = test1.id2 and test.id3='Y';
> 7 BEGIN
> 8 FOR test_curs IN test_cur
> 9 LOOP
> 10 update test
> 11 set col1 = test_curs.col1
> 12 where test.id1 = test_curs.id1
> 13 and test.id2 = test_curs.id2
> 14 and test.id3='Y';
> 15 END LOOP;
> 16 commit;
> 17 END;
> 18 /
>
> PL/SQL procedure successfully completed.
>
> Elapsed: 00:00:00.20
>

No profiler information or reports, nothing to indicate what, exactly, Oracle is doing with this block of PL/SQL code. Also, no spool file from your session where all of this code was executed.

> SQL> update test a
> 2 set a.col1 = (select col1
> 3 from test1 b
> 4 where a.id1=b.id1
> 5 and a.id2 = b.id2 and a.id3='Y')
> 6 where exists
> 7 (select 1 from test1 b where a.id1=b.id1
> 8 and a.id2 = b.id2 and a.id3='Y');
>
> 499 rows updated.
> Elapsed: 00:01:27.75
>

No explain plan, no statistics, nothing to indicate the access path Oracle chose to take. And the statement above doesn't really equate to the statement in your cursor in the PL/SQL block.

> PL/SQL is much faster.

No, all you've shown is a WHERE EXISTS statement is less efficient than a join.

>

Let's look at a spool file generated by a complete session using your code (modified as I don't have unlimited space to consume for a 'play' table):

SQL>
SQL> alter session set optimizer_index_cost_adj = 15;

Session altered.

Elapsed: 00:00:00.00
SQL>
SQL> -- (this is to mimic creating a huge table which makes full table scan expensive)
SQL> create table test (id1 number,id2 number,id3 char(1), col1 char(100))
  2 pctfree 90;

Table created.

Elapsed: 00:00:00.04
SQL>
SQL> declare
  2 v_number number :=1;
  3 begin
  4 loop
  5 insert into test values (v_number, v_number + 1,'n','a');   6 v_number := v_number+1;
  7 exit when v_number > 1000000;
  8 end loop;
  9 commit;
 10 end;
 11 /

PL/SQL procedure successfully completed.

Elapsed: 00:06:13.11

SQL>
SQL>
SQL> create table test1 as select * from test where id1 < 400000;

Table created.

Elapsed: 00:02:12.14
SQL> update test1 set col1='b';

399999 rows updated.

Elapsed: 00:01:35.03

Execution Plan



Plan hash value:
4001801719
| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)|
Time     |
----------------------------------------------------------------------------
|   0 | UPDATE STATEMENT   |       |   374K|    36M|  1509   (4)|
00:00:19 |
|   1 |  UPDATE            | TEST1 |       |       |
|          |

| 2 | TABLE ACCESS FULL| TEST1 | 374K| 36M| 1509 (4)| 00:00:19 |

Note


Statistics


       3219 recursive
calls

     444181 db block
gets

     395570 consistent
gets

       6555 physical
reads
  209575360 redo
size

        404 bytes sent via SQL*Net to
client

        309 bytes received via SQL*Net from client

          3 SQL*Net roundtrips to/from
client

          2 sorts
(memory)

          0 sorts
(disk)

     399999 rows
processed

SQL> update test set id3='Y' where id1 < 500;

499 rows updated.

Elapsed: 00:00:58.29

Execution Plan



Plan hash value:
3859524075
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)|
Time     |
---------------------------------------------------------------------------
|   0 | UPDATE STATEMENT   |      | 49782 |   777K| 38029   (2)|
00:07:37 |
|   1 |  UPDATE            | TEST |       |       |
|          |

|* 2 | TABLE ACCESS FULL| TEST | 49782 | 777K| 38029 (2)| 00:07:37 |

Predicate Information (identified by operation id):


   2 -
filter("ID1"<500)

Note


Statistics


          6 recursive
calls

         89 db block
gets

     167154 consistent
gets

     157957 physical
reads

      58468 redo
size

        404 bytes sent via SQL*Net to
client

        323 bytes received via SQL*Net from client

          3 SQL*Net roundtrips to/from
client

          2 sorts
(memory)

          0 sorts
(disk)

        499 rows
processed

SQL> commit;

Commit complete.

Elapsed: 00:00:00.04

SQL>
SQL>
SQL> create unique index test_ind on test(id1,id2);

Index created.

Elapsed: 00:01:09.95
SQL> create bitmap index test_idx3 on test(id3);

Index created.

Elapsed: 00:00:43.11
SQL> create unique index test1_ind on test1(id1,id2);

Index created.

Elapsed: 00:00:07.43

SQL>
SQL>
SQL> exec dbms_stats.gather_table_stats(user, 'TEST', method_opt=>'for
all indexed columns', cascade=>TRUE);

PL/SQL procedure successfully completed.

Elapsed: 00:03:10.53
SQL> exec dbms_stats.gather_table_stats(user, 'TEST1', method_opt=>'for all indexed columns', cascade=>TRUE);

PL/SQL procedure successfully completed.

Elapsed: 00:00:14.97

SQL>
SQL> --
SQL> -- Modified the cursor to return values from the test1 table
SQL> --
SQL> declare

  2 v_count number;
  3 CURSOR test_cur IS
  4 select test1.id1,test1.id2,test1.col1 from test, test1   5 where test1.id1=test.id1
  6 and test1.id2 = test.id2 and test.id3='Y';   7 BEGIN
  8 FOR test_curs IN test_cur
  9 LOOP
 10  	  update test
 11  	  set col1 = test_curs.col1
 12  	  where test.id1 = test_curs.id1
 13  	    and test.id2 = test_curs.id2
 14  	    and test.id3='Y';
 15  	END LOOP;

 16 commit;
 17 END;
 18 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.35

--
-- Yes, it's fast, but SQL*Plus is faster if you write the correct
statement
--

SQL>
SQL> update test a
  2  set a.col1 = (select col1
  3  	     from test1 b
  4  	     where a.id1=b.id1
  5  	     and a.id2 = b.id2 and a.id3='Y')
  6  where (a.id1, a.id2) in
  7  (select b.id1, b.id2 from test1 b where a.id1=b.id1
  8  	     and a.id2 = b.id2 and a.id3='Y');

499 rows updated.

Elapsed: 00:00:00.10

--
-- Gee, my SQL executed in 1/3 of the time of the PL/SQL,
-- because now we're comparing apples to apples, essentially.
--

Execution Plan
----------------------------------------------------------
Plan hash value:
3792759269

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

| Id  | Operation                      | Name      | Rows  | Bytes |
Cost (%CPU)
| Time
|

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

|   0 | UPDATE STATEMENT               |           |   145 | 17690
|    88   (2)
| 00:00:02
|

|   1 |  UPDATE                        | TEST      |       |
|
|
|

|   2 |   NESTED LOOPS                 |           |   145 | 17690
|    88   (2)
| 00:00:02
|

|   3 |    TABLE ACCESS BY INDEX ROWID | TEST      |   362 | 40544
|    33   (0)
| 00:00:01
|

|   4 |     BITMAP CONVERSION TO ROWIDS|           |       |
|
|
|

|*  5 |      BITMAP INDEX SINGLE VALUE | TEST_IDX3 |       |
|
|
|

|*  6 |    INDEX UNIQUE SCAN           | TEST1_IND |     1 |    10
|     1   (0)
| 00:00:01
|

|*  7 |   FILTER                       |           |       |
|
|
|

|   8 |    TABLE ACCESS BY INDEX ROWID | TEST1     |     1 |   112
|     1   (0)
| 00:00:01
|

|*  9 |     INDEX UNIQUE SCAN          | TEST1_IND |     1 |
|     1   (0)
| 00:00:01
|

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


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

   5 -
access("A"."ID3"='Y')
   6 - access("A"."ID1"="B"."ID1" AND
"A"."ID2"="B"."ID2")
   7 -
filter(:B1='Y')
   9 - access("B"."ID1"=:B1 AND
"B"."ID2"=:B2)


Statistics
----------------------------------------------------------
          1  recursive
calls
        105  db block
gets
       2582  consistent
gets
          0  physical
reads
     162900  redo
size
        402  bytes sent via SQL*Net to
client
        505  bytes received via SQL*Net from
client
          3  SQL*Net roundtrips to/from
client
          1  sorts
(memory)
          0  sorts
(disk)
        499  rows
processed

--
-- The WHERE EXISTS syntax here is woefully inefficient, as  you've
proven.
-- And that's all you've proven, really.
--

SQL>
SQL> -- update test a
SQL> -- set a.col1 = (select col1
SQL> -- from test1 b
SQL> -- where a.id1=b.id1
SQL> -- and a.id2 = b.id2 and a.id3='Y')
SQL> -- where exists
SQL> -- (select 1 from test1 b where a.id1=b.id1
SQL> -- and a.id2 = b.id2 and a.id3='Y');
SQL>
SQL> spool off


> Thanks very much for your help.
>
>
>
>
>
You really need to know what you're proving when you make such statements. And I've proven you wrong on your contention that PL/SQL is faster than SQL. David Fitzjarrell
Received on Tue Dec 18 2007 - 11:31:51 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US