Thursday, March 17, 2022

Vacuum vs Vacuum freeze impact on visibility map in PostgreSQL

 PostgreSQL use visibility map (VM) to determine whether all tuples in the page are visible and whether further vacuum effort needed. (Reference: pg_visibility)

  • The all-visible bit in the visibility map indicates that every tuple in the corresponding page of the relation is visible to every current and future transaction.
  • The all-frozen bit in the visibility map indicates that every tuple in the page is frozen; that is, no future vacuum will need to modify the page until such time as a tuple is inserted, updated, deleted, or locked on that page.

Let's examine in detail.

1. Install the pg_visibility extension.

mytest=> create extension pg_visibility;
CREATE EXTENSION

mytest=> \x
Expanded display is on.
mytest=> \dx
List of installed extensions
-[ RECORD 1 ]-----------------------------------
Name        | pg_visibility
Version     | 1.2
Schema      | public
Description | examine the visibility map (VM) and page-level visibility info
-[ RECORD 2 ]------------------------------------
Name        | plpgsql
Version     | 1.0
Schema      | pg_catalog
Description | PL/pgSQL procedural language
mytest=> \x
Expanded display is on.

mytest=> \df pg_visibility_map
List of functions
-[ RECORD 1 ]-------+---------------------------
Schema              | public
Name                | pg_visibility_map
Result data type    | record
Argument data types | regclass, blkno bigint, OUT all_visible boolean, OUT all_frozen boolean
Type                | func
-[ RECORD 2 ]-------+---------------------------
Schema              | public
Name                | pg_visibility_map
Result data type    | SETOF record
Argument data types | regclass, OUT blkno bigint, OUT all_visible boolean, OUT all_frozen boolean
Type                | func

2. Create sample table (t) and insert testing data

mytest=> create table t (id int,name varchar(10));
CREATE TABLE
mytest=> insert into t values(1,'a'),(2,'b');
INSERT 0 2

3. Check the VM before vacuum

mytest=> select pg_visibility_map('t'::regclass);
 pg_visibility_map
-------------------
 (0,f,f)
(1 row)

mytest=> select age(relfrozenxid) from pg_class where relname='t';
 age
-----
  16
(1 row)

Findings:

  • blkno: 0
  • all_visible: f
  • all_frozen: f
  • relfrozenxid age: 16 (The number may vary based on your database workload)

4. Run the Vacuum, and check VM, "all_visible" shall be updated.

mytest=> vacuum (verbose) t;
INFO:  vacuuming "public.t"
INFO:  "t": found 0 removable, 2 nonremovable row versions in 1 out of 1 pages
DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 598
There were 0 unused item identifiers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
VACUUM
mytest=> select pg_visibility_map('t'::regclass);
 pg_visibility_map
-------------------
 (0,t,f)
(1 row)

mytest=> select age(relfrozenxid) from pg_class where relname='t';
 age
-----
  16
(1 row)

Findings:

  • blkno: 0
  • all_visible: t
  • all_frozen: f
  • relfrozenxid age: 16

5. Run the Vacuum with "freeze=true", and check VM, "all_frozen" shall be updated. relfrozenxid age reduced as well.

mytest=> vacuum (verbose,freeze) t;

INFO:  aggressively vacuuming "public.t"
INFO:  "t": found 0 removable, 2 nonremovable row versions in 1 out of 1 pages
DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 599
There were 0 unused item identifiers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
VACUUM
mytest=> select pg_visibility_map('t'::regclass);
 pg_visibility_map
-------------------
 (0,t,t)
(1 row)

mytest=> select age(relfrozenxid) from pg_class where relname='t';
 age
-----
   0
(1 row)
  • blkno: 0
  • all_visible: t
  • all_frozen: t
  • relfrozenxid age: 0

Sunday, March 13, 2022

CHECKSUM function in Oracle 21c, be aware of these surprises

 In Oracle 21c, CHECKSUM was added as a new analytical function, with below purpose stated in Oracle documentation:

Use CHECKSUM to detect changes in a table. The order of the rows in the table does not affect the result.

Let's try it out.

Setup the testing data. These 2 tables purposely populated with different datasets.

create table t1 (id number, c1 varchar2(10));
insert into t1 values (1,'a');
insert into t1 values (2,'b');
commit;

create table t2 (id number, c1 varchar2(10));
insert into t2 values (1,'b');
insert into t2 values (2,'a');
commit;
SQL> select * from t1;

   ID    C1
_____ _____
    1 a
    2 b

SQL> select * from t2;

   ID    C1
_____ _____
    1 b
    2 a

Finding 1: regardless the data setup is NOT the same, the check is same, as checksum computed without considering data ordering.

SQL> select checksum(id),checksum(c1) from t1;

   CHECKSUM(ID)    CHECKSUM(C1)
_______________ _______________
         778195            3783

SQL> select checksum(id),checksum(c1) from t2;

   CHECKSUM(ID)    CHECKSUM(C1)
_______________ _______________
         778195            3783

Finding 2: if the data not same regardless ordering, the checksum will be different.

SQL> update t2 set c1='A' where id=2;

1 row updated.

SQL> commit;

Commit complete.

SQL> select checksum(id),checksum(c1) from t2;

   CHECKSUM(ID)    CHECKSUM(C1)
_______________ _______________
         778195          585598

Finding 3: checksum return "0" for some test cases, below is one example.

Observation: pair of repeating values generated value "0", canceling the effect of checksum. For example, three values 'a' have the same effect as single value 'a'

SQL> insert into t1 values (1,'a');

1 row inserted.

SQL> insert into t1 values (2,'b');

1 row inserted.

SQL> commit;

Commit complete.

SQL> select * from t1;

   ID    C1
_____ _____
    1 a
    2 b
    1 a
    2 b


SQL> select checksum(id),checksum(c1) from t1;

   CHECKSUM(ID)    CHECKSUM(C1)
_______________ _______________
              0               0

Saturday, March 12, 2022

SQL Macros in 21c (backported to 19.6 onwards in 19c)

 

SQL Macros provides the benefits of Pl/SQL to abstract the complexity of logic, while eliminates context switches between SQL and Pl/SQL processing engine.

Example to re-write the PL/SQL into SQL Macros Scalar Expression:

-- PL/SQL Version create or replace function get_full_name (first varchar2,last varchar2) return varchar2 is begin return first||','||last; end; /
-- SQL Macros Version create or replace function get_full_name_sqm (first varchar2,last varchar2) return varchar2 sql_macro(scalar) is begin return q'(first||','||last)'; end; /

Example to re-write the View into SQL Macros Table Expression. In this way, Oracle Optimizer can transform original code into optimal execution plans.

-- View Version create or replace view v_employees as select employee_id,salary, avg(salary) over(partition by department_id) avg_salary from hr.employees;
-- SQL Macros Version create or replace function f_employees return varchar2 sql_macro is begin return q'( select employee_id,salary, avg(salary) over(partition by department_id) avg_salary from hr.employees )'; end; /
SQL> explain plan for select * from v_employees where employee_id=107; Explained. SQL> select * from table(dbms_xplan.display(format => 'advanced')); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------ Plan hash value: 3819948069 ----------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 107 | 4173 | 4 (25)| 00:00:01 | |* 1 | VIEW | V_EMPLOYEES | 107 | 4173 | 4 (25)| 00:00:01 | | 2 | WINDOW SORT | | 107 | 1177 | 4 (25)| 00:00:01 | | 3 | TABLE ACCESS FULL| EMPLOYEES | 107 | 1177 | 3 (0)| 00:00:01 | ----------------------------------------------------------------------------------- PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------ Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$2 / "V_EMPLOYEES"@"SEL$1" 2 - SEL$2 3 - SEL$2 / "EMPLOYEES"@"SEL$2" Outline Data ------------- /*+ PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------ BEGIN_OUTLINE_DATA FULL(@"SEL$2" "EMPLOYEES"@"SEL$2") NO_ACCESS(@"SEL$1" "V_EMPLOYEES"@"SEL$1") OUTLINE_LEAF(@"SEL$1") OUTLINE_LEAF(@"SEL$2") ALL_ROWS DB_VERSION('21.1.0') OPTIMIZER_FEATURES_ENABLE('21.1.0') IGNORE_OPTIM_EMBEDDED_HINTS END_OUTLINE_DATA */ PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("EMPLOYEE_ID"=107) Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - (rowset=256) "EMPLOYEE_ID"[NUMBER,22], "V_EMPLOYEES"."SALARY"[NUMBER,22], "V_EMPLOYEES"."AVG_SALARY"[NUMBER,22] PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------ 2 - (#keys=1; rowset=256) "DEPARTMENT_ID"[NUMBER,22], "EMPLOYEE_ID"[NUMBER,22], "SALARY"[NUMBER,22], AVG("SALARY") OVER ( PARTITION BY "DEPARTMENT_ID")[22] 3 - (rowset=256) "EMPLOYEE_ID"[NUMBER,22], "SALARY"[NUMBER,22], "DEPARTMENT_ID"[NUMBER,22] Query Block Registry: --------------------- SEL$1 (PARSER) [FINAL] SEL$2 (PARSER) [FINAL] PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------ 56 rows selected.
SQL> explain plan for select * from f_employees() where employee_id=107; Explained. SQL> select * from table(dbms_xplan.display(format => 'advanced')); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------ Plan hash value: 720055818 --------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 107 | 4173 | 4 (25)| 00:00:01 | |* 1 | VIEW | | 107 | 4173 | 4 (25)| 00:00:01 | | 2 | WINDOW SORT | | 107 | 1177 | 4 (25)| 00:00:01 | | 3 | TABLE ACCESS FULL| EMPLOYEES | 107 | 1177 | 3 (0)| 00:00:01 | --------------------------------------------------------------------------------- PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------ Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$3 / "SYS__$"@"SEL$2" 2 - SEL$3 3 - SEL$3 / "EMPLOYEES"@"SEL$3" Outline Data ------------- /*+ PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------ BEGIN_OUTLINE_DATA FULL(@"SEL$3" "EMPLOYEES"@"SEL$3") NO_ACCESS(@"SEL$F5BB74E1" "SYS__$"@"SEL$2") OUTLINE(@"SEL$2") OUTLINE(@"SEL$1") MERGE(@"SEL$2" >"SEL$1") OUTLINE_LEAF(@"SEL$F5BB74E1") OUTLINE_LEAF(@"SEL$3") ALL_ROWS DB_VERSION('21.1.0') OPTIMIZER_FEATURES_ENABLE('21.1.0') PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------ IGNORE_OPTIM_EMBEDDED_HINTS END_OUTLINE_DATA */ Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("SYS__$"."EMPLOYEE_ID"=107) Column Projection Information (identified by operation id): ----------------------------------------------------------- PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------ 1 - (rowset=256) "SYS__$"."EMPLOYEE_ID"[NUMBER,22], "SYS__$"."SALARY"[NUMBER,22], "SYS__$"."AVG_SALARY"[NUMBER,22] 2 - (#keys=1; rowset=256) "DEPARTMENT_ID"[NUMBER,22], "EMPLOYEE_ID"[NUMBER,22], "SALARY"[NUMBER,22], AVG("SALARY") OVER ( PARTITION BY "DEPARTMENT_ID")[22] 3 - (rowset=256) "EMPLOYEE_ID"[NUMBER,22], "SALARY"[NUMBER,22], "DEPARTMENT_ID"[NUMBER,22] Query Block Registry: --------------------- PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------ SEL$1 (PARSER) SEL$F5BB74E1 (VIEW MERGE SEL$1 ; SEL$2) [FINAL] SEL$2 (PARSER) SEL$3 (PARSER) [FINAL] 61 rows selected.

21c new feature: Using Expressions in Initialization Parameters

 

Using Expressions in Initialization Parameters

In this post, we will run through how to set parameter in initialization parameters when they depend on environmental characteristics, such as system configurations, run-time decisions, or the values of other parameters by using expressions.

Objective: Set SGA_MAX_SIZE=4096M, and set SGX_TARGET to 80% of SGA_MAX_SIZE or 1024M, whichever is larger.

Implement the initial changes

SQL> alter system set sga_max_size='4096*1024*1024' scope=spfile; System altered. SQL> alter system set sga_target='max(1024*1024*1024, sga_max_size*0.8)' scope=spfile; System altered.

Reboot the instance and verify the change

SQL> shutdown immediate SQL> startup SQL> show parameter sga_max_size NAME TYPE VALUE ------------ ----------- ----- sga_max_size big integer 4G SQL> show parameter sga_target NAME TYPE VALUE ---------- ----------- ----- sga_target big integer 3280M

Change SGA_MAX_SIZE only to observe impact on SGA_TARGET

SQL> alter system set sga_max_size=3G scope=spfile; System SET altered. SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 3221225384 bytes Fixed Size 9691048 bytes Variable Size 1191182336 bytes Database Buffers 2013265920 bytes Redo Buffers 7086080 bytes Database mounted. Database opened. SQL> show parameter sga_max_size NAME TYPE VALUE ------------ ----------- ----- sga_max_size big integer 3G SQL> show parameter sga_target NAME TYPE VALUE ---------- ----------- ----- sga_target big integer 2464M

How does it work internally

The formula stored in spfile and processed during instance startup.

SQL> show parameter spfile NAME TYPE VALUE -------- ------ ------------------------------------- spfile string /u01/app/oracle/dbs/spfileorcl21c.ora
[oracle@ol8 dbs]$ strings /u01/app/oracle/dbs/spfileorcl21c.ora|egrep "sga_max_size|sga_target" orcl21c.__sga_target=2583691264 *.sga_max_size=3221225472 *.sga_target=MAX(((1024 * 1024) * 1024) , (sga_max_size * 0.8))

How to enacle datapump trace using trace flag

 

[oracle@ol8 ~]$ expdp donghua/Password@pdbn1 metrics=y logtime=all reuse_dumpfiles=y trace=1FF0300
[oracle@ol8 ~]$ ls -ltr /u01/app/oracle/diag/rdbms/orcl21c/orcl21c/trace/*dw*.trc /u01/app/oracle/diag/rdbms/orcl21c/orcl21c/trace/*dm*.trc -rw-r----- 1 oracle oinstall 1937397 Mar 12 16:18 /u01/app/oracle/diag/rdbms/orcl21c/orcl21c/trace/orcl21c_dw00_8803.trc -rw-r----- 1 oracle oinstall 726755 Mar 12 16:18 /u01/app/oracle/diag/rdbms/orcl21c/orcl21c/trace/orcl21c_dm00_8787.trc

Export/Import DataPump Parameter TRACE - How to Diagnose Oracle Data Pump (Doc ID 286496.1)

Oracle recreate user without knowing password

For pure testing purpose, we will use a dummy password. The same procedure tested in Oracle 21c, high chance it will work in other versions as well.

SQL> alter user donghua2 identified by "OneTimePassword_DemoOnly";

User DONGHUA2 altered.

Retrieve the encoded password. There are parts starting with "S:" and "T:".

S:

  • Total 60 characters = 30 bytes
    • Password hash (20 bytes) = sha1(password + salt (10 bytes))
    • salt (10 bytes) (B09C6257E62C93E07E10 in below example)
  • Based on SHA1

T:

  • From 12.1.0.2 onwards
  • Total 160 characters = 80 bytes
  • Based on PBKDF2-based SHA512 hashing algorithm
  • Used to enforce 12c only client through "SQLNET.ALLOWED_LOGON_VERSION_SERVER".

Other password part like "H" is possible, to enforce XDB authentication.

SQL> select spare4 from user$ where name='DONGHUA2';

SPARE4
--------------------------------------------------------------------
S:6D31F29927DDC0C2032ED7847DA99227F647EE5BB09C6257E62C93E07E10;T:F9D263BC89140FE935283431FB4D63FE2A055C3C32B85C9AB3347C38CDEE3BB374086798825867E4FF9D662B256AA22ABED4EA0B3F43A1921706E10B0EAE0953B32BA71D93E35ACD258D9C83EC9579F7

Let's recreate the user using the password hash, without knowing the password. This is the same way datapump used to recreate users.

SQL> create user donghua3 identified by VALUES 'S:6D31F29927DDC0C2032ED7847DA99227F647EE5BB09C6257E62C93E07E10;T:F9D263BC89140FE935283431FB4D63FE2A055C3C32B85C9AB3347C38CDEE3BB374086798825867E4FF9D662B256AA22ABED4EA0B3F43A1921706E10B0EAE0953B32BA71D93E35ACD258D9C83EC9579F7';

User DONGHUA3 created.

Grant privilege to allow created user to logon database, and verify whether old password works for it:

SQL> grant create session to donghua3;
Grant succeeded.

SQL> conn donghua3/OneTimePassword_DemoOnly@pdbn1
Connected.

Further more, we can use only "S:" part of the hash to recreate the user, internally database will setup the password hash with both "S:" and "T:".

SQL> alter user donghua3 identified by VALUES 'S:6D31F29927DDC0C2032ED7847DA99227F647EE5BB09C6257E62C93E07E10';
User DONGHUA3 altered.

SQL> conn donghua3/OneTimePassword_DemoOnly@pdbn1
Connected.