CODEWITHSUNDEEP
oracle11gdatabase-performancequery-performancetoad
pyroCoder
pyroCoder

Reputation: 170

Oracle query speed drastically changes with new connections

I have a db query with 3 joins on 2 tables (2 self joins) and order by clause at the end. Database server is on another db server, Oracle11g. We have a tool that runs this query internally that is slow(11 hours instead of 1 hour at max).

I grabbed this query from logs and run it in Toad 11.5, it takes 10 seconds at first (in the morning - db untouched over night). Then I go to the db server and run the tool, kill it or let it finish, then I come back to Toad and run this query, it takes about 2-3 hours to finish, same query without any changes.

Please help me trouble shoot this strange behavior. Also, when the query is taking 10 secs, on removal of order by , it takes about 500 mili seconds if this information helps in anyway.

Upvotes: 0

Views: 36

Answers (1)

Littlefoot
Littlefoot

Reputation: 142705

TOAD fooled you. It did return first 500 (or what) rows, but not the whole data set. Try to go to the end of data set (Ctrl + End), you'll see how much it takes (read: ages).

Two options I can think of:

  • use driving_site hint, which will force execution on the database server, not computer you're currently connected to
  • move the procedure to the database server itself and call it from your local connection

Upvotes: 1

Related Questions