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?

VNeha
Profile Answers by VNeha Questions by VNeha
Apr 11th, 2012
6
8804

Questions by VNeha

Oracle

Answer

Showing Answers 1 - 6 of 6 Answers

koushikinath
Profile Answers by koushikinath

May 24th, 2012

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

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.