Re: Table with ~255+ columns
Date: Mon, 16 Nov 2020 21:32:18 +0000
Message-ID: <DM6PR01MB592931974F7013E1398379A4CEE30_at_DM6PR01MB5929.prod.exchangelabs.com>
Because V$SESSION only has STATUS of ACTIVE while a query is running I would remove this condition from the query so if short-running queries are executed by a session that result in a fetch continued row the session shows in the result.
Mark Powell
Database Administration
(313) 592-5148
From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Lok P <loknath.73_at_gmail.com> Sent: Monday, November 16, 2020 4:12 PM
To: oracle-l_at_freelists.org <oracle-l_at_freelists.org> Subject: Table with ~255+ columns
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://clicktime.symantec.com/3Ed6i6GzRGLnZjrM3Bgc3917Vc?u=https%3A%2F%2Fasktom.oracle.com%2Fpls%2Fapex%2Fasktom.search%3Ftag%3Dtable-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 |
-------------------------------------------------------------------------------------------------------------------------
-- http://www.freelists.org/webpage/oracle-lReceived on Mon Nov 16 2020 - 22:32:18 CET