Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Question On Row chaining

user10472047Nov 11 2020 — edited Nov 11 2020

Hi, We are using version 11.2.0.4 of oracle. We have a got a table having already ~270 columns and we just got request from dev team to add couple of more columns. But considering the rows chaining after ~255 columns we are asking team to not add those new columns but to plan for dropping those existing columns such that total number of columns will be restricted within ~255.
But Dev team asking, can we show some evidence of what amount of overhead are we currently bearing having those additional columns in that table. And i was trying to see if by anyway we can relate statistics "table fetch continued row" with the database time.
I do see in dba_hist_sysystat , there is Avg ~400million "table fetch continued row" stats getting noted in per hour of AWR snapshot. But i am not sure , how that can be converted to the amount DB time its contributes to? So is ther any way out for this? And also i am not able to associate these stats to any sql_id, so wanted to know if we have some AWR/ASH view which stores that stats("table fetch continued row") for specific sql_ids?
Secondly, i tried running a query manually which in reality getting executed million of times/day from application. But when i tried fetching the stats "table fetch continued row" from v$sysystat for that session, I am seeing "0" value for that. So it means at least at current stage this table is not suffered from "row chaining". But how to ensure that by adding couple of more columns we will still be safe and we wont suffer from row chaining symptom?
The table is range partitioned by column CRT_DT and hold ~1Tb of data with Num Rows   = 1,400,752,800 and AV_ROW_LEN noted as "236" at global level and it spans across ~73 partitions. Total Number of blocks= Blocks   60,904,702.
Sample query which i tested to see row chaining effect.

SELECT * FROM TRAN_TAB WHERE ID = :B2 AND CRT_DT = to_date(:B1 ,'MM/DD/YYYY HH24:MI:SS');


Plan hash value: 2186597613

-------------------------------------------------------------------------------------------------------------------------
| Id | Operation                         | Name              | Rows | Bytes | Cost (%CPU)| Time    | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                  |                   |      |      |    3 (100)|         |      |      |
|  1 | PARTITION RANGE SINGLE           |                   |    1 |  265 |    3  (0)| 00:00:01 |  KEY |  KEY |
|  2 |  TABLE ACCESS BY LOCAL INDEX ROWID| TRAN_TAB          |    1 |  265 |    3  (0)| 00:00:01 |  KEY |  KEY |
|  3 |   INDEX UNIQUE SCAN              | TRAN_TAB_UK       |    1 |      |    2  (0)| 00:00:01 |  KEY |  KEY |
-------------------------------------------------------------------------------------------------------------------------

Comments

Processing

Post Details

Added on Nov 11 2020
12 comments
632 views