CODEWITHSUNDEEP
oracle-databaseoracle10gtable-index
KeenUser
KeenUser

Reputation: 5525

1st access to Oracle SP is very slow, subsequent access seem fine

Not sure if this question has been already asked. I face this problem where the 1st hit from the website to an Oracle SP takes a lot of time. Subsequent accesses work just fine.

The SP i'm taking about here is a dynamic SP used for Search functionality(With different search criteria selection option available)

1st access time ~200 seconds subsequent access time ~20 to 30 seconds.

Stored Procedure logic on a high level. Conditional JOINS are appended based on some logics. Dynamic SQL and cursor used to retrieve data.

Any help to start tackling these kind of issues is very helpful..

Thanks, Adarsh

Upvotes: 0

Views: 70

Answers (1)

Srini V
Srini V

Reputation: 11355

The reason why it takes only a few secs to execute the query after the first run is that Oracle caches the results. If you change the SQL then Oracle considers it a different query and won't serve the results from the cache but executes the new query (even formatting the code again or adding a space in between will be a difference).

It is a hard question how to speed up first execution. You'll need to post your query and explain plan and probably you'll have to answer further questions if you want to get help on that.

Upvotes: 0

Related Questions