Home » SQL & PL/SQL » SQL & PL/SQL » Why some of my SQL are run slower in Oracle In-memory than it was before?
Why some of my SQL are run slower in Oracle In-memory than it was before? [message #665444] Wed, 06 September 2017 04:22 Go to next message
Peter TL Wong
Messages: 2
Registered: September 2017
Junior Member
We try to upgrade our Oracle database to Oracle In-memory feature, It seems most of SQL are running better especially for those simple SQL, but I notice some complex SQL are getting worse even all related tables are populated in In-memory. Anyone found the same problem?
Re: Why some of my SQL are run slower in Oracle In-memory than it was before? [message #665449 is a reply to message #665444] Wed, 06 September 2017 05:10 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Welcome to the forum. Please read our OraFAQ Forum Guide and How to use [code] tags and make your code easier to read

You will have to provide a lot more information, starting with the queries and the execution plans (with and without inmemory); execution statistics; detail of the objects; their size on disc and inmemory; compression type; your exact database release number.
Re: Why some of my SQL are run slower in Oracle In-memory than it was before? [message #665451 is a reply to message #665449] Wed, 06 September 2017 06:48 Go to previous messageGo to next message
RichardTo
Messages: 1
Registered: August 2017
Junior Member
Please remember that a SQL with tables being populated into In-memory, it will change the query plan of the SQL, performance may be changed too. Oracle SQL optimizer may do a good job on most of SQL, but there are some SQL performance may be degraded. Sometimes you have to use No_inmemory hint to hide some in-memory objects in you SQL, so you have to understand you SQL's execute plans before and after in-memory population. It is like a trial and error process. Have fun.
Re: Why some of my SQL are run slower in Oracle In-memory than it was before? [message #665467 is a reply to message #665444] Wed, 06 September 2017 11:27 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
How in-memory objects are populated in your case? At instance startup or when in-memory objects are accessed? If latter, first Query against it will take longer so you should exclude it from test.

SY.
Re: Why some of my SQL are run slower in Oracle In-memory than it was before? [message #665507 is a reply to message #665467] Thu, 07 September 2017 19:27 Go to previous messageGo to next message
Peter TL Wong
Messages: 2
Registered: September 2017
Junior Member
Yes, At instance startup we populate all selected objects. The problem seems related to execution plan changes. I am going to disable some objects in-memory by NO_INMEMORY my SQLs to see if i get back to normal performance.
Re: Why some of my SQL are run slower in Oracle In-memory than it was before? [message #665508 is a reply to message #665507] Thu, 07 September 2017 19:36 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Ready, Fire, AIM!

post both EXPLAIN PLAN for same SQL that is both fast & slow
Re: Why some of my SQL are run slower in Oracle In-memory than it was before? [message #665510 is a reply to message #665507] Fri, 08 September 2017 02:02 Go to previous message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Quote:
I am going to disable some objects in-memory by NO_INMEMORY my SQLs to see if i get back to normal performance.
This is absolutely the wrong approach. You have paid a massive amount (nearly 50% uplift on your licensing cost) for the inmemory option. You need to make it work, not turn it off. For example, perhaps you are not getting Bloom filtered joins or vectored aggregations. Or perhaps there is too much use of indexed access paths.
Previous Topic: updated column on the same table automatically
Next Topic: left outer join of 3 tables
Goto Forum:
  


Current Time: Fri Mar 29 05:06:38 CDT 2024