Forum Stats

  • 3,781,314 Users
  • 2,254,500 Discussions
  • 7,879,640 Comments

Discussions

About the last_call_et column in v$session

2»

Answers

  • Randolf Geist
    Randolf Geist Member Posts: 2,214 Silver Trophy
    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
  • user112472
    user112472 Member Posts: 8
    edited Aug 12, 2010 1:17PM
    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
  • Any further help ?
  • Randolf Geist
    Randolf Geist Member Posts: 2,214 Silver Trophy
    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
This discussion has been closed.