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!

About the last_call_et column in v$session

user112472Aug 11 2010 — edited Aug 13 2010

Hello,

Can someone help me about a behaviour that is very strange ...

We have connection about SAS on a database which manipulate Oracle table and others SAS Structure.

The query is a "join" about that table and a "SAS Table" between million of lines in the Oracle table one ....

It last a long time ....

During the query, we have time to see at the v$session table...

At any time (it lasts 3h), we saw that the column 'last_call_et' of the v$session never change : it continuesly stay at 0.

What does it mean ? it means that SAS send a query every second ? Or this column shows the last "internal call"?

If this is the right reason, i would'nt understand why, when i take the same query( launched by SAS) in TOAD, there is no the same behaviour and this behaviuor is in that case the right behaviour : the session is ACTIVE but the last_call_et respresents really the time since i launched the query... So why when SAS do it, the last_call_et stay at 0 ? it works like SAS launch every second this query !!!!

Can someone help me ?

Thanks ...

tlokweng

Answer

クエリトランスフォーメーションのビューマージで２階層のクエリが１階層に置換され、結果として各行でプロシジャが２回づつ呼ばれているということですね。なので単純にプロシジャを呼んでいるインラインビューがマージされないようにすれば解決します。素直に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

Locked Post

New comments cannot be posted to this locked post.

Locked on Sep 10 2010

Added on Aug 11 2010

#general-database-discussions

14 comments

24,278 views

Oracle Database Discussions

About the last_call_et column in v$session

Comments

Post Details