intermittent slow INSERT...VALUES
Narendra, November 17, 2024 - 10:49 pm UTC
Hello Chris/Connor,
Setup:
Database => Oracle 19.23 ExaC@C (4-node RAC)
JDBC => 23.3 ojdbc8.jar
I am trying to diagnose an intermittent performance issue where application and a SAAS enterprise monitoring tool, reports that a relatively simple INSERT...VALUES takes anywhere from 3 seconds to 2 minutes. The table has 3 columns; no LOB columns; with single partition and a local non-unique index and with no PK/UK constraint.
However, what is making it very difficult, if not impossible, is a combination of below reasons:
1. No patter/sequence of events identified that leads to the slow performance
2. When application/SAAS tool reports slow executions, there is literally no clue in AWR/ASH. ASH data, GV$ACTIVE_SESSION_HISTORY or DBA_HIST_ACTIVE_SESS_HISTORY report at the most one row per execution.
3. The issue can not be reproduced in any of the non-production environment
4. Almost 95% of executions complete within milliseconds (as expected) but the other 5% run slow and relative customer-facing application journey times out.
Any idea how I can even begin investigation into this? It is (rightly) considered risky to enable any kind of logging/tracing, either on application side or on database side as the logs/traces can quickly get huge and fill up file system. We will literally have to have someone monitor the file system, either on app server or on DB server, to manage the space and trace files....and that too for days, which makes this very difficult to use.
In an ideal world, what would help is ability to trace/log only those executions which take more than a defined threshold. But sql trace does not seem to have such an option. Neither the Oracle JDBC driver appear to have this option.
Any suggestions on how I can approach this issue?
My line of thought so far is
1. First, find out the discrepancy, if any, between the response time reported by app/SAAS monitoring tool and database
2. If no discrepancy is found between app/SAAS and database about execution time of the INSERT...VALUES then (somehow) try to get more details on the slow executions from database (like sql tracing output)
Thanks in advance
November 26, 2024 - 2:16 pm UTC
A single INSERT .. VALUES statement on a 3 column table should be essentially instant. Things that might affect this are:
- Blocking on primary/unique key values
- Insert triggers
I'm guessing you've ruled these out and there are no other obvious causes. In which case enabling some form of tracing is the way forward. Start logging a subset of the servers to find the problem.
to manage the space and trace files....and that too for days, which makes this very difficult to use.
Surely you can setup some kind of cleaning job to remove the unwanted files?
there is literally no clue in AWR/ASH
That strongly suggests the problem is somewhere in the application/network rather than the database. End-to-end tracing really is the way forward here though.