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

Last updated: September 16, 2020 - 1:50 am UTC

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.

Rating

  (6 ratings)

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

Comments

Thank you

Mas Husain, January 27, 2016 - 6:57 pm UTC


Magic

A reader, November 28, 2016 - 1:30 am UTC

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
November 28, 2016 - 2:57 am UTC

Glad we could help

Solved my issue

Mike, February 07, 2017 - 11:45 am UTC

Frustrated beyond belief trying to find the cause of this behaviour, the hint resolved my issue.
Connor McDonald
February 07, 2017 - 4:26 pm UTC

Glad we could help, and thanks for letting us know

Works Like a Charm

Kara Danvers, July 31, 2017 - 8:57 pm UTC

Exactly what I was looking for, thanks Connor McDonald
Connor McDonald
August 01, 2017 - 2:22 am UTC

glad we could help

Oracle's bizarre behavior

Mo, March 28, 2018 - 3:38 am UTC

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

A reader, September 14, 2020 - 4:44 pm UTC

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