Oracle Database Discussions

I see few blogs stating issue with columns beyond ~255. But still trying to relate/match it to this database, if we are going to be affected by such issues in future( if will add N number of more columns) or we are already getting impacted unknowingly.
https://jonathanlewis.wordpress.com/2018/02/28/255-columns-3/

I checked using below query and seeing the total max number of columns having num_distinct >0 is ~157, So it measn this table is having lot of columns populated with NULL values, even total number of columns is ~270+. Can that be the reason, behind, may be we are not seeing the chaining effect actually so we are safe for adding new columns to this table?
select partition_name, count(*) from dba_part_col_statistics where table_name='TRAN_TAB' and num_distinct>0 '
group by partition_name
order by count(*)desc

The "Avg ~400million "table fetch continued row" stats getting noted in per hour" can be easily translated into I/O, you have very impressive piece of hardware there.
In general, in my opinion, you can add at least one more I/O for each read through unique index scan. Unique index scan is 4 I/O (generally speaking, of course index can be less then 3 levels, then unique scan is less I/O or all data requested by read can be inside index and there is no need to go to table...). As well you may want to consider performance of inserts into the table (more I/O per insert).
Using awrsqrpt you can see detailed wait events and time spent for specific sql_id.

Hope above helps

Thank you @evgenyg1
I was initially wondering if those 400million/hour "table fetch continued row" statistics, may be because of combination of some other table. But then , i verified using below query , that there is only one table(same table TRAN_TAB which we are now asked for adding two more columns) in this database having number of columns>255, so i am not sure if its correct and safe to assume that all the "table fetch continued row" stats are coming from same one table only.
Now, if above is true then we must be getting some performance degradation because of this and that is something i want to measure.
The table is having only one index in it and that is unique too. If i see stats from dba_hist_sqlstat, the sample "select query" which i highlighted in first post , is running ~2-3million times/day with Avg response time of Avg ~.0008 seconds/execution with ~6buffer gets/execution and out of that ~238Micro seconds/execution is the CPU time.
And insert into that table also happen through row by row insert , taking avg ~.0005seconds/execution with Avg ~8 buffer_gets/executions and ~300 micro seconds/execution is the CPU time.
Now from above figures , Is there anything obvious which points that we are doing X% of more work because of "table fetch continued row" ?
select owner,table_name, count(*) from dba_tab_columns
group by table_name,owner
having count(*)>250

Can you run awrsqrpt for the query? for the insert? it would give you good picture of events/waits for this sql.
If performance explained above is good and fit into SLA and have no performance issue why to bother?
Wide tables is, in my opinion, sign of bad design so I would try to avoid any support of such design (thinking about maintenance and management and possible strange side effects) .

my 2c.
regards

Yes , we don't have current issue with respect to the speed at which things are processing. But as we keep on adding new columns and are asking team to not to do that, and in turn team asking to understand what amount/percent of additional work we are doing because of these large amount of column/chaining we have currently in our table. So was trying to see ways to quantify that overhead if any.
With respect to awrsqrpt report , i will try to get it from DBA's for the exact query. I do have read only access to OEM, but i am not seeing an option to generate this AWR for a single sql_id from OEM.

Good luck.
Explain to someone that bad design is bad design can be a very difficult task indeed.
Fortunately, I never had an issue to run any of the reports, in my opinion, this is DBA job and not application developers and as a DBA I always have access and rights.
Btw, https://jonathanlewis.wordpress.com/2011/02/23/awr-reports/
For future use, you may ask DBA (or copy it yourself) these scripts to your machine and then only ask for specific read privileges.

Regards

As i already seeing ~400 million of "table fetch continued row" being noted in dba_hist_systat each hour, So I tried doing the backtrack from the stats 'table fetch continued row' from v$sesstat(using below query) which can well be joined with v$session to get the exact sql_id which is suffering from this overhead. But surprisingly i a seeing some sqls having different tables which were having lot less column( some <50 columns) logging this 'table fetch continued row' stats in v$sesstat.
Now i am bit confused, initially my thought was that the only table having columns must be logging those stats but now i am seeing some queries involving other tables were logging this stats. As per my understanding this statistics was related to either "row migration" or "row chaining" only . And then is it true that, we are suffering from row migration in all those tables due to which all these 400million/hour stats being logged?
And again with respect to current discussion, i think i was wrong on another part i.e. its not necessarily true that if we just exceed the total number of column > ~255 column , then we will suffer Row chaining. As long as the sum of the lengths/bytes of the all the rows are less than the block size( <8K), then i believe there wont be any row migration effect observed. Is my understanding is correct here?
And if above is true, then we can calculate the ~AVG length of each rows which currently showing as ~236Bytes in our case. So we are (8K-236) bytes away from being affected by chained row. Which means we are safe to add few more columns considering this figures. Please correct me if wrong?

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; 

I would start with awrsq reports on the top (let's say 10) queries to see what the waits are. And what the effect of "table fetch continued" event on the performance of the queries.
There is good reading regarding this event.
table fetch continued row - Ask TOM (0 Bytes)Quote from above: "If you have longs and long raws in your tables, you will have this regardless. If you have tables with more then 255 columns, you will have this regardless (they are stored in 255 column rowpieces -- never as a single big row)."
Since you confirmed that not only wide tables reason for the numbers (again, 400M per hour is, in my opinion, huge). May be good idea to start to review the design of additional tables as well. Not only large amount of columns is sign for bad design.
Have fun.
Best Regards
Evgeny

Is this below statement is correct
"If you have tables with more then 255 columns, you will have this regardless (they are stored in 255 column rowpieces -- never as a single big row)."
As because as i posted in my initial post, i tested a query(doing select *) on the same table with ~270 columns , and i noted "0" of "table fetch continued row" in v$sysstat for that session. I posted the plan too in my first post. How is that possible? Or i am checking it in wrong way?

Hi
Tom Kyte is trusted source of information indeed. The answer I think "is depends", in my experience to create migrated/chaining rows you need to think how to do it, oracle "trying to avoid" any chain/migrated row, since this is increase I/O .
yours select running unique index scan using local index on partition, in this case this is question of "luck" to get chain/migrated row or not (if such exists in specific partition).
This is why I am very much consistently asking for awrsqrpt of top queries. It can be that massive amount of "table fetch continued row" events is result of another operation/activity. That may/and may not affected by wide tables.
You need awr report for let's say 15 minutes oif high time, and awrsqrpt for each select from "wide" tables.
As well you may want to see awrsqrpt for each sql_id associated with high amount of "table fetch continued row" waits..
This is simple when you have AWR, this is not simple when you have only v$, as v$ is "snapshot" of SGA (not exactly but good enough for current context) .

Best Regards
Evgeny

@loknath
You may very well be testing this incorrectly.
Since you've read my blog note (the 3rd about exceeding 255 columns) you will have noticed that the way the statistics are counted cann change between versions.
In 11.2.0.4 I think there's a critical detail that "table fetch continued row" is incremented only if the second (or later) part of the row is in a different block, so fetching a few random rows from a table with 270 columns might never do a continued fetch.
You need to do some modelling and testing with data that you can control very carefully to check (as closely as you can) what Oracle does that counts as a "continued" fetch. E.g. create a table of 270 colums with enough data to fill a number of full blocks but some NULL columns in the middle of each row. Tablescan to see if you get any "continue", then force an index full scan to check, then update a a load of rows on their null columns so some of the rows have to migrate or partially migrate and repeat the queries.
Regards
Jonathan Lewis