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!

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

tlokweng
Answer

クエリトランスフォーメーションのビューマージで2階層のクエリが1階層に置換され、結果として各行でプロシジャが2回づつ呼ばれているということですね。なので単純にプロシジャを呼んでいるインラインビューがマージされないようにすれば解決します。素直にNO_MERGEヒントでOKでしょう。テーブルサイズが大きくなければWITHにしてファクタリングするのもアリですね。

> create table test_table(n) as select 1 from dual union select 2 from dual;

> create function test_func return varchar2 as begin RETURN DBMS_RANDOM.STRING('x', 10); end;

> select n, r r1, r r2 from (select n, test_func r from test_table);

        N R1          R2

--------- ------------ ------------

        1 AJ1LNZ30HD  LAMQV4CQUY

        2 F911DGQDYZ  ID8FDREGKF

> select n, r r1, r r2 from (select /*+ no_merge */ n, test_func r from test_table);

--------- ------------ ------------

        1 K5MPMUR3DQ  K5MPMUR3DQ

        2 DD2Y5NUWPM  DD2Y5NUWPM

> with t as (select /*+ materialize */ n, test_func r from test_table) select n, r r1, r r2 from t;

--------- ------------ ------------

        1 0F2RE7Y8C3  0F2RE7Y8C3

        2 O6C4WCBAFP  O6C4WCBAFP

Marked as Answer by 988825 · Sep 27 2020
988825

tlokweng様、お礼が遅くなってしまい、申し訳ありません。サンプルまでお示しくださいまして、誠にありがとうございます。

クエリトランスフォーメーションがこのようなSQLに対し作用しているとは全く一度も想像すらつきませんでした。

頂戴したサンプルで動作も確認出来ましたので、本来の組み込み先に入れてみようと思います。

ありがとうございました。

1 - 2

Post Details

Added on Nov 11 2020
12 comments
667 views