Bug in block presentation for partitions and some other strange things with partion parts

Toad for Oracle
1

Toad for Oracle Xpert (64-bit)
Add-Ons: DB Admin Module
16.3.231.2085

Oracle 19client x64/19.15 x64 DB
This is what toad tell me (did irefresh, reconnect, reconnect sessions ...did try all!):

image
image1920×879 139 KB

Wrong data for blocks, num_rows, last analyzed...looks liek it is no partiticpating in the refresh...

and query

      select * from (
         SELECT utp.partition_name,
                LAG (utp.PARTITION_NAME, 1) OVER (ORDER BY utp.PARTITION_POSITION) AS prev_partition_name,
                utp.tablespace_name,
                utp.num_rows,         --STAT value!!
                LAG (utp.num_rows, 1) OVER (ORDER BY utp.PARTITION_POSITION) AS prev_num_rows,
                us.blocks,           -- REAL value!!
                LAG (us.blocks, 1) OVER (ORDER BY utp.PARTITION_POSITION) AS prev_blocks,
                utp.compression,
                utp.compress_for
           FROM USER_TAB_PARTITIONS utp, user_segments us
          WHERE 1=1         
            and utp.table_name = 'BEN_HIST'
            and utp.TABLE_NAME = us.SEGMENT_NAME 
            and utp.PARTITION_NAME = us.PARTITION_NAME
         ORDER BY utp.PARTITION_POSITION
      ) 
      where 1=1 
        --AND rownum <= 100
        and blocks <=10000
      ;

F9 works .... returns:

image
image1771×856 217 KB

Interesting also, F5 execution has an error on some query (same user). :

image
image1634×563 145 KB

And to see query for one of partition toad say it has small number of part. blocks (random taken):

SELECT utp.partition_name,
                LAG (utp.PARTITION_NAME, 1) OVER (ORDER BY utp.PARTITION_POSITION) AS prev_partition_name,
                utp.tablespace_name,
                utp.num_rows,         --STAT value!!
                LAG (utp.num_rows, 1) OVER (ORDER BY utp.PARTITION_POSITION) AS prev_num_rows,
                us.blocks,           -- REAL value!!
                LAG (us.blocks, 1) OVER (ORDER BY utp.PARTITION_POSITION) AS prev_blocks,
                utp.compression,
                utp.compress_for
           FROM USER_TAB_PARTITIONS utp, user_segments us
          WHERE 1=1         
            and utp.table_name = 'BEN_HIST'
            and utp.TABLE_NAME = us.SEGMENT_NAME 
            and utp.PARTITION_NAME = us.PARTITION_NAME
            AND utp.PARTITION_NAME='**BEN_HIST_102800704**'
         ORDER BY utp.PARTITION_POSITION

image
image1713×537 181 KB

And to show you only segments part view (no join to avoid this part of questions)....

image
image807×367 78.5 KB

image
image848×369 87.6 KB

Guys what is this ...why things that previously were working in new version do not work.

And didi again test .... still missing the point why!??

image
image1920×708 137 KB

and here is sql spool what toad execute:


----------------------------------
-- Session: C##SDBA_Q383211[C##SDBA_A1]@SMOBCST1
-- Timestamp: 17:59:28.356
Select 1
from sys.DBA_OBJECTS
where owner =:o
and object_name =:nm
and object_type =:t;
:o(VARCHAR[7],IN)='MOBAPPO' 
:nm(VARCHAR[8],IN)='BAN_HIST' 
:t(VARCHAR[17],IN)='MATERIALIZED VIEW'

----------------------------------
-- Session: C##SDBA_Q383211[C##SDBA_A1]@SMOBCST1
-- Timestamp: 17:59:28.372
Select t.table_name, t.owner, t.cluster_name,
       t.tablespace_name, round(t.num_rows) num_rows,
       t.initial_extent, t.degree, t.cache
       , t.partitioned, t.last_analyzed, t.logging, t.iot_type, t.buffer_pool
       , t.monitoring, t.temporary, t.table_type, t.table_type_owner, t.nested
       , t.Compression, t.dropped
       , t.compress_for, t.flash_cache, t.cell_flash_cache, t.segment_created
       , external
from sys.DBA_ALL_TABLES t
where 1=1
and t.owner = :own
and t.table_name = :obj;
:own(VARCHAR[7],IN)='MOBAPPO' 
:obj(VARCHAR[8],IN)='BAN_HIST'

----------------------------------
-- Session: C##SDBA_Q383211[C##SDBA_A1]@SMOBCST1
-- Timestamp: 17:59:28.372
Select created, last_ddl_time, object_id, status
from sys.DBA_OBJECTS
where object_name = :nm
and owner = :o
and object_type = :t;
:nm(VARCHAR[8],IN)='BAN_HIST' 
:o(VARCHAR[7],IN)='MOBAPPO' 
:t(VARCHAR[5],IN)='TABLE'

----------------------------------
-- Session: C##SDBA_Q383211[C##SDBA_A1]@SMOBCST1
-- Timestamp: 17:59:28.387
Select comments
from sys.DBA_TAB_COMMENTS
where table_name = :obj
and origin_con_id = TO_NUMBER(sys_context('USERENV','CON_ID'))
and owner = :own;
:obj(VARCHAR[8],IN)='BAN_HIST' 
:own(VARCHAR[7],IN)='MOBAPPO'

----------------------------------
-- Session: C##SDBA_Q383211[C##SDBA_A1]@SMOBCST1
-- Timestamp: 17:59:28.417
Select cols.column_id, cols.column_name as Name, cols.nullable, cols.data_type as Type, cols.data_type_mod,
       decode(cols.data_type, 'CHAR', cols.char_length,
                              'VARCHAR', cols.char_length,
                              'VARCHAR2', cols.char_length,
                              'NCHAR', cols.char_length,
                              'NVARCHAR', cols.char_length,
                              'NVARCHAR2', cols.char_length,
                              null) nchar_length,
       decode(cols.data_type, 'NUMBER', cols.data_precision + cols.data_scale, cols.data_length) length,
       cols.data_precision Precision, cols.data_scale Scale, cols.data_length dlength, cols.data_default  
       ,cols.char_used
       ,cols.hidden_column, cols.internal_column_id
       ,cols.qualified_col_name
       ,InitCap(cols.histogram) histogram
       ,cols.virtual_column
       ,cols.num_distinct, cols.num_nulls, round(cols.density, 5) density
       ,cols.user_generated, cols.default_on_null, cols.identity_column
       ,cols.collation
       ,null as comments
FROM
      sys.DBA_TAB_COLS cols
where 1=1
and   cols.table_name = :TABNAME
and   cols.owner = :OWNNAME
order by column_id, internal_column_id;
:TABNAME(VARCHAR[8],IN)='BAN_HIST' 
:OWNNAME(VARCHAR[7],IN)='MOBAPPO'

----------------------------------
-- Session: C##SDBA_Q383211[C##SDBA_A1]@SMOBCST1
-- Timestamp: 17:59:28.432
select column_name, encryption_alg, salt
from sys.DBA_ENCRYPTED_COLUMNS
where owner = :own
and table_name = :tn;
:own(VARCHAR[7],IN)='MOBAPPO' 
:tn(VARCHAR[8],IN)='BAN_HIST'

----------------------------------
-- Session: C##SDBA_Q383211[C##SDBA_A1]@SMOBCST1
-- Timestamp: 17:59:28.602
SELECT CN.NAME
FROM   SYS.CDEF$ C, SYS.CON$ CN, SYS."_CURRENT_EDITION_OBJ" O, SYS.USER$ U
WHERE  C.Type# = 2
AND    C.CON# = CN.CON#
AND    C.OBJ# = O.OBJ#
AND    O.OWNER# = U.USER#
AND    U.NAME = :uname
AND    O.NAME = :tname;
:uname(VARCHAR[7],IN)='MOBAPPO' 
:tname(VARCHAR[8],IN)='BAN_HIST'

----------------------------------
-- Session: C##SDBA_Q383211[C##SDBA_A1]@SMOBCST1
-- Timestamp: 17:59:28.602
SELECT  c1.column_name, c1.position
FROM    SYS.DBA_CONS_COLUMNS C1
WHERE   C1.table_name = :tname
AND     C1.constraint_name = :cname
AND     C1.owner = :uname
ORDER BY 2;
:tname(VARCHAR[8],IN)='BAN_HIST' 
:cname(VARCHAR[11],IN)='BAN_HIST_PK' 
:uname(VARCHAR[7],IN)='MOBAPPO'

----------------------------------
-- Session: C##SDBA_Q383211[C##SDBA_A1]@SMOBCST1
-- Timestamp: 17:59:28.704
Select comments
from sys.DBA_TAB_COMMENTS
where table_name = :obj
and origin_con_id = TO_NUMBER(sys_context('USERENV','CON_ID'))
and owner = :own;
:obj(VARCHAR[8],IN)='BAN_HIST' 
:own(VARCHAR[7],IN)='MOBAPPO'

----------------------------------
-- Session: C##SDBA_Q383211[C##SDBA_A1]@SMOBCST1
-- Timestamp: 17:59:28.730
Select t.trigger_name, t.trigger_type, t.triggering_event,
       t.when_clause, t.status enabled, o.status, t.owner, o.object_id, t.trigger_body
from  sys.DBA_OBJECTS o, sys.DBA_TRIGGERS t
where t.table_owner = :own
and   t.table_name = :obj
and   o.object_type = 'TRIGGER'
and   o.object_name = t.trigger_name
and   o.owner = t.owner;
:own(VARCHAR[7],IN)='MOBAPPO' 
:obj(VARCHAR[8],IN)='BAN_HIST'

----------------------------------
-- Session: C##SDBA_Q383211[C##SDBA_A1]@SMOBCST1
-- Timestamp: 17:59:28.788
Select owner, index_name, uniqueness, status
       ,INDEX_TYPE, TEMPORARY, PARTITIONED, LOGGING, DEGREE, funcidx_status, join_index
from   sys.DBA_INDEXES
where  table_owner = :town
and    table_name  = :tname
order by index_name;
:town(VARCHAR[7],IN)='MOBAPPO' 
:tname(VARCHAR[8],IN)='BAN_HIST'

----------------------------------
-- Session: C##SDBA_Q383211[C##SDBA_A1]@SMOBCST1
-- Timestamp: 17:59:28.788
Select *
from   sys.DBA_IND_COLUMNS
where  index_owner = :iown
and    index_name  = :iname
order by column_position;
:iown(VARCHAR[7],IN)='MOBAPPO' 
:iname(VARCHAR[11],IN)='BAN_HIST_PK'

----------------------------------
-- Session: C##SDBA_Q383211[C##SDBA_A1]@SMOBCST1
-- Timestamp: 17:59:28.888
select partitioning_type, subpartitioning_type
       , interval
       , interval_subpartition, autolist, autolist_subpartition
       , ref_ptn_constraint_name
from sys.DBA_PART_TABLES
where owner = :own
and table_name = :TableName;
:own(VARCHAR[7],IN)='MOBAPPO' 
:TableName(VARCHAR[8],IN)='BAN_HIST'

----------------------------------
-- Session: C##SDBA_Q383211[C##SDBA_A1]@SMOBCST1
-- Timestamp: 17:59:28.888
select column_name
from sys.DBA_PART_KEY_COLUMNS
where owner = :own
and name = :objname
and object_type = :objtype
order by column_position;
:own(VARCHAR[7],IN)='MOBAPPO' 
:objname(VARCHAR[8],IN)='BAN_HIST' 
:objtype(VARCHAR[5],IN)='TABLE'

----------------------------------
-- Session: C##SDBA_Q383211[C##SDBA_A1]@SMOBCST1
-- Timestamp: 17:59:28.903
Select PARTITION_POSITION, PARTITION_NAME,
       HIGH_VALUE, HIGH_VALUE_LENGTH, TABLESPACE_NAME, NUM_ROWS, LAST_ANALYZED
       ,SUBPARTITION_COUNT
       ,COMPRESSION
       ,AVG_SPACE, BLOCKS, EMPTY_BLOCKS
FROM sys.DBA_TAB_PARTITIONS
WHERE TABLE_NAME = :obj
AND TABLE_OWNER = :own
ORDER BY PARTITION_POSITION;
:obj(VARCHAR[8],IN)='BAN_HIST' 
:own(VARCHAR[7],IN)='MOBAPPO'

what means you are quering "USER_TAB_PARTITIONS.blocks column", and this seems to have stat value not real one.
More on https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/ALL_TAB_PARTITIONS.html#GUID-D6A09AE6-BE9F-43F2-874C-60D218F09AC1

2

Hi Damir,

This info comes from user_tab_partitions as you found.

image
image1250×706 81.6 KB

Yes, this shows statistical values, not actual values.

why things that previously were working in new version do not work.

Nothing has changed here in Toad. We have always displayed data here from user_tab_partitions, not user_segments.

I could add an option there to include actual size if you want, but there is also an "analyze" button on the toolbar above the list of partitions. You could easily get your statistics up to date, then the Number of Blocks would be more accurate.

3

Ok maybe I missed the previous version .... sorry.
But this really hit me.
Any signs of proper information should be somehow shown

4

To show actual block size here would be slow. Then people would expect actual number of rows too. That would be very slow. This is why Oracle provides statistic values. But I will add an indicator in there so that it is more clear they are from statistics.