Bind variables and memOptimized Row (Fast Lookup) — oracle-tech

    Forum Stats

  • 3,714,738 Users
  • 2,242,615 Discussions
  • 7,845,038 Comments

Discussions

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Bind variables and memOptimized Row (Fast Lookup)

Arturo Gutierrez
Arturo Gutierrez Member Posts: 331 Blue Ribbon
edited June 2020 in Exadata

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

Answers

Sign In or Register to comment.