Skip to Main Content
  • Questions
  • how the data stored in partition table

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Ken.

Asked: May 04, 2017 - 4:32 am UTC

Last updated: May 05, 2017 - 1:47 am UTC

Version: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production

Viewed 1000+ times

You Asked

Hi Tom,

I have a partition table, Range partition by date, list sub-partition by region. my question is: Is it possible that same day but different regions' data can be stored at same data block.

when running complicated query (the query join a couple of tables) to extract one day one region's data (at same time, same day different regions' data are inserted into this table). sometimes, the query performance really slow. it is hanging for a couple of hours. I doubt this is because same data block has different region's data which causes a large amount of undo reading.

I query the table. for same SCN, it returns different region's records. does this mean same data block has different regions.

My database version is below:

Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
PL/SQL Release 11.1.0.7.0 - Production
CORE 11.1.0.7.0 Production
TNS for Solaris: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production

thanks in advance.



and Connor said...

Each partition is a different physical segment (ie, like an individual table).

So they will not share blocks.

SO check the *complete* DDL for the table, for example if your table was:

...
partition by range (x)
subpartition by list (y)
(
  partition p1 values less than ..
    subpartition p1a values ('a'),
    subpartition p1b values ('b')
  partition p2 values less than
  partition p3 values less than
    subpartition p1a values ('c'),
    subpartition p1b values ('d')


then you can see that just because p1 and p3 have actual subpartitions, this is not a guarantee that *every* partition has subpartitions defined.

Trace your query, or look at v$active_session_history and see *why* its waiting - that will assist in working out the cause.

Rating

  (9 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

one more question

Ken HaN, May 04, 2017 - 5:51 am UTC

thanks Connor for your quick response.
I have one more question. when query table with ORA_ROWSCN, same ORA_ROWSCN, returns different regions. our process load data based on sub-partition. per day, per region. for my understanding, different region should have different ORA_ROWSCN.
Connor McDonald
May 04, 2017 - 12:04 pm UTC

ORA_ROWSCN is *not* the block, it is (conceptually) the point in time at which a block (or row) was modified.

If you want to see what *block* a row belongs to, you can use:

select dbms_rowid.rowid_block_number(rowid) from [my_table]

my query infor

Ken HaN, May 04, 2017 - 3:55 pm UTC

Hi Connor,
below is query I run and the output. I am pretty new for these information. would you please take a look. what the issue is.
  SELECT B.NAME,
         COUNT (*),
         SUM (TIME_WAITED)
    FROM v$ACTIVE_SESSION_HISTORY A, v$EVENT_NAME B
   WHERE     A.SAMPLE_TIME BETWEEN TO_DATE('20170504 07:00:00', 'YYYYMMDD HH24:MI:SS')
                               AND TO_DATE('20170504 12:00:00', 'YYYYMMDD HH24:MI:SS')
         AND A.EVENT# = B.EVENT#
         AND A.SQL_ID = '9y2p4th176gag'
GROUP BY  B.NAME

NAME COUNT(*) SUM(TIME_WAITED)
db file sequential read 1222 168940637
gc cr multi block request 5 3111770
db file scattered read 26 3110257
gc cr grant 2-way 20 13249730
gc current block 2-way 2 1414684
db file parallel read 7 1049467
gc cr grant congested 1 10241

Thanks in advance

Connor McDonald
May 05, 2017 - 1:47 am UTC

As you've already surmised, 'db file sequential read' is typically an index lookup. So you'd need to focus your energies there in terms of a tuning solution. It *might* be not using the index is better.

Ken HaN, May 04, 2017 - 4:46 pm UTC

Sorry, Connor. In my previous post, I didn't say clearly. I checked my query exec plan. It join 3 tables and 3 table used INDEX RANGE SCAN. as per document, hig DB file sequential read wait event may be caused by INDEX reading. do you think I should drop the index. or there are other reasons which causes the high DB file sequential read.

thanks in advance

Ken HaN, May 05, 2017 - 12:10 am UTC

Hi Connor,

I used the query you provide to query our table, found there are some blocks have multiple regions data. I talked to DBA. He said the is normal.

Do you have oracle official document link which mentioned "each partition is a different physical segment". I have searched this document whole afternoon, but not luck.


DATA_BLOCK ROWID REGION DT
907208 AL/u10AKxAADdfIAAA REG1 5/2/2017
907208 AL/u10AKxAADdfIAAB REG2 5/2/2017
907208 AL/u10AKxAADdfIAAC REG3 5/2/2017

thanks in advance

Connor McDonald
May 05, 2017 - 1:45 am UTC

Give us

- the *complete* DDL for the table.
- the SQL statement you ran for the above


Ken HaN, May 05, 2017 - 2:50 am UTC

Thanks Connor

below is DML and DDL. for the DDL, there a lot of partitions, I only put one. and I removed unnecessary columns as well.

CREATE TABLE SCHE.TABLE1
(
DT DATE NOT NULL,
REG VARCHAR2(6 BYTE) NOT NULL,
)
NOCOMPRESS
TABLESPACE DATA_02
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
BUFFER_POOL DEFAULT
)
PARTITION BY RANGE (DT)
SUBPARTITION BY LIST (REG)
SUBPARTITION TEMPLATE
(SUBPARTITION REG1 VALUES ('REG1') TABLESPACE DATA_02,
SUBPARTITION REG2 VALUES ('REG2') TABLESPACE DATA_02,
SUBPARTITION REG3 VALUES ('REG3') TABLESPACE DATA_02,

(
PARTITION 2017Y_05M_02D VALUES LESS THAN (TO_DATE(' 2017-05-03 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
TABLESPACE DATA_02
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
MAXSIZE UNLIMITED
BUFFER_POOL DEFAULT
)
( SUBPARTITION 2017Y_05M_02D_REG1 VALUES ('REG1') TABLESPACE DATA_02,
SUBPARTITION 2017Y_05M_02D_REG2 VALUES ('REG2') TABLESPACE DATA_02,
SUBPARTITION 2017Y_05M_02D_REG3 VALUES ('REG3') TABLESPACE DATA_02,
),
PARTITION 2017Y_05M_02D
NOLOGGING
NOCOMPRESS
TABLESPACE INDX01
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
MAXSIZE UNLIMITED
BUFFER_POOL DEFAULT
)
( SUBPARTITION 2017Y_05M_02D_REG1 TABLESPACE INDX01,
SUBPARTITION 2017Y_05M_02D_REG2 TABLESPACE INDX01,
SUBPARTITION 2017Y_05M_02D_REG3 TABLESPACE INDX01,
),
NOPARALLEL;

ALTER TABLE SCHE.TABLE1 ADD (
CONSTRAINT XPK_DEL
PRIMARY KEY
(P_KEY, DT, REG)
USING INDEX LOCAL
DISABLE NOVALIDATE);

ALTER TABLE SCHE.TABLE1 ADD (
CONSTRAINT XFK_DTL_1
FOREIGN KEY (I_KEY)
REFERENCES SCHEM1.INS (I_KEY)
DISABLE NOVALIDATE,
CONSTRAINT XFK_DTL_2
FOREIGN KEY (REG)
REFERENCES SHCEM1.CODE (REG)
DISABLE NOVALIDATE);

--DDL

select dbms_rowid.rowid_block_number(rowid) DATA_BLOCK
, ROWID
, REG
, DT
FROM table1
WHERE dbms_rowid.rowid_block_number(rowid) = 907208
AND DT = '02-MAY-17';

Files/Blocks

Racer I., May 05, 2017 - 10:54 am UTC

Maybe the block number is accidentally the same, but from different files :

SELECT
dbms_rowid.rowid_relative_fno(rowid) REL_FNO,
dbms_rowid.rowid_block_number(rowid) BLOCKNO,
dbms_rowid.rowid_row_number(rowid) ROWNO,
empno, ename
FROM emp WHERE empno = 7369;


Further test

Racer I., May 05, 2017 - 11:07 am UTC

Hi,

On second thoughts differen files should show up as visible differences in the ROWID on the left.

Maybe try something like this :

select
dbms_rowid.rowid_relative_fno(rowid) RFN,
dbms_rowid.rowid_block_number(rowid) DATA_BLOCK,
ROWID, REG, DT
FROM table1 partition 2017Y_05M_02D subpartition 2017Y_05M_02D_REG1

to see if Oracle returns invalid rows (don't use WHERE).

If so I would suspect some partition exchange shenanigans, where Oracle trusted the user and didn't validate the data.

Loophole

Racer I., May 05, 2017 - 11:10 am UTC

ALTER TABLE CALL
EXCHANGE PARTITION call_partition WITH TABLE call_temp
>WITHOUT VALIDATION<

Further test result

Ken HaN, May 05, 2017 - 3:44 pm UTC

Hi Racer,

I did testing, you are right. the data has same data block number and different data file.

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.