Oracle Stored Procedure execution time vs Query execution time

Question

I have PLSQL stored procedure which contain the SELECT statement, which inserts the value to a table:

CREATE OR REPLACE PROCEDURE welcome_msg () 
IS
BEGIN
    INSERT INTO TABLE1 ( 
        SELECT DIFFERENT FIELDS
        FROM TABLE2 T1, TABLE3 T2
        WHERE 
            T1.NAME = T2.NAME 
            AND T1.REGNO = T2.REGNO
            AND T1.TIMESTAMP1 >  CURRENT_TIMESTAMP - 1 day
    )
    END;

This stored procedure runs in few minutes, but if I execute the SELECT SQL query outside, then it is taking more time to execute:

SELECT DIFFERENT FIELDS
FROM TABLE2 T1, TABLE3 T2
WHERE 
    T1.NAME = T2.NAME
    AND T1.REGNO = T2.REGNO
    AND T1.TIMESTAMP1 >  CURRENT_TIMESTAMP - 1 day

The query execution time is greater than the stored procedure execution time, because of Oracle PLSQL is compiled version, so the query plan is determined? Or is my observation wrong?

If the query returns a large number of records, it might actually take more time to fetch them and return them to the client than to insert them to another table... — GMB, Sep 02 '19 at 22:49
Yes, try running the `INSERT`, not the `SELECT` outside of PL/SQL and see what the performance is like. — eaolson, Sep 02 '19 at 23:49
When collecting your timings which did you run first - the procedure or the standalone query? — Bob Jarvis - Слава Україні, Sep 03 '19 at 00:23
[Bad habits to kick : using old-style JOINs](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) - that old-style *comma-separated list of tables* style was replaced with the *proper* ANSI `JOIN` syntax in the ANSI-**92** SQL Standard (**more than 25 years** ago) and its use is discouraged — marc_s, Sep 03 '19 at 03:45
@marc_s - whatever your personal feelings about join syntax are, the use of ANSI-89 joins is irrelevant to the question. Furthermore I think the continual berating of new SO users about implicit joins is unhelpful and (dread word) unwelcoming. It remains perfectly valid syntax and is something people need to know about. For instance I currently find myself at a site where ANSI-89 joins are mandated by the coding standards. Yes, back to `(+)` notation for outer joins. The code still runs. — APC, Sep 03 '19 at 06:39
I agree with @GMB that you are seeing the effects of rendering the result set in the client. PL/SQL native compilation only improves the performance of PL/SQL code and not SQL statements. Performance tuning in Oracle is a matter of many different factors. Please read [this other SO thread on Oracle tuning questions](https://stackoverflow.com/q/34975406/146325) to get some understanding of what's involved. — APC, Sep 03 '19 at 06:45

Tad Harrison · Answer 1 · 2019-09-03T20:03:25.397

As several have commented, there are two likely suspects here: changing plan and time to return the rows. For such a simple query, it seems most likely that the time to deliver the rows over the network is the culprit.

So it would be useful to perform some tests to see how long it takes to get the full data set back in your client (e.g. SQL*Plus) vs. within PL/SQL. There are many ways to achieve this, and a resourceful developer will surely find a suitable way. I provide one method here that uses V$ tables to get a fairly clear picture of what Oracle is doing.

One way to gain insight into the impact of network slowness on your query is by inspecting the various wait events generated by your session.

Note: You need access to V$SESSION_EVENTS for this particular method to work.

Connect to the database using your tool of choice and get the current session ID:

SELECT SYS_CONTEXT ('USERENV', 'SID') FROM DUAL;

Now run the very slow query, making sure to bring back all rows.

If you are using SQL*Plus, you might want to use SET AUTOTRACE TRACEONLY in order to see the plan and stats, but not see the data flying by. SQL*Plus will still pull the rows across the network, it will simply keep from displaying them.

Once your slow query completes go into another connection and run this query:

select wait_class, event, total_waits, time_waited from v$session_event where sid = <SID from first query>;

Here is an example of the results after I ran an intentionally data-volume-heavy query:

WAIT_CLASS  EVENT                               TOTAL_WAITS TIME_WAITED
----------- --------------------------------- ----------- -----------
User I/O    Disk file operations I/O            8           0          
Application enq: KO - fast object checkpoint    1           0          
Commit      log file sync                       2           0          
User I/O    db file sequential read             147         9          
User I/O    db file scattered read              491         50         
User I/O    direct path read                    146         6          
Network     SQL*Net message to client           2072        0          
Network     SQL*Net more data to client         618         8          
Idle        SQL*Net message from client         2071        50067      
Other       events in waitclass Other           1           0

Times are in 1/100 sec. Of interest is the time spent in "SQL*Net message from client"...that represents time waiting for SQL*Plus to say "thanks for those rows, please send me more"

Times listed as "User I/O" are related to database reads. "System I/O" is related to database writes.

In this example, it is clear that the lion's share of the time is being spent sending the rows to SQL*Plus.

Note that these times don't account for everything: the total time your SQL is running consists of waits and CPU, so this set of event waits doesn't show any CPU time. But if we are only interested in the relative times of different wait events this does a pretty good job of it.

In addition, the wait times are cumulative for the entire session, so be sure that the test session is connected at the time you run the test SQL, and check the V$SESSION_EVENTS data immediately, as more wait time might be accumulated that is irrelevant to your test.

Oracle Stored Procedure execution time vs Query execution time

1 Answers1