Forum Stats

  • 3,837,534 Users
  • 2,262,268 Discussions
  • 7,900,315 Comments

Discussions

Categories

About the last_call_et column in v$session

user112472
user112472 Member Posts: 8
edited Aug 13, 2010 3:28PM in General Database Discussions
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 ...
· Share on TwitterShare on Facebook
«1

Answers

  • sb92075
    sb92075 Member Posts: 42,196 Blue Diamond
    when all else fails, Read The Fine Manual
    http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/dynviews_2088.htm#i1414383
    LAST_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.
    · Share on TwitterShare on Facebook
  • 790883
    790883 Member Posts: 3
    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.
    · Share on TwitterShare on Facebook
  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,998 Blue Diamond
    user12046632 wrote:

    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 !!!!
    Did you check that SAS and TOAD were using the same execution plans ?

    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
    · Share on TwitterShare on Facebook
  • user112472
    user112472 Member Posts: 8
    edited Aug 12, 2010 4:25AM
    @ 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
    · Share on TwitterShare on Facebook
  • user112472
    user112472 Member Posts: 8
    edited Aug 12, 2010 4:25AM
    @ 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
    · Share on TwitterShare on Facebook
  • user112472
    user112472 Member Posts: 8
    @ 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 ...
    · Share on TwitterShare on Facebook
  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,998 Blue Diamond
    user12046632 wrote:

    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 ...
    A FETCH is a database call - so the two scenaros may be:
    <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
    · Share on TwitterShare on Facebook
  • user112472
    user112472 Member Posts: 8
    edited Aug 12, 2010 10:14AM
    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
    · Share on TwitterShare on Facebook
  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,998 Blue Diamond
    user12046632 wrote:

    When you say :
    A FETCH is a database call :
    That means that it must affect the value of 'last_call_et' or not ?
    "A Fetch is a database call"
    "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 ....
    But the question is there : what sort of call affetcs the value of "last_call_et"
    All database calls - which you should read as "all calls from the client to the server".

    >
    >
    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 ?
    True - 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.)

    >
    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 ?
    11g
    In my case : i'am on a v10.2.0.3 ::: Is there a bug ?
    Not that I know of.
    >

    Regards
    Jonathan Lewis
    · Share on TwitterShare on Facebook
  • user112472
    user112472 Member Posts: 8
    edited Aug 12, 2010 11:43AM
    Jonathan Lewis wrote:
    user12046632 wrote:

    When you say :
    A FETCH is a database call :
    That means that it must affect the value of 'last_call_et' or not ?
    "A Fetch is a database call"
    "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).
    Ok
    >
    (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.
    Be sure that i don't do so.
    But the explanation of Oracle of the term 'cal' is not so precise at all ...

    >
    Your follonwing explanations seems to answers contrarily ....
    But the question is there : what sort of call affetcs the value of "last_call_et"
    All database calls - which you should read as "all calls from the client to the server".
    Ok
    >
    >>
    >>
    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 ?
    True - 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.)
    Ok
    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) ?

    >
    >>
    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 ?
    11g
    In my case : i'am on a v10.2.0.3 ::: Is there a bug ?
    Not that I know of.
    ok. So it is in V11 that you have an incorrect behaviour ....
    >>
    >
    Regards
    Jonathan Lewis
    LAST 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.

    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
    · Share on TwitterShare on Facebook
This discussion has been closed.