Correcting Row Chaining by using Larger Block Size

 Correcting Row Chaining by using Larger Block Size

• Row chaining Scenario Creation :

SQL> DROP TABLE CUST_ORDERS;

Table dropped.

SQL> DROP TABLE CUST;

Table dropped.

SQL>

SQL> set timing on

SQL> CREATE TABLE SOE.CUST

  2  ( CUSTOMER_NO    NUMBER(6),

  3   FIRST_NAME VARCHAR2(40),

  4   LAST_NAME  VARCHAR2(40),

  5   NOTE1 VARCHAR2(4000),

  6   NOTE2 VARCHAR2(4000)

  7  ) TABLESPACE SOETBS;

Table created.

Elapsed: 00:00:00.00

SQL>

SQL> INSERT INTO SOE.CUST

  2  SELECT LEVEL AS CUSTOMER_NO,

  3         DBMS_RANDOM.STRING('A', 40) FIRST_NAME,

  4         DBMS_RANDOM.STRING('A', 40) LAST_NAME,

  5         DBMS_RANDOM.STRING('A', 4000) NOTE1,

  6         DBMS_RANDOM.STRING('A', 4000) NOTE2

  7  FROM DUAL

  8  CONNECT BY LEVEL <= 10000;

10000 rows created.

Elapsed: 00:00:52.86

SQL> set timing off

SQL> COMMIT;

Commit complete.

SQL>

SQL> CREATE INDEX CUST_NO_IDX ON CUST(CUSTOMER_NO) NOLOGGING TABLESPACE SOETBS;

Index created.

• Analyze Table CUST

SQL> ANALYZE TABLE CUST COMPUTE STATISTICS;

Table analyzed.

• Check row chaining using query below. 

SQL> SELECT CHAIN_CNT,

  2         ROUND(CHAIN_CNT/NUM_ROWS*100,2) CHAIN_PCT,

  3         AVG_ROW_LEN, PCT_FREE , PCT_USED, BLOCKS

  4    FROM USER_TABLES

  5  WHERE TABLE_NAME = 'CUST';

 CHAIN_CNT  CHAIN_PCT AVG_ROW_LEN   PCT_FREE   PCT_USED     BLOCKS

---------- ---------- ----------- ---------- ---------- ----------

     10000        100        8101         10                 20048

• Connect database check and create tablespace with db_block_size=16|32. In our case it is window machine we are getting issue by increasing 32 so we use the value to 16.

C:\Users\varun>sqlplus sys/system123@pdb as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Jan 4 23:03:30 2021

Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

SQL> SELECT VALUE FROM V$PARAMETER WHERE NAME = 'db_block_size';

VALUE

--------------------------------------------------------------------------------

8192

• Testing curresnt session CPU used by this session and DB time 

SQL> @ test_queries.sql 10000

System altered.

System altered.

PL/SQL procedure successfully completed.

Table created.

old   4: FOR I IN 1..&1 LOOP

new   4: FOR I IN 1..10000 LOOP

PL/SQL procedure successfully completed.

Table created.

MYSTAT

--------------------------------------------------------------------------------

CPU used by this session: 96

DB time: 651

table fetch continued row: 0

Table dropped.

Table dropped.

• Set DB_16K_CACHE_SIZE scope is memory 

SQL> ALTER SYSTEM SET DB_16K_CACHE_SIZE= 160M;

System altered.

SQL> SELECT NAME, BLOCK_SIZE, CURRENT_SIZE FROM V$BUFFER_POOL;

NAME                 BLOCK_SIZE CURRENT_SIZE

-------------------- ---------- ------------

DEFAULT                    8192         3552

DEFAULT                   16384          160

• Create Tablespace TBS16K with block size 16K

SQL> CREATE TABLESPACE TBS16K DATAFILE 'C:\APP\VARUN\ORADATA\ORCL\PDB\tbs16k.dbf' SIZE 160M AUTOEXTEND ON MAXSIZE 1000M BLOCKSIZE 16K;

Tablespace created.

• Set Quota to schema on tablespace

SQL> ALTER USER SOE QUOTA UNLIMITED ON TBS16K;

User altered.

SQL> conn soe/soe@pdb

Connected.

• Move table cust to new tablespace

SQL> ALTER TABLE CUST MOVE TABLESPACE tbs16k;

Table altered.

SQL> ALTER INDEX CUST_NO_IDX REBUILD;

Index altered.

• Analyze and check status

SQL> ANALYZE TABLE CUST COMPUTE STATISTICS;

Table analyzed.

SQL> SELECT CHAIN_CNT,

  2         ROUND(CHAIN_CNT/NUM_ROWS*100,2) CHAIN_PCT,

  3         AVG_ROW_LEN, PCT_FREE , PCT_USED, BLOCKS

  4    FROM USER_TABLES

  5  WHERE TABLE_NAME = 'CUST';

 CHAIN_CNT  CHAIN_PCT AVG_ROW_LEN   PCT_FREE   PCT_USED     BLOCKS

---------- ---------- ----------- ---------- ---------- ----------

         0          0        8095         10                 10090

• Testing curresnt session CPU used by this session and DB time

SQL> @ test_queries.sql 10000

System altered.

System altered.

PL/SQL procedure successfully completed.

Table created.

old   4: FOR I IN 1..&1 LOOP

new   4: FOR I IN 1..10000 LOOP

PL/SQL procedure successfully completed.

Table created.

MYSTAT

--------------------------------------------------------------------------------

CPU used by this session: 81

DB time: 544

table fetch continued row: 0

Table dropped.

Table dropped.

SQL>