CODEWITHSUNDEEP
oracle-databaseoracle11g
Kamil Zadora
Kamil Zadora

Reputation: 2397

Why SQL query could take each time more time execute on subsequent executions?

I run a complex query against Oracle DB 11G based eBS R12 schema:

For first run it takes 4 seconds. If I run it again, it takes 9, next 30 etc.

If I add "and 1=1" it takes 4 seconds again, then 9, the 30 and so on.

Quick workaraound is that we added a random generated "and sometstring = somestring" and now the results are always in 4 second.

I have never encoutered a query that would behave this way (it should be opposite, or no siginificat change between executions). We tested it on 2 copies of same DB, same behaviour.

How to debug it? And what internal mechanics could be getting confused?

UPDATE 1:

EXPLAIN PLAN FOR
(my query);
SELECT * FROM table(DBMS_XPLAN.DISPLAY);

Is exactly the same before first run that it is for subsequent ones. see http://pastebin.com/dMsXmhtG

Upvotes: 1

Views: 288

Answers (2)

Boneist
Boneist

Reputation: 23578

Sounds like you might be suffering from adaptive cursor sharing or cardinality feedback. Here is an article showing how to turn them off - perhaps you could do that and see if the issue stops happening, as well as using @OldProgrammer's suggestion of tracing what is happening.

If one of these is found to be the problem, you can then take the necessary steps to ensure that the root cause (eg. incorrect statistics, unnecessary histograms, etc.) is corrected.

Upvotes: 2

Husqvik
Husqvik

Reputation: 5809

Check the DBMS_XPLAN.DISPLAY_CURSOR. The reason could be cardinality feedback or other adaptive techniques Oracle uses. You should see multiple child cursors related to SQL_ID of your query and you can compare their plans.

Has your query bound variables and columns used for filtering histograms? This could be another reason.

Upvotes: 3

Related Questions