Oracle 18c : MEMOPTIMIZE_POOL_SIZE in SGA

Description:-

  • Oracle 18c introduces new feature called MemOptimize pool, a memory area in the system global area (SGA).
  • We can increase the size of the memoptimize pool by manually adjusting the MEMOPTIMIZE_POOL_SIZE initialization parameter.
  • We have use this feature’s to frequently access tables in database level. Those tables are pined into buffer cache using primary key values
  • Tables must have primary key column then only we will achieve the expected performance from the query level.
  • Tables should not be compressed. Compressed tables not suitable for Memoptimized Rowstore option.

To check the value of Memoptimized _Pool_size parameter

To set the size of MEMOPTIMIZE_POOL_SIZE parameter.

Note this parameter is not dynamic and required database restart.

Now the MEMOPTIMIZE_POOL_SIZE parameter value has been changed to 1GB.

Create table with  Memoptimized Rowstore option.

Alter the existing table for memoptimized read.

We can load the table data into pool using DBMS_MEMOPTIMIZEpackage:

Catch Me On:- Hariprasath Rajaram

LinkedIn:    https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:   https://www.facebook.com/HariPrasathdba
FB Group:   https://www.facebook.com/groups/894402327369506/
FB Page:      https://www.facebook.com/dbahariprasath/?
Twitter:       https://twitter.com/hariprasathdba

 

 

 

Oracle 18c – Cancel SQL Statement in a Session

Description:-

Before 18c, If we want to terminate a SQL query for consuming excessive resources in a session or to cancel the blocking session or cancel incorrect SQL statement, the only way is to find out the particular session’s SID, SERIAL id and the kill the session using following command ALTER SYSTEM KILL SESSION ‘sid,serial#’;.

After 18c, instead of kill the originating session we can cancel the particular SQL of another session using “ALTER SYSTEM CANCEL SQL ‘SID, SERIAL'”.
If we are cancel a DML statement, the statement has been rolled backed.

SQL> select banner_full from v$version;

BANNER_FULL
———————————————————–
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 – Production
Version 18.2.0.0.0

Step:1 To find the SID for current session.

SQL> select distinct sid from v$mystat;

SID
———-
280

SQL> begin loop null; end loop; end;
/

Step:2 This command help us to find out the SQL_ID using the SQL_TEXT from particular SQL statement. 

SQL> select sql_text,sql_id from v$sql where sql_text like ‘%loop null; end loop%’;

SQL_TEXT                                                                                                    SQL_ID
——- ————————–                                   ————-
select sql_text,sql_id from v$sql                                                       6vbb9d7zj9t5w where sql_text like ‘%loop null; end loop%’

begin loop null; end loop; end;                                                              3tfmdd4xagv3y

Step:3 To find the SERIAL# using the session ID.

SQL> select serial# from v$session where sid=280;

SERIAL#
———-
22827

Step:4 Here is the syntax of cancelling a SQL statement.

ALTER SYSTEM CANCEL SQL ‘SID, SERIAL’

SQL> alter system cancel sql ‘280,22827,3tfmdd4xagv3y’;

System altered.

SQL> begin
loop null;
end loop;
end;
/
begin
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation

 

Catch Me On:- Hariprasath Rajaram

LinkedIn:    https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:   https://www.facebook.com/HariPrasathdba
FB Group:   https://www.facebook.com/groups/894402327369506/
FB Page:      https://www.facebook.com/dbahariprasath/?
Twitter:       https://twitter.com/hariprasathdba