1 2 Previous Next 18 Replies Latest reply: Sep 11, 2008 3:12 PM by MichaelS RSS

    How to find out the execution time of a sql inside a function

    532815
      Hi All,

      I am writing one function. There is only one IN parameter. In that parameter, i will pass one SQL select statement. And I want the function to return the exact execution time of that SQL statement.

      CREATE OR REPLACE FUNCTION function_name (p_sql IN VARCHAR2)
      RETURN NUMBER
      IS
      exec_time NUMBER;
      BEGIN
      --Calculate the execution time for the incoming sql statement.
      RETURN exec_time;
      END function_name;
      /
        • 1. Re: How to find out the execution time of a sql inside a function
          Gurjas
          You can use "set timing on" to get the execution time of the query. like
          SQL> set timing on
          SQL> select * from emp;
          
               EMPNO ENAME
          ---------- ----------
                7654 JONES
                7698 BLAKE
                7782 CLARK
                7788 SCOTT
                7839 KING
                7844 TURNER
                7876 ADAMS
                7900 JAMES
                7902 FORD
                7934 MILLER5
                7936 MILLER7
          
          11 rows selected.
          
          Elapsed: 00:00:00.04 
          SQL> 
          Regards
          Singh
          • 2. Re: How to find out the execution time of a sql inside a function
            BluShadow
            DebadattaKar wrote:
            Hi All,

            I am writing one function. There is only one IN parameter. In that parameter, i will pass one SQL select statement. And I want the function to return the exact execution time of that SQL statement.

            CREATE OR REPLACE FUNCTION function_name (p_sql IN VARCHAR2)
            RETURN NUMBER
            IS
            exec_time NUMBER;
            BEGIN
            --Calculate the execution time for the incoming sql statement.
            RETURN exec_time;
            END function_name;
            /
            The problem is that to execute the query in a function it has to be able to select data into something and if the SQL is dynamic you're not going to know what that data is. So you need to wrap it in something you know like doing a count(*) to ensure it selects all the data...
            SQL> ed
            Wrote file afiedt.buf
            
              1  CREATE OR REPLACE FUNCTION function_name (p_sql IN VARCHAR2)
              2  RETURN NUMBER
              3  IS
              4  exec_time DATE;
              5  v_cnt NUMBER;
              6  BEGIN
              7    exec_time := sysdate;
              8    EXECUTE IMMEDIATE ('SELECT COUNT(*) FROM ('||p_sql||')');
              9    RETURN (sysdate-exec_time)*(24*60*60);
             10* END function_name;
            SQL> /
            
            Function created.
            
            SQL> var num_secs number;
            SQL> exec :num_secs := function_name('select * from emp');
            
            PL/SQL procedure successfully completed.
            
            SQL> print num_secs;
            
              NUM_SECS
            ----------
                     0
            
            SQL>
            • 3. Re: How to find out the execution time of a sql inside a function
              532815
              What you have mentioned is right. But my requirement is something different. I will call a function with that select statement as a parameter passed to it like below and it will return 4 millisecond to me like below:

              declare
              exec_time number;
              begin
              exec_time:=function)name('select * from emp');
              dbms_output.put_line('Execution time is : '||exec_time);
              end;
              /
              • 4. Re: How to find out the execution time of a sql inside a function
                BluShadow
                Singh wrote:
                You can use "set timing on" to get the execution time of the query. like
                Not inside a function you can't. That's a SQL*Plus command.
                • 5. Re: How to find out the execution time of a sql inside a function
                  BluShadow
                  DebadattaKar wrote:
                  What you have mentioned is right. But my requirement is something different. I will call a function with that select statement as a parameter passed to it like below and it will return 4 millisecond to me like below:
                  If you're really that bothered about queries that take less than 1 second then you can still use the same as I've shown you, but you'll need to use timestamps rather than dates to get the finer granularity of time.

                  Why exactly are you wanting to do this? Seems a little odd as to determine the time a query takes to execute you actually have to execute it anyway.
                  • 7. Re: How to find out the execution time of a sql inside a function
                    316280
                    I think you're going into a wrong direction as such approach would produce unpredicable and incorrect results as you're dealing with a server application (Oracle server). It serves many requests (queries) concurrently so measuring execution time of a particular SQL inside a stored procedure would not provide an accurate result.
                    Have a look into Oracle built-in features like DBMS_PROFILER. It has nice integration with developer tools like TOAD or PL/SQL Developer (you can get profiler results on your own because they are kept in special tables on the database). It may be it satisfies your requirements.

                    If you're still looking for a custom measurement approach - an easiest way would be to add something like this to your stored proc:

                    declare
                    vStart timestamp := systimestamp;
                    vEnd timestamp;
                    begin
                    -- do your magic here
                    vEnd := systimestamp;
                    -- return result here
                    return vEnd - vStart;
                    end;

                    Regards,
                    • 8. Re: How to find out the execution time of a sql inside a function
                      Keith Jamieson
                      You can get it in 100ths of a second using dbms_utility.get_time;
                      declare
                      v_exec_time NUMBER;
                      v_start_time number;
                      v_end_time number;
                      v_variable varchar2(100);
                      BEGIN
                      v_start_time := dbms_utility.get_time;
                      --Calculate the execution time for the incoming sql statement.
                      select count(*) into v_variable
                      from all_objects;
                      v_end_time := dbms_utility.get_time;
                      v_exec_time := (v_end_time-v_start_time)/100;
                      dbms_output.put_line( v_exec_time);
                      END;
                      • 9. Re: How to find out the execution time of a sql inside a function
                        BluShadow
                        Actually, I made a mistake. I forgot to query the count back into the variable which is necessary to determine the time correctly...
                        SQL> ed
                        Wrote file afiedt.buf
                        
                          1  CREATE OR REPLACE FUNCTION function_name (p_sql IN VARCHAR2)
                          2  RETURN NUMBER
                          3  IS
                          4  exec_time DATE;
                          5  v_cnt NUMBER;
                          6  BEGIN
                          7    exec_time := SYSDATE;
                          8    EXECUTE IMMEDIATE ('select count(*) from ('||p_sql||')') into v_cnt;
                          9    RETURN (sysdate-exec_time)*(24*60*60);
                         10* END function_name;
                        SQL> /
                        
                        Function created.
                        
                        SQL> exec :exec_time := function_name('select count(*) from large_text where instr(text,''FRED'') > 1');
                        
                        PL/SQL procedure successfully completed.
                        
                        SQL> print exec_time;
                        
                         EXEC_TIME
                        ----------
                                 9
                        
                        SQL>
                        ;)
                        • 10. Re: How to find out the execution time of a sql inside a function
                          Keith Jamieson
                          duplicate (due to forum issue)

                          Edited by: Keith Jamieson on Sep 8, 2008 1:30 PM
                          • 11. Re: How to find out the execution time of a sql inside a function
                            BluShadow
                            Ivan R. wrote:
                            I think you're going into a wrong direction as such approach would produce unpredicable and incorrect results as you're dealing with a server application (Oracle server). It serves many requests (queries) concurrently so measuring execution time of a particular SQL inside a stored procedure would not provide an accurate result.
                            Agreed, and not to mention the caching of datablocks or hard/soft parsing of queries etc. etc.

                            Hence why I asked the OP to clarify what he's trying to achieve.
                            • 12. Re: How to find out the execution time of a sql inside a function
                              BluShadow
                              Keith Jamieson wrote:
                              You can get it in 100ths of a second using dbms_utility.get_time;
                              Good point Keith, I keep forgetting about that one.
                              • 13. Re: How to find out the execution time of a sql inside a function
                                Randolf Geist
                                Please note that wrapping query in a "SELECT COUNT(*) FROM (<query>)" doesn't necessarily reflect the execution time of the stand-alone query because the optimizer is smart and might choose a completely different execution plan for that query.

                                A simple test case shows the potential difference of work performed by the database:
                                Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
                                
                                
                                Session altered.
                                
                                SQL>
                                SQL> drop table count_test purge;
                                
                                Table dropped.
                                
                                Elapsed: 00:00:00.17
                                SQL>
                                SQL> create table count_test as select * from all_objects;
                                
                                Table created.
                                
                                Elapsed: 00:00:02.56
                                SQL>
                                SQL> alter table count_test add constraint pk_count_test primary key (object_id)
                                ;
                                
                                Table altered.
                                
                                Elapsed: 00:00:00.04
                                SQL>
                                SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'COUNT_TEST')
                                
                                PL/SQL procedure successfully completed.
                                
                                Elapsed: 00:00:00.29
                                SQL>
                                SQL> set autotrace traceonly
                                SQL>
                                SQL> select * from count_test;
                                
                                5326 rows selected.
                                
                                Elapsed: 00:00:00.10
                                
                                Execution Plan
                                ----------------------------------------------------------
                                Plan hash value: 3690877688
                                
                                --------------------------------------------------------------------------------
                                | Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
                                --------------------------------------------------------------------------------
                                |   0 | SELECT STATEMENT  |            |  5326 |   431K|    23   (5)| 00:00:01 |
                                |   1 |  TABLE ACCESS FULL| COUNT_TEST |  5326 |   431K|    23   (5)| 00:00:01 |
                                --------------------------------------------------------------------------------
                                
                                
                                
                                Statistics
                                ----------------------------------------------------------
                                          1  recursive calls
                                          0  db block gets
                                        419  consistent gets
                                          0  physical reads
                                          0  redo size
                                     242637  bytes sent via SQL*Net to client
                                       4285  bytes received via SQL*Net from client
                                        357  SQL*Net roundtrips to/from client
                                          0  sorts (memory)
                                          0  sorts (disk)
                                       5326  rows processed
                                
                                SQL>
                                SQL> select count(*) from (select * from count_test);
                                
                                Elapsed: 00:00:00.00
                                
                                Execution Plan
                                ----------------------------------------------------------
                                Plan hash value: 572193338
                                
                                -------------------------------------------------------------------------------
                                | Id  | Operation             | Name          | Rows  | Cost (%CPU)| Time     |
                                -------------------------------------------------------------------------------
                                |   0 | SELECT STATEMENT      |               |     1 |     5   (0)| 00:00:01 |
                                |   1 |  SORT AGGREGATE       |               |     1 |            |          |
                                |   2 |   INDEX FAST FULL SCAN| PK_COUNT_TEST |  5326 |     5   (0)| 00:00:01 |
                                -------------------------------------------------------------------------------
                                
                                
                                Statistics
                                ----------------------------------------------------------
                                          1  recursive calls
                                          0  db block gets
                                         16  consistent gets
                                          0  physical reads
                                          0  redo size
                                        412  bytes sent via SQL*Net to client
                                        380  bytes received via SQL*Net from client
                                          2  SQL*Net roundtrips to/from client
                                          0  sorts (memory)
                                          0  sorts (disk)
                                          1  rows processed
                                
                                SQL>
                                As you can see the number of blocks processed (consistent gets) is quite different. You need to actually fetch all records, e.g. using a PL/SQL block on the server to find out how long it takes to process the query, but that's not that easy if you want to have an arbitrary query string as input.

                                Regards,
                                Randolf

                                Oracle related stuff blog:
                                http://oracle-randolf.blogspot.com/

                                SQLTools++ for Oracle:
                                http://www.sqltools-plusplus.org:7676/
                                http://sourceforge.net/projects/sqlt-pp/
                                • 14. Re: How to find out the execution time of a sql inside a function
                                  BluShadow
                                  Randolf Geist wrote:
                                  Please note that wrapping query in a "SELECT COUNT(*) FROM (<query>)" doesn't necessarily reflect the execution time of the stand-alone query because the optimizer is smart and might choose a completely different execution plan for that query.
                                  I know that. Again, hence why I asked the OP to clarify what they're trying to achieve and what the point is behind their request.

                                  In reality, if the SQL is unknown, it's not really possible to write a procedure to determine the execution time of it because you can't really execute an unknown SQL and measure it without having some means of capturing the results. I'm sure it may be possible to shell out the SQL statement to an external process to run the SQL within a 'hidden' SQL window and not have to worry about the results, but then you introduce overhead of shelling out and any delay in the results being 'displayed' (albeit hidden) by the external process that's running it.

                                  Wrapping it in "select count(*)" is probably the least impact for most SQL as the sub-query will, in a lot of cases, execute in the same manner for the sub query and then just count the result rows, ensuring they are all retrieved.

                                  Edit: I take that last sentance back... I've just looked at your example more closely.

                                  Well, I guess it's now down to the OP to tell us what he's trying to achieve, but he appears to be a little on the quiet side. ;)

                                  Edited by: BluShadow on Sep 9, 2008 9:14 AM
                                  1 2 Previous Next