Find which Oracle SQL_ID hits the SQL*Net break/reset to client event | Tanel Poder Consulting

[giscus[bot]]

Find which Oracle SQL_ID hits the SQL*Net break/reset to client event | Tanel Poder Consulting

I’m finalizing things for launching my next-gen Linux Performance & Troubleshooting and Advanced Oracle Troubleshooting video courses in June, so expect much more blogging (and new scripts) to show up here. By the way, this week I’m running a spring-cleaning sale with unprecedented discounts!
Oracle’s SQL*Net break/reset to client wait event shows up whenever Oracle (SQL, PL/SQL call, etc) returns an ORA- error back to the client instead of the normal expected results (and ORA-0 under the hood): - Linux, Oracle, SQL performance tuning and troubleshooting - consulting & training.

https://tanelpoder.com/posts/find-which-oracle-sql-id-hit-sqlnet-break-reset-event/

[dincosman]

Hi Tanel,

I’m a fan of your work and troubleshooting skills. Thank you for the wonderful explanation.

Please correct me if I'm wrong: Not all, but some SQL statements that fail during parsing (due to syntax errors) are typically not stored in the shared pool, so they don’t appear in v$sql or similar views. Some of them might be partially visible through the kglnaobj column in sys.x$kglob, while others are not, even though they trigger a SQL*Net break/reset to client event.

In such cases, even if we manage to capture the prev_sql_id using break.sql, we still can’t retrieve the sql_fulltext unless we’re using an AFTER SERVERERROR ON DATABASE trigger or enabling errorstack tracing.

This brings me to a puzzling behavior I’ve observed in our environment:
After enabling errorstack tracing for events like ORA-904 and ORA-936, I noticed that during daily peak times, an average of 5 errors per minute are logged in the alert.log along with related trace files. However, in the errorstack dumps, there’s a line (I don’t recall the exact wording) along the lines of:

"Current SQL statement is not available."

Surprisingly, these statements are also not captured by the AFTER SERVERERROR ON DATABASE trigger. the trigger doesn’t even fire. Event 10035 tracing didn’t help either. They are only recorded when an errorstack is opened.

At one point, I suspected silent handling in PL/SQL, so I replaced all WHEN OTHERS THEN NULL blocks with logging behavior to see if anything was being swallowed, but nothing surfaced.

What would you suggest as the next steps to further investigate or capture these SQLs? My hunch is that they might be internal statements, never raised to the client, especially since no users or developers have reported related errors. But I’m wondering if there’s something I might be missing.

Thanks again for your time and guidance.

Best regards,
Osman DİNÇ

[tanelpoder]

Yeah, since the parse failed, this cursor can't be used, there's no reason to keep that allocation around in shared pool (although it initially was allocated from shared pool). In some cases you still might have the parent cursor (with SQL text) in cache as it might have another, successfully compiled child under it (perhaps the same SQL executed in a different schema). There may be other special cases too, as you said.

Regarding the SERVERERROR not firing and event 10035... did you run your tests as SYS by any chance? Some things work differently (are not enabled) when being SYS... not sure if this is the case here.

Is the problem you're trying to solve about having some invalid "partial" cursors in library cache created by someone - or does your app actually get ORA- errors or just see SQL*Net break/reset waits when interacting with the DB?

[tanelpoder]

Also, which level did you use for the event 10035? Apparently you need level 3+ for recursive statements:

https://dincosman.com/2022/12/17/parse-error-warnings/

[dincosman]

Hi Tanel,

Thanks again for getting back to me. I really appreciate it, especially knowing how busy you always are.

I think I may not have explained the situation clearly in my previous message, so let me try to clarify.

Here’s what I find unusual:

I’ve enabled ERRORSTACK for ORA-904 with level 4, set event 10035 with level 4 and also implemented a database SERVERERROR trigger. When I execute a test case such as select "?" from dual using a regular user account (not an Oracle-managed one); All three mechanisms, 10035, ERRORSTACK and the trigger work as expected and capture the ORA-00904 error.

So far, so good.

However, here’s the strange part: when ERRORSTACK is active, I notice additional trace files being generated and recorded in the alert.log. These trace files seem unexpected to me. If I disable ERRORSTACK but leave 10035 and the SERVERERROR trigger enabled, no such trace files are produced.

To be more specific:

When only ERRORSTACK for ORA-904 is enabled (with 10035 and the trigger disabled), and I let the system run for about 5 minutes, I observe around 15–20 trace files being generated.

Under a similar application workload and time window, if I disable ERRORSTACK but enable only 10035 and the SERVERERROR trigger, no trace files are created.

This shows that ERRORSTACK is catching something that 10035 and SERVERERROR trigger do not, possibly internal errors or signals that don’t propagate with 10035 or database SERVERERROR trigger.

What makes this more puzzling is that the application shows no indication of errors, everything appears to function normally on the user side. Yet, when ERRORSTACK is enabled, trace files keep appearing for ORA-904 and ORA-936.

Looking at these trace files:

The service names point to regular user sessions—not SYS.

The “Current SQL Statement for this session” is not available in trace files.

However, some SQLs are listed in the parser state section, possibly from previous activity in the session.

This behavior is confusing, and I wonder whether it could be tied to the client libraries or a bug. The database is running on Oracle 19.26, and the clients use ODPM 19 clients.

Do you have idea what might be happening behind the scenes?

Thanks again for your time and support.

[tanelpoder]

Haha, I just now noticed that I quoted your own blog back to you :-) You've already written about the latest bugfix/diagnostic improvement re: event 10035 & alert log. Perhaps it's an unintentional coincidence right now, where these latest changes have multiple modes/combinations of tracing kicking in or not kicking in. Without ERRORSTACK enabled, perhaps the _kks_parse_error_warning=100 parameter affects you too - you'd need to run your bad test query 100+ times in a row. But I see you've written about this in on Oracle 19.16 already too (but perhaps something has changed again over the patchsets).

I'll think about this a little (working on some new Oracle troubleshooting material anyway), but I guess going one level deeper would mean hooking into some standard error/event checking function calls with perf or eBPF and seeing where such an error is internally raised by internal function calls.

If this is a test env, then you could enable full DB SQL tracing (or SQL trace just for the common sessions experiencing this) and see if any interesting SQL statements are parsed/opened before that... I think you already wrote about CURSORDUMP too. Btw not all events take dump commands as "attachments". For example (last time I checked), you can add ERRORSTACK for 10046 event, but it won't fire any custom dumps.