CODEWITHSUNDEEP
sqloracle-databaseplsqloracle11goracle-sqldeveloper
Ashok Kumar Dabbadi
Ashok Kumar Dabbadi

Reputation: 265

same Query is executing with different time intervals

I have a scenario, in which i have one stored proc which contains set of sql statements( combination of joins and sub queries as well, query is large to displays) and finally result is storing in temp table.

this is executing by user from frontend or programmer from backend with specific permissions.

here the problem is, there is difference in execution time for this query.

sometimes it is taking 10 mins, sometimes it is taking 1 hour, but an average elapsed time is 10 mins, and one common thing is always it is giving the same amount of records (approximately same).

Upvotes: 0

Views: 750

Answers (4)

ErikL
ErikL

Reputation: 2041

or if that doesn't solve it, you can also try tracing the session that calls the SP from the frontend. There's a very good explanation about tracing here: http://tinky2jed.wordpress.com/technical-stuff/oracle-stuff/what-is-the-correct-way-to-trace-a-session-in-oracle/

Upvotes: 0

Sandeep
Sandeep

Reputation: 349

If you are new to oracle then you can use dbms_output or use a logging table to store intermediate execution times, that way you will know which SQL is causing the issue.

declare
run_nbr number;
begin
run_nbr = 1; -- or get it from sequence
SQL1;
log(run_nbr ,user,'sql1',sysdate);
SQL2;
log(run_nbr ,user,'sql2',sysdate);
commit;

end;

here log procedure is nothing but simple insert statements which will insert into a table say "LOG" and which has minimal columns say run_nbr, user, sql_name, execution_date

procedure log(run_nbr number, user varchar2, sql_name varchar2, execution_date date)
is
begin
insert into log values(run_nbr, user, sql_name, execution_date);
-- commit; -- Un-comment if you are using pragma autonomous_transaction
end;

This is little time consuming to put these log statements, but can give you idea about the execution times. Later once you know the issue, you simply remove/comment these lines or take a code backup of your original procedure without these log statements and re-compile it after pin-pointing the issue.

Upvotes: 1

Tim Sanders
Tim Sanders

Reputation: 851

As ErikL mentioned checking the execution plan of the query is a good start. In Oracle 11g you can use the DBMS_PROFILER. This will give you information about the offending statements. I would run it multiple times and see what the difference is between multiple run times. First check to see if you have the DBMS_PROFILER installed. I believe it comes as a seperate package.

To start the profiler:

SQL> execute dbms_profiler.start_profiler('your_procedure_name');

Run your stored procedure:

SQL> exec your_procedure_name

Stop the profiler:

SQL> execute dbms_profiler.stop_profiler;

This will show you all statements in your store procedure and their associated run time, and this way you can narrow down the problem to possibly a single query that is causing the difference.

Here is the Oracle doc on DBMS_PROFILER: Oracle DBMS PROFILER

Upvotes: 1

ErikL
ErikL

Reputation: 2041

I would check the execution plan of the query, maybe there are profiles in there that are not always used.

Upvotes: 0

Related Questions