Discussions
Categories
- 197.1K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.7K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 555 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.3K SQL Developer
- 296.3K Development
- 17 Developer Projects
- 139 Programming Languages
- 293K Development Tools
- 110 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 158 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 205 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 468 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 Portuguese
About the last_call_et column in v$session

user112472
Member Posts: 8
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 ...
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 ...
Answers
-
when all else fails, Read The Fine Manual
http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/dynviews_2088.htm#i1414383LAST_CALL_ET NUMBER If the session STATUS is currently ACTIVE, then the value represents the elapsed time in seconds since the session has become active. If the session STATUS is currently INACTIVE, then the value represents the elapsed time in seconds since the session has become inactive.
-
Are you sure about that there is no other session in database with the same username?
As the previous poster referred to manual , It say it all. -
user12046632 wrote:Did you check that SAS and TOAD were using the same execution plans ?
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 !!!!
One possibility is that the SAS call switches to first_rows_N optimisation and produces a plan that uses nested loops everywhere to return data almost immediately, and then carries on issuing (small) fetch calls - leaving the last_call_et at zero - while TOAD uses all_rows optimisation and produces a plan that includes a large blocking operation that takes a long time to return the first few rows - so last_call_et keeps climbing until the first few rows are available.
Regards
Jonathan Lewis -
@ sb92075:Yes but it is a bit light to answer to my question ... which deals, in fact, with detail about "what does oracle consider about 'become active'". is that erery time a user lunch a query or is that when oracle loops internal fetch for a query ....
Edited by: user12046632 on 12 août 2010 01:25 -
@ umanish
i've checked that by the SID of the session... So no doubt
Thanks indeed for your idea ...
Edited by: user12046632 on 12 août 2010 01:25 -
@ Jonathan Lewis
I had this idea too but i don't check that really ...
Because, functionnaly, (it is in SAS that the timing is too long ...) SAS asks for the whole data in ist query. Why the plan would be for 'first rows' ?
Because SAS needs all the data "now" to answer its question ... And i could not explain why Oracle would cut the query into multiples ones with for each one, such a plan ...
This idea would mean that Oracle after obtaining the "first rows", relaunched the query to obtain tne next ones ?
I don't see this sort of filter in the query too ...
I'm going to check all the same that ... But i am not sure that it is the real reason ... -
user12046632 wrote:A FETCH is a database call - so the two scenaros may be:
This idea would mean that Oracle after obtaining the "first rows", relaunched the query to obtain tne next ones ?
I don't see this sort of filter in the query too ...
<blockquote>
all_rows plan => parse call, execute call ..... with very long call time, lots of fetch calls
first_rows_n plan => parse call, execute call ... with very short call time, lots of fetch calls
</blockquote>
In the first case you would see last_call_et constantly growing until the fetches started
In the second case last_call_et would keep resetting to zero so fast you probably wouldn't see it as a non-zero value.
Having said that, I've just run a quick test in SQL*Plus on 11.1.0.6 and 10.2.0.3 to demonstrate this point, and 11g is behaving differently from 10g - in the case where the last_call_et should be growing it stays at zero.
Regards
Jonathan Lewis -
Hello Jonathan Lewis ,
Thanks for following that affair ...
But to be more precise ...
When you say :
A FETCH is a database call :
That means that it must affect the value of 'last_call_et' or not ?
(accordint to me, only user's activity do so and not "database internal callsl") ! isn't it ?
Your follonwing explanations seems to answers contrarily ....
But the question is there : what sort of call affetcs the value of "last_call_et"
When you say
all_rows plan => parse call, execute call ..... with very long call time, lots of fetch calls
first_rows_n plan => parse call, execute call ... with very short call time, lots of fetch calls
In the first case you would see last_call_et constantly growing until the fetches started
In the second case last_call_et would keep resetting to zero so fast you probably wouldn't see it as a non-zero value.
I don't understand : ok for the conséquences on the timing ... but if there are a lot of fetch call in both cases : the behaviour on the 'last_call_et' has to be equal ?
When you say
Having said that, I've just run a quick test in SQL*Plus on 11.1.0.6 and 10.2.0.3 to demonstrate this point, and 11g is behaving differently from 10g - in the case where the last_call_et should be growing it stays at zero.
"in the case" : in the case of the v11 or the v10 ?
In my case : i'am on a v10.2.0.3 ::: Is there a bug ?
Thanks for your answer ...
Regards
Edited by: user12046632 on 12 août 2010 07:14 -
user12046632 wrote:"A Fetch is a database call"
When you say :
A FETCH is a database call :
That means that it must affect the value of 'last_call_et' or not ?
"last_call_et" -- certainly sounds like that could be something to do with database calls, perhaps End Time of LAST database CALL.
A parse is also a database call, as is an execute ... I feel that perhaps I was a little too concise with my description. You asked why SAS could be doing lots of calls (although that wasn't exactly how you put the question) and I replied that calls could include fetches. And yes, the last_call_et is reset on every fetch (amongst other things - and ignoring the special behaviour of background processes).(accordint to me, only to user's activity do so and not to "database internal callsl") ! isn't it ?You don't want to believe everything you read on the internet - even if it did come from the online manuals. But a "fetch" is not a "database internal call" anyway, it's a call from a client to the server.Your follonwing explanations seems to answers contrarily ....All database calls - which you should read as "all calls from the client to the server".
But the question is there : what sort of call affetcs the value of "last_call_et"
>
>When you sayTrue - but in one case I've included an execute call that is active for a very long time, and in the other I've proposed an execute call that is active for a very short time. (And execute calls are calls, and I did ask you about the exact details of your observations.)
all_rows plan => parse call, execute call ..... with very long call time, lots of fetch calls
first_rows_n plan => parse call, execute call ... with very short call time, lots of fetch calls
In the first case you would see last_call_et constantly growing until the fetches started
In the second case last_call_et would keep resetting to zero so fast you probably wouldn't see it as a non-zero value.
I don't understand : ok for the conséquences on the timing ... but if there are a lot of fetch call in both cases : the behaviour on the 'last_call_et' has to be equal ?
>When you say11g
Having said that, I've just run a quick test in SQL*Plus on 11.1.0.6 and 10.2.0.3 to demonstrate this point, and 11g is behaving differently from 10g - in the case where the last_call_et should be growing it stays at zero.
"in the case" : in the case of the v11 or the v10 ?In my case : i'am on a v10.2.0.3 ::: Is there a bug ?Not that I know of.
>
Regards
Jonathan Lewis -
Jonathan Lewis wrote:Okuser12046632 wrote:"A Fetch is a database call"
When you say :
A FETCH is a database call :
That means that it must affect the value of 'last_call_et' or not ?
"last_call_et" -- certainly sounds like that could be something to do with database calls, perhaps End Time of LAST database CALL.
A parse is also a database call, as is an execute ... I feel that perhaps I was a little too concise with my description. You asked why SAS could be doing lots of calls (although that wasn't exactly how you put the question) and I replied that calls could include fetches. And yes, the last_call_et is reset on every fetch (amongst other things - and ignoring the special behaviour of background processes).
>Be sure that i don't do so.(accordint to me, only to user's activity do so and not to "database internal callsl") ! isn't it ?You don't want to believe everything you read on the internet - even if it did come from the online manuals. But a "fetch" is not a "database internal call" anyway, it's a call from a client to the server.
But the explanation of Oracle of the term 'cal' is not so precise at all ...
>OkYour follonwing explanations seems to answers contrarily ....All database calls - which you should read as "all calls from the client to the server".
But the question is there : what sort of call affetcs the value of "last_call_et"
>
>>
>>OkWhen you sayTrue - but in one case I've included an execute call that is active for a very long time, and in the other I've proposed an execute call that is active for a very short time. (And execute calls are calls, and I did ask you about the exact details of your observations.)
all_rows plan => parse call, execute call ..... with very long call time, lots of fetch calls
first_rows_n plan => parse call, execute call ... with very short call time, lots of fetch calls
In the first case you would see last_call_et constantly growing until the fetches started
In the second case last_call_et would keep resetting to zero so fast you probably wouldn't see it as a non-zero value.
I don't understand : ok for the conséquences on the timing ... but if there are a lot of fetch call in both cases : the behaviour on the 'last_call_et' has to be equal ?
If we consider that every call reset to 0 the value of 'last_call_et',
why, in the case of launching the same request by TOAD, shouldn't i see the reset to 0 when the fetches works (because in TOAD during the whole query, which means since the click on "launching query" to the retrieving of data, the value did not reset to 0) ?
>
>>ok. So it is in V11 that you have an incorrect behaviour ....When you say11g
Having said that, I've just run a quick test in SQL*Plus on 11.1.0.6 and 10.2.0.3 to demonstrate this point, and 11g is behaving differently from 10g - in the case where the last_call_et should be growing it stays at zero.
"in the case" : in the case of the v11 or the v10 ?In my case : i'am on a v10.2.0.3 ::: Is there a bug ?Not that I know of.
>>
>RegardsLAST NEWS : we have relaunched the query in SAS and observed that we have the same context of plan in both cases (by SAS en y TOAD) : ALL_ROWS.
Jonathan Lewis
Very strange ...
Last question :
Just to be sure : When you talk about "First Rows plan" : it means that the retrieve data are restricted to the first rows encountered
or
it is just the plan of the query which is in "that context" which means that the engine works so although the whole data are retrieved ....
thanks for your return
This discussion has been closed.