DevOps, CI/CD and Automation

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.

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.

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

@ 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 ...

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

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

"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).
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.
All database calls - which you should read as "all calls from the client to the server".

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

> 11g Not that I know of.
>

Regards
Jonathan Lewis

Ok
> Be sure that i don't do so.
But the explanation of Oracle of the term 'cal' is not so precise at all ...

> Ok
>
>>
>> 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) ?

>
>> ok. So it is in V11 that you have an incorrect behaviour ....
>>
> 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

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

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 ?

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