Skip to main content

Search

Welcome to Tibero GTS!

Correlation between HighWaterMark and Table Fullscan

Document Type | Technical Information

Category | Tuning

Applicable Product Version | 7FS04PS

Document Number | TTUTI026

Overview

When performing a Table Fullscan, all blocks up to the HWM (High Water Mark) point are fully scanned regardless of the amount of data. Due to this operation method, tables with many deletes cause inefficiency because even though there are many empty blocks, all blocks must be scanned during the Fullscan. 
Therefore, for tables with frequent delete operations, periodic ReOrg should be performed to prevent inefficiency caused by Fullscan.

 

Method

Scenario

Case 1. High HWM due to data deletion

Data Input

[tibero@kimmi ~]$ tbsql sys/tibero

tbSQL 7

TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.

Connected to Tibero.

SQL> DROP TABLE FULL_SCAN_TEST;

TBR-7071: Schema object 'SYS.FULL_SCAN_TEST' was not found or is invalid.

SQL> CREATE TABLE FULL_SCAN_TEST(A NUMBER, B VARCHAR(15), C DATE);

Table 'FULL_SCAN_TEST' created.

SQL> INSERT INTO FULL_SCAN_TEST SELECT LEVEL, 'DELETE DATA', SYSDATE FROM DUAL CONNECT BY LEVEL <= 1000000;  -- Insert large amount of data to delete

1000000 rows inserted.

SQL> INSERT INTO FULL_SCAN_TEST SELECT LEVEL, 'HWM DATA', SYSDATE FROM DUAL CONNECT BY LEVEL <= 100; -- Data to check block count after deletion

100 rows inserted.

COMMIT;

Commit completed.

 

Data Query

SQL> SET ROWS OFF
SQL> SET AUTOT ON EXP PLANS STAT 
SQL> SELECT /*+ FULL(A) */ * FROM FULL_SCAN_TEST A;

1000100 rows selected.
SQL ID: 9qf0vdc0u1zzy
Child number: 2430
Plan hash value: 3263165585
Execution Plan
--------------------------------------------------------------------------------------------------------------------------------------------------------------
  1  TABLE ACCESS (FULL): FULL_SCAN_TEST (Cost:1930, %%CPU:0, Rows:783672)

Execution Stat
--------------------------------------------------------------------------------------------------------------------------------------------------------------
  1  TABLE ACCESS (FULL): FULL_SCAN_TEST (Time:0. ms, Rows:0, Starts:0)

NAME                                VALUE
------------------------------ ----------
db block gets                         189
consistent gets                      4345
physical reads                          0
redo size                               0
sorts (disk)                            0
sorts (memory)                          0
rows processed                    1000100

Note

consistent gets: For 1,000,100 rows, 4,345 consistent blocks are read.

 

Data Deletion

SQL> DELETE FROM FULL_SCAN_TEST WHERE B='DELETE DATA';
1000000 rows deleted.

SQL> commit;

Commit completed.

 

Data Query

SQL> SELECT /*+ FULL(A) */ * FROM FULL_SCAN_TEST A;
100 rows selected.
SQL ID: 9qf0vdc0u1zzy
Child number: 2430
Plan hash value: 3263165585
Execution Plan
--------------------------------------------------------------------------------------------------------------------------------------------------------------
  1  TABLE ACCESS (FULL): FULL_SCAN_TEST (Cost:1930, %%CPU:0, Rows:783672)

Execution Stat
--------------------------------------------------------------------------------------------------------------------------------------------------------------
  1  TABLE ACCESS (FULL): FULL_SCAN_TEST (Time:0. ms, Rows:0, Starts:0)

NAME                                VALUE
------------------------------ ----------
db block gets                          46
consistent gets                      4344
physical reads                          0
redo size                               0
sorts (disk)                            0
sorts (memory)                          0
rows processed                        100

Note

consistent gets: For 100 rows, the same 4,344 consistent blocks are read as before.

 

Case 2. Low HWM for newly created table

Data Input

[tibero@kimmi ~]$ tbsql sys/tibero

tbSQL 7

TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.

Connected to Tibero.

SQL> DROP TABLE FULL_SCAN_TEST2;

TBR-7071: Schema object 'SYS.FULL_SCAN_TEST2' was not found or is invalid.

SQL> CREATE TABLE FULL_SCAN_TEST2(A NUMBER, B VARCHAR(15), C DATE);

Table 'FULL_SCAN_TEST2' created.

SQL> INSERT INTO FULL_SCAN_TEST2 SELECT LEVEL, 'HWM DATA', SYSDATE FROM DUAL CONNECT BY LEVEL <= 100; -- Insert same data as 100 rows in scenario 1 for comparison

100 rows inserted.

SQL> COMMIT;

Commit completed.

 

Data Query

SQL> SET ROWS OFF
SQL> SET AUTOT ON EXP PLANS STAT
SQL> SELECT /*+ FULL(A) */ * FROM FULL_SCAN_TEST2 A;
100 rows selected.
SQL ID: 4ag60gv78a125
Child number: 2445
Plan hash value: 3262512719
Execution Plan
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  1  TABLE ACCESS (FULL): FULL_SCAN_TEST2 (Cost:12, %%CPU:0, Rows:2759)

Execution Stat
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  1  TABLE ACCESS (FULL): FULL_SCAN_TEST2 (Time:0. ms, Rows:0, Starts:0)

NAME                                VALUE
------------------------------ ----------
db block gets                           6
consistent gets                        15
physical reads                          0
redo size                              76
sorts (disk)                            0
sorts (memory)                          0
rows processed                        100

Note

consistent gets: Because the HWM is low, 15 consistent blocks are read for 100 rows.