14 Replies Latest reply: Aug 13, 2010 2:28 PM by Randolf Geist RSS

    About the last_call_et column in v$session

    user112472
      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 ...
        • 1. Re: About the last_call_et column in v$session
          sb92075
          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.
          • 2. Re: About the last_call_et column in v$session
            790883
            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.
            • 3. Re: About the last_call_et column in v$session
              Jonathan Lewis
              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
              • 4. Re: About the last_call_et column in v$session
                user112472
                @ 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
                • 5. Re: About the last_call_et column in v$session
                  user112472
                  @ 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
                  • 6. Re: About the last_call_et column in v$session
                    user112472
                    @ 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 ...
                    • 7. Re: About the last_call_et column in v$session
                      Jonathan Lewis
                      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
                      • 8. Re: About the last_call_et column in v$session
                        user112472
                        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
                        • 9. Re: About the last_call_et column in v$session
                          Jonathan Lewis
                          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
                          • 10. Re: About the last_call_et column in v$session
                            user112472
                            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
                            • 11. Re: About the last_call_et column in v$session
                              Randolf Geist
                              user12046632 wrote:
                              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 ....
                              I'm not sure I understand your description of the problem, but if this means that SAS "joins" an Oracle table to SAS data then this might explain what you observe: SAS presumably fetches the data from the Oracle table and performs the "join" on the SAS side, and as outlined by others each of these fetch calls will reset the LAST_CALL_ET to 0.

                              However I might be wrong since you say that you run the "same query" in TOAD, which is simply not possible since within TOAD you cannot join anything to a SAS table. If you run the same in TOAD that has been executed by SAS on the Oracle side then we might need more details what exactly this is what you execute - is it a simple SELECT ... FROM TABLE or something more complex?

                              Regards,
                              Randolf

                              Oracle related stuff blog:
                              http://oracle-randolf.blogspot.com/

                              Co-author of the "OakTable Expert Oracle Practices" book:
                              http://www.apress.com/book/view/1430226684
                              http://www.amazon.com/Expert-Oracle-Practices-Database-Administration/dp/1430226684
                              • 12. Re: About the last_call_et column in v$session
                                user112472
                                Randolf Geist wrote:
                                user12046632 wrote:
                                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 ....
                                I'm not sure I understand your description of the problem, but if this means that SAS "joins" an Oracle table to SAS data then this might explain what you observe: SAS presumably fetches the data from the Oracle table and performs the "join" on the SAS side, and as outlined by others each of these fetch calls will reset the LAST_CALL_ET to 0.

                                However I might be wrong since you say that you run the "same query" in TOAD, which is simply not possible since within TOAD you cannot join anything to a SAS table. If you run the same in TOAD that has been executed by SAS on the Oracle side then we might need more details what exactly this is what you execute - is it a simple SELECT ... FROM TABLE or something more complex?
                                My question was, at this post, dealing with "when exactly and precisly Oracle reset the column 'last_call_et" ?
                                But you are also right : the aim of the question was to find out how SAS works.
                                Because, you are right : the aim of the SAS process is to make a join between an Oracle TABLE an a SAS Table.
                                Nevertheless, the query that we see in TOAD during the process doesn't show any join which means that SAS make that "join" in its environnement.
                                So Oracle seems to retrieve all data of the Oracle table et restrict it in its environnement.
                                But i have a doubt on what we see : because SAS must ask JUST ONCE this query if he makes the join after in its context ?
                                So SAS must have launched just one time the query in the Oracle side (i hope so ... else if it launches the query each time for each record in the join, it is absurde !!! BUT could explain why it lasts à long time and why the last_call_et is always at 0... but i can't imagine that)
                                That's why i wanted to know how many querys SAS send to Oracle.
                                And, for me, the answer could be brougth by the Elapsed Time of the last call ... unless we understand when this counter is reset to 0...
                                But in front of Jonathan's explication, we can't make apparently a 1per1 relation between the number of query send by the user and the time when the last_call_et is reset to 0.
                                Because every event( parse, execution and fetch) for a simple query car reset this to 0 ...
                                And i thougt that perhaps SAS send the same query multiples times ....

                                Si finally, if SAS send just, one time, its query (such as i do in TOAD when i launched manually this same query) and we have the same plan : why in the cas of SAs, the last_call_e is reset to 0 frequantly ?


                                It is a bit compilcated to explain this pb, even for me in english beacause i am not english-man speaking ...
                                I hope you have all undestood

                                (Above all, it would have been complex for me to explain it in french so ....)

                                Thanks for your reflexion about that subject ...

                                Edited by: user12046632 on 12 août 2010 10:14

                                Edited by: user12046632 on 12 août 2010 10:15
                                • 13. Re: About the last_call_et column in v$session
                                  user112472
                                  Any further help ?
                                  • 14. Re: About the last_call_et column in v$session
                                    Randolf Geist
                                    user12046632 wrote:
                                    Any further help ?
                                    As already mentioned it might be helpful if you provide more details what exactly you see is getting executed from SAS on the Oracle side. It is that "thing" that you say you also execute via TOAD and see the different behaviour in the V$SESSION.LAST_CALL_ET column.

                                    What also can be very helpful is to use Tanel Poder's "snapper" utility (For download and instructions how to use, see http://tech.e2sn.com/oracle-scripts-and-tools/session-snapper) to see what your session is doing - it would allow you identify the difference in the behaviour of SAS vs. TOAD, at least by what can be told from looking at the various statistics that snapper captures.

                                    Regards,
                                    Randolf

                                    Oracle related stuff blog:
                                    http://oracle-randolf.blogspot.com/

                                    Co-author of the "OakTable Expert Oracle Practices" book:
                                    http://www.apress.com/book/view/1430226684
                                    http://www.amazon.com/Expert-Oracle-Practices-Database-Administration/dp/1430226684