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@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on behalf
of Lok P <loknath.73@xxxxxxxxx>
Sent: Monday, November 16, 2020 4:12 PM
To: oracle-l@xxxxxxxxxxxxx <oracle-l@xxxxxxxxxxxxx>
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 |
-------------------------------------------------------------------------------------------------------------------------
<https://clicktime.symantec.com/37MmaNFXn1RWpA3WSyC5FWX7Vc?u=https%3A%2F%2Fcommunity.oracle.com%2Ftech%2Fdevelopers%2Fdiscussion%2F4477403%2Fquestion-on-row-chaining%23>
