Hi we have version 19C of Oracle database and its an Exadata box. We got complaint from user stating a query running longer and I see the query is big one, but after running the multiple parts of the query manually , I see below query which is part of main query is running for more than hours itself when we tested it from sqlplus prompt, it appears its struggling for fetching data and pass it to the client through network , as because the elapsed time shows very small as compared to the total run duration of the query. And this same CLOB columns gets fetched from the main query. Its using XMLAGG function and mostly returning a CLOB as output for one column causing the bottleneck. So my question is , can this fetch be made faster someway by converting into VARCHAR etc. or by changing any setup in client side? Also I see the "module" in dba_hist_active_sess_history is showing as "jdbc thin client" for this query.
SQL Text
------------------------------
WITH WITHTAB
AS ( SELECT DISTINCT TAB1.CKEY, TAB2.MCODE || ': ' || TAB2.MDATA , TAB1.CCODE
FROM TXN_TAB TAB1, TXN_TAB TAB2
WHERE TAB1.MID IS NOT NULL
AND TAB1.CCODE IN ('XX', 'YY')
AND TAB2.MID IS NOT NULL
AND TAB2.CCODE = 'AA'
AND ( ( TAB1.CID = TAB2.L1ID
AND TAB1.CCODE = TAB2.L1CD)
OR ( TAB1.CID = TAB2.L2ID
AND TAB1.CCODE = TAB2.L2CD)
)
)
SELECT CKEY, RTRIM ( XMLAGG (XMLELEMENT (e, MDATA, ',').EXTRACT ('//text()') ORDER BY MDATA).GetClobVal (), ',') STR FROM WITHTAB
GROUP BY CKEY
Global Information
------------------------------
Status : EXECUTING
Instance ID : 1
SQL ID : 6mdjzkvs9r73s
SQL Execution ID : 16777216
Execution Started : 08/16/2020 06:39:23
First Refresh Time : 08/16/2020 06:39:27
Last Refresh Time : 08/16/2020 07:36:45
Duration : 3442s
Module/Action : SQL*Plus/-
Program : sqlplus.exe
Fetch Calls : 40629
Global Stats
=========================================================================================================================================================================================
| Elapsed | Cpu | IO | Application | Concurrency | PL/SQL | Other | Fetch | Buffer | Read | Read | Write | Write | Uncompressed | Offload | Offload | Cell |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Time(s) | Waits(s) | Calls | Gets | Reqs | Bytes | Reqs | Bytes | Bytes | Elig Bytes | Returned Bytes | Offload |
=========================================================================================================================================================================================
| 147 | 133 | 12 | 0.00 | 0.00 | 2.51 | 1.97 | 40629 | 8M | 10936 | 11GB | 129 | 65MB | 9GB | 11GB | 254MB | 97.66% |
=========================================================================================================================================================================================
SQL Plan Monitoring Details (Plan Hash Value=761207327)
========================================================================================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Write | Write | Mem | Temp | Activity | Activity Detail | Progress |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | Reqs | Bytes | | | (%) | (# samples) | |
========================================================================================================================================================================================================================================
| -> 0 | SELECT STATEMENT | | | | 3430 | +16 | 1 | 40663 | | | | | . | . | 7.38 | Cpu (11) | |
| -> 1 | SORT GROUP BY | | 144K | 2M | 3431 | +15 | 1 | 40663 | 10 | 6MB | 129 | 65MB | 4MB | 65MB | 83.89 | Cpu (116) | 9% |
| | | | | | | | | | | | | | | | | direct path read temp (9) | |
| 2 | VIEW | | 144K | 2M | 108 | +16 | 1 | 671K | | | | | . | . | | | |
| 3 | HASH UNIQUE | | 144K | 2M | 120 | +4 | 1 | 671K | | | | | . | . | 1.34 | Cpu (2) | |
| 4 | NESTED LOOPS | | 144K | 2M | 13 | +4 | 1 | 2M | | | | | . | . | | | |
| 5 | NESTED LOOPS | | 144K | 2M | 13 | +4 | 1 | 2M | | | | | . | . | | | |
| 6 | TABLE ACCESS STORAGE FULL | TXN_TAB | 316K | 380K | 13 | +4 | 1 | 2M | 10926 | 11GB | | | . | . | | | 100% |
| 7 | BITMAP CONVERSION TO ROWIDS | | | | 13 | +4 | 2M | 2M | | | | | . | . | | | |
| 8 | BITMAP OR | | | | 13 | +4 | 2M | 2M | | | | | . | . | 0.67 | Cpu (1) | |
| 9 | BITMAP CONVERSION FROM ROWIDS | | | | 13 | +4 | 2M | 2M | | | | | . | . | | | |
| 10 | INDEX RANGE SCAN | TXN_TAB_UK | | 1 | 13 | +4 | 2M | 2M | | | | | . | . | 1.34 | Cpu (2) | |
| 11 | BITMAP CONVERSION FROM ROWIDS | | | | 13 | +4 | 2M | 2M | | | | | . | . | | | |
| 12 | INDEX RANGE SCAN | TXN_TAB_UK | | 1 | 13 | +4 | 2M | 2M | | | | | . | . | 1.34 | Cpu (2) | |
| 13 | TABLE ACCESS BY INDEX ROWID | TXN_TAB | 1 | 2M | 15 | +2 | 2M | 2M | | | | | . | . | 4.03 | Cpu (6) | |
========================================================================================================================================================================================================================================
Predicate Information (identified by operation id):
---------------------------------------------------
6 - storage("TAB2"."MID" IS NOT NULL AND "TAB2"."CCODE"='AA')
filter("TAB2"."MID" IS NOT NULL AND "TAB2"."CCODE"='AA')
10 - access("TAB1"."CID"="TAB2"."L1ID" AND "TAB1"."CCODE"="TAB2"."L1CD")
12 - access("TAB1"."CID"="TAB2"."L2ID" AND "TAB1"."CCODE"="TAB2"."L2CD")
13 - filter("TAB1"."MID" IS NOT NULL AND ("TAB1"."CID"="TAB2"."L1ID" AND "TAB1"."CCODE"="TAB2"."L1CD" OR "TAB1"."CID"="TAB2"."L2ID" AND
"TAB1"."CCODE"="TAB2"."L2CD") AND ("TAB1"."CCODE"='YY' OR "TAB1"."CCODE"='XX'))
Note
-----
- Unoptimized XML construct detected (enable XMLOptimizationCheck for more information)