Re: How to identify server client requester in the Oracle 12.2.x

From: Eriovaldo Andrietta <ecandrietta_at_gmail.com>
Date: Sun, 11 Jul 2021 22:02:40 -0300
Message-ID: <CAJdDhaPYAzvGUd-bbP24sYpee1FF0qHR=Sny6Ui=vFbNykTe-Q_at_mail.gmail.com>

Hi Priit,
Thanks for your answers.

1.) We did a test running a shell script at the application server side. This script connects to the database, executes a .sql script and returns the time.
Comparing the time running at the application side with the same script running inside the database server ...
We saw : at the application side, the time is variable ... I know that we need to consider the network traffic ... at the database side, the time keeps stable. The network team is preparing some tool/configuration in order to investigate if packet loss occurs.

2.) About the DBMS_APPLICATION_INFO, it is a good way. I had already suggested it and more .. to add logs and logs to trace the java code. There are also options as profile, yourkit that can debug the java code. Dev team also is working to get more information.

3.) Using the homol environment, also is a good idea ... but it is not like the production environment.
In the production everything is different, there are more transactions, more traffic ... environment configuration and other things that can change results.

There was a test that ran between 15:00 to 15:12, at the application server it took 400 seconds.
So I went to the database and ran this query :

SELECT tab.sql_id,
       obj.OBJECT_ID,
       obj.OWNER,
       obj.OBJECT_NAME,
       tab.SNAP_ID,
        SUM(hst.parse_calls_delta)          parse
       ,SUM(hst.executions_delta)           executions
       ,SUM(hst.sharable_mem)/1024          sharable_mem
       ,SUM(hst.buffer_gets_delta)          buffer_gets
       ,SUM(hst.disk_reads_delta)           disk_reads
       ,SUM(hst.cpu_time_delta)/1000000     cpu_time
       ,SUM(hst.elapsed_time_delta)/1000000 elapsed_time
       ,SUM(hst.iowait_delta)/1000000       iowait
       ,SUM(hst.clwait_delta)/1000000       clwait
       ,SUM(hst.apwait_delta)/1000000       apwait
       ,SUM(hst.ccwait_delta)/1000000       ccwait
       ,SUM(hst.rows_processed_delta)       rowss
FROM DBA_HIST_ACTIVE_SESS_HISTORY TAB ,
     dba_objects obj,
     dba_hist_sqlstat hst

WHERE TAB.SAMPLE_TIME > to_date('10/07/2021 15:00:00','dd/mm/yyyy hh24:mi:ss')
  and TAB.SAMPLE_TIME < to_date('10/07/2021 15:12:00','dd/mm/yyyy hh24:mi:ss')
and program like '%nodeprd01%'

and tab.CURRENT_OBJ# = obj.OBJECT_ID
and hst.snap_id = tab.SNAP_ID
and hst.instance_number = tab.INSTANCE_NUMBER
group by tab.sql_id,
       obj.OBJECT_ID,
       obj.OWNER,
       obj.OBJECT_NAME,
       tab.snap_id;

The result of this query was only 1 sql_id ... The time spent on the database was a few seconds to run this sql operation. I am not sure if the query above is the right way to get all "sql_id" that ran at that interval. The best way is turn on trace in the session or node in order to retrive all queries.

I believe that with the support of the network team and dev team ... it will be clarified.

Best Regards
Eriovaldo

Em dom., 11 de jul. de 2021 às 11:46, Priit Piipuu <priit.piipuu_at_gmail.com> escreveu:

>
> On Sun, 11 Jul 2021 at 01:13, Eriovaldo Andrietta <ecandrietta_at_gmail.com>
> wrote:
>
>>
>> The Java application shows that it is waiting for the data
>>
>>
> Does it mean threads are waiting on sun.nio.ch.SocketDispatcher.read0
> method or somesuch? Do the waits resolve by itself after some time, in less
> than 127s? We had a rather interesting case of a packet loss somewhere in
> the network, which showed up as an extra latency, but only on the
> application side.
>
> If application thread is idle, or doing something else, then it sounds
> like a bug in the app.
>
>
>> If yes, can I use this data to inform the application team to locate
>> the jboss stack at the client application server ?
>>
>
> This depends on OS. For example, in Linux file descriptor table is per
> process, so to correlate socet with native thread using it, application
> team needs strace. (Haven't tried it, but in theory this should be possible
> ;) ) Much easier would be to ask developers to set Java thread id with
> DBMS_APPLICATION_INFO.
>
>
>> Is there a way to locate the another session used by the Application
>> in the database ?
>>
>
> Without additional instrumentation, no. If the issue is reproducible in
> non-production environments you could try with small number of sessions.
> Tracking, say, 4 sessions is much easier than tracking 200.
>
>

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

Received on Mon Jul 12 2021 - 03:02:40 CEST