1

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.

Improve this question
7
  • 1
    Do you have diagnostic pack licenses? Are you familiar with statspack and awr?
    – Philᵀᴹ
    Commented Jan 25, 2013 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
    Commented Jan 25, 2013 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
    Commented Jan 25, 2013 at 17:28
  • 2
    @Julian and we don't really do anecdotes. We deal with technical answers. :)
    – Philᵀᴹ
    Commented Jan 25, 2013 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ᵀᴹ
    Commented Jan 25, 2013 at 17:58

2 Answers 2

Reset to default
1

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.

Improve this answer
-1

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.

Improve this answer
2
  • There's no query result cache in Oracle 10g
    – David Aldridge
    Commented Mar 29, 2013 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
    Commented Mar 31, 2013 at 21:22

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