Re: Table with ~255+ columns

Do some research on _rowlen_for_chaining_threshold hidden parameter and associated bug/notes. Depending on your use case it might help with the physical reads associated with “table fetch continued row” events. I’ve had some success with it in my distant past. I have also used a process that used "analyze table list chained rows into …” to identify problematic rows, back them up into a separate table, delete the rows and reinsert them with the hidden parameter to get the multiple row pieces into single blocks. This is useful when the chaining is being caused by row updates.

Kenny

> On Nov 16, 2020, at 4:12 PM, Lok P <loknath.73_at_gmail.com> wrote:
>
> We are using version 11.2.0.4 of oracle. We have a table having ~270 columns. But considering the rows chaining is inevitable after exceeding the ~255 columns limit(i also see in the below blog stating the same) we are asking the team to not add additional new columns and to plan for dropping those existing columns such that the total number of columns will be restricted within ~255. But the team is asking to measure the current overhead on the queries as we already have ~15more columns in the table exceeding the ~255 column limit. Is there a way to measure the overhead of row chaining for that table in production in terms of DB time ?
>
> https://asktom.oracle.com/pls/apex/asktom.search?tag=table-fetch-continued-row <https://asktom.oracle.com/pls/apex/asktom.search?tag=table-fetch-continued-row>
> I do know that the statistics "table fetch continued row" is pointing to the additional work done because of rows chaining/row migration , and I see dba_hist_systat is showing value for "table fetch continued row" as Avg ~400million per hour for this database. But i am not seeing any direct way to relate it to the DB time contributed by this. And also no direct way to relate this to the responsible sql_ids.
>
> I tried fetching details from production during run time from v$sesstat using below query, But surprisingly i a seeing some sqls having different tables which were having a lot less column( some <50 columns) logging this 'table fetch continued row' stats in v$sesstat. Not seeing anything related to this table which is holding ~270 columns and this is the only table in our database exceeding the ~255 column limit.
>
> select sn.name,
> se.SID,ss.sql_id,ss.prev_sql_id,
> VALUE
> from gv$session ss,
> gv$sesstat se,
> gv$statname sn
> where se.STATISTIC# = sn.STATISTIC# and ss.inst_id= se.inst_id and se.inst_id= sn.inst_id
> and se.SID = ss.SID and sn.name='table fetch continued row' and value>0
> and ss.status='ACTIVE'
> and ss.username is not null
> order by VALUE desc;
>
>
> Secondly, I tried running a query(it does a SELECT *) manually in the same table in production which has ~270 columns in it, which in reality gets executed millions 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 the current stage this table is not suffering from "row chaining". Is it true or am I checking it the wrong way? Without doing an Analyze table list chain, is there any other quick way to figure out if this table is really suffering from row chaining? And how to ensure that by adding a couple of more columns we will still be safe and we won't suffer from row chaining symptoms?
>
> The table is range partitioned by column CRT_DT and holds ~1Tb of data with Num Rows = 1,400,752,800 and AVG_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 |
> -------------------------------------------------------------------------------------------------------------------------
>
>
>
> <https://community.oracle.com/tech/developers/discussion/4477403/question-on-row-chaining#>

--
http://www.freelists.org/webpage/oracle-l