Bind variables and memOptimized Row (Fast Lookup)

Arturo Gutierrez

Hello,

I write this topic in this forum because the memoptimized capability is available only in Exadata environments. If you think it is better to do it in another forum, you can tell me.

Testing the memoptimized (fast lookup) option I have observed that making accesses from PL / SQL blocks using variable does not activate the fast lookup method.

This is the proof:

I create the EMP table with the option MEMOPTIMIZE FOR READ and load some rows.

The fast lookup work fine, if I use querys of type:

Select * from emp where empno = 1 ; or some value.

However with PL/SQL.

run this PL/SQL block:

Declare

a number;

b varchar2(20);

Begin

for i in 1..100 loop

select empno,ename into a,b from emp where empno=i;

dbms_output.put_line (a);

dbms_output.put_line (b);

end loop;

end;

/

The Pl/SQL block is executed correctly but the memoptimized row statistics about lookup and hits are 0.

SQL> select a.name,

  2  round(b.value/1024/1024,2) MB,

  3  b.value valor

  4   from v$sysstat a, v$mystat b

  5   where a.statistic# = b.statistic#

  6   and  a.name like '%memopt r%'

  7  order by b.value desc;

NAME                                                                     MB      VALOR

---------------------------------------------------------------- ---------- ----------

memopt r hits                                                             0          0

memopt r lookups                                                          0          0

memopt r misses                                                           0          0

memopt r tag collisions                                                   0          0

memopt r lookup skipped deleted rows                                      0          0

memopt r lookup skipped locked rows                                       0          0

memopt r lookup skipped chained rows                                      0          0

memopt r failed reads on buckets                                          0          0

memopt r failed reads on blocks                                           0          0

memopt r lookup detected CR buffer                                        0          0

memopt r puts                                                             0          0

memopt r puts:buckets full                                                0          0

memopt r successful puts                                                  0          0

memopt r successful puts:with evictions                                   0          0

memopt r successful puts:with cuckoo                                      0          0

memopt r successful puts:cuckoo deadend                                   0          0

memopt r successful puts:max cuckoo                                       0          0

memopt r failed puts                                                      0          0

memopt r failed puts:bucket in flux                                       0          0

memopt r failed puts:no space                                             0          0

memopt r populate tasks accepted                                          0          0

memopt r populate tasks not accepted                                      0          0

memopt r populate skipped locked rows                                     0          0

memopt r populate skipped deleted rows                                    0          0

memopt r populate skipped chained rows                                    0          0

memopt r rows populated                                                   0          0

memopt r populate                                                         0          0

memopt r blocks populated                                                 0          0

memopt r failed to get tbs drop EQ                                        0          0

memopt r failed to get tbs offline EQ                                     0          0

memopt r failed to get segment drop EQ                                    0          0

memopt r repopulate tasks accepted                                        0          0

memopt r repopulate tasks not accepted                                    0          0

memopt r repopulate                                                       0          0

memopt r rows repopulated                                                 0          0

memopt r blocks repopulated                                               0          0

memopt r repopulate skipped locked rows                                   0          0

memopt r repopulate skipped deleted rows                                  0          0

memopt r repopulate skipped chained rows                                  0          0

memopt r repopulate invalidated entries                                   0          0

memopt r cleanup                                                          0          0

memopt r NO IM tasks accepted                                             0          0

memopt r NO IM tasks not accepted                                         0          0

memopt r DROP IM tasks accepted                                           0          0

memopt r DROP IM tasks not accepted                                       0          0

memopt r fail to pin buffer                                               0          0

memopt r entries deleted                                                  0          0

Also doing a SQL trace of this PL/SQL the trace reveal a conventional Index Scan not a READ OPTIM index scan.

Does anyone know if it is a limitation of this functionality? or I need to modify something.

Many Thanks

Arturo

Arturo Gutierrez

Hi,

I forget to comment the Software version is : Oracle EE :19.6.0.0.0

Regards

Arturo Gutierrez

Hi,

Dear Content Manager.

Because there is no answer to this question and it is a PL / SQL topic. Is it possible to move this question to the PL / SQL forum?

Thanks

Arturo