Sunday, January 23, 2022

Performance and Storage comparisons between UUID and BigSerial (BigInt)

 UUID: Universally Unique Identifiers (UUID) 

·        Defined by RFC 4122, ISO/IEC 9834-8:2005, and related standards

·        Known as globally unique identifier, or GUID

·        Example: a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11

·        Length in Text: 36 characters

·        Storage in UUID: 32 digits = 128bits = 16bytes

 

Bigserial: 

·        Same as “SERIAL8”

·        Range : 1 - 9223372036854775807

·        “create table t (id bigserial)” same as:

o    create sequence t_id_seq;

o    create table t(id bigint not null default nextval('t_id_seq’));

o    alter sequence t_id_seq owned by t.id;


Install Extension:

create extension if not exists "uuid-ossp";


mytest=> \df

                                 List of functions

 Schema |        Name        | Result data type |    Argument data types    | Type

--------+--------------------+------------------+---------------------------+------

 public | uuid_generate_v1   | uuid             |                           | func

 public | uuid_generate_v1mc | uuid             |                           | func

 public | uuid_generate_v3   | uuid             | namespace uuid, name text | func

 public | uuid_generate_v4   | uuid             |                           | func

 public | uuid_generate_v5   | uuid             | namespace uuid, name text | func

 public | uuid_nil           | uuid             |                           | func

 public | uuid_ns_dns        | uuid             |                           | func

 public | uuid_ns_oid        | uuid             |                           | func

 public | uuid_ns_url        | uuid             |                           | func

 public | uuid_ns_x500       | uuid             |                           | func


Insert Performance:


create table t1(c1 uuid primary key, c2 char(100));

create table t2(c1 char(36) primary key, c2 char(100));

create table t3(c1 bigserial primary key, c2 char(100));


mytest=> insert into t1 select uuid_generate_v4(),'x' from generate_series(1,1000000);

INSERT 0 1000000

Time: 8165.939 ms (00:08.166)


mytest=> insert into t2 select uuid_generate_v4(),'x' from generate_series(1,1000000);

INSERT 0 1000000

Time: 12102.101 ms (00:12.102)


mytest=> insert into t3(c2) select 'x' from generate_series(1,1000000);

INSERT 0 1000000

Time: 5617.863 ms (00:05.618)

 

Storage Comparison:


mytest=> \di+

                                List of relations

 Schema |  Name   | Type  |  Owner   | Table | Persistence | Size  | Description

--------+---------+-------+----------+-------+-------------+-------+-------------

 public | t1_pkey | index | postgres | t1    | permanent   | 38 MB |

 public | t2_pkey | index | postgres | t2    | permanent   | 73 MB |

 public | t3_pkey | index | postgres | t3    | permanent   | 21 MB |

(3 rows)


select * from (

(select pg_column_size(c1) as "t1.c1" from t1 limit 1) as a1 cross join

(select pg_column_size(c1) as "t2.c1" from t2 limit 1) as a2 cross join

(select pg_column_size(c1) as "t3.c1" from t3 limit 1) as a3);

 t1.c1 | t2.c1 | t3.c1

-------+-------+-------

    16 |    37 |     8

 


Other Considerations:


       Large size could mean bloat, large WAL logs, more space in both storage and memory

       Randomization means more random IOs for the index, less efficient buffer cache as hot data spread evenly.

       Foreign Key (FK) Constraints usually reference to PK.

       Max(id) doesn’t make sense to you any more.

Thursday, December 23, 2021

Error Ora-28500 and Sqlstate I or Displays a Square Bracket - [ - Issuing Selects From a Unicode Oracle RDBMS With Dg4odbc To Non-Oracle Databases Using the UnixODBC Driver Manager

Symptom:

 SQL> select * from "t"@pglink;

select * from "t"@pglink

                *

ERROR at line 1:

ORA-28500: connection from ORACLE to a non-Oracle system returned this message:

[


Log file: /u01/db/hs/log/pgdsn_agt_15012.trc:

Exiting hgocont, rc=28500 at 2021/12/23-14:57:07 with error ptr FILE:hgocont.c LINE:2842 ID:Something other than invalid authorization

Cause:

The Oracle RDBMS is using a Unicode characterset:

select * from NLS_DATABASE_PARAMETERS ; 

PARAMETER                     VALUE 
------------------------      ----------------------------------------
NLS_CHARACTERSET              AL32UTF8


Solution:

Add the following to the DG4ODBC parameter file init<dg4odbc>.ora -


HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P1

[oracle@ip-10-2-2-192 admin]$ cat /u01/db/hs/admin/initpgdsn.ora


# This is a sample agent init file that contains the HS parameters that are

# needed for the Database Gateway for ODBC

#

# HS init parameters

#

HS_FDS_CONNECT_INFO = pgdsn

HS_FDS_TRACE_LEVEL = DEBUG

HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so

# Avoid "[" displayed after "ORA-28500"

HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P1



#

# ODBC specific environment variables

#

set ODBCINI=/etc/odbc.ini

Wednesday, December 15, 2021

Set hostname in AWS EC2

  cat << EOD >> /etc/cloud/cloud.cfg.d/99_hostname.cfg

 #cloud-config

hostname: my-node2

fqdn: my-node2.mydomain.com

EOD


## in case of selinux enabled:

cd /etc/cloud/cloud.cfg.d/

chown root: 99_hostname.cfg

chmod 644 99_hostname.cfg

chcon system_u:object_r:etc_t:s0 99_hostname.cfg


# reboot


Monday, November 29, 2021

OPERATIONSQL_REDOSQL_UNDOINFO
DDLcreate table t (id number(8,0), c clob); USER DDL (PlSql=0 RecDep=0)
INSERTinsert into "ADMIN"."T"("ID","C") values ('1',EMPTY_CLOB());delete from "ADMIN"."T" where "ID" = '1'; 
UPDATEupdate "ADMIN"."T" set "C" = 'a' where "ID" = '1';update "ADMIN"."T" set "C" = NULL where "ID" = '1'; 
INSERTinsert into "ADMIN"."T"("ID","C") values ('2',EMPTY_CLOB());delete from "ADMIN"."T" where "ID" = '2'; 
UPDATEupdate "ADMIN"."T" set "C" = 'b' where "ID" = '2';update "ADMIN"."T" set "C" = NULL where "ID" = '2'; 
INSERTinsert into "ADMIN"."T"("ID","C") values ('3',EMPTY_CLOB());delete from "ADMIN"."T" where "ID" = '3'; 
UPDATEupdate "ADMIN"."T" set "C" = 'c' where "ID" = '3';update "ADMIN"."T" set "C" = NULL where "ID" = '3'; 
UPDATEupdate "ADMIN"."T" set "C" = 'c1' where "ID" = '3';update "ADMIN"."T" set "C" = NULL where "ID" = '3'; 
UPDATEupdate "ADMIN"."T" set "C" = 'b1' where "ID" = '2';update "ADMIN"."T" set "C" = NULL where "ID" = '2'; 
INSERTinsert into "ADMIN"."T"("ID","C") values ('4',EMPTY_CLOB());delete from "ADMIN"."T" where "ID" = '4'; 
UPDATEupdate "ADMIN"."T" set "C" = NULL where "ID" = '4';update "ADMIN"."T" set "C" = NULL where "ID" = '4'; 
INSERTinsert into "ADMIN"."T"("ID","C") values ('5',EMPTY_CLOB());delete from "ADMIN"."T" where "ID" = '5'; 
UPDATEupdate "ADMIN"."T" set "C" = NULL where "ID" = '5';update "ADMIN"."T" set "C" = NULL where "ID" = '5'; 
UPDATEupdate "ADMIN"."T" set "C" = EMPTY_CLOB() where "ID" = '4';update "ADMIN"."T" set "C" = NULL where "ID" = '4'; 
SEL_LOB_LOCATORDECLARE 
 loc_c CLOB; 
 buf_c VARCHAR2(6156); 
 loc_b BLOB; 
 buf_b RAW(6156); 
 loc_nc NCLOB; 
 buf_nc NVARCHAR2(6156); 
BEGIN
 select "C" into loc_c from "ADMIN"."T" where "ID" = '4' for update;
 LOB sql_redo not re-executable
INTERNAL   
LOB_WRITE
 buf_c := '464646464646464646464646464646464646464646464646464646464646464646446464. <modified for better reading>..46464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464664646464646'; 
  dbms_lob.write(loc_c, 1022, 1, buf_c);
 LOB sql_redo not re-executable
LOB_WRITE
 buf_c :=  '464646464646464646464646464646464646464646464646464646464646464646446464. <modified for better reading>..46464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464664646464646'; 
  dbms_lob.write(loc_c, 1022, 1023, buf_c);
 LOB sql_redo not re-executable
LOB_WRITE
 buf_c :=  '464646464646464646464646464646464646464646464646464646464646464646446464. <modified for better reading>..46464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464664646464646'; 
  dbms_lob.write(loc_c, 1022, 2045, buf_c);
 LOB sql_redo not re-executable
LOB_WRITE
 buf_c :=  '464646464646464646464646464646464646464646464646464646464646464646446464. <modified for better reading>..46464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464664646464646'; 
  dbms_lob.write(loc_c, 934, 3067, buf_c);
 LOB sql_redo not re-executable
INTERNAL   
INTERNALEND;

  
UPDATEupdate "ADMIN"."T" set "C" = EMPTY_CLOB() where "ID" = '5';update "ADMIN"."T" set "C" = NULL where "ID" = '5'; 
SEL_LOB_LOCATORDECLARE 
 loc_c CLOB; 
 buf_c VARCHAR2(6156); 
 loc_b BLOB; 
 buf_b RAW(6156); 
 loc_nc NCLOB; 
 buf_nc NVARCHAR2(6156); 
BEGIN
 select "C" into loc_c from "ADMIN"."T" where "ID" = '5' for update;
 LOB sql_redo not re-executable
INTERNAL   
LOB_WRITE
 buf_c :=  '464646464646464646464646464646464646464646464646464646464646464646446464. <modified for better reading>..46464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464664646464646'; 
 LOB sql_redo not re-executable
LOB_WRITE
 buf_c :=  '464646464646464646464646464646464646464646464646464646464646464646446464. <modified for better reading>..46464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464664646464646'; 
 LOB sql_redo not re-executable
LOB_WRITE
 buf_c :=  '464646464646464646464646464646464646464646464646464646464646464646446464. <modified for better reading>..46464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464664646464646'; 
  dbms_lob.write(loc_c, 1022, 2045, buf_c);
 LOB sql_redo not re-executable
LOB_WRITE
 buf_c :=  '464646464646464646464646464646464646464646464646464646464646464646446464. <modified for better reading>..46464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464664646464646'; 
  dbms_lob.write(loc_c, 964, 3067, buf_c);
 LOB sql_redo not re-executable
LOB_WRITE
 buf_c :=  '464646464646464646464646464646464646464646464646464646464646464646446464. <modified for better reading>..46464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464664646464646'; 
  dbms_lob.write(loc_c, 1022, 4031, buf_c);
 LOB sql_redo not re-executable
LOB_WRITE
 buf_c :=  '464646464646464646464646464646464646464646464646464646464646464646446464. <modified for better reading>..46464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464664646464646'; 
  dbms_lob.write(loc_c, 1022, 5053, buf_c);
 LOB sql_redo not re-executable
LOB_WRITE
 buf_c :=  '464646464646464646464646464646464646464646464646464646464646464646446464. <modified for better reading>..46464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464664646464646'; 
  dbms_lob.write(loc_c, 1022, 6075, buf_c);
 LOB sql_redo not re-executable
LOB_WRITE
 buf_c :=  '464646464646464646464646464646464646464646464646464646464646464646446464. <modified for better reading>..46464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464664646464646'; 
 LOB sql_redo not re-executable
LOB_WRITE
 buf_c :=  '464646464646464646464646464646464646464646464646464646464646464646446464. <modified for better reading>..46464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464664646464646'; 
  dbms_lob.write(loc_c, 1022, 8061, buf_c);
 LOB sql_redo not re-executable
LOB_WRITE
 buf_c :=  '464646464646464646464646464646464646464646464646464646464646464646446464. <modified for better reading>..46464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464664646464646'; 
  dbms_lob.write(loc_c, 1022, 9083, buf_c);
 LOB sql_redo not re-executable
LOB_WRITE
 buf_c :=  '464646464646464646464646464646464646464646464646464646464646464646446464. <modified for better reading>..46464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464664646464646'; 
  dbms_lob.write(loc_c, 1022, 10105, buf_c);
 LOB sql_redo not re-executable
LOB_WRITE
 buf_c :=  '464646464646464646464646464646464646464646464646464646464646464646446464. <modified for better reading>..46464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464664646464646'; 
 LOB sql_redo not re-executable
LOB_WRITE
 buf_c :=  '464646464646464646464646464646464646464646464646464646464646464646446464. <modified for better reading>..46464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464664646464646'; 
  dbms_lob.write(loc_c, 1022, 12091, buf_c);
 LOB sql_redo not re-executable
LOB_WRITE
 buf_c :=  '464646464646464646464646464646464646464646464646464646464646464646446464. <modified for better reading>..46464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464664646464646'; 
  dbms_lob.write(loc_c, 1022, 13113, buf_c);
 LOB sql_redo not re-executable
LOB_WRITE
 buf_c :=  '464646464646464646464646464646464646464646464646464646464646464646446464. <modified for better reading>..46464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464664646464646'; 
 LOB sql_redo not re-executable
LOB_WRITE
 buf_c :=  '464646464646464646464646464646464646464646464646464646464646464646446464. <modified for better reading>..46464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464664646464646'; 
  dbms_lob.write(loc_c, 964, 15157, buf_c);
 LOB sql_redo not re-executable
LOB_WRITE
 buf_c :=  '464646464646464646464646464646464646464646464646464646464646464646446464. <modified for better reading>..46464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464664646464646'; 
  dbms_lob.write(loc_c, 1022, 16121, buf_c);
 LOB sql_redo not re-executable
LOB_WRITE
 buf_c :=  '464646464646464646464646464646464646464646464646464646464646464646446464. <modified for better reading>..46464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464664646464646'; 
  dbms_lob.write(loc_c, 1022, 17143, buf_c);
 LOB sql_redo not re-executable
LOB_WRITE
 buf_c :=  '464646464646464646464646464646464646464646464646464646464646464646446464. <modified for better reading>..46464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464664646464646'; 
  dbms_lob.write(loc_c, 1022, 18165, buf_c);
 LOB sql_redo not re-executable
LOB_WRITE
 buf_c :=  '464646464646464646464646464646464646464646464646464646464646464646446464. <modified for better reading>..46464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464664646464646';
  dbms_lob.write(loc_c, 964, 19187, buf_c);
 LOB sql_redo not re-executable
LOB_WRITE
 buf_c :=  '464646464646464646464646464646464646464646464646464646464646464646446464. <modified for better reading>..46464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464664646464646'; 
  dbms_lob.write(loc_c, 1022, 20151, buf_c);
 LOB sql_redo not re-executable
LOB_WRITE
 buf_c :=  '464646464646464646464646464646464646464646464646464646464646464646446464. <modified for better reading>..46464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464664646464646';  
  dbms_lob.write(loc_c, 1022, 21173, buf_c);
 LOB sql_redo not re-executable
LOB_WRITE
 buf_c :=  '464646464646464646464646464646464646464646464646464646464646464646446464. <modified for better reading>..46464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464664646464646';  
  dbms_lob.write(loc_c, 1022, 22195, buf_c);
 LOB sql_redo not re-executable
LOB_WRITE
 buf_c :=  '464646464646464646464646464646464646464646464646464646464646464646446464. <modified for better reading>..46464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464664646464646'; 
  dbms_lob.write(loc_c, 964, 23217, buf_c);
 LOB sql_redo not re-executable
LOB_WRITE
 buf_c :=  '464646464646464646464646464646464646464646464646464646464646464646446464. <modified for better reading>..46464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464664646464646'; 
  dbms_lob.write(loc_c, 1022, 24181, buf_c);
 LOB sql_redo not re-executable
LOB_WRITE
 buf_c :=  '464646464646464646464646464646464646464646464646464646464646464646446464. <modified for better reading>..46464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464664646464646'; 
  dbms_lob.write(loc_c, 1022, 25203, buf_c);
 LOB sql_redo not re-executable
LOB_WRITE
 buf_c :=  '464646464646464646464646464646464646464646464646464646464646464646446464. <modified for better reading>..46464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464664646464646'; 
  dbms_lob.write(loc_c, 1022, 26225, buf_c);
 LOB sql_redo not re-executable
LOB_WRITE
 buf_c := '464646464646464646464646464646464646464646464646464646464646464646446464. <modified for better reading>..46464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464664646464646'; 
  dbms_lob.write(loc_c, 964, 27247, buf_c);
 LOB sql_redo not re-executable
LOB_WRITE
 buf_c :=  '464646464646464646464646464646464646464646464646464646464646464646446464. <modified for better reading>..46464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464664646464646'; 
  dbms_lob.write(loc_c, 1022, 28211, buf_c);
 LOB sql_redo not re-executable
LOB_WRITE
 buf_c :=  '464646464646464646464646464646464646464646464646464646464646464646446464. <modified for better reading>..46464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464664646464646'; 
  dbms_lob.write(loc_c, 1022, 29233, buf_c);
 LOB sql_redo not re-executable
LOB_WRITE
 buf_c :=  '464646464646464646464646464646464646464646464646464646464646464646446464. <modified for better reading>..46464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464664646464646'; 
  dbms_lob.write(loc_c, 1022, 30255, buf_c);
 LOB sql_redo not re-executable
LOB_WRITE
 buf_c :=  '464646464646464646464646464646464646464646464646464646464646464646446464. <modified for better reading>..46464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464664646464646'; 
  dbms_lob.write(loc_c, 964, 31277, buf_c);
 LOB sql_redo not re-executable
LOB_WRITE
 buf_c :=  '464646464646464646464646464646464646464646464646464646464646464646446464. <modified for better reading>..46464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464664646464646'; 
  dbms_lob.write(loc_c, 527, 32241, buf_c);
 LOB sql_redo not re-executable
INTERNAL   
INTERNALEND;

  


EXEC rdsadmin.rdsadmin_util.force_logging(p_enable => true);

begin

    rdsadmin.rdsadmin_util.alter_supplemental_logging(

        p_action => 'ADD');

end;

/

begin

    rdsadmin.rdsadmin_util.alter_supplemental_logging(

        p_action => 'ADD',

        p_type   => 'PRIMARY KEY');

end;

/


begin

    rdsadmin.rdsadmin_util.set_configuration(

        name  => 'archivelog retention hours',

        value => '24');

end;

/

commit;


drop table t purge;


select current_scn from v$database; --759308


create table t (id number(8,0), c clob);

insert into t values(1,'a');

commit;

insert into t values(2,'b');

commit;

insert into t values(3,'c');

commit;

update t set c='c1' where id=3;

commit;

update t set c='b1' where to_char(c)='b';

commit;


select * from t;


insert into t values(4,'');

insert into t values(5,'');

commit;



declare

  inbuf1  raw(3964); -- 3964 still possible for in-line lob

  inbuf2  raw(20000);

begin

  inbuf1 := utl_raw.cast_to_raw(rpad('FF',3964,'FF'));

  update t set c = inbuf1 where id=4;

  commit;

  inbuf2 := utl_raw.cast_to_raw(rpad('FF',20000,'FF'));

  update t set c = inbuf2 where id=5;

  commit;

end;

/


select length(c) from t;




select current_scn from v$database; --759455



execute dbms_logmnr_d.build(options => dbms_logmnr_d.store_in_redo_logs);


alter session set nls_date_format='yyyy-mon-dd hh24:mi:ss';


select * from v$log;



/rdsdbdata/db/MYTEST_A/onlinelog/o1_mf_4_jt9fqdq7_.log

/rdsdbdata/db/MYTEST_A/onlinelog/o1_mf_3_jt9fqcqr_.log

/rdsdbdata/db/MYTEST_A/onlinelog/o1_mf_2_jt9fqc7g_.log

/rdsdbdata/db/MYTEST_A/onlinelog/o1_mf_1_jt9fqbox_.log


select * from v$archived_log where next_change#>759308 and first_change#<759455 ;



execute dbms_logmnr.add_logfile(logfilename => '/rdsdbdata/db/MYTEST_A/arch/redolog-24-1-1089890283.arc', options => dbms_logmnr.new);

execute dbms_logmnr.add_logfile(logfilename => '/rdsdbdata/db/MYTEST_A/onlinelog/o1_mf_3_jt9fqcqr_.log', options => dbms_logmnr.addfile);

execute dbms_logmnr.add_logfile(logfilename => '/rdsdbdata/db/MYTEST_A/onlinelog/o1_mf_1_jt9fqbox_.log', options => dbms_logmnr.addfile);

execute dbms_logmnr.add_logfile(logfilename => '/rdsdbdata/db/MYTEST_A/onlinelog/o1_mf_2_jt9fqc7g_.log', options => dbms_logmnr.addfile);

execute dbms_logmnr.add_logfile(logfilename => '/rdsdbdata/db/MYTEST_A/onlinelog/o1_mf_4_jt9fqdq7_.log', options => dbms_logmnr.addfile);


begin

  dbms_logmnr.start_logmnr(

   startSCN=> 759308,

   endSCN=> 759455, 

   options => dbms_logmnr.ddl_dict_tracking + dbms_logmnr.dict_from_redo_logs 

   + dbms_logmnr.no_rowid_in_stmt);

end;

/


drop table my_logmnr_contents purge;

create table my_logmnr_contents tablespace users as select * from v$logmnr_contents;


select count(*) from my_logmnr_contents;


select operation,sql_redo,sql_undo,info from my_logmnr_contents where seg_owner='ADMIN' and seg_name='T';