Donghua's Blog - DBAGlobe: June 2020

Saturday, June 27, 2020

SQL Developer 19.4 onwards new features - CODESCAN

SQL> help codescan
SET CODESCAN
---------

set CODESCAN ALL | NONE
|SQLINJECTION [ON | OFF]
Controls warning messages issued for code quality issues.
ALL or SQLINJECTION ON turns on warnings for possible SQL injection vulnerabilities.
NONE or SQLINJECTION OFF disables warnings.
Default is ALL.

SQL> create or replace procedure p(name in varchar2)
2 as
3 begin
4 execute immediate 'select * from t1 where id1='''||name||'''';
5 end;
6* /

SQLcl security warning: SQL injection NAME line 1 -> NAME line 4

Procedure P compiled

19c New Features: Same table enabled for both memoptimized read and write

SQL> select memoptimize_read,memoptimize_write from dba_tables where table_name='TEST_FAST_INGEST';

MEMOPTIM MEMOPTIM
-------- --------
ENABLED ENABLED

SQL> alter table test_fast_ingest no memoptimize for read;

Table altered.

SQL> alter table test_fast_ingest no memoptimize for write;

Table altered.

SQL> select memoptimize_read,memoptimize_write from dba_tables where table_name='TEST_FAST_INGEST';

MEMOPTIM MEMOPTIM
-------- --------
DISABLED DISABLED

SQL> alter table test_fast_ingest memoptimize for read;

Table altered.

SQL> alter table test_fast_ingest memoptimize for write;

Table altered.

SQL> select memoptimize_read,memoptimize_write from dba_tables where table_name='TEST_FAST_INGEST';

MEMOPTIM MEMOPTIM
-------- --------
ENABLED ENABLED

18c New Features - Memoptimized rowstore fast lookup

Fast lookup enables fast data retrieval from database tables for applications, such as Internet of Things (IoT) applications.
Fast lookup uses a hash index that is stored in the SGA buffer area called memoptimize pool to provide fast access to blocks of tables permanently pinned in the buffer cache, thus avoiding disk I/O and improving query performance.

Reference: https://docs.oracle.com/en/database/oracle/oracle-database/19/tgdba/tuning-system-global-area.html#GUID-E46EF11C-E999-4277-950F-E78EEC895ABB

Execution Plan with memoptimized read fast lookup

SQL> show parameter memoptimize_pool_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
memoptimize_pool_size                big integer 400M


SQL> alter table test_fast_ingest memoptimize for read;

Table altered.

SQL> select * from test_fast_ingest where id=1;

        ID TEST_COL
---------- ---------------
         1 test

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 1177632651

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                  |     1 |    22 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID READ OPTIM| TEST_FAST_INGEST |     1 |    22 |     2   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN READ OPTIM         | SYS_C008161      |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID"=1)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        487  bytes sent via SQL*Net to client
        392  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

Execution Plan without memoptimize

SQL> alter table test_fast_ingest no memoptimize for read;

Table altered.

Elapsed: 00:00:00.01
SQL> /* take 2nd execution output, avoid overhead with SQL parsing */
SQL> select * from test_fast_ingest where id=1;

        ID TEST_COL
---------- ---------------
         1 test

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 1177632651

------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                  |     1 |    22 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_FAST_INGEST |     1 |    22 |     2   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | SYS_C008161      |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID"=1)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        487  bytes sent via SQL*Net to client
        392  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processe

19c New Features Memoptimized Rowstore Fast Ingest

Summary:

Regular insert (per row commit): Elapsed: 14:47.37

Regular insert (batch commit): Elapsed: 04:34.80

Memoptimized Rowstore Fast Insert (per row commit): Elapsed: 04:27.56

Test Script

create table test_normal_ingest (
id number primary key,
test_col varchar2(15));

declare
i number(9,0);
begin
for i in 1..10000000
loop
  insert /*+ normal_write */ into test_normal_ingest values (i, 'test');
  commit;
end loop;
end;
/

create table test_normal_ingest_batch (
id number primary key,
test_col varchar2(15));

declare
i number(9,0);
begin
for i in 1..10000000
loop
  insert /*+ normal_write */ into test_normal_ingest_batch values (i, 'test');
end loop;
commit;
end;
/


create table test_fast_ingest (
id number primary key,
test_col varchar2(15))
segment creation immediate
memoptimize for write;

declare
i number(9,0);
begin
for i in 1..10000000
loop
  insert /*+ MEMOPTIMIZE_WRITE */ into test_fast_ingest values (i, 'test');
  commit;
end loop;
end;
/

Test Output:

Regular insert (per row commit): Elapsed: 14:47.37

SQL> create table test_normal_ingest (
  2  id number primary key,
  3  test_col varchar2(15));

Table created.

Elapsed: 00:00:00.01
SQL>
SQL> declare
  2  i number(9,0);
  3  begin
  4  for i in 1..10000000
  5  loop
  6    insert /*+ normal_write */ into test_normal_ingest values (i, 'test');
  7    commit;
  8  end loop;
  9  end;
 10  /

PL/SQL procedure successfully completed.

Elapsed: 00:14:47.37

Regular insert (batch commit): Elapsed: 04:34.80

SQL> create table test_normal_ingest_batch (
  2  id number primary key,
  3  test_col varchar2(15));

Table created.

Elapsed: 00:00:00.01
SQL>
SQL> declare
  2  i number(9,0);
  3  begin
  4  for i in 1..10000000
  5  loop
  6    insert /*+ normal_write */ into test_normal_ingest_batch values (i, 'test');
  7  end loop;
  8  commit;
  9  end;
 10  /

PL/SQL procedure successfully completed.
Elapsed: 00:04:34.80

Memoptimized Rowstore Fast Insert (per row commit): Elapsed: 04:27.56

SQL> create table test_fast_ingest (
  2  id number primary key,
  3  test_col varchar2(15))
  4  segment creation immediate
  5  memoptimize for write;

Table created.

Elapsed: 00:00:00.28
SQL>
SQL> declare
  2  i number(9,0);
  3  begin
  4  for i in 1..10000000
  5  loop
  6    insert /*+ MEMOPTIMIZE_WRITE */ into test_fast_ingest values (i, 'test');
  7    commit;
  8  end loop;
  9  end;
 10  /

PL/SQL procedure successfully completed.

Elapsed: 00:04:27.56

DB Setting:

SQL> show parameter  large_pool_size                      big integer 1G

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
large_pool_size                      big integer 1G


SQL> select total_size,used_space,free_space from v$memoptimize_write_area;

TOTAL_SIZE USED_SPACE FREE_SPACE
---------- ---------- ----------
2154823680    1212896 2153610784

Reference: https://docs.oracle.com/en/database/oracle/oracle-database/19/tgdba/tuning-system-global-area.html#GUID-CFADC9EA-2E2F-4EBB-BA2C-3663291DCC25

Monday, June 1, 2020

How to achieve listagg effect in pre-11g Oracle database and listagg example

Version 1

select department_id, rtrim(first_name,',') enames
from ( select department_id, first_name, rn
       from employees
       model 
       partition by (department_id)
       dimension by (
         row_number() over (partition by department_id order by first_name) rn
       )
       measures (cast(first_name as varchar2(300)) first_name)
       rules (
        first_name[any] order by rn desc = first_name[cv()]||','||first_name[cv()+1]
        )
)
where rn = 1
order by department_id
;

Version 2

select department_id, substr(max(sys_connect_by_path(first_name,',')),2) members
from (select department_id, first_name, 
    row_number() over (partition by department_id order by department_id) rn
    from employees)
start with rn = 1
connect by prior rn = rn-1
and prior department_id = department_id
group by department_id
;

Version 3

-- 11g Orace listagg function
select department_id, listagg(first_name, ',') within group (order by department_id) members
from employees
group by department_id
;

Version 4

-- 19c new features: distinct clause to remove duplicates
select department_id, listagg(distinct first_name, ',') within group (order by department_id) members
from employees
group by department_id
;

Donghua's Blog - DBAGlobe