slowness on database when connecting to DB

[Roshan]

Oracle Database 11.2.0.3

Hi Erman,

we are experiencing latency issues when connecting to DB

I have checked the awr report. Please find below findings:

1. Elapsed time > DB time (load is high on DB)

2. The DB CPU and SQL net message using db link is high
GCI.docx
awrrpt_1_42154_42155.html
awrrpt_1_09_29_21June2018.html

Do you advise to increase the CPU or should I check with network team?

Regards,

Roshan

[ErmanArslansOracleBlog]

Is your network okay?
These dblink and server-client related network waits are on top five..
Your system is busy, but you have 4 cores.. So not that busy.

You have also log file sync wait.. Be careful with that.. -> Troubleshooting: 'Log file sync' Waits (Doc ID 1376916.1)

[Roshan]

Hi Erman,

thanks a lot for your support :)

I have checked the document.

" As a rule of thumb, an average time for 'log file parallel write' over 20 milliseconds suggests a problem with IO subsystem (the typical time may be much smaller for more modern storage systems with lots of disk caching and/or non-moving parts e.g. SSD, NVRAM, etc.)."

From the AWR report, I see the log file parallel write is below 20 ms. I don't think log sync wait will cause the issue.
Please advise.

I also see the SQL ID: 75n0zz4t6a3wu has high elapsed time. There must be some fine tunings to be done  with the query(maybe joins to be used). A DB link is also used by the query which points to IP address A. I have checked the alert log and saw network error for that IP

Fatal NI connect error 12170.

  VERSION INFORMATION:
        TNS for Linux: Version 11.2.0.3.0 - Production
        Oracle Bequeath NT Protocol Adapter for Linux: Version 11.2.0.3.0 - Production
        TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.3.0 - Production
  Time: 21-JUN-2018 13:32:11
  Tracing not turned on.
  Tns error struct:
    ns main err code: 12535

TNS-12535: TNS:operation timed out
    ns secondary err code: 12560
    nt main err code: 505

TNS-00505: Operation timed out
    nt secondary err code: 110
    nt OS err code: 0
  Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=A)(PORT=1521))


I will check with the network team for the network wait event.

[ErmanArslansOracleBlog]

TNS-12170 may be encountered when client abormally closes connection.. This may be due to network.
You also have network waits in AWR..
Please check the network.

As for the log file sync,  please also check "Excessive Application Commits".. This may also trigger the log file sync event..

[ErmanArslansOracleBlog]

Again, You have lots of waits when querying the records from a remote database using a dblink..

Why is that so? Consider improving your design..

As it is shown in your AWR, you are waiting for the records from dblink all the time..

[Roshan]

Thanks.

What should I tell the network team to check? There is lots of waits when querying the records from a remote database using a dblink

[ErmanArslansOracleBlog]

I said network team, but I also said other things ..

Here ->

Again, You have lots of waits when querying the records from a remote database using a dblink..
Why is that so? Consider improving your design -- I don't know your exact neeeds, but for example, you may use Materialized views. Using MWs you query one time from the db links and populate your db and then you query the MW locally. Currrently I think you query from the dblink directly on-the-fly. This may also be necessary(if you need so) but consider revising it.

Network team should check the network between these databases.. (databases accessed by dblink)
Also a check is needed the database and the client because in AWR, there are also "SQL*Net more data to client" waits.. I don't that the type and size of data you are querying from your application, but you may consider improving your desing for this as well..