Skip to Main Content
  • Questions

  • Analyse table or purge shared pool then Query run faster.

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, sam.

Asked: March 08, 2016 - 6:07 pm UTC

Last updated: March 10, 2016 - 2:41 am UTC

Version: 11g2

Viewed 1000+ times

You Asked

Now I run query which take a more time more than 15 Minute and suddenly I fire v$session view and I see sql_text column shows a join query which contain 3 table and this join query written in function.
Now some time this query generate event like 'DB Sequential reads' or 'latch buffer cache chain'.
When More than 2 user run same query at a time then I watch on v$session view ,join query are there and I refer some statistics of v$session and v$sql like user wait is more than 190394 and buffer also 6756778.

but when I purge shared pool for this join query or analyse 3 table in join condition then work well so I can't understand what happen so please give your suggestion.

Note :- All three table contain less number of distinct value and contain only one index and no primary-foreign key relationship are there.

and Connor said...

It might be due to cardinality feedback.

When a query does not run as as well as we expected it to, we might mark it for a "second look" by the optimizer on the next time it runs. We'll then use that second plan.

If we got that wrong, ie, the first plan was a better one, then flushing the shared pool would then pick up that one again.

*If* that is the case, look at creating an outline or hinting the SQL to lock down the plan that you want.

Hope this helps.

Rating

  (2 ratings)

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

Comments

Smith, March 09, 2016 - 4:04 pm UTC

Thank 4 help me.
But Sometime Skewed column is come picture so what is it?

Like a column contain low distinct value or high distinct value.
Example :- Column have 3 value like Open , close, pending for task so total rows is 100000 and now close contain 50000 rows and open contain 30000 and pending contain 20000 so here what is skewed column?
plz help me.
Chris Saxon
March 10, 2016 - 2:41 am UTC

In which case, you would want to have a histogram on the column. Adaptive cursor sharing should then take care of it

https://prutser.files.wordpress.com/2010/11/adaptivecursorsharing.pdf

Smith, March 09, 2016 - 4:06 pm UTC


But Sometime Skewed column is come picture so what is it?

Like a column contain low distinct value or high distinct value.
Example :- Column have 3 value like Open , close, pending for task so total rows is 100000 and now close contain 50000 rows and open contain 30000 and pending contain 20000 so here what is skewed column?
plz help me.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.