| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Straight SQL always put perform PL/SQL?
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
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------- | 0 | UPDATE STATEMENT | | 374K| 36M| 1509 (4)| 00:00:19 | | 1 | UPDATE | TEST1 | | | | |
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
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | UPDATE STATEMENT | | 49782 | 777K| 38029 (2)| 00:07:37 | | 1 | UPDATE | TEST | | | | |
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=>'forall 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
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;
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
 |
 |