SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Query runs much slower the second time!

669711Aug 31 2011 — edited Feb 21 2013

I am doing a simple SELECT on a fairly complex view:

select * from balances where day = date'2011-08-15';

The results are correct, and they arrive in about 3 seconds. However, immediately repeating the exact same query takes about 3 minutes!

Using a different SELECT list and/or WHERE clause gives a quick result, but only once; repeating any past query triggers the big slowdown.

Doing ANALYZE TABLE on any of the tables involved in the view seems to reset things and the queries become fast again, but still only once for each specific query.

This view worked fine on 9i and has only started doing this since we moved to 11g.

Weird, eh?

Locked Post

New comments cannot be posted to this locked post.

Locked on Mar 21 2013

Added on Aug 31 2011

#11g, #database, #error, #oracle, #performance, #query

20 comments

9,816 views