SQL queries performance issue

You have a batch job which has hundreds of SQL queries in it. Earlier is was finishing in expected time. However, now it is taking twice the time. There is no CPU utilization issue due to other jobs. How would you know that which of the queries are taking more time now and why?

Questions by VNeha

Showing Answers 1 - 2 of 2 Answers

1. At first enable the trace, at the beginning of the sqls.
2. Then check the trace file.
3. Using tkprof create the output file.
4. According to explain plan check the elapsed time for each query.
5. Then tune them

  Was this answer useful?  Yes

Nagaraj Teli

  • Jul 22nd, 2012

Please find the below methods for finding one of the query which is taking longer time in the batch script.

Methods :-
1) Put the dbms_output after each of the SQL query.
2) Create a logfile using the tkprof utility.
3) Best Method is take the AWR(Automatic Workload Repository) report from the database and find the particular query which is taking longer time to execute.

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.


Related Answered Questions


Related Open Questions