ORACLE CONSISTENT GET MECHANISM

As you know there are 2 types of IO operations on Oracle Database

           

A.physical reads: these are done by reading from disk (expensive reads). However, physical reads can also occur on IO devices (such as RAW,ASM) or disk cache.

B.logical reads: these are done by reading the data blocks on buffer cache. (performance reads). There are 2 types of logical reads:

                                                                                                                                                                                                               

                                                               

B.1. Consistent Get: this is a read on buffer cache to access the data in the block which is consistent with a given point time or SCN. Consistent gets will be increased if it needs to use undo segments to apply the uncommited changes.

                               

B.2. DB Block Get(current get): this is a read on buffer cache to access the most up-to-date current copy of the data in the blcok. There can be only one current copy of a block. 

**db block gets are generally used while DML operations

                               

This is a very brief explanation of reads, now I will only try to explain consistent get mechanism.

                                 

What is consistent gets?

Actually, a consistent get is the number of accessing blocks in the buffer cache.

Example;

--create a tablespace with uniform extent size;

create tablespace test_tbs datafile '+DATA' size 100m extent management local uniform size 64k;

--create a table;

create table demo_objects tablespace test_tbs as select * from dba_objects where rownum between 1 and 300;

--gather the statistics

begin

  dbms_stats.gather_table_stats('HR', 'DEMO_OBJECTS', granularity => 'ALL');

end;

--check the number of rows and total number of blocks

select num_rows, blocks from dba_tables where table_name='DEMO_OBJECTS';

       NUM_ROWS     BLOCKS

            300          8

  

--check the data block and number of rows for each block

select dbms_rowid.ROWID_BLOCK_NUMBER(rowid) blkno, count(*) row_cnt

  from demo_objects

 group by dbms_rowid.ROWID_BLOCK_NUMBER(rowid)

 order by 1

              BLKNO      ROW_CNT

                131           76

                132           73

                133           72

                134           70

                135            9

  

--there are total 8 blocks but 5 of them are data blocks. So, what is the rest of 3 blocks?

select extent_id, block_id from dba_extents where segment_name='DEMO_OBJECTS'

       EXTENT_ID      BLOCK_ID

               0           128

               

***our first block is at 128th block in the 0th extent

--header block

select header_block from dba_segments s where s.segment_name='DEMO_OBJECTS'

       HEADER_BLOCK

                130

Question:

if our segment header block is 130, what are the 128th and 129th blocks?

Answer:
They are segment space management blocks

AUTOMATIC SEGMENT SPACE MANAGEMENT BLOCKS (BitMapBlocks)

Before the data blocks, every segment has "some segment management blcoks" to hold some information. When we start to read a table, these blocks also cause some consistent gets.

L1 Leaf   BMB---> always starts from first block at first extent, indicates the free space of blocks, can be more than one

L2 Branch BMB---> contains search hint for first L1 BMB

L3 Root   BMB---> segment header and L3 Root reside in same block

For our “demo_objects” table:

Block# 128------------> L1 BMB

Block# 129------------> L2 BMB

Block# 130------------> L3 BMB and segment header

  

CHECK THE STATISTICS AND CONSISTENT GETS

session#1

---------

select * from demo_objects;

Statistics

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

         1  recursive calls

         0  db block gets

        26  consistent gets

         0  physical reads

         0  redo size

      35259  bytes sent via SQL*Net to client

       760  bytes received via SQL*Net from client

        21  SQL*Net roundtrips to/from client

         0  sorts (memory)

         0  sorts (disk)

       300  rows processed

We have 8 blocks for “demo_objects” table but consistent gets value is 26. So, it means we accessed the blocks multiple times.

Let's make a DML operation from another session (without commit) and check the consistent gets again; 

session#2

---------

SQL> update demo_objects set object_name='xxx' where rownum < 50;

49 rows updated.

session#1

---------

SQL> select * from demo_objects;

Statistics

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

         0  recursive calls

         0  db block gets

        76  consistent gets

         0  physical reads

       116  redo size

35259  bytes sent via SQL*Net to client

       760  bytes received via SQL*Net from client

        21  SQL*Net roundtrips to/from client

         0  sorts (memory)

         0  sorts (disk)

       300  rows processed

As you see, consistent gets are increased from 26 to 76. Also there is no physical reads and db_block gets.

So where are these extra consistent gets are coming from? Answer is "undo segments"

After we rollback the session, you will see that consistent gets will be 26 again.

Now, how the consistent gets are happening?

To explain the mechanism, we should understand the array size first;

array size:  When you send a query to the database, the rows are fetched from the block according to the array size. The default value of  array size for SQL*Plus is 15. You can change this value if you want.

SQL> show arraysize

arraysize 15

Now, check how many rows each data block has for ‘demo_objects’ table;

select dbms_rowid.ROWID_BLOCK_NUMBER(rowid) blkno, count(*) row_cnt

  from demo_objects

 group by dbms_rowid.ROWID_BLOCK_NUMBER(rowid)

 order by 1

         BLKNO         ROW_CNT

           131             76

           132             73

           133             72

           134             70

           135              9

we have 5 data blocks between 131 and 135

76+73+72+70+9 = 300 rows

When we send the query, database will start to read 15 rows for each fetch.

For e.g.:

*****

Block# 132 Rows=72

**For the last fetch, there is only 12 rows but as we know fetch wants to read 15 rows.

So, what will be to the excess 3 rows?   These  rows will be read from next block.

**Another point is  Oracle starts the execution of query by finding the 1st row of the table. We can prove this from 10046 trace file:

r=  number of rows

cr= consistent reads

PARSE #139848017540176:c=1000,e=955,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=1714943783,tim=793948881469

EXEC  #139848017540176:c=0,e=21,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1714943783,tim=793948881532

FETCH #139848017540176:c=1000,e=242,p=0,cr=3,cu=0,mis=0,r=1,dep=0,og=1,plh=1714943783,tim=793948881828

FETCH #139848017540176:c=0,e=34,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=1,plh=1714943783,tim=793948882270

FETCH #139848017540176:c=0,e=33,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=1,plh=1714943783,tim=793948882561

FETCH #139848017540176:c=0,e=78,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=1,plh=1714943783,tim=793948883180

FETCH #139848017540176:c=0,e=32,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=1,plh=1714943783,tim=793948883455

FETCH #139848017540176:c=0,e=32,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=1,plh=1714943783,tim=793948883730

FETCH #139848017540176:c=0,e=34,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=1,plh=1714943783,tim=793948884373

FETCH #139848017540176:c=0,e=33,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=1,plh=1714943783,tim=793948884658

FETCH #139848017540176:c=0,e=33,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=1,plh=1714943783,tim=793948884979

FETCH #139848017540176:c=0,e=40,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=1,plh=1714943783,tim=793948885401

FETCH #139848017540176:c=0,e=40,p=0,cr=2,cu=0,mis=0,r=15,dep=0,og=1,plh=1714943783,tim=793948885722

FETCH #139848017540176:c=0,e=85,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=1,plh=1714943783,tim=793948886235

FETCH #139848017540176:c=0,e=36,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=1,plh=1714943783,tim=793948886681

FETCH #139848017540176:c=0,e=31,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=1,plh=1714943783,tim=793948886842

FETCH #139848017540176:c=0,e=32,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=1,plh=1714943783,tim=793948887558

FETCH #139848017540176:c=0,e=35,p=0,cr=2,cu=0,mis=0,r=15,dep=0,og=1,plh=1714943783,tim=793948887873

FETCH #139848017540176:c=0,e=33,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=1,plh=1714943783,tim=793948888158

FETCH #139848017540176:c=0,e=30,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=1,plh=1714943783,tim=793948889540

FETCH #139848017540176:c=0,e=28,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=1,plh=1714943783,tim=793948889818

FETCH #139848017540176:c=0,e=23,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=1,plh=1714943783,tim=793948890134

FETCH #139848017540176:c=0,e=37,p=0,cr=2,cu=0,mis=0,r=14,dep=0,og=1,plh=1714943783,tim=793948890409

STAT #139848017540176 id=1 cnt=300 pid=0 pos=1 obj=94899 op='TABLE ACCESS FULL DEMO_OBJECTS (cr=26 pr=0 pw=0 time=1284 us cost=4 size=27900 card=300)'

As you see, the first fetch requests only 1 row and it makes 3 consistent reads.

We can say that, the first fetch used the "segment space management blocks" to find the 1st row and it makes 3 consistent gets for this operation.

Demonstration:

select * from demo_objects;

3 (first row) + 5 + 5 + 6 +6 + 1 = 26 consistent gets

We read 9+5=14 rows for the last fetch and we can check this from 10046 trace file

.

.

.

FETCH #139848017540176:c=0,e=28,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=1,plh=1714943783,tim=793948889818

FETCH #139848017540176:c=0,e=23,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=1,plh=1714943783,tim=793948890134
FETCH #139848017540176:c=0,e=37,p=0,cr=2,cu=0,mis=0,r=14,dep=0,og=1,plh=1714943783,tim=793948890409

I wrote a little complicated query to calculate the consistent gets. I assumed that it will cost 3 cr to find the 1^st row. This can change according to extent size and number of extents.

CALCULATION QUERY

select 'Finding 1st row' block_no,
       1 rows_1st_count,
       1 rows_2nd_count,
       0 last_fetch_rows,
       3 "block_touch(gets)"
  from dual
union all
select to_char(d.blok_no),
       d.row_count ROWs_1st_COUNT,
       case
         when rownum = 1 then
          d.final_cnt - 1
         else
          d.final_cnt
       end ROWS_2nd_COUNT,
       d.rows_left last_fetch_rows,
       case
         when rownum = 1 and d.rows_left = 0 then
          d.block_acc
         when rownum != 1 and d.rows_left = 0 then
          d.block_acc + 1
         when lead(d.rows_left)
          over(order by d.blok_no) is null and d.rows_left = 0 then
          d.block_acc + 1
         when lag(d.rows_left) over(order by d.blok_no) = 0 then
          d.block_acc + 1
         else
          d.block_acc + 2
       end "block_touch(gets)"
  from (select c.*,
               case
                 when rownum = 1 then
                  (c.final_cnt - c.rows_left - 1) / 15
                 else
                  (c.final_cnt - c.rows_left) / 15
               end block_acc
          from (select b.blok_no,
                       b.row_count,
                       case
                         when rownum = 1 or lag(b.rows_left)
                          over(order by b.blok_no) = 0 then
                          b.row_count
                         else
                          (b.row_count -
                          (15 - lag(b.rows_left) over(order by b.blok_no)))
                       end final_cnt,
                       b.rows_left
                  from (select a.*, mod(rr - 1, 15) rows_left
                          from (select dbms_rowid.rowid_block_number(rowid) blok_no,
                                    count(*) row_count,
                                    sum(count(*)) over(order by dbms_rowid.rowid_block_number(rowid)) rr
                                  from demo_objects t
                                 group by dbms_rowid.rowid_block_number(rowid)
                                 order by 1) a) b) c) d

Query Result:

How to change tablespace of sys.aud$ and how to shrink it?

To shrink the aud$ table, you have to enable "row movement". The default tablespace of aud$ is SYSAUX and because of that you need to change its tablespace first.

After 11g, changing the tablespace of aud$ table is supported.

begin

dbms_audit_mgmt.set_audit_trail_location(

audit_trail_type => dbms_audit_mgmt.audit_trail_aud_std,

audit_trail_location_value => 'AUDTBS');

end;

alter table sys.aud$ enable row movement;

alter table sys.aud$ shrink space cascade;

alter table sys.aud$ disable row movement;

---

How to reset an expired database user without changing the password?

The simple method to reset a user is changing the password. If you dont have chance to change the password and if you can not remember the old one, you can use the following method:

select dbms_metadata.get_ddl('USER', u.username) AS ddl 
from  dba_users u 
where u.username = 'HR';

You can find the hashed passwords for all users in the database with this query.

After that you can set the old password and reset the account.

alter user HR identified by values 'S:ASSDSKDEIRUIERUTEUYTEIEORIEORIEPORI;ERIUERIUTY';


----

Oracle 18c: New Hidden Parameter: "_PX_PARTITION_SKEW_THRESHOLD"

With Oracle Database 18c, we have a new parameter which can change the execution plan of parallel queries which consist of partitioned tables. There is not too much information about this parameter yet but based on my experiments I have observed its behaviour. Actually this parameter flags if our table is "SKEWED" or "NOT SKEWED"

Let's create a monthly partition table and insert some data;

 CREATE TABLE part_skew_table
   ( prod_id       NUMBER(6)
   , cust_id       NUMBER
   , time_id       DATE
   , channel_id    CHAR(1)
   , promo_id      NUMBER(6)
   , quantity_sold NUMBER(3)
   , amount_sold   NUMBER(10,2)
   )
  PARTITION BY RANGE (time_id)
  INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
  (PARTITION sales_q1_2017 VALUES LESS THAN  (TO_DATE('01-FEB-2017','dd-MON-yyyy')));

 begin
    for rec in 1 .. 25 loop
      
      insert into part_skew_table 
      values (10,110,TO_DATE(TRUNC(DBMS_RANDOM.VALUE(2457755+00, 2457755 + 270)), 'J'),'S',910,140, 1000);
      
      insert into part_skew_table 
      values (10,110,TO_DATE(TRUNC(DBMS_RANDOM.VALUE(2457755+270, 2457755 + 360)), 'J'),'S',910,140, 1000);
      
      insert into part_skew_table 
      values (10,110,TO_DATE(TRUNC(DBMS_RANDOM.VALUE(2457755+180, 2457755 + 270)), 'J'),'S',910,140, 1000);
      
      insert into part_skew_table 
      values (10,110,TO_DATE(TRUNC(DBMS_RANDOM.VALUE(2457755+00, 2457755 + 180)), 'J'),'S',910,140, 1000);
    
    end loop;
 end;

 analyze table part_skew_table compute statistics;

  select table_name, partition_name, num_rows
   from user_tab_partitions
  where table_name = 'PART_SKEW_TABLE'
  order by 3 desc;

 TABLE_NAME         PARTITION_NAME  NUM_ROWS

 ----------             --------------   --------
 PART_SKEW_TABLE SYS_P479  16
 PART_SKEW_TABLE SYS_P482  14
 PART_SKEW_TABLE SYS_P481  12
 PART_SKEW_TABLE SYS_P477  11
 PART_SKEW_TABLE SYS_P478  9
 PART_SKEW_TABLE SALES_Q1_2017  8
 PART_SKEW_TABLE SYS_P484  8
 PART_SKEW_TABLE SYS_P480  5
 PART_SKEW_TABLE SYS_P487  5
 PART_SKEW_TABLE SYS_P485  5
 PART_SKEW_TABLE SYS_P486  4
 PART_SKEW_TABLE SYS_P483  3

 How does it work?

 The mechanism is only relevant when the number of partitions is at least twice the DOP you choose.

 If the DOP (parallelism) is N, then examine the N-1 largest partitions, sum their sizes, and express this as a percentage  of  the size    of the whole table, call it p%

• number of partitions > 2xDOP

• if p% is greater than "_px_partition_skew_threshold" a plan that could be executed as a hash distribution plan

• if p% is less than "_px_partition_skew_threshold" then the optimizer will choose the partition-wise plan.

 Let's check the following query:


  select /*+ parallel(3)*/time_id,count(*) from part_skew_ group by time_id;


 DOP  (N)                => 3 
 N-1 largest partitions  => SYS_P479, SYS_P482
 p%                      => ((16+14)/100)*100 = %30 


 1st Case:          
                    p% =30 > _px_partition_skew_threshold =29


  alter session set "_px_partition_skew_threshold" = 29;
  
  explain plan for
  select /*+ parallel(3)*/time_id,count(*) from part_skew_table group by time_id;
  
  select * from table(dbms_xplan.display('plan_table',null,'basic,predicate'));


   Plan hash value: 1775209382
 
----------------------------------------------------
| Id  | Operation                | Name            |
----------------------------------------------------
|   0 | SELECT STATEMENT         |                 |
|   1 |  PX COORDINATOR          |                 |
|   2 |   PX SEND QC (RANDOM)    | :TQ10001        |
|   3 |    HASH GROUP BY         |                 |
|   4 |     PX RECEIVE           |                 |
|   5 |      PX SEND HASH        | :TQ10000   --------------------> PX HASH
|   6 |       HASH GROUP BY      |                 |
|   7 |        PX BLOCK ITERATOR |                 |
|   8 |         TABLE ACCESS FULL| PART_SKEW_TABLE |
----------------------------------------------------

           - Degree of Parallelism is 3 because of hint

2nd Case:          
                    p% =30 < _px_partition_skew_threshold =31


  alter session set "_px_partition_skew_threshold" = 31;
  
  explain plan for
  select /*+ parallel(3)*/time_id,count(*) from part_skew_table group by time_id;
  

  select * from table(dbms_xplan.display('plan_table',null,'basic,predicate'));


Plan hash value: 1410516856
 
----------------------------------------------------
| Id  | Operation                | Name            |
----------------------------------------------------
|   0 | SELECT STATEMENT         |                 |
|   1 |  PX COORDINATOR          |                 |
|   2 |   PX SEND QC (RANDOM)    | :TQ10000        |
|   3 |    PX PARTITION RANGE ALL|--------------------------------> PX PARTITION
|   4 |     HASH GROUP BY        |                 |
|   5 |      TABLE ACCESS FULL   | PART_SKEW_TABLE |

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

           - Degree of Parallelism is 3 because of hint