Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

This question already has an answer here:

We have an application which we recently upgraded and is having bad performance issues. Any ideas would be much appreciated.

The main issue is windows taking ages (minutes) to open because the initial query to fill their list boxes is taking so long. The second time a user opens the window its fine.

We would expect this to be application cacheing but we don't think so. We see similar problems using Crystal Reports and using command line SQL queries.

We have increased the RAM available to the database.

Another part of our business has a slightly different version of the same application and database and doesn't get this problem.

The time to complete a query can be 30 minutes for the first time in a session and 30 seconds for subsequent executions.

share|improve this question

marked as duplicate by Mark Storey-Smith, Paul White, RolandoMySQLDBA, Max Vernon, Shawn Melton Dec 13 '13 at 5:48

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

1  
Do you have diagnostic pack licenses? Are you familiar with statspack and awr? –  Phil Jan 25 '13 at 9:34
    
In addition to what Phil asked, have you done an autotrace and/or trace on the first and second runs of the query? –  Leigh Riffel Jan 25 '13 at 14:00
    
our supplier's DBAs are doing all that good stuff. I posted here in case anyone had any anecdotal 'hey that happened to us and...' ideas. –  Julian Jan 25 '13 at 17:28
2  
@Julian and we don't really do anecdotes. We deal with technical answers. :) –  Phil Jan 25 '13 at 17:45
1  
@Julian BTW, because of the client, not because of Oracle itself. I've seen it happen before with Java clients (specifically using Hibernate) casting to the wrong datatype because of charactersets. Oracle ends up internally having to do a TO_CHAR or TO_NCHAR (which shows up as INTERNAL_FUNCTION() or SYS_OPxxxxx in EXPLAIN PLAN output), which means it can't use the index. I've had to use a functional index before to fix this kind of problem. You can also check DBA_HIST_SQL_BIND_METADATA to see what datatype the client bound a bind variable to, then compare it with the DB column type –  Phil Jan 25 '13 at 17:58
show 2 more comments

2 Answers

The usual reason for such issues is that the first execution of the query requires physical reads to fetch the required blocks from disk, and the second and subsequent executions find that they are already cached.

You can test this by running the queries from SQL*Plus (and timing them) prior to first opening these windows of which you speak, to see if that affects performance.

If you can come back with comparison of the timings when running the queries through SQL*Plus and then through the application it would help further analysis.

share|improve this answer
add comment

The reason why its take a more times in first because 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.

These kinds of effects can happen even on index scans, but are especially exacerbated on expensive operations such as full table scans (because of a large number of pages touched).

Check you query plan and see if there are any expensive operations that might touch large number of pages...

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

Thanks.

share|improve this answer
    
There's no query result cache in Oracle 10g –  David Aldridge Mar 29 '13 at 9:14
    
As David already pointed, there is no "query result cache" in any of the big database champions AFAIK. There is a data cache, where pages of real data are cached in memory and used from there when a query requests the same pages. They are evicted from memory using a LRU (least recently used) algorithm (at least for MS SQL Server). Record sets returned by a procedure or statement are not cached in that exact form. Now, for the other part of the answer you are right, the explain plan needs to be checked. –  Marian Mar 31 '13 at 21:22
add comment

Not the answer you're looking for? Browse other questions tagged or ask your own question.