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.
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
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
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.