Home » SQL & PL/SQL » SQL & PL/SQL » Time Elapsed
Time Elapsed [message #610286] Wed, 19 March 2014 02:27 Go to next message
mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad
Senior Member
Hi all,

Can you please the below code please i am getting time Elapsed more using Bulk than Using Cursor.

SQL> set lines 10000
SQL> set serveroutput on
SQL> set timing on
SQL> DECLARE 
  2      CURSOR emp_cur 
  3      IS 
  4        SELECT empno, ename, job 
  5        FROM   emp; 
  6  BEGIN 
  7      FOR rec IN emp_cur LOOP 
  8          dbms_output.Put_line ('Empno : ' ||rec.empno); 
  9  
 10          dbms_output.Put_line ('Name  : ' ||rec.ename); 
 11  
 12          dbms_output.Put_line ('Job   : ' ||rec.job); 
 13      END LOOP; 
 14  END; 

   PL/SQL procedure successfully completed.

   Elapsed: 00:00:00.04



Using Bulk..

SQL> DECLARE 
  2      TYPE emp_no_type   IS TABLE OF emp.empno%TYPE; 
  3      TYPE emp_name_type IS TABLE OF emp.ename%TYPE; 
  4      TYPE Job_name_type IS TABLE OF emp.job%TYPE; 
  5       emp_no_tab    emp_no_type; 
  6       emp_name_tab   emp_name_type; 
  7       Job_name_tab  Job_name_type; 
  8  BEGIN 
  9      SELECT empno, 
 10             ename, 
 11             job 
 12      bulk   collect INTO emp_no_tab, emp_name_tab, Job_name_tab
 13      FROM   emp; 
 14  
 15      FOR i IN emp_no_tab.first..emp_no_tab.last LOOP 
 16          dbms_output.Put_line ('Emp Number: '||emp_no_tab(i)); 
 17  
 18          dbms_output.Put_line ('Emp Name: ' ||emp_name_tab(i));
 19  
 20          dbms_output.Put_line ('Emp Job: ' ||Job_name_tab(i)); 
 21      END LOOP; 
 22  END; 
 23  /
    
   PL/SQL procedure successfully completed.

   Elapsed: 00:00:00.07


Using Cursor Elapsed: 00:00:00.04

Using BULK Elapsed: 00:00:00.07
Re: Time Elapsed [message #610289 is a reply to message #610286] Wed, 19 March 2014 02:47 Go to previous messageGo to next message
Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

1/ 0.04 and 0.07 are the same thing, do it 10000 times to see any difference
2/ in 10g and up PL/SQL does a bulk collect behind the scene even if you get row by row.

Re: Time Elapsed [message #610292 is a reply to message #610289] Wed, 19 March 2014 02:54 Go to previous messageGo to next message
mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad
Senior Member
Quote:
1/ 0.04 and 0.07 are the same thing, do it 10000 times to see any difference


Ok Then what is the purpose of "bulk collect" ,Mean so we just use cursor right? Let me know please.
Re: Time Elapsed [message #610293 is a reply to message #610292] Wed, 19 March 2014 03:05 Go to previous messageGo to next message
Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

It is good programming and default PL/SQL behaviour may (and will) change and your application one too and you will come here with a question "why my application times have changed after upgrade?".
It has been implemented in PL/SQL to try to workaround bad programmers.

Re: Time Elapsed [message #610303 is a reply to message #610293] Wed, 19 March 2014 04:52 Go to previous messageGo to next message
vslabs
Messages: 26
Registered: March 2014
Location: Cape Town
Junior Member
Your benchmark is invalid. And severely flawed.

You are failing to ensure consistency ito of I/O performance for each method benchmarked. Which is a very difficult thing to do in a RDBMS.

You are also not only benchmarking cursor performance differences. You are benchmarking network performance (what do you think DBMS_OUTPUT does?). And local display performance (which is impacted by local platform's CPU utilisation).

And seeing that time measurement for this benchmark is in 100th of seconds, the benchmark is not worth the bytes it is written in. It is completely and utterly useless.. as many such so-called "Oracle benchmarks" are.

Besides, the performance difference between a FOR cursor-loop and a BULK COLLECT comes down to context switching. Which has very limited returns on improving performance. The best is no context switching at all. Which means do NOT process cursors using PL/SQL where possible, as that by its very nature is not optimal.
Re: Time Elapsed [message #610335 is a reply to message #610303] Wed, 19 March 2014 08:05 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
Senior Member
vslabs wrote on Wed, 19 March 2014 05:52
Your benchmark is invalid. And severely flawed.


Why is it flawed? Is single row test and bulk load test produce different number of DBMS_OUTPUT.PUT_LINE calls? Is network speed any different during both tests? As you can see time spent on DBMS_OUTPUT.PUT_LINE is same in both tests, so test1 elapsed time - test1 elapsed time zeroes out DBMS_OUTPUT spent time. Now about "Besides, the performance difference between a FOR cursor-loop and a BULK COLLECT comes down to context switching". You'd be surpised but there is almost no context switch difference. And that almost comes from declaring a cursor in first test:

SCOTT@orcl > connect scott@orcl
Enter password: *****
Connected.
SCOTT@orcl > set linesize 132
SCOTT@orcl > col event_unit for a15
SCOTT@orcl > col event_unit_kind for a15
SCOTT@orcl > col event_comment for a30
SCOTT@orcl > alter session set events '10938 trace name context forever, level 33'
  2  /

Session altered.

SCOTT@orcl > DECLARE 
  2      CURSOR emp_cur 
  3      IS 
  4        SELECT empno, ename, job 
  5        FROM   emp; 
  6  BEGIN 
  7      FOR rec IN emp_cur LOOP 
  8        dbms_output.Put_line ('Empno : ' ||rec.empno); 
  9        dbms_output.Put_line ('Name  : ' ||rec.ename); 
 10        dbms_output.Put_line ('Job   : ' ||rec.job); 
 11      END LOOP; 
 12  END; 
 13  /

PL/SQL procedure successfully completed.

SCOTT@orcl > alter session set events '10938 trace name context off'
  2  /

Session altered.

SCOTT@orcl > select  event_seq,
  2          event_unit,
  3          event_unit_kind,
  4          event_comment,
  5          sum(decode(event_comment,'PL/SQL Virtual Machine started', 1))
  6            over(partition by runid order by event_seq) context_switch
  7    from  sys.plsql_trace_events
  8    where runid = (select max(runid) from sys.plsql_trace_events)
  9    order by 1
 10  /

 EVENT_SEQ EVENT_UNIT      EVENT_UNIT_KIND EVENT_COMMENT                  CONTEXT_SWITCH
---------- --------------- --------------- ------------------------------ --------------
         1                                 PL/SQL Trace Tool started
         2                                 Trace flags changed
         3 <anonymous>     ANONYMOUS BLOCK PL/SQL Virtual Machine started              1
         4 <anonymous>     ANONYMOUS BLOCK Procedure Call                              1
         5 <anonymous>     ANONYMOUS BLOCK SELECT EMPNO, ENAME, JOB FROM               1
                                           EMP

         6 <anonymous>     ANONYMOUS BLOCK Return from procedure call                  1
         7 <anonymous>     ANONYMOUS BLOCK SELECT EMPNO, ENAME, JOB FROM               1
                                           EMP


 EVENT_SEQ EVENT_UNIT      EVENT_UNIT_KIND EVENT_COMMENT                  CONTEXT_SWITCH
---------- --------------- --------------- ------------------------------ --------------
         8 <anonymous>     ANONYMOUS BLOCK Package Body Elaborated                     1
         9 DBMS_OUTPUT     PACKAGE BODY    Return from procedure call                  1
        10 <anonymous>     ANONYMOUS BLOCK Procedure Call                              1
        11 DBMS_OUTPUT     PACKAGE BODY    Return from procedure call                  1
        12 <anonymous>     ANONYMOUS BLOCK Procedure Call                              1
        13 DBMS_OUTPUT     PACKAGE BODY    Return from procedure call                  1
        14 <anonymous>     ANONYMOUS BLOCK Procedure Call                              1
        15 DBMS_OUTPUT     PACKAGE BODY    Return from procedure call                  1
        16 <anonymous>     ANONYMOUS BLOCK Procedure Call                              1
        17 DBMS_OUTPUT     PACKAGE BODY    Return from procedure call                  1
        18 <anonymous>     ANONYMOUS BLOCK Procedure Call                              1

 EVENT_SEQ EVENT_UNIT      EVENT_UNIT_KIND EVENT_COMMENT                  CONTEXT_SWITCH
---------- --------------- --------------- ------------------------------ --------------
        19 DBMS_OUTPUT     PACKAGE BODY    Return from procedure call                  1
        20 <anonymous>     ANONYMOUS BLOCK Procedure Call                              1
        21 DBMS_OUTPUT     PACKAGE BODY    Return from procedure call                  1
        22 <anonymous>     ANONYMOUS BLOCK Procedure Call                              1
        23 DBMS_OUTPUT     PACKAGE BODY    Return from procedure call                  1
        24 <anonymous>     ANONYMOUS BLOCK Procedure Call                              1
        25 DBMS_OUTPUT     PACKAGE BODY    Return from procedure call                  1
        26 <anonymous>     ANONYMOUS BLOCK Procedure Call                              1
        27 DBMS_OUTPUT     PACKAGE BODY    Return from procedure call                  1
        28 <anonymous>     ANONYMOUS BLOCK Procedure Call                              1
        29 DBMS_OUTPUT     PACKAGE BODY    Return from procedure call                  1

 EVENT_SEQ EVENT_UNIT      EVENT_UNIT_KIND EVENT_COMMENT                  CONTEXT_SWITCH
---------- --------------- --------------- ------------------------------ --------------
        30 <anonymous>     ANONYMOUS BLOCK Procedure Call                              1
        31 DBMS_OUTPUT     PACKAGE BODY    Return from procedure call                  1
        32 <anonymous>     ANONYMOUS BLOCK Procedure Call                              1
        33 DBMS_OUTPUT     PACKAGE BODY    Return from procedure call                  1
        34 <anonymous>     ANONYMOUS BLOCK Procedure Call                              1
        35 DBMS_OUTPUT     PACKAGE BODY    Return from procedure call                  1
        36 <anonymous>     ANONYMOUS BLOCK Procedure Call                              1
        37 DBMS_OUTPUT     PACKAGE BODY    Return from procedure call                  1
        38 <anonymous>     ANONYMOUS BLOCK Procedure Call                              1
        39 DBMS_OUTPUT     PACKAGE BODY    Return from procedure call                  1
        40 <anonymous>     ANONYMOUS BLOCK Procedure Call                              1

 EVENT_SEQ EVENT_UNIT      EVENT_UNIT_KIND EVENT_COMMENT                  CONTEXT_SWITCH
---------- --------------- --------------- ------------------------------ --------------
        41 DBMS_OUTPUT     PACKAGE BODY    Return from procedure call                  1
        42 <anonymous>     ANONYMOUS BLOCK Procedure Call                              1
        43 DBMS_OUTPUT     PACKAGE BODY    Return from procedure call                  1
        44 <anonymous>     ANONYMOUS BLOCK Procedure Call                              1
        45 DBMS_OUTPUT     PACKAGE BODY    Return from procedure call                  1
        46 <anonymous>     ANONYMOUS BLOCK Procedure Call                              1
        47 DBMS_OUTPUT     PACKAGE BODY    Return from procedure call                  1
        48 <anonymous>     ANONYMOUS BLOCK Procedure Call                              1
        49 DBMS_OUTPUT     PACKAGE BODY    Return from procedure call                  1
        50 <anonymous>     ANONYMOUS BLOCK Procedure Call                              1
        51 DBMS_OUTPUT     PACKAGE BODY    Return from procedure call                  1

 EVENT_SEQ EVENT_UNIT      EVENT_UNIT_KIND EVENT_COMMENT                  CONTEXT_SWITCH
---------- --------------- --------------- ------------------------------ --------------
        52 <anonymous>     ANONYMOUS BLOCK Procedure Call                              1
        53 DBMS_OUTPUT     PACKAGE BODY    Return from procedure call                  1
        54 <anonymous>     ANONYMOUS BLOCK Procedure Call                              1
        55 DBMS_OUTPUT     PACKAGE BODY    Return from procedure call                  1
        56 <anonymous>     ANONYMOUS BLOCK Procedure Call                              1
        57 DBMS_OUTPUT     PACKAGE BODY    Return from procedure call                  1
        58 <anonymous>     ANONYMOUS BLOCK Procedure Call                              1
        59 DBMS_OUTPUT     PACKAGE BODY    Return from procedure call                  1
        60 <anonymous>     ANONYMOUS BLOCK Procedure Call                              1
        61 DBMS_OUTPUT     PACKAGE BODY    Return from procedure call                  1
        62 <anonymous>     ANONYMOUS BLOCK Procedure Call                              1

 EVENT_SEQ EVENT_UNIT      EVENT_UNIT_KIND EVENT_COMMENT                  CONTEXT_SWITCH
---------- --------------- --------------- ------------------------------ --------------
        63 DBMS_OUTPUT     PACKAGE BODY    Return from procedure call                  1
        64 <anonymous>     ANONYMOUS BLOCK Procedure Call                              1
        65 DBMS_OUTPUT     PACKAGE BODY    Return from procedure call                  1
        66 <anonymous>     ANONYMOUS BLOCK Procedure Call                              1
        67 DBMS_OUTPUT     PACKAGE BODY    Return from procedure call                  1
        68 <anonymous>     ANONYMOUS BLOCK Procedure Call                              1
        69 DBMS_OUTPUT     PACKAGE BODY    Return from procedure call                  1
        70 <anonymous>     ANONYMOUS BLOCK Procedure Call                              1
        71 DBMS_OUTPUT     PACKAGE BODY    Return from procedure call                  1
        72 <anonymous>     ANONYMOUS BLOCK Procedure Call                              1
        73 DBMS_OUTPUT     PACKAGE BODY    Return from procedure call                  1

 EVENT_SEQ EVENT_UNIT      EVENT_UNIT_KIND EVENT_COMMENT                  CONTEXT_SWITCH
---------- --------------- --------------- ------------------------------ --------------
        74 <anonymous>     ANONYMOUS BLOCK Procedure Call                              1
        75 DBMS_OUTPUT     PACKAGE BODY    Return from procedure call                  1
        76 <anonymous>     ANONYMOUS BLOCK Procedure Call                              1
        77 DBMS_OUTPUT     PACKAGE BODY    Return from procedure call                  1
        78 <anonymous>     ANONYMOUS BLOCK Procedure Call                              1
        79 DBMS_OUTPUT     PACKAGE BODY    Return from procedure call                  1
        80 <anonymous>     ANONYMOUS BLOCK Procedure Call                              1
        81 DBMS_OUTPUT     PACKAGE BODY    Return from procedure call                  1
        82 <anonymous>     ANONYMOUS BLOCK Procedure Call                              1
        83 DBMS_OUTPUT     PACKAGE BODY    Return from procedure call                  1
        84 <anonymous>     ANONYMOUS BLOCK Procedure Call                              1

 EVENT_SEQ EVENT_UNIT      EVENT_UNIT_KIND EVENT_COMMENT                  CONTEXT_SWITCH
---------- --------------- --------------- ------------------------------ --------------
        85 DBMS_OUTPUT     PACKAGE BODY    Return from procedure call                  1
        86 <anonymous>     ANONYMOUS BLOCK Procedure Call                              1
        87 DBMS_OUTPUT     PACKAGE BODY    Return from procedure call                  1
        88 <anonymous>     ANONYMOUS BLOCK Procedure Call                              1
        89 DBMS_OUTPUT     PACKAGE BODY    Return from procedure call                  1
        90 <anonymous>     ANONYMOUS BLOCK Procedure Call                              1
        91 DBMS_OUTPUT     PACKAGE BODY    Return from procedure call                  1
        92 <anonymous>     ANONYMOUS BLOCK Procedure Call                              1
        93 DBMS_OUTPUT     PACKAGE BODY    Return from procedure call                  1
        94                                 PL/SQL Virtual Machine stopped              1

94 rows selected.

SCOTT@orcl > connect scott@orcl
Enter password: *****
Connected.
SCOTT@orcl > alter session set events '10938 trace name context forever, level 33'
  2  /

Session altered.

SCOTT@orcl > DECLARE 
  2      TYPE emp_no_type   IS TABLE OF emp.empno%TYPE; 
  3      TYPE emp_name_type IS TABLE OF emp.ename%TYPE; 
  4      TYPE Job_name_type IS TABLE OF emp.job%TYPE; 
  5      emp_no_tab    emp_no_type; 
  6      emp_name_tab   emp_name_type; 
  7      Job_name_tab  Job_name_type; 
  8  BEGIN 
  9      SELECT empno, 
 10             ename, 
 11             job 
 12        bulk   collect INTO emp_no_tab, emp_name_tab, Job_name_tab
 13        FROM   emp; 
 14        FOR i IN emp_no_tab.first..emp_no_tab.last LOOP 
 15          dbms_output.Put_line ('Emp Number: '||emp_no_tab(i)); 
 16          dbms_output.Put_line ('Emp Name: ' ||emp_name_tab(i));
 17          dbms_output.Put_line ('Emp Job: ' ||Job_name_tab(i)); 
 18        END LOOP; 
 19  END; 
 20  /

PL/SQL procedure successfully completed.

SCOTT@orcl > alter session set events '10938 trace name context off'
  2  /

Session altered.

SCOTT@orcl > select  event_seq,
  2          event_unit,
  3          event_unit_kind,
  4          event_comment,
  5          sum(decode(event_comment,'PL/SQL Virtual Machine started', 1))
  6            over(partition by runid order by event_seq) context_switch
  7    from  sys.plsql_trace_events
  8    where runid = (select max(runid) from sys.plsql_trace_events)
  9    order by 1
 10  /

 EVENT_SEQ EVENT_UNIT      EVENT_UNIT_KIND EVENT_COMMENT                  CONTEXT_SWITCH
---------- --------------- --------------- ------------------------------ --------------
         1                                 PL/SQL Trace Tool started
         2                                 Trace flags changed
         3 <anonymous>     ANONYMOUS BLOCK PL/SQL Virtual Machine started              1
         4 <anonymous>     ANONYMOUS BLOCK SELECT EMPNO, ENAME, JOB FROM               1
                                           EMP

         5 <anonymous>     ANONYMOUS BLOCK PL/SQL Internal Call                        1
         6 <anonymous>     ANONYMOUS BLOCK PL/SQL Internal Call                        1
         7 <anonymous>     ANONYMOUS BLOCK Package Body Elaborated                     1
         8 DBMS_OUTPUT     PACKAGE BODY    Return from procedure call                  1
         9 <anonymous>     ANONYMOUS BLOCK Procedure Call                              1

 EVENT_SEQ EVENT_UNIT      EVENT_UNIT_KIND EVENT_COMMENT                  CONTEXT_SWITCH
---------- --------------- --------------- ------------------------------ --------------
        10 DBMS_OUTPUT     PACKAGE BODY    Return from procedure call                  1
        11 <anonymous>     ANONYMOUS BLOCK Procedure Call                              1
        12 DBMS_OUTPUT     PACKAGE BODY    Return from procedure call                  1
        13 <anonymous>     ANONYMOUS BLOCK Procedure Call                              1
        14 DBMS_OUTPUT     PACKAGE BODY    Return from procedure call                  1
        15 <anonymous>     ANONYMOUS BLOCK Procedure Call                              1
        16 DBMS_OUTPUT     PACKAGE BODY    Return from procedure call                  1
        17 <anonymous>     ANONYMOUS BLOCK Procedure Call                              1
        18 DBMS_OUTPUT     PACKAGE BODY    Return from procedure call                  1
        19 <anonymous>     ANONYMOUS BLOCK Procedure Call                              1
        20 DBMS_OUTPUT     PACKAGE BODY    Return from procedure call                  1

 EVENT_SEQ EVENT_UNIT      EVENT_UNIT_KIND EVENT_COMMENT                  CONTEXT_SWITCH
---------- --------------- --------------- ------------------------------ --------------
        21 <anonymous>     ANONYMOUS BLOCK Procedure Call                              1
        22 DBMS_OUTPUT     PACKAGE BODY    Return from procedure call                  1
        23 <anonymous>     ANONYMOUS BLOCK Procedure Call                              1
        24 DBMS_OUTPUT     PACKAGE BODY    Return from procedure call                  1
        25 <anonymous>     ANONYMOUS BLOCK Procedure Call                              1
        26 DBMS_OUTPUT     PACKAGE BODY    Return from procedure call                  1
        27 <anonymous>     ANONYMOUS BLOCK Procedure Call                              1
        28 DBMS_OUTPUT     PACKAGE BODY    Return from procedure call                  1
        29 <anonymous>     ANONYMOUS BLOCK Procedure Call                              1
        30 DBMS_OUTPUT     PACKAGE BODY    Return from procedure call                  1
        31 <anonymous>     ANONYMOUS BLOCK Procedure Call                              1

 EVENT_SEQ EVENT_UNIT      EVENT_UNIT_KIND EVENT_COMMENT                  CONTEXT_SWITCH
---------- --------------- --------------- ------------------------------ --------------
        32 DBMS_OUTPUT     PACKAGE BODY    Return from procedure call                  1
        33 <anonymous>     ANONYMOUS BLOCK Procedure Call                              1
        34 DBMS_OUTPUT     PACKAGE BODY    Return from procedure call                  1
        35 <anonymous>     ANONYMOUS BLOCK Procedure Call                              1
        36 DBMS_OUTPUT     PACKAGE BODY    Return from procedure call                  1
        37 <anonymous>     ANONYMOUS BLOCK Procedure Call                              1
        38 DBMS_OUTPUT     PACKAGE BODY    Return from procedure call                  1
        39 <anonymous>     ANONYMOUS BLOCK Procedure Call                              1
        40 DBMS_OUTPUT     PACKAGE BODY    Return from procedure call                  1
        41 <anonymous>     ANONYMOUS BLOCK Procedure Call                              1
        42 DBMS_OUTPUT     PACKAGE BODY    Return from procedure call                  1

 EVENT_SEQ EVENT_UNIT      EVENT_UNIT_KIND EVENT_COMMENT                  CONTEXT_SWITCH
---------- --------------- --------------- ------------------------------ --------------
        43 <anonymous>     ANONYMOUS BLOCK Procedure Call                              1
        44 DBMS_OUTPUT     PACKAGE BODY    Return from procedure call                  1
        45 <anonymous>     ANONYMOUS BLOCK Procedure Call                              1
        46 DBMS_OUTPUT     PACKAGE BODY    Return from procedure call                  1
        47 <anonymous>     ANONYMOUS BLOCK Procedure Call                              1
        48 DBMS_OUTPUT     PACKAGE BODY    Return from procedure call                  1
        49 <anonymous>     ANONYMOUS BLOCK Procedure Call                              1
        50 DBMS_OUTPUT     PACKAGE BODY    Return from procedure call                  1
        51 <anonymous>     ANONYMOUS BLOCK Procedure Call                              1
        52 DBMS_OUTPUT     PACKAGE BODY    Return from procedure call                  1
        53 <anonymous>     ANONYMOUS BLOCK Procedure Call                              1

 EVENT_SEQ EVENT_UNIT      EVENT_UNIT_KIND EVENT_COMMENT                  CONTEXT_SWITCH
---------- --------------- --------------- ------------------------------ --------------
        54 DBMS_OUTPUT     PACKAGE BODY    Return from procedure call                  1
        55 <anonymous>     ANONYMOUS BLOCK Procedure Call                              1
        56 DBMS_OUTPUT     PACKAGE BODY    Return from procedure call                  1
        57 <anonymous>     ANONYMOUS BLOCK Procedure Call                              1
        58 DBMS_OUTPUT     PACKAGE BODY    Return from procedure call                  1
        59 <anonymous>     ANONYMOUS BLOCK Procedure Call                              1
        60 DBMS_OUTPUT     PACKAGE BODY    Return from procedure call                  1
        61 <anonymous>     ANONYMOUS BLOCK Procedure Call                              1
        62 DBMS_OUTPUT     PACKAGE BODY    Return from procedure call                  1
        63 <anonymous>     ANONYMOUS BLOCK Procedure Call                              1
        64 DBMS_OUTPUT     PACKAGE BODY    Return from procedure call                  1

 EVENT_SEQ EVENT_UNIT      EVENT_UNIT_KIND EVENT_COMMENT                  CONTEXT_SWITCH
---------- --------------- --------------- ------------------------------ --------------
        65 <anonymous>     ANONYMOUS BLOCK Procedure Call                              1
        66 DBMS_OUTPUT     PACKAGE BODY    Return from procedure call                  1
        67 <anonymous>     ANONYMOUS BLOCK Procedure Call                              1
        68 DBMS_OUTPUT     PACKAGE BODY    Return from procedure call                  1
        69 <anonymous>     ANONYMOUS BLOCK Procedure Call                              1
        70 DBMS_OUTPUT     PACKAGE BODY    Return from procedure call                  1
        71 <anonymous>     ANONYMOUS BLOCK Procedure Call                              1
        72 DBMS_OUTPUT     PACKAGE BODY    Return from procedure call                  1
        73 <anonymous>     ANONYMOUS BLOCK Procedure Call                              1
        74 DBMS_OUTPUT     PACKAGE BODY    Return from procedure call                  1
        75 <anonymous>     ANONYMOUS BLOCK Procedure Call                              1

 EVENT_SEQ EVENT_UNIT      EVENT_UNIT_KIND EVENT_COMMENT                  CONTEXT_SWITCH
---------- --------------- --------------- ------------------------------ --------------
        76 DBMS_OUTPUT     PACKAGE BODY    Return from procedure call                  1
        77 <anonymous>     ANONYMOUS BLOCK Procedure Call                              1
        78 DBMS_OUTPUT     PACKAGE BODY    Return from procedure call                  1
        79 <anonymous>     ANONYMOUS BLOCK Procedure Call                              1
        80 DBMS_OUTPUT     PACKAGE BODY    Return from procedure call                  1
        81 <anonymous>     ANONYMOUS BLOCK Procedure Call                              1
        82 DBMS_OUTPUT     PACKAGE BODY    Return from procedure call                  1
        83 <anonymous>     ANONYMOUS BLOCK Procedure Call                              1
        84 DBMS_OUTPUT     PACKAGE BODY    Return from procedure call                  1
        85 <anonymous>     ANONYMOUS BLOCK Procedure Call                              1
        86 DBMS_OUTPUT     PACKAGE BODY    Return from procedure call                  1

 EVENT_SEQ EVENT_UNIT      EVENT_UNIT_KIND EVENT_COMMENT                  CONTEXT_SWITCH
---------- --------------- --------------- ------------------------------ --------------
        87 <anonymous>     ANONYMOUS BLOCK Procedure Call                              1
        88 DBMS_OUTPUT     PACKAGE BODY    Return from procedure call                  1
        89 <anonymous>     ANONYMOUS BLOCK Procedure Call                              1
        90 DBMS_OUTPUT     PACKAGE BODY    Return from procedure call                  1
        91 <anonymous>     ANONYMOUS BLOCK Procedure Call                              1
        92 DBMS_OUTPUT     PACKAGE BODY    Return from procedure call                  1
        93                                 PL/SQL Virtual Machine stopped              1

93 rows selected.

SCOTT@orcl > 


But it is besides the point. Elapsed time diff would include context swich difference if there would be one. This is a basic benchmark. Normally that's what most people do as a first cut. If it shows significant (and what is significant is for person running benchmark to decide) difference we might want to drill down and find out where that difference is coming from.

SY.
Re: Time Elapsed [message #610425 is a reply to message #610335] Thu, 20 March 2014 00:00 Go to previous messageGo to next message
vslabs
Messages: 26
Registered: March 2014
Location: Cape Town
Junior Member
The main reason why it is flawed is that it assumes that the I/O performance will be the same in both cases.

As for context switching - the point I tried to make was that the difference boils down to explicit context switching (you control the bulk fetching) versus what PL/SQL does behind the scenes implicitly. As PL/SQL's optimiser uses bulk fetching for implicit cursor processing in FOR loop, the benchmark is not a very sensible look at explicitly controlled context switching versus implicitly managed context switching.

The reason why it is not sensible - reduction in context switching has fast diminishing returns. A 1000 rows cursor fetch (which needs a 1000 context switches for single row fetch/switch), has only a 10 context switches with a bulk fetch of a 100 (the default of a FOR cursor fetch loop). That is a 99% performance improvement over a single row fetch/switch. Doing an explicit 1000 row fetch for only 1 context switch, is only 1% "better". A senseless comparison where performance gains are practically non existent - as your code sample also showed.
Re: Time Elapsed [message #610426 is a reply to message #610425] Thu, 20 March 2014 00:24 Go to previous messageGo to next message
vslabs
Messages: 26
Registered: March 2014
Location: Cape Town
Junior Member
Some more comments from the peanut gallery.

I have a major issue with most benchmarks done by Oracle developers. As they will do a non-sensible benchmark (e.g. disregard PIO vs LIO) and then draw the erroneous conclusion that one cursor construct is "better" than another (given that it is 0.01 sec faster). It is then religiously used in all their code in the believe that they are crafting optimal code. And this "fact" is then proclaimed to others, who in their ignorance, believes it.

The Case Of select count(1) being Faster Than select count(*). As everyone can "see" the former is faster with this type of benchmark and "evidence"...

SQL> set timing on
SQL> select count(*) from dba_objects;

  COUNT(*)
----------
    176830

Elapsed: 00:00:03.46
SQL> 
SQL> select count(1) from dba_objects;

  COUNT(1)
----------
    176830

Elapsed: 00:00:01.20
SQL> 


Bluntly put. Most benchmarks done by Oracle developers are flawed and invalid - with not the vaguest clue being had of what the basic and fundamental requirements are when benchmarking.


Re: Time Elapsed [message #610460 is a reply to message #610426] Thu, 20 March 2014 05:01 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
Senior Member
OK. Down from theory to trenches. So we have two pieces of code. We want to find out which runs faster (end-to-end). We don't care at this point if, for example, one of them uses more resources thus affecting other users, etc. What would be no-flaw benchmark?

SY.

[Updated on: Thu, 20 March 2014 05:02]

Report message to a moderator

Re: Time Elapsed [message #610748 is a reply to message #610460] Mon, 24 March 2014 01:49 Go to previous message
vslabs
Messages: 26
Registered: March 2014
Location: Cape Town
Junior Member
Question is - why benchmark? How does that achieve the goal in a better/superior way than another method?

If one understand the moving parts involved, one can
a) evaluate each moving part technically
b) compare moving parts to one another
c) make an informed decision as to which fits the requirements the best

For example, an INDEX hint (hardcoded for a specific index name) could provide better performance, than no hint at all. There are however risks using that hint (index name changes: a better index comes along, table structure is altered, etc). There is a reason why the CBO discard using the index - and if an error, one should address the cause and not the symptom.

So benchmarking alone does not really and comprehensively provide an answer to which piece of code is the best. In most cases I would argue, it actually tells very little.


Previous Topic: update rows based on column values
Next Topic: How to display Last Day In Week Indicate As Y or N as input is start date and end date
Goto Forum:
  


Current Time: Tue Apr 23 18:05:00 CDT 2024