oracle 12c in memory

[#1]

Hello,

As you know oracle 12c support in memory. we configured this feature.

when check execution plan in some case I don't know why oracle don't use in memory and used index, for example when t.code MB51 run with plant, Movement type ,posting date execution plan fire index ( mseg~0) why oracle !!!

I know oracle is cost base, when invisible indexes, query cost in memory is lower than index cost 😞

Please help me. how can I resolve this issue.

Regards.

Ali Mirnia

[janos_mucsi-besze · Accepted solution]

Hi Ali,

Check if the table got really populated FULLY in the memory. I admit, in case the table got fully or mostly loaded in the memory, and index search does not really make any sense as it would have higher costs. Please verify if the indexes got modified to "invisible" as in case of a correct In Memory usage, there is no need for the indexes.

[Former Member · Accepted solution]

Hi Janos,

thank you , we Invisible index and query speed is good but cpu cost 3% increse.

Regards.

[janos_mucsi-besze · Accepted solution]

Hi Ali,

CPU cost is going up of course as it is using CPU. It is not a big issue until the CPU utilization does not reach the ceiling. But in such a case, it does not mean an issue with In-Memory, just the way how it works. In any case I recommend testing the feature in depth and collect more experience before finding any conclusion.

Regards,

János

[JamesZ]

Hi,

Can you please share the details of this?
How the mseg was populated to in memory? Can you check whether inmemory hint works for this scenario?

Best regards,
James

[Former Member]

Hi James,

Sure

1- Go t.code mb51 and fill data ( figure 1 )

2- Go t.code db02> performance > session monitor > filter SAP user ( figure 2)

3- Run mb51 and in db02 refresh (f8) and you can see sql statement and check Explan

4- In explain we can see Execution plan and oracle use mseg~0 [Figure 4] (all data in memory how check we check in V$im_segments) [Figure 5]

Answer your question :

1-How the mseg was populated to in memory ? this is very steps you can check below link and SAP note.

http://www.oracle.com/us/solutions/sap/using-sap-netweaver-with-dbim-2594359.pdf

2178980 – Using Oracle Database In-Memory with SAP NetWeaver

2- How the mseg was populated to in memory? ALTER TABLE "SAPSR3"."MSEG" MODIFY PARTITION "MSEG2014" INMEMORY MEMCOMPRESS FOR QUERY LOW PRIORITY HIGH; <Our mseg table is partition>

Can you check whether inmemory hint works for this scenario? we can use hint in z report but not in standard system.

[Former Member]

scn-in-memory-pagenumber003.jpg

[JamesZ]

Hi Ali,

You can just inmemory hint in the execution plan part, then explain again.
By doing this, I just want to confirm the inmemory is correctly configured.

Assume it is correct, please share the SBP level and parameter check as according to SAP note 1171650.

Best regards,
James