CODEWITHSUNDEEP
sqloracle-database
Amin
Amin

Reputation: 129

Oracle low speed in first execute

I've written a query in SQL that selects some columns from a table.My problem is this,when I execute this query in Oracle 10g,it takes about 7 seconds, but next time it executes in a few milisecond.If I change the where clause, it takes more than 5 seconds again. How can I speed up this query at first time?The query is like this :

SELECT Id,Qflag, LetterId, LetterNo, LetterDate, InstanceDate, ViewDate, DeadlineDate 
FROM VWLETTERINSTANCESEARCH 
WHERE ( QFLAG IS NULL OR QFLAG = 0 ) AND 
  EXISTS(SELECT * FROM VWLETTERINSTANCESEARCH VW 
         where PARENTINSTANCEID=VWLETTERINSTANCESEARCH.Id AND refSenderId=1947)AND 
  EXISTS(SELECT * FROM LetterInstances ChildInstance, Folders 
         WHERE ChildInstance.ParentInstanceId=VWLETTERINSTANCESEARCH.Id AND 
               ChildInstance.FolderId=Folders.Id AND Folders.OwnerId=23) AND 
  OwnerId IN (SELECT StaffId FROM vwStaffUsers WHERE UserId=2 AND Deleted=0)

Upvotes: 1

Views: 7414

Answers (2)

bpgergo
bpgergo

Reputation: 16037

The reason why it takes only a few millisecs 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.

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.

My first try would be eliminate subqueries, although these days Oracle optimizes this quite well:

SELECT DISTINCT VW1.Id, VW1.Qflag, VW1.LetterId, VW1.LetterNo, VW1.LetterDate, 
  VW1.InstanceDate, VW1.ViewDate, VW1.DeadlineDate 
FROM 
  VWLETTERINSTANCESEARCH VW1,
  VWLETTERINSTANCESEARCH VW2,
  LetterInstances, ChildInstance, Folders, 
  vwStaffUsers SU
WHERE ( QFLAG IS NULL OR QFLAG = 0 ) 
  AND (VW1.PARENTINSTANCEID=VW2.Id AND VW2.refSenderId=1947)
  AND (ChildInstance.ParentInstanceId=VW1.Id AND 
               ChildInstance.FolderId=Folders.Id AND Folders.OwnerId=23) 
  AND (VW1.OwnerId = SU.StaffId AND SU.UserId=2 AND SU.Deleted=0)

EDIT Also, I'd try to run the query only on the main table (i.e. VWLETTERINSTANCESEARCH VW1) and see if it is fast enough. Then I'd add the rest of the tables gradually, one by one, and see where the performance starts to degrade.

Upvotes: 5

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52107

It is likely that you are experiencing the effects of caching:

  • The first time you execute the query, Oracle cache is is still "cold". As the query executes, Oracle caches the pages that were touched during query execution.
  • When you re-execute the query, needed pages are already in the cache, so it's much faster.
  • When you change WHERE, query touches different pages and the cycle repeats again.

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...

Upvotes: 2

Related Questions