Skip to Main Content
  • Questions
  • Query runs really fast on 1st attempt, but then slows down considerably in subsequent runs

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Mas.

Asked: January 26, 2016 - 10:37 pm UTC

Answered by: Connor McDonald - Last updated: September 16, 2020 - 1:50 am UTC

Category: Database - Version: 11.2.0.4

Viewed 10K+ times! This question is

You Asked

Hi Tom and Team,

I have a SELECT query that runs fine if submitted the 1st time (takes about 3 seconds), but if I submit it right after again, it could take anywhere from 22 to 37 seconds (on a pretty consistent basis). If I wait a few hours (or if I issue the "alter system set pga_aggregate_target=xxxx" or even alter session sort_area_size=xxxx" or bounce the database), its runtime immediately goes back to 3 seconds. But if I issue the query right after again, it goes back to slow.

I printed out & compared the explain plan for the time it took 3 seconds to the one when it took 20+ seconds and found them to be identical.

So how I came up with this work-around (i.e. running alter system set pga_.... or alter session)? When I looked at the output of "autotrace on" I noticed that in the fast version of the query, it was not doing that much sorting in the memory. But in the slow version of the query, it did more than 10x the sorting in memory so I thought I should play around with memory allocated for sorting.

*** Autotrace output for the slow running version of query:
Statistics
----------------------------------------------------------
50 recursive calls
0 db block gets
7981548 consistent gets
0 physical reads
0 redo size
3466 bytes sent via SQL*Net to client
509 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
1179499 sorts (memory)
0 sorts (disk)
0 rows processed


*** Autotrace output for the fast running version of query:
Statistics
----------------------------------------------------------
166 recursive calls
0 db block gets
40646 consistent gets
0 physical reads
0 redo size
3466 bytes sent via SQL*Net to client
509 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
1531 sorts (memory)
0 sorts (disk)
0 rows processed

I did not provide the query text as it contains some company specific information. And I frankly thought looking at these systems, with your excellent expertise, you would be able to find out why. Thanks!

Mas

and we said...

Check out this post from the optimizer team

https://blogs.oracle.com/optimizer/entry/cardinality_feedback

Cardinality feedback in 11g is where after the first execution, the optimizer may mark a query as a candidate for a "second look" at re-optimization.

Perhaps try it with this:

select /*+ opt_param('_optimizer_use_feedback' 'false') */

or created a stored outline for the fast one.

Hope this helps.

and you rated our response

  (6 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

Thank you

January 27, 2016 - 6:57 pm UTC

Reviewer: Mas Husain


Magic

November 28, 2016 - 1:30 am UTC

Reviewer: A reader

This is magic
i experienced the same issue a query will run in 2 seconds but only at the first time

and this fix really worked for me

Thanks
Connor McDonald

Followup  

November 28, 2016 - 2:57 am UTC

Glad we could help

Solved my issue

February 07, 2017 - 11:45 am UTC

Reviewer: Mike from UK

Frustrated beyond belief trying to find the cause of this behaviour, the hint resolved my issue.
Connor McDonald

Followup  

February 07, 2017 - 4:26 pm UTC

Glad we could help, and thanks for letting us know

Works Like a Charm

July 31, 2017 - 8:57 pm UTC

Reviewer: Kara Danvers from National City, CA

Exactly what I was looking for, thanks Connor McDonald
Connor McDonald

Followup  

August 01, 2017 - 2:22 am UTC

glad we could help

Oracle's bizarre behavior

March 28, 2018 - 3:38 am UTC

Reviewer: Mo from Australia

I tried the same hint and it really helped me. However this does not explain why it happened? Oracle's carnality feedback feature is supposed to improve performance of the query by benefiting from some real life statistics collected from the first execution. How on earth could that lead up to drastically slowing the query down and generating crappy execution plans? Oracle should really apologize to customers over this!!

September 14, 2020 - 4:44 pm UTC

Reviewer: A reader

I just got hit with this in 2020. How is this not considered a bug? A query that should take 20s to complete wouldn't finish on for one of my clients, but with no error. It's nice there's a workaround, but the SQL should just run.
Connor McDonald

Followup  

September 16, 2020 - 1:50 am UTC

When you encounter things like this, I encourage you to dump out a full test via

dbms_sqldiag.export_sql_testcase

and log it with Support. Every input we get helps make the optimizer better