Re: Sql net msg from client and fetch arrays

There is a hidden parameter "_ash_sample_all" with the obvious effect. Defaults to FALSE, can be set to TRUE at the system level without restarting the instance.
It's probably not a good idea for most systems though. I've seen so many AWR reports showing connected sessions in the hundreds or thousands, with active sessions running in the order of a few 10s.

There would be a lot of systems where the memory requirment for ASH would probably jump by a factor of a hundred or more if you collected all connected sessions on every sample. (The same applies for dba_hist..., of course).

Regards
Jonathan Lewis

On Sun, 24 Jan 2021 at 19:32, Clay Jackson (cjackson) < Clay.Jackson_at_quest.com> wrote:

> And (fully agreeing with what JL and MWF said) – Cary Millsap has some
> interesting insights into “sql_net message” in the Method-R book and
> Workbench.
>
>
>
> I heartily DISAGREE with Oracle’s approach to effectively ignoring this –
> yes, it’s “outside” of Oracle, and so “not an RDBMS problem”; but if I have
> users waiting on the end of a high-latency network (for WHATEVER reason),
> it darn sure IS “a problem”!
>
>
>
> Where we’re starting to see this is in “hybrid cloud”, where the pieces
> are located on different continents.
>
>
>
> “The speed of light isn’t just a good idea, IT’S THE LAW” (at least until
> we get to Quantum Entangled computing).
>
>
>
>
>
> Clay
>
>
>
> *From:* oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> *On
> Behalf Of *Mark W. Farnham
> *Sent:* Saturday, January 23, 2021 4:52 PM
> *To:* jlewisoracle_at_gmail.com; oracle-l_at_freelists.org
> *Subject:* RE: Sql net msg from client and fetch arrays
>
>
>
> *CAUTION:* This email originated from outside of the organization. Do not
> follow guidance, click links, or open attachments unless you recognize the
> sender and know the content is safe.
>
>
>
> And (meaning not taking exception to anything JL has said, with which I
> heartily agree)…
>
>
>
> Sometimes it can be arranged to run queries like this on the database
> server depositing the result in a file to be dragged back to the client to
> look at. Usually this so dramatically reduces the latency and increases the
> bandwidth that all the dithering you are seeing happens in so little
> elapsed time that it is not important.
>
>
>
> The earliest versions of the Oracle E-business applications used the
> “concurrent manager” with a report destination to do just that
> comprehensively at a time when network latencies were much higher and
> bandwidths were much lower, so your mileage may vary. Still, if you can run
> the query on the server as a test it may tell you if the gains of that
> engineering might be net useful (or not).
>
>
>
> mwf
>
>
>
> *From:* oracle-l-bounce_at_freelists.org [
> mailto:oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org>] *On
> Behalf Of *Jonathan Lewis
> *Sent:* Saturday, January 23, 2021 8:44 AM
> *To:* oracle-l_at_freelists.org
> *Subject:* Re: Sql net msg from client and fetch arrays
>
>
>
>
>
> Answering the last bit first -
>
> It's possible that the application sets a memory limit for the fetch, and
> then an internal mechanism derives an array size from the column lengths of
> the data to be fetched.
>
>
>
> Finding time spent on the network and at the client - the best method
> depends on what licences you have, what you're allowed to do in the
> application, and what access you have to the database, and WHEN you can get
> access. (And how accurate you need to be).
>
>
>
> e.g.
>
>
>
> 1) If you are licensed for ASH etc. then you could check the SQL Monitor
> report - it will show you the run time of the query (Global Information .
> duration) and the work done by the query (Global Stats . Elapsed time). As
> a reasonable approximation "duration" - "elapsed time" = client/network
> time.
>
>
>
> 2) If you can't get at the SQL Monitor but know the SQL_ID of the query
> of interest you can query v$active_session_history directly (or
> dba_hist_active_sess_history). Each execution of the query should have a
> separate SQL_EXEC_ID so a query like:
>
>
>
> select sql_exec_id, count(*), max(sample_time) - min(sample_time) from
> v$active_session_history where sql_id = 'xxxxxxxxxxxxxxxx' group by
> sql_exec_id;
>
>
>
> should give you the (approximate) difference between the first and last
> calls of the execution, and the number of active samples (= 1 second of
> time for v$active_session_history, or 10 seconds for
> dba_hist_active_sess_history). The time will be in days, so multiply by
> 86400 for seconds. The difference between that and count(*) -- or count(*)
> * 10 -- gives you the client/network time. APPROXIMATELY.
>
>
>
>
>
> 3) If you can modify the code that calls the big queries, and since you
> expect them to take a lot of time, it's worth enabling SQL trace for the
> queries of interest. You could set the module and action to some suitable
> value before executing a query, then clear them afterwards. The trace files
> would report these values, and a call to tkprof (or trcsess) can extract
> data based on module/action. So you can find the right trace files and
> generate the tkprof reports. This should give you a fairly direct view of
> the client/network time. (Note this type of shotgun approach is viable
> only when you have a small number of large queries that you want to trace -
> if some of your code goes into single row processing - e.g. using a "fetch
> by key" for every row in your 10M row array fetch) then the overheads on
> that bit will swamp any useful information.
>
>
>
> Regards
>
> Jonathan Lewis
>
>
>
>
>
>
>
>
>
>
>
>
>
> On Fri, 22 Jan 2021 at 01:08, Moustafa Ahmed <moustafa_dba_at_hotmail.com>
> wrote:
>
> Hello lister!
>
> I have couple of question about sql net msg from client..
> 1-considering it is an idle event and it is not sampled at ash or dba_hist
> for ash which views can show which sql_id’s spent most of the time of that
> idle event?
>
> 2-as it sounds odd I can explain why I’m asking when running massive DW
> sql’s that retrieves (unfortunate tens of millions of rows) adding to that
> oracle last execution (when the statement is still fetch rows and feeding
> them to the top row source) it may look confusing to DBA’s and app folks
> That. Wing said we may have a sql which is spending most of the time
> retrieving the rows not processing the rows so if we can spot the weight of
> sql net msg’s from client it can help big deal!
>
> 3-some apps do have variant rows per fetch for different sql’s varying
> from 1000 to 100
> Meanwhile the app itself has the maximum set for rows perf fetch what
> causes that value to change from one sql to another although the app does
> not dictate that?
>
> Thank you,
>
>
>
>
> <https://nam12.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.avg.com%2Femail-signature%3Futm_medium%3Demail%26utm_source%3Dlink%26utm_campaign%3Dsig-email%26utm_content%3Dwebmail&data=04%7C01%7Cclay.jackson%40quest.com%7Cf460991d139d41733bfe08d8c0025987%7C91c369b51c9e439c989c1867ec606603%7C0%7C0%7C637470463653725542%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C2000&sdata=TY07mw12W349LwF4uHUiRuRYvf1fsHDO6FLn%2FxOMxJ0%3D&reserved=0>
>
> Virus-free. www.avg.com
> <https://nam12.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.avg.com%2Femail-signature%3Futm_medium%3Demail%26utm_source%3Dlink%26utm_campaign%3Dsig-email%26utm_content%3Dwebmail&data=04%7C01%7Cclay.jackson%40quest.com%7Cf460991d139d41733bfe08d8c0025987%7C91c369b51c9e439c989c1867ec606603%7C0%7C0%7C637470463653725542%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C2000&sdata=TY07mw12W349LwF4uHUiRuRYvf1fsHDO6FLn%2FxOMxJ0%3D&reserved=0>
>
>
>

--
http://www.freelists.org/webpage/oracle-l




image/png attachment: image001.png