Skip to content

T3. Analysing a Cause with AI (CogentAI, SQL, Thread Dumps, Events)

Diátaxis: Tutorial · Audience: operators / administrators ← Back to contents · ← T2 Finding and Tracing from the Dashboard

If T2 found the dashboard red and slow, this exercise follows the four ways of having the AI analyse the cause -- ① the whole system with CogentAI Diagnosis, ② the SQL of a slow transaction with AI Query Diagnosis, ③ the point where things stalled with a thread dump → AI analysis, and ④ the events that have piled up with AI analysis on the spot. It takes about 9 minutes.

Prerequisite The AI buttons are visible only when an administrator has turned them on (Settings ▸ Cogent AI). If they are not visible, ask an administrator. The AI features generally are gathered in H14. Getting AI Analysis of Charts and Events.

Part 1. CogentAI Diagnosis — the Whole System at Once​

When the dashboard is slow and you have no idea where the cause is, this is the quickest first step. The CogentAI Diagnosis button in the middle of the top of the screen (the header) checks the whole selected app group.

Step 1 — Choosing the Group to Diagnose​

First choose the app group to analyse in the context selector (→ H1). AI diagnosis always looks at the whole group currently selected.

Step 2 — Pressing the AI Diagnose Button​

  1. Click AI Diagnose (the CogentAI icon plus the label) on the right of the header. (Hovering shows "Is anything wrong with the system now -- AI diagnosis".)
  2. The CogentAI widget opens and automatically asks about the state of the whole group -- "Analyse the current situation and tell me whether there is any problem with the [group name] application and system."
CogentAI Diagnosis -- the automatic question and the whole-group analysis report in the widget on the right

Result -- the AI checks the group overall (application and system) and, as in the screen above, reports the anomalies and where to look next in a structured form: the data retrieval period → the application position → the performance metric analysis. Follow-up questions can be asked in the input box at the bottom of the widget.

When there are delays or errors, the answer comes with links to the transactions themselves. The AI does not merely say "there are slow requests" -- it points at which ones.

Clicking a link opens the transaction detail right there, without leaving the screen (it does not jump to a new tab). The chat panel folds away while the detail is open, and you return to the conversation when you close it.

Note Opening the performance analysis tab in the detail takes you straight to the problem-detection cards -- the shortest path from the AI's estimate to the actual data.

Diagnosing from My Dashboard​

You can run the same diagnosis from My Dashboard, where the target is the application group on that screen. If the widget is pinned to one group, that group is what it looks at.

It picks the transactions with the largest delays automatically and analyses them too, so you do not have to decide what to look at first.

Note This button always looks at the whole group, regardless of the tab you are on. To narrow to one chart, use the AI icon on that chart card; for a T-Map pattern, use the AI button on the dot (→ T2 step 4).

Step 3 — Cross-Checking the Answer​

An AI answer is a starting point. Confirm the metric it points at against R2. Chart Reference and the actual charts, turning the estimate into fact.

Caution The AI provides estimates. For actions that are hard to undo, such as a restart or a configuration change, a person should confirm the evidence before deciding.

Part 2. Slow SQL → Diagnosing the Query with AI​

Going into a slow transaction found in T2 or H6 usually reveals slow SQL inside it. Before a person reads that SQL's execution plan, have CogentAI analyse it first and get a hypothesis for the cause and an index suggestion.

Step 4 — Finding the Slow SQL and Opening the SQL Dialog​

  1. Go into the detail of the slow transaction (→ T2 step 4 · H6. Finding the Cause of a Slow Transaction).
  2. Pressing the [SQL] button on a database span in the waterfall opens the SQL query dialog.
  3. In the dialog, check the query as actually executed (with the bind values filled in) and the fetch and execution statistics.
The transaction call flow -- the SQL button on the right of a database span

The example above is a screen where one entry has been chosen from the transaction query opened by dragging on the T-Map. Database calls (executeQuery) repeat in the call flow (waterfall), and a [SQL] button appears on the right of each database span -- that button is the entrance to the SQL query dialog.

The SQL query dialog -- database connection details, the SQL query, the parameters, the mapped query, and FETCH

The dialog shows, in order, the database connection details (driver, host, instance, user), the SQL query (with ? bind variables), the parameters, the mapped query with the bind values filled in, and FETCH (count and time). The copy button on each block takes the query straight away.

Result -- one SQL statement is open in the dialog, ready to analyse.

Step 5 — Pressing "AI Query Diagnosis"​

  1. Press the AI Query Diagnosis button (the CogentAI icon) in the dialog header (beside the status badge) or footer (to the left of [Close]).
  2. That SQL is passed to CogentAI together with the database type and comes back with an execution plan analysis plus index recommendations.
  3. The dialog does not close; the result appears in the CogentAI panel at the bottom right (with a "check the result in the CogentAI panel" toast).
The CogentAI panel -- the query execution plan visualization and the SQL under analysis

Result -- before interpreting the execution plan yourself, the AI has already picked out why it is slow and how to fix it (indexes and so on).

How to Read the AI Query Diagnosis Report​

The report in the CogentAI panel is laid out in the following order. Reading from the top gives you symptom → cause → prescription.

1) The execution plan -- the visualization chart and the table

The Query Execution Plan visualization at the top of the panel shows the execution plan as a tree. Nodes with a high cost are highlighted in colour, and hovering over a node shows its cost, estimated rows, and severity -- TABLE ACCESS FULL (a full scan) or a node with critical severity is the suspect. The same content is also provided as the query plan table (operation, cost, E-Rows, E-Time), so if you are comfortable with EXPLAIN output you can read the table directly.

The query plan table -- cost and estimated rows per operation

2) The table structure (ERD) and the bottleneck

Together with an entity relationship diagram (ERD) of the tables under analysis, it points out what the query does (joins, aggregation, sorting) and the performance bottleneck in a step-by-step table -- which scan repeats how many times, and why an index is not used, are set out here.

The AI analysis -- the table ERD, an overview of what the query does, and the performance bottleneck

3) The optimization -- an improved SQL statement and index suggestions

Following the bottleneck analysis, it offers a rewritten SQL statement (consolidating subqueries into a CTE, for example), a list of recommended indexes, and the expected improvement from applying them (for example, "currently 3,086 ms → expected to fall to about a fifth").

The optimization -- an example of improved SQL consolidating subqueries into a CTE
The index creation suggestion -- the recommended indexes and the expected improvement

Note On what basis? The AI looks at that SQL's execution plan plus table and index statistics plus issue detection (full scans, missing indexes) and goes as far as suggesting indexes. What is the impact on production? The console's AI Query Diagnosis only runs EXPLAIN (the estimated execution plan) and statistics queries and does not actually run the query, so the burden on the production database is minimal.

Note A SQL statement that failed (with its error message) can be sent for analysis as it is too, so it is also used for diagnosing the cause of an error. However, errors where the table or view does not exist (ORA-00942 and the like) cannot usefully be analysed, so the request is blocked and a notice toast appears.

Caution The AI's suggestions (creating an index and so on) are a starting point. Before applying them, cross-check the DBMS metrics and the execution plan against R2.3 DBMS Charts and a DBA review.

Part 3. Thread Dump → Analysis with AI​

When "the instance is alive but there is no response, or it is slow" (a hang or a stall), take a thread dump to see what the threads were doing when they stalled, and have the AI analyse it.

WAS Troubleshooting -- thread dump analysis

Step 6 — Taking a Thread Dump​

  1. Go to left menu ▸ WAS ▸ Troubleshooting and open the Thread Dump Analysis tab. Choose the instance to diagnose at the top. (The same screen also has heap object, open files, network state, and active thread control tabs.)
  2. Choose a dump from the thread dump list (or click the Request button for a new one).
  3. The thread dump analyser shows the thread list and their states (Runnable, Blocked, Waiting, Timed waiting, and so on). Choosing a thread shows its detailed stack in the thread detail analysis on the right.

Result -- you have the threads that are stalled now and their stacks.

Note Many blocked suggests lock contention, and many waiting suggests thread pool exhaustion (E3.2). A dump is sometimes collected automatically when a deadlock or OOM event occurs (R3).

Step 7 — Analysing the Dump with AI​

  1. Press the CogentAI Analysis button in the thread dump list toolbar.
  2. The AI summarizes and analyses the dump and points out the common stack, whether there is a deadlock, and the call targets taking a long time.
Thread dump CogentAI analysis -- the thread groups by state and the candidate causes, set out in a table

Result -- without reading hundreds of threads one by one, the AI picks out the thread groups by state and the related classes and candidate causes in a table, as in the screen above. Check the common cause of the stall first, then cross-check that thread's stack in the analyser from step 6.

Heap object analysis (for memory leaks) can be obtained the same way → H7. Checking for a Memory Leak · H20. Checking Agents.

Part 4. The Alert Centre and Event List → AI Analysis of an Event​

When a threshold is passed or a state changes, the system records that occurrence as an event. When an event leaves you stuck, you can have the AI analyse that one event straight away.

The event list and the detail drawer -- AI diagnosis

Step 8 — Opening the Alert Centre and the Event List​

  1. The alert centre -- the bell icon on the right of the header quickly shows the critical and warning alerts that have just occurred. (A red badge appears on the bell when there is a critical alert.)
  2. The event list -- see every event at left menu ▸ Event. It is organized by level (critical, warning, information), time of occurrence, kind, target, and message, with a bar chart at the top showing the volume by time of day.
  3. Clicking an event row opens a detail drawer on the right showing the kind, target (instance/host), and message together with the what / why / impact / checks description.

Step 9 — Analysing the Event with AI​

  1. Press the AI icon on the right of the event row, or the AI Diagnose button in the detail drawer header.
  2. That event's kind, level, target, and message (plus any automatically collected thread dump or diagnostic data, as for a deadlock or OOM) are passed to CogentAI together.
  3. The AI reports a hypothesis for the cause of that event and the next things to check in plain language.

Result -- even a baffling event can be asked, on the spot, "why did this happen and what should I look at first".

Note For an event where a dump was collected automatically, such as a deadlock or an OOM, that dump is analysed too and the answer is more specific. The meaning and points to investigate per kind of event are in R3. Event Reference.

Done — What You Can Now Do​

  • Have the whole selected group's cause analysed at once with AI Diagnose in the header
  • Have the SQL of a slow transaction analysed as far as the execution plan and indexes with AI Query Diagnosis
  • Take a thread dump to see the stalled threads, and have CogentAI analyse that dump
  • Have one event analysed with AI Diagnose from the alert centre or event list
  • Cross-check an AI answer against the charts and reference before deciding on an action

Next Steps​

What you want to doGuide
The full set of AI buttons (header, chart, transaction, event)H14. Getting AI Analysis of Charts and Events
Diagnosing a memory leak (heap, GC, heap dump)H7. Checking for a Memory Leak
Checking an unresponsive agent or its connectionH20. Checking Agent Status
The 19 slow transaction patternsH6. Finding the Cause of a Slow Transaction